《数据库索引，终于懂了》介绍了为什么B+树适合做数据库索引，数据库索引分为主键索引（PrimaryInkex）和普通索引（SecondaryIndex）。InnoDB和MyISAM是如何使用B+树来实现这两类索引的，有什么区别？问题一：MyISAM的索引结构是怎样的？MyISAM索引和行记录分开存储，称为非聚集索引（UnClusteredIndex）。主键索引和普通索引没有本质区别：有一个连续的聚合区，单独存储行记录；主键索引的叶节点存储主键，以及指向对应行记录的指针；普通索引的叶子节点存放索引列，以及对应行的Record指针；画外音：MyISAM表不能有主键。主键索引和普通索引是两个独立的索引B+树。通过索引列查找时，先定位到B+树的叶子节点，再通过指针定位到行记录。例如，MyISAM：t(idPK,nameKEY,sex,flag);表中有4条记录：1,shenjian,m,A3,zhangsan,m,A5,lisi,m,A9,wangwu,f,B的B+树索引结构如上图所示：行记录单独存储；id为PK，有一个id的索引树，叶子指向行记录；name为KEY，有name的索引树，leaves也指向行记录；问题2：InnoDB的索引结构是怎样的？InnoDB的主键索引和行记录存储在一起，所以称为聚集索引（ClusteredIndex）：没有单独的区域来存储行记录；主键索引的叶子节点存储主键和对应的行记录（而不是指针）；画外音：因此，InnoDB的PK查询是非常快的。因为这个特性，InnoDB的表必须有聚簇索引：如果表定义了PK，那么PK就是聚簇索引；如果表没有定义PK，第一个非空唯一列是聚集索引；否则，InnoDB将创建一个隐藏的row-id作为聚集索引；只能有一个聚簇索引，因为行数据在物理磁盘上只能有一个聚簇存储。InnoDB可以有多个普通索引，与聚簇索引不同的是：普通索引的叶子节点存放的是主键（不是指针）；问题三：为什么InnoDB推荐使用趋势递增的主键？由于InnoDB集成了数据行和索引，如果使用趋势递增的主键，在插入记录时，不会有索引分裂，也不会移动大量的行记录。问题四：为什么InnoDB不要使用长列作为主键？假设有一个以用户为中心的场景，包括身份证号码、身份证MD5、姓名、出生日期等业务属性。这些属性有查询要求和事务要求，必须使用InnoDB存储引擎。此时，如何设计数据表呢？最简单的设计思路是：身份证为主键；其他属性的索引；user(id_codePK,id_md5(index),name(index),birthday(index));这个索引树和行记录结构如上：id_code聚合索引，关联行记录；其他索引，存放id_code属性值；身份证号id_code是一个比较长的字符串，每个索引都存储这个值，当数据量大，内存宝贵的情况下，mysql缓冲区有限，索引和存储的数据会减少，出现的概率磁盘IO会增加。画外音：同时，索引占用的磁盘空间也会增加。此时应该添加一个没有业务意义的id自增列：将id自增列作为聚簇索引关联行记录；其他索引存储id值；用户（idPKautoinc、id_code（索引）、id_md5（索引）、姓名（索引）、生日（索引））；这样，有限的缓冲区可以缓冲更多的索引和行数据，磁盘IO频率会降低，整体性能会提高。你明白为什么InnoDB不应该使用更长的列作为主键吗？问题5：InnoDB的普通索引存储主键值可能有什么问题？使用普通索引查询时，可能会出现查询回表的情况。什么是查询回表？还是上面的例子：t(idPK,nameKEY,sex,flag);画外音：id是聚簇索引，name是普通索引。表中有4条记录：1,shenjian,m,A3,zhangsan,m,A5,lisi,m,A9,wangwu,f,B两个B+树索引如上图：id是PK，聚簇索引，Leaf节点存储行记录；name为KEY，普通索引，叶子节点存放PK值，即id；既然普通索引不能直接定位到行记录，那么普通索引的查询过程是怎样的呢？通常，需要对索引树进行两次扫描编码。例如：selectid,name,sexfromtwherename='lisi';行记录；这就是所谓的回表查询，先定位主键值，再定位行记录，其性能低于扫描索引树。问题六：如何优化查询回表？一个常见的解决方案是覆盖索引。什么是索引覆盖率（Coveringindex）？嗯，楼主在MySQL的官网上没有找到这个概念。画外音：你学习严谨吗？借用SQL-Server官网的说法。在MySQL官网上，在explainqueryplanoptimization章节中出现了类似的说法，即explain输出结果的Extra字段为Usingindex时，可以触发索引覆盖。无论是SQL-Server的官网还是MySQL的官网，都表示SQL需要的所有列数据都可以在一个索引树上获取，不需要回表，速度更快。如何实现索引覆盖？常用的方法是：将要查询的字段创建到联合索引中。查询需求selectid,name,sexfromtwherename='lisi';将单列索引（name）升级为联合索引（name，sex），避免返表。画外音：属性sex不需要通过聚簇索引查询。小结MyISAM和InnoDB都是使用B+树来实现索引的：MyISAM索引与数据分开存储；MyISAM索引叶子节点存储指针，主键索引与普通索引没有太大区别；InnoDB的聚簇索引和行数据统一存储；InnoDB的聚簇索引存储数据行本身，普通索引存储主键；InnoDB不应使用长列作为PK；InnoDB普通索引可能存在回表查询，常见的解决方案是覆盖索引；稿件转载请联系原作者】点此查看该作者更多好文
