Mysql 优化

1.优化查询

（1）慢查询日志

慢查询日志记录了查询过程当中影响查询速度的因素，可通过下面的命令查看是否开启：

show variables like '%slow%';

输入命令后可以看到slow_query_log参数，如果值为ON，则说明开启；如果没开启，可以通过下面命令开启：

set global slow_query_log=on;

然后需要设置慢的标准，此时输入命令：

show variables like 'long%';

可以看到long_query_time参数，对应的值即为慢的标准，此时可以通过下面命令来修改慢的时间标准：

set long_query_time=时间;

此时如果有语句执行时间超过该标准，就会被记录下来，从而知道哪些语句效率低下，需要优化了

（2）语句查询

当知道是哪个语句需要优化时，可以在这个语句前面加上：explain，再执行，查看执行过程情况，举例：

mysql> explain select password from peoples where id=1\G 
mysql> explain select password from peoples where username='aaa'\G

可以看到两个的查询过程情况如下：

#语句1情况id: 1select_type: SIMPLE
table: peoples
partitions: NULLtype: constpossible_keys: PRIMARY
key: PRIMARY
key_len: 4ref: constrows: 1filtered: 100.00Extra: NULL id: 1#########################语句2情况select_type: SIMPLE
table: peoples
partitions: NULLtype: ALL
possible_keys: username
key: NULLkey_len: NULLref: NULLrows: 16filtered: 10.00Extra: Using where

这里重要查看的数据就是possible_keys（查询的类型） 、key（索引）、key_len（索引长度）、rows（查询行数），可以看到左边的数据查询是主键索引，且查询行数只有1；而右边的没有索引，并且查询行数为16，可以看出这里性能就有很大的差异了。

（3）性能查询

可以通过输入下面命令来查看性能：

show variables like '%profiling%';

可以看到profiling参数，值为ON说明为打开；如果没打开，可以通过下面命令打开：

set profiling=on;

此时执行sql语句后，输入命令：

show profiles;

会显示前面输入的所有sql语句的执行时间和对应语句id，然后可以输入下面命令来查看某个语句内部执行过程的每个部分所花的时间：

show profile for query 语句ID

此时就可以找到耗时间的部分，然后去寻找优化的方法

2.优化语句

（1）建立索引

数据库查询优化最常见的方法就是建立索引，有无索引直接关系到查询的行数等，比如上面的语句查询中遇到的情况就是没有索引而导致的性能低下，那么上面那个情况的解决方案就是给username这列加个索引，举例：

alter table peoples add index(username);

此时用explain查看如下（关键部分）：

possible_keys: username_2,username
key: username_2
key_len: 137
rows: 6

可以发现查询的行数少了很多，通过命令：show profiles，也可以发现在耗时上也有所减少。

（2）确定类型

数据类型如果设置不当，或者语句的类型没有对应，也会影响到性能，比如设置一个password的类型为varchar，那么查询语句中：

select * from peoples where password=333
select * from peoples where password='333'

这两句的效率也是有所区别，因为数据类型并非int型，所以第一条的333还需要转成varchar型，这中间就会耗费部分时间，所以数据类型的设置也很重要，从时间上也能看出两个之间的区别：

Query_ID | Duration| Query16 | 0.00893175 | select * from peoples where password=33317 | 0.00027225 | select * from peoples where password='333'

（3）限制内容

查询语句中一般有很大一部分时间在传输数据，所以尽量少使用select * ...，还有就是可以用limit关键字来截取需要的数据，同样也能够提高性能。

（4）避免运算

尽量不要在sql语句中做运算，这样也会降低性能，比如下面的两个语句：

|   1 | 0.00067375 | select * from peoples where id=1
|   2 | 0.00884850 | select * from peoples where id+1=2

可以看出第二条语句明显耗时高于第一条，所以当需要运算时，先将结果运算好，然后在传入sql语句执行。
还有就是尽量不要用>/</!=这类的运算符，也会降低效率

（5）少用关键字

像count()函数、like关键字（特别是左边加%时）这些在sql语句中也会影响性能，所以尽量少用，可以寻找能替代的方式，比如要计算行数有多少可能会用：count(*)，来实现，但是如果在一个id递增的表里，且数据完整的情况下，就可以使用：

select id from ... group by id desc limit 1;#根据id排序，倒序查询出数据的最后一条

此时就能够使效率大大提高

3.缓存优化

开启缓存后能够将查询的内容进行缓存，从而在下次执行相同内容时大大提高效率，但其本身也会消耗部分效率，尤其是不同语句第一次执行的时候，所以可以根据需求开启/关闭缓存

（1）缓存设置

输入下面命令查看缓存开启状态和缓存大小：

show variables like '%cache%';

此时可以看到两个参数：query_cache_size、query_cache_type分别代表缓存大小和状态，如果缓存关闭了，需要配置my.ini文件：将query_cache_type的值设置为1，然后重启mysql服务即可。设置缓存大小可通过下面命令设置：

set global query_cache_size=缓存大小;

此时，连续执行两次同样的语句可以发现效率上的差距：

| Query_ID | Duration   | Query          ||        1 | 0.01313100 | select * from food ||        2 | 0.00014100 | select * from food |

因为开启缓存只是对于重复的操作能够相对提高效率，其本身也有部分消耗，所以当不需要开启时可通过以下命令关闭缓存：

set query_cache_type=0;

（2）缓存命中率

缓存并非每次都能刚好用到，其存在命中率，查看缓存命中率：

show status like '%qcache%';

其中Qcache_hits参数对应的值就是命中率

作者：dawsonenjoy
链接：https://www.jianshu.com/p/2414ef50b464