MySQL 索引相关

基础知识

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

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

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

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

查找过程

索引的查找过程是依照 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，显然成本非常非常高。

索引分类

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

聚集索引

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

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

辅助索引

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

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

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

创建索引

索引类型

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

举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表
有下列字段：
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

# 除此之外还有全文索引，即FULLTEXT
会员备注信息如果需要建索引的话，可以选择全文搜索。
用于搜索很长一篇文章的时候，效果最好。
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。

# 其他的如空间索引SPATIAL，了解即可，几乎不用

各个索引的应用场景

索引定义

语法介绍

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

-- 方法一：创建表时
    　　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 表名字;

功能测试

--  准备表，注意此时表没有设置任何类型的索引
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) 再次查找则快了很多