MySQL 索引设计原则、索引失效场景汇总 - 《MySQL》

索引的设计原则
索引失效情况

索引的设计原则

1．选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2．为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

6．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

索引失效情况

查询条件包含or，可能导致索引失效

create table t_goods (
-- 主键索引
id varchar(32) primary key,
name varchar(100),
user_id varchar(32),
type_id varchar(32),
merchant_id varchar(32)
);
-- 创建普通索引
create index inx_goods_name on t_goods(name);
create index inx_goods_user_id on t_goods(user_id);
create index inx_goods_merchant_id on t_goods(merchant_id);
-- 显示索引
show index from t_goods;
-- 插入数据
insert into t_goods(id, name, user_id, type_id, merchant_id) values ('111', '蓝光电视', '333', '222', '250');
-- 使用索引
explain select * from t_goods where id = '111' or name = '蓝光电视';
-- 索引失效
explain select * from t_goods where id = '111' or type_id = '222';

查询条件为字符串类型（数字字符串），未使用’’可能导致索引失效 ```sql insert into t_goods(id, name, user_id, type_id, merchant_id) values (‘123’, ‘西瓜’, ‘333’, ‘456’, ‘250’); — 索引失效（字符串与数字比较，类型不匹配，数据库引擎默认隐式转换为浮点数再比较） explain select from t_goods where id = 123; — 使用索引 explain select from t_goods where id = ‘123’;


3. 通配符‘like’、‘_’可能导致索引失效
```sql
-- 索引失效
explain select * from t_goods where name like '%电视';
explain select * from t_goods where name like '%光电%';
explain select * from t_goods where name like '__电视';
explain select * from t_goods where name like '_光电_';
-- 使用索引
explain select * from t_goods where name like '蓝光%';
explain select * from t_goods where name like '蓝光__';
-- 使用索引（覆盖索引，只查询索引字段）
explain select name from t_goods where name like '%电视';
-- 使用索引（主键索引）
explain select id, name from t_goods where name like '%电视';
-- 索引失效
explain select id, name, user_id from t_goods where name like '%电视';

联合索引，不符合最左匹配原则，索引可能失效 ```sql create table t_merchant ( — 主键索引 id varchar(32) primary key, name varchar(100), phone varchar(20), address varchar(200) ); insert into t_merchant(id, name, phone, address) values (‘250’, ‘胡歌’, ‘17635124678’, ‘湖南省长沙市’); — 创建联合索引 alter table t_merchant add index inx_merchant_name_phone_address(name, phone, address); — （非联合索引）create index inx_merchant_name_phone on t_merchant(name, phone, address); — 使用索引 explain select from t_merchant where name = ‘胡歌’; explain select from t_merchant where name = ‘胡歌’ and phone = ‘17635124678’; explain select from t_merchant where name = ‘胡歌’ and phone = ‘17635124678’ and address = ‘湖南省长沙市’; — 索引不失效（优化器优化？） explain select from t_merchant where phone = ‘17635124678’; explain select * from t_merchant where phone = ‘17635124678’ and address = ‘湖南省长沙市’;


5. 在索引列上使用MySQL内置函数，索引可能失效
```sql
-- 索引失效
explain select * from t_goods where concat(name, '1') = '蓝光电视1';

在索引列上使用+、-、、/运算，索引可能失效 ```sql — 索引失效 explain select from t_goods where type_id + ‘1’ = ‘2221’;


7. 在索引列上使用!=、<>、not in等，索引可能失效
```sql
-- 索引不失效（优化器优化？）
explain select * from t_goods where name != '蓝光电视1';

索引列上将is null、is not null与or一起使用，索引可能失效 ```sql — 索引失效 explain select * from t_goods where name is null or user_id is null;


9. 左连接或右连接字段，编码不一样可能导致索引失效
```sql
-- 查询表的字符编码
show create table t_merchant;
-- 修改表的字符编码
alter table t_merchant character set utf8mb4, collate utf8mb4_0900_ai_ci;
-- 修改表列的字符编码
alter table t_merchant change id id varchar(32) character set utf8mb4, collate utf8mb4_0900_ai_ci;

数据库优化器觉得使用全表扫描快，可能不使用索引