一条查询SQL语句是如何执行的?
1、连接
默认的端口是3306,客户端连接服务端的方式有很多。
可以是同步的也可以是异步的,可以是长连接也可以是短连接,可以是TCP也可以是Unix Socket, MySQL有专门处理连接的模块,连接的时候需要验证权限。
怎么查看有多少个连接
SHOW GLOBAL STATUS LIKE 'Thread%'
字段 | 含义 |
---|---|
Threads_cached | 缓存中的线程连接数 |
Threads_connected | 当前打开的连接数 |
Threads_created | 为处理连接创建的线程数 |
Threads_running | 非睡眠状态的连接数,通常指并发连接数 |
参数级别说明:
MySQL中的参数(变量)分为session和global级别,分别是在当前会话中生效和全局生效。
所以有些通过sql语句修改的内容,重启服务后会自动改回默认值。
客户端每产生一个连接或者一个会话, 在服务端就会创建一个线程来处理。 反过来,如果要杀死会话,就是Kill线程。
既然是分配线程的话,保持连接肯定会消耗服务端的资源。MySQL会把那些长时间不活动的(SLEEP) 连接自动断开。
2、查询缓存
MySQL内部自带了一个缓存模块。
有一张2000万行数据的表,没有索引,如果我两次执行一模一样的 SQL语句,第二次会不会变得很快?
show variables like 'query_cache%';
MySQL自带的缓存的应用场景有限,第一个是它要求SQL语句必须一模一样,中间多个空格,字母大小写不同都被认为是不同的的SQL.
第二个是表里面任何一条数据发生变化的时候,这张表所有缓存都会失效,所以对于有大量数据更新的应用,也不适合。
所以缓存这块,我们还是交给ORM框架(比如MyBatis默认开启了一-级缓存) ,或者独立的缓存服务,比如Redis来处理更合适。
在MySQL 8.0中,查询缓存已经被移除了。
3、词法解析和预处理器
为什么一条 SQL语句能够被识别呢?
假如随便执行一个字符串,服务器报了一个1064的错,它是怎么知道我输入的内容是错误的?
这个就是MySQL的解析器和预处理模块。
这一步主要做的事情是对语句基于SQL语法进行词法和语法分析和语义的解析。
3.1词法解析
你说的是中国话写的是中文我才能懂!
词法分析就是把一个完整的SQL语句打碎成一个个的单词。
比如一个简单的SQL语句:
select name from user_innodb where id = 1
它会打碎成8个符号,每个符号是什么类型,从哪里开始到哪里结束。
所以在回话窗中输入多条语句,点击运行是会报错 因为没加分号,会认为是一句话
select name from user_innodb where id = 1
select name from user_innodb where id = 2
select name from user_innodb where id = 3
3.2语法解析
第二步就是语法分析,语法分析会对SQL做一些语法检查,比如单引号有没有闭合,然后根据MySQL定义的语法规则,根据SQL语句生成一个数据结构。这个数据结构我们把它叫做解析树
关键字 | 字段 | 关键字 | 表 | 关键字 | 条件 |
---|---|---|---|---|---|
select | user | from | user | where |
3.3预处理器
问题:如果我写了一个词法和语法都正确的SQL,但是表名或者字段不存在,会在哪里报错?是在数据库的执行层还是解析器?
实际上还是在解析的时候报错,解析SQL的环节里面有个预处理器。
它会检查生成的解析树,解决解析器无法解析的语义。比如,它会检查表和列名是否存在,检查名字和别名,保证没有歧义。预处理之后得到一个新的解析树。
4、查询优化器
得到解析树之后,是不是执行SQL语句了呢?
这里我们有一个问题,一条SQL语句是不是只有一种执行方式? 或者说数据库最终执行的SQL是不是就是我们发送的SQL?
这个答案是否定的。一条SQL语句是可以有很多种执行方式的,最终返回相同的结果,他们是等价的。但是如果有这么多种执行方式,这些执行方式怎么得到的?最终选择哪一种去执行?根据什么判断标准去选择?
这个就是MySQL的查询优化器的模块 。
查询优化器的目的就是根据解析树生成不同的执行计划(Execution Plan),然后选择一种最优的执行计划,MySQL 里面使用的是基于开销(cost) 的优化器,那种执行计划开销最小,就用哪种。
MySQL的优化器能处理哪些优化类型呢?
举两个简单的例子:
1、当我们对多张表进行关联查询的时候,以哪个表的数据作为基准表。
2、有多个索引可以使用的时候,选择哪个索引。
实际上,对于每一引种数据库来说,优化器的模块都是必不可少的,他们通过复杂的算法实现尽可能优化查询效率的目标。
但是优化器也不是万能的,并不是再垃圾的SQL语句都能自动优化,也不是每次都能选择到最优的执行计划,大家在编写SQL语句的时候还是要注意。
优化完之后,得到一一个什么东西呢?优化器最终会把解析树变成一个查询执行计划,查询执行计划是一个数据结构。
explain select name from user where id = 1
如果要得到详细的信息,还可以用FORMAT=JSON。
explain FORMAT=JSON select name from user where id = 1
5、存储引擎
比如这两张表,都是二维的存储结构,表面上看起来是一模一 样的。
但是他们的表类型是不一样的。
innodb:
CREATE TABLE `user_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`gender` tinyint(1) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8mb4;
myisam:
CREATE TABLE `user_myisam` (
`id` bigint(64) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
这里我们有几个问题:
1、表类型是怎么选择的? 可以修改吗?
2、MySQL为什么支持这么多存储引擎呢?一种还不够用吗?
3、这些不同的存储引擎,到底有什么区别?
试想一下:
如果我有一张表,需要很高的访问速度,而不需要考虑持久化的问题,是不是要把数据放在内存?
如果一张表,是用来做历史数据存档的,不需要修改,也不需要索引,那它是不是要支持数据的压缩?
如果一张表用在读写并发很多的业务中,是不是要支持读写不干扰,而且要保证比较高的数据一致性呢?
功能 | MylSAM | MEMORY | InnoDB | Archive |
---|---|---|---|---|
存储限制 | 256TB | RAM | 64TB | None |
支持事务 | No | No | Yes | No |
支持全文索引 | Yes | No | No | No |
支持树索引 | Yes | Yes | Yes | No |
支持哈希索引 | No | Yes | No | No |
支持数据缓存 | No | N/A | Yes | No |
支持外键 | No | No | Yes | No |
MYISAM:
应用范围比较小。表级锁定限制了读/写的性能,因此在Web和数据仓库配置中,它通常用于只读或以读为主的工作。
特点:
支持表级别的锁(插入和更新会锁表)。不支持事务。
拥有较高的插入(insert) 和查询(select) 速度。
存储了表的行数(count 速度更快)。
适合:
只读之类的数据分析的项目。
InnoDB :
mysq| 5.7中的默认存储引擎。InnoDB 是一个事务安全(与ACID兼容)的MySQL存储引擎,它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB 行级锁(不升级为更粗粒度的锁)和Oracle风格的一引致非锁读提高了多用户并发性和性能。InnoDB 将用户数据存储在聚集索引中,以减少基于主键的常见查询的I/O。为了保持数据完整性,InnoDB还支持外键引用完整性约束。
特点:
支持事务,支持外键,因此数据的完整性、一致性更高。
支持行级别的锁和表级别的锁。
支持读写并发,写不阻塞读(MVCC)。
特殊的索引存放方式,可以减少I0,提升查询效率。
适合:
经常更新的表,存在并发读写或者有事务处理的业务系统。
如果对数据一致性要求比较高,需要事务支持,可以选择InnoDB。
如果数据查询多更新少,对查询性能要求比较高,可以选择MylSAM。
如果需要一个用于查询的临时表,可以选择Memory。
如果所有的存储引擎都不能满足你的需求,并且技术能力足够,可以根据官网内部手册用C语言开发一个存储引擎
6、执行引擎
存储引擎分析完了,它是我们存储数据的形式,那是谁使用执行计划去操作存储引擎呢?
这就是我们的执行引擎,它利用存储引擎提供的相应的API来完成操作。
为什么我们修改了表的存储引擎,操作方式不需要做任何改变?
因为不同功能的存储引擎实现的API是相同的。最后把数据返回给客户端。
为什么能支持这么多存储引擎,还能自定义存储引擎,表的存储引擎改了对Server访问没有任何影响,就是因为大家都遵循了一定了规范,提供了相同的操作接口。
每个存储引擎都有自己的服务。
show engine innodb status;
这些存储引擎用不同的方式管理数据文件,提供不同的特性,但是为上层提供相同的接口。
MYSQL架构总结
1、连接层
我们的客户端要连接到MySQL服务器3306端口,必须要跟服务端建立连接,那么管理所有的连接,验证客户端的身份和权限,这些功能就在连接层完成。
2、服务层
连接层会把SQL语句交给服务层,这里面又包含一系列的流程 :
比如查询缓存的判断、根据SQL调用相应的接口,对我们的SQL语句进行词法和语法的解析(比如关键字怎么识别,别名怎么识别,语法有没有错误等等)。
然后就是优化器,MySQL 底层会根据一定的规则对我们的SQL语句进行优化,最后再交给执行器去执行。
3、存储引擎
存储引擎就是我们的数据真正存放的地方,在MySQL里面支持不同的存储引擎。
再往下就是内存或者磁盘。
InnoDB
这里有一个问题,操作数据的时候,每次都要从磁盘读取到内存(再返回给Server),
有没有什么办法可以提高效率?
1、缓冲池(Buffer Pool)
还是缓存的思想。把读取过的数据页缓存起来。
InnoDB设计了一一个内存的缓冲区。读取数据的时候,先判断是不是在这个内存区域里面,如果是,就直接读取,然后操作,不用再次从磁盘加载。如果不是,读取后就写到这个内存的缓冲区。
这个内存区域有个专属的名字,叫Buffer Pool。
修改数据的时候,也是先写入到buffer pool,而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候, 我们把它叫做脏页。那脏页什么时候才同步到磁盘呢?
InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘,每隔一段时间就
一次性地把多个修改写入磁盘,这个动作就叫做刷脏。
总结一下:
Buffer Pool的作用是为了提高读写的效率。
Buffer Pool默认大小是128M (134217728 字节),可以调整。
查看系统变量:
SHOW VARIABLES like "%innodb_buffer_pool%';
查看服务器状态,里面有很多跟Buffer Pool相关的信息:
SHOW STATUS LIKE '%innodb_buffer_pool%';
设置bufferpool大小 单位是字节 Session级别
SET GLOBAL innodb_buffer_pool_size=size_in_bytes
如果mysql单机部署,一般设置为内存大小的 40-80%
内存的缓冲池写满了怎么办?
InnoDB 用LRU算法来管理缓冲池(链表实现,不是传统的LRU,分成了young和old),经过淘汰的数据就是热点数据。
2、BinLog
binlog以事件的形式记录了所有的DDL和DML语句(因为它记录的是操作而不是数据值,属于逻辑日志),可以用来做主从复制和数据恢复。
跟redo log不一样,它的文件内容是可以追加的,没有固定大小限制。在开启了binlog功能的情况下,我们可以把binlog导出成SQL语句,把所有的操作重放一遍,来实现数据的恢复。
binlog的另一个功能就是用来实现主从复制,它的原理就是从服务器读取主服务器的binlog,然后执行一遍。
2.1、binlog 配置
做数据恢复的前提是必须有全量的备份!然后通过binlog日志进行恢复
binlog 默认是不开启的,需要在服务端手动配置。注意有一定的性能损耗。
是否开启 binlog
show variables like 'log_bin%';
2.2、binlog 格式
查看 binlog 格式:
show global variables like '%binlog_format%';
STATEMENT:记录每一条修改数据的 SQL 语句(减少日志量,节约 IO)。
ROW:记录哪条数据被修改了,修改成什么样子了(5.7 以后默认)。
MIXED:结合两种方式,一般的语句用 STATEMENT,函数之类的用 ROW。
Binlog 文件超过一定大小就会产生一个新的,查看 binlog 列表:
show binary logs;
大小:
show variables like 'max_binlog_size';
查看 binlog 内容
show binlog events in 'mysql-bin.000001';
附:MySQL使用binlog日志做数据恢复
https://blog.csdn.net/king_kgh/article/details/74890381
索引
第一个是索引的名称,第二个是索引的列,比如我们是要对id创建索引还是对name创建索引。后面两个很重要, -个叫索引类型。
在InnoDB里面,索引类型有三种,普通索引、唯- -索引(主键索引是特殊的唯一索引)、全文索引。
普通(Normal) :也叫非唯一索引, 是最普通的索引,没有任何的限制。
唯一(Unique) :唯一索引要求键值不能重复。另外需要注意的是,主键索引是一种特殊的唯一索引, 它还多了一个限制条件,要求键值不能为空。主键索引用primay key创建。
全文(Fulltext) :针对比较大的数据,比如我们存放的是消息内容、一篇文章, 有几KB的数据的这种情况,如果要解决like查询在全文匹配的时候效率低的问题,可以创建
1、索引使用原则
我们容易有以一个误区,就是在经常使用的查询条件上都建立索引,索引肯定不是越多越好
2、离散度
c
o
u
n
t
(
d
i
s
t
i
n
c
t
(
c
o
l
u
m
n
n
a
m
e
)
)
:
c
o
u
n
t
(
∗
)
count(distinct(column_ name)) : count(*)
count(distinct(columnname)):count(∗)
列的全部不同值和所有数据行的比例。
数据行数相同的情况下,分子越大,列的离散度就越高。
按照这个定义,name的离散度更高,还是gender的离散度更高?
简单来说,如果列的重复值越多,离散度就越低,重复值越少,离散度就越高。
所以不建议大家在离散度低的字段上建立索引。
3、联合索引最左匹配
前面我们说的都是针对单列创建的索引,但有的时候我们的多条件查询的时候,也会建立联合索引。
比如我们在user表上面,给name和phone建立了一个联合索引。
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
那么问题来了下列四条语句,那条用到了联合索引
EXPLAIN SELECT * FROM user_innodb WHERE name='公良祸' AND phone ='15006237319';
EXPLAIN SELECT * FROM user_innodb WHERE name='公良祸'
EXPLAIN SELECT * FROM user_innodb WHERE phone ='15006237319';
EXPLAIN SELECT * FROM user_innodb WHERE phone ='15006237319' and name='公良祸';
4、什么时候用不到索引?
1、索引列上使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式计算(±*/) :
explain SELECT * FROM 't2' where id+1 = 4;
2、字符串不加引号,出现隐式转换
ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
explain SELECT * FROM user_innodb where name= 136;
explain SELECT * FROM user_innodb where name = '136';
3、like 条件中前面带%
where条件中like abc%、 like %2673%、like %888都用不到索引吗?为什么?
explain select * from user_innodb where name like 'wang%';
explain select * from user_innodb where name like "%wang';
讨滤的开销大大、这个时候可以 用全文索引
4、负向查询
NOT LIKE不能:
explain select * from employees where last_name not like 'wang'
!= (<>)和NOT IN在某些情况下可以:
explain select * from employees where emp_no not in (1)
explain select * from employees where emp_no <> 1
注意跟数据库版本、数据量、数据选择度都有关系。
其实,用不用索引,最终都是优化器说了算。优化器是基于什么的优化器?
基于cost开销(Cost Base Optimizer) ,它不是基于规则(Rule-Based Optimizer),也不是基于语义。怎么样开销小就怎么来。
使用索引有基本原则,但是没有具体细则,没有什么情况一定用索引, 什么情况一定不用索引的规则。
优化总结
我们说到性能调优,大部分时候想要实现的目标是让我们的查询更快。一个查询的流程又是由很多个环节组成的,每个环节都会消耗时间。
要减少查询所消耗的时间,就要从每一个环节入手。
1、连接
第一个环节是客户端连接到服务端,连接这-块有 可能会出现什么样的性能问题?
有可能是服务端连接数不够导致应用程序获取不到连接。
比如我们遇到过的Mysql: error 1040: Too many connections的错误。这个是超过了服务端设置的最大并发连接数。
我们可以从两个方面来解决连接数不够的问题:
1、从服务端来说,我们可以增加服务端的可用连接数。
如果有多个应用或者很多请求同时访问数据库,连接数不够的时候,我们可以:
(1) 增加可用连接数,修改max_ connections的大小:
SHOW VARIABLES LIKE 'max_connections'
(2)或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是28800秒,8小时,我们可以把这个值调小。
show global variables like 'wait_timeout'; -- 非交互式超时时间,如JDBC程序
show global variables like 'interactive_timeout'; -- 交互式超时时间,如数据库工具
及时释放不活动的连接,注意不要释放连接池还在使用的连接
2、从客户端来说,可以减少从服务端获取的连接数。如果我们想要不是每一次执行SQL都创建一个新的连接, 应该怎么做?
这个时候我们可以引入连接池,实现连接的重用。
常见的数据库连接池有老牌的DBCP和C3P0、阿里的Druid、Hikari (Spring Boot2.x版本默认的连接池)。
2、缓存
在系统里面有一些很慢的查询,要么是数据量大,要么是关联的表多,要么是计算逻辑非常复杂,这样的查询每次会占用连接很长的时间。
所以为了减轻数据库的压力,和提升查询效率,我们可以把数据放到内存缓存起来,比如使用Redis。
缓存适用于实时性不是特别高的业务,例如报表数据,一次查询要 2分钟,但是一天只需要更新一次。
3、解析器
解析器,词法和语法分析,主要保证语句的正确性,语句不出错就没问题。
4、优化器
优化器的作用就是对我们的SQL语句进行优化分析,生成执行计划。
问题:在我们做项目的时候,我们的服务层每天执行了这么多SQL语句,它怎么知道哪些SQL语句比较慢呢?
第一步,我们要把SQL执行情况记录下来,用到服务端的慢查询日志。
4.1慢日志
因为开启慢查询日志是有代价的(跟binlog一样),所以它默认是关闭的:
show variables like 'slow_query%';
除了这个开关,还有一个参数,控制执行超过多长时间的SQL才记录到慢日志,默认是10秒。如果改成0秒的话就是记录所有的SQL。
show variables like '%long_query%';
4.1.1修改配置:
SESSION级别(重启失效)
set @@global.slow_query_log=l; -- 1开启,0关闭,重启后失效
set @@global.long_query_ftime=3; -- 默认10秒,另开一个窗口后才会查到最新值
my.ini
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/lib/mysql/localhost-slow.log
4.1.2日志分析
MySQL提供了mysqldumpslow的工具,在MySQL的bin目录下。
mysqldumpslow常用参数:
-s 排序方式 后面跟排序列
al 平均锁定时间
ar 平均返回记录时间
at 平均查询时间(默认)
c 计数 l 锁定时间
r 返回记录
t 查询时间
-r 反向排序,最大的在最后
-t 后面跟数字,返回行数
-a 不将所有的数字抽象为N,字符串抽象为S
-n 后面跟数字,在名称中至少有n个数字抽象为数字 -g 正则后边可以写一个正则匹配模式,大小写不敏感的
mysqldumpslow -s t-t 10 -g 'select' /var/lib/mysql/localhost-slow.log
Reading mysql slow query log from /var/lib/mysql/192-slow.log
Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[192.168.0.1]
SELECT trigger_name, event_manipulation, event_object_table, action_statement, action_timing, DEFINER FROM information_schema.triggers WHERE BINARY event_object_schema='S' AND BINARY event_object_table='S'
Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=3.0 (6), root[root]@[192.168.0.1]
SELECT * FROM `information_schema`.`columns` WHERE TABLE_NAME='S' AND TABLE_SCHEMA='S'
Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[192.168.0.1]
SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, PARTITION_METHOD, SUBPARTITION_METHOD, PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_COMMENT, NODEGROUP, TABLESPACE_NAME FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA LIKE 'S' AND NOT ISNULL(PARTITION_NAME) AND TABLE_NAME LIKE 'S' ORDER BY TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, SUBPARTITION_ORDINAL_POSITION
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@[192.168.0.1]
SELECT * FROM `t3` LIMIT N, N
Count: 1 Time=3.01s (3s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
select sleep(N)
Count代表这个SQL执行了多少次;
Time代表执行的时间,括号里面是累计时间;
Lock表示锁定的时间,括号是累计;
Rows表示返回的记录数,括号是累计。
4.2EXPLAIN 执行计划
在任何执行语句前加上 explain 关键字 就能看到执行计划
4.2.1 id
id是查询序列编号,每张表都是单独访问的,一个SELECT就会有一个序号。
id值不同:
id值不同的时候,先查询id值大的(先大后小)。
id值相同:
id值相同的时候,表的查询顺序是从上往下顺序执行。
在连接查询中,先查询的叫做驱动表,后查询的叫做被驱动表。 小表驱动大表的思想
4.2.2 select_type
示查询中每个select子句的类型
SIMPLE
简单SELECT,不使用UNION或子查询等
PRIMARY
子查询中最外层查询,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
UNION
UNION中的第二个或后面的SELECT语句
DEPENDENT UNION
UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT
UNION的结果,union语句中第二个select开始后面所有select
SUBQUERY
子查询中的第一个SELECT,结果不依赖于外部查询
DEPENDENT SUBQUERY
子查询中的第一个SELECT,依赖于外部查询
DERIVED
派生表的SELECT, FROM子句的子查询
UNCACHEABLE SUBQUERY
一个子查询的结果不能被缓存,必须重新评估外链接的第一行
4.2.3table
显示这一步所访问数据库中表名称(显示这一行的数据是关于哪张表的),有时不是真实的表名字,可能是简称,例如上面的e,d,也可能是第几步执行的结果的简称
4.2.4 type
对表访问方式,表示MySQL在表中找到所需行的方式,又称“访问类型”。
常用的类型有: ALL、index、range、 ref、eq_ref、const、system(从左到右,性能从差到好)
system
system是const的一种特例,只有一行满足条件, 对于MyISAM、Memory的表,只查询到一条记录,也是system。
const
主键索引或者唯一索引,只能查到一条数据的SQL。
eq_ ref
通常出现在多表的join 查询,被驱动表通过唯一性索引 (UNIQUE或PRIMARYKEY)进行访问,此时被驱动表的访问方式就是eq_ ref
是除const之外最好的访问类型。
小结:
以上三种system, const, eq_ ref, 都是可遇而不可求的,基本上很难优化到这个状态。
ref
查询用到了非唯一-性索引, 或者关联操作只使用了索引的最左前缀。
range
索引范围扫描。
如果where后面是between and 或<或>或>=或<=或in这些,type类型就为range。
index
Full Index Scan,查询全部索引中的数据(比不走索引要快)。
all
Full Table Scan,如果没有索引或者没有用到索引,type 就是ALL。代表全表扫描。
小结:
一般来说, 需要保证查询的type至少达到range级别,最好能达到ref.
ALL (全表扫描)和index (查询全部索引)都是需要优化的。
4.2.5 possible_ key、 key
可能用到的索引和实际用到的索引。如果是NULL就代表没有用到索引。
4.2.6 key_ len
索引的长度(使用的字节数)。跟索引字段的类型、长度有关。
不损失精确性的情况下,长度越短越好
4.2.7 filtered
这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,它是一个百分比。
如果比例很低,说明存储引擎层返回的数据需要经过大量过滤,这个是会消耗性能的,需要关注。
4.2.8 Extra
该列包含MySQL解决查询的详细信息,有以下几种情况:
Using where
不用读取表中所有信息,仅通过索引就可以获取所需数据,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary
表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询,常见 group by ; order by
Using filesort
当Query中包含 order by 操作,而且无法利用索引完成的排序操作称为“文件排序” 需要优化
Using join buffer
改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。
Impossible where
这个值强调了where语句会导致没有符合条件的行(通过收集统计信息不可能存在结果)。
Select tables optimized away
这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行
No tables used
Query语句中使用from dual 或不含任何from子句
4.3 optimizer trace
如果觉得EXPLAIN还不够详细,可以用开启optimizer trace.
SHOW VARIABLES LIKE 'optimizer trace';
set optimizer_trace='enabled=on';
select * from information schema optimizer trace\G
4.4总结:
模拟优化器执行SQL查询语句的过程,来知道MySQL是怎么处理一条SQL语句的。
通过这种方式我们可以分析语句或者表的性能瓶颈。
分析出问题之后,就是对SQL语句的具体优化。
优化案例
当然肯定不是这个
1、服务端状态分析:
如果出现连接变慢,查询被阻塞,无法获取连接的情况。
1、 重启!
2、 show processlist查看线程状态,连接数数量、连接时间、状态
3、分查看锁的状态
4、 kill有问题的线程
2、分析查询基本情况
涉及到的表的表结构,字段的索引情况、每张表的数据量、查询的业务含义。
这个非常重要,因为有的时候你会发现SQL根本没必要这么写,或者表设计是有问题的。
3、找出慢的原因
1、查看执行计划,分析SQL的执行情况,了解表访问顺序、访问类型、索引、扫描行数等信息。
2、如果总体的时间很长,不确定哪一个因素 影响最大,通过条件的增减,顺序的调整,找出引起查询慢的主要原因,不断地尝试验证。
找到原因
比如是没有走索引引起的,还是关联查询引起的,还是order by引起的。
找到原因之后:
4、对症下药
1、创建索引或者联合索引
2、改写SQL,这里需要平时积累经验,例如:
1)使用小表驱动大表
2)用join来代替子查询
- or 改成union
4)如果结果集允许重复的话,使用UNION ALL代替UNION
如果SQL本身解决不了了,就要上升到表结构和架构了。
3、表结构(冗余、拆分、not null等) 、架构优化(缓存读写分离分库分表)。
4、业务层优化
https://blog.csdn.net/chenshun123/article/details/79677037 MySQL 架构及优化原理 详解
https://gper.club/articles/7e7e7f7ff4g5agc2g66 赶集网mysql开发36军规
https://gper.club/articles/7e7e7f7ff4g5agc2g67 58到家MySQL军规升级版
https://www.cnblogs.com/joeyJss/p/11096597.html Sql优化总结