MySQL索引学习总结

1.什么是索引？

MySQL官方定义：索引是帮助MySQL高效获取数据的数据结构。

即：索引是数据结构！！！

2.索引有哪几种数据结构？

6种。二叉树、平衡二叉树、红黑树、BTree、B+Tree、Hash

旧金山大学数据结构可视化网站： Data Structure Visualization (usfca.edu)

二叉树

• 对半搜索，每个节点最多两个孩子
• 左侧孩子小于根节点，右侧孩子大于等于根节点
• 二叉排序树的查找性能在0(Log2n)到O(n)之间

       

• 最坏情况时，时间复杂度就是O(n)

平衡二叉树

• 满足二叉树
• 任何节点的两个子树的高度最大差为1
• 如果对平衡二叉树进行删除和新增，那么会破坏平衡，就会出发旋转，最终达到平衡，也成自平衡二叉树

红黑树

• 也是自平衡（但没有高度差为1的限制，它有另外一套规则）
• 每个结点是红的或者黑的
• 根结点是黑的
• 每个叶子结点是黑的（NULL），如2号下还有两个黑的叶子节点
• 树中不存在两个相邻的红色结点（即红色结点的父结点和孩子结点均不能是红色）
• 从根结点到其任何后代 NULL 结点（默认是黑色的）的每条路径都具有相同数量的黑色结点。

BTree

• 叶节点具有相同的深度，叶节点的指针为空；
• 所有索引元素不重复；
• 节点中的数据索引从左到右递增排列；
• 数据节点存在每个节点上

B+Tree

• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用双向指针连接，提高区间访问的性能

B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。

上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。

辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据
              
原文链接：https://blog.csdn.net/weixin_44203782/article/details/131202433

Hash

• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候Hash索引要比B+ 树索引更高效
• 仅能满足 “=”，“IN”，不支持范围查询
• hash冲突问题（davi、alice链式结构，链表）

对于自平衡中的左旋、右旋

        左旋：对A节点要左旋，对A节点的右节点作为他的爹节点，将他爹原来的左节点作为A现在的右节点

        右旋：对A节点要右旋，对A节点的左节点作为他的爹节点，将他爹原来的右节点作为A现在的左节点

BTree和B+Tree详细：http://t.csdnimg.cn/P9nb5

3.索引的优势？

• 提高数据检索的效率，降低数据库IO成本
• 通过索引对数据数据进行排序，降低数据排序成本，降低CPU的消耗（排序是A与B做对比，也是属于检索。分组的底层为排序，故也可提高分组的效率）

4.索引的劣势？

• 索引需要占用空间
• 降低表的更新速度，增删改时MySQL不仅要更新数据，还要保存索引文件。调整因为更新带来减值变化的索引的信息

5.索引使用场景

• 主键自动建立唯一索引
• 为频繁查询的字段建立索引（where后面的查询）
• 查询中与其他表关联的字段，外键关系建立索引
• 多字段查询下倾向创建组合索引
• 查询中排序的字段
• 查询中统计或者分组的字段

不建议使用索引场景：

• 表记录少
• 经常增删改
• where条件用不到

6.索引的分类与创建

主键索引

1.建表时定义了主键，数据库随建表自动创建主键索引

2.单独创建和删除语法：

创建：
alter table 表名 add primary key(字段);
删除：
alter table 表名 drop primary key;

唯一索引

1.建表时定义了唯一约束，数据库随建表自动创建唯一索引

2.单独创建和删除语法：

创建:
alter table 表名 add unique 索引名(字段);
或
create unique index 索引名 on 表名(字段);

删除：
drop index 索引名 on 表名;

单值索引

即一个索引只包含单个列，一个表可以有多个单值索引

1.建表时可随表一起创建单值索引

2.单独创建和删除单值索引语法：

创建：
alter table 表名 add index 索引名(字段);
或
create index 索引名 on 表名(字段);

删除：
drop index 索引名 on 表名;

复合索引

即一个索引包含多个列

1.建表时可随表一起创建复合索引

2.单独创建和删除复合索引语法：

创建:
alter table 表名 add index 索引名(字段1,字段2);
或
create index 索引名 on 表名(字段1,字段2);

删除:
drop index 索引名 on 表名;

7.性能分析

SQL中对大量数据进行比较、关联、排序、分组时CPU的瓶颈

Explain关键字

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL的

使用方法：explain + SQL 语句

以下为测试代码数据准备，打开数据库运行：

-- 创建四张测试表
create table t1(
	id int(10) auto_increment,
	content varchar(100),
	primary key (id)
);
create table t2(
	id int(10) auto_increment,
	content varchar(100),
	primary key (id)
);
create table t3(
	id int(10) auto_increment,
	content varchar(100),
	primary key (id)
);
create table t4(
	id int(10) auto_increment,
	content varchar(100),
	primary key (id)
);

-- 每张表插入一条数据
insert into t1(content) values(concat('t1_',FLOOR(1+RAND()*1000)));
insert into t2(content) values(concat('t2_',FLOOR(1+RAND()*1000)));
insert into t3(content) values(concat('t3_',FLOOR(1+RAND()*1000)));
insert into t4(content) values(concat('t4_',FLOOR(1+RAND()*1000)));

Explain重要字段名

1.1  id

select中查询的序列号，表示查询中执行select子句或操作表的顺序

id相同时，执行顺序由上至下

执行以下代码：

explain select * from t1,t2,t3 where t1.id=t2.id and t2.id=t3.id;

结果如下：即执行顺序为 t1、t2、t3

id不同时，如果是子查询，id的序号会递增，id优先级越高，则先被执行

执行代码：

explain select t1.id from t1 where t1.id in(
select t2.id from t2 where t2.id in
(select t3.id from t3 where t3.id = 1)
);

id相同和不同都存在时，id相同的为一组，从上往下，所有组中，id值大的先执行

执行代码：

explain select t2.* from t2,(select * from t3) s3 where s3.id = t2.id;

执行的结果与MySQL的版本有关：

        嵌套查询的执行顺序应该是从内层到外层，但MySQL 的优化器可能将所有的子查询视为同一级别的操作，特别是当查询较简单时，优化器可能不会为每个子查询分配不同的 id。在这种情况下，所有的子查询可能会被视为在同一级别上执行，从而在 EXPLAIN 输出中看到相同的 id 值。

1.2  select_type

为查询的类型，常见的有：

simple:  简单的select查询

explain select * from t1;

derived:  在 from 中包含的子查询被标记为 derived（衍生），MySQL会递归这些子查询，把结果存临时表

primary:  查询中若包含复杂的子部分，最外层查询会被标记为 primary

subquery:  在 select 或 where 列表中包含了子查询

explain select t2.* from t2 where t2.id=(select t3.id from t3);

1.3  type

访问类型排序（表获取数据时是否使用索引、索引的类型等）：

system： 表只有一行记录（类似于系统表）

const： 通过索引一次就找到，用于比较 primary key 或者 unique 索引

-- id 为主键
explain select * from t1 where t1.id = 1;

eq_ref： 唯一性索引扫描，对于每个索引键表中只有一条记录与之匹配

explain select t1.*,t2.*
from t1
join t2 on t1.id = t2.id;

解释：id 相同时从上往下，将 t1 表中的 id 字段全部取出，故为 all ，取出的 id 字段用于 t2 表中的扫描，id 字段在 t2 表中为主键，此时 type 为 eq_ref

ref： 非唯一性索引扫描，返回匹配某个单独值的所有行

-- 为 t1 表中 content 字段添加索引
alter table t1 add index idx_t1_content(content);
explain select * from t1 where t1.content = "abc";

range： 只检索给定范围的行，使用一个索引来选择行

-- id 为主键
explain select * from t2 where t2.id > 0;

index： 对索引文件进行扫描

-- 此时 t1 表中所有字段 id、content 都添加了索引
explain select id,content from t1;

解释：因为两个都为索引字段，在数据不仅会存表中，还会存索引文件中。在查询时优先从索引文件中取。index 与 all 区别在于 index 只遍历索引树，通常会比 all 快

all： 遍历全表找匹配的行

-- t2 表中 content 字段没有添加索引
explain select * from t2 where t2.content = "abc";

最好到最差依次为：system > const > eq_ref > ref > range > index > all

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

1.4  possible_keys

显示可能应用在这张表中的索引，一个或多个。但不一定会被用的上

1.5  key

查询中实际使用到的索引

1.6  key_len

使用到索引的字节数。一般情况下 key_len 越大越好

1.7  ref

显示索引的哪一列被使用了

explain select t2.*,t3.* 
from t2 
join t3 on t2.id = t3.id;

1.8  rows

显示MySQL认为执行时查询必须检查的行数，一般越少越好

1.9  Extra

Using filesort：排序时没有使用索引，称”文件排序“

Using temporary：分组时没有使用索引，分组的底层为排序，即 Using temporary 与 Using                               filesort 常同时出现。效率低

Using index：表示索引被用来执行索引键值的查找。效率不错

Using where：表示使用了 where 过滤

8.查询优化

-- 数据准备
drop table if exists students;
create table students(
id int primary key auto_increment comment "主键id",
sname varchar(24) comment "学生姓名",
age int comment "年龄",
score int comment "分数",
time timestamp comment "入学时间"
);

insert into students(sname,age,score,time) values("小明",22,100,now());
insert into students(sname,age,score,time) values("小红",23,85,now());
insert into students(sname,age,score,time) values("小绿",25,70,now());
insert into students(sname,age,score,time) values("小黑",21,92,now());

索引失效

1. 最佳左前缀法则：如果是复合索引，要遵循左前缀法则。即查询从索引左前边的列开始且不跳过索引中的列

   -- 添加复合索引
   alter table students add index idx_sname_age_score(sname,age,score);
   

   -- 正常使用复合索引查询
   explain select * from students where sname = "小明" and age = 22 and score = 100;

   

   ---

   -- 不是从索引左前边 sname 开始，索引失效
   explain select * from students where age = 22 and score = 100;

   

   ---

   -- 跳过后面索引列 age 和 score，此时 key_len 为99，索引使用不完全
   explain select * from students where sname = "小明";

   

   ---

   -- 跳过了中间索引列 age，key_len 小于109，索引利用不完全
   -- key_len 等于99，说明此时score没有发挥作用
   explain select * from students where sname = "小明" and score = 100;

   

   ---

   -- 跳过索引列 score，此时 key_len 为104，索引使用不完全
   explain select * from students where sname = "小明" and age = 22;

   

2. 在索引列上做任何计算、函数操作，会导致索引失效转为全表扫描

   -- 使用函数，索引失效
   explain select * from students where left(sname,2) = "小明";

   

3. 存储引擎不能使用索引中范围条件右边的列

   -- 此时 key_len 为104，说明此时 score 没有生效
   explain select * from students where sname = "小明" and age > 22 and score = 100;

   

4. MySQL在使用不等于时无法使用索引会导致全表扫描（mysql 8以上不会导致全表扫描）

5. is null 可以使用索引，is not null 无法使用索引（mysql 8以上都可以使用索引）

6. like 以通配符开头会使索引失效导致全表扫描

   -- 此时通配符在前面，索引失效
   explain select * from students where sname like "%明";

   

   -- 通配符在后面，索引不影响
   explain select * from students where sname like "明%";

   

7. 字符串不加引号，索引会失效

8. 使用 or 连接时索引失效

排序优化 | 排序优化

1. 尽量避免使用 Using FileSort 方式排序
2. order by 语句使用索引最左前列或使用 where 子句与 order by 子句条件组合满足索引最左前列
3. where 子句中如果出现索引范围查询会导致 order by 索引失效

关联查询优化

• 内连接，mysql 自动将小结果集的表选为驱动表，所以大表要加索引
• 左外连接，左表一定会全表扫描，将小表放左边，对右表大表要加索引
• 右外连接，右边一定会全表扫描，所以左表要加索引

总结：对大表加索引

9.慢查询日志

慢查询日志介绍

        是 MySQL 提供的一种日志记录，用来记录 MySQL 中响应时间的超过阈值的语句。超过最长忍耐时间的 SQL 语句会被记录到日志中。

慢查询日志的使用

        注：非调优场景不建议开启，将日志写入文件或多或少会影响性能

        默认情况下是关闭的，需要手动设置开启

-- 查看是否开启，OFF 为关闭状态，ON 为开启状态
show variables like '%show_query_log%';

-- 开启日志
set global slow_query_log = 1;

-- 设置时间，这里设置最长忍耐时间为1秒
set global long_query_time = 1;

-- 查看时间，这里需要新开一个窗口才能看到
show variables like 'long_query_time%';

-- 关闭慢查询日志
set global slow_query_log = 'OFF';

查看超时的 sql 记录日志：在 mysql 安装目录下的 data 目录下 \设备名称-slow.log