MySQL进阶——索引

在上篇文章我们学习了MySQL进阶——SQL性能分析,这篇文章我们学习MySQL进阶——索引。

索引

索引是一种有序的数据结构,能让MySQL高效获取数据。

在没使用索引时,查询语句是进行全局扫描,例如:执行select * from user where Age=30时,MySQL会查找是否满足Age等于30的数据,在这期间即使找到满足条件的数据,也会一直查找下去,直到查找完所有数据。

在上面的例子中,需要查找9条数据才返回最终的查询结果。

为Age字段创建索引后,会通过某种索引结构来维护Age数据,例如通过二叉树数据结构来维护Age数据,如下图所示:

当使用索引后,只要找到满足Age等于30的数据就会停止继续往下查找,并返回Age等于30的数据,也就是只需要查找4次即可返回查询结果。

「索引优点:」

  • 提高数据检索效率,降低数据库的IO成本;

  • 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗

「索引缺点:」

  • 索引列也需要占用空间;

  • 索引大大提高了查询效率,但同时也降低更新表的速度,如对表进行增删改时,效率会降低;

索引结构

MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结果,主要有以下四种索引结构:

  • B+Tree索引:最常见的索引类型,大部分引擎都支持B+树索引;

  • Hash索引:底层数据结构是用哈希表实现的,但只有精确匹配索引列的查询才有效,不支持范围查询,只有Memory存储引擎支持该索引;

  • R-tree空间索引:MyISAM引起的一个特殊索引,主要用于地理空间数据类型;

  • Full-text全文索引:通过建立倒排索引,快速匹配文档的方式,类似于Lucene,Solr,ES,Memory存储引擎不支持该索引;

MySQL索引数据结构对经典的B+Tree进行了优化,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。

为什么InnoDB存储引擎选择使用B+Tree索引结构?

  • 相对于二叉树,层级更少,搜索效果高;

  • 对于B-Tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;

  • 相对Hash索引,B+Tree支持范围匹配及排序操作;

索引分类

索引主要有以下四种类型:

分类说明关键字
主键索引对于表中主键创建的索引,只能有一个,默认自动创建PRIMARY
唯一索引避免同一个表中某数据列中的值重复,可以有多个UNIQUE
常规索引快速定位特定数据,可以有多个
全文索引全文索引查找的是文本中的关键词,而不是比较索引中的值,可以有多个FULLTEXT

在InnoDB存储引擎中,根据索引的存储形式可以分为以下两种:

  • 聚集索引:将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据,有且只有一个;

  • 二级索引:将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键,可以存在多个;

聚集索引选取规则:

  • 如果存在主键,则主键索引就是聚集索引;

  • 如果不存在主键,将使用第一个唯一索引作为聚集索引;

  • 如果不存在主键、唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引;

索引操作

查看索引

查看索引语法格式如下:

SHOW INDEX FROM 表名;

示例代码如下:

SHOW INDEX FROM UserTable;

运行结果如下:

查看索引

这里我们的UserTable表的主键是ID,所以MySQL会自动创建主键索引,其索引结构BTREE。

注意:当我们创建的数据表没有主键、索引时,查看索引返回的结果为空。

创建索引

创建索引语法格式如下:

CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名 (表字段,...) ;

示例代码如下:

CREATE INDEX name_idx ON UserTable (UserName);
CREATE UNIQUE INDEX email_idx ON UserTable (Email);
CREATE INDEX UserName_Phone_Occupation_idx ON UserTable (UserName,Phone,Occupation);
SHOW INDEX FROM UserTable;

运行结果如下:

注意:

  • 当我们创建索引时,只添加了一个表字段,就会形成单列索引;

  • 当我们创建索引时,添加多个表字段,就会形成联合索引;

在多条件查询时,建议创建联合索引。

前缀索引

有时候索引字段的数据长度很长,这会让索引变得很大,查询时浪费大量的磁盘IO,影响查询效率。这时就可以为该字段设置前缀索引,也就是为该字段的一部分创建索引,其语法格式如下:

create index 索引名 ON 表名 (字段(前n个字符))

示例代码如下:

create index idx_UserName ON UserTable (UserName(2));

如下图所示:

这样就为UserName字段的前两个字符创建了索引。

前缀的长度可以根据索引的选择性来决定,而选择性是指不重复的索引值和数据表的数据总数的比值,选择性越高则查询效率越高,获取选择性语法格式如下:

select count(distinct 字段)/count(*) from 表名;
select count(distinct substring(字段,开始下标,截取的长度))/count(*) from 表名;

示例代码如下:

select count(distinct Phone)/count(*) from UserTable;
select count(distinct substring(Phone,1,3))/count(*) from UserTable;

如下图所示:

唯一索引的选择性是1,是最好的索引选择性,性能也是最好的。

删除索引

删除索引语法格式如下:

DROP INDEX 索引名 ON 表名;

示例代码如下:

DROP INDEX name_idx ON UserTable;
SHOW INDEX FROM UserTable;

运行结果如下:

这样就成功删除了name_idx索引了。

索引效率

在二十多万数据中,不使用索引时,查询手机号为18778169646的数据用了0.08秒,如下图所示:

为手机号phone设置索引后,查询手机号为18778169646的数据用时不到0.01秒,如下图所示:

对比发现,当我们使用索引后,查询效率大大提升。

好了,MySQL进阶——索引就学到这里了。

公众号:白巧克力LIN

该公众号发布Python、数据库、Linux、Flask、Django、自动化测试、Git、算法、前端、服务器等相关文章!

- END -

  • 14
    点赞
  • 28
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

白巧克力LIN

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值