1、mysql较慢的原因
1）慢SQL消耗了70%~90%的数据库CPU资源；
2）SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低；
3）SQL语句可以有不同的写法；
2、比较常见的sql优化方法
1）不使用子查询
例：SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');
子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。
在MariaDB10/MySQL5.6版本里，采用join关联方式对其进行了优化，这条SQL会自动转换为
SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;
注意：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，固生产环境应避免使用子查询
2）避免函数索引
例：SELECT * FROM t WHERE YEAR(d) >= 2016;
由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描。
应改为----->
SELECT * FROM t WHERE d >= '2016-01-01';
3）用IN来替换OR
低效查询
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
----->
高效查询
SELECT * FROM t WHERE LOC_IN IN (10,20,30);
4）
LIKE双百分号无法使用到索引
SELECT * FROM t WHERE name LIKE '%de%';
----->
SELECT * FROM t WHERE name LIKE 'de%';
目前只有MySQL5.7支持全文索引（支持中文）
5）读取适当的记录LIMIT M,N
SELECT * FROM t WHERE 1;
----->
SELECT * FROM t WHERE 1 LIMIT 10;
6）避免数据类型不一致
SELECT * FROM t WHERE id = '19';
----->
SELECT * FROM t WHERE id = 19;
7）分组统计可以禁止排序
SELECT goods_id,count(*) FROM t GROUP BY goods_id;
默认情况下，MySQL对所有GROUP BY col1，col2...的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。
----->
SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;
8）避免随机取记录
SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;
MySQL不支持函数索引，会导致全表扫描
----->
SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;
9）禁止不必要的ORDER BY排序
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;
----->
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;
10）批量INSERT插入
INSERT INTO t (id, name) VALUES(1,'Bea');
INSERT INTO t (id, name) VALUES(2,'Belle');
INSERT INTO t (id, name) VALUES(3,'Bernice');
----->
INSERT INTO t (id, name) VALUES(1,'Bea'), (2,'Belle'),(3,'Bernice');
3、mysql调优策略
1）硬件层相关优化
修改服务器BIOS设置
选择Performance Per Watt Optimized(DAPC)模式，发挥CPU最大性能。
Memory Frequency（内存频率）选择Maximum Performance（最佳性能）
内存设置菜单中，启用Node Interleaving，避免NUMA问题
2）磁盘I/O相关
使用SSD硬盘
如果是磁盘阵列存储，建议阵列卡同时配备CACHE及BBU模块，可明显提升IOPS。
raid级别尽量选择raid10，而不是raid5.
3）文件系统层优化
使用deadline/noop这两种I/O调度器，千万别用cfq
使用xfs文件系统，千万别用ext3；ext4勉强可用，但业务量很大的话，则一定要用xfs；
文件系统mount参数中增加：noatime, nodiratime, nobarrier几个选项（nobarrier是xfs文件系统特有的）；
4）内核参数优化
修改vm.swappiness参数，降低swap使用率。RHEL7/centos7以上则慎重设置为0，可能发生OOM
调整vm.dirty_background_ratio、vm.dirty_ratio内核参数，以确保能持续将脏数据刷新到磁盘，避免瞬间I/O写。产生等待。
调整net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都设置为1，减少TIME_WAIT，提高TCP效率。
5）MySQL参数优化建议
建议设置default-storage-engine=InnoDB，强烈建议不要再使用MyISAM引擎。
调整innodb_buffer_pool_size的大小，如果是单实例且绝大多数是InnoDB引擎表的话，可考虑设置为物理内存的50% -70%左右。
设置innodb_file_per_table = 1，使用独立表空间。
调整innodb_data_file_path = ibdata1:1G:autoextend，不要用默认的10M,在高并发场景下，性能会有很大提升。
设置innodb_log_file_size=256M，设置innodb_log_files_in_group=2，基本可以满足大多数应用场景。
调整max_connection（最大连接数）、max_connection_error（最大错误数）设置，根据业务量大小进行设置。
另外，open_files_limit、innodb_open_files、table_open_cache、table_definition_cache可以设置大约为max_connection的10倍左右大小。
key_buffer_size建议调小，32M左右即可，另外建议关闭query cache。
mp_table_size和max_heap_table_size设置不要过大，另外sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等设置也不要过大。
本文转自 sykmiao 51CTO博客，原文链接：http://blog.51cto.com/syklinux/1759085，如需转载请自行联系原作者
