定义及作用

配置

1. 位置

• windows下配置文件为my.ini
• linux下配置文件位置为my.cnf

2. 开启配置

slow_query_log = 1 # 0关闭 1开启
slow_query_log_file = slow.log # 慢文件存放位置(安装目录date文件夹下)
long_query_time = 5 # 查询多久以上的算是慢

排查

Query_time: xxx # 执行时间
Lock_time: xxx # 锁定时间

分析工具

• mysqldumpslow
MySQL自带的慢查询文件解析工具，自带分析。位于bin下。

mysqldumpslow -s at -t 5 /usr/local/data/slow.log
-s at # 算出平均时间
-t 5 # top5

常见优化

1. 系统层面入手(排查sql导致cpu占用过高)

top -H # 查出性能占用最大的pid

2. mysql参数优化

# 独立表空间
Using filesort

# 设置缓存空间(连表之类的操作使用的缓存空间)
set sort_buffer_size = 256*1024*1024

3. sql优化

• 子查询变成关联查询

4. 反范式设计优化

• 针对范式化设计而言
• 为了性能和读取效率考虑，适当对数据库设计范式进行违反
• 允许适当冗余，以空间换时间(减少连表)

5. 索引优化

• explain查看索引使用情况
• 根据使用情况重写建立索引(创建时范围索引放最后)

执行计划

explain sql

• key 是否使用了索引，使用了什么索引
• key_len 是否充分使用了索引

varchar(50)的索引计算

1. 字符类型 varchar +2 char+0
2. 字符集 utf8(3) 一个字符的长度
3. 本身长度 50
4. 是否null null(+ 1) not null(+ 0)

50 * 3
varchar(50) * 一个字符3个字节 + 0(not null) + 2(varchar类型)

可以通过索引长度计算出使用了几个索引

Explain字段详解

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
row in set (0.03 sec)

1. id

• 执行顺序的标识，id大的优先执行

2. select_type

• SIMPLE简单的查询，不使用UNION或子查询等
• PRIMARY查询中包含任何复杂字部分，最外层查询会被标记为PRIMARY
• UNION中的后一个语句标记为UNION
• DEPENDENT UNION，UNION中的后一个语句，取决于外面的查询
• UNION RESULT是UNION语句的结果
• SUBQUERY子查询中的第一个SELECT
• DEPENDENT SUBQUERY，子查询中的第一个SELECT，取决于外面的查询
• DERIVED派生表的SELECT，FROM子句的子查询
• UNCACHEABLE SUBQUERY一个子查询的结果不能被缓存，必须重新评估外链接的第一行

3. table

• 这一行的数据是关于那张表的，可能是tableNamex，x代表执行的步骤

4. type

• 表示查找到这一行的方式，性能从低到高

1. ALL遍历全表找到匹配的行
2. index遍历全部索引树找到匹配的行
3. range只检索给定范围的行，使用一个索引来选择行
4. ref表的连接匹配条件，即那些列或常量被用于查找索引上的值
5. eq_ref类似ref区别在于是使用的所有是唯一索引，即多表中使用primary key或者unique key作为关联条件
6. const、system，当MySQL对查询某部分进行优化，并转换成一个常量时，使用这些类型访问。例如主键置于where中，MySQL就能把改查询转换成一个常量，system是const的特例，当查询的表只有一行情况下是system。
7. NULL执行时不用访问表或索引，例如从索引列选取最小值，可以直接通过索引查找完成。

5. possible_keys

• 指出MySQL能使用那个索引在表中找到记录，查询涉及到的字段若存在索引，则索引会被列出，但不一定会被利用到。

6.key

• MySQL决定使用的键

7. key_len

• 表示索引中使用的字节数，可通过改列计算查询中使用的索引的长度。

8. ref

• 列与索引的比较，表示上述表的连接匹配条件，即那些列或常量被用于查找索引列的值

9. rows

• 估算出结果集行数，表示MySQL根据表统计信息及索引选用情况估算找到所需记录所需要读的行数

10. Extra

• 包含MySQL解决查询的详细信息

1. Using where，不用读取表中的所有信息，通过索引就能获取所需数据