sql优化的一般步骤

mysql优化教程

答： 本文邀请ryangz分享近来项目的mysql优化经验~

准备知识

了解select的执行顺序有助于理解语句的结果并对其进行优化，执行顺序如下：

可以通过show status like 'XXX', show global status like 'XXX'，show variables like 'XXX' 查看很多重要的数据，eg：Connections 连接数，Slow_queries 慢查询（可以用 show variables like ‘%slow%’来查询是否开启慢查询日志）等等。

可以通过 desc sql命令 （同样可是使用explain命令，用法相同）获取这个查询的各种属性，检查这个语句是否达到性能标准。着重看重点要看这几列：

rows（影响行数）

select_type（查询类型，是单表查询还是多表查询）

type、possible_key和key（可能用到的索引，以及真正用到索引等）

[ possible_key说明可能用到索引competition_id，但是key为null表明最终没有使用索引，进行了全表扫描，rows为全表数量 ]

[ 本次查询是嵌套查询，两张表的主键都是id，通过desc命令可以看出，player_unique_id的查询时使用的主键索引，影响条数rows为1，但是外部查询虽然id是主键但是没有使用索引，进行了全表查询 ]

一些小技巧和注意事项：

（1）有些时候即便你加了索引，数据库查询的时候也不会使用：

like的%如果只有一个，并且放在开头，则不会使用索引；eg ‘%user’不会使用索引，但是’user%’,’%user%’都会使用索引；

查询的时候and和or 如果想使用索引的话需要前后都加索引，如果只有一个则不会使用索引；

如果查询的时候该列是varchar，但是写的时候没有带引号（写成2018，而不是’2018’）,则不会使用索引；

反向条件查询不能使用索引，尽量少用 !=,not in,not exists；

复合索引最左前缀，eg: 复合索引(name`, age)：

select * from XXX where age=? and name=? 使用索引

select * from XXX where name=? 使用索引

select from XXX where age=? *不使用索引

（2）尽量避免用嵌套查询，外层的就算是主键也不会使用索引（可以参看准备知识中的例子），可以使用左连接、右连接或者相同的功能的其他写法代替。

（3）如果明确知道只有一条结果返回，limit 1能够提高效率

（4）所有不清楚的操作可以通过?查询，类似linux的man操作，? view 就可以查询视图基本语法

（5）开始数据库慢日志：

vim /etc/my.cnf 添加log_slow_queries=slow.log 以及long_query_time=5(设置慢日志的时长)， 然后重启mysql

综上，一般的查询优化步骤如下：

（1）查看慢查询日志，或者通过show status命令查看数据库各项指标是否正常，其中 show status like ‘%handler_read%’ 中 Handler_read_rnd_next如果很高的话，说明需要检查索引了，看看是索引加的不对还是用法不对。

（2）找到问题的语句，通过desc定位这个语句到底哪里有问题，是语句写法问题、索引问题、还是表结构问题等等。

（3）优化语句，重复第1步，直到符合业务需求

如何进行SQL性能优化

一、使用索引 1.单表索引建议控制在5个以内 2.单索引字段数不允许超过5个因为字段超过5个时，实际已经起不到有效过滤数据的作用了。 3.禁止在更新十分频繁、区分度不高的属性上建立索引，因为更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。 4.性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性 能与全表扫描类似。 5.建立组合索引，必须把区分度高的字段放在前面，因为能够更加有效的过滤数据。

二、SQL使用规范优化 1.禁止使用SELECT *，只获取必要的字段，需要显示说明列属性。

1.1读取不需要的列会增加CPU、IO、NET消耗。 1.2不能有效的利用覆盖索引。 2.禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性。 2.1容易在增加或者删除字段后出现程序BUG。 3.禁止使用属性隐式转换。 3.1 SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不 能命中phone索引。 4.禁止在WHERE条件的属性上使用函数或者表达式。 4.1SELECT uid FROM t_user WHERE from_unixtime(day)>='2019-07-15' 会导致全 表扫描。 4.2正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2019-07-15 00:00:00')。 5.禁止负向查询，以及%开头的模糊查询。 5.1 负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描。 5.2 %开头的模糊查询，会导致全表扫描。 6.禁止大表使用JOIN查询，禁止大表使用子查询。 6.1会产生临时表，消耗较多内存与CPU，极大影响数据库性能。 7.禁止使用OR条件，必须改为IN查询。 7.1旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费 更多的CPU帮助实施查询优化呢？ 8.应用程序必须捕获SQL异常，并有相应处理 总结：大数据量高并发的互联网业务，极大影响数据库性能的都不能用哦。

相关标签： # Mysql