Mysql笔记（二）索引介绍

索引介绍

索引是什么

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目
录，能加快数据库的查询速度。

索引的优势和劣势

优势：

• 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。 – 检索

• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。 --排序

  • 被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
  • 如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。
  • where 索引列在存储引擎层 处理 - 索引条件下推 ICP（Index Condition Push）。
  • 覆盖索引 select 字段 字段是索引。

劣势：

• 索引会占据磁盘空间。
• 索引虽然会提高查询效率，但是会降低更新表的效率，比如每次对表的增删改操作。
• Mysql不仅要保存数据，还有保存或者跟新对应的索引文件。

索引的分类

• 单列索引
• 组合索引
• 全文索引
• 空间索引
• 位图索引（Oracle）

索引的使用

创建索引

• 单列索引之普通索引

CREATE INDEX index_name ON table(column(length));
ALTER TABLE table_name ADD INDEX index_name (column(length));

• 单列索引之唯一索引

CREATE UNIQUE INDEX index_name ON table(column(length));
ALTER TABLE table_name ADD UNIQUE INDEX index_name(column);

• 单列索引之全文索引

CREATE UNIQUE INDEX index_name ON table(column(length));
ALTER TABLE table_name ADD UNIQUE INDEX index_name(column);

• 组合索引

CREATE FULLTEXT INDEX index_name ON table(column(length));
ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column);

删除索引

DROP INDEX index_name ON table;

查看索引

SHOW INDEX FROM table_name;

索引原理分析

索引的存储结构

索引结构

• 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
• MyISAM和InnoDB存储引擎：只支持B+TREE索引， 也就是说默认使用BTREE，不能够更换
• MEMORY/HEAP存储引擎：支持HASH和BTREE索引

B树和B+树

数据结构示例网站：

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B树图示

B树是为了磁盘或其它存储设备而设计的一种多叉（下面你会看到，相对于二叉，B树每个内结点有多个
分支，即多叉）平衡查找树。 多叉平衡

• B树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数。
• 如果是三层树结构支撑的数据可以达到20G，如果是四层树结构支撑的数据可以达到几十T。

B+Tree对比BTree和的优势

1. 磁盘读写代价更低
   一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。
2. 查询速度更稳定
   由于B+Tree非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。
3. B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是有顺序的。

非聚集索引（MyISAM）

主键索引

辅助索引（次要索引）

聚集索引（InnoDB）

主键索引

• 若表没建立主键，Mysql会自动找唯一字段或自动生成伪列当主键
• 主键的创建建议使用整数，不要使用大字符串（UUID等），因为这种字符本身无序。

辅助索引（次要索引）

• 从辅助索引树上找到主键后在主键索引树下找到数据称为（回表）
• 在可能的情况下优化能避免回表，利用组合索引即在当前辅助索引树中包含需要查询的字段，则可以避免回表称为（索引覆盖）

索引树

利用组合索引 完成覆盖索引（利用组合索引完成在辅助索引树的遍历，不回表）