MySql索引

MySql索引

B+ 树

默认是主键，如果没有主键则使用唯一索引，唯一索引也没有则使用rowid，行号。

所以一定要建立主键。

B+树是B-树的变体，也是一种多路搜索树, 它与 B- 树的不同之处在于:

1. 所有关键字存储在叶子节点出现,内部节点(非叶子节点并不存储真正的 data)

2. 为所有叶子结点增加了一个链指针

简化 B+树 如下图

为什么使用B-/B+ Tree

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构。MySQL 是基于磁盘的数据库系统,索引往往以索引文件的形式存储的磁盘上,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取，I/O存取的消耗要高几个数量级,索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。为什么使用B-/+Tree，还跟磁盘存取原理有关

MySQL(默认使用InnoDB引擎),将记录按照页的方式进行管理,每页大小默认为16K(这个值可以修 改).linux 默认页大小为4K

6、为什么使用B+树

1. B+树更适合外部存储,由于内节点无 data 域,一个结点可以存储更多的内结点,每个节点能索引的范围更大更精确,也意味着 B+树单次磁盘IO的信息量大于B-树,I/O效率更高。

2. Mysql是一种关系型数据库，区间访问是常见的一种情况，B+树叶节点增加的链指针,加强了区间访问性，可使用在范围区间查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。

为什么要使用索引

1、mysql的数据是存在磁盘上的，磁盘的读取速度是比较慢的使用主键的读取方式会很快，当数据来

那个太大非主键的字段查询是会特别耗时。一条语句可能查询10秒以上。

2、建立在主键上的B+树结构可以快速查询出咱们的数据，那使用其他字段就一定要这么慢吗？

有了索引怎么查询

• 从索引里自上而下查询
• 走到叶子节点查询到id
• 根据id去聚簇索引中查找真正的数据，这个过程叫做 回表
• 如果你要的数据索引都有了不需要回表，就叫 索引覆盖 。

索引的分类和创建

索引的命名

普通索引：idx_字段名

唯一索引：ux_字段名

使用的数据

-- 学科表 
create table subject( id int(10) auto_increment, name varchar(20), teacher_id int(10), primary key (id), index idx_teacher_id (teacher_id)); 
-- 教师表 
create table teacher( id int(10) auto_increment, name varchar(20), teacher_no varchar(20), primary key (id), unique index unx_teacher_no (teacher_no(20))); 
-- 学生表
create table student( id int(10) auto_increment, name varchar(20), student_no varchar(20), primary key (id), unique index unx_student_no (student_no(20))); 
-- 学生成绩表
create table student_score( id int(10) auto_increment, student_id int(10), subject_id int(10), score int(10), primary key (id), index idx_student_id (student_id), index idx_subject_id (subject_id));
-- 教师表增加名字普通索引 
alter table teacher add index idx_name(name(20));

insert into student(name,student_no) values ('zhangsan','20200001'), ('lisi','20200002'),('yan','20200003'),('dede','20200004'); insert into teacher(name,teacher_no) values('wangsi','T2010001'), ('sunsi','T2010002'),('jiangsi','T2010003'),('zhousi','T2010004'); insert into subject(name,teacher_id) values('math',1),('Chinese',2), ('English',3),('history',4); insert into student_score(student_id,subject_id,score) values(1,1,90),(1,2,60), (1,3,80),(1,4,100),(2,4,60),(2,3,50),(2,2,80),(2,1,90),(3,1,90),(3,4,100), (4,1,40),(4,2,80),(4,3,80),(4,5,100);

聚簇索引和非聚簇索引

1. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

2. 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子

节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

聚簇索引具有唯一性，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。

表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这

是因为聚簇索引改变了表中行的物理顺序，数据行 按照一定的顺序排列，并且自动维护这个顺序；

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果

没有这样的索引，InnoDB 会隐式定义一个主键（类似oracle中的RowId）来作为聚簇索引。如果已经

设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，

最后恢复设置主键即可。

MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只

是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存

储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个

键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

使用聚簇索引的优势：

1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同

行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁

盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键

Id来组织数据，获得数据更快。

2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引

树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次

新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为

辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小

注：我们知道一次io读写，可以获取到16K大小的资源，我们称之为读取到的数据区域为Page。而

我们的B树，B+树的索引结构，叶子节点上存放好多个关键字（索引值）和对应的数据，都会在一次IO

操作中被读取到缓存中，所以在访问同一个页中的不同记录时，会在内存里操作，而不用再次进行IO操

作了。除非发生了页的分裂，即要查询的行数据不在上次IO操作的换村里，才会触发新的IO操作。

3.因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地

址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈

的对比）

4.不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，

因为索引所占空间小，这些操作是需要在内存中完成的。

主键建议使用自增id

当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出

线新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资

源。

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一

定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想 象，它会干些什么，不断地调整数据

的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，

那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

2、主键索引 （primary key）

也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长

的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键

的数据类型最好是数值

一种方法在创建表的时候创建，再次是修改表

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

索引都能在创建表是指定

create table student_score( id int(10) auto_increment, student_id int(10), subject_id int(10), score int(10), primary key (id), index idx_student_id (student_id), index idx_subject_id (subject_id) );

小tips：

int(n)括号里面的数字表示显示宽度，不是代表能存放多少位数，始终占用4个字节的空间int(M) 跟 int数据类型是相同的。int(M) 只有跟 zerofill 结合起来，才能使我们清楚的看到不同之处

1.创建表t

mysql> create table t(id int(5) zerofill); --zerofill填充0

2.插入数据

mysql> insert into t(id) values(10);

3.显示select * from t

00010

普通索引 （常规索引）(normal)

CREATE INDEX idx_indexName ON mytable(username(length));

create index idx_myDeptIndex on detail(dept_id);

DROP INDEX [idx_indexName] ON mytable;

ALTER TABLE tbl_name ADD INDEX idx_index_name (column_list);

唯一索引（UNIQUE）

索引的值不能重复

唯一索引（UNIQUE KEY）

方式一：

CREATE UNIQUE INDEX ux_indexName ON mytable(username(length))

方式二:

ALTER table mytable ADD UNIQUE [ux_indexName] (username(length))

• 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
• 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
• 唯一性索引列允许空值，而主键列不允许为空值。
• 主键列在创建时，已经默认为空值 + 唯一索引了。
• 主键可以被其他表引用为外键，而唯一索引不能。
• 一个表最多只能创建一个主键，但可以创建多个唯一索引。
• 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

全文索引（FULLTEXT）

做全文检索使用的索引，我们有更好的替代品 ElacticSearch，所以实际使用不多，只当了解。通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。

你可能会说，用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本比较少时是合

适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N倍，速度不是一个数量级，但是全文索引可能存在精度问题。

你可能没有注意过全文索引，不过至少应该对一种全文索引技术比较熟悉：各种的搜索引擎。虽然搜索引擎的索引对象是超大量的数据，并且通常其背后都不是关系型数据库，不过全文索引的基本原理是一样的。

全文索引的版本支持

1. MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；

2. MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;

3. 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

使用全文索引的注意

1. 使用全文索引前，搞清楚版本支持情况；

2. 全文索引比 like + % 快 N 倍，但是可能存在精度问题；

3. 如果需要全文索引的是大量数据，建议先添加数据，再创建索引；

4. 对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。

1、创建表时创建全文索引

create table fulltext_test ( id int(11) NOT NULL AUTO_INCREMENT, content text NOT NULL, tag varchar(255), PRIMARY KEY (id), FULLTEXT KEY content_tag_fulltext(content,tag) // 创建联合全文索引列 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2、在已存在的表上创建全文索引

create fulltext index content_tag_fulltext on fulltext_test(content,tag);

3、通过 SQL 语句 ALTER TABLE 创建全文索引

alter table fulltext_test add fulltext index content_tag_fulltext(content,tag);

4、直接使用 DROP INDEX 删除全文索引

drop index content_tag_fulltext on fulltext_test;

5、通过 SQL 语句 ALTER TABLE 删除全文索引

alter table fulltext_test drop index content_tag_fulltext

6、全文检索的语法

select * from test where match(content) against('aaa');

6、空间索引（SPATIAL）

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。这是在地理位置领域使用

的一种索引，其他场景用的很少，所以不需要深入学习。

复合索引（联合索引）重要

当有多个查询条件时，我们推荐使用复合索引。索引的 组合使用 （索引合并）效率是低于 复合索引 的。

比如：我们经常按照 A列 B列 C列进行查询时，通常的做法是建立一个由三个列共同组成的复合索引而不是对每一个列建立普通索引。

关于复合索引的一些知识：

为什么要使用联合索引

减少开销。建一个联合索引 (Gid,Cid,SId) ，实际相当于建了 (Gid)、(Gid,Cid)、(Gid,Cid,SId)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引。对联合索引 (Gid,Cid,SId) ，如果有如下的sql: select Gid,Cid,SId from student where Gid =1 and Cid =2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where Gid =1 and Cid =2 and SId =3,假设假设每个条件可以筛选出10%的数据，如果只有单值

索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合Gid =2 and Cid = 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10%0%=1w，效率提升可想而知！

缺点。联合索引越多，索引列越多，则创建的索引越多，索引都是存储在磁盘里的，通过索引算法

(Btree代表索引算法使用二叉树的形式来做索引的)来查找数据，的确可以极大的提高查询效率，但是与此同时增删改的同时，需要更新索引，同样是需要花时间的，并且索引所占的磁盘空间也不小。

建议。单表尽可能不要超过一个联合索引，单个联合索引不超过3个

联合索引的创建注意事项

1. 最左前缀匹配原则 ，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、

like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

创建联合索引，就是括号里多了几个列

alert table test add idx_clo1_col2 table (a1,a2,a3)

create index idx_clo1_col2 on table(a1,a2,a3);

hash索引:

hash相信大家应该都很熟悉，hash是一种key-value形式的数据结构。实现一般是数组+链表的结

构，通过hash函数计算出key在数组中的位置，然后如果出现hash冲突就通过链表来解决。当然

还有其他的解决hash冲突的方法。hash这种数据结构是很常用的，比如我们系统使用HashMap来

构建热点数据缓存，存取效率很好。

hash结构存数据首先通过计算key的hash值来确定其在数组中的位置，如果有冲突就在该数组位

置建一个链表。这样很明显有几个问题：

即使是具有相同特征的key计算出来的位置可能相隔很远，连续查询效率低下。即 不支持范围查询 。

hash索引存储的是计算得到的hash值和行指针，而不存储具体的行值，所以通过hash索引查询数据需要进行两次查询(首先查询行的位置，然后找到具体的数据)

hash索引查询数据的前提就是计算hash值，也就是要求key为一个能准确指向一条数据的key，所以对于like等一类的匹配查询是不支持的。

所以我们可以知道的是hash索引适用于快速选取某一行的数据，超级大表中定位某一行特别快。

只要是只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引。

创建的语法

create index index_test using hash on test1(id);

你会发现创建了也没有用，因为InnoDB和myIsam都不支持hash索引。

创建自定义hash索引

若存储引擎不支持hash索引，又想拥有hash索引所带来的性能提升，则可以模拟InnoDB一样创建

哈希索引。

思路也比较简单，就是在B+tree基础上创建一个伪哈希索引。这和真正的hash索引不是一回事，因

为还是采用B-Tree进行查找，但是它使用的是hash值而不是键本身进行查找。只需要在查询的where子

句中手动指定使用hash函数即可。下面举个简单的例子：

比如：当我们需要存储大量的URL，并需要根据URL进行搜索查找。若用B-Tree来存储URL，存储

的内容就会很大。此时的查询语句就是：

select id from url where url = "www.baidu.com";

若删除原来的url列上的索引，而新增一个被索引的url_crc列，使用crc32做hash函数，则可以使用如下

方式查询：

select id from url where url = "www.baidu.com" and url_crc=CRC32("www.baidu.com");

这样做的话，性能就会有很大提升，因为mysql优化器会使用这个选择性高而体积很小的基于url_crc列的索引来完成查找。即使有多个记录相同的索引值，查找仍然很快，只需要根据hash值做快速的整数比较就能找到索引条目，然后一一返回对应的行。

缺点

1、需要维护hash值，可以手动维护，也可以使用触发器实现。

2、若数据表非常大的话，CRC32（）会出现大量hash冲突，则可以自己实现一个64位的hash函数，这个自定义的hash函数要返回整数而不是字符串，因为范围整数，对此效率更高。一个简单的办法就是使用MD5（）函数返回值的一部分来作为自定义的hash函数。但是这可能比自己写一个hash算法性能要差一些。

案例博客：https://segmentfault.com/a/1190000003741888