SQL 优化

数据库分层

1. 连接层
   提供与客户端的连接
2. 服务层
   提供各种用户使用的接口，如select 接口。提供SQL优化器
3. 引擎层
   提供各种存储数据的方式，Mysql默认的是InnoDB（支持事务，行锁）
4. 存储层
   存储数据

SQL优化

原因

性能低， 执行时间长， 等待时间长， SQL语句欠佳(主要是连接查询）， 索引失效， 服务器参数不合理（如线程数）

SQL 语句

编写过程

select … from … join … on … where … group by … having … order by … limit …

解析过程

from … on … join … where … group by … having … select … order by … limit …
（参考文章： 步步深入 MYSQL 架构）

SQL 优化

sql 优化主要是优化索引，

SQL 性能

分析SQL的执行计划： explain , 可以模拟SQL优化器执行SQL语句，

ID

ID值相同时

笛卡尔积

小表驱动大表, 数据量最小的表先查询

ID值不同时

select type

primary 包含子查询中的主查询
subquery: 包含子查询SQL中子查询
simple: 简答查询，不包含子查询， union
derived： 衍生查询，使用到了临时表。

衍生表1

from 子查询中，只有一张表，或者在from子查询中，

衍生表2

如果有 table1 union table2, 则table1 就是derived。

type

要对type进行优化的前提： 有索引
索引类型。
system> const> eq_ref > ref > range > index > all
其中system和const 为理想情况

system

： 只有一条数据的系统表或者衍生表只有一条数据的衍生表

const

仅仅能查到一条数据，且索引类型是Primary key 或者 unique索引

eq_ref

唯一性索引
对于每个索引建的查询，返回匹配唯一的数据，有且只有一个不能多，也不能0。常见于主键索引和唯一索引

ref

对于每个索引的查询值，返回匹配的所有行

range 级别

where后面是一个范围查询， between in > < 。 in有时会失效

index

查询所有索引中的数据
tid 是索引

all

查询所有表中的数据
cid 不是索引

possible key

可能用到的索引

key

实际用到的索引

key_len

索引的长度
判断复合索引是否被完全使用

单索引时

复合索引时

因为查询name1 要用到name , 所以索引长度为121 （为什么这里不满足最左查询原则）

ref

指明当前表 所参照的字段

c.tid用到了t.tid， t.name用的是 常量。这里三个字段都要有索引，否则会出现NULL

rows

被索引优化的数据的行数。

EXTRA

using filesort： 性能消耗大，需要额外的（排序）查询
a1 a2 都是单索引

using temporary

using index

性能提升，不读取源文件，只从索引中获取数据
例如 查询结果和条件都位于联合索引中，就会出现。

using where

无法从索引中获取值，需要回表查询

优化示例

如下图所示，建立索引。

下图是推荐写法

由于在分层结构中，服务层有SQL优化器，虽然下图中不是按照a1,a2,a3,a4的顺序，但优化之后，也会使用聚集索引。

上图中，这里由于最左匹配原则，只有a1. a2会使用到索引（8字节），a4并不会使用到索引，所以会出现using where 进行回表查询。

上图中，同样，也是因为最左匹配原则，只有第一个字段会使用到索引，但是与上上图中不同的是，这里有一个using filesort，因为where + order by 中的字段（where中的字段只计索引字段a1），a1 和a3出现了跨类，所以出现了using filesort。但是上上图中，where中的索引字段是a1和a2，order by中的索引字段是a3，没有跨列，所以不会出现using filesort。

上图中， where中的索引字段和order by中的字段拼起来 是连续的字段，不会出现using filesort。

单表优化实例

创建

查询语句

加索引

查看结果

根据SQL实际的解析顺序，调整索引顺序，因为先获取from，select语句位于后面，所以如下调整索引顺序

执行结果

虽然可以回表查询bid，但将其放到索引中，可以提高效率。
需要注意的是，索引优化后，需要删除就索引，防止产生干扰。

上图中， 因为查询语句中使用了in，所以可能会导致 索引失效，从而使后面的bid不符合最左匹配原则。

双表优化实例

建表

查询语句

优化

索引创建原则，小表驱动大表。一般左外连接给左表加索引，右外连接给右表加索引。

所以编写语句的时候，要把小表放左边。
添加索引前的执行结果

上图中，加了索引后，有一张表已经到了index级别。

给小表加了索引之后，还要给查询条件加索引。

避免索引失效

概率层面的优化

因为服务层的SQL优化器会对编写的SQL语句进行优化，所以会导致索引失效。
一般来说，范围查询之后的索引失效。

like 模糊查询 索引失效

使用like 会使索引失效，这里tname已建立索引

补救方法，查询结果可以只显示tname。

查询条件数值运行

where a.money * 3 = 90

尽量不用使用 类型转换

or失效

其他的优化方法

order by

单路排序有一定隐患，可能不是一次IO。可能会有多次IO。例如如果数据量过大，可能需要多次排序。

如果max_length_for_sort_data太小，MySQL会自动进行切换

慢查询