mysql 索引优点和缺点_Mysql添加索引及索引的优缺点

逃离我推掉我的手 2023-10-16 09:44 11阅读 0赞

一、什么是索引？

索引是对数据库表中的一列或多列值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。

二、索引的作用？

索引相当于图书上的目录，可以根据目录上的页码快速找到所需的内容，提高性能(查询速度)

三、优点：

通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性。

可以加快数据的检索速度

可以加速表与表之间的连接

在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间

四、缺点

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

索引需要占用物理空间，数据量越大，占用空间越大

会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护

五、什么时候需要创建索引

主键自动建立唯一索引

频繁作为查询条件的字段应该创建索引

查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找

查询中统计或者分组的字段；

六、什么时候不需要创建索引

频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件

where条件里用不到的字段，不创建索引；

表记录太少，不需要创建索引；

经常增删改的表；

数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引。

七、索引的分类：

普通索引：最基本的索引，它没有任何限制

唯一索引：索引列的值必须唯一，且不能为空，如果是组合索引，则列值的组合必须唯一。

主键索引：特殊的索引，唯一的标识一条记录，不能为空，一般用primary key来约束。

[联合索引][Link 1]：在多个字段上建立索引，能够加速查询到速度

八、索引和sql语句的优化

1、前导模糊查询不能使用索引，

如name like ‘%静’

2、Union、in、or可以命中索引，建议使用in

3、负条件查询不能使用索引，可以优化为in查询，

其中负条件有！=、<>、not in、not exists、not like等

4、联合索引最左前缀原则，又叫最左侧查询，

如果在(a，b，c)三个字段上建立联合索引，那么它能够加快a|(a，b)|(a，b，c)三组的查询速度。

5、建立联合查询时，区分度最高的字段在最左边

6、如果建立了(a，b)联合索引，就不必再单独建立a索引。

同理，如果建立了(a，b，c)索引就不必再建立a，(a，b)索引

7、存在非等号和等号混合判断条件时，在建索引时，要把等号条件的列前置

8、范围列可以用到索引，但是范围列后面的列无法用到索引。

索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。范围条件有：、>=、between等。

9、把计算放到业务层而不是数据库层。

在字段上计算不能命中索引，

10、强制类型转换会全表扫描，

如果phone字段是varcher类型，则下面的SQL不能命中索引。Select \* fromuser where phone=13800001234

11、更新十分频繁、数据区分度不高的字段上不宜建立索引。

更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。

“性别”这种区分度不太大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

一般区分度在80%以上就可以建立索引。区分度可以使用count(distinct(列名))/count(\*)来计算。

12、利用覆盖索引来进行查询操作，避免回表。

被查询的列，数据能从索引中取得，而不是通过定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速度查询。

13、建立索引的列不能为null，使用not null约束及默认值

14、利用延迟关联或者子查询优化超多分页场景，

MySQL并不是跳过offset行，而是取offset+N行，然后放弃前offset行，返回N行，那当offset特别大的时候，效率非常低下，要么控制返回的总数，要么对超过特定阈值的页进行SQL改写。

15、业务上唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

16、超过三个表最好不要用join，

需要join的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。

17、如果明确知道查询结果只要一条，limit 1能够提高效率，比如验证登录的时候。

18、Select语句务必指明字段名称

19、如果排序字段没有用到索引，就尽量少排序

20、尽量用union all 代替 union。

Union需要将集合合并后在进行唯一性过滤操作，这会涉及到排序，大量的cpu运算，加大资源消耗及延迟，当然，使用union all的前提条件是两个结果集没有重复数据。

21、使用合理的分页提高效率。

select id,name from product limit 866613, 20

使用上述SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

优化的方法如下：

可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。

比如此列中，上一页最大的id是866612。

SQL可以采用如下的写法：select id,name from product where id> 866612 limit 20。

[Link 1]: https://so.csdn.net/so/search?q=%E8%81%94%E5%90%88%E7%B4%A2%E5%BC%95&spm=1001.2101.3001.7020

发表评论取消回复

表情：

评论列表（有 0 条评论，11人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL索引失效及使用索引的优缺点

本文所有实验基于MySQL5.7.21，实验将会用到Explain工具，不了解的同学可参考此文章：[MySQL性能优化神器Explain详解][MySQL_Explai...

r囧r小猫/ 2024年04月18日 20:30/ 0 赞/ 74 阅读

相关 MySQL 索引的优缺点及索引注意事项

MySQL索引是数据库中用于加快数据检索速度的一种数据结构。它在数据库表中的列上创建一个索引，以便数据库可以更快地查找和访问数据。索引的优缺点优点： 1. 快速

骑猪看日落/ 2024年03月22日 20:44/ 0 赞/ 133 阅读

相关 MySQL索引：索引分别有哪些优点和缺点？

> 索引的优点： > > 快速访问数据表中的特定信息，提高检索速度。 > 创建唯一性索引，保证数据表中每一行数据的唯一性。 > 加速表与表之间的连接。 >

骑猪看日落/ 2024年02月17日 10:07/ 0 赞/ 75 阅读

相关 mysql 索引优点和缺点_Mysql添加索引及索引的优缺点

一、什么是索引？索引是对数据库表中的一列或多列值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。二、索引的作用？索引相当于图书上的目录，可以根据目录上

逃离我推掉我的手/ 2023年10月16日 09:44/ 0 赞/ 12 阅读

相关 SQL/MySQL--索引的优点/缺点/创建索引的原则

原文网址：[SQL/MySQL--索引的优点/缺点/创建索引的原则\_IT利刃出鞘的博客-CSDN博客][SQL_MySQL--_IT_-CSDN] 索引优点索引优点

我就是我/ 2022年09月13日 05:20/ 0 赞/ 254 阅读

相关使用索引的优点缺点

[数据库创建索引有什么优点和缺点][Link 1] 数据库中索引的优缺点为什么要创建索引呢？这是因为，创建索引可以大大提高系统的性能，优点: 第

清疚/ 2022年05月22日 13:37/ 0 赞/ 253 阅读

相关索引的优点和缺点

[加菲猫][Link 1] Just have a little faith. [索引的优点和缺点][Link 2] 一、为什么要创建索引呢（优点）？这是因为，

骑猪看日落/ 2022年05月19日 14:10/ 0 赞/ 228 阅读

相关 MySQL索引的优缺点

一、什么是索引索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录

雨点打透心脏的1/2处/ 2022年02月27日 13:12/ 0 赞/ 297 阅读

相关 MySQL索引失效及使用索引的优缺点

> 本文所有实验基于MySQL5.7.21，实验将会用到Explain工具，不了解的同学可参考此文章：MySQL性能优化神器Explain详解联合索引失效先创建一个

悠悠/ 2022年01月12日 16:17/ 0 赞/ 312 阅读

相关 MySQL建立索引的优点和缺点

建立索引的优缺点: 为什么要创建索引呢？这是因为，创建索引可以大大提高系统的性能。第一、通过创建唯一性索引，可以保证数据库表中每一

古城微笑少年丶/ 2021年09月15日 06:00/ 0 赞/ 459 阅读