MySQL索引优缺点+常用SQL优化问题

学徒630

已于 2023-07-16 08:08:23 修改

阅读量514

点赞数

分类专栏：数据库文章标签： sql 数据库

于 2023-05-23 11:22:51 首次发布

本文链接：https://blog.csdn.net/weixin_73077810/article/details/130821998

版权

数据库专栏收录该内容

7 篇文章 2 订阅

订阅专栏

目录

MySQL索引优缺点

什么情况下建议使用索引?

什么情况下不建议使用索引?

优化SQL的必要性：

索引优化

前缀索引优化

覆盖索引优化

主键采用自增id

Insert优化

批量插入

手动提交事务

主键顺序插入

主键优化

数据组织方式

页分裂

页合并

Order By优化

Group By优化

Limit优化

Count优化

Update优化

MySQL索引优缺点

优点：提高查询记录的速度
缺点：需要占用空间，索引是一种用空间换时间的做法创建索引和维护索引都需要消耗时间，会降低表的增删查改效率，因为每次进行增删查改，都需要对索引进行维护，需要消耗时间

什么情况下建议使用索引?

经常作为查询条件的字段，如果需要同时查找多个字段，可以建立联合索引
经常放到GROUP BY或者ORDER BY后面的字段进行GROUP BY或者ORDER BY都会对数据进行排序，这些字段就可以建立一个索引，这样就不需要每次都进行排序了
数据篇幅很长的字段可以采用前缀索引的方式
对部分数据的查询可以采用覆盖索引避免回表查询提高效率

什么情况下不建议使用索引?

不经常作为查询条件的字段
不经常放到GROUP BY或者ORDER BY后面的字段
重复率高的字段，比如性别，建立索引并不会明显提高查询效率，毕竟索引也需要占空间。
表数据量很少的时候，全表查也非常快，你创建维护索引反倒需要开销
经常需要更新的字段，如果建立了索引，索引也需要频繁维护 (B+树是要保证数据有序的) ，会影响数据更新的效率
经常为null的字段上，如果索引列存在null会导致MySQL更加难以优化，会影响到查询的效率 (比如进行统计的时候，count会省略为null的行)，null虽然是空，但是需要额外的一个字节来标识这个null值，更占用空间

优化SQL的必要性：

提高查询性能：由于SQL可能会涉及到多表联合查询、复杂的条件过滤等情况，如果没有进行优化，会导致查询结果返回时间变慢，甚至影响系统的整体性能。通过SQL优化可有效提高查询的性能，并减少系统负载。
优化数据库结构设计：在某些情况下，不恰当的数据库表结构设计会影响SQL的执行性能，比如数据表中缺乏合适的索引、重复字段或存在谓词逻辑错误等。 SQL优化可以帮助检测这些问题，并为调整数据库结构提供方向。
减少CPU和内存的使用：一些SQL查询可能需要耗费较长的时间，导致对数据库执行操作的会话保持时间过长，进而占用了系统资源。通过SQL优化可使部分SQL查询占用的资源解放出来，既提升了性能，又减少了CPU和内存的使用率。
避免死锁和阻塞：在多用户并发的情况下，多个SQL语句执行可能会发生互相阻塞或者死锁现象，导致整个系统陷入僵局。通过SQL优化可避免这类问题的发生，保证数据的正确处理。

索引优化

前缀索引优化

        对一个字符类型的字段使用前缀索引，前缀索引指的是只为前几个字符建立索引，而不是整个字段建立索引，这样可以减小索引的大小

        适用场景：对于一些字符串很长的字段可以使用前缀索引

        局限性:

                1.order by无法使用前缀索引;

                2.无法把前缀索引用作覆盖索引

覆盖索引优化

覆盖索引针对的也是一个联合索引，它的意思就在于你的where语句里面用到的索引在你要查询的字段里面都是包含的，那这样子的话，我直接在二级索引包含了索引信息，那我直接就能返回，而不需要进行回文查询聚集索引。

在覆盖索引中不需要进行回表查询，这就是为什么尽量查询的时候不要一次性查询所有，因为你不能保证你所有字段都是该索引包含的字段

主键采用自增id

        因为MySQL中索引底层采用的是B+树的结构，维护了一个有序的双向链表的结构
        如果主键不是自增的，插入新的记录就很可能导致页分裂的情况，导致数据页的记录需要发生移动以此来维护索引的有序性，会影响到数据的插入效率同时也可能会导致内存空间的浪费，影响查询效率

        如下所示: 假设数据页中存有1,3,5.9的索引数据此时数据页已满，插入索引数据7就会导致页分裂，需要移动元素，同时后续左边的数据页只有小于7的数据才能使用了，大于7的数据就无法使用。

        如果主键是自增的，每插入一条记录，在数据页中都是追加操作，一个页不够了就开辟一个新的页就不会发生页分裂的情况，插入效率更高。同时也不会有内存空间的浪费

Insert优化

批量插入

如果需要向表中插入大量数据，可以考虑使用批量插入。通过将多个insert语句合并为一个较长的SQL语句一次性执行，有效降低了网络传输和数据库访问的次数，提高了插入操作的效率。

insert into tb test values(1,'Tom'),(2,'Cat'),(3,erry')

手动提交事务

频繁的开启和提交事务会产生很大的资源开销，我们尽量在一个时间内开启事务，专门进行某些操作，最后提交，这样子就减少资源消耗

start transaction;
insert into tb test values(1,'Tom'),(2,'Cat'),(3,'erry');
insert into tb test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

主键顺序插入

主键的值是按照聚集索引方式存储的，由于索引的底层是B+树，内部维护了一个有序的双向链表，按顺序插入可以避免页分裂等问题，这样就有利于提高插入效率。

主键优化

数据组织方式

在innoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table lOT）。

页分裂

MySQL页分裂指的是当数据库表中某个页已经填满数据后，如果继续往该页插入数据，则会出现页分裂的情况（页的大小默认是16K的）。简单来说，页分裂就是指将一个页拆分成两个或多个页。

页内元素和页与页间都维持着一个主键的顺序，是一个有序的状态

当插入一个打破顺序的元素，回导致所有页内元素进行分割重排

页合并

MySQL页合并指的是当数据库表中某个页已经动态删除了大量数据后变得很空闲，此时MySQL会考虑将该页和相邻的一些空闲页合并成一个新的页，以减少数据库中的碎片。

在MySQL中，当数据库进行数据插入、更新和删除操作时，可能会出现页面分裂导致数据页变小的情况。而如果数据表中部分数据被删除或者过期后，其所在的页可能就变得十分空闲，此时MySQL会考虑将该页合并到相邻的一页中，以减少碎片和提高性能。

Order By优化

因为索引的底层是一个B加树，它的底层维护了一个有序的双向链表。通过这个有序。我们就可以得到一个有序的结果集。就是说，如果我们要对数据进行排序操作的话，我们排序字段尽量采用设置了索引的字段。

由上可见，我们进行排序操作的时候，尽量使得排序达到“Using index”，那么就要求我们使用到索引，以下是一些注意事项:

Group By优化

使用索引：为GROUP BY子句中的字段创建适当的索引可以提高查询性能。如果没有索引，则MySQL将扫描整个表以找到匹配的行。注意，对于字符串列，应该使用前缀索引以减少索引大小。
数据量减少：限制返回的行数、限制GROUP BY子句中的项数量、只查询需要的列并排除不需要的列、缩小WHERE子句的范围等操作都可以减少数据量并提高查询效率。
使用覆盖索引：如果GROUP BY使用的字段就在索引中，那么MySQL可以从索引中直接获取数据，从而避免回调查询聚合索引。
禁止排序：在GROUP BY中，MySQL默认会根据 GROUP BY 子句中的字段进行排序。如果您能够保证每个分组内的数据顺序不重要，则可以通过禁用排序来提高性能。这可以通过在查询末尾添加ORDER BY NULL代替ORDER BY子句来实现