QQ咨询 官方微信

添加微信好友

官方小程序

百度小程序

微信小程序

头条小程序

关于我们

数据库

【转载】MySQL索引介绍

 admin  2022-11-28 14:59:11

1. MySQL索引的分类

在创建索引之前了解一下MySQL有哪些索引,然后我们才能选择合适的索引。

常见的索引有,普通索引、唯一索引、主键索引、联合索引、全文索引等。

普通索引

普通索引就是最基本的索引,没有任何限制。

可以使用命令创建普通索引:

ALTER TABLE `table_name` ADD INDEX index_name (`column`);

唯一索引

与普通索引不同,唯一索引的列值必须唯一,允许为null。

创建方式是这样的:

ALTER TABLE `table_name` ADD UNIQUE index_name (`column`);

主键索引

主键索引是一种特殊的唯一索引,并且一张表只有一个主键,不允许为null。

创建方式是这样的:

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`);

联合索引

联合索引是同时在多个字段上创建索引,查询效率更高。

创建方式是这样的:

ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`);

全文索引

全文索引主要用来匹配字符串文本中关键字。

当需要字符串中是否包含关键字的时候,我们一般用like,如果是以%开头的时候,则无法用到索引,这时候就可以使用全文索引了。

创建方式是这样的:

ALTER TABLE `table_name` ADD FULLTEXT (`column`);

 

2. 哪些字段适合创建索引?

我总结了有以下几条:

2.1 频繁查询的字段适合创建索引

一张表的字段总会有冷热之分,很明显那些频繁使用的字段更适合为它创建索引。

2.2 在where和on条件出现的字段优先创建索引

为什么不是在select后面出现的字段优先创建索引?

因为查询SQL会先匹配on和where条件的字段,具体的匹配顺序是这样的:

from > on > join > where > group by > having > select > distinct > order by > limit

2.3 区分度高的字段适合创建索引

比如对于一张用户表来说,生日比性别的区分度更高,更适合创建索引。

可以使用下面的方式手动统计一下,每个字段的区分度,值越大,区分度越高:

select 
    count(distinct birthday)/count(*), 
    count(distinct gender)/count(*) 
from user;

 

对于已经创建好的索引,我们还可以使用MySQL命令查看每个索引的区分度排名:

 

图中Cardinality列表示索引的区分度排名,也被称为基数。

2.4 有序的字段适合创建索引

有序的字段在插入数据库的过程中,仍能保持B+树的索引结构,不需要频繁更新索引文件,性能更好。

 

3. 哪些字段不合适创建索引?

说完哪些字段适合创建索引,就有不适合创建索引的的字段。

3.1 区分度低的字段不适合创建索引。

刚才说了用户表中性别的区分度较低,不如生日字段适合创建索引。

3.2 频繁更新的字段不适合创建索引

更新字段的过程中,需要维护B+树结构,会频繁更新索引文件,降低SQL性能。

3.3 过长的字段不适合创建索引

过长的字段会占用更多的空间,不适合创建索引。

3.4 无序的字段不适合创建索引

无序的字段在插入数据库的过程中,为了维护B+树索引结构,需要频繁更新索引文件,性能较差。

 

4. 创建索引的其他注意事项

4.1 优先使用联合索引

查询的时候,联合索引比普通索引能更精准的匹配到所需数据。

 

图中就是在(age,name)两个字段上建立的联合索引,在B+树中的存储结构。

可以看出,是先age排序,age相等的数据,再按name排序。

对于这条查询SQL:

select age,name from user where age=18 and name='李四';

联合索引只需一次就可以查到所需数据,如果我们只在age字段上建立索引,会先匹配到age=18的三条数据,然后再逐个遍历,效率更差,所以平时应该优先使用联合索引。

4.2 使用联合索引时,区分度的字段放前面

这样可以减少查询次数,更快地匹配到所需数据。

4.3 过长字符串可以使用前缀索引

比如在匹配用户地址的时候,如果乡镇已经能区分大部分用户了,就没必要精确到街道小区了。

创建普通索引的时候,指定索引长度,就可以创建前缀索引了。

ALTER TABLE `user` ADD INDEX idx_address (address(3));

4.4 值唯一的字段,使用唯一索引

使用唯一索引,可以避免程序bug导致产生重复数据。

4.5 排序和分组字段也尽量创建索引

在order by和group by中的字段也尽量创建索引,避免使用文件排序,可以使用索引排序提供性能。

4.6 避免创建过多索引

索引好用,适度即可。创建过多的索引,会占用更多存储空间,也会严重影响SQL性能,每次更新SQL,都需要更新大量索引文件,得不偿失。

知识点总结:

¥ 打赏
×
如果您觉得文章帮助了您就打赏一下吧
非常感谢你的打赏,我们将继续分享更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏

本文《【转载】MySQL索引介绍》发布于石头博客文章,作者:admin,如若转载,请注明出处:https://www.pweb123.com/html/Database/925.html,否则禁止转载,谢谢配合!

文章点评

我来说两句 已有0条评论
点击图片更换
小程序插件

PHPcms小程序插件

phpcms小程序插件万能接口,支持一键授权登录

Pbootcms小程序插件

Pbootcms小程序插件,支持一键授权登录

迅睿cms小程序插件

xunruicms小程序插件,简单易学,专为小白设计

dedecms小程序插件

不需要php基础,只需要div+css就可,方便简单

添加微信好友

添加微信好友

微信小程序

百度小程序