定义及作用
配置
- 位置
-
windows下配置文件为my.ini -
linux下配置文件位置为my.cnf
- 开启配置
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
常见优化
- 系统层面入手(排查sql导致cpu占用过高)
top -H # 查出性能占用最大的pid
- mysql参数优化
# 独立表空间
Using filesort
# 设置缓存空间(连表之类的操作使用的缓存空间)
set sort_buffer_size = 256*1024*1024
- sql优化
- 子查询变成关联查询
- 反范式设计优化
- 针对范式化设计而言
- 为了性能和读取效率考虑，适当对数据库设计范式进行违反
- 允许适当冗余，以空间换时间(减少连表)
- 索引优化
-
explain查看索引使用情况 - 根据使用情况重写建立索引(创建时范围索引放最后)
执行计划
explain sql
-
key是否使用了索引，使用了什么索引 -
key_len是否充分使用了索引
varchar(50)的索引计算
- 字符类型 varchar +2 char+0
- 字符集 utf8(3) 一个字符的长度
- 本身长度 50
- 是否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
- 表示查找到这一行的方式，性能从低到高
-
ALL遍历全表找到匹配的行 -
index遍历全部索引树找到匹配的行 -
range只检索给定范围的行，使用一个索引来选择行 -
ref表的连接匹配条件，即那些列或常量被用于查找索引上的值 -
eq_ref类似ref区别在于是使用的所有是唯一索引，即多表中使用primary key或者unique key作为关联条件 -
const、system，当MySQL对查询某部分进行优化，并转换成一个常量时，使用这些类型访问。例如主键置于where中，MySQL就能把改查询转换成一个常量，system是const的特例，当查询的表只有一行情况下是system。 -
NULL执行时不用访问表或索引，例如从索引列选取最小值，可以直接通过索引查找完成。
5. possible_keys
- 指出
MySQL能使用那个索引在表中找到记录，查询涉及到的字段若存在索引，则索引会被列出，但不一定会被利用到。
6.key
-
MySQL决定使用的键
7. key_len
- 表示索引中使用的字节数，可通过改列计算查询中使用的索引的长度。
8. ref
- 列与索引的比较，表示上述表的连接匹配条件，即那些列或常量被用于查找索引列的值
9. rows
- 估算出结果集行数，表示
MySQL根据表统计信息及索引选用情况估算找到所需记录所需要读的行数
10. Extra
- 包含
MySQL解决查询的详细信息
-
Using where，不用读取表中的所有信息，通过索引就能获取所需数据
网友评论