Mysql常见优化技巧
数据库优化维度有四个：硬件、系统配置、数据库表结构、SQL及索引。
# 优化选择
- 优化成本：硬件>系统配置>数据库表结构>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文件的
# 优化经验
# 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可以采用如下的写法：
select id,name from product where id> 866612 limit 20`;1不建议使用%前缀模糊查询,例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”;
分解关联查询, 将一个大的查询分解为多个小查询;
避免在where子句中对字段进行表达式操作，比如
select user_id,user_project from table_name where age*2=36;1对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成
select user_id,user_project from table_name where age=36/2;1对于联合索引来说，要遵守最左前缀法则, 举列来说索引含有字段
id,name,school，可以直接用id字段，也可以id,name这样的顺序，但是name，school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面;利用小表去驱动大表，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数;
尽量使用
inner join，避免left join参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表;
# 数据库表结构的优化
- 选择正确的存储引擎
- 永远为每张表设置一个ID,应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的 AUTO_INCREMENT标志
- 将字段很多的表分解成多个表 ,于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
- 增加中间表, 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
# 架构的优化
- 业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。
- 单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。
- 服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。
- 不同业务的mysql物理上放在不同机器，分散压力。
- 使用比主库更好的硬件设备作为slave总结，mysql压力小，延迟自然会变小。
# 硬件的优化
- 采用好服务器，比如4u比2u性能明显好，2u比1u性能明显好。
- 存储用ssd或者盘阵，提升随机写的性能。
- 主从间保证处在同一个交换机下面，并且是万兆环境。