-
优化成本：硬件>系统配置>数据库表结构>SQL及索引
-
优化效果：硬件<系统配置<数据库表结构<SQL及索引
优化工具
-
show [SESSION | GLOBAL] variables 查看数据库参数信息
-
SHOW [SESSION | GLOBAL] STATUS 查看数据库的状态信息
-
information_schema 获取元数据的方法
-
SHOW ENGINE INNODB STATUS Innodb引擎的所有状态
-
SHOW PROCESSLIST 查看当前所有连接session状态
-
explain 获取查询语句的执行计划
-
show index 查看表的索引信息
-
slow-log 记录慢查询语句
-
mysqldumpslow 分析slowlog文件的
优化经验
1.SQL及索引优化
-
当只要一行数据时使用 LIMIT 1，这是为了使EXPLAIN中type列达到const类型；
-
如果排序字段没有用到索引，就尽量少排序；
-
在Join表的时候使用相当类型的例，并将其索引,如果你的应用程序有很多 JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。
-
避免SELECT *从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载;
-
尽可能的使用NOT NULL , 对于null的判断会导致引擎放弃使用索引而进行全表扫描;
-
把IP地址存成 UNSIGNED INT ,很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2;
-
使用合理的分页方式以提高分页的效率,可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。SQL可以采用如下的写法：
-
不建议使用%前缀模糊查询,例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”;
-
分解关联查询, 将一个大的查询分解为多个小查询;
-
避免在where子句中对字段进行表达式操作，比如
select user_id,user_project from table_name where age*2=36;
-
对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成
select user_id,user_project from table_name where age=36/2;
-
对于联合索引来说，要遵守最左前缀法则, 举列来说索引含有字段id,name,school，可以直接用id字段，也可以id,name这样的顺序，但是name，school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面;
-
利用小表去驱动大表，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数;
-
尽量使用inner join，避免left join参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表;
2.数据库表结构的优化
-
选择正确的存储引擎
-
永远为每张表设置一个ID,应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的 AUTO_INCREMENT标志
-
将字段很多的表分解成多个表 ,于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
-
增加中间表, 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
3.架构的优化
-
业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。
-
单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。
-
服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。
-
不同业务的mysql物理上放在不同机器，分散压力。
-
使用比主库更好的硬件设备作为slave总结，mysql压力小，延迟自然会变小。
4.硬件的优化
-
采用好服务器，比如4u比2u性能明显好，2u比1u性能明显好。
-
存储用ssd或者盘阵，提升随机写的性能。
-
主从间保证处在同一个交换机下面，并且是万兆环境。
