一、explain包含字段详解
二、主键索引、联合索引(Innodb)
InnoDB聚集索引的叶子节点存储行数据记录(上左图),因此, InnoDB必须要有,且只有一个聚集索引:
(1)如果表定义了PK,则PK就是聚集索引;
(2)如果表没有定义PK,则第一个not NULL unique列是聚集索引;
(3)否则,InnoDB会创建一个隐藏的row-id作为聚集索引;
普通索引(上右图):
只存储主键值、和索引值
根据普通索引查询过程:先从普通索引树检索查看普通索引树叶子节点是否有select全部包含的数据,如果没有需要根据主键值回表操作(根据主键去主键索引检索找到所需数据)
三、索引查询注意事项
表employees、employees_copy结构完全相同(employees表数据量10万条、employees_copy表只有几条)
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表'
1、联合索引第一个字段是范围查询一般不会索引(只是大多数)
当表数据较大时不会走索引
EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
同样的语句查询employees_copy(表中数据只有几条)反而走索引
**大概原因:mysql内部会有个cost成本计算、当表中数据较多回表次数较多(io次数变多)不如全表扫描**
2、mysql在使用不等于(!=或者<>),not in ,not exists 的时候无法使用索引会导致全表扫描< 小于、 > 大于、 <=、>= 这些,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
3、is null,is not null 一般情况下也无法使用索引
4、like以通配符开头支持在最左前缀原则’aaa%’,like一般都会走索引、无论表大表小
like其实就是范围查找(只不过内部用到了索引下推、有时**>=**也会用到索引下推)
索引下推:可以在索引遍历过程中,对索引中包含的所有字段先做判断,过滤掉不符合条件的记录之后再回表,可以有效的减少回表次数
5、in和or在表数据量比较大的情况会走索引,在表记录不多的情况下会选择全表扫描
6、一般查询尽量走索引覆盖(就是查询字段在普通索引树中叶子节点存在)