2.5 高性能索引策略
哪些情况需要建立索引?
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应建立索引
- 查询中与其他表关联的字段、外键应建立索引
- 高并发下倾向创建组合索引
- 查询中排序、统计、分组的字段
哪些情况不应建立索引?
- 记录很少的表
- 频繁更新的字段不应建立索引
- Where中用不到的字段不应建立索引
- 数据重复且分布平均的表字段
2.6 SQL性能分析——Explain指令
explain关键字可以模拟优化器执行SQL语句,分析性能瓶颈。
explain select id, name from table stu_info where ....
解释
-
id select查询的序列号,复合查询中:
-
id相同的语句,执行顺序从上到下
-
id不同,如果是子查询,id序号会递增,id越大,优先级越高,越早执行
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-INffLKR8-1584090801259)(C:\Users\weitu\AppData\Roaming\Typora\typora-user-images\1584083859383.png)]
-
-
select_type 查询的类型,包括:
- SIMPLE 简单的select查询 ,奴包含子查询或UNION
- PRIMARY 查询中包含子查询,则外层被标记为PRIMARY
- SUBQUERY 在select或where中的子查询
- DERIVED 在from中的子查询(衍生)
- UNION 子查询在union后
- UNION RESULT 从union表获取结果的select
-
table 表示这一行的数据是关于哪张表的
-
type 表示查询使用了何种类型,从好到差依次是: system>const>eq_ref>ref>range>index>ALL
- system: 表中只有一行记录,相当于系统表
- const: 通过索引一次就找到了(如将主键置于where中)
- eq_ref: 唯一性索引,每个索引键表中只有一行数据与之匹配
- ref:非唯一扫描,返回匹配某个单独值的所有行
- range: 只检索给定范围的行,使用一个索引来选择行(即where中对索引使用了between,<,>,in等)
- index: 遍历索引树
- all: 遍历全表
-
possible_keys 显示可能应用的这张表中的索引
-
key 实际查询使用到的索引
-
key_len 索引最大可能使用的字节数,越短越好
-
ref 显示索引的哪一列被使用了,哪些列或常量被用于查找索引列上的值
-
rows 大致估计找到所需记录应读取的行数
-
Extra:
- Using filesort 使用文件排序,未使用索引,性能低
- Using temporary 使用了临时表保存中间结果,性能极低
- Using index 使用了覆盖索引,性能高
- Using where 使用了where过滤
2.7 索引优化
- 索引失效
- 最佳左前缀法则
- 不在索引列上做任何操作(计算、函数、自动或手动类型转换),会导致索引失效转向全表扫描(如字符串不加单引号导致自动转为INT)
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引,避免select *
- MySQL在使用 != 或 <> 时无法使用索引会导致全表扫描
- is null , is not null也无法使用索引
- like以通配符开头会导致索引失效,全表扫描(解决办法: 主键索引,覆盖索引)
- or 连接时会索引失效
- 一般性建议
- 对于单键索引,尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
- 在选择组合索引的时候,尽量选择可以能包含当前query中的where子句中更多字段的索引
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
3. 查询截取分析
3.1 查询优化的4种方法
- 慢查询的开启并捕获
- explain+慢查询语句 分析
- show profiles查询 SQL 在MySQL服务器里的执行细节和生命周期情况
- SQL数据库的参数调优
3.2 慢查询日志
-
对当前会话有效: set global slow_query_log = 1
-
永久生效: 修改 my.cnf 文件
开启慢查询日志会影响性能
默认 long_query_time = 10, 即大于10秒会被记录
3.3 show profiles
MySQL提供用来分析当前会话中语句执行的资源消耗情况,默认关闭,会保存最近15次运行结果
-
分析步骤
指令:show profiles;
show profile cpu,block io … for query 2;
需要注意的几个结果
converting HEAP to MyISAM 查询结果太大,内存不够用,往磁盘上存
Creating tmp table 创建临时表
Creating to tmp data on disk 把内存中的临时表复制到磁盘,危险!!
locked
-
全局查询日志
general_log = 1