数据库索引失效如何避免
十大原则：
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（全表扫描）了。
