mysql语句优化

Sql优化

插入数据

1, 我们在进行插入操作时, 尽量使用批量插入的操作, 因为这样我们可以减少数据库的链接, 插入的速度会更快
2, 手动事务提交, 即假设我们是批量执行单条插入语句时, 手动开启事务, 减少事务的提交
3, 使用主键顺序插入, 顺序插入的效率要比乱序插入的效率更高, 这是因为我们在表数据的聚集索引结构中, 叶子节点形成一个顺序的链表结构, 若乱序插入, 那我们插入时就需要在链表中的某段插入, 这将导致表节点的改变, 同时也会造成页分裂的情况, 增加插入的时间

大数据插入操作优化

大数据量的插入时, 我们可以使用mysql的load指令, 具体可以自行百度, 假设300w数据插入, 使用批量插入的时间需要10多分钟, 而通过load指令直接读取本地数据的速度只需要10多秒, 速度提升很大0

主键优化

主键设计的原则如下
1,尽量减少主键的长度, 比如像uuid, 身份证号这种自然唯一的值并不适合作为主键, 这是因为主键的长度越长, 那占的空间就越大, 我们在创建二级索引时所叶子节点存放的是id值, 那id值越大, 意味着我们二级索引所占据的空间就越大
2, 尽量使用顺序值作为主键, 如自增主键, 这里的原因也就是上文所说到的主键顺序插入的问题
3, 在业务中尽量不要去修改主键, 因为这将导致表的索引结构(包括聚集索引和二级索引)发生改变, 需要耗费比较高的时间

order by优化

mysql的排序方式分两种
1, using filesort(通过表的索引, 或全表扫描得出数据行后在缓冲区进行排序)
2. using index(直接通过表的索引, 就可以得出排序, 不需要在缓冲区再进行排序)
而我们要做的就是在order by的时候, 尽量达到using index的情况, 在这里也有可能会出现 backward index scan的情况, 这种情况代表了反向扫描索引
而order by 的建议如下
1, 对需要order by的字段建立索引, 但需要注意, 在联合索引时, 索引失效的问题, 因为在order by中, 索引同样遵循最左法则, 若创建索引时的字段顺序与排序时的字段顺序不同, 同样会出现using filesort的情况(比如建立a_b联合索引, 多字段排序时写法为 order by b,a)
2, 尽量使用覆盖索引, 这样可以让我们达到using index的排序方式, 速度最佳
3, 注意索引结构中叶子节点的排序方式, 假设按默认的建立索引, 那索引叶子节点中存放的顺序将是升序, 若此时我们对数据进行倒序排序, 这时就会出现 backward index scan的情况, 会降低性能, 在mysql8的版本中, 支持在建立索引时指定索引的排序方式
4, 若不可避免出现filesort的情况时, 大数据量排序我们可以适当增加排序缓冲区的大小(具体怎么增加请百度)

group by优化

建议
1, group by也是可以使用索引对其进行优化的
2, 联合索引时也需要注意最左法则, 比如一个索引a_b, 对a,b字段创建联合索引, 那如果我们只使用b字段进行排序, 也会导致索引失效的问题

limit优化

limit的优化官方推荐使用覆盖索引+子查询的方式, 这是因为如果我们在大数据量数据进行limit时, 查询的速度会根据偏移量的增加而增加, 而覆盖索引+子查询的方式即先通过索引查询出指定的id, 再通过联表或在业务中用in查询的方式去查询指定的分页数据

count优化

count的优化其实并没有什么好的解决办法, 如果大数据量非要优化, 可以通过缓存在插入或删除数据时记录数量, 那这样就能很快的取出总数

count的用法

count各个用法的速度对比如下
count(*) > count(1) > count(id) > count(字段值)

update优化

update的优化主要是需要注意锁表的情况出现
innodb引擎虽说特点是锁行, 但锁行是针对索引去锁的, 若是update时并未用到索引, 这将导致mysql对该数据表进行锁表, 那在别的事务中就无法再对该表进行数据的更改, 更容易出现死锁的问题, 大大降低并发性能, 所以我们update的条件中最好带上索引字段, 触发行锁