MySQL笔记 | 6.MySQL中不能不学的索引（上）

系列文章目录

提示：所有文章的目录
1.了解SQL的执行过程
2.Docker下搭建MySQL&查看BinLog文件
3.MySQL中涉及的锁
4.MySQL数据库设计-字段类型
5.在Docker中搭建主备

---

前言

前面学习了许多的理论基础，但是在实际的工作中，我们主要的任务还是完成任务。常常会因为查询时性能上不去而苦恼，这就是我们在这次的文章中需要去学会解决的，认识索引，合理使用索引以及索引的优劣。为了更好的整理知识点，这里提供出一个思维导图作为参考。

---

提示：以下是本篇文章正文内容

一、索引是什么？

用大白话：索引相当于书签，图书馆的目录。可以快速的寻找到位置。
用数据结构：单独的、物理的数据库表中一列或多列进行排序的数据结构，是物理数据页，数据库页大小（Page Size）决定了一个页可以存储多少个索引行，以及需要多少页来存储指定大小的索引。

1. 索引的优缺点

优点：

1. 索引大大减小了服务器需要扫描的数据量。
2. 索引可以帮助服务器避免排序和临时表。
3. 索引可以将随机 I/O 变成顺序 I/O。

缺点：

1. 查询速度提高，其他的操作效率降低，会导致数据页空间利用率降低。
2. 列包含重复数据较多，无需索引。
3. 索引文件会导致，索引文件变大。

2. 索引的特点

InnoDB索引:
基于磁盘的平衡二叉树，但树非常矮，通常为 3~4 层，能存放千万到上亿的排序数据。树矮意味着访问效率高，从千万或上亿数据里查询一条数据，只用 3、4 次 I/O。

对于普通索引和唯一索引的选择
先来看一下，两个索引查询的步骤

1. 普通索引，查询到符合条件的数据之后没有返回，再向下查询一次。
2. 唯一索引，查询到复核条件的数据直接返回。
   相比较，性能上的差别几乎很小，可能也就多查询一次。

3. MySQL主要使用的数据结构

二叉排序树 → 二叉平衡树 → B-Tree（B树） → B+Tree（B+树）

以下图片来源于网上

二、索引的应用

索引是对记录进行排序， 高度为 1 的 B+ 树索引中，存放的记录都已经排序好了，若要在一个叶子节点内再进行查询，只进行二叉查找，就能快速定位数据。

1. 回表查询，命中索引后还要回去聚簇索引中查找其他数据，少用select *。
2. 索引覆盖，如果明确了列，就不需要回表，这也是为什么要避免select *的原因。
3. 最左前缀原则，因为B+树是从左到右的顺序，匹配的时候也就从左到右，a,b,c三个是联合索引，按照最左前缀的原则，只要a是第一个，
   索引才生效，相当于创建了联合索引(a,b),(a,c),(a,b,c)
4. 索引下堆优化（在5.7之后才有）

select * from table where name like '陈%' and age > 26；
1.命中name索引，然后进行回表
2.命中name索引，在命中age索引，然后进行回表

5. 优化group by,默认情况下会对group by进行排序，想要避免排序结果消耗，可以指定order by null禁止排序

索引的失效

既然索引有这么多的应用空间，那操作不当肯定会造成索引的失效

1. 函数造成索引失效

select count(*) from tradelog where month(t_modified)=7;
-- 对字段做函数操作，破坏索引值的有序性，优化器就会放弃树的搜索功能。
-- 就有可能造成全表扫描

2. 隐式类型转换

1. 如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；
mysql> select "10">9;
+--------+
| "10">9 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

2. 如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。
mysql> select "10">"9";
+----------+
| "10">"9" |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql中的转换规则：在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。

4. 隐式字符编码转换

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;
-- 还是函数造成了索引失效，不同字符之间需要函数来转换

要注意，函数如果使用在输入的值上，就不会造成索引失效，我们常用的时间比较，就常常会犯错。

思考题：当主键是数值类型的时，输入值为字符串，是够能走索引？
能走索引，字符串会主动转换成数值，就不会造成索引失效。

三、索引操作建议

1. 索引列不能为null
2. 使用短索引，一般开头几个字符不同的时候适合创建。

alter table yy_order add index `test_kkkk`(`car_uuid`(4));

3. 索引如果已经进行where排序，在order上则失效。
4. 不要在索引列上进行操作，否则会进行全表扫描。
5. 不要使用not in和<>避免索引失效。
6. 使用前缀索引可以使索引更小，更快，但是前缀索引无法做ORDER BY和GROUP BY。

我们可以通过select count(distinct left(a.drug_apply_no, 15)) from drug_apply a;
来确定前缀索引最佳位数

7. 不要在区分度小的列上建索引，这样子没办法缩小范围，仍然需要走全表
8. 不要在小表上建索引

开启慢查询日志

日志分析工具：mysqldumpslow
sql命令开启：

-- 查询当前索引使用情况
show status like '%Handler_read%';
-- 设置慢查询最大时间
set global long_query_time=4;
-- 开启慢查询日志
set global slow_query_log = 1;
-- 慢查询超过的执行时间值
slow_launch_time: 
-- 是否打开慢查询日志功能
slow_query_log: 
-- 慢查询日志目录
show_query_log_file:
-- 查询慢查询配置
show variables like '%slow%';

步骤一：根据log_file 我们找到文件

cat 2b2ee975926a-slow.log

-- 查看日志输出方式
show variables like '%log_output%';

-- 未使用索引记录
show variables like 'log_queries_not_using_indexes';