1、MySQL优化

Mysql的逻辑架构

#

连接层

#

客户端连接器（Client Connectors）：提供与MySQL服务器建立连接的支持。目前几乎支持所有主流的服务端编程技术，例如常见的 Java、C、Python、.NET等，它们通过各自API与MySQL建立连接。

服务层

#

服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。

• 系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群管理等。
• 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。
• SQL 接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。
• 解析器（Parser）：负责将请求的SQL解析生成一个解析树。然后根据一些MySQL规则进一步检查解析树是否合法。
• 查询优化器（Optimizer）：当解析树通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。
• 缓存（Cache&Buffffer）： 缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

引擎层

#

可插拔存储引擎层（Pluggable Storage Engines）：使用可插拔存储引擎架构，能够在运行的时候动态加载或者卸载这些存储引擎。存储引擎负责MySQL中的数据存储的与提取，与底层的系统文件进行交互。MySQL 存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信 ，接口屏蔽了不同的存储引擎之间的差异。现在有很多的存储引擎，常见的就是MyISAM和InnoDB。

查看MySQL中现在提供的存储引擎，在Support列中，YES表示当前版本支持这个存储引擎；DEFAULT表示该引擎是默认的引擎，即InnoDB。

show engines;

查看MySQL现在默认使用的存储引擎

show variables like '%storage_engine%';

MyISAM引擎和InnoDB引擎简单对比：

存储层

#

系统文件层（File System）：该层负责将数据库的数据和日志存储在文件系统上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志，数据文件，配置文件，pid 文件，socket 文件等。

InnoDB引擎执行顺序

#

MySQL日志

#

查询当前日志记录的状况

#

show variables like '%log%';

MySQL的日志分类

#

MySQL Server

#

• General Log（查询日志）：当客户端连接或断开连接时，服务器将信息写入此日志，并记录从客户端收到的每个 SQL 语句。
  • 使用场景：当遇到一些事务或者有关sql执行情况问题时候可以开启查看。

# 查看general log状态以及位置
show variables like '%general_log%';

# 临时开启
set global general_log=on;

• Error Log（错误日志）：启动、运行或停止mysqld时遇到的问题记录的日志。错误日志包含mysqld 启动和关闭次数的记录。它还包含诊断消息，例如服务器启动和关闭期间以及服务器运行期间发生的错误、警告和注释。例如，如果mysqld注意到需要自动检查或修复一个表，它会在错误日志中写入一条消息。
  • 使用场景：在整个mysql启动或运行出问题时候查找原因。

# 查看error log位置
show variables like 'log_error';

• Binary Log（二进制日志）：二进制日志包含描述数据库更改的事件，例如表创建操作或表数据更改（不包含数据查询）。二进制日志还包含有关每个语句花费更新数据多长时间的信息。
  • 使用场景：做数据库主从以及市面上一些数据同步工具实现原理就是通过二进制日志，做数据恢复操作需要使用二进制日志。
  • 三种文件模式
    • STATMENT（5.7以前默认）：记录的是修改SQL语句，日志文件小，节约I0，提高性能，但是准确性差，对一些系统函数不能准确复制或不能复制。
    • ROW（5.7以后默认）：记录的是每行变更的实际数据，准确性强，能准确复制数据的变更，但是日志文件大，占用较大的网络10和磁盘lO。
    • MIXED：一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

# 查看binary log状态
show global variables like '%binlog%';
# 查看binary log位置
show variables like '%log_bin%';
# 查看bin log列表
show binary logs;
# 查看正在写入的bin log
show master status;
# 清空所有bin log
reset master;
# 查看bin log内容
show binlog events in 'binlog.000001';

• Relay Log（中继日志）：主从复制过程中使用的一种日志类型，在从服务器上记录主服务器上所有的二进制日志（Binary log）的信息
  • 使用场景：查看主从复制的情况。

#查看relay log日志位置
show variables like '%relay_log%';

• Slow Query Log（慢查询日志）：慢速查询日志由执行时间超过指定时间（long_query_time）并且查询扫描行数大于（min_examined_row_limit）的 SQL 语句组成。
  • 使用场景：开启后排查系统执行时间过长的SQL。默认是超过十秒的查询。

# 查看slow query log位置以及状态
show variables like '%query_log%';

# 临时开启
set global slow_query_log=on;

# 查看sql超时时间
SHOW VARIABLES LIKE '%long_query_time%';

# 设置sql超时时间
set global long_query_time=3;

InnoDB Engine

#

慢查询日志

#

默认没有开启

临时开启，重启后失效

#

SET GLOBAL slow_query_log=on; # 开启了慢查询日志
SET GLOBAL long_query_time=3; # 设置阈值3秒
SHOW VARIABLES LIKE '%slow_query_log%';

永久开启

#

[mysqld]
# Slow logging.
slow-query-log=1
slow_query_log_file=/var/lib/mysql/slow_query.log
long_query_time=3

测试

#

SELECT SLEEP(4);

执行后，可以在/var/lib/mysql/slow_query.log中看见慢查询日志

Explain

#

可以模拟优化器执行SQL语句，从而知道Mysql是如何处理你的SQL语句的，分析你的SQL语句或者表结构的性能瓶颈

id

#

验证表的读取和加载顺序，select查询的序列号，包含一组数字，表示查询中，执行select子句或操作表的顺序

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

select_type

#

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

type

#

• System
  • 表只有一行记录(等于系统表)，这是const类型的特例，平时不会出现，可以忽略不计
• const
  • 表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，Mysql就能将该查询转换为一个常量
• eq_ref
  • 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引
• ref
  • 非唯一性索引扫描，返回匹配某个单独值的所有行，简单说，用索引查出了多条记录
• range
  • 只检索给定范围的行，使用一个索引来选择行，key列显示是用来那个索引，一般就是在where句中出现了between, < , > , in等的查询
• index
  • Full Index Scan全索引扫描 ,index只遍历索引树，通常比All快，有些情况，索引会被加载到内存中。读索引肯定比权标扫描要快
• All
  • Full Table Scan 全表扫描

查询效率的高低

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

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

possible_keys&key

#

possible_keys

#

显示可能应用在这张表中的索引，一个或多个

查询设计到的字段上若存在索引，则该索引将被列出。但不一定被查询实际使用。

key

#

实际使用到的索引，如果为null，则没有使用索引

查询中若是用了覆盖索引，则该索引仅出现在key列表中

key_len

#

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

Key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref

#

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

rows

#

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

每张表有多少行被优化器查询

extra

#

包含不适合在其他列中显示，但是十分重要的信息

• Using fileSort文件内排序
  • 在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序
• using temporary内部临时表
  • 需要把数据先拷贝到临时表，最后再删除。也非常的慢，最好不要发生。如果要发生，要确保数据量
  • 使用临时表保存中间结果，Mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by，往往见于统计分析
• using index
  • select操作中使用了覆盖索引，Covering Index，避免访问了表的数据行，效率高
  • 如果同时出现using where ,表明索引被用来执行索引键值的查找
• using where
  • 使用了条件查询
• using join buffer
  • 使用了链接缓存
• impossible where
  • where子句的值总是false，不能用来获取任何元素
• Using index condition
  • 查询的列不全在索引中，where条件中是一个前导列的范围

索引优化

#

单表

#

例如，有商品表goods

执行sql语句，查询所有状态为1且价格大于20的商品，并对库存进行排序

EXPLAIN
SELECT * FROM goods WHERE gstate=1 AND gprice>20 ORDER BY gcount

发现结果为，此时不仅类型为全表扫描，而且进行了文件内排序

所以，建立索引，因为查询使用到的字段为gprice，gcount，gstate，所以添加这三个字段的联合索引

CREATE INDEX goods_idx_all ON goods(gstate,gprice,gcount)

再次执行sql语句

EXPLAIN
SELECT * FROM goods WHERE gstate=1 AND gprice>20 ORDER BY gcount

发现此时，虽然命中索引了，而且type也为range，但是还是进行了文件内排序

如果将sql语句中的范围查询改为固定常量

EXPLAIN
SELECT * FROM goods WHERE gstate=1 AND gprice=20 ORDER BY gcount

发现结果正常，没有文件内排序了，所以是范围查询引起的文件内排序，原因是：Btree的工作原理，在创建索引的时候，会先对gstate进行排序，如果gstate相同，对gprice进行排序，如果gprice相同，对gcount进行排序，而gprice处于联合索引中间的位置，如果进行范围查询，也就是range，那么范围查询字段后面的索引将失效

解决方法是

需要范围查询的字段，不参与联合索引的创建

多表

#

EXPLAIN
SELECT * FROM orders LEFT JOIN goods ON orders.git=goods.git

执行结果，orders表，进行了全表扫描，而goods表，因为有主键索引，索引命中

索引的建立规则

如果使用left join，那么关联字段的索引建立在右表上，right join，索引建立在左表上

索引失效的解决

#

1、最好全值匹配，索引怎么建立的，就怎么用，使用and连接的查询语句，可以不与索引顺序相同，因为sql优化器会自动进行优化

2、最佳左前缀法，查询时，要从索引建立的最左前列（第一列）开始，不可以跳过中间的列，例如

建立索引

CREATE INDEX goods_idx_all ON goods(gcount,gstate,gprice)

只查询前两列，gcount、gstate，结果正常

EXPLAIN
SELECT * FROM goods WHERE gcount=20 AND gstate=1

只查询第一列，gcount，结果正常

EXPLAIN
SELECT * FROM goods WHERE gcount=20

只查询第二列和第三列，gstate、gprice，进行了全表扫描，因为中间跳过了索引的第一列

EXPLAIN
SELECT * FROM goods WHERE gstate=1 AND gprice=35

只查询第一列和第三列，gcount、gprice，虽然使用了索引，但是索引的长度只使用了一个索引，也就是第一列的索引，并没有使用第三列的索引

EXPLAIN
SELECT * FROM goods WHERE gcount=20 AND gprice=35

3、不在索引列上做任何的操作（计算、函数、类型转换），会导致索引失效，全表扫描

4、不在索引列查询条件前面使用范围查询（>，<，between and），也就是范围查询后的索引全部失效

5、查询结果尽量覆盖索引，也就是，尽量只查询建立索引的字段，少使用select *

6、在MySQL中使用除等于=以外的运算符，会导致索引失效，在MySQL8以后，查询计划的type为all

7、is null和is not null会导致索引失效

8、like中通配符位置除了在右边，其他都会索引失效，即like '%M'、like '%M%'

9、字符串不加单引号，会索引失效，MySQL引擎会进行类型转换，和第三条意思一样

10、使用or连接，会导致索引失效