MYSQL 精通索引【快速理解】

1、什么是索引？

2、索引结构

1.为什么不使用二叉树呢？

2.B树数据结果

3.B+树

4.Hash结构

3、索引语法

1.创建索引

2.查看索引

3.删除索引

4、SQL性能分析

1.SQL执行频次

2.慢查询日志

3.profile详情

4.EXPLAIN

5、索引规则

1.最左前缀法则

2.索引失效情况

3.SQL提示

4.索引覆盖&回表查询

5.前缀索引

6.单列索引&联合索引

6、索引设计原则

1、什么是索引？

索引是一种可以快速查询的有序的数据结构。如果查询没用上索引的话，那么就会出现把表中的每一条数据进行扫描，这样效率太差。

索引的优点就是查找速度快，并且减少进行IO请求次数，根据索引排序减少与CPU消耗。但是存储索引要空间进行存储，并且增删改的时候效率较低因为要在数据结构上修改节点。

索引一般是一级索引也叫聚集索引，非聚集索引就是以非主键作为索引生成的索引，一般主键就是聚集索引，没有主键会把第一个非空索引当做聚集索引，如果都没有会生成一个隐藏的rowId作为聚集索引。

2、索引结构

B+树索引大部分引擎都支持的数据结构、Hash索引，R-tree索引、Full-Text索引

目前使用InnoDB树结构那么就主要介绍B+树结构

1.为什么不使用二叉树呢？

因为二叉树可能会有链化问题，比如二叉排序树，链化斜树，这样查找层树过多导致查找效率太慢，红黑树保证不会倾斜，但是数据的存储的值每个节点只能存两个，对于存储多个数据，也会导致层次太高查找性能不足。

2.B树数据结果

5阶数说明有5个子节点，有4个key 5个指针。

3.B+树

它的特点是所有数据都会出现在叶子节点，如果数据满的话键值会从中间向上分裂，并且叶子节点会有一根单向链表进行连接。在Mysql中优化了一个点就是多了一个指针形成双向链表在叶子节点，保持查找的顺序，提供区间访问性能。

目前我们用的是B+树，为什么？因为如果根据B+树的话，非叶子节点存储的就是指针而不是值，因为一个取最多能存1M大小的内容，然后一个页最多存16k的内容，也就是一个区最多能存64页，我们设想如果节点上存键值指针以及数据，那么一个区能存的指针键值就少，那么我树的高度就会变高层级变大，如果是B+树，非叶子节点只存储键值和指针，那么同样空间能存储的键值就多，树的层级变小，查找效率变高。比如这个数是5阶那么可能单数据太大，B树节点可能4个数据加指针加键值就存满了。

4.Hash结构

确定是不能范围查询、不支持排序功能，好处是块，因为存储的时候会生成一个对应的hash值，对应着该数据的位置，如果查找的时候根据键值通过hash算法就可以找到对应hash值位置的数据 Memory引擎支持该结构。

3、索引语法

1.创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name on table_name(index_col_name)

2.查看索引

SHOW INDEX FROM table_name;

3.删除索引

DROP INDEX index_name ON table_name;

4、SQL性能分析

1.SQL执行频次

show global status like 'Com______'；

当前数据库的增删改查的次数的频率

2.慢查询日志

查看慢查询日志开关

show variables like 'slow_query_log';

在/etc/my.cnf中

日志存在/var/lib/mysql下如果超过2秒的查询就会存的该日志里

3.profile详情

select @@have_profiling; 查询是否支持profile操作

SETprofiling=1;开启

通过SHOW profiles;就看到查询的语句对应的耗时时间。

4.EXPLAIN

可以看到SQL执行计划，能看到查询语句，可以看到表的查询的连接顺序，还能看到使用的索引。

EXPLAIN SELECT 字段列表 FROM 表名 WHERE条件，意思就是任意查询前加一个EXPLAIN就可以了。

根据查询结果，id值相同的话是执行顺讯是从上到下，id值不同的执行顺序是id值越大越先执行。

5、索引规则

1.最左前缀法则

如果使用了联合索引，那么查询条件使用索引是从最左侧那个索引必须使用，并且不能跳过中间列，如果跳过了那么后面的索引失效也就是当初设置索引的顺序是ABC，可以是AB因为中间没有跳过。与写的时候的位置无关，也就是当初设置索引的顺序是ABC,写的时候只有有ABC就可以无需关系是ABC BCA ACB。但是如果使用了 > <的查询操作则范围查询右侧索引失效。