Mysql 调优

Mysql 调优

1. 调优金字塔

1. 架构调优：通过实际的业务情况，选择合适的技术选型

• 是否可以把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做

• 是否需要采用分布式

• 是否数据库读写压力大，需要读写分离

• 传输的数据是否允许丢失

2. Mysql 调优：

• 表结构设计是否合理

• SQL语句优化是否足够，该添加的索引是否都添加了，是否可以剔除多余的索引

3. 硬件和OS调优：

• 非 DBA 能想到的调整就是 SSD 盘比用机械硬盘更好

• DBA 级别考虑的至少包括了，使用什么样的磁盘阵列（ RAID ）级别、是否可以分散磁盘 IO、是否使用裸设备存放数据，使用哪种文件系统（ 目前比较推荐的是 XFS ），操作系统的磁盘调度算法选择，是否需要调整操作系统文件管理方面比如 atime 属性等等

2. 查询优化

2.1 查询语句不需要的记录

# 表示从第10000条开始查询20条数据
select * from user limit 100000,20

这条 sql 语句，需要 mysql 遍历 100000 数据只会，才能返回结果

相对于添加 where 筛选和 从 0 开始更耗时间

select * form user where name = "Sakura" limit 100000,20
select * from user limit 0,20

2.2 总是取出全部列

# 开发过程中,尽量不要查询全部的列
select * from user where....

# 应该写需要的列
selct id,name form where....

同时在三星索引中，索引中的列包含在查询语句中则获得三星 （ 查询星 ）

2.3 重复查询相同的数据

如果需要反复查询的相同数据，就可以把这些数据放到 Redis 缓存中

3. 慢查询

慢查询日志，顾名思义，就是查询花费大量时间的日志，是指 mysql 记录所有执行超过 long_query_time 参数设定的时间阈值的 SQL 语句的日志

-- 查询慢查询日志开关
show variables like 'slow_query_log'

-- 设置慢查询日志开关
set global slow_query_log = 1;

-- 慢查询配置
show variables like 'long_query_time'

-- 查看慢查询日志文件的位置
show VARIABLES like '%slow_query_log_file%';

-- 慢查询没有用索引
-- 打开就意味着：一个 sql 语句如果没有使用如何索引，也会记录在慢查询日志里里面
show VARIABLES like '%log_queries_not_using_indexes%';

3.1 慢查询日志格式

慢查询日志中的一段，从#time 开始，到一个具体的 sql 语句

Time： 查询的执行时间

User：用户

Query_Time：查询花费的时间

Lock_Time：获得锁的时间，

一般将Lock_Time 和Query_Time相加作为 Sql 的执行时间就是下面的set timestamp=...

Rows_examined: 扫描的行数

Rows_sent：扫描的结果

3.2 mysqldumpslow 命令

对 mysql 进行分析和过滤

# 以获得结果的行数进行排序,展示10条
mysqldumpslow -s r -t 10 sentosvm-slow 4cd42188c079-slow.log

4. 执行计划

执行计划就是 Mysql 是如何处理和执行一条查询语句的

语法: 在 SQL 查询的前面加上explain关键字

explain select * from user_basic;

id ： 在一个大的查询语句中每个SELECT关键字对应一个唯一的 id

select_type ： SELECT关键字对应的那个查询的类型

table ：表名

partitions ：匹配的分区信息

type ：针对单表的访问方法

possible_keys ：可能用到的索引

key ：实际上使用的索引

key_len ：实际使用到的索引长度

ref ：当使用索引列等值查询时，与索引列进行等值匹配的对象信息

rows ：预估的需要读取的记录条数

filtered ：某个表经过搜索条件过滤后剩余记录条数的百分比

Extra ：—些额外的信息

4.3 table

这条查询语句里包含了多少张表

4.4 partition

和分区表有关，一般情况下我们的查询语句的执行计划 的 partitions 列的值都是 NULL。

4.5 type

type 表明了这个访问方法/访问类型(本次的查询类型)是个什么东西，是较为重要的一个指标，结果值从最好到最坏依次是：

system>const>eq_ref>ref>range>index>ALL

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

• system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。

如果将存储引擎改为 InnoDB，那么 type 的值就会变为 All

• const

就是当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 const。因为只匹配一行数据，所以很快

EXPLAIN
SELECT * FROM s1 WHERE id = 716;

• eq_ref

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的〈如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较)，则对该被驱动表的访问方法就是 eq_ref。

(驱动表与被驱动表： A表和B表join连接查询，如果通过A表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到B表中查询数据，然后合并结果。那么我们称A表为驱动表，B**表为被驱动表)

EXPLAIN
SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

如图将 s2 表中的结果集过滤到 s1 表中查询数据，所以 B 表为驱动表，A 表为被驱动表

• ref

当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref

本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他属于查找和扫描的混合体

EXPLAIN
SELECT * FROM s1 WHERE order_no = 'a';

• range

如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法，一般就是在你的where语句中出现了between、<、>、in等的查询。

EXPLAIN
SELECT * FROM s1 WHERE order_no IN ('a', 'b', 'c');

EXPLAIN
SELECT * FROM s1 WHERE order_no > 'a' AND order_no < 'b';

• index

使用覆盖索引 ( 联合索引 ) 的时候，type 为 index

# 定义了 insertime,exporttime,ordertime的联合索引
EXPLAIN
SELECT insert_time FROM s1 WHERE expire_time = '2021-03-22 18:36:47';

• all

最熟悉的全表扫描，将遍历全表以找到匹配的行

EXPLAIN
SELECT * FROM s1;

4.6 possible_key 和 key

possible_keys : 表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，

key : 实际用到的索引有哪些，如果为NULL，则没有使用索引。

4.7 key_len

key_len：表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，计算方式是这样的：

EXPLAIN
SELECT * FROM s1 WHERE id = 718; # key_len = 8
# 由于id列的类型是bigint，并且不可以存储NULL值，所以在使用该列的索引时key_len大小就是8。

EXPLAIN
SELECT * FROM s1 WHERE order_no = 'a'; # key_len = 152
# 由于order_no列的类型是VARCHAR(50)，所以该列实际最多占用的存储空间就是50*3字节，又因为该列是可变长度列，所以key_len需要加2，所以最后ken_len的值就是152。

key_len 列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列(复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用)

4.8 rows

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的rows列就代表预计扫描的索引记录行数。比如下边两个个查询:

EXPLAIN
SELECT * FROM s1 WHERE order_no > 'z'; // rows = 1

EXPLAIN
SELECT * FROM s1 WHERE order_no > 'a'; # rows = 19340

4.8 filtered

查询优化器预测有多少条记录满⾜其余的搜索条件

对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，比方说下边这个查询:

4.9 Extra

Extra 列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句

5. 查询优化器

一条 SQL 语句在 Mysql 中的执行过程:

1. 如果是查询语句（ select 语句），首先会查询缓存是否已有相应结果，有则返回结果，无则进行下一步（如果不是查询语句，同样调到下一步）

2. 解析查询，创建一个内部数据结构（解析树），这个解析树主要用来 SQL 语句的语义与语法解析；

3. 优化：优化 SQL 语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等。

6. 索引使用策略

6.1 不在索引列上做任何操作

• 表达式

# 假设id有主键索引,这个查询就无法激活主键索引
EXPLAIN SELECT * FROM order_exp WHERE id + 1 = 17;

• 函数

# 查询插入时间在去年的记录
EXPLAIN SELECT * from order_exp WHERE YEAR(insert_time)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));

因为在索引列上用了YEAR 函数，所以索引没有被利用

使用另一种写法，并且返回的字段包含联合索引字段，就可以解决

EXPLAIN SELECT insert_time  from order_exp WHERE insert_time BETWEEN str_to_date('01/01/2021', '%m/%d/%Y') and str_to_date('12/31/2021', '%m/%d/%Y');

6.2 尽量全值匹配

建立了联合索引列后，如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，

# 一个联合索引包含三个字段 insert_time order_status  expire_time
# 使用如下查询语句也会触发索引
EXPLAIN select * from order_exp where insert_time='2021-03-22 18:34:55' and order_status=0 and expire_time='2021-03-22 18:35:14';

如果顺序不一样也没关系，查询优化器会根据定义的索引进行优化调整查询语句

6.3 尽量左前缀匹配

建立了联合索引之后，要遵循最左前缀匹配原则：指的是查询从索引的最左前列开始并且不跳过索引中的列

# 可以触发索引
EXPLAIN select * from order_exp where insert_time='2021-03-22 18:23:42' and order_status=1;

# 缩影列不在左签子
EXPLAIN SELECT * FROM order_exp WHERE order_status=1;

因为B+树的数据页和记录先是按照 insert_time 列的值排序的，在 insert_time 列的值相同的情况下才使用order_status 列进行排序，也就是说 insert_time 列的值不同的记录中 order_status 的值可能是无序的。而现在你跳过 insert_time 列直接根据 order_status 的值去查找，怎么可能呢？expire_time 也是一样的道理，那如果我就想在只使用expire_time的值去通过B+树索引进行查找咋办呢？这好办，你再对expire_time列建一个B+树索引就行了。

但是需要特别注意的一点是，如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列。比方说联合索引u_idx_day_status中列的定义顺序是 insert_time, order_status, expire_time，如果我们的搜索条件中只有insert_time和expire_time，而没有中间的order_status，

EXPLAIN select * from order_exp where insert_time='2021-03-22 18:23:42' and expire_time='2021-03-22 18:35:14';
# key_len,只有5，说明只有insert_time用到了，其他的没有用到

6.4 范围条件放最后

这条也是对于联合索引来说的，所有记录都是按照索引列的值从小到大的顺序排好序的，而联合索引则是按创建索引时的顺序进行分组排序

# 用了一个字段的范围条件,keys_lens可以出发 orderexp_cup 和 insert_time
EXPLAIN select * from order_exp_cut where insert_time>'2021-03-22 18:23:42' and insert_time<'2021-03-22 18:35:00';

对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引：

# 只有insert_time这一个字段用到了联合索引
select * from order_exp_cut 
where 
	insert_time>'2021-03-22 18:23:42' 
and insert_time<'2021-03-22 18:35:00' 
and order_status > -1;

所以对于一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找：

# 可以用到所有联合索引,因为左边的列都是精确匹配,最右边的列才会范围查找
EXPLAIN select * from order_exp_cut
where 
	insert_time='2021-03-22 18:34:55' 
and order_status=0 
and expire_time>'2021-03-22 18:23:57' 
and expire_time<'2021-03-22 18:35:00' ;

中间有范围查询会导致后面的列全部失效，无法充分利用这个联合索引：

EXPLAIN select * from order_exp_cut
where 
	insert_time='2021-03-22 18:23:42' 
and order_status>-1 
and expire_time='2021-03-22 18:35:14';