MySQL 基础知识 + 索引相关

java小李

关注

发布于: 2021 年 07 月 28 日

1.0 基础知识

一张数据表中具有百万级的数据时，如何精确且快速的拿出其中某一条或多条记录成为了人们思考的问题。

　　 InnoDB 存储引擎的出现让这个问题得到了很好的解决，InnoDB 存储引擎是以索引来进行数据的组织，而索引在 MySQL 中也被称之为键，因此 UNIQUE KEY，PRIMARY KEY 约束字段会作为索引字段。

　　当没有明确指出 PRIMAY KEY 时，InnoDB 存储引擎会自动的创建一个 6 字节的隐藏主键用于组织数据，但是由于该主键是隐藏的所以对查询没有任何帮助。

　　索引相当于一本大字典的目录，有了目录来找想要的内容就快很多，否则就只能进行一页一页的遍历查询

2.0 查找过程

　　索引的查找过程是依照 B+树算法进行查找的，而每一张数据表都会有一个且只能有一个与之对应的树

　　只有最下面一层节点中存储一整行记录
　　第二层及第一层中黄色部分为指针

　　如图所示，如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO，在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘的 IO）可以忽略不计，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO，29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指针，通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总计三次 IO。真实的情况是，3 层的 B+树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高。

3.0 索引分类

　　索引分为聚集索引与辅助索引

3.1 聚集索引

　　聚集索引是会直接按照 B+树进行查询，由于 B+树的底层叶子节点是一整行记录，所以聚集索引能够十分快速的拿到一整行记录。

　　值得注意的是，一张数据表中只能有一个聚集索引。

3.2 辅助索引

　　辅助索引的树最底层的叶子节点并不会存储一整行记录，而是只存储单列索引的数据，并且还存储了聚集索引的信息。

　　通过辅助索引进行查询时，先拿到自身索引字段的数据，再通过聚集索引拿到整行记录，也就是说辅助索引拿一整行记录而言需要最少两次查询。

　　而一张数据表中可以有多个辅助索引。

4.0 创建索引

4.1 索引类型

索引名类型 INDEX(field)普通索引，只加速查找，无约束条件 PRIMARY KEY(field)主键索引，加速查找，非空且唯一约束 UNIQUE(field)唯一索引，加速查找，唯一约束 INDEX(field1,field2)联合普通索引 PRIMARY KEY(field1,field2)联合主键索引 UNIQUE(field1,field2)联合唯一索引 FULLTEXT(field)全文索引 SPATIAL(field)空间索引

使用场景

4.2 语法介绍

　　索引应当再建立表时就进行创建，如果表中已有大量数据，再进行创建索引会花费大量的时间。

-- 方法一：创建表时    　　CREATE TABLE 表名 (                字段名1  数据类型 [完整性约束条件…],                字段名2  数据类型 [完整性约束条件…],                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY                [索引名]  (字段名[(长度)]  [ASC |DESC])                 );

-- 方法二：CREATE在已存在的表上创建索引        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名                      ON 表名 (字段名[(长度)]  [ASC |DESC]) ;

-- 方法三：ALTER TABLE在已存在的表上创建索引        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX                             索引名 (字段名[(长度)]  [ASC |DESC]) ;                             -- 删除索引：DROP INDEX 索引名 ON 表名字;

复制代码

4.3 功能测试

--  准备表，注意此时表没有设置任何类型的索引create table s1(        id int,        number varchar(20));
-- 创建存储过程，实现批量插入记录delimiter $$  -- 声明存储过程的结束符号为$$        create procedure auto_insert1()         BEGIN                declare i int default 1; -- 声明定义变量                while(i < 1000000) do                        insert into s1 values                                (i,concat('第', i, '条记录'));                set i = i + 1;                end while;        END $$ -- 存储过程创建完毕delimiter ;
-- 调用存储过程，自动插入一百万条数据call auto_insert1();

复制代码

　　在无索引的情况下，查找 id 为 567891 的这条记录，耗时 0.03s

mysql> select * from s1 where id = 567891;+--------+--------------------+| id     | number             |+--------+--------------------+| 567891 | 第567891条记录     |+--------+--------------------+1 row in set (0.33 sec)

复制代码

　　接下来为 id 字段建立主键索引后再进行查找，耗时为 0.00s

mysql> ALTER TABLE s1 MODIFY id int PRIMARY KEY;Query OK, 0 rows affected (4.76 sec)  -- 创建索引花费寺庙Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from s1 where id = 567891;+--------+--------------------+| id     | number             |+--------+--------------------+| 567891 | 第567891条记录     |+--------+--------------------+1 row in set (0.00 sec) 再次查找则快了很多

复制代码

小编整理了一份 Java 基础视频、技术文档、电子书、面试题、简历模板等福利分享给大家。

需要的可以按照以下方式免费领取

1、点击此处【链接】即可免费领取

发布于: 2021 年 07 月 28 日阅读数: 5

java小李

关注

除了帅一无是处 2021.07.23 加入

拥有扎实的Core Java基础，良好的编程风格;熟悉JSP+Servlet+JavaBean模式的WEB开发;熟悉Struts，Hibernate，Spring等开源框架，了解EJB; 熟悉Tomcat，Jboss服务器等，熟悉基于Linux及Unix环境下的软件开发。

发布

暂无评论

创作场景

MySQL 基础知识 + 索引相关

1.0 基础知识

2.0 查找过程

3.0 索引分类

3.1 聚集索引

3.2 辅助索引

4.0 创建索引

4.1 索引类型

4.2 语法介绍

4.3 功能测试

需要的可以按照以下方式免费领取

1、点击此处【链接】即可免费领取

java小李

评论