基础编程学习快乐每一天
首页
留言
Siddim.com
当前位置:
首页
>
编程知识库
>
后端开发知识
>
MySQL索引优缺点、何时需要/不需要创建索引、索引及sql语句的优化
MySQL索引优缺点、何时需要/不需要创建索引、索引及sql语句的优化
阅读
1
2020-06-21
: 小
Flag
实现,
一、什么是索引?
索引是对数据库表中的一列或多列值进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息。
二、索引的作用?
索引相当于图书上的目录,可以根据目录上的页码快速找到所需的内容,提高性能(查询速度)
三、优点:
通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性。
可以加快数据的检索速度
可以加速表与表之间的连接
在使用分组和排序进行检索的时候,可以减少查询中分组和排序的时间
四、缺点
创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
索引需要占用物理空间,数据量越大,占用空间越大
会降低表的增删改的效率,因为每次增删改索引,都需要进行动态维护
五、什么时候需要创建索引
主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找
查询中统计或者分组的字段;
六、什么时候不需要创建索引
频繁更新的字段不适合创建索引,因为每次更新不单单是更新记录,还会更新索引,保存索引文件
where
条件里用不到的字段,不创建索引;
表记录太少,不需要创建索引;
经常增删改的表;
数据重复且分布平均的字段,因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据,因此他建立索引就没有太大的效果,例如性别字段,只有男女,不适合建立索引。更多面试题,欢迎
七、索引的分类:
普通索引:最基本的索引,它没有任何限制
唯一索引:索引列的值必须唯一,且不能为空,如果是组合索引,则列值的组合必须唯一。
主键索引:特殊的索引,唯一的标识一条记录,不能为空,一般用
primary
key
来约束。
联合索引:在多个字段上建立索引,能够加速查询到速度
八、索引和sql语句的优化
1
、前导模糊查询不能使用索引,如
name
like
‘%静’
2
、
Union
、
in
、
or
可以命中索引,建议使用
in
3
、负条件查询不能使用索引,可以优化为
in
查询,其中负条件有!=、&
lt
;&
gt
;、
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
、范围列可以用到索引,但是范围列后面的列无法用到索引。索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。范围条件有:&
lt
;、&
lt
;=、&
gt
;、&
gt
;=、
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
&
gt
;
866612
limit
20
。
出处:
cnblogs
.
com
/
l199616j
/
p
/
11232392
.
html
? ~
以上数据来源于网络,如有侵权,请联系删除。
上一篇:
TCP协议面试10连问,总会用得到,建议收藏~
下一篇:
面试官:熟悉JVM吗?为什么新生代内存需要有两个Survivor区?
评论
(0)
提交
类别
基础编程学习
HTML
PHP
Python
编程知识库
后端开发知识
热门文章
Java并发中的同步容器与并发容器,你了解多少?
Innodb中的事务隔离级别和锁的关系,难倒一半面试者!
SpringBoot + minio实现分片上传、秒传、续传
面试官:你知道消息队列如何保证数据不丢失吗?
JAVA知识 Java8新特性
面试官:谈谈为什么要限流,有哪些限流方案?
说说动态代理与静态代理区别
面试官:思考Tomcat 类加载器为什么要违背双亲委派模型?
boot-admin 基于SpringBoot的后台权限管理系统,可作为脚手架,用于快速搭建项目
SpringBoot+Vue+App+硬件实现智能家居系统项目