MySQL的优化笔记

表设计の角度

表设计原则

一对一，通过主键关联
一对多，在多的一方设置外键
多对多，增加中间表，持有双方外键

使用反范式设计

严格采用一、二、三范式的设计会将系统中的表拆分的非常多，不利于查询。可以将一些冗余的字段加在必要的查询表中，从而将原本需要多表关联查询变成了单表查询，这就是反范式的目的。

优缺点：

设置代理主键

什么是自然主键，什么又是代理主键？

自然主键是指事物属性中的自然唯一标识，比如身份证号，学号，工号等
代理主键是指与业务无关的，无意义的数字序列值

通常设计一个自增主键作为代理主键，将具体的业务逻辑ID加上索引作为自然主键，且因为代理主键使用了自增，插入时是有顺序的，对B+Tree插入影响范围较小，这样计算量也是最小的。

索引优化の角度

了解 - 索引的形式

索引的存储形式是由存储引擎决定的

数据表索引分类

从存储结构上划分：
- BTree索引（B-Tree 或 B+Tree）
- Hash索引
- full-index全文索引
- R-Tree索引，通常用于GIS系统来表示空间数据
从应用分层上划分：
- 普通索引，普通的字段索引
- 唯一索引，常用于主键的索引
- 复合索引，用于多个条件的查询组织
从数据的物理顺序与键值逻辑（索引）顺序关系划分：
- 聚集索引，指插入顺序与索引建立顺序一致
- 非聚集索引

B+Tree结构

查找8和9的路径为：

了解 - MySQL常用的索引

B+Tree索引 - 适合用于范围查找
- InnoDB与MyISAM采用的是B+Tree索引
- B+Tree索引采用树形链表结构建立数据“目录”
- 在其他字段上的索引通过和主键的索引产生关联来绑定
Hash索引 - 适合于精确匹配
- Hash索引基于哈希表实现
- 精确匹配所有列的查询才有效，不支持范围查询，模糊查询及排序
- Hash索引为每条数据生成一个HashCode
- Hash索引只包含哈希值和行指针
- Hash取值速度非常快，但索引选择性很低时不建议使用
- MySQL目前只有Memory 显示支持Hash索引

上文说了只有在memory引擎中才会显示支持hash索引，那么在innodb中的hash索引如何创建呢？其实在Innodb中会自动帮我们创建，这里记一下他的几个特点：
InnoDB存储引擎只支持显示创建BTree索引
当数据精确匹配时MySQL会自动生成HashCode，存入缓存

了解 - MySQL中的锁

职责分类	粒度分类
共享锁 - 读锁	行级锁
独占锁（排他锁） - 写锁	表级锁

共享锁：
- 这个数据可以被所有的事务连接、共享，持有该数据共享锁的所有进程都可以访问到该数据，共享锁只服务于访问，又称为读锁。
独占锁：
- 当某一个线程获得了某一条数据的访问权限时，其他所有的数据操作都将进入等待的操作，直到解锁，又称为写锁。独占锁与共享锁是互斥关系。
行级锁：
- 只对当前修改的数据进行锁定，如果不用程序访问不同数据，那彼此间是互不影响的可以并行操作的。行级锁只出现于多个程序访问同一数据时。
表级锁：
- 只要获取到表级锁，无论里面更新几条数据，该表都是锁定状态，都无法修改成功。
- 表级锁锁定范围比较大，行级锁锁定范围比较小，从并发性角度来说，行级锁性能比表级锁性能好；从锁自身状态管理角度来说，行级锁的开销要比表级锁大。

了解 - InnoDB引擎

InnoDB支持事务
InnoDB默认使用行级锁
InnoDB具备良好的高并发特性

在InnoDB中只有利用索引的更新、删除操作，才可以使用行级锁，不能使用索引的写操作则是表级锁。在实际开发的时候，如果遇到写操作，一定要确保update/delete语句的条件要能够使用索引，否则就会锁表，程序将不具备并发性。

了解 - 什么情况下不会用到索引

索引选择性太差
<>或not in无法使用索引
is null会使用索引，is not null不会使用索引
where子句跳过左侧索引列，直接查询右侧索引字段
对索引列进行计算或使用函数

优化 - 使用索引优化排序

当order by字段与索引字段顺序/排序方向相同时，索引可以优化排序速度

在单字段情况下，索引支持升降序，比如：

sql

# field上面有索引，where时走了索引，order by上也走了索引
select * from table where field < 10000 order by field;

在多字段情况下，左侧字段必须是升序，且顺序不允许打乱

sql

# order by field必须为升序，如果用了desc，用explain分析则会是 Using filesort，代表效率较差
select * from table where field < 10000 order by field, field2;

优化 - 删除冗余索引

使用pt-duplicate-key-checker工具，他可以帮助检测表中重复的索引或者主键，仅支持linux环境。
执行实用索引SQL语句，按照索引维度看一下磁盘I/O的处理情况

sql

SELECT
	object_type, object_schema, object_name, index_name,
	count_read, count_fetch, count_insert,
	count_update, count_delete
FROM
	`performance_schema`.table_io_waits_summary_by_index_usage
ORDER BY
	sum_timer_wait DESC;

count_read: 索引在计算过程中读取了多少行
count_fetch: 最终查询结果是多少行
sum_timer_wait: 利用索引查询的总时间
当后面几个查询结果全为0时，代表MySQL服务运行起来后索引没有被使用过，可以被删掉

优化 - SQL编写

使用where条件

在精确匹配时，允许使用btree索引，比如where uid = 10001
在范围匹配时，允许使用btree索引，比如where uid > 10000 and uid < 10005
在匹配类型不一致时，比如where uid = 10001换成where uid = "10001"中，仍然可以使用btree索引，这是因为查询优化器会自动进行类型转换，但建议使用与定义相符的类型，减少转换操作。不过当使用where uid like "1000%"时，这时候走的就是全表扫描，查询优化器无法进行类型转换。

使用like条件

在字符串字段中，btree索引允许进行“前缀查询”，比如where sno like "20210901000%"，不过查询效率与前缀有关，当选择性太低的时候，依然是全表查询
在字符串字段中，后缀查询和模糊匹配时，btree索引均不支持，比如where sno like "%09010001" 和 where sno like "%0901%"

使用复合索引

复合索引查询条件必须包含左侧列
直接书写右侧列将导致数据无法查询
<>与not会导致不使用索引

优化 - 减少表与索引碎片

在数据进行频繁的增、删、改操作后，操作数据会重新组织结构。在组织过程中，可能出现一些空间的浪费以及数据组织上的不合理地方。如下两条命令就是解决该问题的：

analyze table 表名，让统计信息进行重新计算，从而让查询分析器在执行sql时基于新计算的准确的结果选择最合适的优化方案。
optimize table 表名，对于表数据进行优化，连续的数据之间可能存在“缝隙”，在大量数据操作后，“缝隙”越来越大，此外删除的数据是被空闲下来，并不会在“表空间”这个文件中将删除数据的空间进行释放，故使用该命令重新优化表空间。执行该命令会锁表，所以一定要在维护期间，否则会造成I/O阻塞

MySQL自带の分析器

开启慢SQL日志分析

首选要开启慢SQL日志功能

sql

# 设置慢sql日志状态
SET GLOBAL slow_query_log = on;

# 设置慢sql执行时间阈值，单位（秒）
SET GLOBAL long_query_time = 0.3; # 代表300毫秒

# 设置慢sql日志保存文件，路径为mysql/data
SET GLOBAL show_query_log_file = "show-sql.log"

利用explain执行计划

id：计划ID
select_type：查询类型
- SIMPLE：简单查询，指查询不包含子查询和union
- PRIMARY：复杂查询中最外侧的select
- DERIVED：包含在from子句中的子查询，mysql会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
- SUBQUERY：子查询
- UNION：连接查询
- UNION RESULT：连接查询后的结果集
table：访问的具体表名
partitions：分区表
type：表示关联类型或访问类型，即MySQL决定如何查找表中的行，执行效率排序为：
- system
- const
  - mysql能对查询的某部分进行优化并将其转化成一个常量，用于primary key 或 unique key的所有列与常数比较时，所以表最多有一个匹配行，读取一次，速度比较快
  - explain select * from (select * from film where id = 1) tmp;
- eq_ref
  - primary key 或 unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在const之外最好的联结类型了，简单的select查询不会出现这种type
  - explain select * from film_actor left join film on film_actor.film_id = film.id
- ref
  - 相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行
  - explain select * from film where name = "film1"
- fulltext
- ref_or_null
  - 类似ref，但是可以搜索值为NULL的行
  - explain select * from film where name = "film1" or name is null;
- index_merge
- unique_subquery
- index_subquery
- range
  - 范围扫描通常出现在in()，between，>， <，>=等操作中，使用一个索引来检索给定范围的行
  - explain select * from actor where id > 1;
- index
  - 和ALL一样，不同就是mysql只需扫描索引树，这通常比ALL快一些
  - explain select count(*) from film;
- ALL
  - 全表扫描，意味着mysql需要从头到尾去查找所需要的行，通常这种情况下需要增加索引来优化了
possible_keys：显示查询可能使用哪些索引来查找
key：显示mysql实际采用哪个索引来优化对该表的访问
key_len：显示mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引的哪些列
ref：显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const、func、NULL、字段名（例：film.id）
rows：是mysql估计要读取并检测的行数，注意这个不是结果集里的行数
filtered：是一个百分比的值，代表 (rows * filtered) / 100，这个结果将于前表产生交互
Extra：展示的是额外信息
- distinct:
  - 一旦mysql找到了与行相联合匹配的行，就不再搜索了
  - explain select distinct name from film left join film_actor on film.id = film_actor.film_id;
- Using index:
  - 又称为 索引覆盖，这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录，是性能高的表现
  - explain select id from film order by id;
- Using where:
  - mysql服务器将在存储引擎检索行后再进行过滤，就是先读取整行数据，再按where条件进行检查，符合就留下，不符合就丢弃
  - explain select * from film where id > 1;
- Using temporary:
  - mysql需要创建一张临时表来处理查询，出现这种情况一般是要进行优化的，首先是想到利用索引来优化
  - explain select distinct name from actor;
  - 当使用语句create index idx_name on actor(name);创建索引后，将会变成Using index
- Using filesort:
  - 1. 采用文件扫描对结果进行计算排序，效率很差
  - explain select * from actor order by name;
  - 1. 尽管在name上加了索引也不会使用Using index，这是因为对于排序，只有select字段与order by字段都被索引覆盖时才允许使用Using index，比如
  - explain select name from actor order by name;
  - 1. 再加一个字段，并且添加索引后查询，extra结果是Using index; Using filesort
  - explain select name, update_time from actor order by update_time, name;
  - create index idx_name_ut on actor(name, update_time);
  - 1. 只有必须按照复合索引的顺序来才会使用Using index，比如
  - explain select name, update_time from actor order by name, update_time;

id = 1 的那张表是查询的驱动表，id按照升序排列看，按顺序是依次的select，尽可能少的使用union，因为union result是使用的临时表，临时表没有索引

MySQL的优化笔记 ​

表设计の角度 ​

表设计原则 ​

使用反范式设计 ​

设置代理主键 ​

索引优化の角度 ​

了解 - 索引的形式 ​

了解 - MySQL常用的索引 ​

了解 - MySQL中的锁 ​

了解 - InnoDB引擎 ​

了解 - 什么情况下不会用到索引 ​

优化 - 使用索引优化排序 ​

优化 - 删除冗余索引 ​

优化 - SQL编写 ​

优化 - 减少表与索引碎片 ​

MySQL自带の分析器 ​

开启慢SQL日志分析 ​

利用explain执行计划 ​