MySQL索引原理

Lou.Chen2022年11月4日

MySQL

MySQL

大约 36 分钟

MySQL存储引擎

查看支持的引擎

Show engines

默认为InnoDB，并且支持事务，行锁，外键

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

查看MySQL版本：select version()

MyISAM与InnoDB的区别

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁	表锁和行锁
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

各种JOIN连接

A表：hr表

B表：department表

左连接

左表包含公共部分

#左连接
# A左连接B,显示A的所有部分,A没有的显示为null
SELECT * from hr LEFT JOIN department on hr.depId=department.depId

左表不包含公共部分

# A左连接B, 只显示A部分，不显示公共部分。
SELECT * from hr LEFT JOIN department on hr.depId=department.depId where department.depId is null

右连接

右表包含公共部分

#右连接
# A右连接B,显示B的所有部分
SELECT * from hr right JOIN department on hr.depId=department.depId

右表不包含公共部分

# A右连接B, 只显示B部分，不显示公共部分。
SELECT * from hr right JOIN department on hr.depId=department.depId where hr.depId is null;

内连接(等值连接)

只包含公共部分

#内连接
SELECT * from hr INNER JOIN department on hr.depId=department.depId 
#等值连接 和 内连接一致
SELECT * from hr,department WHERE hr.depId=department.depId

左表UNION右表不包含公共部分

union会自动去重

#查询 A的独有 和 B的独有，不包含AB的共有
SELECT * from hr left JOIN department on hr.depId=department.depId where department.depId is null
union 
SELECT * from hr right JOIN department on hr.depId=department.depId where hr.depId is null

左表UNION右表包含公共部分

union会自动去重

#全连接
#包含A的所有,B的所有
SELECT * from hr left JOIN department on hr.depId=department.depId 
union 
SELECT * from hr right JOIN department on hr.depId=department.depId

索引的定义

索引是一种用于快速查询和检索数据的数据结构，简单的理解为：排好序的快速查找数据结构

索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

索引的优缺点

优点
- 使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
缺点
- 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
- 索引需要使用物理文件存储，也会耗费一定空间。

使用索引查询一定比没使用索引速度快吗？

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

索引的创建/更新/删除

创建表时添加索引：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_age` (`age`),
  UNIQUE KEY `idx_user_age1` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8;

添加索引：

添加普通索引：
- create index 索引名 on 表名 (字段名)
- alter table 表名 add index 索引名 (字段名)
添加主键索引：
- alter table 表名 add primary key (列名)
添加全文索引：
- alter table 表名 add fulltext (列名)
添加唯一索引：
- alter table 表名 add unique (列名) 默认以列名作为索引名
- alter table 表名 add unique 索引名 (列名)
添加多列(复合/组合/联合)索引:
- ALTER TABLE 表名 ADD INDEX 索引名 ( 列1, 列2, 列3 )

删除索引：

alter table 表名 drop index 索引名
drop index 索引名 on 表名

查看表的所有索引：

show index from 表名

索引的数据结构

Hash表

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。

为何能够通过 key 快速取出 value呢？ 原因在于 哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。

缺点：

哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 。解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中
Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点： 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。

例如：SELECT * FROM tb1 WHERE id < 500;

在这种范围查询中，优势非常大，直接遍历比 500 小的叶子节点就够了。而 Hash 索引是根据 hash 算法来定位的，难不成还要把 1 - 499 的数据，每个都进行一次 hash 计算来定位吗?这就是 Hash 最大的缺点了。

B树 & B+树

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树& B+树区别

B树：

B 树的所有节点既存放键(key) 也存放数据(data)
B 树的叶子节点都是独立的
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了

B+树：

B+树只有叶子节点存放 key 和 data，其他节点只存放 key。
B+树的叶子节点有一条引用链指向与它相邻的叶子节点
B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显

在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构

MyISAM与InnoDB实现B+Tree

MyISAM实现B+Tree(非聚集索引)

MyISAM 引擎中，采用的B+Tree使用的是非聚集索引，即索引文件和数据文件分开的。

主键索引和二级(辅助)索引的B+Tree 叶节点 data 域存放的都是数据记录的地址指针

在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引:

如果查找的主键索引，则找到主键的叶子结点值，存储的为数据的文件地址，然后根据数据地址找到数据记录。
如果查找的为二级索引，若为覆盖索引，则可以直接取出key值，不用根据叶子结点存储的数据指针去查找记录。如果为非覆盖索引，则得到叶子存储的数据记录地址后会根据此地址得到记录

InnoDB实现B+Tree(聚集索引)

MyISAM 引擎中，采用的B+Tree使用的是聚集索引，即索引文件和数据文件在一起：

主键索引B+Tree叶结点的data域存放的是数据的完整记录
二级索引B+Tree叶结点的data域存放的是主键值

在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引:

如果查找的为二级索引，若为覆盖索引，则可以直接取出key值，不用回表。如果为非覆盖索引，则需要先找到叶子结点存储的主键key,再根据主键key回表重新查询，然后根据主键id找到叶子结点存储的记录。
如果查找的主键索引，则找到主键的叶子结点值，叶子结点即为存储的记录

索引类型

主键索引(Primary Key)

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引(辅助索引)

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是「主键」。也就是说，通过二级索引，可以定位主键的位置。

MyISAM的二级索引叶子结点存储的为数据指针地址
InnoDB的二级索引叶子结点存储的的为主键值

唯一索引，普通索引，前缀索引等索引属于二级索引。

唯一索引(Unique Key) ：
- 唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许多列数据为 NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
普通索引：
- 普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
前缀索引(Prefix) ：
- 前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。
全文索引(Full Text)
- 全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6之前只有MYISAM引擎支持全文索引，5.6之后InnoDB也支持了全文索引。

聚簇索引和非聚簇索引

聚簇索引

聚簇索引(聚集索引)即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

优点：

查询速度非常快 ：聚簇索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引，聚簇索引少了一次读取数据的 IO 操作。
对排序查找和范围查找优化 ：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点:

依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
更新代价大 ：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚簇索引

非聚簇索引(非聚集索引)即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。

MyISAM的二级索引叶子结点存储的为数据指针地址
InnoDB的二级索引叶子结点存储的的为主键值

MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引，所以在MyISAM引擎表的.MYD文件为表的数据，.MYI文件为表的索引

非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

优点：

更新代价比聚簇索引要小。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的

缺点：

依赖于有序的数据 ：跟聚簇索引一样，非聚簇索引也依赖于有序的数据
可能会二次查询(回表) ：这应该是非聚簇索引最大的缺点了。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

非聚簇索引一定回表查询吗

非聚簇索引不一定回表查询。

试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

 SELECT name FROM table WHERE name='zhangsan';

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

即使是 MYISAM 也是这样，虽然 MYISAM 的主键索引确实需要回表，因为它的主键索引的叶子节点存放的是指针。但是！如果 SQL 查的就是主键呢?

SELECT id FROM table WHERE id=1;

主键索引本身的 key 就是主键，查到返回就行了。这种情况就称之为覆盖索引了。

覆盖索引和联合索引

覆盖索引

如果一个索引包含（或者说覆盖）查询的字段，即查询的字段属于建立的复合索引的子集或本身，我们就称之为「覆盖索引」。

在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要「回表」，也就是要通过主键再查找一次。这样就会比较慢，覆盖索引就是把要查询出的列和索引是对应的，不做回表操作，也就是查询的值即为key

覆盖索引无需回表

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。
再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引，那么直接根据这个索引就可以查到数据，也无需回表。

联合索引

使用表中的多个字段创建索引，就是联合索引，也叫「组合索引」或「复合索引」。

最左前缀匹配原则

若使用类组合索引，那么在进行查询时，就要尽量遵循最左前缀匹配原则：即查询从联合索引的最左前列开始并且不跳过索引中的列，否则将不会使用索引或者只能使用到部分索引。

索引下推

索引下推（Index Condition Pushdown）是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

索引创建建议

哪些字段适合建立索引

不为 NULL 的字段：如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
被频繁查询的字段
被作为条件查询的字段
频繁需要排序的字段
被经常频繁用于连接的字段
查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
查询中统计或者分组字段

哪些字段不适合建立索引

更新频繁的字段不适合索引
表记录太少
经常增删改的字段
数据重复较多的字段

尽可能建立联合索引而不是单列索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

避免建立冗余索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

删除长期未使用的索引

使用EXPLAIN执行计划索引分析

EXPLANIN SELECT ... ;

使用执行计划进行SQL索引分析的时候，主要有以下字段：

id「查询的序列号」

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况：

id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
先执行优先级高即序号最大的id,若id相同，则从上到下依次执行

select_typep「查询类型」

查询的类型，主要用于区别：普通查询、联合查询、子查询等的复杂查询

SIMPLE
- 简单的select查询，查询中不包含子查询或者UNION
PRIMARY
- 查询中若包含任何复杂的子部分，最外层查询则被标记为primary
SUBQUERY
- 在SELECT或者WHERE列表中包含了子查询
DERIVED
- 在FROM列表中包含的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询，把结果放在临时表里。
UNION
- 若第二个SELECT出现在UNION之后，则被标记为UNION; 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT
从UNION表获取结果的SELECT

table「哪张表」

显示这一行的数据是关于哪张表的

type「访问类型」

显示查询使用了何种类型从最好到最差依次是： system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询只是达到range级别，最好达到ref

system
表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计
const
- 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键至于where列表中，MySQL就能将该查询转换为一个常量
eq_ref
- 唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。相当于在表中通过主键索引或唯一索引只找到一条数据
ref
- 非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
- 通过非唯一索引扫描找到多条数据
range
只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束语另一点，不用扫描全部索引
index
- Full Index Scan/index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的
all
- FullTable Scan,将遍历全表以找到匹配的行 (效率最差)

possible_keys「可能用到的索引」

显示可能应用在这张表中的索引，一个或多个。查询涉及的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key「真正用到的索引」

实际使用的索引。如果为null则没有使用索引
查询中若使用了覆盖索引，则索引和查询的select字段重叠

key_len「索引中使用的字节数」

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref「引用的索引列」

显示索引那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值

rows「读取到的行」

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
行数越小越好

Extra「其他额外信息」

包含不适合在其他列中显示但十分重要的额外信息：

Using filesort
- 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作成为“文件排序”（需要优化）
Using temporary
- 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by (需要优化)
Using index
- 表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！ 如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表面索引用来读取数据而非执行查找动作。
Using where
- 表面使用了where过滤
using join buffer
- 使用了连接缓存
impossible where
- where子句的值总是false，不能用来获取任何元组
select tables optimized away
- 在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct
- 优化distinct，在找到第一匹配的元组后即停止找同样值的工作

JOIN表的联合优化

单表

范围索引导致索引失效。即查询的时候如果查询的范围字段建立了索引，那么会索引失效。应该避开范围字段索引的建立

测试表:

drop table if EXISTS article;
CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');
SELECT * FROM ARTICLE;
show index from article;

# type:全表扫描, extra:using filesort
explain select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;

#建立 category_id和comments和views的联合索引
create index idx_article_ccv on article(category_id,comments,views);
#再次查看执行计划  type:range, extra:using filesort
explain select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;

#删除原来的索引
drop index idx_article_ccv on article;
#建立 category_id和views的联合索引. 即不对范围的字段建立索引
alter table article add index idx_article_cv(category_id,views);

#再次查看执行计划  type:ref, extra:Using where
explain select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;

两表

得出结论，我们使用左连接时，即以左表为主表时，需建立右表索引。得出结论，我们使用右连接时，即以右表为主表时，需建立左表索引。所以当以哪个表作为主表是，可以建立另一个表的索引。

测试表

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));

# 执行查询计划
explain SELECT * from book left JOIN class on book.card=class.card;
#建立左表的索引
alter table book add index b(card);
# row:总共为40; 左表使用了索引，右表为全表扫描
explain SELECT * from book left JOIN class on book.card=class.card;

#删除左表的索引
drop index b on book;
#建立右表的所索引
create index c on class(card);
#再次执行查询计划，左表进行全表扫描，右表type:ref; 右表使用了索引， rows：总共为21
explain SELECT * from book left JOIN class on book.card=class.card;

三表

无论以哪个表作为主表，另一个表都要建立索引

# 在上面两表的基础上增加一个表
CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

#执行查询计划。三张表都没有使用索引，type都是all全表扫描 rows：总共为 60
EXPLAIN SELECT * from class left JOIN book on book.card=class.card left JOIN phone on book.card=phone.card;
#为leftjon的第二和第三张表建立索引
create index b on book(card);
alter table phone add index p(card);
#再次执行查询计划，发现 第二和第三张表使用了索引 rows：总共为20+1+1=22
EXPLAIN SELECT * from class left JOIN book on book.card=class.card left JOIN phone on book.card=phone.card;

#当我们join联立多张表时，建立在其它表的索引即可。

总结

尽可能减少join语句中的nestedloop的循环总次数：永远用小结果集驱动大结果集
保证join语句中被驱动表上join条件字段已经被索引

索引的失效及优化

测试表：

CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('ls',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

JOIN时索引优化

无论以哪种表作为主表时，另一张表的索引字段必须建立索引
尽可能减少join语句中的nestedloop的循环总次数：永远用小结果集驱动大结果集

最佳左前缀法则

如果使用类组合索引，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

order by的字段和group by也要遵守最左前缀法则(必须为从左到右的顺序，否则索引order by/group by后面的索引字段失效)，若前面的索引失效，则需要在order by和group by的后建立起完整的左前缀规范
必须使用到联合索引的第一个索引，索引才有效。如果只使用第一二四个索引，那么中间缺失的索引和后面的索引列会失效，此时只会用到第一二个索引。

#建立 name和age和pos的联合索引
alter table staffs add index idx_nameAgePos(name,age,pos);

# 索引失效的情况
# 用到联合索引的第二和第三个，没有用到第一个 type:all全表扫描 
EXPLAIN SELECT * from staffs where age=22 and pos='manager'; 
# 只用到联合索引的第二个 或者  第三个 type:all全表扫描 
EXPLAIN SELECT * from staffs where pos='manager'; 

# 索引有效的情况
# 联合索引的字段全部用到 type:ref   key:idx_nameAgePos
EXPLAIN SELECT * from staffs where pos='manager' and age=22 and name='ls' 
# 只用到联合索引的第一个和第三个，第二个字段缺失。那么只会使用到第一个索引。
EXPLAIN SELECT * from staffs where name='ls' and pos='manager';
# 只用到联合索引的第一个和第二个，那么只会使用到第一个和第二个索引。
EXPLAIN SELECT * from staffs where name='ls' and age=22;
# 只用到联合索引的第一个，那么只会使用到第一个索引。
EXPLAIN SELECT * from staffs where name='ls';

不在条件索引列上做(计算/函/自动/手动类型转换)

会导致索引失效而转向全表扫描

不使用索引范围条件右边的列

即使用的范围自字段的索引，那么范围字段后面的字段索引将会失效

# key_len=74
EXPLAIN SELECT * from staffs where name='ls';
# key_len=78   type=range
EXPLAIN SELECT * from staffs where name='ls' and age>22 
# key_len=78   type=range  即范围索引后面的字段索引失效，即只使用到了name和age索引  
EXPLAIN SELECT * from staffs where name='ls' and age>22 and pos='manager'

尽量使用覆盖索引/全索引匹配

尽量使用覆盖索引，减少select *
若where使用到索引并且遵循最左前缀法则，则无论查询字段是否满足索引都会走索引
若where使用到索引并且没有最左前缀法则，若查询字段使用到索引则会走索引，若查询字段没用到索引则全表扫描。
若where没用到索引，则无论查询字段是什么都会全表扫描。

# extra:Using index
EXPLAIN SELECT name,age,pos from staffs where name='ls' and age>22
# extra:Using index condition 
EXPLAIN SELECT * from staffs where name='ls' and age>22 
#结论：在select查询后面，使用索引的查询列比使用*多了 Using index，即效果会更好。

使用不等于导致全表扫描

使用！=或者<> 导致全表扫描，但是根据具体业务情况分析，该得用的时候还是得用

# type:all 全表扫描
EXPLAIN SELECT * from staffs where name!='ls'
# type:all 全表扫描
EXPLAIN SELECT * from staffs where name<>'ls'

is null/is not null导致全表扫描

# type:null 索引失效
EXPLAIN SELECT * from staffs where name is null
# type:all 全表扫描
EXPLAIN SELECT * from staffs where name is not null

like以通配符开头导致全表扫描

模糊匹配：like '%abc' 或者 like '%abc%' 会导致索引失效，导致全表扫描

like KK%相当于常量,而 %KK和%KK% 相当于范围

即只要like后面的字段不以%开头，即算是定值，都能后续索引都有效
若以%开头，那么当前%的字段索引和后续索引即全部失效，相当于范围字符

解决办法：

like匹配的 % 写右边即可避免全表扫描即 like 'a%'

问题：解决like '%字符串%' 索引不被使用的方法？则需要满足查询字段满足以下条件：

使用主键索引
使用覆盖索引，查询字段必须是建立覆盖索引字段 ,即查询的字段属于建立的复合索引的子集或本身。否则使用 select * 或者查询到索引外的字段那么会全表扫描
当覆盖索引指向的字段是varchar(380)及380以上的字段时，覆盖索引会失效！

CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');


# 创建 name和age的联合索引
alter table tbl_user add index idx_nameAge(name,age);

#extra:Using where; Using index 使用到了索引
#id使用了主键索引。name,age使用的联合索引
explain SELECT id,name,age from tbl_user where name like '%a%';
#extra:Using where; Using index 使用到了索引
#name使用了覆盖索引。即select的字段是索引字段
explain SELECT name from tbl_user where name like '%a%';

#type:all全表扫描。索引失效
#email没有建立索引，导致索引失效
explain SELECT id,name,age,email from tbl_user where name like '%a%';
explain SELECT * from tbl_user where name like '%a%';

字符串不加单引号索引失效

底层会自动进行类型转换

#type:all全表扫描，extra:Using where
# 索引失效
EXPLAIN SELECT * from staffs where name=2000;

or导致全表扫描

查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;

#type:all全表扫描，extra:Using where
# 索引失效
EXPLAIN SELECT name from staffs where name='july' or email='2000'

索引重排

联合索引：index(c1,c2,c3,c4);

全匹配的索引字段与顺序无关，MYSQL底层会自动帮我们进行重排

底层自动重排序，与顺序无关

#索引全部有效
EXPLAIN SELECT * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3'

范围之后全失效，范围索引也会重排序

底层MYSQL对索引重排序后顺序为 c1,c2,c3,c4 虽然从c4范围索引后失效，但是c4重排序后面并无其他索引。所以索引全部有效

#索引全部有效
EXPLAIN SELECT * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3'

小表驱动大表

小表驱动大表，即小的数据集驱动大的数据集.

注意：a表与b表的id字段应建立索引

当b表的数据集小于a表的数据集时，用in优于exists

select * from a where id in(select id from b)

等价于==>

for select id from b
	for select * from a where a.id=b.id

当a的数据集小于b表的数据集时，用exists优于in

select * from a where exists(select 1 from b where a.id=b.id)

等价于==>

for select id from a
    for select * from b where a.id=b.id

exists理解
- select ... from table where exists(subquery)
- 将主查询的数据，放到子查询中做条件验证，根据验证结果（true或false）来决定主查询的数据结果是否得以保留

order by/group by排序使用索引

Mysql两种排序方式：文件排序(using filesort需要优化)或有序索引排序

Mysql能为排序与查询使用相同的索引

建立索引 index(a,b,c)

order by 能使用索引最左前缀法则。没有where的情况，select后的字段满足覆盖索引
- order by a
- order by a,b
- order by a,b,c
- order by a desc ,b.desc，c.desc 同为升序或降序
如果where使用索引的最左前缀定义为常量，则oder by能使用索引，可以使用 select *
- where a =const order by b,c
- where a =const and b=const order by c
- where a =const order by b,c
不能使用索引进行排序的情况
- order by a asc,b desc, c desc #排序不一致
- where g=const order by b,c #不满足最左前缀法则，丢失a索引
- where a=const order by c #丢失b索引
- where a=const order by a,d #d不是索引的一部分
- select * from 表名 order a,b,c #select后的字段必须满足覆盖索引的情况，否则filesort
- where a in(...) order by b,c #a也相当于范围索引，即a后面的索引失效，即order by缺失a索引

建立索引 index(c1,c2,c3,c4)

order by必须按照顺序索引排列，当where中的索引字段在order by中时，order by中的索引字段可忽略。当where中的范围索引失效时，需要在order by中重新补上索引来保证其顺序

#因为c2是常量，所以我们在order by中的c2无效，即order by被优化为c3，所以我们使用的索引为 c1,c2,c3 (其中c1,c2用于查找，c3用于排序)
EXPLAIN SELECT * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2

#因为我们只用到了c1,在order by中必须按照索引顺序，所有这里我们只用到c1索引。extra: Using filesort 产生文件排序
EXPLAIN SELECT * from test03 where c1='a1' and c5='a5' order by c3,c2

#where中c1索引有效，order by按照顺序索引，则c1,c2,c3都有效，（其中c1用于查找，c2,c3用于排序）
EXPLAIN SELECT * from test03 where c1='a1' and c5='a5' order by c2,c3

#where中的c2后范围索引失效，在roder by 中需要重新补上索引c2
#EXPLAIN SELECT * from test03 where c1='a1'  and c2>'a2' and c5='a5' order by c2,c3

group 也必须按照顺序索引排序，和order by的用法一致

# c1,c2,c3索引有效（其中c1用于查找，c2,c3用于分组）
EXPLAIN SELECT * from test03 where c1='a1'  and c4='a4' GROUP BY c2,c3
#因为我们只用到了c1,在group by中必须按照索引顺序，所有这里我们只用到c1索引。extra:  Using temporary; 产生临时表  Using filesort 产生文件排序
EXPLAIN SELECT * from test03 where c1='a1' and c4='a4' GROUP BY  c3,c2
#因为c1是常量，所以我们在group by中的c2无效，即group by被优化为c3，所以我们使用的索引为 c1,c2,c3 (其中c1,c2用于查找，c3用于分组)
EXPLAIN SELECT * from test03 where c1='a1' and c2='a2' and c4='a4' GROUP BY  c3,c2

where和 order by/group by中的索引字段相互独立，只需保证where中的有效索引字段和order by/group by中补的索引字段有序即可

慢查询日志SQL

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为 10，意思是运行10秒以上的语句。
由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

开启慢查询日志

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

临时开启

SQL 语句	描述	备注
SHOW VARIABLES LIKE '%slow_query_log%';	查看慢查询日志是否开启	默认情况下 slow_query_log 的值为 OFF，表示慢查询日志是禁用的
set global slow_query_log=1;	开启慢查询日志
SHOW VARIABLES LIKE 'long_query_time%';	查看慢查询设定阈值	默认为10s
set long_query_time=1	设定慢查询阈值	单位秒

永久开启

修改配置文件my.ini

[mysqld] 
slow_query_log=1 
slow_query_log_file=/var/lib/mysql/atguigu-slow.log 
long_query_time=3 
log_output=FILE

运行查询时间长的 sql，打开慢查询日志查看

测试

我们设置3s为日志慢查询sql

set long_query_time=3

#使用sleep测试超过3s的sql的执行
select sleep(4);
select sleep(5);

查看慢查询sql日志文件

D:\mysql-5.7.24-winx64\data\LAPTOP-LJJM9R29-slow.log

MySQL, Version: 5.7.24 (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 2020-07-31T10:01:19.381079Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 4.000882  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use index_test;
SET timestamp=1596189679;
select sleep(4);
# Time: 2020-07-31T10:01:54.843723Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 5.000658  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1596189714;
select sleep(5);

使用日志分析工具

查看mysqldumpslow的帮助信息

常用命令

#得到返回记录集最多的 10 个 SQL 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

#得到访问次数最多的 10 个 SQL 
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#得到按照时间排序的前 10 条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "leftjoin" /var/lib/mysql/atguigu-slow.log

#另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log|more

查看SQL执行周期

利用 showprofile 可以查看 sql 的执行周期！

开启profile

查看 profile 是否开启show variables like'%profiling%';

如果没有开启，可以执行 set profiling=1 开启

使用profile

必须先开启set profiling=1，否则以下命令无效

执行 show profiles 命令，可以查看最近的几次查询。

根据 Query_ID,可以进一步执行 show profile cpu,block io for query 「Query_id」 来查看 sql 的具体执行步骤。

show profile cpu,block io for query 115

MySQL索引原理

# MySQL存储引擎

# 查看支持的引擎

# MyISAM与InnoDB的区别

# 各种JOIN连接

# 左连接

# 左表包含公共部分

# 左表不包含公共部分

# 右连接

# 右表包含公共部分

# 右表不包含公共部分

# 内连接(等值连接)

# 左表UNION右表不包含公共部分

# 左表UNION右表包含公共部分

# 索引的定义

# 索引的优缺点

# 索引的创建/更新/删除

# 索引的数据结构

# Hash表

# B树 & B+树

# B 树& B+树区别

# MyISAM与InnoDB实现B+Tree

# MyISAM实现B+Tree(非聚集索引)

# InnoDB实现B+Tree(聚集索引)

# 索引类型

# 主键索引(Primary Key)

# 二级索引(辅助索引)

# 聚簇索引和非聚簇索引

# 聚簇索引

# 非聚簇索引

# 非聚簇索引一定回表查询吗

# 覆盖索引和联合索引

# 覆盖索引

# 覆盖索引无需回表

# 联合索引

# 最左前缀匹配原则

# 索引下推

# 索引创建建议

# 哪些字段适合建立索引

# 哪些字段不适合建立索引

# 尽可能建立联合索引而不是单列索引

# 避免建立冗余索引

# 删除长期未使用的索引

# 使用EXPLAIN执行计划索引分析

# id「查询的序列号」

# select_typep「查询类型」

# table「哪张表」

# type「访问类型」

# possible_keys「可能用到的索引」

# key「真正用到的索引」

# key_len「索引中使用的字节数」

# ref「引用的索引列」

# rows「读取到的行」

# Extra「其他额外信息」

# JOIN表的联合优化

# 总结

# 索引的失效及优化

# JOIN时索引优化

# 最佳左前缀法则

# 不在条件索引列上做(计算/函/自动/手动类型转换)

# 不使用索引范围条件右边的列

# 尽量使用覆盖索引/全索引匹配

# 使用不等于导致全表扫描

# is null/is not null导致全表扫描

# like以通配符开头导致全表扫描

# 字符串不加单引号索引失效

# or导致全表扫描

# 索引重排

# 小表驱动大表

# order by/group by排序使用索引

# 慢查询日志SQL

# 开启慢查询日志

# 临时开启

# 永久开启

# 测试

# 使用日志分析工具

# 查看SQL执行周期

# 开启profile

# 使用profile