MySQL 索引和优化

MYSQL 中有哪些日志

错误日志

默认是开启的

命令：show variables like '%log_error%'；

通用查询日志

记录了一般查询语句

命令：show variables like '%general%'

二进制日志

记录了对 MySQL 数据执行的更改操作，并且记录了语句的发生时间、执行的时长。但是它不记录 select、show 等不修改数据的SQL。

命令：show variables like '%binlog%'

慢查询日志

记录了所有执行时间超时的查询 SQL，这个超时的时间长默认是 10s

命令：

• 是否开启：show variables like 'slow_query'
• 执行的时长：show variables like 'long_query_time'

MySQL 的索引

索引可以提升查询的速度，会影响到 where 查询，以及 order by 排序。

索引有哪些类型

我们先从索引的数据结构来看，常见的有四种：

• B Tree 索引

• Hash 索引

• FULLTEXT 索引

• R Tree 索引

从索引的键值类型来看可以分为两种：

• 主键索引
• 辅助索引，其中赋值索引又叫二级索引

从索引的数据结构和键值的逻辑关系来看可以分为两种：

• 聚簇索引又叫聚集索引
• 非聚簇索引又叫非聚集索引

从应用的层次来看可以分为四种：

• 普通索引

• 唯一索引

• 主键索引

• 复合索引

普通索引

1. 这是最基本的索引类型，基于普通字段建立的索引

2. 创建普通索引的命令：

3. 1. create index <索引的名称> on 表名 （字段名）
   2. alter table 表名 add index [索引的名称]（字段名）
4. 3. create table 表名（[....]，index索引的名称）

唯一索引

1. 索引的字段值必须唯一，但是允许有空值。在创建表或修改表的时候追加唯一约束，就会自动创建对应的唯一索引。

2. 创建唯一索引的命令：

3. 1. create unique index <索引的名称> on 表名 （字段名）
   2. alter table 表名 add unique index [索引名]（字段名）
4. 3. create table 表名（[...]，unqiue[索引名]（字段名））

主键索引

1. 是一种特殊的索引，不允许有空值。在创建和修改表的时候追加主键约束。每个表中只能有一个主键。

2. 创建主键索引的命令：

3. 1. create table 表名（[...]，primary key（字段名））
   2. alter table 表名 add primary key (字段名)

复合索引

1. 在多个列上建立索引，这种索引叫复合索引（也叫组合索引）。复合索引可以代替多个单一索引，相比较多个单一索引复合索引所需要的开销更小。

2. 索引同时还有两个概念，窄索引和宽索引。

3. 1. 窄索引：指索引的列为 1-2 列的索引
   2. 宽索引：指索引的列为 2 个以上的索引

4. 设计索引有一个重要的原则：能用窄索引就不用宽索引，因为窄索引往往比复合索引更高效。

5. 创建复合索引的命令：

6. 1. create index <索引名> on 表名 （字段名1，字段名2...）
   2. alter table 表名 add index[索引名称]（字段名1，字段名2...）
7. 3. create table 表名（[...]，index[索引名称]（字段名1，字段名2...））

8. 复合索引使用的注意事项：

9. 1. 需要根据 where 条件建立索引，注意不要过多的使用索引，过多的使用索引会影响到更新操作的效率
   2. 如果表已经建立了（col1，col2），就没有必要再单独建立（col1）；如果现在 col1 有索引，查询时有 col1 和 col2 条件，可以建立（col1，clo2）复合索引，可以提高些查询效率。

全文索引

查询操作在数据量比较少的时候可以使用 like 进行模糊查询，但是对于大量的文本数据检索时，效率会很低。这个时候我们可以使用全文索引，提高查询效率。

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引，以后的版本中，MyISAM 和 InnoDB 均支持全文索引。

1. 创建全文索引的命令：

2. 1. create fulltext index <索引名> on 表名（字段名）
   2. alter table 表名 add fulltext[索引名]（字段名）
3. 3. craete table 表名（[...]，fulltext key[索引名]（字段名））

select * from user where match(name) against('aaa');

2. 使用全文索引的注意事项：

3. 1. 全文索引只能建立在字符串、文本字段上
   2. 全文索引的字段值范围必须在最小字符和最大字符之间的时候才有效（InnoDB：3-84，MyISAM：4-84）
4. 3. 全文索引的字段值要进行切词处理，按 syntax 字符进行切割（例如：b+aaa 会切分成 b 和 aaa）
   4. 全文索引匹配查询，默认使用的是等值匹配（例如：a 匹配 a，不会匹配 ab 或者 ac）。如果想匹配可以在布尔模式下搜索 a*（如下）

select * from user where match(name) against('a*' in boolean mode);

MySQL 索引原理

认识下索引

索引是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。并且索引是物理数据页存储，在数据文件中（InnoDB，ibd 文件），需要利用数据页（page）存储。

查询用到的算法

二分查找法（折半查找）

具体原理和算法看这篇 www.yuque.com/solider/whn…

Hash

我么首先看下 Hash 的结构：

Hash 底层是由 Hash 表实现的，键值对<key,value>存储数据的结构。非常适合根据 key 查找 value 的值，也就是说等值查询或者说单个 key 查询。

从上图可以看到 Hash 索引可以很方便的提供等值查询。对于范围查询就需要进行全表扫描了。在 MySQL 中 Hash 结构主要用在原生 Memory 原生的 Hash 索引上，InnoDB 自适应 Hash 索引。

InnoDB 自适应 Hash 索引

InnoDB 为了提升查询效率，会监控表上各个索引页的查询。如果发现某些索引值被访问的很频繁时（热点页），会在内存中基于 B+Tree 索引再创建一个 Hash 索引，使得内存中的 B+Tree 索引具备 Hash 索引的功能，从而达到能够快速访问到被频繁访问到的索引页。

我们只能选择开启或关闭 InnoDB 的自适应哈希索引功能，无法进行人工干涉：

show engine innadb status \G;
show variables like '%innodb_adaptive%';

MySQL 数据库索引采用的是 B+Tree 结构（B+Tree 在 B-Tree 上做了优化）。下面我们分别看下这两种数据结构。

B-Tree 结构

从 B-Tree 的数据结构中我们可以看到：

• 索引值和 data 数据分布在整个树的结构中
• 每个节点可以存放多个索引值和对应的 data 数据（按照从左到右升序排列）

如何查询呢？

对于 B-Tree 的搜索，采用了二分查找算法，从根节点开始，对节点内的索引值进行搜索，如果命中就结束查找。没有命中的话就会进入左右孩子重复进行二分查找，直到所对应的节点指针为空，或者叶子节点才结束。

B+Tree 结构

从 B+Tree 的结构来看，我们可以得到这几点信息：

• 非叶子节点不存储 data 数据，只存储索引值，这样便于存储更多的索引值

• 叶子节点包含了所有的索引值和 data 数据

• 叶子节点互相用指针连接，太高了区间的访问性能

B-Tree 和 B+Tree 对比

相比于 BTree/B-Tree，B+Tree 在进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。但是 BTree/B-Tree 需要遍历范围内的所有节点和数据。显然 B+Tree 的搜索效率更高。

聚簇索引（聚集索引）

B+Tree 的叶子节点存放的是主键索引值和行记录时就属于聚簇索引。如果索引值和行记录分开放的话就属于非聚簇索引。

主键索引和辅助索引：

• B+Tree 的叶子节点存放的是主键字段值就属于主键索引
• B+Tree 的叶子节点存放的是非主键字段就属于辅助索引（也叫二级索引）

在 InnoDB 引擎中，主键索引采用的就是聚簇索引结构存储，如下：

InnoDB 的聚簇索引就是按照主键顺序构建 B+Tree 结构的。B+Tree 的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。这就意味着 InnoDB 的主键索引就是数据表本身，它按照主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。

InnoDB 的表要求必须要有聚簇索引：

• 如果定义了主键，那么主键索引就是聚簇索引

• 如果表没有定义主键，则第一个非空 unqiue 列作为聚簇索引

• 否则 InnoDB 会重建一个隐藏的 row-id 作为聚簇索引

辅助索引

InnoDB 辅助索引，也叫作二级索引。根据索引的列构建成 B+Tree 结构，但是这个 B+Tree 的叶子节点只存储了索引列和主键的信息。所以二级索引占用的空间比聚簇索引少。创建辅助索引的目的也是为了提升查询效率，一个 InnoDB 只能创建一个聚簇索引，但是可以创建多个辅助索引。

非聚簇索引

与 InnoDB 表存储不同，MyISAM 数据表的索引文件和数据文件是分开的。

回表查询

InnoDB 索引有主键索引和聚簇索引，聚簇索引的叶子节点存储行记录（InnoDB 必须要有，有且只有一个）。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下需要扫描两边索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询。

EXPLAIN

MySQL 提供了一个 EXPAIN 命令，它可以对 SELECT 语句进行解析并且输出 SELECT 执行的详细信息。例如：

EXPLAIN SELECT * FROM `sys_config`;

从图中我可以看到语句的执行结果。我们解释下各个列代表的含义：

• select_type：表示的是查询的类型，最常见的查询类型是 SIMPLE，表示此次查询既没有子查询也没有 UNION查询。

• • SIMPLE：查询的语句不包含子查询或者 union
  • PRIMARY：表示此查询时最外层的查询

• • UNION：表示此查询是 UNION 的第二个或者后续的查询
  • DEPRENDENT UNION：UNION 中的第二个或者后续的查询语句，使用了外面查询的结果

• • UNION RESULT：UNION 的结果
  • SUBQUEY：SELECT 语句的子查询

• • DEPENDENT SUBQUERY：SELECT 子查询语句依赖外层查询的结果。

• type：表示的是存储引擎查询数据时采用的方式。通过它可以判断出查询是全表扫描还是基于索引的部分扫描。

• • ALL：表示的是全表扫描，性能最差的
  • index：表示是基于索引的全表扫描，先扫描索引再扫描全表数据
• • range：表示使用索引范围查询。使用 >、>=、<、<=、in 等等
  • ref：表示使用非唯一索引进行单值查询
• • eq_ref：一般情况下出现在多表 join 查询，表示前面表的每一个记录，都只能匹配后面表的一行结果
  • const：表示使用主键或唯一索引做等值查询、常量查询
• • NULL：表示不要访问表，速度最快

• possibleKeys：表示查询时能够使用到的索引，显示的是索引名称，但是不一定会真正使用

• key：表示查询时真正使用到的索引，显示的是所有名称

• rows：MySQL 查询优化器会根据统计信息，估算 SQL 要查询到结果需要扫描多少行记录。原则上 rows 是越少效率越高，可以直观的看到 SQL 效率的高低。
• key_Len：表示查询使用了索引的字节数量。可以通过此列判断是否使用了组合索引。这个长度有计算规则

• • 字符串类型：字符串长度跟字符集有关。latin1=1；gbk=2；utf8=3；utf8mb4=4；char(n)：n*字符集长度；varchar(n)：n * 字符集长度 + 2字节
  • 数值类型：TINYINT：1个字节；SMALLINT：2个字节；MEDIUMINT：3个字节；INT、FLOAT：4个字节；BIGINT、DOUBLE：8个字节

• • 时间类型：DATE：3个字节；TIMESTAMP：4个字节；DATETIME：8个字节
  • 字段属性：NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项

• filtered：表示按照条件过滤掉的行记录数的百分比
• extra：这里会展示很多额外的信息

• • Using where：表示查询需要通过索引回表查询数据
  • Using index：表示查询需要通过索引，索引就可以满足所需数据

• • Using filesort：表示查询出来的结果需要额外排序，数据量小在内存中排序，数据量大的话在磁盘，因此有 filesort 时=，建议做优化。
  • Using temproary：查询使用到了临时表，一般出现在去重、分组等操作

回表查询

在之前我们介绍过，InnoDB 索引有聚簇索引和辅助索引（详情看这里www.yuque.com/solider/qcl…）。聚簇索引的叶子节点存储行记录，辅助索引存储的是主键值和索引字段值，通过辅助索引无法直接定位到行记录，通常情况下需要遍历两遍索引树：

• 先通过辅助索引树定位到主键值
• 在通过聚簇索引定位到行记录

所以回表查询：就是通过索引查询主键值，然后再去聚簇索引查询行记录。

---

覆盖索引

在 MySQL 官网中，有一个说法，当 explain 的输出结果 Extra 字段为 Using index 时，能够触发索引覆盖。

什么是索引覆盖？

只需要在一棵索引树上就能获取 SQL 所需的所有列数据，就不需要回表，速度更快，这种就叫索引覆盖。

如何实现索引覆盖呢？

将被查询的字段建立到组合索引中。

最左前缀原则

复合索引在使用的时候遵循最左前缀的原则。意思就是查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查询，索引就会失效。如下图：

LIKE 查询

MySQL 在使用 like 模糊查询时，索引能不能起作用？

MySQL 在使用 like 做模糊查询时，索引时可以被使用的，但是只有把 % 字符写在条件的后面才会使用到索引。如下：

SELECT * FROM USER WHERE name like "%m%" // 索引不生效
SELECT * FROM USER WHERE name like "%m" // 索引不生效
SELECT * FROM USER WHERE name like "m%" // 索引生效

NULL 查询

如果 MySQL 表的某一列含有 NULL 值，那么包含该列的索引是否有效？

MySQL 可以在含有 NULL 的列上使用索引。但是 NULL 和其它数据还是有区别的。不建议列上允许为 NULL。最好列的值设置为 NOT 并且给一个默认值（比如：0 和 "" 字符串等，如果是 datetime 类型，可以设置系统的当前时间或者固定的特殊值）

解释如下：

对于 MySQL 来说，NULL 是一个特殊的值（比如：不能使用 =、<、>这样的运算符，对 NULL 做算数运算的结果都是 NULL）。count 的时候不会包含 NULL 行等。

我们看下官方中给的解释：

“NULL columns require additional space in the row to record whether their values
are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to
the nearest byte.”

意思就是 NULL 列需要额外的空间来记录其值是否为 NULL。对于 MyISAM 表，每一个空列额外占用一位，四舍五入到最近的字节。

索引与排序

MySQL 查询支持 filesort 和 index 两种方式的排序：

• filesort：先把结果查出，然后在缓存或磁盘中进行排序操作，效率较低
• index：使用 index 是指利用索引字段实现排序，不需要另外做排序，效率相对较高

filesort

我们说说 filesort 吧，filesort 有两种排序算法：单路排序和双路排序。

单路排序

从磁盘中查询所需要的所有列数据，然后在内存中排序将结果返回。如果查询数据超出缓存 sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后会产生多次的 IO 操作。

解决方案：

• 少使用 select *
• 增加 sort_buffer_size 容量

• 增加 max_length_for_sort_data 容量

双路排序

需要做两次磁盘扫描读取，第一次扫描磁盘将排序字段读取出来，然后排序；第二次去读取其它字段数据。

如果我们用 Explain 分析 SQL，结果中 Extra 属性显示 Using filesort，表示使用了 filesort 排序方，需要优化。

如果我们用 Explain 分析 SQL，结果中 Extra 属性显示 Using index，表示使用了覆盖索引，即操作都在索引上完成（建议尽可能采用覆盖索引）。

下面我们看几个使用 index 方式排序的案例（使用了索引的案例） ：

• ORDER BY 子句索引列组合满足索引最左前列（最左前缀原则）

explain select id from user order by id; // 对应(id)、(id,name)索引有效

• WHERE 子句 + ORDER BY 子句索引列组合满足索引最左前缀原则

explain select id from user where age=18 order by name;// 对应（age,name）索引

下面几种情况，会使用 filesort 方式排序（不使用索引的案例，都会使索引失效） ：

• 对索引列同时使用了 ASC 和 DESC

explain select id from user order by age asc,name desc;// (age,name)索引

• WHERE 子句和 ORDER BY 子句满足最左前缀，但 WHERE 子句使用了范围查询（例如：>，<，in 等）

explain select id from user where age > 10 order by name;// (age,name)索引

• ORDER BY 或者 WHERE + ORDER BY 索引列没有满足索引最左前缀原则

explain select id from user order by name, age;// 对应 (age)、(name)两个索引

• 使用了不同的索引，MySQL 每次查询只采用一个索引，ORDER BY 设计了两个索引

explain select id from user order by name,age; // 对应(name)、(age)两个索引

• WHERE 子句与 ORDER BY 子句，使用了不同的索引

explain select id from user where name = 'tom' order by age; // 对应(name)、(age)索引

• WHERE 子句或者 ORDER BY 子句中索引列使用了表达式，包括函数表达式

explain select id from user order by abs(age);// 对应(age)索引

查询优化

慢查询定位

我们如何去定位慢查询：

• 首先查看下是否开启了慢查询日志

• • 查询命令：show variables like 'slow_query_log'

• 如果没有开启慢查询日志，开启慢查询日志

• • set global slow_query_log = on
  • set global slow_query_log_file = 'OAK-slow.log' // 这是慢查询日志文件

• • set global log_queries_not_using_indexes = on // 会记录没有使用索引的查询 SQL，前提是当 slow_query_log 已经开启，否则不会奏效。
  • set long_query_time = 10 // 指定慢查询的阈值，超过这个阈值则为慢查询，会记录到日志文件中

• 查看慢查询日志

• • 文本方式查看：直接用文本编辑器打开 slow.log 日志即可，结果如下图所示

• • 使用 mysqldumpslow 查看：这是 MySQL 提供的慢查询日志分析工具，在 MySQL 的 bin 目录下执行下面命令：

perl mysqldumplslow.pl --help
// 查看慢查询日志信息
perl mysqldumpslow.pl -t 5 -s at C:\ProgramData\MySQL\Data\OAK-slow.log

• • 当然还可以使用第三方工具查看慢查询日志（例如：mysqlsla、pt-query-digest 等）

如何判断是否为慢查询？

• 我们可以看到 SQL 的执行时间，与我们配置的 long_query_time 做比较，如果 SQL 的执行时间超过了这个阈值，则为慢查询

如何判断查询是否使用了索引?

• 可以用 explain 命令分析，查询结果中的 key 列值是否为 null，为 null 的则没有使用索引

慢查询优化

上面我们通过一系列操作定位到了慢查询的 SQL，现在我们看下如何对慢查询做优化：

提高索引过滤性

案例：

假如有一个5000万记录的用户表，通过sex='男'索引过滤后，还需要定位3000万，SQL执行速度也不会很快。其实这个问题涉及到索引的过滤性，比如1万条记录利用索引过滤后定位10条、100条、1000条，那他们过滤性是不同的。索引过滤性与索引字段、表的数据量、表设计结构都有关系。

表：student
字段：id,name,sex,age
造数据：insert into student (name,sex,age) select name,sex,age from
student;
SQL案例：select * from student where age=18 and name like '张%';（全表扫
描）

针对上面的案例，我们可以对 SQL 做一些优化：

• alter table student add index(name)；// 追加 name 索引

• alter table student add index(age,name)；// 追加 age、name 索引

• 可以把名字的第一个字和年龄做一个联合索引

// 为 user 表添加 first_name 虚拟列，以及联合索引(first_name，age)
alter table student add first_name varchar(2) generated always as (left(name,1)),add index(first_name，age)

总结：慢查询产生的原因

• SQL 执行了全表扫描，explain 分析后可以看到 type 属性值为 all

• 全索引扫描 explain 分析 type 属性

• 索引过滤性不好：靠索引字段选型、数据量统计、表设计

• 频繁的回表查询：尽可能少用 select *，推荐使用覆盖索引

分页查询优化

1. 一般性分页： 一般分页的查询使用简单的 limit 子句就能实现。

select * from  user limit [offset,] rows;

• offset：指定的是一个返回记录的偏移量（从 0 开始的）
• rows：指定返回记录行的最大数目

• 如果只给定一个参数，那么它会返回最大的记录行数目

如果偏移量固定，返回记录的数目对执行时间有什么影响？

select * from user limit 10000,1;
select * from user limit 10000,10;
select * from user limit 10000,100;
select * from user limit 10000,10000;

上面的语句执行结果，可以得出一个结论。当返回记录的数目小于 100 条时，查询语句执行的时间基本没有变化。随着查询记录的量越大，所花费的时间也会越来越多。

如果查询偏移量变化，返回的记录数目对执行时间有什么影响？

select * from user limit 1,100;
select * from user limit 10,100;
select * from user limit 1000,100;
select * from user limit 10000,100

在查询记录的时候，如果查询记录的量相同，偏移量超过 100 后随着偏移量的增大，查询时间会急剧增加。（因为这种分页查询机制，每次都会从数据库的第一条。记录开始扫描，越往后查询越慢，而且查询的数据会越多，就会拖慢查询速度）

优化的方案

1. 利用覆盖索引优化

select * from user limit 10000,100;
select id from user limit 10000,100;

2. 利用子查询优化

select * from user limit 10000,100;
select * from user where id >= (select id from user limit 10000,1) limit 100;

id 是主键，使用了 id 做主键比较（id >=），并且子查询使用了覆盖索引进行优化。