中间件MySQL
Source
Edit
History

索引优化

Catalogue
  1. 1. 索引分类
  2. 2. 创建索引语法
  3. 3. 单表索引优化
  4. 4. 关联查询索引优化
  5. 5. 子查询优化
  6. 6. 排序分组优化
  7. 7. 排序算法
  8. 8. 慢查询日志
  9. 9. Explain说明
    1. 9.1. id
    2. 9.2. select_type
    3. 9.3. table
    4. 9.4. type
    5. 9.5. possible_keys
    6. 9.6. key
    7. 9.7. key_len
    8. 9.8. ref
    9. 9.9. rows
    10. 9.10. Extra
  10. 10. 索引实现

索引分类

  • 普通索引：是最基本的索引，它没有任何限制，即一个索引只包含单个列，一个表可以有多个单列索引；建议一张表索引不要超过5个，优先考虑复合索引
  • 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
  • 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
  • 复合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
  • 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配

创建索引语法

1

示例表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 CREATE TABLE `dept` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`deptname` VARCHAR (30) DEFAULT NULL,
	`address` VARCHAR (40) DEFAULT NULL,
	`ceo` INT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

CREATE TABLE `emp` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`empno` INT NOT NULL,
	`name` VARCHAR (20) DEFAULT NULL,
	`age` INT (3) DEFAULT NULL,
	`deptId` INT (11) DEFAULT NULL,
	PRIMARY KEY (`id`) #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

单表索引优化

  1. 全职匹配指的是，查询的字段尽量按照复合索引创建时的顺序
  2. 最佳前缀法则：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用
  3. 不要在索引列上做任何计算，如计算、函数、(自动 or 手动)类型转换，会导致索引失效而转向全表扫描，字符串不加单引号，则会做一次转换使索引失效
  4. 尽量将可能做范围查询的字段的索引顺序放在最后，也会引起索引失效
  5. 尽量使用覆盖索引： 即不要写select *,而明确指定列
  6. 使用!=和<>时，有时会无法使用索引会导致全表扫描
  7. is not null用不到索引，is null是可以用到索引
  8. like时尽量后缀模糊匹配，即用xxx%
  9. 减少使用or，使用 union all 或者 union 来替代

口决：
全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE 百分写最右，覆盖索引不写*；
不等空值还有 OR，索引影响要注意；
VAR 引号不可丢，SQL 优化有诀窍。

示例如下，假设存在索引 index(a,b,c)

where语句 索引是否被使用
where a = 3 Y,使用到 a
where a = 3 and b = 5 Y,使用到 a，b
where a = 3 and b = 5 and c = 4 Y,使用到 a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 N
where a = 3 and c = 5 使用到 a， 但是 c 不可以，b 中间断了
where a = 3 and b > 4 and c = 5 使用到 a 和 b， c 不能用在范围之后，b 断了
where a is null and b is not null is null支持索引，is not不支持，a用到了
where a <> 3 N
where abs(a) =3 N
where a = 3 and b like ‘kk%’ and c = 4 Y,使用到 a,b,c
where a = 3 and b like ‘%kk’ and c = 4 Y,只用到 a
where a = 3 and b like ‘%kk%’ and c = 4 Y,只用到 a
where a = 3 and b like ‘k%kk%’ and c = 4 Y,使用到 a,b,c

关联查询索引优化

  1. left join 时，左侧的为驱动表，右侧为被驱动表，在优化关联查询时，只有在被驱动表上建立索引才有效
  2. inner join 时，mysql 会自己帮你把小结果集的表选为驱动表。
  3. straight_join效果和 inner join 一样，但是会强制将左侧作为驱动表！
  4. 子查询尽量不要放在被驱动表，由于子查询是虚表，无法使用索引，尽量让实体表作为被驱动表
  5. 能多表关联的尽量直接关联，不用子查询
  6. 两表关联，我们可以考虑将小表作为驱动表,即小表驱动大表

子查询优化

在范围判断时，尽量不要使用 not in 和 not exists，使用 left join on xxx is null 代替

1
2
3
-- 取所有不为掌门人的员工，按年龄分组
反例：EXPLAIN SELECT SQL_NO_CACHE t1.age, count(1) FROM emp t1 where t1.id not in (SELECT ceo FROM dept) GROUP BY t1.age;
EXPLAIN SELECT t1.age, count(1) FROM emp t1 LEFT JOIN dept t2 on t1.id = t2.ceo where t2.id is NULL GROUP BY t1.age;

排序分组优化

  1. 无过滤，不索引: 用了过滤再排序才能用上索引，where、limit都属于过滤

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE INDEX idx_age_deptid_name on emp(age,deptid,name);

    -- 可以用上索引
    explain select * from emp where age=40 order by deptid; 
    explain select * from emp order by age,deptid limit 10;

    -- 不能用上索引，会产生filesort
    explain select * from emp order by age,deptid;

  2. 顺序错，必排序：非按照建索引时的顺序使用，会产生filesort

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- ok
    explain SELECT * FROM emp where age=45 ORDER BY deptid,name;

    -- no, empno字段并没有建立索引，因此也无法用到索引，此字段需要排序！
    explain SELECT * FROM emp where age=45 ORDER BY deptid,empno;

    -- no, order by 列的顺序不能随便变换
    explain SELECT * FROM emp where age=45 ORDER BY name,deptid;

    -- no, deptid 作为过滤条件的字段，无法使用索引，因此排序也没法用上索引
    explain SELECT * FROM emp where deptid=45 ORDER BY age;

  3. 方向反，必排序：多个字段排序时，不同方向时会产生排序

    1
    2
    3
    4
    5
    -- ok, 如果可以用上索引的字段都使用正序或者逆序，实际上是没有任何影响的，无非将结果集调换顺序。
    explain SELECT * FROM emp WHERE age=45 ORDER BY deptid desc, name desc;

    -- no, 如果排序的字段，顺序有差异，就需要将差异的部分，进行一次倒置顺序，因此还是需要手动排序的！
    explain select * from emp where age=45 order by deptid asc, name desc ;

  4. group by 使用索引的原则几乎跟 order by 一致 ，唯一区别是 groupby 即使没有过滤条件用到索引，也可以直接使用索引。

  5. 当范围条件和 group by 或者 order by 的字段出现二选一时 ，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

    1
    2
    3
    4
    5
    CREATE INDEX idx_age_name on emp(age, name);
    CREATE INDEX idx_age_empno on emp(age, empno);

    -- 根据实际情况选择使用哪个索引
    EXPLAIN SELECT SQL_NO_CACHE * FROM emp use index(idx_age_name) WHERE age=30 and empno < 101000 ORDER BY name;

    所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。 相对的 empno<101000 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，使用 empno 字段的范围查询，过滤性更好。

排序算法

双路排序：MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据。从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。简单来说，取一批数据，要对磁盘进行了两次扫描，众所周知，I\O 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序。

单路排序：从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO,但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

单路排序的问题：有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序，期间会创建 tmp 文件，多路合并，排完再取取 sort_buffer 容量大小，再排……从而多次 I/O。本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

单路排序问题解决：

  1. 增大 sort_butter_size 参数的设置: 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M 之间调整。
  2. 增大 max_length_for_sort_data 参数的设置: mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data。 提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大， 明显症状是高的磁盘 I/O 活动和低的处理器使用率。（1024-8192 之间调整）。
  3. 减少 select 后面的查询的字段。当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。
  4. 两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O， 但是用单路排序算法的风险会更大一些,所以要提高 sort_buffer_size。

慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为 10，意思是运行10秒以上的语句。

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。 慢查询日志支持将日志记录写入文件。

查看慢日志，可直接打开文件看，或者通过mysqldumpslow命令查看，具体看help

1
2
3
4
5
6
7
8
9
10
11
## 查看慢日志是否开启
show variables like '%slow_query_log%';

## 开启
set global slow_query_log=1;

## 设置阀值，单位秒
set long_query_time=1

## 查看阀值
show variables like '%long_query_time%';

如永久生效需要修改配置文件 my.cnf 中[mysqld]下配置

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

Explain说明

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。
用法： Explain+SQL 语句，Explain 执行后返回的信息：

id select_type table type possible_keys key key_len ref rows Extra
序列号 查询类型 目标表

id

select 查询的序列号, 包含一组数字，表示查询中执行 select 子句或操作表的顺序。id 号每个号码，表示一趟独立的查询。一个 sql 的查询趟数越少越好。

  • id 相同，执行顺序由上至下
  • id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

select_type

select_type 代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

可取值 含义
SIMPLE 简单的 select 查询,查询中不包含子查询或者 UNION
PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary
DERIVED 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) , 会递归执行这些子查询, 把结果放在临时表里
SUBQUERY 在SELECT或WHERE列表中包含了子查询，一般是单值
DEPEDENT SUBQUERY 在SELECT或WHERE列表中包含了子查询,子查询基于外层，一般是一组值
UNCACHEABLE SUBQUERY 无法使用缓存的子查询
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT 从UNION表获取结果的SELECT

table

这个数据是基于哪张表的。

type

type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

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

可取值 含义
system 表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计
const 表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快 如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量。
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
ref 非唯一性索引扫描，返回匹配某个单独值的所有行,属于查找和扫描的混合体
range 只检索给定范围的行,使用一个索引来选择行,where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
index 出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。
ALL 将遍历全表以找到匹配的行

possible_keys

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

key

实际使用的索引。如果为NULL，则没有使用索引。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长，说明索引使用的越充分

ref

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

rows

rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好！

Extra

可取值 含义
Using filesort 说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。
Using temporary 使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by
Using Index 代表表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！ 如果同时出现 using where，表明索引被用来执行索引键值的查找;如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。
Using Where 表明使用了 where 过滤。
Using join buffer 使用了连接缓存。
impossible where where 子句的值总是 false，不能用来获取任何元组。

索引实现

索引是在存储引擎层而不是服务器层实现的，Mysql索引使用的数据结构主要有BTree索引和哈希索引。

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。

  • MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
  • InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。