MySQL的索引总结

什么是索引？

索引(Index)是帮助MySQL高效获取数据的数据结构。可以简单理解为：快速查找排好序的一种数据结构，可以提高数据检索的效率，降低数据库的IO成本。MySQL的索引主要分为主键索引(PRIMARY KEY)，唯一索引(UNIQUE) ，普通索引(INDEX)和全文索引(FULLTEXT) 。MySQL索引主要有两种结构：B+Tree索引和Hash索引，我们常用的索引结构是B+Tree索引。

主键索引

主键索引(PRIMARY KEY)的叶子节点保存着主键即对应行的全部数据。在InnoDB里，主键索引也被称为聚簇索引、聚集索引（clustered index）, 主键索引主要有以下特点：

• 主键索引列不允许有空值
• 一个表最多只能创建一个主键索引
• 主键索引可以有多列
• 主键索引可以被其他表引用为外键

什么是聚集索引？

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

二级索引（非主键索引）

按照定义，除了聚集索引以外的索引都是非聚集索引

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

而二级索引也成为非聚集索引，二级索引树中的叶子结点保存着索引值和主键值，当使用二级索引进行查询时，需要进行回表操作。如何通过sql语句来区分主键索引和二级索引的查询？

上面图展示有一个主键索引（id）和一个普通索引（name），那么：
select * from t_user where id = 1 即主键索引查询方式，则只需要搜索id这棵索引树
select * from t_user where name = 张三 即普通索引查询方式，则需要先搜索 name索引树，得到id的值为3，再到 id索引树搜索一次。这个过程也称为回表，也就是说，基于二级索引（非主键索引）的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询，前面已经说了主键索引，接下来说说非主键索引主要有哪些：

唯一索引

索引列的值必须唯一，但允许有空值。如果是联合索引，则列值的组合必须唯一。ps：说说唯一索引和主键索引的区别？

普通索引

普通索引允许在索引所在的列插入重复值和空值。单值索引就是常见的普通索引：一个索引只包含单个列，一个表可以有多个单值索引

联合索引

联合索引又叫复合索引，即一个覆盖表中两列或者以上的索引，看看什么情况下会使用联合索引：

• 需要加索引的字段，要在where条件中
• 数据量少的字段不需要加索引
• 如果where条件中是OR关系，加索引不起作用
• 符合最左原则
• 联合索引可以调用到覆盖索引，可以减少树的搜索次数，不再需要回表查整行记录，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

覆盖索引

当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中 （联合索引），可以直接使用索引查询而不需要回表。这就是覆盖索引，通过使用覆盖索引，可以减少搜索树的次数。

全文索引

FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列

最左前缀原则

顾名思义就是最左优先原则，以index （a,b,c）为例建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。

索引下推

如果在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器

• 索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。
• 关闭索引下推可以使用如下命令 set optimizer_switch='index_condition_pushdown=off

如何创建索引

以下是2种创建索引方式的基本语法：

create [unique] index indexname on tablename(columnname(length));

alter table tablename add index indexname (columnname(length));

举个例子：

1. 添加主键索引

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) 

2. 添加唯一索引

ALTER TABLE `table_name` ADD UNIQUE (`column`) 

3. 添加全文索引

ALTER TABLE `table_name` ADD FULLTEXT (`column`) 

4. 添加普通索引

ALTER TABLE `table_name` ADD INDEX index_name (`column` ) 

5. 添加组合索引

ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)

如何查看索引

show index from tablename;

如何删除索引

drop index indexname on tablename;

索引优化

哪些情况需要建索引

1. 主键，唯一索引
2. 经常用作查询条件的字段需要创建索引
3. 经常需要排序、分组和统计的字段需要建立索引
4. 查询中与其他表关联的字段，外键关系建立索引

哪些情况不要建索引

1. 表的记录太少，百万级以下的数据不需要创建索引
2. 经常增删改的表不需要创建索引
3. 数据重复且分布平均的字段不需要创建索引，如 true,false 之类。
4. 频发更新的字段不适合创建索引
5. where条件里用不到的字段不需要创建索引

SQL执行慢的原因

1. 硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。
2. 没有索引或者索引失效。（一般在互联网公司，DBA会在半夜把表锁了，重新建立一遍索引，因为当你删除某个数据的时候，索引的树结构就不完整了。所以互联网公司的数据做的是假删除.一是为了做数据分析,二是为了不破坏索引 ）
3. 数据过多（分库分表）
4. 服务器调优及各个参数设置（调整my.cnf）

索引失效怎么分析

1. 先观察，开启慢查询日志，设置相应的阈值（比如超过3秒就是慢SQL），在生产环境跑上个一天过后，看看哪些SQL比较慢。
2. Explain和慢SQL分析。比如SQL语句写的烂，索引没有或失效，关联查询太多
3. Show Profile是比Explain更近一步的执行细节，可以查询到执行每一个SQL都干了什么事，这些事分别花了多少秒。
4. 不遵守最左原则
5. 尽量使用覆盖索引
6. 使用不等于（！=或<>）会使索引失效
7. is null 或 is not null也无法使用索引
8. like通配符以%开头会使索引失效
9. 字符串不加单引号导致索引失效
10. 少用or，用or连接会使索引失效

Explain分析索引

使用explain关键字可以模拟优化器执行sql查询语句，从而得知MySQL是如何处理sql语句。
一张图解释MySQL的Explain

Show Profile分析索引

参考文章：MySQL高级知识（十一）——Show Profile

参考文章

https://www.runoob.com/mysql/mysql-index.html
https://blog.csdn.net/GV7lZB0y87u7C/article/details/79969293
https://www.cnblogs.com/developer_chan/p/9208404.html