一、索引的概念
1、索引是什么
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。所以索引的本质是数据结构。可以简单理解为“排好序的快速查找数据结构”。在数据之外，数据库系统还维护这满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引
2、索引优缺点
-
优点
- 提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
-
劣势
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
二、MySQL索引
1、BTREE索引
BTREE索引.png
-
一棵B树，浅蓝色的块我们称为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色）和指针（黄色）
- 磁盘块1包含数据项17和35，包含指针P1、P2、P3，
- P1表示小于17的磁盘块
- P2表示在17和35之间的磁盘块
- P3表示大于35的磁盘块
-
真实的数据存储在叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99
-
非叶子节点不存储真实的数据，只存储指引索引方向的数据项，如17、35并不是真实存储在数据表中
-
查询过程：要查找数据项29
- 1、首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短可以忽略不计
- 2、通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计3次IO
-
真实的情况是，3层的B树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
三、索引分类
1、单值索引
一个索引只包含单个列，一个表可以有多个单列索引
- 建表
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);
2、唯一索引
索引列的值必须唯一，但允许有空值
- 建表
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);
3、主键索引
设定为主键后数据库会自动建立索引，innodb为聚簇索引
- 建表
CREATE TABLE customer
(
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id)
);
4、复合索引
一个索引包含多个列
- 随表一起创建索引
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_name),
KEY (customer_no, customer_name)
);
- 单独创建索引
CREATE INDEX idx_no_name ON customer(customer_no, customer_name);
5、基本语法
- 创建索引
CREATE [UNIQUE] INDEX [indexName] ON table_name(column)
- 删除索引
DROP INDEX [indexName] ON mytable;
- 查看
SHOW INDEX FROM table_name\G
四、索引的创建时机
1、适合创建索引的情况
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段，外键关系建立索引
- 单键/组合索引的选择问题，组合索引性价比更高
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
2、不适合创建索引的情况
- 表记录太少
- 经常增删改的表或字段
- where条件里用不到的字段不创建索引
- 过滤性不好的不适合建索引
网友评论