数据索引

最新推荐文章于 2023-06-27 14:57:07 发布

qq_37971261

最新推荐文章于 2023-06-27 14:57:07 发布

阅读量268

点赞数

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

本文链接：https://blog.csdn.net/qq_37971261/article/details/112342343

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、什么是数据库索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。
通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录

创建索引

对于tb_user表：
在这里插入图片描述

如果要根据phone列进行查询，就可以对phone列创建索引：

ALTER TABLE tb_user ADD INDEX idx_phone(phone);

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如性别列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义。

创建唯一索引

通过UNIQUE关键字我们就添加了一个唯一索

ALTER TABLE tb_user ADD UNIQUE INDEX unix_phone(phone);

二、索引失效

如何判读索引是否有效

查看表结构

desc tb_user;

在这里插入图片描述
如何查看执行语句是否使用了索引语句？
explain显示了MySQL如何使用索引来处理select语句以及连接表。他可以帮助选择更好的索引和写出更优化的查询语句。

EXPLAIN SELECT * FROM tb_user WHERE  id='1'

在这里插入图片描述

可以看到，使用explain显示了很多列，各个关键字的含义如下：

table：顾名思义，显示这一行的数据是关于哪张表的；
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为：const、eq_reg、ref、range、indexhe和ALL；
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句；
key：实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在Select语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MySQL忽略索引；
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好；
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数；
rows：MySQL认为必须检查的用来返回请求数据的行数；
Extra：关于MySQL如何解析查询的额外信息。

索引失效条件

1、应尽量避免在 where 子句中使用 != 或 <> 操作符

否则引擎将放弃使用索引而进行全表扫描;
如果是主键依然会使用索引

EXPLAIN SELECT * FROM tb_user WHERE id != '1'

EXPLAIN SELECT * FROM tb_user WHERE id <> '1'

在这里插入图片描述
其他索引则不会

EXPLAIN SELECT * FROM tb_user WHERE phone != '1'

EXPLAIN SELECT * FROM tb_user WHERE phone <> '1'

在这里插入图片描述

2、尽量避免在 where 子句中使用 or 来连接条件

将导致引擎放弃使用索引而进行全表扫描，即使其中有条件带索引也不会使用

EXPLAIN SELECT * FROM tb_user WHERE phone  = '12987828382' or id = '1'

在这里插入图片描述

3、like的模糊查询以 % 开头，索引失效；

EXPLAIN SELECT * FROM tb_user WHERE phone  like '%87828382'

在这里插入图片描述
但是非前导模糊查询则行

EXPLAIN SELECT * FROM tb_user WHERE phone  like '1298782838%'

在这里插入图片描述

4、避免在 where 子句中对字段进行表达式操作

EXPLAIN SELECT * FROM tb_user WHERE id/2=4

在这里插入图片描述
改为则可以

EXPLAIN SELECT * FROM tb_user WHERE id=4*2

在这里插入图片描述

5、不要在 where 子句中的 “=” 左边进行函数、算术运算或其他表达式运算

EXPLAIN SELECT * FROM tb_user WHERE SUBSTRING(phone,1,3)= '129'

在这里插入图片描述

6.where 子句里对索引列上有数学运算，用不上索引

7、如果mysql估计使用全表扫描要比使用索引快,则不使用索引，比如数据量极少的表。

8、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

在这里插入图片描述

什么情况下使用索引

数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引
频繁更新的字段不要使用索引
字段不在where语句出现时不要添加索引,如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件，不建议使用索引
4） where 子句里对索引列使用不等于符号 “<>”，使用索引效果一般