索引单表优化案例
1. 建表
创建表 插入数据
下面是一张
用户通讯表的表结构信息,这张表来源于真实企业的实际项目中,有接近500万条数据.
CREATE TABLE user_contacts (
id INT(11) NOT NULL AUTO_INCREMENT,
user_id INT(11) DEFAULT NULL COMMENT '用户标识',
mobile VARCHAR(50) DEFAULT NULL COMMENT '手机号',
NAME VARCHAR(20) DEFAULT NULL COMMENT '姓名',
verson INT(11) NOT NULL DEFAULT '0' COMMENT '版本',
create_by VARCHAR(64) DEFAULT NULL COMMENT '创建者',
create_date DATETIME NOT NULL COMMENT '创建时间',
update_by VARCHAR(64) DEFAULT NULL COMMENT '更新者',
update_date DATETIME NOT NULL COMMENT '更新时间',
remarks VARCHAR(255) DEFAULT NULL COMMENT '备注信息',
del_flag CHAR(1) NOT NULL DEFAULT '0' COMMENT '删除标识',
PRIMARY KEY (id)
);
2. 单表索引分析
需求一
- 查询所有名字中包含李的用户姓名和手机号,并根据user_id字段排序
SELECT NAME, mobile FROM user_contacts WHERE NAME LIKE '李%' ORDER BY user_id;
- 通过explain命令 查看SQL查询优化信息
- 结论：很显然type是ALL，即最坏情况。Extra里还出现Using filesort（文件内排序,未使用到索引），也是最坏情况，所以优化是必须的。
优化
1、首先添加联合索引, 该联合索引包含所有要查询的字段,使其成为覆盖索引,一并解决like模糊查询时索引失效问题 。
-- 添加联合索引
ALTER TABLE user_contacts ADD INDEX idx_nmu(NAME,mobile,user_id);2、进行分析
EXPLAIN SELECT NAME, mobile FROM user_contacts WHERE NAME LIKE '%李%' ORDER BY user_id;
分析结果显示: type连接类型提升到了index级别,通过索引就获取到了全部数据,但是Extra字段中还是存在 Using filesort.
继续优化: 根根据最佳左前缀法则,之后最左侧列是有序的, 在创建联合索引时,正确的顺序应该是: user_id,NAME,mobile
-- 删除索引
DROP INDEX idx_nmu ON user_contacts
-- 添加重新排序后的索引
ALTER TABLE user_contacts ADD INDEX idx_unm(user_id,NAME,mobile);执行查询,发现type=index , Using filesort没有了.
EXPLAIN SELECT NAME, mobile FROM user_contacts WHERE NAME LIKE '%李%' ORDER BY user_id;
需求二
统计手机号是135、136、186、187开头的用户数量.
EXPLAIN SELECT COUNT(*) FROM user_contacts WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';
type=index : 用到了索引,但是进行了索引全表扫描
key=idx_unm: 使用到了联合索引,但是效果并不是很好
Extra=Using where; Using index: 查询的列被索引覆盖了,但是无法通过该索引直接获取数据.
综合上面的执行计划给出的信息,需要进行优化.
优化
经过上面的分析,发现联合索引没有发挥作用,所以尝试对 mobile字段单独建立索引
ALTER TABLE user_contacts ADD INDEX idx_m(mobile);
再次执行,得到下面的分析结果
EXPLAIN SELECT COUNT(*) FROM user_contacts WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';
type=range: 使用了索引进行范围查询,常见于使用>，>=，<，<=，BETWEEN，IN() 或者 like 等运算符的查询中。
key=idx_m: mysql选择了我们为mobile字段创建的索引,进行数据检索
rows=1575026: 为获取所需数据而进行扫描的行数,比之前减少了近三分之一。
count(*) 和 count(1)和count(列名)区别
进行统计操作时,count中的统计条件可以三种选择:
EXPLAIN SELECT COUNT(*) FROM user_contacts
WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';
EXPLAIN SELECT COUNT(id) FROM user_contacts
WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';
EXPLAIN SELECT COUNT(1) FROM user_contacts
WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';
执行效果:
count(*) 包括了所有的列,在统计时 不会忽略列值为null的数据.
count(1) 用1表示代码行,在统计时,不会忽略列值为null的数据.
count(列名)在统计时,会忽略列值为空的数据,就是说某个字段的值为null时不统计.
执行效率:
列名为主键, count(列名)会比count(1)快
列名为不是主键, count(1)会比count(列名)快
如果表没有主键,count(1)会比count(*)快
如果表只有一个字段,则count(*) 最优.需求三
查询2017-2-16日,新增的用户联系人信息. 查询字段: name , mobile
EXPLAIN SELECT NAME,mobile FROM user_contacts WHERE DATE_FORMAT(create_date,'%Y-%m-%d')='2017-02-16';
explain分析的结果显示 type=ALL : 进行了全表扫描,需要进行优化,为create_date字段添加索引.
ALTER TABLE user_contacts ADD INDEX idx_cd(create_date);
EXPLAIN SELECT NAME,mobile FROM user_contacts WHERE DATE_FORMAT(create_date,'%Y-%m-%d')='2017-02-16';
添加索引后,发现并没有使用到索引
key=null分析原因: create_date字段是datetime类型 ,转换为日期再匹配,需要查询出所有行进行过滤, 所以导致索引失效.
优化:
改为使用 between ... and ... ,使索引生效
EXPLAIN SELECT NAME,mobile FROM user_contacts WHERE create_date BETWEEN '2017-02-16 00:00:00' AND '2017-02-16 23:59:59';
type=range : 使用了索引进行范围查询
Extra=Using index condition; Using MRR :Using index condition 表示使用了部分索引, MRR表示InnoDB存储引擎 通过把「随机磁盘读」，转化为「顺序磁盘读」,从而提高了索引查询的性能.
需求四
获取用户通讯录表第10万条数据开始后的100条数据.
EXPLAIN SELECT * FROM user_contacts uc LIMIT 100000,100;
-- 查询记录量越来越大，所花费的时间也会越来越多
EXPLAIN SELECT * FROM user_contacts uc LIMIT 1000000,1000;
EXPLAIN SELECT * FROM user_contacts uc LIMIT 2000000,10000;
EXPLAIN SELECT * FROM user_contacts uc LIMIT 3000000,100000;
LIMIT 子句可以被用于指定 SELECT 语句返回的记录数。需注意以下几点：
第一个参数指定第一个返回记录行的偏移量，注意从0开始()
第二个参数指定返回记录行的最大数目
如果只给定一个参数：它表示返回最大的记录行数目
初始记录行的偏移量是 0(而不是 1)
优化1: 通过索引进行分页
直接进行limit操作 会产生全表扫描,速度很慢. Limit限制的是从结果集的M位置处取出N条输出,其余抛弃.
假设ID是连续递增的,我们根据查询的页数和查询的记录数可以算出查询的id的范围，然后配合 limit使用
EXPLAIN SELECT * FROM user_contacts WHERE id >= 100001 LIMIT 100;
- type类型提升到了 range级别
优化2: 使用子查询优化
-- 首先定位偏移位置的id
SELECT id FROM user_contacts LIMIT 100000,1;
-- 根据获取到的id值向后查询.
EXPLAIN SELECT * FROM user_contacts WHERE id >=
(SELECT id FROM user_contacts LIMIT 100000,1) LIMIT 100;
