Mysql索引详解

索引可以极大的提升Mysql的数据检索速度，类似于书籍的目录，可以根据目录的章节标题快速定位到书中的内容。但索引在提高查询速度的同时需要额外的存储成本和维护成本，会降低对表更新操作的速率。

01 | 索引类型

1.1 普通索引

最基本的索引类型，基于普通字段建立的索引，没有任何限制。

1.2 唯一索引

在普通索引的基础上，要求字段值必须唯一，但允许NULL值

1.3 主键索引

特殊的唯一索引，不允许NULL值且一个表只能有一个主键

1.4 复合索引（组合索引）

在多个列上建立索引称为符合索引。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

1.5 全文索引

应用于大量的文本检索，速度优于like模糊查询

基本语法

# 方法一：创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                );


# 方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;


# 方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;
                             
# 删除索引：DROP INDEX 索引名 ON 表名字;

02 | 索引原理

索引是存储引擎用于快速查找记录的一种数据结果，是物理数据页存储，在数据文件中，利用数据页存储；索引可以加快检索速度，同时也会降低增删改操作速度，需要额外的维护代价。

Hash和BTree

• Hash索引 ：查询单条快，范围查询慢
• BTree索引 ：B+树，范围查询快
  不同存储引擎支持索引如下：

存储引擎	是否支持事物	锁级别	B+Tree	Hash	Full-Text
InnoDB	是	行级锁	是	否	是
MyISAM	否	表级锁	是	否	是
NDB	是	行级锁	否	是	是

03 | 索引分析优化

3.1 EXPLAIN 命令

-- 使用
EXPLAIN select * from XXXX where columnXXX = xxxx;

• select_type : 查询类型，常用值如下：

  • SIMPLE ： 简单查询，不含好字查询或union
  • PRIMARY： 表示次查询是最外层的查询
  • UNION：表示此查询是UNION的第二个或后续的查询
  • DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
  • UNION RESULT：UNION的结果
  • SUBQUERY：SELECT子查询语句
  • DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

• type ：存储引擎查询数据采用的方式

  • ALL：表示全表扫描，性能最差。
  • index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
  • range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
  • ref：表示使用非唯一索引进行单值查询。
  • eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
  • const：表示使用主键或唯一索引做等值查询，常量查询。
  • NULL：表示不用访问表，速度最快。

• possible_keys : 查询是可能用到的索引，并不一定会真正使用

• key ：查询是真正用到的索引名称

• rows : MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低

• key_len： 表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。

• Extra ： Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：

  • Using where：表示查询需要通过索引回表查询数据。
  • Using index：表示查询需要通过索引，索引就可以满足所需数据。
  • Using filesort：表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化。
  • Using temprorary：查询使用到了临时表，一般出现于去重、分组等操作。

3.2 回表查询、覆盖索引

在InnoDB中索引分为聚簇索引和辅助索引，聚簇索引的叶子结点存储行记录，辅助索引叶子结点存储的是聚簇索引值和索引字段值。
通过索引值查询无法直接定位整条记录，如查询的字段信息中包含除索引字段之外的其他字段，就需要先通过辅助索引定位聚簇索引，再通过聚簇索引定位具体行记录，这个过程称为回表查询。当索引中包含查询的所有字段，此时则不需要回表，因为辅助索引中包含了所需的字段值，直接在索引中读取字段值即可，这类查询过程称为覆盖索引。
InnoDB中规定了每一张表必须有一个聚簇索引，聚簇索引规则如下：

• 1. 如果表定义了主键，那么主键就是这个表的聚簇索引
• 2. 如果没有定义主键，那么第一个非空的唯一索引就是这个表的聚簇索引
• 3. 如果没有定义主键且没有非空唯一索引，那么InnoDB会额外创建一个隐藏的ROWID作为聚簇索引

3.3 最左前缀原则

最左前缀原则是基于复合索引，在一个复合索引中，如查找中使用到最左边的列，那么查询就会使用到复合索引，否则索引将失效。
原理 ：索引都是有序数列，如建立符合索引(name,age,sex)，那么在索引结构中，会先对name进行排序，接着age最后sex。如果从第二个索引开始查的话，索引无法进行工作。

04 | 查询优化

4.1 开启慢查询日志

-- 查看慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log%'
-- 通过一下命令开启慢查询日志
SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10; -- 慢查询阀值，单位秒

4.1 查询优化

• 适当建立索引，尽可能选择过滤性好的数据列作为索引
• 避免使用select * ，利用覆盖索引机制减少回表次数

更多优化项目参考：

参考：
https://www.cnblogs.com/nickchen121/archive/2019/07/09/11155947.html
https://www.cnblogs.com/yanggb/p/11252966.html
https://www.cnblogs.com/houss/p/10598901.html