架构师系列-MYSQL调优（七）- 索引单表优化案例

索引单表优化案例

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;