Mysql 性能优化——必胜之道

感谢博主，

原址：http://superleedo.blog.51cto.com/12164670/1906410

1 定位慢SQL语句(记录)

    开启慢日志功能，在my.cnf中添加配置：

        slow_query_log=1

        slow_query_log_file=mysql.slow

        long_query_time=2        #超过两秒的记录下来

        当数据库连接数较高时，就可以截取某段时间的满日志

        sed -n '#time 2017-03-08 14:30:00/,/end/p' mysql.slow > slow.log

    然后用mysqldumpslow 命令取出耗时间最长的10条慢sql分析

        mysqldumpslow -s t -t 10 slow.log

    2 优化not in子查询

    用left join 代替not in 子查询

    原语句：> select sql_cache count(*) from t1 where id not in (select id from t2);

    优化： > select sql_cache count(*) from t1 left join t2 on t1.id=t2.id where t2.id is null; 

    3 优化like语句

    在mysql中，like 'xxx%'可以用到索引，但是like '%xxx%'不能用到索引。

    使用索引可以减少IO，提高性能。

    原语句：> select * from t1 where name like '%game%';

    优化： > select id from t1 where name like '%game%';

    4 limit 分页优化

    原语句： > select game * from t1 order by id limit 99,10;

    优化： > select game * from t1 where id>=100 order by id limit 10;

    5 优化count统计

    利用辅助索引和distinct优化

    原语句： > select count（*） from my_user;

    优化： > select count(*) from my_user where id >=0;

               > select count(*) from (select distinct k from my_user)tmp;

    6 优化or语句

    使用union all 代替or

    原语句： > select * from user where name='a' or age=18;

    优化： > select * from user where name='a' union all select * from user where age =18;

    7 合理使用索引

    

八 my.cnf配置文件优化

    配置文件内的可以设置的项很多，下面列出常常需要注意重要项

    1 max_connections 最大连接数，默认100 ，一般设置500-1000即可。

    2 innodb_buffer_pool_size，默认128M，可以设置为物理内存的60%-70%。

    3 query_cache_size=64M query_cache_type=1 query_cache_limit=1M

    4 wait_timeout=100    等待时长

    5 connect_timeout=20    interactive_timeout=100

    6 slow_query_log=1    开启慢日志

    7 thread_cache_size=64

    8 relay_log_recovery=1    中继日志恢复

    9 open_files_limit=28512

    ..........

具体配置参数需要根据硬件环境和业务需求去定。