mysql优化

mysql

优化工具

• EXPLAIN 或者 desc 命令。这2个命令可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。相关的参数解释：
  
• 使用一些第三方工具。比如阿里云自带的rds会给出你相关的慢查询记录以及相关的优化建议。具体见这里

优化步骤

• 通过show status命令了解各种SQL的执行频率。查询命令 show status like 'Com_%'. 主要观察 Com_select,Com_insert,Com_update,Com_delete 这几个参数下的状态。可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。
• 定位执行效率较低的SQL语句(重点select). 相关的mysql的命令如下：
  
  • show status like 'Slow_queries' 慢查询的次数
  • Show variables like 'long_query_time'; 慢查询时间
  • set long_query_time=2 修改mysql 慢查询时间
  -
• 通过explain分析低效率的SQL
• 确定问题并采取相应的优化措施

优化建议

• 合理的建立索引。
  
  • 索引越多，update,insert数据的速度越慢。一个表里面的索引不宜超过6个。
  • 较频繁的作为查询条件字段应该创建索引
  • 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件.如性别这样的字段
  • 更新非常频繁的字段不适合创建索引。因为加了索引，dml会变慢
  • 不会出现在WHERE子句中的字段不该创建索引
• 避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符。因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如: SELECT id FROM employee WHERE id != “B%” 优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。在in语句中能用exists语句代替的就用exists.
• LIKE操作符: LIKE '%5400%' 这种写法不会使用索引。LIKE '5400%' 则会引用索引
• 避免在索引列上使用计算, 这样会无法使用索引

WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．举例:
低效： 
SELECT … FROM  DEPT  WHERE SAL * 12 > 25000; 
高效: 
SELECT … FROM DEPT WHERE SAL > 25000/12;

• 存储日期使用DATE类型。
• VARCHAR(N)，N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存
• 使用VARBINARY存储变长字符串
• 如果子串的长度为定值, 使用char来定义，因为查询的计算量会减少，会比varchar提高查询效率
• 如果一个字段可以使用整数解决，则不应该将其设置为字串类型。比如status这种类型的字段，里面状态码都是整数，如果设置为了varchar类型的话，会使得查询效率降低，因为字串匹配比整数比配的计算量更大。
• 能用UNION ALL就不要用UNION。UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源
• 避免使用NULL类型：NULL对于大多数数据库都需要特殊处理，MySQL也不例外，它需要更多的代码，更多的检查和特殊的索引逻辑，有些开发人员完全没有意识到，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默认值