SQL优化

一、巧用limit分页查询（id必须有序）

二、like百分号向右

说明：全模糊查询或者左边出现%的模糊查询会导致索引失效，应该尽量从查询方式或表结构设计上避免，若无法避免且数据量庞大的情况下，一定要使用elasticsearch来进行替代

三、union加个all

说明：union对两个结果集进行并集操作时，不包括重复行，相当于distinct，同时进行默认规则的排序，而union+all对两个结果集进行并集操作时，包括重复行，且不进行排序，union因为要进行重复值扫描，所以在结果集庞大的情况下效率极低，因此建议使用union+all，若结果集去重是强需求，则在应用程序代码上进行去重，因为数据库资源要比应用服务器资源更加珍贵

四、善用straight_join（小数据表驱动大数据表）

说明：straight_join功能与inner join类似，但它能让左边的表来驱动右边的表，通过改变优化器对于联表查询的执行顺序的方式获取更好的性能；若驱动表（左边）的数据量小于被驱动表（右边）的数据量，它的执行性能要高于驱动表的数据量大于被驱动表

假设我们事先知道t2表的数据量一定小于t1表的话，就可以使用上面的方式指定t2表为驱动表，注意事项：straight_join只适用于inner_join并不适合left join和right join；大部分情况下，MySQL优化器是可以做出正解的，因此使用straight_join一定要慎重，因为人为指定的执行顺序并不一定会比优化引擎靠谱

五、exists和in的取舍

如果子查询得出的结果集数据较少，主查询中的表较大且又有索引时，应该用in

反之，如果外层的主查询数据较少时，子查询中的表大且又有索引时，应该用exists

如果是exists，以外层表为驱动表，先被访问，如果是in，那么先执行子查询，in是把内表和外表做哈希连接，而exists是对外表做loop循环，每次loop循环再对内表进行查询

所以，我们会以驱动表的快速返回为目标，目标是以小表来驱动大表，这是性能优化的本质

六、清表要用truncate

说明：truncate是直接把表删除，然后重建表结构，性能很高，但删除操作记录不记入日志，不能回滚；而delete语句执行删除的过程是每次从表中删除一行，性能较低，但该行的删除操作会作为事务记录在日志中保存，以便进行回滚操作

truncate后表和索引所占用空间会恢复到起始大小，而delete只是将被删除的记录标记为已被删除，并不会立即减少表或索引所占用的空间

七、尽量批量操作

说明：SQL批量操作即一次数据库操作中插入多个数据行相比于单条插入可减少大量的IO交互和SQL解析开销，从而提高了插入效率

八、过滤优先于一切

说明：无论是分组还是排序还是多表join，如果可以的话，第一件事就是把用不到的记录先过滤掉

九、函数在等号右侧

说明：如果在索引列上使用函数，会导致索引失效

十、数据类型最小可用

说明：一般情况下，应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常会使SQL执行更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少，但是要确保没有低估需要存储的值的范围，因为在表schema中，修改数据类型是非常耗时和痛苦的操作（表数据流很大时），如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型

十一、char和varchar的抉择

说明：char：例如手机号、电话、身份证号、密码等，长度不够的话，采取右补空格的方式

varchar：具体规则是如果列的最大长度小于于等于255字节，则使用1个字节表示，否则使用2个字节，varchar由于行是变长的，在update的时候可能使行变得比原来更长，会导致分裂页和产生碎片

十二、varchar长度最小可用

十三、适当的索引策略

说明：频繁需要查询的字段应该创建索引，频繁更新的字段不适合创建索引，多表关联查询中的关联字段、查询中统计或者分组字段或者排序字段应该创建索引，尽量使用区分度高的字段创建索引，多条件组合查询优先创建组合索引，熟悉组合索引的最左前缀原则，不要创建冗余索引，禁止使用全文索引，可以用前缀索引进行替代，善于利用覆盖索引来优化查询，delete和update语句里面的where条件必须有索引，否则会导致锁表