mysql使用技巧---2、mysql常用使用规范（二）（总结）

一、总结

一句话总结：

 

1、【禁止使用 order by rand()】 进行随机排序？

会把表中所有符合条件的【数据装载到内存】中，然后在内存中对所有数据根据随机生成的值进行排序，并且【可能会对每一行都生成一个随机值】，如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源。
推荐【在程序中获取一个随机值】，然后从数据库中获取数据的方式。

 

2、避免建立【冗余索引】和【重复索引】？

因为这样会增加【查询优化器】【生成执行计划的时间】。
重复索引示例：primary key(id)、index(id)、unique index(id)
冗余索引示例：index(a,b,c)、index(a,b)、index(a)

 

3、尽量避免使用外键约束？

不建议使用外键约束（foreign key），但一定要在表与表之间的【关联键上建立索引】。
外键可用于保证数据的参照完整性，但建议在【业务端实现】。
外键会影响父表和子表的写操作从而【降低性能】。

 

4、充分利用表上已经存在的索引？

【避免使用双 % 号的查询条件】。如a like '%123%'，（如果无前置 %，只有后置 %，是可以用到列上的索引的）
一个 SQL只能利用到【复合索引中的一列】进行范围查询如：有 a,b,c 列的联合索引，在查询条件中有 a 列的范围查询，则在 b,c 列上的索引将不会被用到，在定义联合索引时，如果a列要用到范围查找的话，就要把 a 列放到【联合索引的右侧】。
使用 left join 或 not exists 来优化 not in 操作，因为 【not in也通常会使用索引失效】。

 

5、程序连接【不同的数据库使用不同的账号】，禁止跨库查询？

为【数据库迁移】和【分库分表】留出余地
降低业务【耦合度】
避免权限过大而产生的【安全风险】

 

6、禁止使用 SELECT * 【必须使用 SELECT <字段列表> 】查询，原因如下？

消耗【更多的CPU】 和 【IO】 以及【网络带宽资源】
无法使用【覆盖索引】
可减少【表结构变更带来的影响】

 

7、【避免使用子查询】，可以把子查询优化为 【JOIN 操作】？

通常子查询在 【in子句】中，且子查询中为简单 SQL ( 不包含 union、group by、order by、limit 从句 ) 时，才可以把子查询转化为关联查询进行优化。

 

8、子查询性能差的原因？

子查询的结果集【无法使用索引】，通常子【查询的结果集会被存储到临时表】中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
由于子查询会产生大量的临时表也没有索引，所以会【消耗过多的 CPU 和 IO 资源】，产生【大量的慢查询】。

 

 

9、避免使用 JOIN 关联太多的表？

对于 MySQL 来说，是存在【关联缓存】的，缓存的大小可以由 【join_buffer_size 参数】进行设置。
在 MySQL 中，【对于同一个 SQL 多关联（join）一个表，就会多分配一个关联缓存】，如果在一个 SQL 中关联的表越多，所占用的内存也就越大。如果程序中大量的使用了多表关联的操作，同时 join_buffer_size 设置的也不合理的情况下，就容易造成【服务器内存溢出】的情况，就会影响到服务器数据库性能的稳定性。
【关联操作不超过5个表】：同时对于关联操作来说，会产生临时表操作，影响查询效率 MySQL 最多允许关联 61 个表，建议不超过 5 个。

 

 

10、减少同数据库的交互次数？

数据库【更适合处理批量操作 】，合并【多个相同的操作到一起】，可以提高处理效率

 

 

11、拆分复杂的大 SQL 为多个小 SQL？

大 SQL：逻辑上比较复杂，需要【占用大量 CPU】进行计算的SQL 。
MySQL：一个 SQL 只能使用【一个CPU】 进行计算。
SQL 拆分后可以通过【并行执行】来【提高处理效率】。

 

12、对大表数据结构的修改一定要谨慎？

对大表数据结构的修改一定要谨慎，会造成【严重的锁表操作】，尤其是生产环境，是不能容忍的。

 

 ​