Mysql索引问题

发表于 分类于 Waline：
MySQL8.x索引失效的场景验证。面试八股

最近面试，碰到几家都有问数据库索引失效的场景，发现的面试官说的都有点不一样，网上查到的结果也有差异，应该跟数据库版本有关系吧。在这里用MySQL8.x做个测试并记录一下。

首先 插入10w条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--MySQL插入10w数据
DROP PROCEDURE if exists test_insert ;
DELIMITER ;;

CREATE PROCEDURE test_insert ()
BEGIN

DECLARE i INT DEFAULT 1;

-- 插入条数
WHILE i<100000
DO
-- 插入语句
INSERT INTO `pay_order`( `order_no`, `user_id`, `product_id`, `product_name`, `amount`, `count`, `currency`, `channel`, `status`, `channel_status`, `order_type`, `source`, `client_ip`, `buy_id`, `out_trade_no`, `created_time`, `updated_time`, `pay_time`, `extra`, `goods_order_no`) VALUES ( CONCAT('36845975578043',i), CONCAT(1000,i), i, '商品', 0.01, 1, 'GBEANS', 'aliPay', 2, NULL, 1, 3, '124.193.79.2', NULL, '2020071022001476181414677434', '2020-07-10 11:25:56', '2020-07-10 11:26:26', '2020-07-10 11:26:26', '{\"pay_url\":\"https://qr.alipay.com/bax04964pzhmhf4mz8dr2000\"}', '368459754211766272');

SET i=i+1;
END WHILE ;
commit;
END;;
CALL test_insert();

MySQL版本8.0.17，索引信息如下：

  • INDEX idx_user_id_product_id_out_trade_no(user_id, product_id, out_trade_no`)

    随便选三个字段建立复合

  • INDEX idx_goods_order_no(goods_order_no)

    建一个普通索引

1.最佳左前缀

1
2
3
4
5
6
7
8
9
10
--索引生效
explain select * from pay_order where 	user_id ='100030017' and  product_id='1' and   out_trade_no='20200710220014761811' 
--索引生效
explain select * from pay_order where   product_id='1' and  user_id ='100030017' and out_trade_no='20200710220014761811'
----索引生效
explain select * from pay_order where   out_trade_no='20200710220014761811' and product_id='1' and  user_id ='100030017' 
----索引无效
explain select * from pay_order where   product_id='9'  and out_trade_no='2'
--索引生效
explain select * from pay_order where   user_id='9'

只要查询条件包含复合索引最左侧的列即可，与顺序无关，数据库执行引擎会帮我们做优化。

2.范围查询

1
2
3
4
5
6
7
--索引无效
explain select * from pay_order where 	user_id <'100030017' and  product_id='1' and   out_trade_no='20200710220014761811' 
--Using index condition
explain select * from pay_order where 	user_id ='100030017' and  product_id>'1' and   out_trade_no='20200710220014761811' 
--Using index condition
explain select * from pay_order where 	user_id ='100030017' and  product_id='1' and   out_trade_no<'20200710220014761811'

如果对复合索引最左侧的列进行范围查询，那么索引就会失效

3 Like模糊查询

1
2
3
4
5
6
7
8
9
10
11
12
--不走索引
explain select * from pay_order where 	user_id like '300%'
--不走索引
explain select * from pay_order where 	user_id like '%300'
--不走索引
explain select * from pay_order where 	user_id like '%300%'
--Using index condition
explain select * from pay_order where 	goods_order_no like '111%'
--不走索引
explain select * from pay_order where 	goods_order_no like '%111'
--不不走索引
explain select * from pay_order where 	goods_order_no like '%111'

只有普通索引，在%号在后边才会生效。复合索引like查询无效。

4 is not null、 is null

1
2
3
4
--索引无效
explain select * from pay_order where   goods_order_no is not null
--Using index condition
explain select * from pay_order where   goods_order_no is  null

is not null 索引失效

5 in和not in

1
2
3
4
--Using index condition
explain select * from pay_order where   goods_order_no in ('1000','1000000')
--索引无效
explain select * from pay_order where   goods_order_no  not in ('1000','1000000')

in 查询执行计划Extra Using index condition，而not in 索引失效

6 != 、<、>、between and

1
2
3
4
5
6
7
8
9
10
11
12
--Using index condition
explain select * from pay_order where   goods_order_no <'368459754211766272'
--索引无效
explain select * from pay_order where   goods_order_no <='368459754211766272'
--Using index condition
explain select * from pay_order where   goods_order_no >'368459754211766272'
--索引无效
explain select * from pay_order where   goods_order_no >='368459754211766272'
--Using index condition
explain select * from pay_order where   goods_order_no !='368459754211766272'
--索引无效
explain select * from pay_order where   goods_order_no !='11'

尽量不要使用这些条件。有些情况下会导致索引无效（可能跟匹配行有关系）

7 对索引加函数或者运算

1
2
---索引不生效
explain select * from pay_order where   left(goods_order_no,2) = '1000'

索引列如果做任何函数运算，索引就会失效

8 索引列类型不匹配

1
2
--索引不生效
explain select * from pay_order where   goods_order_no = 1000

查询条件值类型要和数据库匹配，否则索引失效

9 or

1
2
3
4
5
6
---索引无效
explain select * from pay_order where 	user_id ='10002' and  product_id='2' or   out_trade_no='20200710220014761812' 
--索引无效
explain select * from pay_order where   goods_order_no = '1000' or product_id ='10002'
--两个条件都有索引时索引生效
explain select * from pay_order where   goods_order_no = '1000' or user_id ='10002'

只有查询条件字段都存在索引时，索引才可能会生效。否则只要出现or，索引就会失效