mysql innodb引擎 sql优化经验总结

1. sql大概有增删改查四种。

   • select语句
     
     • 不要使用 select * ，需要什么字段，查什么字段，这样不仅能降低数据库服务器磁盘I/O压力，也能够降低网络传输I/O消耗。
     • 尽量不要在sql中使用join，过多的join会使数据库服务器产生临时表，消耗过多内存进行表关联，并且会加重数据库服务器cpu的负担，另外，如果一个很大的join查询语句被包裹在一个大事务中，而此事务包含其他更新操作，持有了共享锁或者写锁，由于join查询语句执行时间过长，会导致事务不能被及时提交，锁一直被占有，可能会导致锁征用程度增大，阻塞其他事务的执行，从而导致数据库服务器的吞吐量下降。因此，可以分多次查询，使事务尽可能的小，同时在应用服务器端进行数据组装的操作，由于大部分互联网企业应用服务器都是I/O密集型，不是CPU密集型，所以对于部分数据计算工作来说，应用服务器压力不大。另外，考虑到扩展性，如果过多的join，以后可能分库分表可能会比较麻烦。
     • 对于出现在where条件中的查询字段，根据情况加上适当的索引，由于避免了全表扫描，可以极大地提升查询效率。但是并不是出现在where条件中的所有字段都需要加索引，只有那些唯一性较强的字段适合加索引，因为过滤效果较好；对于那些唯一性较差但频繁出现在where条件的字段，可以考虑和其他字段建立联合索引，并且联合索引的另外一个优势是，索引树的大小较小，因为分别建立多棵树相当于在索引树中维护了多份主键值；同时，索引字段最好符合趋势递增的规律，这样可以避免在新增数据时对索引树的重建工作；更新较为频繁的字段也不适合建索引，理由同上；另外，如果where条件中的字段较多，并不是每个字段都有索引效率会更高，通常情况下，对于一条sql语句，mysql执行引擎只会选择其中的一到两个索引使用。而过多的索引也会在新增、更新数据时加重数据库维护索引的负担。
     • 对于一些字符串类型的字段，如果在上面建立了索引，由于索引树中保存了索引字段，如果字段过长，会导致索引树空间膨胀，也会拖慢维护索引树的时间。因此可以考虑建立固定长度的前缀索引，可以参考公式 select count(distinct(left(name,10)))/count(*) form user; ，在长度合理的情况下，返回值越接近1越好。
     • 使用like字段进行模糊匹配时，尽量不要进行前缀模糊 like ‘%tom’，这样将导致无法利用索引而选择进行全表扫描。
     • 使用在sql语句中尽量不要使用函数，一方面，使用函数意味着需要消耗额外的数据库CPU资源，计算工作最好放到应用服务器中；另一方面，在一些查询字段上使用数据库函数将无法利用索引而进行全表扫描。
     • 对于order by 关键字后的字段，建议加索引，由于索引有序，因此可以减少数据库对结果局排序的成本。
     • 不要在sql语句中使用 ！=或<>，这将导致mysql无法利用索引而进行全表扫描。
     • 在数据库客户端（应用服务器）使用PreparedStatement，并在数据库服务器端开启预编译缓存功能，这样会使客户端与服务器两端均缓存预编译后带占位符的sql语句，之后再进行查询的时候，客户端不再需要发送sql语句，而是预编译语句的编号，在sql语句较长的情况下能节省部分网络I/O的消耗；同时，预编译语句能够防止sql注入攻击。
     • 能用覆盖索引的语句尽量使用覆盖索引。
   • update语句
     
     • update语句一定要带where条件…，并且where条件字段一定要加索引，否则将导致全表加写锁，影响其他事务的执行，严重降低数据库吞吐量。
   • delete语句
     
     • 最好不要使用delete语句，比较危险，建议用update更新状态字段进行软删除。
   • insert语句
     
     • 批量插入的时候，使用orm框架的批量插入，比如mysql的foreach标签；或者使用jdbc的ps.addBatch() 、ps.executeBatch()方法，这样可以提升插入效率。

2. 新建数据库表

   • 主键使用bigint类型，标记为无符号类型，最大程度避免主键溢出风险。
   • 字段类型选取，在满足需求的前提下类型越小越好，比如状态字段，可能只有有限几种，则可以使用tinyint而不使用占用空间更大的int。
   • 为查询较为频繁的字段建立索引，但是要控制表的索引个数，最好不要超过6个，否则在插入和更新数据时，维护索引树将是一个不小的负担。
   • 适当加入冗余字段，减少表间关联或多次查询的次数，比如某个帖子表关联了发帖人的userid，在展示帖子内容时还需要查询发帖人的名字，这时可以在帖子表中冗余username，从而减少磁盘I/O的次数。然而，为了享受冗余字段的红利，你需要额外维护冗余字段的数据一致性。当用户名更改的时候，冗余字段需要更新。通常，保证数据的最终一致性即可。
   • 对于一些不常用的大字段，可以将其拆分到扩展表，由于大字段在磁盘中占用空间较大，却又不被经常查询使用，将会导致产生较多的磁盘I/O，而在这些大字段空间上产生的I/O相当于无效I/O。将其拆分出去，可以减少磁盘的I/O次数，提高查询效率

3. 如果在线上或者测试过程中发现某些数据库操作较慢，可以开启数据库服务器的慢查询日志，使用explain分析慢sql的执行计划，profile测试sql的执行时间，优化sql。