除了上面的几个明显的问题外,还有索引的选择问题。MySQL 在执行一段 sql 的时候,会先决定使用哪一个索引,如果 选了一个性能比较差的索引,即使走了索引,也会带来性能问题。
对上面的第 4 条做一个例子说明:
create database ITTest;
use ITTest;
CREATE TABLE tbl_user (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
userName varchar(100) not null comment '姓名',
userAge int default 0 comment '年龄',
userStatus int default 0 comment '用户状态 0 有效,1 无效',
userSex int default 0 comment '性别 0 男 1 女',
birthDate DATE COMMENT '生日',
createTime DATETIME DEFAULT NOW() COMMENT '创建时间',
dataChangeLastTime DATETIME DEFAULT NOW() ON UPDATE NOW() COMMENT '最后更新时间'
);
alter table tbl_user add index idx_userName(userName);
alter table tbl_user add index idx_userSex(userSex);
alter table tbl_user add index idx_userStatus(userStatus);
alter table tbl_user add INDEX idx_union_userStatus_userSex_birthDate(userStatus,userSex,birthDate );
insert into tbl_user(userName,userAge,userStatus,userSex,birthDate)
values
('user001',rand()*100,rand(),rand(),curdate()),
('user002',rand()*100,rand(),rand(),curdate()),
('user003',rand()*100,rand(),rand(),curdate()),
('user004',rand()*100,rand(),rand(),curdate()),
('user005',rand()*100,rand(),rand(),curdate()),
('user006',rand()*100,rand(),rand(),curdate()),
('user007',rand()*100,rand(),rand(),curdate()),
('user008',rand()*100,rand(),rand(),curdate()),
('user009',rand()*100,rand(),rand(),curdate())
各个字段的含义
mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2";
-- 结果:
id: 1
select_type: SIMPLE -- 查询类型(简单查询、联合查询、子查询)
table: user -- 显示这一行的数据是关于哪张表的 。
type: range -- 区间索引(在小于1990/2/2区间的数据),这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表扫描了全表才确定结果。一般来说,得保证查询至少达到range级别,最好能达到ref。
possible_keys: birthday -- 指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。
key: birthday -- 实际使用到的索引。如果为NULL,则没有使用索引。如果为primary的话,表示使用了主键。
key_len: 5 -- 最长的索引宽度。如果键是NULL,长度就是NULL。在不损失精确性的情况下,长度越短越好。
ref: const -- 显示哪个字段或常数与key一起被使用。
rows: 20 -- 这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。
Extra: Using where; Using index -- 执行状态说明,这里可以看到的坏的例子是Using temporary和Using
MySQL 索引的选取是基于成本计算的,影响查询成本的因素有 扫描行数、是否需要临时表以及是否需要排序**等。
成本的决定因素很多,就那扫描行数来说,影响扫描行数最大的因素是数据的离散度
,但是数据又是动态变化的,所以在使用的离散度比较低的索引的时候需要注意后续的索引变化。例如:
select * from user where userSex=0 and userStatus=0 and birthDate>'1897-01-01'
select * from user where userSex in(0,1) and userStatus=0 and birthDate>'1897-01-01'
分析下上面的两个sql 的执行速度那个快:
如果这个时候,MySQL 发现 userSex 走索引的成本小于全表扫描,则可能会走索引下推