Mysql性能优化

基础篇

1.避免使用外键
外键会导致父表和子表之间耦合，十分影响SQL性能，出现过多的锁等待，甚至造成死锁

2.表必须有主键，例如自增主键
从性能的角度来说，使用UUID作为主键是个最不好的方法，它会使插入变得随机

3.禁止使用分区表
使用分区表查询的字段必须是分区键，否则会遍历所有的分区表，并不会带来性能上的提升。此外分区表在物理结构上仍旧是一张表，此时我们更改表结构，一样不会带来性能上的提升。所以应采用切表的形式做拆分，如程序上需要对历史数据做查询，可通过union all的方式关联查询。另外随着时间的推移，历史数据表不再需要，只需要在从库上dump出来，即便捷地迁移至备份机

4.用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

5.字段定义为NOT NULL要提供默认值
NULL值很难进行查询优化，它会使索引统计更加复杂，还需要mysql内部进行特殊处理

6.尽可能不使用TEXT、BLOB类型

7.不要在索引列进行数学运算和函数运算
无法使用索引导致全表扫描

8.不在低基数列上建立索引，例如‘性别’
有的时候进行全表浏览要比必须读取索引和数据表更快，尤其是当索引包含的是平均分布的数据集更是如此

9.不使用%前导的查询，例如like '%xxx’
无法使用索引，导致全表扫描

10.不使用反向查询，如not in/not like
无法使用索引，导致全表扫描

11.用in来代替or
低效查询
select * from t where id = 10 or id = 20 or id = 30;
高效查询
select * from t where id in (10,20,30);

12.避免数据类型不一致

select * from t where id = '10';
select * from t where id = 10;

13.避免使用连表查询

14.禁止使用 order by rand()

select * from t where 1=1 order by rand() limit 5;
select * from t where id >= ceil(rand()*1000) limit 5;

索引也会占据物理存储空间，而且可能会比表本身还大。因此在考虑数据库的存储空间时，需要考虑索引要占用的空间。

在选择是使用单字段索引还是组合索引时，要考虑在查询的WHERE子句里最经常使用什么字段。如果经常只使用一个字段，单字段索引就是最适合的；如果经常使用两个或多个字段，组合索引就是最好的索引。

大多数用于表结合的字段都应该设置索引。
经常在ORDER BY和GROUP BY里引用的字段也应该考虑设置索引。

索引不应该用于小规模的表。因为查询索引会增加额外的查询时间。对于小规模的表，让搜索发动机进行全表搜索，往往比先查询索引的速度更快。

当字段用于WHERE子句作为过滤器会返回表里的大部分记录时，该字段就不适合设置索引。

经常会被批量更新的表可以具有索引，但批量操作的性能会由于索引而降低。

不应该对包含大量NULL值的字段设置索引。索引对在不同记录中包含不同数据的字段特别有效。字段中过多的NULL值会严重影响索引的运行效率。

经常被操作的字段不应该设置索引，因为对索引的维护会变得很繁重。

对于特别长的关键字创建索引时要十分谨慎，因为大量I/O开销会不可避免地降低数据库性能。

FROM子句里表的安排或次序对性能有很大影响，取决于优化器如何读取SQL语句。举例来说，把较小的表列在前面，把较大的表列在后面，就会获得更好的性能。

下面是应该被索引的数据：
1.作为主键的字段；
2.作为外键的字段；
3.在结合表里经常使用的字段；
4.经常在查询里作为条件的字段；
5.大部分值是唯一值的字段。

待续