【MySQL 数据宝典】【索引原理】- 007 索引优化示例

最新推荐文章于 2025-12-26 14:31:53 发布

原创

最新推荐文章于 2025-12-26 14:31:53 发布 · 820 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #android #数据库

一、单表优化

1.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 '姓名',
  version 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)
);

DELIMITER //

CREATE PROCEDURE insert_random_user_contacts()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 100000 DO
        INSERT INTO user_contacts (user_id, mobile, name, version, create_by, create_date, update_by, update_date, remarks, del_flag) 
        VALUES (FLOOR(RAND() * 100000), 
                CONCAT('138', FLOOR(RAND() * 1000000000)), 
                CONCAT('姓名', i), 
                1, 
                'admin', 
                NOW(), 
                'admin', 
                NOW(), 
                CONCAT('备注', i), 
                '0');
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

call insert_random_user_contacts();

1.2 需求分析

1.2.1 查询所有名字中包含李的用户姓名和手机号,并根据user_id字段排序

SELECT NAME, mobile FROM  user_contacts WHERE NAME LIKE '李%' ORDER BY user_id;

EXPLAIN SELECT NAME, mobile FROM  user_contacts WHERE NAME LIKE '%李%' ORDER BY user_id;

分析的结果显示: type=ALL 是最坏的情况,并且Extra中还出现了 Using FIlesort(文件排序未使用到索引),所以必须优化

添加联合索引

ALTER TABLE user_contacts ADD INDEX idx_nmu(NAME,mobile,user_id);

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;

1.2.2 统计手机号是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: 为获取所需数据而进行扫描的行数,比之前减少非常多
using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

1.2.3 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(1)	count(1) 只需检查行是否存在，不涉及具体列的读取，比 count(*) 更快。
表只有一个字段	count(*)	count(*) 返回表中所有行的数量，不受特定列是否为主键的影响，同时避免了检查特定列是否为 NULL 的开销。

1.2.4 查询2017-2-16日,新增的用户联系人信息. 查询字段: name , mobile

EXPLAIN SELECT NAME,mobile FROM user_contacts  WHERE DATE_FORMAT(create_date,'%Y-%m-%d')='2017-02-16';

创建索引

分析 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';

1.2.5 获取用户通讯录表第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 语句返回的记录数。需注意以下几点：
limit a,b :