1、MySQL优化

Mysql的逻辑架构

20210606204741

连接层

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

服务层

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

引擎层

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

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

show engines;

image.png

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

show variables like '%storage_engine%';
引擎 说明
InnoDB MySQL默认的存储引擎，支持ACID事务，行锁。目前说目前最稳定的引擎。
MyISAM MyISAM版本早起版本存储引擎，无事务支持，表锁级别，5.6,5.7版本有些表还是这个引擎，系统表也存在引擎。
Memory 将所有数据存储在内存中，以便在需要快速查找非关键数据的环境中进行快速访问。这个引擎以前称为HEAP引擎。
CSV CSV表允许以CSV格式导入或转储数据，以便与读写相同格式的脚本和应用程序交换数据。因为CSV表没有索引，所以在正常操作过程中，通常将数据保存在InnoDB表中，只在导入或导出阶段使用CSV表。
Archive 紧凑的、未索引的表用于存储和检索大量很少引用的历史、归档或安全审计信息。
Blackhole MySQL在5.x系列提供了Blackhole引擎：“黑洞”，不存储数据，但会正常的记录下Binlog，而且这些Binlog还会被正常的同步到Slave上。binlog分发引擎。
NDB 分片方式实现数据冗余，分片机制，表会在集群进程之间被复制，企业版特有引擎。
MRG_MYISAM 对一系列相同的MyISAM表进行逻辑分组，并将引用为一个对象。适合于数据仓库等环境。
Federated 从多个物理服务器创建一个逻辑数据库，类似dblink。
Example 该引擎在MySQL源代码中作为示例引擎，演示如何开始编写新的存储引擎。

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

MyISAM引擎 InnoDB引擎
外键 不支持 支持
事务 不支持 支持
行表锁 表锁，不适合高并发 行锁，适合高并发
缓存 只缓存索引 缓存索引和真实数据
表空间 小 大
关注点 性能，偏读 事务
默认安装 是 是

存储层

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

InnoDB引擎执行顺序

image-20240919102059000

MySQL日志

查询当前日志记录的状况

show variables like '%log%';

MySQL的日志分类

MySQL Server

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

# 临时开启
set global general_log=on;

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

# 查看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日志位置
show variables like '%relay_log%';

# 查看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中看见慢查询日志

image-20240919154348408

Explain

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

image-20210917140507497

id

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

select_type

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

select type 解释 测试sql
simple 简单的select select * from tbl_student
简单的查询，查询中不包含子查询或者UNION。
primary 需要union或者子查询 EXPLAIN #年龄大于平均年龄的同学
SELECT * FROM tblstudent WHERE stuage>(
SELECT AVG(stuage) FROM tblstudent t1
)
查询中若包含任何复杂的子部分，最外层则被标记为PRIMARY,一般Primary也是最后被加载的那个
subquery 在select或者Where包含的子查询 上面的SQL就有subquery
derived 派生表 在From列表中，包含的子查询被标记为derived,MYSQL会递归执行这些子查询，把结果放在哪临时表（变量内存交换）里面。
select * from (select * from tb_student) t
#省市区 注意t1是一个虚表
EXPLAIN
select t2.* from
(SELECT cid FROM zone WHERE zname='未央区') t1,city t2
where t1.cid=t2.cid
union union 如第二个select出现在union之后，则被标记为UNION
若union包含在From子句的子查询中 ，外层select将被标记为Derived select * from tb_student union select * from tb_student
union result union结果集 两种结果的合并，临时表
select * from tb_student union select * from tb_student
depend subquery 类似depend union select (select name from test.tb_student a where a.id=b.id) from test.tb_student b
dependent union 查询与外部相关 （mysql优化器会将in优化成exists） select * from tb_student where id in(select id from tb_student union select id from tb_student)
select * from tb_student a where EXISTS (select 1 from tb_student where id = a.id union select id from tb_student where id = a.id)

type

查询效率的高低

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

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

索引优化

单表

例如，有商品表goods

image-20210919175422609

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

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

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

image-20210919175558224

所以，建立索引，因为查询使用到的字段为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，但是还是进行了文件内排序

image-20210919180048008

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

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

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

image-20210919180259367

解决方法是

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

多表

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

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

image-20210919195704280

索引的建立规则

如果使用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

image-20210919200827918

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

EXPLAIN
SELECT * FROM goods WHERE gcount=20

image-20210919200916751

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

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

image-20210919201314148

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

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

image-20210919201546952

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连接，会导致索引失效

Contact

Information

Email : super@ygang.top

QQ : 929880282

Explanation

  • 本站以及(ygang.top)下所有二级域名网站所提供的资源（文档、软件等），版权均归作者所有。
  • 本站资源仅供学习与参考，请勿使用于商业用途。如需商业用途，请与作者联系。
  • 本站在建设中引用了因特网上的资源并对有明确来源的标明了出处，版权归原作者及网站所有。如有内容侵犯了您的权益，请联系我们。
  • 未经本站明确许可，任何人不得盗链本站资源。
  • 转载本站原创资源，请标注来自本站。