索引

什么是索引

• MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质： 索引是数据结构。
• 可以简单理解为：排好序的快速查找数据结构

索引的优缺点

优点

• 提高数据检索的效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

索引的分类

基本语法

创建

CREATE [UNIQUE] INDEX [indexName] ON table_name(column);

删除

DROP INDEX [indexName] ON table_name;

查看

SHOW INDEX FROM table_name;

分类

单值索引

• 定义：即一个索引只包含单个列，一个表可以有多个单列索引
• 语法：

--和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200), 
PRIMARY KEY(id), 
KEY (customer_name) --单值索引
);

--单独创建单值索引
CREATE INDEX idx_customer_name ON customer(customer_name);

唯一索引

• 定义：索引列的值必须唯一，但允许有空值
• 语法：

--和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200), 
PRIMARY KEY(id), 
KEY (customer_name), --单值索引
UNIQUE (customer_no) --唯一索引
);

--单独创建唯一索引
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

主键索引

• 定义：设定为主键后数据库会自动建立索引，innodb为聚簇索引
• 语法：

--和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200), 
PRIMARY KEY(id) --主键索引
);

复合索引

• 定义：即一个索引包含多个列
• 语法：

--和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200), 
PRIMARY KEY(id), 
KEY (customer_name), --单值索引
UNIQUE (customer_no), --唯一索引
KEY (customer_no,customer_name) --复合索引
);

--单独创建复合索引
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

MySQL的索引

B树与B+树: 链接.

为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？

1. B+树的磁盘读写代价更低
   B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点 的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了
2. B+树的查询效率更加稳定
   由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相同

MySQL在创建表时，会根据主键来创建主键索引（如果没有主键，会用一个隐藏值来作为主键）。主键索引所构建的B+树，表中所有的记录都存放在了树的最后一层。且与一般的B+树不同的是：叶子节点间的指针是双向的

索引的使用场景

适合索引的场景

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键关系建立索引
• 单键/组合索引的选择问题，组合索引性价比更高
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

不适合索引的场景

• 表记录太少（有无索引差别不大）
• 经常增删改的表或者字段
• Where 条件里用不到的字段不创建索引
• 过滤性不好的不适合建索引（重复性较高，比如国籍、性别之类的字段）