SQL语句优化 学习笔记

sql语句时间花在哪了？

1 等待时间 2 执行时间

这两个时间并非孤立的，单条语句执行的快 其他语句等待的时间就少

 

执行时间花在哪了？

1 查找 沿着索引查找 慢者可能全表扫描

2 取出 查到行后，把数据取出来（sending data）

 

如何查询快？

1 查询快 联合索引的顺序 区分度 长度

2 取的快 索引覆盖（不回行）

3 传输的少 ，更少的行和列

 

切分查询：按数据拆分多次

插入1w行数据，每1000条为单位插入

分解查询：按逻辑把多表连接查询分成多个简单的sql

 

 

sql语句优化思路

不查 通过业务逻辑来计算

少查 尽量精准数据，少取行

高效的查 走索引查

 

 

explain分析：

explain select user_id,name from sys_user

id 查询序号 有几个id就是查询了几次

select_type 简单查询

table 查询表

type 索引扫描多少

possible_keys 系统估计可能用到的键 只能用1个

key 真正用到的键

key_len 用到的键长度

ref 联查引用关系

rows 估计本次查多少行

extra 额外信息

 

select_type 类型：

simple (不含子查询)

primary( 含子查询或派生查询 )

--> subquery ( 非from子查询)

--> derived (from型子查询)

--> union

--> union result (往往和union同时出现）

 

 

 

table ：

可能是 ：

1 实际表名 select * from t1

2 表的别名 select * from t2 as a

3 derived from 子查询时

4 null 没有from

 

 

possible_keys：

可能用到的索引，但最终，只能用到1个

 

key：

最终使用到的索引

 

key_len

使用索引的最大长度

 

表达式用不上索引!

如select * from goods where goods_id +1 > 31

 

type类型:

1 all 扫描所以的数据，意味着从表的第一行往后全表扫描 运气不好扫描到最后一行 最慢

select * from email where uname = 'lisi'

 

2 index 扫描所有的索引节点 比all快点

select email from email where ...

 

3 range 查询时，能根据索引做范围的扫描

select * from goods where good_id > 25

 

4 ref 通过索引列，可以直接引用到某些数据行

select good_id from goods where cat_id = 25

 

5 eq_ref 通过索引列，可以直接引用到某1行数据 常见连接查询中

 

6 const system null 这3个分别值查询优化到常量级别，甚至不需要查找时间

一般按主键查询时 易出现const system

直接查询某个表达式 不经过表时 出现null

 

 

ref 类型：

连接查询时，表之间的字段引用关系（2个表关联的那个字段）

 

rows:

估计要扫描多少行

 

extra：

1 using index 索引覆盖 效率非常高 不需要回行

2 using where 光靠索引定位不了 还得配合where判断一下

3 using temporary 指用上了临时表

4 using filesort 文件排序（可能在内存或磁盘） 比较长的列如text列 就要在磁盘上排序了

 

 

in 型子查询引出的陷阱

题：在商城表中 查询6号栏目的商品，6号是一个大栏目里面有78911小栏目

select * from goods where cat_id in (select cat_id from category where parent_id = 6)

 

误区：给我们的感觉是先查到内存的6号栏目的子栏目 然后外层cat_id in (7,8,9,11)

 

事实，goods表全盘扫描并逐行与category 表对照看parent_id = 6 是否成立

原因：mysql查询优化器，针对in型做优化，被改成了exists子查询的执行结果 当goods表越大时，查询速度越慢

 

改进： 用连接查询来代替子查询

select * from goods inner join (select cat_id from category where parent_id = 6) as tmp

on goods .cat_id = tmp.cat_id

 

exists 子查询也会有这种问题

 

from型子查询

内层from语句查到的临时表，是没有索引的 所以from的返回内容要尽量少 需要排序 在内层先排好序

 

count和union

1 count优化：

误区：

1 myisam的count（）非常快

仅限于所有行比较快，因为myisam对行数进行了存储，一旦有条件的查询，速度就不再快了 尤其是where条件的列上没有索引

 

2 想查id>= 100的列 很慢

小技巧：查id<100 在用总数减

select count(*) from com 快

select count(*) from com where id<100 快

select count(*) from com - select count(*) from com where id<100 快

 

3 group by

注意：

1 分组用于统计，不用于筛选重复数据

用 group by 去重效率低

group by 列要有索引 可以避免临时表和文件排序

 

2 以ab表连接为例 主要查询a表的列

那么group by，order by 的列尽量相同，而且列应该显示声明为a的列

select * from a inner join b group by a.cat_id order a.cat_id

 

4 union 优化

union一定要用到临时表

注意： 1 union 的字句条件要尽量具体 即 查询更少的行

2 字句的结果在内存里并成结果集 需要去重复 去重复就得先排序 而加all之后，不需要去重

所以union尽量加all

 

 

 

limit 及 翻页优化

select * from table limit m,n

select * from table limit 100,3

指从第100条开始 往后取3条，不过实际上不是直接数到100取的 而且取103条再把前100条扔了

所以 offset 越大 效率越低 （m）

 

优化方法：

1 从业务上去解决 办法： 不允许翻到100页 以百度为例 一般翻页只能到70多页(推荐）

 

2 不用offset 用条件查询

改成 where id > 100 limt 3;(要求 id 不能缺 缺了就不等效了）

好处： 可以用索引

问题：如果id缺了 2次结果不一致

原因： 数据被物理删除过，有空洞

解决：数据不进行物理删除（可以逻辑删除） 最终在页面上显示数据时，逻辑删除的条目不显示即可

（一般来说大网站的数据都是不物理删除的，只做逻辑删除，如is_delete=1）

3 非要物理删除，还要用offset精确查询，还不限制用户分页怎么办

select * from lx_com

inner join

(select id from lx_com limit 100000,3)as tmp

on lx_com.id = tmp.id

分析： 优化思路是 不查 少查 查索引 少取列

我们现在必须要查，则只查索引 不查数据 得到id 再用id去查具体条目，这种技巧就是延迟关联