数据库索引失效如何避免

十大原则：

1.全值匹配我最爱
2.最佳左前缀法则
3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列
5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)， 减少select *
6. mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
7.is null,is not null也无法使用索引
8. like以通配符开头(%abc... )mysq|索引失效会变成全表扫描的操作er问题:解决like "%6字符串%时索引不被使用的方法?
9.字符串不加单引号索引失效
10.少用or,用它来连接时会索引失效

 

当使用like进行模糊查询的时候，'%'写在首尾两端好，还是左边好，还是右边好

复制代码
mysql> explain select * from user where name like '%aa%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from user where name like '%aa';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from user where name like 'aa%';
+----+-------------+-------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys    | key              | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+------------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | range | idx_user_nameAge | idx_user_nameAge | 767     | NULL |    1 | Using where |
+----+-------------+-------+-------+------------------+------------------+---------+------+------+-------------+
1 row in set (0.00 sec)
复制代码

通过以上测试，结果表明：当我们在实际开发中如果要用到like实现模糊查询，前提是这三者查询出来的结果都相同时，选'%'放在右边的比较好，这里一定要记住有前提。

 

如何解决模糊查询like后面的条件字符串首尾都用到'%'而导致的索引失效问题？——解决方案：使用索引覆盖

示例：

1、先创建一张user表

1 CREATE TABLE `user` (
2   `id` int(11) NOT NULL AUTO_INCREMENT,
3   `name` varchar(255) NOT NULL,
4   `age` int(11) NOT NULL,
5   `sex` char(1) DEFAULT 'F',
6   PRIMARY KEY (`id`),
7   KEY `idx_user_nameAge` (`name`,`age`)
8 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

2、然后向表中添加记录

insert into user (name, age) values ('0aa0', 20);
insert into user (name, age) values ('1aa1', 21);
insert into user (name, age) values ('2aa2', 22);
insert into user (name, age) values ('3aa3', 19);
insert into user (name, age) values ('4aa4', 23);

 3、没有索引覆盖

添加索引之前

复制代码
mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> explain  select * from user where name like '%aa%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
复制代码

添加组合索引(idx_user_nameAge)之后

复制代码
mysql> create index idx_user_nameAge on user(name, age);
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from user;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY          |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | idx_user_nameAge |            1 | name        | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | idx_user_nameAge |            2 | age         | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain  select * from user where name like '%aa%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
复制代码

通过以上代码，可以观察出，加了索引之后，如果查询的是 * (即所有字段)时，type类型的值仍然是ALL，显然性能没有得到提升，因为此时select后面所要查询的字段没有完全被索引字段所覆盖（专业名词简称索引覆盖），所以对于like的模糊查询后面条件字符串的首位都加'%'的情况，要想提高查询效率，将type类型的值从ALL提高到index，必须要利用索引覆盖

4、索引覆盖

复制代码
--------------------------------------------------------------------------------------------------------------------------
select 后面查询的字段都被索引覆盖

mysql> explain select id from user where name like '%aa%';
+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key              | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | NULL          | idx_user_nameAge | 771     | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select name, age from user where name like '%aa%';
+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key              | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | NULL          | idx_user_nameAge | 771     | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select id, name, age from user where name like '%aa%';
+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key              | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | NULL          | idx_user_nameAge | 771     | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

--------------------------------------------------------------------------------------------------------------------------
select 后面查询的字段没有全被索引覆盖

mysql> explain select sex from user where name like '%aa%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from user where name like '%aa%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
复制代码

此时，从后面两句SQL的执行情况来看，就不难发现：索引覆盖对于like模糊查询条件字符串首尾都加'%'时查询效率有了大大的提升，不再是之前的ALL（全表扫描）了。

posted @   没有你哪有我  阅读(232)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· DeepSeek在M芯片Mac上本地化部署
· 葡萄城 AI 搜索升级：DeepSeek 加持，客户体验更智能
点击右上角即可分享
微信分享提示