MySQL学习 - 索引

索引在MySQL中也称为“键 key”，是存储引擎用于快速找到记录的一种数据结构。通过构建合理的索引，可以优化查询性能，提高查询效率，同时通过添加约束，也保证了字段的唯一性和数据的完整性。通常我们在表中的某一个或多个字段上构建索引，例如经常被查询的字段（where子句）、分组的字段（group by子句）、联合查询（主键或外键字段）等等。

索引分类

1. 根据存储引擎适用范围区分：

• Innodb & MyISAM存储引擎 -> B+树索引：层数越多，数据量呈指数级增长
• Memory & NDB：哈希（HASH）索引：查询单条数据较快，大范围查询较慢

2. 根据约束性区分：

• 普通索引：增加查询速度，可以创建在任何字段（列）上

create table dept(
	deptno int,
	name varchar(20),
	loc varchar(20),
	INDEX idx_deptno(deptno) --普通索引，一般索引命名采取idx_column_name格式
 )

• 唯一索引：增加查询速度和约束，例如主键索引primary key（不为空且不能重复），唯一索引unique key（可以为空但不能重复）

create table dept(
	deptno int,
	name varchar(20),
	loc varchar(20),
	UNIQUE INDEX idx_deptno(deptno) --唯一索引，代表deptno字段数据可以为空，但是数值不能重复
 )

• 全文索引：主要关联在数据类型为char，varchar，text的字段上，增加数据量较大字符串类型查询速度

create table dept(
	deptno int,
	name varchar(20),
	loc varchar(20),
	FULLTEXT INDEX idx_loc(loc) --全文索引
 )

• 以上为基本形式的单字段索引，也可以根据实际需求，构建多个字段组合的索引，称为“多列索引”。多列索引只有在关联字段中的第一个字段被调用才会调用（B+树索引最左匹配特性）。

create table dept(
	deptno int,
	name varchar(20),
	loc varchar(20),
	INDEX idx_name_loc(name, loc) --查询时，通过 where name = ‘xxxx’即可调用关联索引；但通过 where loc = ‘xxx’则无法调用
 )
--查看SQL语句调用情况，使用“explain”,查看返回列表的possible_keys & key 字段是否有显示，有则证明调用成功
-- explain select * from dept where name = ‘office’ ;

索引是一种数据结构，意味着创建索引也会占用一定的空间。因此，不要盲目的创建索引，导致索引占用空间过多而降低查询性能。使用不同索引结构的本质是尽量降低每次查找数据时，磁盘读写的次数，最好控制在一个常量集。
通常情况，使用自增ID作为主键，业务相关主键作为唯一索引（unique key）

存储引擎

为什么使用B+树作为索引，这篇文章从B+树的由来，及各个树状结构的优缺点进行讲解，可以深入了解。

为什么M有SQL数据库索引选择使用B+树？

B+树索引 - 高度通常在2-4层（2-4次读写）

磁盘块包含：1）数据项（深蓝色）；2）指针（黄色）。例如磁盘块1，包含数据项17 和 35，指针P1 代表数据 < 17的磁盘块，P2代表17< 数据< 35的磁盘块，P3代表数据>35的磁盘块。非叶子节点不存储真实数据，只存储指引细分搜索方向的数据项和分段指针；真实而全面的数据则存储在最底层的叶子节点[3,5,9,10,13,15,28,29,36,60,75,79,90,99。
查找过程：例如查找数据36
1. 将磁盘块1加载到内存，发生1次读写：17<29<35，使用指针P2
2. 将P2指针指向的磁盘块3加载到内存，发生第2次读写：26<29<30，使用磁盘块3中的P2指针
3. 将磁盘块3中P2指针指向的磁盘块8加载到内存，发生第3次读写，最终找到数据29

B+树索引可以细分为聚集索引（clustered index）和辅助索引（secondary index）。

INNODB - 支持事务，属于聚簇索引结构（叶子节点存储真实数据）

• 事务的4个特性：
  原子性（atomicity），一致性（consistency），隔离性（isolation），持久性（durability）
  例如：银行收汇款可以看做一项事务，该事务包含汇款操作和收款操作。
  原子性：当汇款操作，即从账户中减少金额完成后，对应的收款操作，即另一账户中增加金额也应完成，才算一个完整的事务结束。否则，任意环节未完成，都不算事务结束。
  一致性：汇款减少的金额 = 收款增加的金额
  隔离性：无法做到同时向多人汇款。因为每笔收汇款操作都是隔离的，必须保证账户中有足够的余额，只有完成一个事务后，才能进行下一个
  持久性：收汇款操作结束后，账户余额的结果是永久保存的
• 主键索引（聚集索引）
  InnoDB存储引擎中表内数据按主键顺序存放。聚集索引就是按照每张表的主键构造一颗B+树，叶子节点存放的是整张表的行记录数据。
• 辅助索引
  除了主键索引以外的其他索引都是辅助索引（非聚集索引）。叶子节点存放的是索引键值和聚集索引建（主键B+树）位置。
  
  MyISAM - 不支持事务，属于非聚簇索引结构（叶子节点存储数据位置）
• 主键索引和辅助索引的叶子节点都存储的是数据存放的地址