3-索引失效

mysql Comments

一、引入

​ 索引可以在一些查询情况下加速查询速度，但某一些情况下索引也会失效，我们需要知道这些特殊情况，以更好的优化 sql 语句。

  1. 模糊匹配以 ==%开头==

    1
    select * from account where ename like "%T";

  2. 使用 or ，如果 or 的两边存在有一个字段 ==没有索引==，那它会导致 另一个 ==有索引==的字段==索引失效==。
    解决办法：

    1. 不使用 or

    2. 给 or 两边的字段==都 建立索引==

      1
      select * from emp where ename ='king 'or job= 'manager';

  3. 使用复合索引时，没有使用 ==左侧==的列进行查找，索引失效。
    什么是复合索引？

    两个字段，或者更多的字段联合起来，添加一个索引，叫做复合索引。———->多个字段，一个索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#建立复合索引
create index emp_job_sal_index on emp(job,sal);
#查询左侧
> 以下的 type 
  ref 表示索引
  all 表示遍历
mysql> explain select * from emp where job ='manager';
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref(ref代表索引)  | emp_job_sal_index | emp_job_sal_index | 39      | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

#查询右侧
mysql> explain select * from emp where sal =800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL(表示遍历)  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  1. 在 where 中，索引列参加了运算，索引失效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create index emp_sal_index on emp(sal);
#
#普通
mysql> explain select * from emp where sal=800;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

#加了运算
mysql> explain select * from emp where sal+1=800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  1. 在 where 当中索引使用了 ==函数==。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    create index emp_ename_index on emp(ename);

    # 转小写
    mysql> explain select * from emp where lower(ename)='smith';
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)

jhfutureWeb Developer & Designer

一个记录技术分享的网站。