索引
在数据库的使用过程中，查询是占据了绝大部分，即使是更新删除，也得先查到对应的数据，所以如何加快查找速度是数据库要解决的一个重大问题。
想要加快查询的速度，可以往前一步思考：如何存储数据，才能在查询的时候更快？
所以 如何加快查询速度 问题就变成一个 如何组织数据的结构 问题。
而 索引 就是一种解决方法。换言之：索引，是一种数据结构。
- 索引的优点：
- 索引的缺点：
- 维护索引需要消耗数据库资源
- 索引需要占用磁盘空间
- 当对标的数据进行增删改的时候，因为需要维护索引，速度会受影响
索引分类
MySQL 中的索引分为：
- InnoDB (主键索引、唯一索引、单值索引、复合索引):
- 主键索引：一个列被设定为主键后会自动建立主键索引，不允许 NULL；InnoDB为聚簇索引
- 唯一索引：列的值必须唯一，但允许 NULL
- 单值索引(普通索引)：即一个索引只包含一个列，一个表可以有多个单值索引
- 复合索引：即一个索引包含多个列
- MyISAM (主键索引、唯一索引、单值索引、复合索引、全文索引)
Tip
单值索引 与 复合索引：
给 name 和 age 分别建立了单值索引，在 WHERE name 或者 WHERE age 的时候会使用上索引。
但是 WHERE name AND age 的时候就用不上索引。
如果给 name 和 age 一起建立了复合索引，就能用上索引。
索引基本操作
主键索引
主键索引在建表时创建主键时自动创建。
如果建表的时候不指定主键，MySQL 会默认创建一个 row_id 作为主键并创建索引。
| mysql> CREATE TABLE t_user(
-> id varchar(20) primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> show index from t_user;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_user | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)
|
唯一索引
单值索引
-
建表时创建索引：
| mysql> CREATE TABLE t_user(
-> id varchar(20),
-> name varchar(20),
->
-> KEY(name)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> show index from t_user;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_user | 1 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)
|
-
建表后创建索引：
| mysql> CREATE INDEX 索引名 ON 表名(列名);
|
| mysql> CREATE INDEX idx_id ON t_user(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from t_user;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_user | 1 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| t_user | 1 | idx_id | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)
|
-
删除索引：
| mysql> DROP INDEX 索引名 ON 表名;
|
| mysql> DROP INDEX idx_id ON t_user;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from t_user;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_user | 1 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
|
复合索引
-
建表时创建索引：
| mysql> CREATE TABLE t_user(
-> id varchar(20),
-> name varchar(20),
-> age int,
->
-> KEY(name, age)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> show index from t_user;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_user | 1 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| t_user | 1 | name | 2 | age | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)
|
-
建表后创建索引：
| CREATE INDEX 索引名称 ON 表名(列名1, 列名2, ...);
|
Tip
命中索引的原则：
给 name 和 age 建立复合索引，在查询时，遵循：
- 最左匹配原则：
- WHERE name, age, bir -> 可以 使用索引
- WHERE name, bir, age -> 无法 使用索引
- WHERE bir, age, name -> 无法 使用索引
- WHERE age, bir -> 无法 使用索引
- WHERE name, bir -> 无法 使用索引
- MySQL 优化器会动态调整字段顺序以顺利使用索引，所以最终是以下情况(只要包含复合索引的字段就能命中)：
- WHERE name, age, bir -> 可以 使用索引
- WHERE name, bir, age -> 可以 使用索引，会被调整为 name, age, bir
- WHERE bir, age, name -> 可以 使用索引，会被调整为 name, age, bir
- WHERE age, bir -> 无法使用索引
- WHERE name, bir -> 可以 使用索引，会先查询 name
索引结构
MySQL 的索引使用的是 B+树。这是一种自平衡数据结构。
一条 记录 由「主键 + 数据 + next指针」组成，多条数据形成链表。
N 条数据构成一张 数据页。N 具体是多少看记录的大小。InnoDB 一页默认是 16KB，那么一张数据页可以存：
| 16K B
- 38 B (File Header)
- 56 B (Page Header)
- 26 B (Infimum + supremum)
- 8 B (File Tailer)
- ? B (Page Directory)
- ? B (Free Space)
÷ 记录大小
-----------------------
= 一张数据页可以存储的记录条数
|
这些页处于 B+树的叶子结点这一层，在叶子结点往上，是 页目录。
页目录不存储记录的数据，只存储每一页的第一条记录的 「主键 + 指向记录的指针」。
页目录的大小也是一页默认 16KB。这意味着一张页目录，可以记录很多很多数据页的地址。
页目录之上，还可以有更高一层页目录，最顶层的页目录，也就是 B+树的根结点，称为 顶层页。
顶层页是常驻内存的，所以查找一个数据时，从内存中找到顶层页，比对主键得到下一层的地址，然后去磁盘中把一整页的数据拖出来。
如果这一页是页目录，就继续找到下一层的地址，如果是数据页就从第一条记录遍历对比主键，直到找到数据返回。
| func (p Page)find(id int) (Node, error) {
var i int
for i = 0; i < len(p.Data); i++ { // Data 按 Primary 从小到大排序
if id >= p.Data[i].Primary { // 确定下一层地址
break
}
}
page := loadPage(p.Data[i].Addr) // 从磁盘中加载整页数据
if page.Type == dir { // 如果是页目录
pageDir.find(id) // 记录查找
}
// 如果是数据页
node := page.First // 从第一条记录开始
for node != nil {
if node.Id != id { // 遍历记录链表，不是就下一个
node = node.Next
continue
}
return node, nil // z找到了返回
}
return nil, errors.New("not found!") // 遍历完还没找到，返回找不到
}
|
- B+树的特点是数据都在叶子结点，这样使得 B+树适合范围查找。
- 在叶子结点的上一层是页目录，页目录不存储数据，这样使得页目录可以存储更多数据页的信息。
- 顶层页常驻内存，可以减少一次 IO。
- 基于这些特点，MySQL 才能在索引方面表现的非常出色。
聚簇索引 和 非聚簇索引
- 聚簇索引：数据和索引放在一起，找到索引就找到了数据
- 非聚簇索引：数据和索引不放在一起，找到索引后，索引指向数据对应的行，得自行访问。
在 InnoDB 中：
- 默认为主键创建聚簇索引，即使不指定主键，也会找一个有非空约束和唯一约束的列创建聚簇索引，如果这种列也没有，会创建一个影藏的主键 row_id，然后建立聚簇索引。
- 在聚簇索引上创建的索引称之为辅助索引，辅助索引中存放的是「列的值 + 主键」，在查询是找到列的值以后，还需要去主键索引上二次查找，拿到数据。
MySQL 中的 InnoDB 引擎处理索引的方式是，将主键组织到一棵 B+树中，行数据就存储在叶子结点上，所以这是聚簇索引，如果使用了 WHERE id = 14 这样的条件查询的时候，则直接去主键索引树上查找，然后获得行数据。
如果使用 WHERE name = 'Boii' 这样的条件查询，则先在辅助索引上找到行，得到 Boii 对应的主键，然后拿着主键去主键索引上二次查找，最终找到整行数据。
而 MySQL 中的 MyISAM 引擎使用的是非聚簇索引，主键索引还是辅助索引都不存储数据，不管是通过 WHERE id = 14 使用主键索引还是通过 WHERE name = 'Boii' 都是直接检索到对应的物理地址，从而访问到整行数据。
- 为什么 InnoDB 中辅助索引不直接存储地址而是主键？为了数据一直性和可维护性。如果存地址，那主键索引上的数据有变动的时候还得去维护所有辅助索引，而主键是不变的，所以选择存主键。
- 为什么用 B+树而不是 B树？因为 B+树的数据都在叶子结点上，适合范围查找，B树不是所有数据都在叶子结点上的，所以范围查找的时候很麻烦。
- 什么时候用 InnoDB，什么时候用 MyISAM？通常用 InnoDB，因为 MyISAM 不支持事务，她的设计比较偏向查询。如果一张表只用来查询，那么可以考虑 MyISAM。