申请网站建设经费的报告,网站所有人查询,郑州自助建站软件,网站名称和网址文章目录 1. 数据库服务器的优化步骤2.查看系统性能参数3. 统计SQL的查询成本#xff1a;last_query_cost4. 定位执行慢的 SQL#xff1a;慢查询日志4.1 开启慢查询日志参数4.2 查看慢查询数目4.3 测试慢sql语句#xff0c;查看慢日志4.4 系统变量 log_output#xff0c; l… 文章目录 1. 数据库服务器的优化步骤2.查看系统性能参数3. 统计SQL的查询成本last_query_cost4. 定位执行慢的 SQL慢查询日志4.1 开启慢查询日志参数4.2 查看慢查询数目4.3 测试慢sql语句查看慢日志4.4 系统变量 log_output log-queries-not-using-indexes log_slow_admin_statements的作用4.5 慢查询日志分析工具mysqldumpslow4.6 关闭慢查询日志4.7 删除慢查询日志 5. 分析查询语句EXPLAIN5.1 简介5.2 执行计划中各字段含义5.2.1 id5.2.2 select_type5.2.3 table5.2.4 type5.2.5 possible_keys 和 key5.2.6 key_len5.2.7 ref5.2.8 rows5.2.9 Extra 5.3 实例分析 在数据库调优中我们的目标就是响应时间更快吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式 1. 数据库服务器的优化步骤
当我们遇到数据库调优问题的时候该如何思考呢这里把思考的流程整理成下面这张图。 整个流程划分成了 观察Show status 和 行动Action 两个部分。字母 S 的部分代表观察会使用相应的分析工具字母 A 代表的部分是行动对应分析可以采取的行动 可以通过观察了解数据库整体的运行状态通过性能分析工具可以让我们了解执行慢的SQL都有哪些查看具体的SQL 执行计划甚至是SQL执行中的每一步的成本代价这样才能定位问题所在找到了问题再采取相应的行动
详细解释一下这张图: 首先在S1部分需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动有可能是周期性节点的原因比如双十一、促销活动等。这样的话可以通过A1这一步骤解决也就是加缓存或者更改缓存失效策略。
如果缓存策略没有解决或者不是周期性波动的原因就需要进一步分析查询延迟和卡顿的原因。接下来进入S2这一步需要开启慢查询。慢查询可以帮我们定位执行慢的SQL语句。可以通过设置long_query_time参数定义“慢的阈值如果SQL执行时间超过了long query_time则会认为是慢查询。当收集上来这些慢查询之后就可以通过分析工具对慢查询日志进行分析。
在S3这一步骤中我们就知道了执行慢的SQL这样就可以针对性地用EXPLAIN查看对应SQL语句的执行计划或者使用show profile查看SQL中每一个步骤的时间成本。这样就可以了解SQL查询慢是因为执行时间长还是等待时间长。
如果是SQL等待时间长就进入A2步骤。在这一步骤中可以调优服务器的参数比如适当增加数据库缓冲池等。如果是SQL执行时间长就进入A3步骤这一步中需要考虑是索引设计的问题?还是查询关联的数据表过多?还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整。 如果A2和A3都不能解决问题需要考虑数据库自身的SQL查询性能是否已经达到了瓶颈如果确认没有达到性能瓶颈就需要重新检查重复以上的步骤。如果已经达到了性能瓶颈进入A4阶段需要考虑增加服务器采用读写分离的架构或者考虑对数据库进行分库分表比如垂直分库、垂直分表和水平分表等。
以上就是数据库调忧的流程思路。如果发现执行SQL时存在不规则延迟或卡顿的时候就可以采用分析工具帮我们定位有问题的SQL这三种分析工具可以理解是SQL调优的三个步骤:慢查询、EXPLAIN和SHOEW PROFILING
小结
2.查看系统性能参数
在MySQL中可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数 、执行频率 。 SHOW STATUS语句语法如下 SHOW [GLOBAL|SESSION] STATUS LIKE 参数;
一些常用的性能参数如下 Connections连接MySQL服务器的次数。 UptimeMySQL服务器的上线时间。 Slow_queries慢查询的次数。 Innodb_rows_readSelect查询返回的行数 Innodb_rows_inserted执行INSERT操作插入的行数 Innodb_rows_updated执行UPDATE操作更新的行数 Innodb_rows_deleted执行DELETE操作删除的行数 Com_select查询操作的次数。 Com_insert插入操作的次数。对于批量插入的 INSERT 操作只累加一次。 Com_update更新操作的次数。 Com_delete删除操作的次数。
演示 show status like Connections;
/*
----------------------
| Variable_name | Value |
----------------------
| Connections | 20 |
----------------------
*/
#查询服务器工怍时间
show status like uptime;
/*
-----------------------
| Variable_name | Value |
-----------------------
| Uptime | 382062 |
-----------------------
*/若查询MySQL服务器的慢查询次数则可以执行如下语句:
SHOW STATUS LIKE Slow_queries ;
/*
----------------------
| Variable_name | Value |
----------------------
| Slow_queries | 0 |
----------------------
*/慢查询次数参数可以结合慢查询日志找出慢查询语句然后针对慢查询语句进行表结构优化或者查询语句优化。再比如如下的指令可以查看相关的指令情况:
SHOW STATUS LIKE Innodb_rows_%;
/*
-----------------------------
| Variable_name | Value |
-----------------------------
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 224 |
| Innodb_rows_read | 214 |
| Innodb_rows_updated | 0 |
-----------------------------
*/3. 统计SQL的查询成本last_query_cost
一条SQL查询语句在执行前需要确定查询执行计划如果存在多种执行计划的话MySQL会计算每个执行计划所需要的成本从中选择成本最小的一个作为最终执行的执行计划。 如果想要查看某条SQL语句的查询成本可以在执行完这条SQL语句之后通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。它通常也是评价一个查询的执行效率的一个常用指标。这个查询成本对应的是SQL语句所需要读取的页的数量
CREATE TABLE student_info (
id INT(11) NOT NULL AUTO_INCREMENT,
student_id INT NOT NULL ,
name VARCHAR(20) DEFAULT NULL,
course_id INT NOT NULL ,
class_id INT(11) DEFAULT NULL,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINEINNODB AUTO_INCREMENT1 DEFAULT CHARSETutf8;如果想要查询 id900001 的记录然后看下查询成本我们可以直接在聚簇索引上进行查找
SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id 900001;
#运行结果1 条记录运行时间为 0.042s 然后再看下查询优化器的成本实际上我们只需要检索一个页即可
SHOW STATUS LIKE last_query_cost;
/*
---------------------------
| Variable_name | Value |
---------------------------
| Last_query_cost | 1.000000 |
---------------------------
*/如果要查询 id 在 900001 到 9000100 之间的学生记录呢
SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id BETWEEN 900001 AND 900100;
#运行结果100 条记录运行时间为 0.046s 然后再看下查询优化器的成本这时我们大概需要进行 20 个页的查询 SHOW STATUS LIKE last_query_cost;/*
----------------------------
| Variable_name | Value |
----------------------------
| Last_query_cost | 21.134453 |
----------------------------
*/能看到页的数量是刚才的 20 倍但是查询的效率并没有明显的变化实际上这两个 SQL 查询的时间基本上一样就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中然后再进行查找。虽然页数量last_query_cost增加了不少 但是通过缓冲池的机制并没有增加多少查询时间 。 使用场景它对于比较开销是非常有用的特别是有好几种查询方式可选的时候 SQL查询是一个动态的过程从页加载的角度来看可以得到以下两点结论: 1.位置决定效率。如果页就在数据库缓冲池中那么效率是最高的否则还需要从内存或者磁盘中进行读取当然针对单个页的读取来说如果页存在于内存中会比在磁盘中读取效率高很多。 2.批量决定效率。如果从磁盘中对单一页进行随机读那么效率是很低的差不多10ms)而采用顺序读取的方式批主对页进行读取平均一页的读取效率就会提升很多甚至要快于单个页面在内存中的随机读取。 所以说遇到I/O并不用担心方法找对了效率还是很高的。首先要考虑数据存放的位置如果是经常使用的数据就要尽量放到缓冲池中其次可以充分利用磁盘的吞吐能力一次性批量读取数据这样单个页的读取效率也就得到了提升。 4. 定位执行慢的 SQL慢查询日志
MySQL的慢查询日志用来记录在MySQL中响应时间超过阈值的语句具体指运行时间超过long_query_time值的SQL则会被记录到慢查询日志中。long_query_time的默认值为10意思是运行10秒以上(不含10秒)的语句认为是超出了最大忍耐时间值。 它的主要作用是帮助我们发现那些执行时间特别长的SQL查询并且有针对性地进行优化从而提高系统的整体效率。当数据库服务器发生阻塞、运行变慢的时候检查一下慢查询日志找到那些慢查询对解决问题很有帮助。比如一条sql执行超过5秒钟就算慢SQL希望能收集超过5秒的sql结合explain进行全面分析。 默认情况下MySQL数据库没有开启慢查询日志需要手动来设置这个参数。如果不是调优需要的话一般不建议启动该参数因为开启慢查询日志会或多或少带来一定的性能影响。 慢查询日志支持将日志记录写入文件
4.1 开启慢查询日志参数
开启slow_query_log 在使用前需要先看下慢查询是否已经开启使用下面这条命令即可:
show variables like slow_query_log;
/*
-----------------------
| Variable_name | Value |
-----------------------
| slow_query_log | OFF |
-----------------------
*/能看到slow_query_logOFF可以把慢查询日志打开注意设置变量值的时候需要使用global否则会报错: set global slow_query_logON;
再来查看下慢查询日志是否开启以及慢查询日志文件的位置
show variables like %slow_query_log%;
/*
-----------------------------------------------------
| Variable_name | Value |
-----------------------------------------------------
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/LSLNO1-slow.log |
-----------------------------------------------------
*/能看到这时慢查询分析已经开启同时文件保存在/var/lib/mysql/LSLNO1-slow.log 文件中
修改long_query_time阈值 接下来看下慢查询的时间阈值设置使用如下命令
show variables like %long_query_time%;
/*
----------------------------
| Variable_name | Value |
----------------------------
| long_query_time | 10.000000 |
----------------------------
*/这里如果想把时间缩短比如设置为 1 秒可以这样设置
#测试发现设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句
set global long_query_time 1;
show global variables like %long_query_time%;set long_query_time1;
show variables like %long_query_time%;
/*
----------------------------
| Variable_name | Value |
----------------------------
| long_query_time | 10.000000 |
----------------------------
*/补充:配置文件中一并设置参数 如下的方式相较于前面的命令行方式可以看作是永久设置的方式 修改my.cnf文件[mysqld]下增加或修改参数long_query_time、slow_query_log 和slow_query_log_file后然后重启MySQL服务器
[mysqld]
slow_query_logON#开启慢查询日志的开关
slow_query_log_file/var/lib/mysql/atguigu-slow.log#慢查询日志的目录和文件名信息
long_query_time1 #设置慢查询的阈值为1秒。超出此设定值的SQL即被记录到慢查询日志
log_outputFTLE如果不指定存储路径慢查询日志将默认存储到MySQL数据库的数据文件夹下。如果不指定文件名默认文件名为hostname-slow.log
4.2 查看慢查询数目
查询当前系统中有多少条慢查询记录
SHOW GLOBAL STATUS LIKE %Slow_queries%;
/*
----------------------
| Variable_name | Value |
----------------------
| Slow_queries | 0 |
----------------------
*/4.3 测试慢sql语句查看慢日志
试慢sql语句查看慢日志
mysql select sleep(2);
----------
| sleep(2) |
----------
| 0 |
----------
1 row in set (2.01 sec)select sleep(2);表执行暂停2秒的sql语句
然后我们打开慢日志文件也就是slow_query_log_file的值即/var/lib/mysql/LSLNO1-slow.log文件跳转到最后可以看到如下内容
# Time: 2021-02-23T11:38:25.222093Z
# UserHost: root[root] localhost [127.0.0.1] Id: 19
# Query_time: 2.000681 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp1614080303;
select sleep(2);Time:Time: 2021-02-23T11:38:25.222093Z 表该条慢sql执行的时间点
UserHost: root[root] localhost [127.0.0.1] Id: 19 表该条慢sql的连接账号为rootIP地址为127.0.0.1Id表连接ID每次连接该ID加1
Query_time: 2.000681 表执行该sql的总时间为2.000681秒
Lock_time: 0.000000 表锁表时间
Rows_sent: 1 表发送sql结果的行数只有1行
Rows_examined: 1 表执行该sql语句检查数据库的行数
4.4 系统变量 log_output log-queries-not-using-indexes log_slow_admin_statements的作用
log_output‘FILE’表示将日志存入文件默认值是’FILE’。log_outputTABLE’表示将日志存入数据库这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式配置的时候以逗号隔开即可如log_output‘FILE,TABLE’。日志记录到系统的专用日志表中要比记录到文件耗费更多的系统资源因此对于需要启用慢查询日志又需要能够获得更高的系统性能那么建议优先记录到文件
mysql show variables like %log_output%;
----------------------
| Variable_name | Value |
----------------------
| log_output | FILE |
----------------------
1 row in set (0.01 sec)log-queries-not-using-indexes未使用索引的查询也被记录到慢查询日志中可选项。如果调优的话建议开启这个选项。另外开启了这个参数其实使用full index scan的sql也会被记录到慢查询日志
mysql show variables like log_queries_not_using_indexes;
--------------------------------------
| Variable_name | Value |
--------------------------------------
| log_queries_not_using_indexes | OFF |
--------------------------------------
1 row in set (0.01 sec)log_slow_admin_statements表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志
mysql show variables like log_slow_admin_statements;
----------------------------------
| Variable_name | Value |
----------------------------------
| log_slow_admin_statements | OFF |
----------------------------------
1 row in set (0.01 sec)4.5 慢查询日志分析工具mysqldumpslow
日志分析工具mysqldumpslowmysql官方自带的只要安装了mysql就可以使用它可以用来帮助我们分析慢日志文件
在生产环境中如果要手工分析日志文件查找、分析SQL显然是个体力活MySQL提供了日志分析工具mysqldumpslow如下
rootkickseed:/home# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are--verbose verbose--debug debug--help write this text to standard output-v verbose-d debug-s ORDER what to sort by (al, at, ar, c, l, r, t), at is defaultal: average lock timear: average rows sentat: average query timec: countl: lock timer: rows sentt: query time -r reverse the sort order (largest last instead of first)-t NUM just show the top n queries-a dont abstract all numbers to N and strings to S-n NUM abstract numbers with at least n digits within names-g PATTERN grep: only consider stmts that include this string-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),default is *, i.e. match all-i NAME name of server instance (if using mysql.server startup script)-l dont subtract lock time from total time-s 是表示按照何种方式排序后面可以带如下排序方式 c: 访问次数 l: 锁定时间 r: 返回记录的行数 t: 查询时间 al:平均锁定时间 ar:平均返回记录的行数 at:平均查询时间 -t 是top n的意思即为返回前面多少条的数据
-g 后边可以写一个正则匹配模式大小写不敏感的
例子
得到返回记录行数最多的10条SQL语句行数最多排序需要 -s r 限制10条需要 -t 10
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
得到访问次数最多的10个SQL访问次数最多排序需要 -s c 限制10条需要 -t 10
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
得到按照执行时间排序的前10条且sql语句里面含有左连接的查询语句执行时间排序需要 -s t限制10条需要 -t 10
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
另外建议在使用这些命令时结合 | 和more 使用 否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
笔者以自己的慢日志为例做一次查询
mysqldumpslow -s t -t 5 LSLNO1-slow.log 表我要分析LSLNO1-slow.log文件该文件是mysql的慢日志文件得到按照执行时间从大到小排序最多5条sql语句
rootkickseed:/var/lib/mysql# mysqldumpslow -s t -t 5 LSLNO1-slow.log Reading mysql slow query log from LSLNO1-slow.log
Count: 2 Time2.00s (4s) Lock0.00s (0s) Rows1.0 (2), root[root]localhostselect sleep(N)Count: 3 Time0.21s (0s) Lock0.00s (0s) Rows25.0 (75), root[root][192.168.54.28]SHOW TABLE STATUS FROM hyj_playerCount: 1 Time0.21s (0s) Lock0.00s (0s) Rows23211.0 (23211), root[root][192.168.54.28]SELECT * FROM hyj_player.t_player_basic_data LIMIT N, NCount: 1 Time0.20s (0s) Lock0.00s (0s) Rows24211.0 (24211), root[root][192.168.54.28]SELECT * FROM t_player_basic_dataCount: 4 Time0.02s (0s) Lock0.00s (0s) Rows1.0 (4), root[root][192.168.54.28]SELECT COUNT(*) FROM t_player_basic_data WHERE playerID N从返回的结果中可以看到从最大耗时的2秒到最小耗时的0.02秒一共5条记录
4.6 关闭慢查询日志
MySQL服务器停止慢查询日志功能有两种方法 方式1永久性方式
[mysqld]
slow_query_logOFF或者把slow_query_log一项注释掉 或 删除
[mysqld]
#slow_query_log OFF重启MySQL服务执行如下语句查询慢日志功能。
SHOW VARIABLES LIKE ‘%slow%’; #查询慢查询日志所在目录
SHOW VARIABLES LIKE ‘%long_query_time%’; #查询超时时长方式2临时性方式 使用SET语句来设置。 1停止MySQL慢查询日志功能具体SQL语句如下
SET GLOBAL slow_query_logoff;SHOW VARIABLES LIKE %slow%;
/*
-----------------------------------------------------------
| Variable_name | Value |
-----------------------------------------------------------
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/LSLNO1-slow.log |
-----------------------------------------------------------
*/
#以及
SHOW VARIABLES LIKE %long_query_time%;
/*
---------------------------
| Variable_name | Value |
---------------------------
| long_query_time | 1.000000 |
---------------------------
*/2重启MySQL服务使用SHOW语句查询慢查询日志功能信息具体SQL语句如下
#在根目录下重启服务
[rootLSLNO1 mysql]# systemctl restart mysqld
mysql SHOW VARIABLES LIKE %long_query_time%;
/*重启后阈值恢复默认值10
----------------------------
| Variable_name | Value |
----------------------------
| long_query_time | 10.000000 |
----------------------------
*/4.7 删除慢查询日志
查询并调优完后该日志没有用了可以将其删除 使用SHOW语句显示慢查询日志信息具体SQL语句如下
show variables like slow_query_log%;
/*
-----------------------------------------------------
| Variable_name | Value |
-----------------------------------------------------
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/LSLNO1-slow.log |
-----------------------------------------------------
*/从执行结果可以看出慢查询日志的目录默认为MySQL的数据目录在该目录下手动删除慢查询日志文件即可
[rootLSLNO1 mysql]# rm LSLNO1-slow.log
rm是否删除普通文件 LSLNO1-slow.logy使用命令mysqladmin flush-logs 来重新生成查询日志文件具体命令如下执行完毕会在数据目录下重新生成慢查询日志文件 mysqladmin -uroot -p flush-logs slow 提示 慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意一旦执行了这个命令慢查询日志都只存在新的日志文件中如果需要旧的查询日志就必须享先备份。 5. 分析查询语句EXPLAIN
5.1 简介
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
通过EXPLAIN我们可以分析出以下结果
表的读取顺序数据读取操作的操作类型哪些索引可能使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询
使用方式如下 EXPLAIN SQL语句 EXPLAIN SELECT * FROM t1
执行计划包含的信息
5.2 执行计划中各字段含义
5.2.1 id
select查询的序列号包含一组数字表示查询中执行select子句或操作表的顺序 id的结果共有3中情况
id相同执行顺序由上至下 id不同如果是子查询id的序号会递增id值越大优先级越高越先被执行 id相同不同同时存在 如上图所示在id为1时table显示的是 ,这里指的是指向id为2的表即t3表的衍生表。
说明 关注点: id号每个号码。表示一趟独立的查询,一个sql的查询趟数越少越好 5.2.2 select_type
常见和常用的值有如下几种 分别用来表示查询的类型主要是用于区别普通查询、联合查询、子查询等的复杂查询。
SIMPLE 简单的select查询查询中不包含子查询或者UNIONPRIMARY 查询中若包含任何复杂的子部分最外层查询则被标记为PRIMARYSUBQUERY 在SELECT或WHERE列表中包含了子查询DERIVED 在FROM列表中包含的子查询被标记为DERIVED衍生MySQL会递归执行这些子查询把结果放在临时表中UNION 若第二个SELECT出现在UNION之后则被标记为UNION若UNION包含在FROM子句的子查询中外层SELECT将被标记为DERIVEDUNION RESULT 从UNION表获取结果的SELECT
5.2.3 table
指的就是当前执行的表
5.2.4 type
type所显示的是查询使用了哪种类型type包含的类型包括如下图所示的几种 从最好到最差依次是
system const eq_ref ref range index all一般来说得保证查询至少达到range级别最好能达到ref。
system 表只有一行记录等于系统表这是const类型的特列平时不会出现这个也可以忽略不计const 表示通过索引一次就找到了const用于比较primary key 或者unique索引。因为只匹配一行数据所以很快。如将主键置于where列表中MySQL就能将该查询转换为一个常量。 首先进行子查询得到一个结果的d1临时表子查询条件为id 1 是常量所以type是constid为1的相当于只查询一条记录所以type为system。eq_ref 唯一性索引扫描对于每个索引键表中只有一条记录与之匹配。常见于主键或唯一索引扫描ref 非唯一性索引扫描返回匹配某个单独值的所有行本质上也是一种索引访问它返回所有匹配某个单独值的行然而它可能会找到多个符合条件的行所以他应该属于查找和扫描的混合体。 range 只检索给定范围的行使用一个索引来选择行key列显示使用了哪个索引一般就是在你的where语句中出现between、 、、in等的查询这种范围扫描索引比全表扫描要好因为它只需要开始于索引的某一点而结束于另一点不用扫描全部索引。 index Full Index ScanIndex与All区别为index类型只遍历索引树。这通常比ALL快因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表但index是从索引中读取的而all是从硬盘读取的 id是主键所以存在主键索引all Full Table Scan 将遍历全表以找到匹配的行
5.2.5 possible_keys 和 key
possible_keys 显示可能应用在这张表中的索引一个或多个。查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询实际使用。
key
实际使用的索引如果为NULL则没有使用索引。可能原因包括没有建立索引或索引失效 查询中若使用了覆盖索引select 后要查询的字段刚好和创建的索引字段完全相同则该索引仅出现在key列表中
5.2.6 key_len
表示索引中使用的字节数可通过该列计算查询中使用的索引的长度在不损失精确性的情况下长度越短越好。key_len显示的值为索引字段的最大可能长度并非实际使用长度即key_len是根据表定义计算而得不是通过表内检索出的。
5.2.7 ref
显示索引的那一列被使用了如果可能的话最好是一个常数。哪些列或常量被用于查找索引列上的值。
5.2.8 rows
根据表统计信息及索引选用情况大致估算出找到所需的记录所需要读取的行数也就是说用的越少越好
5.2.9 Extra
包含不适合在其他列中显式但十分重要的额外信息
Using filesort九死一生 说明mysql会对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。 Using temporary十死无生 使用了用临时表保存中间结果MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。 Using index发财了 表示相应的select操作中使用了覆盖索引Covering Index避免访问了表的数据行效率不错。如果同时出现using where表明索引被用来执行索引键值的查找如果没有同时出现using where表明索引用来读取数据而非执行查找动作。 Using where 表明使用了where过滤
Using join buffer 表明使用了连接缓存,比如说在查询的时候多表join的次数非常多那么将配置文件中的缓冲区的join buffer调大一些。
impossible where where子句的值总是false不能用来获取任何元组
select tables optimized away 在没有GROUPBY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化。
distinct 优化distinct操作在找到第一匹配的元组后即停止找同样值的动作
5.3 实例分析 执行顺序1select_type为UNION说明第四个select是UNION里的第二个select最先执行【select name,id from t2】执行顺序2id为3是整个查询中第三个select的一部分。因查询包含在from中所以为DERIVED【select id,name from t1 where other_column’’】执行顺序3select列表中的子查询select_type为subquery,为整个查询中的第二个select【select id from t3】执行顺序4id列为1表示是UNION里的第一个selectselect_type列的primary表示该查询为外层查询table列被标记为,表示查询结果来自一个衍生表其中derived3中的3代表该查询衍生自第三个select查询即id为3的select。【select d1.name …】执行顺序5代表从UNION的临时表中读取行的阶段table列的 union1,4 表示用第一个和第四个select的结果进行UNION操作。【两个结果union操作】
