提到如何提高MySQL检索性能，一个很直接的答案就是建立索引，但是索引如果建立不恰当可能会起到相反作用，本文默认引擎为InnoDB来解释。
聚集索引和非聚集索引
数据库表一般会将主键Id定义为聚集索引，一张表只存在一个聚集索引，并且在聚集索引B+树的叶子节点上面存放的是整条记录。
而非聚集索引可以创建很多个（但是一般建议不超过5个），在非聚集索引的B+数上叶子节点上面存放的是主键Id。
实例
创建一个MySQL数据库表，语句如下
CREATE TABLE `Teacher` (
`Id` int NOT NULL AUTO_INCREMENT,
`Name` varchar(20) NOT NULL,
`Age` int NOT NULL,
`Phone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB
只包含一个聚集索引，创建完成后随便插入些数据。
回表
假如我们有业务代码需要很频繁使用名称Name去检索信息，可以创建一个Name的索引，修改表：
ALTER TABLE `Teacher`
ADD INDEX `Idx_name` (`Name`)
接着根据Name查询：
EXPLAIN SELECT * FROM Teacher WHERE Name='Alex'
注意前面的EXPLAIN关键字，这个关键字可以显示语句执行的详细信息，包含了索引使用情况。
image.png
其中possible_keys表示这条语句可用的索引，而后面的key才是真正使用的索引。
实际上引擎会先去访问Idx_name这个索引获取到主键Id以后再次查询Primary索引，这就是回表操作。
所以是否可以不回表来进一步提高查询执行效率？当然可以！
索引覆盖
索引覆盖值得是辅助索引中已经包含有想要查询的字段，因此不用再去主索引中再次进行定位。
假如业务中有很高频的请求是根据电话去查找名称，可以建立一个电话-姓名的联合索引。
ALTER TABLE Teacher
ADD INDEX `Idx_Phone_Name` (`Phone`,`Name`)
执行查询语句，只查询姓名：
EXPLAIN SELECT Name FROM Teacher WHERE phone='123'
image.png
可以注意到这句查询使用了Idx_Phone_Name索引并且Extra信息显示是Using index。
再次执行查询语句，这次查询所有的信息：
EXPLAIN SELECT * FROM Teacher WHERE phone='123'
image.png
这一次Extra未显示Using index，说明进行了回表操作。
联合索引和最左前缀原则
前面其实已经使用了联合索引，个人觉得联合索引最大的用处在于减少索引数目，减小索引维护成本，最左前缀原则是指所有和联合索引从左向右顺序相同的查询都可以使用这个联合索引。比如创建了一个A_B_C的索引，其实某种程度上就相当于创建了A，A_B，A_B_C三个索引。
索引下推
在MySQL5.6版本之后，数据库支持了索引下推，看看这个语句：
EXPLAIN SELECT * FROM Teacher WHERE phone='123' AND name LIKE 'alex'
此时Extra中信息为Using index condition
image.png
这里查询的是全部数据，因此不可避免会回表查询。但是因为存在索引下推，引擎在第一次查找Idx_Phone_Name树的时候不但会根据Phone的值去做判断，还会过滤掉不满足Name条件的记录，避免无意义的回表操作。
索引丢失
索引丢失指的是存在没有被使用的索引，这样维护起来的索引是无效的，造成了性能浪费，开发中需要尽量避免索引丢失的情况。而造成索引丢失的原因大概包括：
- 被索引字段发生了隐式类型转换
- 被索引字段使用了表达式计算
- 被索引字段使用了函数
- 被索引字段不是联合索引地最左字段
- like关键字前使用了左模糊匹配或者左右模糊匹配
举个例子
创建一个Phone上面的索引：
ALTER TABLE Teacher
ADD INDEX `Idx_phone` (`Phone`)
然后执行查询语句,注意这里的给phone的值是数字，MySQL查询过程中支持类型转换因此不会报错：
EXPLAIN SELECT * FROM Teacher where phone=123
image.png
可以看到，possible_key中显示可用索引为Idx_phone，但是实际上使用索引key为空。
其他情况下的索引丢失可以自己去试下。
网友评论