MYSQL优化建议总结（长期收集更新）

 

索引优化

1、避免使用非独立的索引列进行查询 如 where a+1=2

2、多列索引和索引顺序

当出现多个索引做相交操作时（多个AND条件），通常来说一个包含所有相关列的索引要优于多个独立索引。

当出现多个索引做联合操作时（多个OR条件），对结果集的合并、排序等操作需要耗费大量的CPU和内存资源，特别是当其中的某些索引的选择性不高，需要返回合并大量数据时，查询成本更高。索引的顺序对于查询是至关重要的，很明显应该把选择性更高的字段放到索引的前面，这样通过第一个字段就可以过滤掉大多数不符合条件的数据。

索引选择性是指不重复的索引值和数据表的总记录数的比值，选择性越高查询效率越高，因为选择性越高的索引可以让MySQL在查询时过滤掉更多的行。

3、避免多个范围条件

select s.* from student where registered_time > '2018-01-01' and age between 8 and 10;

两个范围条件，registered_time 列和age列，MySQL可以使用registered_time 列的索引或者age列的索引，但是无法同时使用。

4、覆盖索引

一个索引包含所有需要查询的字段的值，那么就没有必要再回表查询其他字段内容。

5、使用索引扫描来排序

在设计索引时，如果一个索引既能够满足排序，又满足查询，是最好的。

6、冗余和重复索引

比如有一个索引(A,B)，再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时，比如有人新建了索引(A,B)，但这个索引不是扩展已有的索引(A)。

8、删除长期未使用的索引

定期删除一些长时间未使用过的索引是一个非常好的习惯。

 

系统配置方面的优化

1.innodb_buffer_pool_size

数据和索引被用作缓存的缓冲池

如果您只运行 InnoDB 存储引擎，那么您通常可以分配 80％ 左右的内存给该缓冲池。而如果您要运行非常复杂的查询或者您有大量的并发数据库连接，亦或您有非常大的数据表的情况，那么就可能需要将此值下调一个等级，以便为其他的调用分配更多的内存。

您在设置 InnoDB 缓冲池大小的时候，要确保其设置既不要过大，也不要频繁引起交换。

2.innodb_log_file_size

日志文件应该至少比使用量大 20％，从而保持系统处于最佳的性能状态。

3.max_connections

通常，最大连接数和可用的最大连接数之间应该至少有 30％ 的差额。