数据库分层
- 连接层
提供与客户端的连接 - 服务层
提供各种用户使用的接口,如select 接口。提供SQL优化器 - 引擎层
提供各种存储数据的方式,Mysql默认的是InnoDB(支持事务,行锁) - 存储层
存储数据
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会自动进行切换