MYSQL–>索引

概述

索引是帮助MYSQL 高效获取数据的 有序数据结构

数据库维护着满足特定查找算法的数据结构，这种数据结构以某种方式指向数据。

这样就可以在数据结构上实现高级查找方法，这种数据结构就是索引。

无索引的时候查询数据会进行全表扫描操作

有索引的时候查询数据会进行 排序二叉树的数据结构来查找数据

优点：提高排序效率，检索效率。

缺点：降低插入，删除，更新的效率且索引本身占用空间。

索引的结构

索引在存储引擎层实现，不同的存储引擎有不同的索引结构。

分类

索引结构 描述 B+Tree结构 最常见的索引类型，大部分引擎都支持B+树索引 Hash结构 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，
不支持范围查询。

R-tree结构(空间索引) 空间索引是MyISAM引擎的一种特殊索引类型，主要用于地理空间数据描述，使用较少。 Full-text(全文索引) 是一种通过建立倒排索引，快速匹配文档的方式。

支持情况

索引 InnoDB MyISAM Memory B+Tree索引 支持 支持 支持 Hash索引 不支持 不支持 支持 R-Tree索引 不支持 支持 不支持 Full-text 5.6版本后支持 支持 不支持

二叉树

顺序插入的时候，会形成一个链表，查询性能大大降低，大量数据的情况下，层次较深，

检索速度慢

可以通过红黑树解决，但红黑树在大数据量的情况下，层次也较深，检索速度很慢

B-Tree(多路平衡查找树)

性质

• 一颗m阶的B树存放(m-1)个关键字，一个节点最多m个指针引用。
• 叶节点具有相同的深度，叶结点的指针为空
• 结点中的数据从左到右递增
• 当B-Tree作为索引元素时，所有的索引元素不可以重复

B树形成流程简述

首先定义一个 5阶的B树(平衡5路查找树),现在我们要把

4、9、32、12、24、30、51、29、69、31、90、70、75、79、、80、85、91

1. 根据B树的性质得5阶B树一个节点最多4个值，故取出4 9 32 12形成第一个节点并内部排序成4 9 12 32

1. 插入24

1. 插入30

1. 插入51

1. 插入29

1. 插入69

1. 插入31

1. 插入90

1. 插入70

1. 插入75

1. 插入79

1. 插入80

1. 插入85

1. 插入91

具体去看数据结构B树的组成会更方便理解。暂时只要找到B树的结构性质即可。

B+Tree

首先定义一个 5阶的B树(平衡5路查找树),现在我们要把

4、9、32、12、24、30、51、29、69、31、90、70、75、79、、80、85、91

相较于b树的区别：

1. 所有的数据都会存与叶子节点
2. 叶子节点形成一个单项链表

Hash

特点

• 只能进行对比操作，即 = 和 in这种精确的值，不支持范围查询。
• 无法利用索引进行排序。
• 查询效率很高，一般进行一次索引即可(不出现hash冲突的情况下)效率高于B+树

在MYSQL数据库中，Memory引擎支持hash索引，但是在innodb引擎只能具有自适应hash功能

自适应hash功能:mysql会根据我们的查询条件在指定的条件下自动的将B+tree自动构成为hash索引。

为什么InnoDB引擎选择使用B+tree索引结构

• 相对于二叉树，层级更少，搜索效率更高。
• 对于B-tree，无论叶子节点或者非叶子结点，都会保存数据，这样刀子一夜中存储的键值减少，指针跟着减少，要同样保存大量数据只能增加树的高度，导致性能下降。
• 相当于hash索引，B+tree支持范围匹配和排序操作。

索引的分类

主键索引

作用:针对表中的 主键创建的索引

特点：默认自动创建并且唯一

关键字：primary

唯一索引

作用：避免同一个表中的某数据列的值重复

特点：可以有多个

关键字：unique

常规索引

作用：快速定位特点数据

特点：可以有多个·

全文索引

作用：全文索引查找的是文本中的关键数字，而不是比较索引中的值

特点：可以有多个

关键字：fulltext

在InnoDB中的索引(重点!)

聚集索引

将数据与索引放到一块存储， 索引结构的叶子节点保存了行数据

特点：必须存在且唯一。叶子节点为行数据

选取规则：

• 如果存在主键，那么 主键索引即是聚集索引。
• 如果不存在主键，那么 第一个唯一索引就是聚集索引
• 如果不存在主键，不存在唯一索引，那么innoDB引擎会自动生成一个rowid作为聚集索引

结构：B+tree的结果且叶子节点下方对应该行数据

二级索引

将数据与索引分开存储，索引结构和叶子节点的关联是对应的主键。

特点：可以存在多个。

结构：B+tree且叶子节点下方为对应主键值

回表查询：假定我们查找数据是通过二级索引查找的，但是要查的数据二级索引并不全部存储，就会先通过二级索引查找出主键然后通过聚集索引进行查找出全部数据。

索引的语法

创建索引

Create [unique|fulltext] index 索引名 on 表名(字段列表);

• unique代表唯一索引，字段列表中不能出现重复
• fulltext代表全文索引
• 如果这俩个都不加就默认创建 常规索引
• 一个索引只关联一个字段的叫 单列索引
• 一个索引关联多个字段的叫 联合索引
• 只关联了主键的索引叫主键索引

查看索引

show index from 表名;

删除索引

Drop index 索引名 on 表名;

SQL性能分析

SQL执行效率

查看服务器状态信息

show [session|global] status;

信息量过大一般用模糊匹配方式精确查询

查询增删改查的使用效率

show global status like 'com_______';

慢日志查询

慢查询日志就是记录了所有执行时间超过指定参数（默认10s）的所有SQL语句的日志。

默认是关闭的。

查询慢查询日志是否开启

show variables like 'slow_query_log';

开启慢查询日志

set global slow_query_log = 'ON';

关闭慢查询日志

set global slow_query_log = 'OFF';

设置默认时间参数

set global Long_query_time=时间;

设置慢查询日志存放位置

set global slow_query_log_file='存放位置';

在Linux中 /var/lib/mysql目录下的xx-slow.log 为慢查询日志文件
show variables like ‘slow_query%’; 查询慢查询日志的位置

Profiles

帮助我们在SQL优化的时候了解时间耗费到哪里去了

查询每一条SQL语句的耗时

show profiles;

查询是否支持show profiles

select @@have_profiling;

查询是否开启了profiling

select @@profiling;

开启/关闭profiling

set [global|sessing] profiling =1或者0;

查询指定SQL语句时间用到了哪里

Show profile for query 他显示的id;

查询指定SQL的cpu耗费情况

Show profile cpu for query 他显示的id;

Explain执行计划

Explain会获取MYSLQ如何执行SQL，且信息比前面三种详细且简单。

语法

explain SQL语句;

可在末尾加上 \G 更加方便查看数据

Explain执行计划每个字段的含义

字段 解释 ID select语句查询的序号，因为select存在子查询故这个是显示select的执行顺序。ID值越大越先执行，id相同的执行顺序从上到下。 select_type 表示select的类型，常见取整有simple(简单表，即不使用子查询，连接)，primary(主查表，就子查询外面那个大的select) Type 表示连接的类型，性能由好到坏为(null > system > const > eq_ref > ref > range > index > all) possible_key 显示这张表
可能

用到的索引 key 这张表实际用到的索引 key_len 代表使用索引的字节数，这个值是该索引的最大可能长度，长度越短性能越好 Rows mysql认为必须要执行查询的行数，在innodb中是一个估计值 Filtered 查询返回的行数占总读取的行数百分比

select_type几个常见的值解释

1. SIMPLE，简单查询，不包括子查询或者union操作
2. 在未被SEMIJOIN的情况下
   如果有子查询，那么最外层的查询被标记为PRIMARY
   由于select name from t_user id 依赖于子查询的查询结果，所以该子查询被标记为 DEPENDENT SUBQUERY
3. UNION RESULT，使用union产生的结果集被标记为 UNION RESULT
4. UNION,使用union 或 union all 后的查询被标记union
   将第2，3两个查询与1合并
   1 被标记为PRIMARY
   2,3 被标记为union

key_len的解释:

1. 首先他表示索引使用的字节数的大小，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要。值越大越好。
2. 计算方法：
   创建联合索引(a,b,c)
   key_len=a索引字节大小+b索引字节大小+c索引字节大小
   字节大小=索引的数据类型字节大小+其他部分

其他部分存在情况：

1. 当数据类型为定长时：如int ,char ,datetime这种如果没有not null约束那么为 大小=(数据类型字节+1字节)，如果有not null约束就不需要+1字节
2. 当数据类型为不定长时：比如varchar这种，除了是否为空占1字节，长度信息占2字节即 大小=(数据类型占用字节大小+3字节)
3. 对于char,varchar,text,blob类型：数据大小还与字符集类型有关：latin一个字符占用1字节，gbk一个字符占用2字节，utf8一个字符占3字节

Rows的解释:

1. 是MySQL认为它要检查的行数（仅做参考），而不是结果集里的行数
2. 同时 SQL里的 LIMIT 和这个也是没有直接关系的。

Filtered的解释：

1. 首先Filtered的值越大代表效率越高
2. 这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的百分比，因此这个值越大说明过滤掉的越少，就”越好”，但显然，filtered如果很小，只能说明被过滤的多了，并不一定说明索引不好或者引擎效率不高，所以这个值实质上参考意义不大

Extra几个比较重要的值

extra select的字段 null 查询的列未被索引覆盖 Using index 查询的列被索引覆盖 Using where; Using index 查询的列被索引覆盖 Using where; 查询列未被索引覆盖 Using index condition 查询列未被索引覆盖

使用索引的规则

一个索引关联了多列或多个字段就称之为 联合索引

使用联合索引的原因

建一个联合索引(col1,col2,col3)，实际相当于建了(col1)，(col1,col2)，(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

最左匹配规则

匹配规则：对于联合索引，MySQL会一直向右匹配直到遇到范围查询(＞、＜、between、like)就停止匹配

比如a=1 and b=2 and c＞3 and d=4如果建立(a,b,c,d)顺序的索引，d是用不到索引的

如果建立(a,b,d,c)的索引则都可以用到索引

故MYSQL中创建联合索引的时候， 索引的顺序很重要！

故条件中a|ab|abc是走索引的bc不走索引,ac中a走索引c不走索引
在MySQL8.0版本之后，增加了索引跳跃扫描(Index skip Scan)的功能，当第一列索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到联合索引。

最左匹配规则的底层原理

索引的底层是一颗B+树，联合索引底层当然也是一颗B+树，区别在于联合索引的键值是多个。

但是构建一颗B+树只能根据一个值来构建， 故数据库依据联合索引最左字段来构建B+树！

假定创建一个(a,b,c)的联合索引，索引树如图

该图就是通过(a,b,c)联合索引形成的B+树

可以看出非叶子节点存储的是第一个关键字的所有a，叶子节点存储的是三个关键字的数据。

可以看出a是有序的b，c是无序的。

当a相同时b是有序的，b相同时又是有序的！这就是最左匹配规则的底层原理！

联合索引就是按照第一列进行排序，然后第一列排好序的基础上再对第二列进行排序，以此类推。如果没有第一列直接访问第二列，第二列肯定是无序的，直接访问后面的列就用不到索引了。

查询优化器

问：如果举例索引顺序为(a,b,c)但查询条件为 where b=1 and a = 2;为什么还用到了索引

答： 理论上索引对顺序是敏感的，但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以 MySQL 不存在 where 子句的顺序问题而造成索引失效

索引失效的几种情况与解决方法

范围查询

联合查询中因为最左匹配规则，遇到范围查询，范围查询右边会不走索引。

解决方法：尽量使用>= 或

Original: https://www.cnblogs.com/wdadwa/p/MYSQL_Learning_07.html
Author: wdadwa
Title: MYSQL–>索引