第16期:索引设计(MySQL 的索引结构)

爱可生云数据库 2020-11-11 16:40:58
Mysql 设计 索引 结构


image

上一章讲了数据库基本上都用 B+ 树来存储索引的原因:适合磁盘存储,能够充分利用多叉平衡树的特性,磁盘预读,并且很好的支持等值,范围,顺序扫描等。这篇主要介绍 MySQL 两种常用引擎,MyISAM 和 InnoDB 的索引组织方式,了解这些存储方式,对数据库优化很有帮助。

MySQL 的索引按照存储方式分为两类:

聚集索引:也称 Clustered Index。是指关系表记录的物理顺序与索引的逻辑顺序相同。由于一张表只能按照一种物理顺序存放,一张表最多也只能存在一个聚集索引。与非聚集索引相比,聚集索引有着更快的检索速度。

MySQL 里只有 INNODB 表支持聚集索引,INNODB 表数据本身就是聚集索引,也就是常说 IOT,索引组织表。非叶子节点按照主键顺序存放,叶子节点存放主键以及对应的行记录。所以对 INNODB 表进行全表顺序扫描会非常快。

非聚集索引:也叫 Secondary Index。指的是非叶子节点按照索引的键值顺序存放,叶子节点存放索引键值以及对应的主键键值。MySQL 里除了 INNODB 表主键外,其他的都是二级索引。MYISAM,memory 等引擎的表索引都是非聚集索引。简单点说,就是索引与行数据分开存储。一张表可以有多个二级索引。 

MYISAM 表:

MYISAM 表是典型的数据与索引分离存储,主键和二级索引没有本质区别。比如在 MYISAM 表里主键、唯一索引是一样的,没有本质区别。

假设表 t1 为 MYISAM 引擎,列为 ID,姓名,性别,年龄,手机号码。其中 ID 为主键,年龄为二级索引。记录如下:

image

那对应的两个 B+ 树索引如下图所示,

主键字段索引树:

image

上图是一个 3 阶的 B+ 树,非叶子节点按照主键的值排序存储,叶子节点同样按照主键的值排序存储,并且包含指向磁盘上的物理数据行指针。 

年龄字段索引树:

image

上图年龄字段索引树同样是一个 3 阶的 B+ 树,非叶子节点按照年龄字段的值顺序存储,叶子节点保存年龄字段的值以及指向磁盘上的物理数据行指针。

从上面两张图可以看出,MYISAM 表的索引存储方式最大的缺点没有按照物理数据行顺序存储,这样无论对主键的检索还是对二级索引的检索都需要进行二次排序。 

举个简单例子演示下,

以下 SQL 1 默认没有排序,乱序输出;需要按照 ID 顺序输出,就得用 SQL 2,显式加 ORDER BY 。

mysql
# SQL 1
mysql> select * from t1;
+-------+----------+--------+------+--------------+
| id | username | gender | age | phone_number |
+-------+----------+--------+------+--------------+
| 10001 | 小花 | 女 | 18 | 18501877098 |
| 10005 | 小李 | 女 | 21 | 15827654555 |
| 10006 | 小白 | 男 | 38 | 19929933000 |
| 10009 | 小何 | 男 | 35 | 19012378676 |
| 10002 | 小王 | 男 | 20 | 17760500293 |
| 10003 | 小赵 | 女 | 29 | 13581386000 |
| 10004 | 小青 | 女 | 25 | 13456712000 |
| 10007 | 小米 | 男 | 23 | 19800092354 |
| 10008 | 小徐 | 女 | 22 | 18953209331 |
+-------+----------+--------+------+--------------+
9 rows in set (0.00 sec)
# SQL 2
mysql> select * from t1 order by id;
+-------+----------+--------+------+--------------+
| id | username | gender | age | phone_number |
+-------+----------+--------+------+--------------+
| 10001 | 小花 | 女 | 18 | 18501877098 |
| 10002 | 小王 | 男 | 20 | 17760500293 |
| 10003 | 小赵 | 女 | 29 | 13581386000 |
| 10004 | 小青 | 女 | 25 | 13456712000 |
| 10005 | 小李 | 女 | 21 | 15827654555 |
| 10006 | 小白 | 男 | 38 | 19929933000 |
| 10007 | 小米 | 男 | 23 | 19800092354 |
| 10008 | 小徐 | 女 | 22 | 18953209331 |
| 10009 | 小何 | 男 | 35 | 19012378676 |
+-------+----------+--------+------+--------------+
9 rows in set (0.00 sec)

接下来看看 INNODB 的主键索引和二级索引的组成方式。

INNODB 表:

INNODB 表本身是索引组织表,也就是说索引就是数据。下图表T1的数据行以聚簇索引的方式展示,非叶子节点保存了主键的值,叶子节点保存了主键的值以及对应的数据行,并且每个页有分别指向前后两页的指针。

INNODB 表不同于 MYISAM,INNODB 表有自己的数据页管理,默认 16KB。MYISAM 表数据的管理依赖文件系统,比如文件系统一般默认 4KB,MYISAM 的块大小也是 4KB,MYISAM 表的没有自己的一套崩溃恢复机制,全部依赖于文件系统。

image

INNODB 表这样设计的优点有两个:

  1. 数据按照主键顺序存储。主键的顺序也就是记录行的物理顺序,相比指向数据行指针的存放方式,避免了再次排序。我们知道,排序消耗最大。现在表 t1 的直接拿出来就是按照主键 ID 排序。
mysql
mysql> select * from t1;
+-------+----------+--------+------+--------------+
| id | username | gender | age | phone_number |
+-------+----------+--------+------+--------------+
| 10001 | 小花 | 女 | 18 | 18501877098 |
| 10002 | 小王 | 男 | 20 | 17760500293 |
| 10003 | 小赵 | 女 | 29 | 13581386000 |
| 10004 | 小青 | 女 | 25 | 13456712000 |
| 10005 | 小李 | 女 | 21 | 15827654555 |
| 10006 | 小白 | 男 | 38 | 19929933000 |
| 10007 | 小米 | 男 | 23 | 19800092354 |
| 10008 | 小徐 | 女 | 22 | 18953209331 |
| 10009 | 小何 | 男 | 35 | 19012378676 |
+-------+----------+--------+------+--------------+
9 rows in set (0.00 sec)
  1. 两个叶子节点分别含有指向前后两个节点的指针,这样在插入新行或者进行页分裂时,只需要移动对应的指针即可。

再来看下 INNODB 表的二级索引,如下图所示:

image

INNODB 二级索引的非叶子节点保存索引的字段值,上图索引为表 t1 的字段 age。叶子节点含有索引字段值和对应的主键值。

这样做的优点是当出现数据行移动或者数据页分裂时,避免二级索引不必要的维护工作。当数据需要更新的时候,二级索引不需要重建,只需要修改聚簇索引即可。

但是也有缺点:

  1. 二级索引由于同时保存了主键值,体积会变大。特别是主键设计不合理的时候,比如用 UUID 做主键。下一篇我详细介绍如何设计合理的主键。
  2. 对二级索引的检索需要检索两次索引树。第一次通过检索二级索引叶子节点,找到过滤行对应的主键值;第二次通过这个主键的值去聚簇索引中查找对应的行。

举个例子:

如下 SQL 语句,检索年龄为 23 的行记录:

mysql
select * from t1 where age = 23;

会拆分成以下两个 SQL 语句:

先通过索引字段 age 找到对应的主键值:10005.

mysql
select id from t1 where age=23;

再去聚簇索引上根据主键 ID = 10005 检索到需要的数据行,如果表第一次读取,就需要回表。

mysql
select * from t1 where id = 10005;

不过 MySQL 对这块做了很好的优化,提前做了数据预热(数据预热,这里就不讲了,可以参考 MySQL 手册,手册上介绍的很详细)。

本篇内容介绍到此,简单回顾下本篇内容。本篇主要介绍 MySQL 常见的两种引擎 MYISAM 和 INNODB 的索引组织方式以及各自的优缺点。有问题欢迎批评指正,下一篇我来介绍 MySQL 如何很好的对主键进行设计。


关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!

image

版权声明
本文为[爱可生云数据库]所创,转载请带上原文链接,感谢
https://segmentfault.com/a/1190000037798891

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云