一、MYSQL索引类型

（以下说的建立索引的方法还有建表时建立索引，方法可自查）

1、聚簇索引（主键索引）与非聚簇索引（普通索引）

聚簇索引一个表中只有一个，默认主键的索引即为聚簇索引

即根节点存放具体数据。
非聚簇索引，即整个索引不会放入具体数据，根节点存储的为地址
使用非聚簇索引时，储存的是主键地址，有可能进行全盘扫描比对，直接从磁盘数据中读取数据进内存，也有可能触发回表，重新进入主键索引，再读取数据。
非聚簇索引

回表过程

mysql通过自动分析器选择优化出最快方法，全盘查询or回表。
mysql架构：

-- 创建主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY (`column1`);

-- 创建普通索引
ALTER TABLE `table_name` ADD INDEX (`column1`);

2、唯一索引

唯一索引的特点是不可重复（可以包含空值，主键索引不可以为空值），唯一索引可作为数据的一个合法验证手段，例如学生表的身份证号字段，我们人为规定该字段不能重复，那么就使用唯一索引。

-- 创建唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column1`);

3、组合索引

--创建组合索引
ALTER TABLE `table_name` ADD INDEX index_name (`column1`,`column2`,`column3`);

在创建组合索引时，排序顺序为创建数据库时选择的顺序

4、全文索引（不常用）

--创建全文索引
ALTER TABLE `table_name` ADD FULLTEXT  (`column1`);

*原理是先定义一个词库，然后在文章中查找每个词条(term)出现的频率和位置，把这样的频率和位置信息按照词库的顺序归纳，这样就相当于对文件建立了一个以词库为目录的索引，这样查找某个词的时候就能很快的定位到该词出现的位置。
*

5、空间索引（不常用）

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。

二、MYSQL索引原则

1、索引自动排序

创建一个表

create table tb_index(
a int primary key
b int not null
c int not null
d int not null
)
insert into tb_index values(3,3,3,3)
insert into tb_index values(2,2,2,2)
insert into tb_index values(1,1,1,1)
insert into tb_index values(5,5,5,5)
insert into tb_index values(6,6,6,6)
insert into tb_index values(4,4,4,4)

然后同样创建tb_index2，不同的是a不为主键。

select * fron tb_index1

select * fron tb_index2

使用两种代码后发现表1有排序，表2无排序
是因为mysql默认对主键创建索引，读取数据是通过索引表读取，索引表是有顺序的。
得出结论：mysql默认对主键创建，索引是有顺序的。

2、回表原则

在非聚簇与聚簇索引中已解释，回表即使用非聚簇索引时，根节点数据储存的是主键地址，索引最终得出数据得时候有可能进行全盘扫描比对，直接从磁盘数据中读取数据进内存，也有可能触发回表，重新进入主键索引，再读取数据。

3、最左匹配原则

在使用组合索引的时候，假如建立的是id、name、age、索引，则若想使用该索引，需要按照id、name、age顺序进行筛选，有时候顺序错了，依然可以使用该索引，那是因为sql有语句自动优化器。
例：

select id,name,age from students where XXXX--- 这条能够用上索引。
select id,name from students where XXXX--- 这条依然能够用上索引。
select id,age from students where XXX--- 这条无法用上索引。

是因为索引严格按照id-name-age进行搜素
就跟平时购物时候选择省-市-镇一样，缺少了市，则镇需要进行全盘搜索，无法使用索引。

4、覆盖索引原则

覆盖索引的意思即，建立的索引表即为数据表，意思为，建立以name的普通索引，使用的时候也查找name，则使用的索引可以直接出数据，不用触发回表。在工作时尽量使用覆盖索引。

• ← Previous Post
• Next Post →