1. 概述
索引（index）是帮助MySQL高效获取数据的数据结构(有序) 。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
1.1. 特点
优点：
- 提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
缺点：
- 索引是一种数据结构，占用空间的，增加维护成本。
- 降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。
1.2. 语法
//创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name
ON table_name ( index_col_name,... ) ;
//查看索引
SHOW INDEX FROM table_name ;
//删除索引
DROP INDEX index_name ON table_name ;
//示例
create index index_dish_name on dish(name);
//创建联合索引
create index index_dish_name_price on dish(name,price);
show index from dish;
drop index index_dish_name_price on dish;
2. 结构
2.1. 概述
MySQL的索引是在存储引擎层实现的，常见的索引结构主要包含以下几种，重点关注B+Tree。
| 索引结构 | 描述 |
|---|---|
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持 B+ 树索引 |
| Hash索引 | 底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询 |
| R-tree(空间索引） | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES |
不同的存储引擎对于索引结构的支持情况。
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+tree索引 | Y | Y | Y |
| Hash 索引 | N | N | Y |
| R-tree 索引 | N | Y | N |
| Full-text | Y | Y | N |
（其中Full-text在5.6版本之后，InnoDB才支持）
2.2. B-Tree
B-Tree，B树是一种多叉路衡查找树，多叉即每个节点有多个分支，每个节点储存索引和数据。其中五阶树最多有四个key，五个指针。
- 5阶的B树，每一个节点最多存储4个key，对应5个指针。
- 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
- 在B树中，非叶子节点和叶子节点都会存放数据。
（B-Tree演示网站：B-Tree Visualization）
2.3. B+Tree
B+Tree是B-Tree的变种
- 所有的数据都会出现在叶子节点。
- 叶子节点形成一个单向链表。
- 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。
2.4. MySQL的B+Tree
MySQL的默认引擎为InnoDB，结构为优化的B+Tree。如何优化呢？在每个叶子节点又增加了一个指向相邻叶子节点的指针，形成了双向循环列表
2.4.1. 为什么MySQL要选择B+Tree作为索引结构？
- 相对于二叉树，层级更少，搜索效率高；
- 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
- 相对Hash索引，B+tree支持范围匹配及排序操作；
2.4.2. B+Tree能保存多少数据呢？
假设：
一行数据大小为1k,一页中可以存储16行这样的数据。
InnoDB的指针占用6个字节的空间，主键即使为bigint,占用字节数为8。
当高度为2：
n*8+(n+1)*6=16*1024,算出n约为1170
1171*16=18736
当高度为3：
1171*1171*16=21939856
高度为2，可以理解为2页，每页字节大小为16*1024；指针页通过公式计算，可以算出最大可以存储约1170个key，即1171指针；数据页存储行数据，可以存储16行。
当索引高度为2时，存储的行数据为18000多行数据；当索引高度为3时，存储的行数据接近2200w行数据；
2.5. Hash
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。
一些局限性：
- 只能用于对等比较，不支持范围查询
- 无法利用索引完成排序操作
3. 分类
3.1. MySQL索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建, 只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
主要关注就是主键索引，一般情况下唯一的，自增的，非空的。
3.2. InnoDB索引分类
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚簇索引(Clustered Index) | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
| 二级索引(Secondary Index) | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚簇索引选取规则:
- 如果存在主键，主键索引就是聚簇索引。
- 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚簇索引。
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚簇索引。
3.3. 其他索引概念
聚簇索引也叫聚集索引；二级索引又叫非 聚簇索引，辅助索引。
回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。
覆盖索引： 当查询二级索引返回的值已满足需求，不需回表查询（覆盖了聚集索引）。
联合索引： 一个索引包含多个列。
前缀索引： 当索引类型为字符串时，可取字符串的一部分前缀建立索引，查询效果基本一致，也达到节约索引空间，提高查询效率。
4. 索引建立原则
- 针对经常需要查询（status），数据量较大，查询慢（慢查询日志）的表建立索引。
- 针对于常作为查询条件（where），分组（group by），排序（order by）的操作字段建立索引。
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
- 尽量选择非空列作为索引，在创建表时采取NOT NULL约束。
- 尽量使用联合索引，查询时，联合索引很多时候可以覆盖索引，避免回表，提高查询效率。
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
5. SQL性能分析
5.1. 访问频次status
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：
-- session 是查看当前会话 ;
-- global 是查询全局数据 ; Com后面是七个下划线
SHOW GLOBAL STATUS LIKE 'Com_______';
5.2. 慢查询日志slow_query_log
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。MySQL的慢查询日志默认没有开启。
//查询慢日志的值
show variables like '%slow%';
show variables like 'slow_query_log';
//开启慢日志
set global slow_query_log = on;
//设置慢日志阈值时间
set global long_query_time = 2;
//慢日志地址
D:\Software\MySQL\mysql-8.0.31-winx64\data\xxx-slow.log
//进入容器内部
docker exec -it mysql bash
//登录用户
mysql -uroot -proot
//执行查看慢日志
show variables like 'slow_query_log';
//查看慢日志中信息
cd /root/mysql/data
cat xxx-slow.log
//或找到mysql的数据卷挂载点，用系统编辑器打开
docker volume inspect mysql
5.3. 资源使用profiling
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
//查看系统变量@@have_profiling，它的值取决于 MySQL 是否启用了查询性能分析
select @@have_profiling;
//查看Mysql系统变量@@profiling，它的值用于确定当前会话是否启用了查询性能分析
select @@profiling;
//开启profiling
set profiling = 1;
//查看每一条SQL的耗时基本情况
show profiles;
//查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query 16[query id];
//查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
（数据来源教程截图）
5.4. 执行流程explain
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
一般通过key_len判断SQL语句是否走索引，从而避免索引失效，进行SQL优化
//直接在select语句之前加上关键字 explain / desc
explain select * from dish where id = 10;
Explain 执行计划中各个字段的含义:
| 字段 | 含义 |
|---|---|
| id | select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。 |
| select_type | 表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等 |
| type | 表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。 |
| possible_key | 显示可能应用在这张表上的索引，一个或多个。 |
| key | 实际使用的索引，如果为NULL，则没有使用索引。 |
| key_len | 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。 |
| rows | MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。 |
| filtered | 表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。 |
技术分享 | EXPLAIN 执行计划详解（2）--Extra
6. 索引失效
索引本质上还是一个提高搜索效率的工具，如何正确使用工具？可以从索引失效的场景出发，避免这些场景的出现，从而正确使用索引。索引失效情况分为以下几种：
6.1. 最左前缀法则
最左前缀法则指的是建立联合索引，查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。
通俗说，使用联合索引查询时，三列索引并排，where条件只使用第一列和第三列作为查询条件，那么第三列索引会失效。
举例：建立联合索引是采取三个字段，顺序为name，age，height
查询语句为select * from tb_student where name = "xiaoming" and height = 170 and age = 15;
此时并不会产生索引失效
6.2. 范围查询
联合索引中，出现>或<范围查询，范围查询右侧的列索引失效。
在业务允许的情况下，尽可能的使用 >= 或 <= 这类的范围查询。
6.3. 列运算
对索引列进行函数运算
//索引失效
explain select * from tb_user where substring(phone,10,2) = '15';
6.4. 字符串不加引号
字符串类型字段使用时，不加引号，索引将失效。
//索引有效
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
//索引失效
explain select * from tb_user where profession = '软件工程' and age = 31 and status = 0;
6.5. 模糊查询
模糊查询匹配头部会索引失效，匹配尾部不会。
//索引有效
explain select * from tb_user where profession like '软件%';
//索引失效
explain select * from tb_user where profession like '%工程';
//索引失效
explain select * from tb_user where profession like '%工%';
6.6. or连接条件
用or连接的两个查询字段，需要左右都有索引才会生效。
//索引失效
explain select * from tb_user where id = 10 or age = 23;
//索引失效
explain select * from tb_user where phone = '17799990017' or age = 23;
//由于age没有索引，所以即使id、phone有索引，索引也会失效。
//所以需要针对于age也要建立索引。
6.7. 数据分布影响
MySQL评估使用全表查询比索引快，那么索引失效。
7. SQL优化
7.1. SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
//use index ： 建议MySQL使用哪一个索引完成此次查询
//（仅仅是建议，mysql内部还会再次进行评估）。
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
// ignore index ： 忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
//force index ： 强制使用索引。
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
7.2. 大批量插入数据
如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：
windows/linux
-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -uroot -proot
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user
fields terminated by ',' lines terminated by '\n' ;
docker exec -it mysql bash
-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 查询local_infile是否开启
select @@local_infile;
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 创建数据库
create database xxx;
-- 切换数据库
use xxx;
-- 创建表
CREATE TABLE `tb_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL,
`password` VARCHAR(50) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`birthday` DATE DEFAULT NULL,
`sex` CHAR(1) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
//将load_user_100w_sort.sql文件上传到“/root/mysql/data”下
//注意：/var/lib/mysql目录是mysql容器内部的目录
//和linux上的/root/mysql/data目录相映射关系
load data local infile '/var/lib/mysql/load_user_100w_sort.sql'
into table tb_user fields terminated by ',' lines terminated by '\n' ;
7.3. 主键优化
7.3.1. 概述
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。行数据，都是存储在聚集索引的叶子节点上的。
而我们之前也讲解过InnoDB的逻辑结构图：
在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。
7.3.2. 页分裂
如果一个页快满了,此时我们插入数据,但下一个页的空间也全部占满。基于索引的有序性，这个时候Mysql将创建一个新页，然后将快满的这个页的部分数据迁移到新页中,这部分数据就是超出原来那个页阈值的那部分数据,之后再插入新的数据。
假如主键乱序插入50这条数据，发生页分裂，流程如下：
7.3.3. 页合并
如果页中的数据被删除,那么实际上这块的空间并不会被回收,而是标记为可重复利用。当一个页的数据被删除或者更新,空间小于所规定的阈值大小,那么Mysql会查找前一个页,和后一个页,判断是否可以将这个页合并到另外一个页,这样就可以节省下一个页的空间。
（MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。）
假如陆续删除数据，发生页合并，流程如下：
总结
页分裂和页合并是针对索引结构的优化技术，页分裂保证了索引的有序性，而页合并保证了紧凑性，减少了页数量（磁盘I/O操作）。两者并不是为了直接提高查询效率。然而，通过保持索引的合理组织和大小，这些技术可以间接地影响查询性能。
7.3.4. 主键设计
- 满足业务需求的情况下，尽量降低主键的长度。
- 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
- 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
- 业务操作时，避免对主键的修改。
7.4. order by优化
MySQL的排序，有两种方式：
Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。
7.5. group by优化
在分组操作时，可以通过索引来提高效率。
分组操作时，索引的使用也是满足最左前缀法则的。
7.6. limit优化
在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。所以一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
7.7. count优化
7.7.1. 概述
在之前的测试中，我们发现，如果数据量很大，在执行count操作时，是非常耗时的。
- MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。
- InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。
7.7.2. count用法
count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累计值。
| count用法 | 含义 |
|---|---|
| count(主键) | InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null) |
| count(字段) | 没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。 |
| count(数字) | InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。 |
| count(*) | InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。 |
按照效率排序：
count(字段) < count(主键 id) < count(1) ≈ count(*)
7.8. update优化
其实这个是考虑两个事务之间在修改的时候，是行锁还是表锁的问题。
当在执行update语句的时候
- 条件必须是有索引的字段，这样InnoDB执行的是行锁
- 当条件不是有索引的字段的时候，那么则执行的是表锁，会造成阻塞，性能大大降低。
- InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。
