MySQL索引

一、索引介绍

1.索引是什么

索引是帮助MySQL高效获取数据的数据结构，能优化数据库的查询速度，降低服务器负载（不需要所有数据库中的所有数据，）。

索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

1. 索引有两种存储类型，包括B型树（BTREE）索引和哈希（HASH）索引，BTREE为默认索引。
2. 所有的存储引擎对每个表至少支持16个索引，总索引长度至少为256字节。

---

2.索引的优势和劣势

优势

1. 可以提高数据检索的效率，减低数据库IO成本，类似于书的目录。
2. 通过索引列对数据进行排序，减低数据排序的成本，降低CPU消耗。
   • 有索引的列会自动排序，包括单列索引和组合索引，只是组合索引的排序要复制一些。
   • 如果按照索引列的顺序进行排序，对于order by语句来说，效率会高很多。

劣势

1. 索引会占据磁盘空间。
2. 索引虽然会提高查询效率，但是会降低更新表的效率。每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或更新对于的所有文件。

并非在查询数据的时候总需要应用索引来优化查询，因为所有的创建和维护也需要消耗时间和空间。每插入一次都会根据索引进行排序。索引会影响用户的插入操作，这里的做法可以是在插入大量数据之前把索引删除，插入完成之后再把索引创建。

---

二、MySQL索引分类及创建

使用SHOW CREATE TABLE 表名;查看表结构。创建后使用该语句查看有没有创建成功！

1. 主键索引(PRIMARY)：索引列中的值必须是唯一的，不允许有空值。示例创建id为主键索引。

   CREATE TABLE user_primary(id int(11) NOT NULL AUTO_INCREMENT,name varchar(32) NOT NULL,PRIMARY KEY (id)
   );
   

   ---

2. 普通索引(INDEX)：MySQL中基本索引类型，允许在定义索引的列中插入重复值和空值（无限制）。为id创建一个名为Idx的普通索引。

   CREATE TABLE user_index(id int(11) NOT NULL AUTO_INCREMENT,name varchar(32) NOT NULL,INDEX Idx(id)
   );
   

   ---

3. 唯一索引(UNIQUE)：索引列中的值必须唯一，允许为空值。为id创建名为UniqIdx的唯一索引。

   CREATE TABLE user_unique(id int(11) NOT NULL AUTO_INCREMENT,name varchar(32) NOT NULL,UNIQUE INDEX UniqIdx(id)
   );
   

   虽然添加唯一索引可以约束字段的唯一性，但是有时候并不能提高用户查询速度，即不能实现优化查询的目的，使用时要充分参看场景。

   ---

4. 全文索引(FULLTEXT)：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。为类型为varchar的name创建名为FullIdx的全文索引。

   MyISAM和InnoDB中都可以使用全文索引。

   CREATE TABLE user_fulltext(id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,name varchar(32) NOT NULL,FULLTEXT INDEX FullIdx(name)
   ); 
   

   ---

5. 空间索引(SPATIAL)：MySQL在5.7开始支持了空间索引，支持OpenGIS几何数据模型。只能建立在空间数据类型上，提高系统获取空间数据的效率。只有MyISAM支持空间索引。创建空间索引时需要色合作SPATIAL参数。为relation创建一个命名为spatIdx的空间索引，

   CREATE TABLE user_spatial(relation geometry not null,SPATIAL INDEX spatIdx(relation)
   )engine = MyISAM;
   

   ---

6. 前缀索引：在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。创建一个名为NamePre长度为10前缀索引。

   CREATE TABLE user_prefix(name varchar(32) NOT NULL,INDEX NamePre(name(10))
   );
   

   ---

7. 单列索引(INDEX)：只对应一个字段的索引。为id创建单列索引。

   CREATE TABLE user_index(id int(11) NOT NULL AUTO_INCREMENT,name varchar(32) NOT NULL,INDEX(id)
   );
   

   ---

8. 组合索引(INDEX())：遵循最左前缀匹配原则(最左匹配原则)，一般会使用组合索引代替多个单列索引使用。为id和name创建一个命名为MultiIdx的组合索引。

   CREATE TABLE user_index_multi(id int(11) NOT NULL AUTO_INCREMENT,name varchar(32) NOT NULL,INDEX MultiIdx(id,name)
   );
   

   要使用该索引，用户必须使用这些字段中的第一个字段

   ---

   
   

三、索引的数据结构

索引的数据结构有两种，哈希表和B+树，下面分析一下它们的各自的特点。

HASH

hash表，以键值对（key-value）存储数据。使用Hash表的key可以存储索引列，value可以存储记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。显然这种并不适合作为经常需要查找和范围查找的数据库索引使用。

B+TREE

我们首先要分析一下索引在数据库中的需求：会频繁用到索引和会有范围查找需求。

优化点：尽量减少IO，降低树的高度，尽量使得数据都在一个层级内。

结合这些点，我们想到的最适合的数据结构就是B+TREE了，

B+TREE的数据结构特点是：

1. 它的节点中存储着多个元素，每个内节点有多个分叉。
2. 节点中的元素包含键值（没有数据），节点中的键值从小到大排列。
3. 父节点当中的元素不会出现在子节点中。
4. 所有的叶子结点都位于同一层，叶节点具有相同的深度。
5. 叶子节点之间使用双向链表连接，最底层的叶子节点形成了一个双向有序链表。
6. 只有叶子节点才会存储数据，非叶子节点只会存储键值。

B+树的最底层叶子节点包含了所有的索引项。B+TREE在查找所索引时，由于索引都是存放到最底层的叶子节上，所以每次查找都需要检索到叶子节点才能查询到数据。所以每次的磁盘的IO跟树的高度有直接的关系，从另一个角度来说，由于数据都被发到了叶子节点上，所以存放索引的磁盘块所存放的索引数是会跟着增加的。

又因为此时所有的索引都是以双向链表连接的，此时，只需要查找到索引即可立刻返回，无序后面的继续查找（当范围查找时，就只需要通过B+TREE查找一次找到第一个索引，之后直接根据双向链表获取下一个索引，岂不美哉）。

举个范围查找的例子：

假如查找9~26之间的索引，查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。

1. 首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步将磁盘1，磁盘2和磁盘3加载到内存，发生了三次磁盘IO。

2. 查找到9之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。

3. 第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。

主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。

B+树保证快速查询到索引，所以MySQL就采用了B+树的数据结构存储索引。

---

四、在已有的表上创建和删除索引

再次提示：每操作一次索引，就使用SHOW CREATE TABLE table_name查看表结构是否发生相应的变化。

1.创建索引

ALTER和ADD关键字

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name(ength),…)

【例】在user_index表中的name字段上建立名为NIdx的唯一索引

alter table user_index add unique index NIdx (name);

对于添加主键时，只能使用KEY，不能使用INDEX。

---

CREATE INDEX关键字

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col_name[(length)],…)

【例】在user_index表中的name字段上建立名为NIdx的唯一索引

create unique index NIdx on user_index(name(20));

---

2.删除索引

AUTO_INCREMENT约束字段的索引不能被删除。

ALTER TABLE table_name DROP INDEX index_name

【例】删除user_index表中的name普通索引

--1.查看user_index表中结构
show create table user_index;
CREATE TABLE `user_index` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(32) NOT NULL,KEY `id` (`id`),--待删除KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;--2.删除name的index索引，并且查看表结构
alter  table user_index drop index name;
show create table user_index;
CREATE TABLE `user_index` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(32) NOT NULL,KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

---

DROP INDEX index_name ON table_name

【例】 删除user_index表中的name的唯一索引

--1.查看usesr_index表的结构
show create table user_index;
CREATE TABLE `user_index` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(32) NOT NULL,UNIQUE KEY `name` (`name`),KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8--2.删除名为'name'的索引
drop index name on user_index;
show create table user_index;CREATE TABLE `user_index` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(32) NOT NULL,KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

关键在于多多练习~