由于MySQL
是作为存储层部署在业务系统的最后端,所有的业务数据最终都要入库落盘,但随着一个项目在线上运行的时间越来越久,数据库中的数据量自然会越来越多,而数据体积出现增长后,当需要从表查询一些数据时,效率会越发低下。在正常情况下,表的查询性能和数据量是成反比的,也就是数据越多,查询越慢。
这是什么原因导致的呢?由于
MySQL
默认的查询方式导致的,举个例子~
SELECT * FROM `zz_student`;
+------------+--------+------+--------+
| student_id | name | sex | height |
+------------+--------+------+--------+
| 1 | 竹子 | 男 | 185cm |
| 2 | 熊猫 | 女 | 170cm |
| 3 | 子竹 | 男 | 182cm |
| 4 | 棕熊 | 男 | 187cm |
| 5 | 黑豹 | 男 | 177cm |
| 6 | 脑斧 | 男 | 178cm |
| 7 | 兔纸 | 女 | 165cm |
+------------+--------+------+--------+SELECT * FROM `zz_student` WHERE name = "脑斧";
上面给出了一张学生表,其中有七位学生信息,而此时要查询姓名为「脑斧」的学生信息时,MySQL
底层是如何检索数据的呢?会触发磁盘IO
,对表中的数据进行逐条读取并判断,也就是说,在这里想要查找到符合要求的数据,至少要经过六次磁盘IO
才能检索到目标(暂且先不考虑局部性读取原理与随机IO
)。
- 那假设这个表中有
1000W
条数据呢?要查的目标数据位于表的900W
行以后怎么办?岂不是要触发几百万次磁盘IO
才能检索到数据啊,如果真的这样去干,其效率大家可想而知。
在这种情况下,又该如何去提升数据库的查询性能呢?因为查询往往都是一个业务系统中最频繁的操作,一般项目的写/读请求比例都遵循三七定律,也就是
30%
的请求会涉及到写库操作,另外70%
则属于查库类型的操作。
在思考如何提升查询性能前,咱们不妨先回想一下小时候的场景,小时候由于刚接触汉字,很多字都不认识,所以通常每个人小时候都会拥有一本「新华字典」,但一本字典那么厚,我们是一页页去翻的吗?并不是,字典中有目录索引,我们可以根据音节、偏旁等方式查找不认识的字。
在「新华字典」中一页页翻找某个汉字,就类似于我们前面给出的全表扫描方式,效率特别特别低,而通过目录索引则能够在很短的时间内找到目标汉字。
既然字典中都存在目录索引页,能帮助小时候的我们快速检索汉字,那这个思想能否应用到数据库中来呢?答案是当然可以,并且MySQL
也提供了索引机制,索引是数据库中的核心组件之一,一张表中建立了合适的索引后,往往在面对海量数据查询时,能够事半功倍,接下来一起聊一聊MySQL
的索引。
索引机制会分为上、中、下三篇进行阐述,大致内容如下:
《上篇:索引初识篇》主要讲解索引的概述、分类、使用与管理等;
《中篇:索引应用篇》主要阐述索引优劣分析、建立索引的原则、索引失效的场景、如何正确的使用索引、索引优化机制等;
《下篇:索引原理篇》则主要讲述索引的底层实现、B+Tree、Hash
数据结构、聚簇索引和非聚簇索引实现、索引查询原理、索引管理实现等;
一、MySQL索引机制概述
对于MySQL
索引机制的作用,经过上述「新华字典」的案例后可得知:索引就是用来帮助表快速检索目标数据的。此时先来简单回顾一下MySQL
中索引是如何使用的呢?首先需要创建索引,MySQL
可以通过CREATE、ALTER、DDL
三种方式创建一个索引。
1.1、MySQL索引的创建方式
- ①使用
CREATE
语句创建
CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);
这种创建方式可以给一张已存在的表结构添加索引,其中需要指定几个值:
indexName
:当前创建的索引,创建成功后叫啥名字。tableName
:要在哪张表上创建一个索引,这里指定表名。columnName
:要为表中的哪个字段创建索引,这里指定字段名。length
:如果字段存储的值过长,选用值的前多少个字符创建索引。ASC|DESC
:指定索引的排序方式,ASC
是升序,DESC
是降序,默认ASC
。
当然,上述语句中的INDEX
也可更改为KEY
,作用都是创建一个普通索引,而对于其他的索引类型,这点在后续的索引分类中再聊。
- ②使用
ALTER
语句创建
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);
这里的参数都相同,所以不再重复赘述。
- ③建表时
DDL
语句中创建
CREATE TABLE tableName( columnName1 INT(8) NOT NULL, columnName2 ....,.....,INDEX [indexName] (columnName(length))
);
这种方式就比较适合在库表设计时,已经确定了索引项的情况下建立。
1.2、查询、删除、指定索引
但不管通过哪种方式建立索引,本质上创建的索引都是相同的,当索引创建完成后,可通过SHOW INDEX FROM tableName;
这条命令查询一个表中拥有的索引,如下:
CREATE TABLE `zz_user` (`user_id` int(8) NOT NULL AUTO_INCREMENT,`user_name` varchar(255) NULL DEFAULT "",`user_sex` varchar(255) NULL DEFAULT "",`user_phone` varchar(255) NULL DEFAULT "",PRIMARY KEY (`user_id`) USING BTREE
)ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
在上述的建表SQL
中,为user_id
创建了一个主键索引,然后来查一下当前表的索引信息:
简单的概述一下查询后,每个字段的含义:
- ①
Table
:当前索引属于那张表。 - ②
Non_unique
:目前索引是否属于唯一索引,0
代表是的,1
代表不是。 - ③
Key_name
:当前索引的名字。 - ④
Seq_in_index
:如果当前是联合索引,目前字段在联合索引中排第几个。 - ⑤
Column_name
:当前索引是位于哪个字段上建立的。 - ⑥
Collation
:字段值以什么方式存储在索引中,A
表示有序存储,NULL
表无序。 - ⑦
Cardinality
:当前索引的散列程度,也就是索引中存储了多少个不同的值。 - ⑧
Sub_part
:当前索引使用了字段值的多少个字符建立,NULL
表示全部。 - ⑨
Packed
:表示索引在存储字段值时,以什么方式压缩,NULL
表示未压缩, - ⑩
Null
:当前作为索引字段的值中,是否存在NULL
值,YES
表示存在。 - ⑪
Index_type
:当前索引的结构(BTREE, FULLTEXT, HASH, RTREE
)。 - ⑫
Comment
:创建索引时,是否对索引有备注信息。
这条命令在后续排除问题、性能调优时,会有不小的作用,比如可以通过分析其中的Cardinality
字段值,如果该值少于数据的实际行数,那目前索引有可能失效(对于这些后续排查篇和SQL
优化篇再聊)。
OK~,到这里了解了一下索引相关的创建、查询命令,接着再看看删除、强制使用命令。
在MySQL
中并未提供修改索引的命令,也就说当你建错了索引,只能先删再重新建立一次,删除索引的语句如下:
DROP INDEX indexName ON tableName;
当然,当建立了一条索引后,也可以强制性的为SELECT
语句指定索引,如下:
SELECT * FROM table_name FORCE INDEX(index_name) WHERE .....;
FORCE INDEX
关键字可以为一条查询语句强制指定走哪个索引查询,但要牢记的是:如果当前的查询SQL
压根不会走指定的索引字段,哪这种方式是行不通的,这个关键字的用法是:一条查询语句在有多个索引可以检索数据时,显式指定一个索引,减少优化器选择索引的耗时。
但要注意:如果你对于你整个业务系统十分熟悉,那可以这样干。但如果不熟悉的话,还是交给优化器来自行选择,否则会适得其反!
1.3、数据库索引的本质
前面一直在聊创建、查看、删除、指定等一些索引的基本操作,但索引本质上在数据库中是什么呢?大家都知道,数据库是基于磁盘工作的,所有的数据都会放到磁盘上存储,而索引也是数据的一种,因此与表数据相同,最终创建出的索引也会在磁盘生成本地文件。
不过索引文件在磁盘中究竟以何种方式存储,这是由索引的数据结构来决定的。同时,由于索引机制最终是由存储引擎实现,因此不同存储引擎下的索引文件,其保存在本地的格式也并不相同。
在这里有一个点需要注意:建立索引的工作在表数据越少时越好,如果你想要给一张百万、千万条数据级别的表新创建一个索引,那创建的耗时也不短,这是为什么呢?
因为刚刚聊过,索引本质上和表是一样的,都是磁盘中的文件,那也就代表着创建一个索引,并不像单纯的给一张表加个约束那么简单,而是会基于原有的表数据,重新在磁盘中创建新的本地索引文件。假设表中有一千万条数据,那创建索引时,就需要将索引字段上的1000W
个值全部拷贝到本地索引文件中,同时做好排序并与表数据产生映射关系。
OK~,至此就对
MySQL
提供的索引机制做了简单回顾,下面再来说说数据库中“多样化”的索引类型。