MySQL优化核心

目的：让SQL运行的更快

方法：

1． 减少请求行数

2． 减少IO量

3． 限制结果集

4． 减少抢占

 

深入理解SQL执行计划，从一个explain说起：

mysql> explain select * fromsys_oauth where client_id='905a1f8122d13eac0f3593e7b06f903d'\G

*************************** 1. row***************************

           id: 1

 select_type: SIMPLE

        table: sys_oauth

         type: const

possible_keys: idx_clientid

          key: idx_clientid

     key_len: 194

          ref: const

         rows: 1

        Extra: NULL

1 row in set (0.11 sec)

类型	说明
id	执行计划中，该表/对象的顺序，ID越大越先被执行。ID值一样时，从上到下顺序被执行。
select_type	SELECT的类型，例如SIMPLE/PRIMARY/UNION
table	表/对象名
parititions	被选中的分区，没有使用分区则为NULL
type	表连接的类型。优先顺序是：system,const,eq_ref,ref,fulltext,ref_or_null,index_merge,unique_subquery,index_subquery,range,index,ALL
possible_keys	可能被选中的索引，可能有多个，和最终被选中的索引可能不一样
key	最终被选中的索引，在没有index_merge机制前，只会有一个索引，现在可能有多个进行index_merge
key_len	被选中索引使用的索引长度，可以用于判断联合索引的有效性。如果有索引用于排序，或者联合索引中的一部分用于排序，不会被计入key_len中
ref	显示 哪个字段或常量和索引进行对比
rows	预计被扫描的启示录数
filtered	预计条件过滤的比例
Extra	其它额外信息，尤其注意几种情况：Using filesort,Using temporary

type列类型解析

类型	说明
system	表只有一行
const	当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如果将主键where列表中，MySQL就能将该查询转换为一个常量。表最多只有一行匹配
eq_ref	唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键和唯一索引扫描。每次与之前的表合并行都只在该表读取一行，这是除了system,const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是或非空唯一键的索引
ref	非唯一索引扫描，返回匹配某个单独值的所有行。觉使用非唯一索引即唯一索引的非唯一前缀进行的查找。如果每次只匹配少数行，那就是比较好的一种，使用=或<>，可以是左覆盖索引或非主键或非唯一键
fulltext	全文搜索
ref_or_null	与ref类似，但包括NULL
index_merge	使用到多列索引。表示出现了索引合并优化（包括交集，并集以及交集之间的并集），但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）
unique_subquery	在in子查询中，就是value in (select…)把形如“select unique_key_column”的子查询替换。PS：所以不一定in子句中使用子查询就是低效的
index_subquery	同上，但把形如“select non_unique_key_column”的子查询替换
range	常数值的范围
index	利用索引扫描和ALL差不多，但比ALL快。 1． 当查询是索引覆盖的，即所有数据均可从索引获取的时候（Extra中有Using Index）; 2． 以索引顺序从索引中查找数据行的全表扫描（无Using Index）。另外Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；如单独出现，则是用读索引来代替读行，但不用于查找的
all	全表扫描

Extra中特别注意：Using filesort,Using temporary

类型	说明
Using filesort	看到这个的时候，查询需要优化。 没有办法利用现有索引进行排序，需要额外排序。 建议：根据排序需要，创建相应合适的索引
Using index	利用覆盖索引，无需回表即可取得结果数据。
Using temporary	出面这个需要优化。需要用临时表存储结果集，通常是因为group by的列上没有索引，也有可能是因为同时有group by和order by，但group by和order by的列又不一样
Using where	表示是从索引中根据where条件进行检索。如果连Using where也没有的话，表明本次查询可以从索引中直接取得结果，就是Using index时的情况，不过并不是用于检索。 如果select中的where条件包含了聚集索引列的话，也是可以利用索引进行扫描的，这时有可能不出现Using index字样
impossiable where	where子句总是false的不用执行。如where 1=0

explain或desc中最需要注意的：

type=all

extra中出现Usingfilesort或Usingtemporary

keys:NULL

Rows:数字非常大（超过1万）

 

SQL优化的思路

SQL优化在MySQL设计中占比非常小，因为MySQL更看重架构设计，比较说是扩展性及NoSQL缓存等，还要注意操作系统硬件方面的优化。

在优化的道路中一个重要的原则是：要把做的事能说清楚。

SQL优化注意事项

1． 全面着手

定位每天出现最多的慢SQL

定位业务高峰期的慢SQL

2． 结合缓存

优化不动的SQL，考虑缓存

3． 对于特殊统计类的SQL可以考虑专用从库执行

4． SQL优化的核心：减少IO，减少扫描的行数，限制结果集

SQL优化的技巧

1． 常量转化

WHERR column1=column2 AND column2=’x’

转化为

WHERE column1=’x’ AND column2=’x’

2． 无效代码的排除

WHERE 0=0 AND column1=’y’

转化为

WHERE column1=’y’

3． 常量计算

WHERE column1 = 1+2

转化

WHERE column1 = 3

4． in操作

控制in后面只能接常量，最多不能超过200个。

in后面不能接子查询（mysql5.6之前）

原语句：

select * from tb1 where tb1.id in (select id from tb2 where tb2.c1…)

改为：

select * from tb1,(select id from tb2 where tb2.c1…) t where tb1.id = t.id;

5． 不等于类操作（用不上索引）

not in

!= <>

以上的条件在where语句中出来往往是用不到索引，需要多注意。

原语句：

update sc_brand_info set logo_pic_third=””where id no in (select brandid from sc_product_info api where pricemin=11 groupby brandid);

优化后语句：

UPDATE sc_brand_info a LEFT JOIN (SELECT brandid FROM sc_product_api WHERE pricemin=11 group by brandid) b on(aid=b.brandid) SETa.log_pic_third=”” WHERE b.id is NULL;

6． 不能使用前缀的模糊查询

select c1,c2,c3,… from tb where cX = “%fetion”;

7． 不能对索引本身做运算对比

select * from user where userid+1=2432432;       查询多行

select * from user where userid=2432432-1;        查询1行

8． 同等类型的数据比较

·       字符型比较要加引号

·       数值型的不要加引号，也要注意mediumint和int的区别

 

9． groupby 优化

·       默认groupby会对该字段排序，如果不是明确需要排序，加上orderby null

·       如果明确需要升序或降序，加上groupby DESC/ASC

where id=x order by c2优化

在mysql5.1中执行顺序是先然后再取等号：

·       需要索引(c2,id)

·       用explain进行确认union和

10．                    union all和union

如果不需要对结果集进行去重，则用union all

union有去重的开销

比较：

>select * from t1 union select * from t1;

500 rows in set (0.02 sec)

>select * from t1 union all select *from t1;

1000 row in set (0.00 sec)

11．                    关于count操作

·       尽可能的使用count(*)

·       count索引选择，尽可能使用较短的普通索引

12．                    limit操作

limit原理：

·       limitM,N;

·       取M行记录后再取N行返回给前端

高效分页实现：

·       selectid from tb limit 100000,1;

·       select* from tb where id>M limit 10;