My Little World

索引单表/多表优化

发表于

单表优化

避免全表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 需求一：查询所有名字中包含李的用户姓名和手机号,并根据user_id字段排序。
EXPLAIN SELECT
    NAME,
    mobile
FROM user_contacts WHERE NAME LIKE '%李%' ORDER BY user_id;

--  添加联合索引，包含要查询的字段，实现索引覆盖，一并解决 like %在左边的问题。
ALTER TABLE user_contacts ADD INDEX idx_nm(NAME,mobile,user_id);

-- 删除索引
DROP INDEX idx_nm ON user_contacts;

-- 优化后，extra字段中包含filesort文件排序，还需要继续优化
-- 这里用user_id 字段排序，所以将user_id 放在联合索引的最前面
ALTER TABLE user_contacts ADD INDEX idx_nm(user_id,NAME,mobile);

联合索引不生效

1
2
3
4
5
6
7
8
9
-- 需求二：统计手机号是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%';

-- extra = using where + using index,表示查询的列被索引覆盖了，但是无法通过索引直接获取数据。
-- 联合索引无法生效，根据mobile单独建立一个索引
ALTER TABLE user_contacts ADD INDEX idx_m(mobile);

查询大范围数据如何减少耗时

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 需求三：获取用户通讯录表第10万条数据开始后的100条数据。
EXPLAIN SELECT * FROM user_contacts LIMIT 4000000,10000;

-- 1. 通过索引进行分页，比如id字段如果是自增的话，就可以根据查询的记录数算出id的范围
EXPLAIN SELECT * FROM user_contacts WHERE id >= 100001 LIMIT 100;

-- 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;

多表优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 用户认证表：mob_auth,有11万数据，保存的是通过手机认证后的用户数据。
-- 与其他表关联的字段 user_id
SELECT COUNT(*) FROM mob_auth;

-- 紧急联系人表：ugncy_cntct_psn ,大概有22万条数据，用户注册成功后，保存填写的紧急联系人信息
-- 关联字段：user_id
SELECT COUNT(*) FROM ugncy_cntct_psn;

-- 借款申请表：loan_apply, 接近11万条数据，保存的是每次用户申请借款时填写的信息
-- 关联字段：user_id
SELECT COUNT(*) FROM loan_apply;

-- 需求1：查询所有认证用户的手机号以及认证用户的紧急联系人的姓名与手机号信息。
EXPLAIN SELECT
    ma.`mobile` '认证用户的手机号',
    ucp.`cntct_psn_name` '紧急联系人的姓名',
    ucp.`cntct_psn_mob` '紧急联系人的手机号'
FROM mob_auth ma
LEFT JOIN ugncy_cntct_psn ucp ON ma.`user_id` = ucp.`user_id`;

-- 优化：为 mob_auth的user_id字段添加索引
ALTER TABLE mob_auth ADD INDEX idx_uid(user_id);
-- 上面的索引没有生效，因为mob表是驱动表，驱动表建立索引也不会生效。
-- 一般情况下：左连接，左表是驱动表，右表被驱动表，右连接反之，我们应该在被驱动表连接字段上建立索引
ALTER TABLE ugncy_cntct_psn ADD INDEX idx_uid(user_id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 需求二：获取紧急联系人数量>8的，用户手机号信息。
-- 1.获取紧急联系人 > 8的用户的id
SELECT user_id,COUNT(user_id) c FROM ugncy_cntct_psn GROUP BY user_id
HAVING c > 8;

-- 2.获取认证用户的id和手机号
SELECT user_id,mobile FROM mob_auth;

-- 3.将上面两条SQL进行连接
EXPLAIN SELECT
    ucp.user_id,
    COUNT(ucp.user_id) c,
    m.user_id,
    m.mobile
FROM ugncy_cntct_psn ucp INNER JOIN (SELECT user_id,mobile FROM mob_auth) m
ON ucp.`user_id` = m.user_id
GROUP BY ucp.user_id HAVING c > 8 ORDER BY NULL;

– 需求3：获取所有智能审核的用户手机号和申请额度、申请时间、审核额度。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 需求3：获取所有智能审核的用户手机号和申请额度、申请时间、审核额度。

EXPLAIN SELECT
    ma.`mobile` '用户认证手机号',
    la.`apply_limit` '申请额度',
    la.`apply_time` '申请时间',
    la.`audit_limit` '审核额度'
FROM mob_auth ma INNER JOIN loan_apply la ON ma.id = la.`mob_auth_id`
WHERE la.audit_mod_cde = 2;

-- 选择性低（重复率高）的字段不适合创建索引
ALTER TABLE loan_apply ADD INDEX idx_amc(audit_mod_cde);

SELECT COUNT(*) FROM loan_apply la WHERE la.audit_mod_cde = 2;
-- 如果必须要通过选择性低的状态字段查询的话，可以根据业务需求，添加一个日期条件
EXPLAIN SELECT
    ma.`mobile` '用户认证手机号',
    la.`apply_limit` '申请额度',
    la.`apply_time` '申请时间',
    la.`audit_limit` '审核额度'
FROM mob_auth ma INNER JOIN loan_apply la ON ma.id = la.`mob_auth_id`
WHERE la.`apply_time` BETWEEN '2017-01-01 00:00:00' AND '2017-01-01 23:59:59'
AND la.audit_mod_cde = 2;