高性能mysql(第二版)之查询性能优化

发布时间:2020-03-07编辑:脚本学堂
本文介绍下,高性能mysql(第二版)学习笔记之查询性能优化的内容,有需要的朋友参考下。

mysql中,对于查询的性能优化,尤为重要,本文分享下相关知识点。

1,开销的指标
a.执行时间
b.检查的行数
c.返回的行数

在explain中的type列,访问类型包括全表扫描(full table scan),索引扫描(index scan),范围扫描(range scan),唯一索引查找(unique index lookup)和常量(constant),访问速度依次递增。

type有哪些类型
system:表中只有一行记录,是const表连接类型的一个特例
const:用于PK,UK,索引右边一般是常量,没有表的关联
eq_ref:PK,uk和别的表做关联
ref:非PK,UK索引做关联
ref_or_null:类似ref
unique_subquery:in子查询,子查询中是唯一索引
index_subquery:类似unique_subquery,子查询中是一般索引
range:range用于将某个字段和一个定植用以下任何操作符比较时 =, <>, >,>=, <, <=, is null, <=>, between
index:连接类型跟 all 一样,不同的是它只扫描索引树。它通常会比 all快点,因为索引文件通常比数据文件小(不是使用索引的意思,是扫描索引树的意思)

all:表扫描,

2,三种使用where 的情况,从最好到最差依次是
a.对索引查找使用where子句来消除不匹配的行,发生在存储引擎层
b.使用覆盖索引(extra列是using index)来避免访问行,并且从索引取得数据后过滤不匹配的行,发生的服务器层,但是不需要从表中读取行
c.从表中检索出数据,然后过滤掉不匹配的行(在extra列中显示using where),这发生在服务器端,并且要在过滤之前读取这些行

3,查询状态

总共有12种

休眠
线程正在等待客服端的新查询

查询
线程正在执行查询或者往客服端发送数据

锁定
分析和统计

线程正在检查存储引擎的统计信息并且优化查询

拷贝到磁盘的临时表

排序结果
线程正在对结果排序

发送数据
有多个含义,也许是在查询的各个状态之间传递数据,也可能是传递结果,还有可能是把结果返回给客服端

4,查询优化器
mysql是使用的基于cost的优化器,但是优化器并不能总是选择最好的方案。

5,mysql能够处理的一些优化类型
a.对联接中表的顺序重新排序
b.将外连接转换成内连接
c.代数等价法则
d.优化in(),对in里面的数据进行排序,然后用二分查找某个值是否存在,不同于一般的其他数据库把in()看做是多个or的同义词
e.mysql联接执行策略
把每个查询看成一个联接---->把每个联接看成一个嵌套循环----->嵌套循环联接。

mysql把所有的查询都转换成联接来执行

f.排序优化
如果待排序值的数量和排序缓存的大小相当,mysql就可以在内存中利用快速排序对所有数据完成排序。

排序算法

双路排序:读取行指针和order by列,对他们进行排序,在重新从表中读取相应的行输出。
需要二次读取数据。

单路排序:一次取出所有列对他们进行排序
extra显示(using filesort):order by 只引用了联接中的第一个表
extra显示(using temporary,using filesort):order by 引用了二个表的列

5,优化特定类型的查询
优化count
count(列名):统计栏位值的数量
count(*):不会把通配符*展开所有列,直接忽略所有列统计行数
对应myisam引擎,无where条件的count(*) 会比较快,因为有保存表的行数量。