文章目录
1. 索引
1.1 索引定义
索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
1.2 索引实现
索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关。MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
1.2.1 MyISAM索引实现
- 索引结构为b+tree，叶子结点data域存放数据记录地址。
- 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
- 主索引和辅助索引结构相同都为b+tree，区别在于主索引要求key唯一，辅助索引key可重复。
- 索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
1.2.2 InnoDB索引实现
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
- InnoDB的数据文件本身就是索引文件。表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这种索引也叫聚簇索引。
- InnoDB要求表必须有主键，因为数据文件是按照主键去聚集（MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键；如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
- InnoDB的辅助索引叶子结点的data域存储相应记录主键的值而不是地址。因此辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助。
- 不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
- 建议使用自增字段作为主键，因为非单调的字段作为主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，
主索引，数据文件：
辅助索引：
1.3索引结构
MYSQL支持的索引结构包括hash结构，B+树结构。存储引擎支持的索引类型也不同，MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
1.3.1 B+树
B+树由B树和索引顺序访问方法演化而来，它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行链接。如下图：
B+树索引在数据库中的一个特点就是高扇出性，例如在InnoDB存储引擎中，每个页的大小为16KB。在数据库中，B+树的高度一般都在2～4层，这意味着查找某一键值最多只需要2到4次IO操作，这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作，2～4次的IO操作意味着查询时间只需0.02～0.04秒。
1.3.2 hash和B+树区别
| hash | B+树 |
|---|---|
| hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。 | B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。 |
| hash索引因为经过hash函数建立索引后，索引顺序与原顺序无法保持一致，hash函数是不可预测的，因此不能支持范围查询，模糊查询，以及多列索引的最左前缀匹配。 | B+树的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。 |
| hash索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。 | B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。 |
因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。
1.4 索引优点
- 可以大大加快数据的查询速度，这也是创建索引的主要原因。
- 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
- 在实现数据的参考完整性方面，可以加速表和表之间的连接。
- 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。
1.5 索引缺点
- 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
- 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
1.6 索引分类
- 普通索引和唯一索引
- 普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
- 唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
- 主键索引是一种特殊的唯一索引，不允许有空值。
- 单列索引和组合索引（联合索引）
- 单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
- 组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
- 聚簇索引和非聚簇索引（InnoDB)
- 在InnoDB存储引擎中，可以将B+树索引分为聚簇索引和辅助索引（非聚簇索引）。无论是何种索引，每个页的大小都为16KB，且不能更改。
- 聚簇索引是根据主键创建的一棵B+树，聚簇索引的叶子节点存放了表中的所有记录。
- 非聚簇索引（辅助索引）是根据索引键创建的一棵B+树，其叶子节点仅存放索引键值，以及该索引键值指向的主键。
- 非聚簇索引来查找数据，那么当找到辅助索引的叶子节点后，很有可能还需要根据主键值查找聚簇索引来得到数据，这种查找方式又被称为书签查找。因为辅助索引不包含行记录的所有数据，这就意味着每页可以存放更多的键值，因此其高度一般都要小于聚簇索引。
- 全文索引
- 全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。是搜索引擎的关键，会比模糊查询的性能好。
- 空间索引
- 空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。
联合索引（扩）
- 多个字段（多个列）上建立索引。
- 联合索引存储结构依旧是B+树，键值数量大于等于2。
- 使用时要遵循最左前缀集合（最左匹配原则等），即只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。
1.7 索引创建
1.7.1 在创建表的时候创建索引
使用CREATE TABLE创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。创建表时创建索引的基本语法如下：
CREATE TABLE table_name
[col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC]
其中：UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；INDEX与KEY为同义词，两者作用相同，用来指定创建索引。
例子
CREATE TABLE t1 (
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);
1.7.2 已存在的表上创建索引
在已经存在的表中创建索引，可以使用ALTER TABLE语句或者CREATE INDEX语句。
-
ALTER TABLE
-
语法：
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC] -
例子：在bookId字段上建立名称为UniqidIdx的唯一索引
ALTER TABLE book ADD UNIQUE INDEX UniqidIdx (bookId);
-
-
CREATE INDEX
-
语法：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col_name [length],...) [ASC|DESC] -
例子：在bookId字段上建立名称为UniqidIdx的唯一索引
CREATE UNIQUE INDEX UniqidIdx ON book (bookId);
-
1.8 索引设计原则
- 不建议添加索引
- 避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。
- where条件中用不到的字段不要建立索引，应该经常用于查询的字段创建索引，但要避免添加不必要的字段。
- 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
- 数据重复且分布均匀的字段不适合建立索引。在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。（例子：比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。）
- 参与列计算的列不适合建索引，会失效。
- 建议添加索引
- 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
- 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。
1.9 索引生效
1.9.1 判断索引生效
可以使用EXPLAIN语句查看索引是否正在使用。
- 例子：其year_publication字段上建立了普通索引。执行如下语句：
EXPLAIN SELECT * FROM book WHERE year_publication=1990;
EXPLAIN语句将为我们输出详细的SQL执行信息，其中：
- possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。
- key行是MySQL实际选用的索引。
如果possible_keys行和key行都包含year_publication字段，则说明在查询时使用了该索引。
1.9.2 避免索引失效
- 使用组合索引时，需要遵循“最左前缀”原则；
- 不在索引列上做任何操作，例如计算、函数、类型转换，会导致索引失效而转向全表扫描；
- 尽量使用覆盖索引（访问索引列的查询），减少 select * 覆盖索引能减少回表次数；
- MySQL在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描；
- LIKE以通配符开头（%abc）MySQL索引会失效变成全表扫描的操作；
- 字符串不加单引号会导致索引失效（可能发生了索引列的隐式转换）；
- 少用or，用它来连接时会索引失效。
1.10 索引重构
判断索引需要重构
-
主观上判断：
- 表上频繁发生update,delete操作；
- 表上发生了alter table …move操作（move操作导致了rowid变化）。
-
客观上判断：
- 一般看索引是否倾斜的严重，是否浪费了空间，对索引进行结构分析
analyze index index_name validate structure;- 在相同的session中查询index_stats表：
select height,DEL_LF_ROWS/LF_ROWS from index_stats;当查询的height>=4（索引的深度，即从根到叶节点的高度）或DEL_LF_ROWS/LF_ROWS>0.2的情况下，就应该考虑重建该索引。
重建索引
-
drop+create:删除原索引，然后再创建索引，（不建议）
drop index index_name on table_name; create index index_name on table_name (index_column); -
rebuild
alter index indexname rebuild; alter index indexname rebuild online;
rebuild是快速重建索引的一种有效的办法，因为它是一种使用现有索引项来重建新索引的方法。如果重建索引时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题。由于新旧索引在建立时同时存在，因此，使用这种重建方法需要有额外的磁盘空间可供临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。
rebuild重建索引的过程：
- Rebuild以index fast full scan或table full scan方式（采用那种方式取决于cost）读取原索引中的数据来构建一个新的索引，重建过程中有排序操作，rebuild online执行表扫描获取数据，重建过程中有排序的操作；
- Rebuild会阻塞DML操作，rebuild online不会阻塞DML操作；
- rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表，所有rebuild online时索引的变化都记录在这个表中，当新的索引创建完成后，把这个表的记录维护到新的索引中去，然后drop掉旧的索引，rebuild online就完成了。
重建索引过程中的注意事项：
- 执行rebuild操作时，需要检查表空间是否足够；
- 虽然说rebuild online操作允许DML操作，但还是建议在业务不繁忙时间段进行；
- Rebuild操作会产生大量Redo Log；
2 索引问题回答
| Q | 问题 | 思路 |
|---|---|---|
| 1 | 说一说你对MySQL索引的理解 | 索引定义+实现+优点+缺点 |
| 2 | 索引有哪几种？ | 索引分类 |
| 3 | 如何创建及保存MySQL的索引？ | 索引创建（创建表时+已存在表） |
| 4 | MySQL怎么判断要不要加索引？ | 索引设计原则（不该建立索引+应该建立索引） |
| 5 | 只要创建了索引，就一定会走索引吗？ | 联合索引的最左前缀原则 |
| 6 | 如何判断数据库的索引有没有生效？ | 索引生效判断explain |
| 7 | 如何评估一个索引创建的是否合理？ | 索引设计原则（不该建立索引+应该建立索引） |
| 8 | 索引是越多越好吗？ | 索引的缺点 |
| 9 | 数据库索引失效了怎么办？ | 索引生效判断以及避免失效 |
| 10 | 所有的字段都适合创建索引吗？ | 索引设计原则（不该建立索引+应该建立索引） |
| 11 | 说一说索引的实现原理 | 索引实现（MyISAM+InnoDB） |
| 12 | 介绍一下数据库索引的重构过程 | 索引重构（判断重构+索引重构） |
| 13 | MySQL的索引为什么用B+树？ | 索引结构（B+树定义、B+树相较hash索引的优点） |
| 14 | 联合索引的存储结构是什么，它的有效方式是什么？ | 定义+结构+最左前缀原则 |
| 15 | MySQL的Hash索引和B树索引有什么区别？ | 索引结构（查询过程，支持查询，稳定性） |
| 16 | 聚簇索引和非聚簇索引有什么区别？ | 定义+结构+查询过程 |
| 17 | 什么是联合索引？ | 定义+结构+最左前缀原则 |
| 18 | select in语句中如何使用索引？ | 下面补充 |
| 19 | 模糊查询语句中如何使用索引？ | 下面补充 |
Q18 select in语句中如何使用索引？
索引是否起作用，主要取决于字段类型：
- 如果字段类型为字符串，需要给in查询中的数值与字符串值都需要添加引号，索引才能起作用。
- 如果字段类型为int，则in查询中的值不需要添加引号，索引也会起作用。
IN的字段，在联合索引中，按以上方法，也会起作用。
Q19 模糊查询语句中如何使用索引？
情况1：通配符在后面 like ‘xxx%’ ，可以使用索引
情况2：通配符在前面 like ‘% xxx’ ，索引失效 ，改造成第一种情况
例子：需要根据手机号码后四位进行模糊查询 mobile like ‘%8765’
- 对模糊查询的列，通过reverse反转函数进行反转生成冗余列（虚拟列）mobile_reverse。如 mobile为17312345678，那么 mobile_reverse存储 87654321371。
- 对冗余列上建立新的索引。
- 模糊查询的时候，通过reverse反转函数进行模糊查找，即在回到第一种情况，通配符在后面，这时候就可以使用冗余列新建的索引进行查询记录。 mobile_reverse like reverse(’%5678’) 即mobile_reverse like ‘8765%’ 就可以使用索引了
726
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
