MySQL索引
【MySQL】MySQL索引
目录
-
索引的作用
-
索引种类
-
索引为何不可用
-
索引原则与误区
-
执行计划
-
测试案例
索引的作用
-
提高查询效率
-
消除数据分组/排序
-
避免"回表"查询
-
优化聚合查询
-
用于多表JOIN关联查询
-
利用唯一性约束，保证数据唯一性
-
InnoDB行锁实现
-
增加I/O成本
-
增加磁盘空间
-
不适合的索引，或索引过多，都不是好事
索引种类
-
BTREE，InnoDB & MyISAM
-
Fractal TREE，TokuDB
-
HASH HEAP NDB InnoDB AHI
-
RTREE
-
FULLTEXT
索引讲解
-
聚集索引
-
主键索引
-
唯一索引
-
联合索引
-
覆盖索引
-
前缀索引
聚集索引
-
聚集索引是一种特殊的索引，该索引中键值的逻辑顺序决定了表数据行的物理顺序；
-
每张表只能建一个聚集索引，除了TokuDB引擎；
-
InnoDB中，聚集索引即表，表即聚集索引；
-
MyISAM没有聚集索引的概念
-
聚集索引优先选择列
-
INT/BIGINT;
-
数据连续（单调顺序）递增/自增；
-
不建议的聚集索引
-
修改频繁的列；
-
新增数据太过离散随机；
-
InnoDB聚集索引选择次序原则
-
显示声明的主键；
-
第一个NOT NULL的唯一索引
-
ROWID（实例级，6bytes）
创建测试表1(显示指定主键)
mysql> create table t(id int auto_increment,name varchar(10),primary key(id));
Query OK, 0 rows affected (0.02 sec)
TABLE: name test/t, id 23, flags 1, columns 5, indexes 2, appr.rows 0
COLUMNS: id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name: DATA_VARMYSQL len 30; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
INDEX: name PRIMARY, id 25, fields 1/4, uniq 1, type 3
root page 3, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: id DB_TRX_ID DB_ROLL_PTR name
INDEX: name idx_name, id 26, fields 1/2, uniq 2, type 0
root page 4, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: name id
从上面可以看到InnoDB选择了主键id作为唯一索引，而索引idx_name为普通索引
创建测试表2(不指定主键且指定一个非空唯一索引)
mysql> create table t1(id int ,name varchar(10) not null,unique key idx_name(name));
Query OK, 0 rows affected (0.01 sec)
TABLE: name test/t1, id 25, flags 1, columns 5, indexes 1, appr.rows 0
COLUMNS: id: DATA_INT DATA_BINARY_TYPE len 4; name: DATA_VARMYSQL DATA_NOT_NULL len 30; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
INDEX: name idx_name, id 29, fields 1/4, uniq 1, type 3
root page 3, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: name DB_TRX_ID DB_ROLL_PTR id
从日志看到，聚集索引选择的是idx_name
创建测试表3(不指定主键也无非空唯一索引)
mysql> create table t2(id int ,name varchar(10),key idx_name(name));
Query OK, 0 rows affected (0.02 sec)
TABLE: name test/t2, id 26, flags 1, columns 5, indexes 2, appr.rows 0
COLUMNS: id: DATA_INT DATA_BINARY_TYPE len 4; name: DATA_VARMYSQL len 30; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
INDEX: name GEN_CLUST_INDEX, id 30, fields 0/5, uniq 1, type 1
root page 3, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR id name
INDEX: name idx_name, id 31, fields 1/2, uniq 2, type 0
root page 4, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: name DB_ROW_ID
从日志可以看出，InnoDB选择的是rowid作为聚集索引
创建测试表4(包含以上三种索引)
mysql> create table t3(id int auto_increment,name1 varchar(10) not null,name2 varchar(10),primary key(id),unique key idx_name1(name1),key idx_name2(name2));
Query OK, 0 rows affected (0.02 sec)
TABLE: name test/t3, id 27, flags 1, columns 6, indexes 3, appr.rows 0
COLUMNS: id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name1: DATA_VARMYSQL DATA_NOT_NULL len 30; name2: DATA_VARMYSQL len 30; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
INDEX: name PRIMARY, id 32, fields 1/5, uniq 1, type 3
root page 3, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: id DB_TRX_ID DB_ROLL_PTR name1 name2
INDEX: name idx_name1, id 33, fields 1/2, uniq 1, type 2
root page 4, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: name1 id
INDEX: name idx_name2, id 34, fields 1/2, uniq 2, type 0
root page 5, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: name2 id
从日志看出，InnoDB还是选择的是主键作为聚集索引
主键索引
-
主键由表中一个或多个字段组成，它的值用于唯一地标识表中的某一条记录；
-
在表引用中，主键在一个表中引用来自于另一个表中的特定记录（外键foreign key应用）；
-
保证数据的完整性；
-
加快数据的操作速度；
-
主键值不能重复，也不能包含NULL;
-
主键选择建议
-
对业务透明，无意义，免受业务变化的影响；
-
很少修改和删除
-
最好是自增；
-
不要具有动态属性，例如随机值；
-
糟糕的主键选择：
-
UUID
-
char/varchar
辅助索引
-
非聚集索引，或者二级索引，俗称普通索引
-
当通过InnoDB辅助索引来查找数据的时候，辅助索引会通过页级的指针来找到主键索引的主键，然后通过该主键索引找到相应的行数据
-
索引定义时，不管有无显示包含主键，实际都会存储主键值；
-
在5.6.9后，优化器已能自动识别索引末尾的主键值（Index Extensions）,在这之前则需要显式加上主键列才可以被识别；
WHERE c1 = ? AND PK = ?
WHERE c1 = ? ORDER BY PK
唯一索引
-
不允许具有索引值相同的行，从而禁止重复的索引或键值
-
在唯一约束上，和主键一样（以MyISAM引擎为代表）
-
其他不同的方面：
-
唯一索引允许有空值（NULL）
-
一个表只能有一个主键，但可以有多个唯一索引
-
InnoDB表中主键必须是聚集索引，但聚集索引可能不是主键
-
唯一索引约束可临时禁用，但主键不能
联合索引
-
多列组成，所以也叫多列索引
-
适合WHERE条件中的多列组合
-
有时候，还可以用于避免回表（覆盖索引）
-
MySQL还不支持多列不同的排序规则（MySQL 8.0起支持）
-
联合索引建议
A.WHERE条件中，经常同时出现的列放在联合索引中
B.把选择性（过滤性/基数）大的列放在联合索引的最左边
-
如果第一列是范围查询，就无法用到后面的列了，可以利用ICP的特性
-
覆盖索引就是只要利用索引数就可以得到所有的数据了
示例
root@localhost [xucl]>show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` varchar(10) DEFAULT NULL,
`b` varchar(10) DEFAULT NULL,
`c` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost [xucl]>explain select * from test where a='a';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | idx_a_b_c | idx_a_b_c | 33 | const | 5 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
root@localhost [xucl]>explain select * from test where a='a' and b='b';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | idx_a_b_c | idx_a_b_c | 66 | const,const | 2 | 100.00 | Using index |