1.索引使用
![](https://i-blog.csdnimg.cn/blog_migrate/39226f8beb5078101b23640bdcb76dfd.png)
select * from tb_sku where id = 1\G;
看里面的字段情况,执行如下SQL
:
SELECT * FROM tb_sku WHERE sn = '100000003145001'; 1
我们可以看到根据sn字段进行查询,查询返回了一条数据,结果耗时 20.78sec,就是因为sn没有索 引,而造成查询效率很低。
create index idx_sku_sn on tb_sku(sn) ;
然后再次执行相同的SQL语句,再次查看SQL的耗时。
SELECT * FROM tb_sku WHERE sn = '100000003145001'\G;
我们明显会看到,sn字段建立了索引之后,查询性能大大提升。建立索引前后,查询耗时都不是一个数 量级的。
2 最左前缀法则
create table tb_user(
id int primary key auto_increment comment '主键',
name varchar(50) not null comment '用户名',
phone varchar(11) not null comment '手机号',
email varchar(100) comment '邮箱',
profession varchar(11) comment '专业',
age tinyint unsigned comment '年龄',
gender char(1) comment '性别 , 1: 男, 2: 女',
status char(1) comment '状态',
createtime datetime comment '创建时间'
) comment '系统用户表';
CREATE INDEX idx_user_name ON tb_user(name);
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
#下面的索引profession字段的长度是47,age的长度是2,status的长度是5
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);
CREATE INDEX idx_email ON tb_user(email);
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';
explain select * from tb_user where profession = '软件工程' and age = 31;
explain select * from tb_user where profession = '软件工程';
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';
explain select * from tb_user where profession = '软件工程' and status = '0';
思考题:
3 范围查询
explain select * from tb_user where profession = '软件工程' and age > 30 and status
= '0';
备注: idx_user_pro_age_sta 索引profession字段的长度是47,age的长度是2,status的长度是5
4 索引失效情况
4.1 索引列运算
![](https://i-blog.csdnimg.cn/blog_migrate/822fd758a164676eea25bd0dcacea965.png)
A. 当根据phone字段进行等值匹配查询时, 索引生效。
explain select * from tb_user where phone = '17799990015';
B. 当根据phone字段进行函数运算操作之后,索引失效。
explain select * from tb_user where substring(phone,10,2) = '15';
4.2 字符串
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= 0;
explain select * from tb_user where phone = '17799990015';
explain select * from tb_user where phone = 17799990015;
4.3 模糊查询
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';
经过上述的测试,我们发现,在like模糊查询中,在关键字后面加%,索引可以生效。而如果在关键字 前面加了%,索引将会失效。
4.4 or连接条件
explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;
由于age没有索引,所以即使id、phone有索引,索引也会失效。所以需要针对于age也要建立索引。 然后,我们可以对age字段建立索引。
create index idx_user_age on tb_user(age);
最终,我们发现,当or连接的条件,左右两侧字段都有索引时,索引才会生效。
4.5 数据分布影响
select * from tb_user where phone >= '17799990005';
select * from tb_user where phone >= '17799990015';
explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;
接下来,我们做一个操作将profession字段值全部更新为null。
然后,再次执行上述的两条SQL,查看SQL语句的执行计划。
5 SQL提示
把上述的 idx_user_age, idx_email 这两个之前测试使用过的索引直接删除。
drop index idx_user_age on tb_user;
drop index idx_email on tb_user;
A. 执行SQL : explain select * from tb_user where profession = '软件工程';
查询走了联合索引。
B. 执行SQL,创建profession的单列索引:create index idx_user_pro on
tb_user(profession);
C. 创建单列索引后,再次执行A中的SQL语句,查看执行计划,看看到底走哪个索引。
explain select * from tb_user use index(idx_user_pro) where profession = '软件工
程';
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工
程';
explain select * from tb_user force index(idx_user_pro) where profession = '软件工
程';
![](https://i-blog.csdnimg.cn/blog_migrate/6057776799fc14293f26f50d8c5d6b4f.png)
6 覆盖索引
explain select id, profession from tb_user where profession = '软件工程' and age =
31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '软件工程'
and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '软
件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';
![](https://i-blog.csdnimg.cn/blog_migrate/d14e4b06d69ed192e48480dbc567b8e6.png)
Extra
|
含义
|
Using where; Using Index
|
查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据
|
Using index condition
|
查找使用了索引,但是需要回表查询数据
|
![](https://i-blog.csdnimg.cn/blog_migrate/76840d8232ce4de4af8c9d8b643b3dee.png)
7 前缀索引
create index idx_xxxx on table_name(column(n)) ;
create index idx_email_5 on tb_user(email(5));
select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;
![](https://i-blog.csdnimg.cn/blog_migrate/1de59cb1142eb740b6cdd71da6429d60.png)
8 单列索引与联合索引
![](https://i-blog.csdnimg.cn/blog_migrate/1d93a676ae18cbbebe32e09d912f563c.png)
create unique index idx_user_phone_name on tb_user(phone,name);