MySQL索引基础

索引

索引是什么呢？

索引在MySQL中也叫是⼀种“键”，是存储引擎⽤于快速找到记录的⼀种数据结构。索引对于良好的性能 非常关

键，尤其是当表中的数据量越来越⼤时，索引对于性能的影响愈发重要。 索引优化应该是对查询性能优化最有

效的⼿段了。索引能够轻易将查询性能提⾼好⼏个数量级。 索引相当于字典的⾳序表，如果要查某个字，如果

不使⽤⾳序表，则需要从⼏百⻚中逐⻚去查。

索引得原理：

索引得目的在于提高查询，和我们查阅书一样得道理，先定位到章节 然后在定位到多少页 ，

本质都是：通过不断地缩⼩想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是⽤同⼀种查找⽅式来锁定数据。

索引的数据结构:

B+树 （B+树是通过⼆叉查找树，再由平衡⼆叉树，B树演化⽽来）

1.索引字段要尽量的⼩：通过上⾯的分析，我们知道IO次数取决于b+数的⾼度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N⼀定的情况下，m越⼤，h越⼩；⽽m = 磁盘块的⼤⼩ / 数据项的⼤⼩，磁盘块的⼤⼩也就是⼀个数据⻚的⼤⼩，是固定的，如果数据项占的空间越⼩，数据项的数量越多，树的⾼度越低。这就是为什么每个数据项，即索引字段要尽量的⼩，比如int占4字节，要比bigint8字节少⼀半。这也是为什么b+树要求把真实的数据放到叶⼦节点⽽不是内层节点，⼀旦放到内层节点，磁盘块的数据项会⼤幅度下降，导致树增⾼。当数据项等于1时将会退化成线性表。 2.索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下⼀步的所搜⽅向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下⼀步该查哪个节点，因为建立搜索树的时候name就是第⼀个比较因⼦，必须要先根据name来搜索才能知道下⼀步去哪⾥查询。比如当(张三,F)这样的数据来检索时，b+树可以⽤name来指定搜索⽅向，但下⼀个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

聚集索引（聚簇索引）：

以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。

这是因为 InnoDB 是把数据存放在 B+ 树中的，而 B+ 树的键值就是主键，在 B+ 树的叶子节点中，存储了表中所有的数据。

这种以主键作为 B+ 树索引的键值而构建的 B+ 树索引，我们称之为聚集索引。

非聚集索引（非聚簇索引）：

以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。

索引的管理：

唯⼀索引：

-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）

-唯⼀索引UNIQUE:加速查找+约束（不能重复）

联合索引：

-PRIMARY KEY(id,name):联合主键索引

-UNIQUE(id,name):联合唯⼀索引

-INDEX(id,name):联合普通索引

创建索引

CREATE TABLE t1(

id int primary key, # index
id_number varchar(18) UNIQUE, #UNIQUE KEY --index
phone_number varchar(14),
INDEX ph_index(phone_number)

)

CREATE TABLE t2(

id int,
age int,
dept_id int,
index(dept_id)

);

添加索引的三种方法

ALTER TABLE t2 ADD INDEX(age);

ALTER TABLE t2 ADD INDEX age_index(age);

CREATE INDEX age_index ON t2(age);

删除索引

DROP INDEX 索引名 ON 表名字;

查看索引

SHOW KEYS FROM 表名;

SHOW INDEX FROM 表名;

SHOW CREATE TABLE 表名;

调用

call

范围

betweeb and

注意事项：

• 避免使⽤select *

• 使⽤count(*)

• 创建表时尽量使⽤ char 代替 varchar

• 表的字段顺序固定⻓度的字段优先

• 组合索引代替多个单列索引（由于mysql中每次只能使⽤⼀个索引，所以经常使⽤多个条件查询时更

适合使⽤组合索引）

• 尽量使⽤短索引

• 使⽤连接（JOIN）来代替⼦查询(Sub-Queries)

• 连表时注意条件类型需⼀致

• 索引散列值（重复少）不适合建索引，例：性别不适合