一文了解 MySQL 索引及其原理

MySQL索引

1.数据库中有哪些索引类型?

索引的几种类型：唯一索引、主键索引、聚集索引、普通索引、组合索引、全文索引

唯一索引：在创建唯一索引时要不能给具有相同的索引值。在MySQL中创建索引：

CREATE UNIQUE INDEX indexName ON mytable(username(length));

主键索引：在我们给一个字段设置主键的时候，它就会自动创建主键索引，用来确保每一个值都是唯一的。

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );  

聚集索引：我们在表中添加数据的顺序，与我们创建的索引键值相同，而且一个表中只能有一个聚集索引。

普通索引：它的结构主要以B+树和哈希索引为主，主要是对数据表中的数据进行精确查找。

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

ALTER TABLE mytable ADD INDEX username(length);

**组合索引：**一个表中含有多个单列索引不代表是组合索引,通俗一点讲组合索引是:包含多个字段但是只有索引名称

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );  

全文索引：它的作用是搜索数据表中的字段是不是包含我们搜索的关键字，就像搜索引擎中的模糊查询。

ALTER TABLE tablename ADD FULLTEXT(column1, column2)

使用索引的优点：

• 提高数据的搜索速度
• 加快表与表之间的连接速度
• 在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。

使用索引的缺点:

• 在我们建立数据库的时候，需要花费的时间去建立和维护索引，而且随着数据量的增加，需要维护它的时间也会增加。
• 在创建索引的时候会占用存储空间。
• 在我们需要修改表中的数据时，索引还需要进行动态的维护，所以对数据库的维护带来了一定的麻烦。

2.数据库索引底层实现

不懂数据库索引的底层原理？

MySQL中，索引的底层实现构主要是：哈希索引和B+树索引。

哈希索引的底层数据结构就是哈希表，查询性能最快；

B树索引：B树实现

B+树索引：B+树实现

3. 为什么选用B+树？

红黑树等数据结构也可以用来实现索引，但是文件系统以及数据库系统普遍采用B-Tree/B+Tree作为索引结构。

原因：索引本身也很大，因此索引往往是以索引文件的形式存储在磁盘上。所以，索引查找的过程就会产生磁盘的I/O操作，相比于内存存取，I/O存取消耗要高几个数量级，所以索引的优劣最重要的指标就是在查找过程中的磁盘I/O存取次数。

4. 哈希索引与B+树如何选用？

Hash索引是无序的，所以只适用于等值查询，而不能用于范围查询。大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树索引就可以了。

5. 聚集索引和非聚集索引的区别

聚集索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

区别：

1. 使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
2. 非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
3. 不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。

6. 什么情况下设置了索引但是会失效？

1. 条件中有or
2. 对于多列索引，不是使用的第一部分，则不会使用索引
3. like查询时以%开头
4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引