组合索引
提到组合索引,大家都知道“最左前缀”原则。例如,创建索引 idx_name_age (name,age) ,通常情况下,where age=50 或者 where age>50 之类的,是不会使用到idx_a_b的。那有没有特殊情况呢?
假设表是:
CREATE TABLE users
(id
int(10) unsigned NOT NULL AUTO_INCREMENT,name
varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,email
varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,password
varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,remember_token
varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,created_at
timestamp NULL DEFAULT NULL,updated_at
timestamp NULL DEFAULT NULL,age
int(11) DEFAULT NULL,
PRIMARY KEY (id
),
UNIQUE KEY users_email_unique
(email
),
KEY idx_name_age
(name
,age
)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
覆盖索引
innodb存储引擎支持覆盖索引(covering index),或称索引覆盖(index coverage),即从辅助索引中就能查到的记录,而不需要查询聚集索引中的记录。
使用覆盖索引的好处是辅助索引(个人的理解是辅助索引包含了其他记录,但是不是整行的记录,但是怎么建覆盖索引,我还明白怎么建覆盖索引)不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减少大量的IO操作。
几个sql语句的explain
- explain SELECT name FROM test.users where age>50
possible_keys是null,表示确实没啥索引可用。
key却是idx_name_age,表示优化器出动了,它选择了idx_name_age这个二级索引。
注意select的字段是name,在idx_name_age这个二级索引中就能完成where的查找以及拿到select 的字段 name
- explain SELECT name,age FROM test.users where age>50
同上
- explain SELECT name,age,id FROM test.users where age>50
同上(注意二级索引都包含了主键[ 样例表主键字段是id]以便通过主键去聚簇索引查找其他字段。但是显然上述SQL语句并不需要,因为要select的字段在idx_name_age里都有了。)
- explain SELECT name,age,id,email FROM test.users where age>50
这次没有使用到idx_name_age,因为要select的字段包含了email,在idx_name_age里面是没有的
- explain SELECT count(1) FROM test.users where age>50
对于(a,b)这样的联合索引,对于b列的查询条件进行统计,如果是覆盖索引的,优化器也会选择该联合索引。
注意:在Explain的时候,输出的信息如果有“using index”,就表明了该查询使用了覆盖索引
转载于:https://www.cnblogs.com/xiaoallen/p/8391082.html