-
优化成本:硬件>系统配置>数据库表结构>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或者盘阵,提升随机写的性能。
-
主从间保证处在同一个交换机下面,并且是万兆环境。