高性能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(*) 会比较快，因为有保存表的行数量