Day1《Effective MySQL之SQL语句最优化》阅读笔记

2017-05-04

第一章 DBA五分钟速成

1.1 识别性能问题

用户报告应用程序太慢，在确定不存在物理系统资源瓶颈后（CPU，IO，MEM飙升），就应该把注意力转向MySQL数据库。

1.1.1 寻找运行缓慢的SQL语句

mysql> show full processlist\G
*************************** 1. row ***************************
     Id: 6
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show full processlist
1 row in set (0.00 sec)

从Time列可以读出info中的SQL具体运行时间。

1.1.2 确认低效查询

发现潜在的低效查询后，首先确认这个查询是否每次重复执行都很缓慢。需要验证这个低效查询并不是由于锁定或者系统瓶颈等因素导致的个别现象。

运行SQL语句并记录执行时间

直接在命令行里运行select语句是最简单的方法。
生成查询执行计划（QEP）

使用EXPLAIN。大多数情况下EXPLAIN并不运行实际的SQL查询，然而优化器需要执行这条SQL语句的一部分来决定如何构造QEP时也有例外，例如在FROM字句中使用派生表（select_type中为DERIVED）。

1.2 优化查询

1.2.1 不应该做的事

在毫无头绪可循的时候，可能会想到在这个基于where语句的表上添加一个索引。

在没有进一步验证的情况下，千万不要在生产环境中这样做

如果添加索引执行时间很长，会阻塞其它如select的操作

1.2.2 确认优化

再次执行，看加索引后时间是否有改善。

1.2.3 正确的方式

决定添加索引前，首先验证表的结构，然后确认表大小：

1 2	SHOW CREATE TABLE sss\G SHOW TABLE STATUS like 'sss'\G

第二章基本的分析命令

2.1 EXPLAIN命令

EXPLAIN产生的QEP可以获得很多可能被优化器考虑到的访问策略细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

2.1.1 EXPLAIN PARTITIONS命令

用于满足在partitions列中的查询的特定表分区提供附加信息。

2.1.2 EXPLAIN EXTENDED命令

提供额外的filtered列。EXTENDED关键字还能解释一些其它问题，通过SHOW WARNINGS查看。

2.2 SHOW CREATE TABLE命令

查看表，列，索引信息。

还可以通过mysqldump -u user -p –no-data > schema.sql查看。

2.3 SHOW INDEXES命令

查看索引类型和索引基数。

重点关注输出中的CARDINALITY这一值，代表索引中每一列唯一值的数量的估计值。

2.4 SHOW TABLE STATUS命令

查看数据库表的底层大小以及表结构，包括存储引擎类型，版本，数据和索引大小，行的平均长度以及行数。

2.5 SHOW STATUS命令

SHOW [GLOBAL|SESSION] STATUS 查看MySQL服务器的当前内部状态信息。

2.6 SHOW VARIABLES命令

查看MySQL系统变量的当前值。