mysql优化

 

一、MySQL优化指导原则

IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

 

除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标

 

优先优化高并发的 SQL，而不是执行频率低某些“大”SQL。此外，SQL 优化不能是单独针对某一个进行，而应充分考虑系统中所有的 SQL，尤其是在通过调整索引优化 SQL 的执行计划的时候，千万不能顾此失彼，因小失大。

 

 

尽可能对每一条运行在数据库中的SQL进行explain，知道 SQL 的执行计划才能判断是否有优化余地，才能判断是否存在执行计划问题。

 

 

1.固定长度的表会更快， 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB，如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间

2.尽量少 join，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离

3.我们在多个表进行分页数据查询的时候，我们最好是能够在一个表上先过滤好数据分好页，然后再用分好页的结果集与另外的表 Join，这样可以尽可能多的减少不必要的 IO 操作，大大节省 IO 操作所消耗的时间。

4.虽然 Join 性能并不佳，但是和 MySQL 的子查询比起来还是有非常大的性能优势。MySQL 的子查询执行计划一直存在较大的问题

5.union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。

6.排序操作会消耗较多的 CPU 资源，减少参与排序的记录条数

7.当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍

8.读为主可以设置low_priority_updates=1，写的优先级调低，告诉MYSQL尽量先处理读求

9.如果你有一个大的处理，你定你一定把其拆分，使用 LIMIT 条件是一个好的方法。

 

10.避免select *，特别是表的字段比较多或者行数据比较大的情况，会占用大量内存和带宽

11.避免select id from t where name like ‘%c%’，产生全表扫描

12.利用索引来优化有排序需求的 SQL，是一个非常重要的优化手段，索引数据实际上是有序的，如果我们的需要的数据和某个索引的顺序一致，而且我们的查询又通过这个索引来执行，那么数据库一般会省略排序操作，而直接将数据返回

13.尽量避免在 where 子句中对字段进行表达式和函数操作

14.当 where 子句中存在多个条件以“或”并存的时候，MySQL 的优化器并没有很好的解决其执行计划优化问题，再加上 MySQL 特有的 SQL 与 Storage 分层架构方式，造成了其性能比较低下，很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

15.尽量不要写!=或者<>的sql，否则可能用不到索引

16.Order by 、Group by 、Distinct 最好在需要这个列上建立索引，利于索引排序，没办法的情况下，使用强制索引Force index(index_name)

17.较频繁的作为查询条件的字段应该创建索引;更新非常频繁的字段不适合创建索引；不会出现在 WHERE 子句中的字段不该创建索引.

 

二、字段类型问题

mysql是基于行的数据库，而数据读取则是基于page的。每个page中存放有行。如果每一行的数据量都减小，那么每个page里面存放的行就增多了。每次io就能偶取出更多的行

 

1.数字类型：万不得已，不要用double类型。除了占用空间比较大之外，还有精度问题。同样，固定精度的小数也不要使用decimal，建议乘以固定倍数，转换成整数进行存储。可以节省存储空间，而且不用任何附加维护成本。对于整数的存储，建议分开tinyint/int/bigint，他们存储数据占用空间有一定差距。

2.字符类型：首选char类型，其次varchar，万不得已，不要用text类型。它的处理效率低于char和varchar。varchar切不可以随意给一个很大的长度。

3.时间类型：尽量使用timestamp。存储空间占用只是datetime类型的一半。对于需要精确到某一天的类型，建议使用date类型。因为它存储需要三个字节。比timestamp还少。不建议使用int来存储一个unixtimestamp，不直观，不会带来任何好处。

 

三、Query Cache

  Query Cache 的查找，是在 MySQL 接受到客户端请求后在对 Query 进行权限验证之后，SQL 解析之前。甚至都不需要经过 Optimizer 模块进行执行计划的分析优化，更不许要发生任何存储引擎的交互，减少了大量的磁盘 IO 和 CPU 运算，所以效率非常高。如果该表在发生变更时，首先要把Query_cache和该表相关的语句全部置为失效，然后再写入更新。如果Query_cache非常大，该表的查询结构又比较多，查询语句失效也慢，一个更新或是Insert就会很慢，这样看到的就是Update或是Insert怎么这么慢了。数据库写入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，写入量大的系统，建系把该功能禁掉。即query cache并不适合更新，插入，删除非常大的应用。

 

 

MySQL 提供了一系列的 Global Status 来记录 Query Cache 的当前状态，具体如下：Qcache_free_blocks：目前还处于空闲状态的 Query Cache 中内存 Block 数目Qcache_free_memory：目前还处于空闲状态的 Query Cache 内存总量Qcache_hits：Query Cache 命中次数Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数Qcache_lowmem_prunes：当 Query Cache 内存容量不够，需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQLQcache_queries_in_cache：目前在 Query Cache 中的 SQL 数量Qcache_total_blocks：Query Cache 中总的 Block 数量可以根据这几个状态计算出 Cache 命中率，计算出 Query Cache 大小设置是否足够

 

四、常见误区

1.Select count(column) 是表示结果集中有多少个column字段不为空的记录，count(*) 是表示整个结果集有多少条记录

2.MySQL主从同步并不可靠，经常因为各种问题而停止同步，需要监控和及时修复

3.MySQL的行级锁是比较复杂的，可能会锁住相关的多条记录，也可能会失效从而产生表锁