【MySQL|MySQL — 索引】
目录
- 1、索引概述
- 2、索引结构
- 3、索引分类
- 4、索引语法
- 5、SQL 性能分析
- 5.1、执行频次
- 5.2、慢日志查询
- 5.3、profile
- 5.4、explain
- 6、索引使用规则
- 6.1、单列&联合索引
- 6.2、最左前缀
- 6.3、范围查询
- 6.4、索引失效
- 6.5、SQL 提示
- 6.6、覆盖索引
- 6.7、前缀索引
- 7、索引设计原则
1、索引概述 索引（Index）是高效获取数据的数据结构，就像书的目录，提高检索数据的效率。
优点：提高数据检索效率，降低数据库的 IO 成本；通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗。
缺点：索引列会占用空间；索引提高查询效率的同时也降低了更新表的速度，如对表进行增删改时，效率降低。
2、索引结构 MySQL 的索引结构主要包含以下几种：
| 索引 | 描述 |
|---|---|
| B+Tree | 最常见的索引类型，大部分存储引擎都支持 B+ 树索引 |
| Hash | 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 |
| R-Tree | 空间索引时 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，较少使用 |
| Full-Text | 是一种通过建立倒排序索引，快速匹配文档的方式，类似于 ES、Solr |
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+Tree | 支持 | 支持 | 支持 |
| Hash | × | × | 支持 |
| R-Tree | × | 支持 | × |
| Full-Text | 5.6版本之后支持 | 支持 | × |
- 二叉树
Binary tree，二叉树特点是每个节点最多只能有两棵子树，且有左右之分。如果选择二叉树作为索引结构，会存在以下缺点：顺序插入时，会形成一个链表，查询性能大大降低；大数据量情况下，层级较深，检索速度慢。
- B树
B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。以一颗最大度数（max-degree，一个节点的子节点个数）为5阶的B树为例，那这个B树每个节点最多存储4个key，5个指针。一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。在B树中，非叶子节点和叶子节点都会存放数据。
- B+树
相对于B树的区别：所有的数据都出现在叶子节点；叶子节点形成一个单向链表；非叶子节点仅起到索引数据作用。MySQL 索引数据结构在原B+树上进行了优化，增加了一个相邻叶子节点的链表指针，形成带有顺序指针的B+树，提高区间访问性能，利于排序。
- Hash
哈希索引就是采用一定的hash算法，将键值换算成新的 hash 值，映射到对应的槽位上，然后存储在 hash 表中。如果两个或多个键值映射到一个相同的槽位上，他们就产生了 hash 冲突/碰撞，可以通过链表来解决。Hash 索引只能用于对等比较（=，in），不支持范围查询（between，>，< ，...），无法利用索引完成排序操作，查询效率高，通常不存在 hash 冲突的情况下只需要一次检索就可以了，效率通常要高于B+树索引。
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 表中主键创建的索引 | 默认自动创建，只有能一个 | primary |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | unique |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | fulltext |
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引（ClusteredIndex） | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有，且只有一个 |
| 二级索引（SecondaryIndex） | 将数据与索引分开存储，索引结构的叶子节点关联的是该字段值对应的主键值 | 可以存在多个 |
1、如果存在主键，主键索引就是聚集索引；
2、如果不存在主键，将使用第一个唯一索引作为聚集索引；
3、如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。
4、索引语法
#创建索引
create [ unique | fulltext ] index 索引名 on 表名 (字段名, ...);
#查看索引
show index from 表名;
#删除索引
drop index 索引名 on table;
5、SQL 性能分析 5.1、执行频次 MySQL 客户端连接成功后，通过如下指令可以查看当前数据库 insert、delete、update、select 的访问频次。
#查询全局数据：global
#查看当前会话：session
show global status like 'Com_______';
#七个下划线#执行命令
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog| 0|
| Com_commit| 0|
| Com_delete| 2| #删除次数
| Com_import| 0|
| Com_insert| 5| #插入次数
| Com_repair| 0|
| Com_revoke| 0|
| Com_select| 10| #查询次数
| Com_signal| 0|
| Com_update| 2| #更新次数
| Com_xa_end| 0|
+---------------+-------+
5.2、慢日志查询 慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name| Value |
+----------------+-------+
| slow_query_log | ON|
+----------------+-------+
配置文件：MySQL Server 8.0\my.ini
#慢日志查询开关
slow-query-log=1#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=10
暂略。
5.3、profile show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费在哪里。
查看当前 MySQL 是否支持 profile 操作：
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES|
+------------------+
profile 是默认关闭的，查看 profile 是否开启：
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|0 | #0表示未开启
+-------------+
通过 set 语句在 session / global 级别开启 profiling：
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|1 | #1表示开启
+-------------+
此时执行一系列的业务SQL操作：
select count(*) from student;
select * from student;
select * from student where name = '张三';
通过以下命令查看指令的执行耗时：
#查看每一条SQL的耗时基本情况
show profiles;
#查看指定 query_id 的SQL语句各个阶段的耗时情况
show profile for query query_id;
#查看指定 query_id 的SQL语句CPU的使用情况
show profile cpu for query query_id;
#查看每一条SQL的耗时基本情况
mysql> show profiles;
+----------+------------+-------------------------------------------+
| Query_ID | Duration| Query|
+----------+------------+-------------------------------------------+
|1 | 0.00012400 | select @@profiling|
|2 | 0.00010975 | select * from student|
|3 | 0.00017200 | SELECT DATABASE()|
|4 | 0.00020550 | select count(*) from student|
|5 | 0.09767125 | select * from student|
|6 | 0.00056350 | select * from student where name = '张三'|
+----------+------------+-------------------------------------------+#查看指定 query_id 的SQL语句各个阶段的耗时情况
mysql> show profile for query 4;
+--------------------------------+----------+
| Status| Duration |
+--------------------------------+----------+
| starting| 0.000042 |
| Executing hook on transaction| 0.000005 |
| starting| 0.000006 |
| checking permissions| 0.000004 |
| Opening tables| 0.000234 |
| init| 0.000004 |
| System lock| 0.000006 |
| optimizing| 0.000003 |
| statistics| 0.000007 |
| preparing| 0.000011 |
| executing| 0.097268 |
| end| 0.000010 |
| query end| 0.000003 |
| waiting for handler commit| 0.000009 |
| closing tables| 0.000007 |
| freeing items| 0.000047 |
| cleaning up| 0.000008 |
+--------------------------------+----------+
5.4、explain explain 或 desc 命令获取 MySQL 如何执行 select 语句的信息，包括在 select 语句执行过程中表如何连接和连接的顺序。
[explain | desc] select * from 表名 [where 过滤条件];
mysql> desc select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table| partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|1 | SIMPLE| student | NULL| ALL| NULL| NULL | NULL| NULL |3 |100.00 | NULL|
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
explain 执行计划中各个字段的含义：
| 字段 | 含义 |
|---|---|
| id | 查询序列号，表示查询中执行 select 子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行） |
| select_type | 查询类型，常见的取值有：simple（简单表，即不使用表连接或者子查询）、primary（主查询，即外层的查询）、union（union 中的第二个或者后面的查询语句）、subquery（select / where 之后包含子查询）等 |
| table | 表名 |
| partitions | 涉及到的分区 |
| type | 连接类型，性能由好到差的顺序为：null、system、const、eq_ref、ref、range、 index、all |
| possible_keys | 可能应用在这张表上的索引，一个或多个 |
| key | 实际使用的索引，如果为 null，则没有使用索引 |
| key_len | 索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 |
| ref | 引用到的上一个表的列 |
| rows | MySQL 认为必须要执行查询的行数，在 InnoDB 引擎的表中，是一个估计值，可能并不总是准确的 |
| filtered | 返回结果的行数占需读取行数的百分比， 值越大越好 |
| Extra | 额外的信息说明 |
联合索引：一个索引包含了多个列。
在 and 连接的两个字段上都有单列索引，但 mysql 最终只会选择一个索引，只能走一个字段的索引，此时容易造成回表查询的。
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引。
6.2、最左前缀 最左前缀法则：
- 索引列全部存在，索引生效；
- 索引列部分存在：
- 不存在最左列索引，索引失效；
- 存在最左列索引：
- 最左列索引在第一个条件，后续条件按索引列顺序编写，该部分索引全部生效；
- 最左列索引不在第一个条件，只有最左列索引生效。
6.4、索引失效
- 索引列上进行运算操作；
- 字符串类型字段使用时不加引号；
- 头部模糊匹配；
- or 前的条件有索引，or 后的条件没有索引；
- MySQL 评估使用索引比全表更慢（数据量大于等于表中一半）。
假如在某些场景下，我想告诉数据库，如果 A 字段有多个索引，则需要按照我指定的索引来进行查询，此时需要 SQL 提示。
SQL 提示是优化数据库的一个重要手段，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。
use index：建议 MySQL 使用哪一个索引完成此次查询，MySQL 内部还会再次进行评估。
ignore index：忽略指定索引。
force index：强制使用索引。
语法：
select * from 表名 [ use | ignore | force index(索引名) where 过滤条件];
6.6、覆盖索引 尽量使用覆盖索引（查询使用了索引，且需要返回的列在该索引中能全部找到），减少 select * 。
| Extra | 含义 |
|---|---|
| Using where; Using Index | 查询使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据 |
| Using index condition | 查找使用了索引，但是需要回表查询数据 |
| Null | 需要回表查询数据 |
语法：
create index 索引名 on 表名(字段名(n));
#n：前缀长度
前缀长度：
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值。
索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
#建立前缀索引，降低前缀索引的体积，指定前 n 个字符
select count(distinct substring(字段名, 1, n)) / count(*) from 表名;
#值=1最好
7、索引设计原则
- 针对于数据量较大，且查询比较频繁的表建立索引；
- 针对于常作为查询条件（where）、排序（order by）、分组（group by）之后的字段建立索引；
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高；
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引；
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率；
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率；
- 如果索引列不能存储 null 值，在建表时使用 not null 约束它。当优化器知道每列是否包含 null 值时，它可以更好的确定哪个索引最有效地用于查询。
