mysql主键的全索引扫描和全表扫描的区别

全表扫描的意思就是要把表中所有数据过一遍才能显示数据结果，索引扫描就是索引，只需要扫描一部分数据就可以得到结果， 打个比方吧，在新华字典中，如果没有拼音或笔画索引，当我们查找“做”这个字就要从字典第一页一次往后查，一直插到Z开头的部分才能找到，即使找到也不确定后面是不是还有（假定字典是无序状态的），因此还得往后找，知道正本字典翻完，才确定“哦，原来刚才找到的那个记录就是想要的结果了”。索引扫描的意思就是我们预先知道“做”这个字在拼音的Z区域，然后根据前面目录查看"zuo"这个拼音在那一页，然后直接翻到那一页就能找到我们要的结果了，这样就能大大减少查询的时间

InnoDB默认创建的主键索引是聚簇索引（Clustered Index），其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。

我们使用一个 表来详细说明 商品表 CREATE TABLE `merchandise`

 (

`id` int(11) NOT NULL,

`serial_no` varchar(20) DEFAULT NULL,

 `name` varchar(255) DEFAULT NULL,

`unit_price` decimal(10, 2) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE

)

CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic

插入数据：

id    serial_no   name   unit_price

1    21002    鼠标    100

5    21003    键盘    300

8    20021    显示器    1200

11    12172    手柄    350

18    22391    主机箱    3798

InnoDB 索引默认使用的是B数索引，(关于B+树的数据结构，有兴趣的可以去翻翻相关资料。)mysql 聚簇索引(主键)的叶子节点则记录了主键值、事务id、用于事务和MVCC的回流指针以及所有的剩余列，其结构大概如下图所示：

这意味着， 主键是和 行数据存储在一起。 还有，非叶子节点存储着主键的ID，这样单独扫描主键的时候，是扫描非叶子 节点，而oracle 的主键，其实 是和 行数据分开的. 这是和oracle 最大的不同。

辅助索引 （或者叫二级索引或者一般索引）的优势在于。   （唯一索引、普通索引、前缀索引等都是二级索引）InnoDB在移动行时，无需维护二级索引，因为叶子节点中存储的是主键值，而不是指针。但是查找的过程多了一层， 查找的是时候时候先到的主键，再到数据（但是数据和主键存一起，所以速度也不慢）

因为主键是和 行数据一起存放的，若表的主键不是顺序的id，而是无规律数据，比如字符串，InnoDB无法加单的把一行记录插入到索引的最后，而是需要找一个合适的位置（已有数据的中间位置），甚至产生大量的页分裂并且移动大量数据，在寻找合适位置进行插入时，目标页可能不在内存中，这就导致了大量的随机IO *** 作，影响插入效率。除此之外，大量的页分裂会导致大量的内存碎片。 这也是为什么 mysql 推荐使用 逻辑主键，而不适用业务主键的原因，业务主键不能保证数据的插入是连续的。

主键定位问题叶节点data域保存了完整的数据记录（非叶子节点）聚集索引非叶子节点中，Pointer（索引键值）指向的是ibd文件的Page Offset（File Header中的FIL_PAGE_OFFSET），这样就定位到数据块在ibd文件中的偏移量了。通过关联该ibd文件在 *** 作系统的inode，就能找到磁盘中的具体数据块了。找到数据块，将它读入Innodb buffer pool，然后通过Page Directory（页目录）进行二分查找，来定位到行记录，这个过程中需要使用Record Header中的next_record。

---