SQL优化

insert插入优化

? ? ? ? 1.一般都是一条数据对应一个insert，当插入多个数据时会大量的进行网络连接，效率极低，所以插入数据时可以批量插入，但也不能一次插入太多。

? ? ? ? 2.手动提交事务，在多条插入完成后再提交事务。

? ? ? ? 3.插入时尽量按主键顺序插入数据，主键顺序插入性能高于乱序插入。

? ? ? ? 4.当大批量插入数据时，应该使用load指令。

主键优化

? ? ? ? 1.数据组织方式，在innoDB引擎中，表数据是按B+Tree的结构存放，且叶子节点维护了一张双向链表。

? ? ? ? (1）页分裂：每个页包含了2-N个数据，如果一行数据过大会溢出

? ? ? ? 主键顺序插入时，第一个页写入满，之后写入到第二页中，以此类推，表之间通过双向链表维护

? ? ? ? 主键乱序插入时，要维护叶子节点的有序性，当插入的页不够课空间时，会再新开辟一页，然后把要插入页的50%的数据移动到新开辟页中，然后把该插入的数据插入，再重新设置链表指针，即页分裂。

? ? ? ? （2）页合并：

? ? ? ? 当页中删除的记录达到了一定阈值(默认MERGE_THRESHOLD为50%)，MySQL就会查找该页是否可以和其他最靠近的页合并，以优化空间

? ? ? ? 2.主键设计原则

满足需求的情况下，要减少主键的长度

插入数据时顺序插入并使用自增主键auto_increment

不要使用UUID做主键或其他无序的自然主键

避免对主键进行修改

ORDER BY优化

按照排序的字段建立合适的索引，多字段排序时也遵循最左前缀法则，尽量使用覆盖索引

多字段排序时注意排序时是按照索引升序还是降序排，

不可避免的出现filesort时，可以增加缓冲区大小（默认sort_buffer_size为256k）

GROUP BY优化

分组操作时可以通过索引进行优化

多字段时注意最左前缀法则

LIMIT优化

limit用于表的分页显示，当数据量很大时，显示分页，越往后耗时越长

通过覆盖索引和子查询优化：注意关键字in后不可跟limit，所以可以把子查询作为一个表来进行多表联查。

COUNT优化

可以通过自己来计数的方式来优化

所以按是否取值的分类，可知效率大小为count(字段) < count(主键) < count(1) = count(*)

?UPDATE优化

避免把行级锁升级为表锁，即更新时一定要按照索引字段进行更新，这样上的是行级锁，不按照索引字段更新数据，加上的就是表锁，那样会导致并发性能降低。