索引是一种帮助MySQL高效获取数据的有序数据结构，底层为B+树结构，索引有以下作用：
（1）提高运行效率
（2）降低IO成本（对B+树来说，查询千万级别的数据最多只需要3-4次磁盘IO）
（3）索引对数据排序，降低了CPU的消耗
一、B+树
InnoDB存储引擎的索引使用B+树实现。它只在叶子节点存储数据（注意： InnoDB的主键索引的叶子节点存储行数据，非主键索引的叶子节点存储主键和其他带索引的列数据），且叶子节点之间用指针连接，而非叶子节点只存储指针（索引）。B+树有以下优点：
（1）读写B+树的代价更低
千万级的数据只需要3-4层高度就可以，且B+树有大量冗余节点，删除一个节点的时候可以从叶子节点删除，可以不用改动非叶子节点。
（2）查询B+树更加稳定
每次查询都必须查询到叶子节点，而B-树的数据可能在叶子节点，也可能在中间节点，或者都有
（3）便于扫库和区间查询
B+树的叶子节点之间是双向链表连接
二、索引有哪些
1. 单值索引：一个索引只包含单个列，一个表可以有多个单列索引
- 建表时，加上 key(列名) 指定
- 单独创建，create index 索引名 on 表名(列名)
- 单独创建，alter table 表名 add index 索引名(列名)
2. 唯一索引：索引列的值必须唯一，但允许有 null 且 null 可以出现多次
- 建表时，加上 unique(列名) 指定
- 单独创建，create unique index idx 表名(列名) on 表名(列名)
- 单独创建，alter table 表名 add unique 索引名(列名)
3. 主键索引：设定为主键后数据库会自动建立索引，innodb 为聚簇索引，值必须唯一且不能为null
- 建表时，加上 primary key(列名) 指定
4. 复合索引：即一个索引包含多个列，如（name，password）
- 建表时，加上 key(列名列表) 指定
- 单独创建，create index 索引名 on 表名(列名列表)
- 单独创建，alter table 表名 add index 索引名(列名列表)
5.前缀索引：对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率
三、索引创建原则
1.什么时候需要创建
（1）数据量较大，且查询比较频繁的表建立索引
（2）常作为查询条件的字段创建索引，如where、group by、分组
（3）尽量使用联合索引，减少单列索引
（4）字符串类型的字段，且字段较长的情况下建立索引——前缀索引
2.创建索引需要注意什么
（1）表数据太少，不需要创建索引
（2）索引不是越多越好，控制索引数量
（3）选择区分度高的列，建立唯一索引（如性别就不合适）
（4）频繁变更的字段不适合创建索引，频繁变更会更改B+树，影响数据库性能
四、索引失效
如有一个联合索引（name,age,address），在以下几种情况下会失效
（1）违反最左前缀原则，即跳过某一列查询
如 name，address，跳过了age；使用age，address查询，跳过了name，= 和 in可以乱序
（2）范围查询右边的列会造成索引失效
如 where name = ' Mike' and id >= 1 and address = '上海' ， 会造成address不能命中
（3）like的前导模糊查询
如 like %小米
（4）字符串不加单引号
如 borad = 小米，正确 borad = '小米'
（5）在索引列上进行运算操作
如 where subString（name，3，2）= '小米'
（6）全表查询比使用索引快时，索引失效
（7）索引不会包含有NULL值的列，IS NULL，IS NOT NULL无法使用索引：
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以在数据库设计时，尽量使用NOT NULL约束以及默认值。
五、索引优化
1. 避免使用导致索引失效的用法
（1）like语句的前导模糊查询不能使用索引
（2）范围条件右边的列(范围列可以用到索引)，范围列之后列的索引全失效：
（3）不要在索引列上面做任何操作（计算、函数），否则会导致索引失效而转向全表扫描
范围条件有：<、<=、>、>=、between等
（4）强制类型转换会全表扫描：
字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换，相当于在索引列上进行了操作。
（5）负向条件查询不能使用索引：
负向条件有：!=、<>、not in、not exists、not like 等，优化案例：
2. 其他优化方式
（1）union、in、or 都能够命中索引，但是建议使用 in：
因为in的综合效率最高。
（2）联合索引最左前缀原则：
如果在(a,b,c)三个字段上建立联合索引，那么他会自动建立 a| (a,b) | (a,b,c) 组索引。
- 建立联合索引的时候，区分度最高的字段在最左边
- 存在非等号和等号混合判断条件时，在建立索引时，把等号条件的列前置。如 where a>? and b=?，那么即使a 的区分度更高，也必须把 b 放在索引的最前列
- 最左前缀查询时，并不是指SQL语句的where顺序要和联合索引一致
六、SQL优化
1. 对表的设计优化
分库分表：进行水平分割或垂直分割
2. SQL语句优化
（1）避免使用select *
（2）避免索引失效的写法
（3）用union all 代替union，因为后者多一次过滤
（4）能用inner join就不用left join、right join，因为内连接会对两个表进行优化，默认以小标为驱动，而左右连接不会调整连接顺序
（5）使用between and代替in
（6）对枚举类型的字段，如性别、星期等建议使用enum而不是varchar
（7）字段设计尽可能使用not null
3. 定位慢查询
（1）开源工具调试：如skywalking等，可以查看接口的执行情况
（2）使用MySQL自带的慢日志
在配置文件中 使用 slow-query-log = 1爱妻，设置值为2s
4. 优化慢sql
采用EXPLAIN或者DESC命令，可以展示sql的执行情况，如
EXPLAIN SELECT * FROM user WHERE id = 1，该命令有以下几个字段
（1）possible-key：可能会用的索引
（2）key：实际命中的索引
（3）key-len：索引占用的大小
（4）Extra：额外的优化建议，是否存在回表等
（5）type：这条sql的连接类型，是否有优化空间，类型有以下几种：
const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。
system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
ref：非唯一性索引扫描，其他索引
range：只检索给定范围的行，一般就是在where语句中出现了between、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引
index：扫描全部索引，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）
ALL：全表扫描，遍历全表以找到匹配的行
参考：MySQL高级 之 explain执行计划详解_走慢一点点的博客-CSDN博客、CSView
157
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
