MySQL索引
主键索引和普通索引结构上的区别
每一个索引在InnoDB里面对应一棵B+树。
有一个主键列为ID的表，表中有字段k，在k上有索引。
CREATE TABLE T (
id int PRIMARY KEY,
k int NOT NULL,
name varchar(16),
INDEX(k)
) ENGINE = InnoDB;
表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)。
InnoDB中主键索引也称为聚簇索引，主键索引的叶子节点存的是整行数据。
InnoDB中非主键索引也称为二级索引，非主键索引的叶子节点内容是主键的值。
如果非主键索引无法涵盖查询字段，那么就需要通过主键回表查询主键索引。索引可以覆盖查询字段时称为覆盖索引。
自增主键优势
NOT NULL PRIMARY KEY AUTO_INCREMENT
插入新记录时，系统会获取当前ID最大值加1作为新记录的ID值。
从性能角度来说，插入新记录不涉及其他记录的移动，不触发叶子节点的分裂。
从存储角度来说，主键长度越小，普通索引的叶子节点就越小。
为什么MySQL使用B+树？
树的高度会影响磁盘IO次数。
为什么不用二叉搜索树？
如果数据是顺序的，那么二叉树会变成链表样式，查找速度慢。
为什么不用红黑树？
红黑树一个节点只能存储一个数据，存储大量数据场景，红黑树高度大，查找速度慢。
为什么不用B树？
B+树在查找数据方面比B树更方便。
1. B+树的非叶子节点存储索引值，不存储数据行，叶子节点存储数据行。B树节点存储数据行，B+树比B树高度低。
2. 在范围查找时，B+树叶子节点采用双向链表，比B树快。
前缀索引
B+树可以利用索引的最左前缀来定位记录。
联合索引（name，age）
索引项是按照索引定义里面出现的字段顺序排序的。
当查询名字是张三的人时，快速定位到ID4，向后遍历得到结果。
当查询名字第一个字是张的人即where name like ‘张%’，快速定位到ID3，然后向后遍历，直到不满足条件为止。
最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。
使用联合索引时，考虑索引数量和成本。
索引下推
以市民表的联合索引（name, age）为例，检索出表中名字第一个字是张且年龄是10岁的所有男孩的SQL是
select * from tuser where name like '张%' and age=10 and ismale=1;
根据最左前缀原则，只能用 “张”，找到第一个满足条件的记录ID3，比全表扫描好。
MySQL 5.6之前，只能从ID3开始一个个回表，到主键索引上找出数据行，再对比字段值。MySQL 5.6引入的索引下推优化，在索引遍历过程中，对非主键索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
每一个虚线箭头表示回表一次。
无索引下推执行流程（回表4次）
索引下推执行流程（回表2次）
索引失效的情况
以%开头的like语句执行前导模糊匹配
select * from order where name like '%XX'
or语句前后没有同时使用索引
数据类型出现隐式强制转换
例如，varchar值不加单引号时可能会自动转换成int
select * from user where phone=13800001234
强制类型转换会导致全表扫描
负向条件查询
select * from order where status != 0 and stauts != 1
字段上执行函数计算
select * from order where YEAR(date) < = '2017'
可优化为值计算
select * from order where date < = CURDATE()
或者
select * from order where date < = '2017-01-01'
违反最左前缀匹配
idx_age_name(age, name)
select * from user where name = ‘王五’
范围查询时右侧的列不能使用索引
idx name_age_phoneNumber(name, age, phone_number)
where name = ‘王五’and age > 17 and phone_number = ‘13888888888’
查询行数
COUNT(*)和COUNT(1)没有区别，统计所有，性能差不多，推荐使用COUNT(*)；COUNT(列名)不统计该列是NULL的。
在MySQL 5.7.18之前，通过扫描主键来处理COUNT(*)。
在MySQL 5.7.18后，通过扫描最小的二级索引来处理COUNT(*)。如果不存在二级索引，那么扫描聚簇索引。二级索引比主键索引小。
索引对insert、update和delete的影响
insert
不会用到索引，维护索引会耗时。
update
使用基于区分度较高的字段加的索引，更新更快。
使用基于区分度较低的字段加的索引，更新其他字段时有无索引区别不大，更新这个字段时更新更慢。
delete
删除的条件上用到索引更新更快。
