Mysql优化

统计慢sql查询

一、sql优化

explain详解
type保证达到range级别，最好达到ref

1、满足最左前缀法则

2、不对索引进行操作（计算、函数、类型转换）

3、尽量使用覆盖索引，不使用 select * 语句

4、不使用不等于（！=或者<>），not in ，not exists

5、避免在where子句中对字段进行 null 值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

6、like查询不要以通配符’%'开头

以通配符’%'开头不会使用索引，如果非要使用，使用覆盖索引或者使用搜索引擎

• 索引下推

Extra的值为Using index condition，表示使用了索引下推
假设有联合索引（name，age）

SELECT * from user where  name like '张%' and age=20

（1）没使用索引下推过程
二级索引中查询name是张开头的记录，然后依次回表查询age为20的记录
（2）使用索引下推过程
二级索引中查询name是张开头的记录，查询到马上判断是否是age为20的，都匹配上再回表查询记录完整数据，减少回表次数提高效率

7、字符串不加单引号索引失效（类型转换）

8、少用or或in

数据量大的时候会走索引，不然会选择全表扫描

9、范围查询尽量拆分成多段

可能因为单次查询数据量太大不走索引

10、尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。
注意：union all的前提条件是两个结果集没有重复数据。

11、当只需要一条数据的时候，使用limit 1

这是为了使EXPLAIN中type列达到const类型

12、必要时可以使用force index来强制查询走某个索引

有的时候MySQL优化器采取它认为合适的索引来检索sql语句，但是可能它所采用的索引并不是我们想要的。这时就 可以采用force index来强制优化器使用我们制定的索引。

13、Order by与Group by优化

Extra	含义
Using index	覆盖索引
Using filesort	用的是外部排序不是索引排序，数据较小时从内存排序，否则在磁盘排序。

• Using filesort的情况需要进行索引优化

order by满足两种情况会使用Using index。
（1）order by语句使用索引最左前列。
（2）where子句与order by子句条件列组合满足索引最左前列。

• filesort文件排序方式

mysql通过max_length_for_sort_data变量和所需要查询的的字段总大小选择使用哪种排序方式，总长度比变量大使用双路排序，比变量小使用单路排序。可以通过修改max_length_for_sort_data(默认1024字节)变量的值主动控制使用哪种排序。可以通过trace工具查看sort_mode，看使用的是哪种排序方式

排序方式	过程
单路排序	一次性取出满足条件行的所有字段，然后在sort buffer中进行排序后返回。（<sort_key, additional_fields>）
双路排序 （回表排序模式）	先根据条件取出相应的排序字段和ID，然后在sort buffer中进行排序，排序完后根据id回表查询到其它需要的字段后返回。（<sort_key, rowid>）

14、count(*)查询优化

查询速度对比
count(*) ≈ count(1) > count(索引字段) > count(主键 id) > count(普通字段)
二级索引比主键索引小，所以更快

存储引擎	count(*)优化
myisam	在磁盘上维护了表总行数，直接取，查询不需要优化
innodb	查询count需要实时计算，可以优化 1）show table status 可以查询到表总行数的估计值 2）将总数维护到Redis里 难实现 3）增加数据库计数表 插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

15、分页查询优化

• 原理
  'limit 1000,10’是查询1010条数据，然后舍弃前1000条，返回后10条，查询数据多的大表执行效率很低
• 优化

（1）对于自增且连续的主键排序的分页查询
‘limit 100000,10’可以优化成’id>100000 limit 10’，使用索引，并且扫描行数减少
（2）对于非主键字段排序的分页查询

select * from employees ORDER BY name limit 90000,5;

优化成

select * from employees e inner join (select id from employees order by name limit 90000,5) ed 

16、Join查询优化

假设两个表t1和t2，t1有100行，t2有10000行，a是索引字段，b是非索引

• 表关联两种算法

Nested-Loop Join嵌套循环连接算法	Block Nested-Loop Join基于块的嵌套循环连接算法
关联字段是索引字段	关联字段不是索引字段
select * from t1 inner join t2 on t1.a= t2.a; join连接过程 （1）选择数据少的t1表作为驱动表（选择见下面说明） （2）先在t1中根据t1的过滤条件查询出一条数据 （3）取出索引a，在t2表中查询满足两表关联的条件的数据（因为是索引，索引查询很快，即使t2表很多数据，也只用扫描1条数据） （4）合并t1和t2数据，返回 （5）重复2,3,4步骤 整个过程扫描了100+100*1=200行	select * from t1 inner join t2 on t1.b= t2.b; join连接过程 （1）选择数据少的t1表作为驱动表（选择见下面说明） （2）t1表数据全放到join_buffer中 （3）t2表依次取出来跟join_buffer数据对比 （4）合并t1和t2数据，返回 整个过程扫描了10000+100=10100行，在buffer中还需要100*10000=100万次判断

• join_buffer内存不够时
  分段放，先放一部分，查询出结果，清空buffer接着放，再次从t2表获取数据和buffer对比，会多扫描一次t2表
• 驱动表选择
  left join左边是驱动表
  right join右边是驱动表
  inner join mysql自动判断，小表作为驱动表
• 大表小表判断
  不是根据表数据量大小，是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的表作为驱动表
• 优化
  （1）关联字段加索引，让mysql做join操作时尽量选择NLJ算法
  （2）小表驱动大表，如果知道哪个是小表可以用straight_join固定连接驱动方式，避免mysql自己判断
  （3）sql复杂的转移到java代码里实现，方便横向拓展

17、in和exsits优化

• 原则
  小表驱动大表

select * from A where id in (select id from B)
B驱动A
select * from A where exists (select 1 from B where B.id = A.id)
A驱动B

• 结论

当B表的数据集小于A表的数据集时，in优于exists
当A表的数据集小于B表的数据集时，exists优于in

二、索引设计优化

1、先写sql再建索引

等sql编写完，根据sql建索引，尽量覆盖80%sql条件

2、不要在小基数字段上建立索引

如性别字段，只有两个值，没法快速的二分查找，不如全表扫描了。

3、长字符串采用前缀索引

varchar(255)这种大字段，对这个字段的前20个字符建立索引，每个值的前20个字符放在索引树里。

过程：先根据字段前20个去索引树匹配，查询出来回表聚簇索引，得到完整字段，再匹配，得到结果返回。

注意：order by和group by无法使用此索引。

三、数据库设计优化

1、确定合适的大类型：数字、字符串、时间、二进制；

2、确定具体的类型：有无符号、取值范围、变长定长等

类型	选择建议
数字	1、如果确定没有负数，指定为UNSIGNED无符号类型，容量大一倍。 2、建议使用TINYINT代替ENUM、BITENUM、SET。 3、不要用INT(10)指定字段显示宽度，直接用INT。 4、DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。注意长度设置。 5、建议使用整形类型来运算和存储实数。 6、能用整数尽量用整数，速度快，能使用AUTO_INCREMENT自增。
日期	1、用DATE、TIME、DATETIME来存储时间，而不是使用字符串。 2、数据格式为TIMESTAMP和DATETIME时，用CURRENT_TIMESTAMP作为默认，操作数据时mysql自动修改。 3、TIMESTAMP是UTC时间戳，与时区相关，DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。 4、TIMESTAMP比DATETIME更节约空间，但是TIMESTAMP有时间上限问题，看情况选择。
字符串	1、字符串的长度相差较大用VARCHAR（变长）；字符串短，且所有值都接近一个长度用CHAR（定长，不足会后面补空格，查询时会去掉空格）。 2、要用来计算的数字不要用VARCHAR类型保存，可能影响到计算的准确性和完整性。 3、尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。 4、BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。 5、BLOB和TEXT都不能有默认值

四、锁优化

1、查询数据尽可能通过索引，无索引行锁会升级为表锁，降低并发效率

2、减少范围查询，范围查询会有间隙锁，给不必要的行加锁

3、涉及事务加锁的sql尽量放在事务最后执行

4、不用串行隔离级别，查询也会加上行锁，效率低下，用可重复读