User 表：
1：查询的数量是大表的大部分，30％以上，索引失效
2：like 以 % 开头，索引失效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| #
mysql> CREATE INDEX name ON users(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#
mysql> explain select * from users where name like "%四";
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#
mysql> explain select * from users where name like "四%";
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | users | NULL | range | name | name | 1022 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
|
3：or 只要其中一个条件没有索引，索引失效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| #
mysql> explain select * from users where email = "1234@tt.com" or name = "赵四";
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | name | NULL | NULL | NULL | 5 | 36.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#
mysql> create index email on users(email);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#
mysql> mysql> explain select * from users where email = "1234@tt.com" or name = "赵四";
+----+-------------+-------+------------+-------------+---------------+------------+-----------+------+------+----------+--------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+------------+-----------+------+------+----------+--------------------------------------+
| 1 | SIMPLE | users | NULL | index_merge | name,email | email,name | 1023,1023 | NULL | 2 | 100.00 | Using union(email,name); Using where |
+----+-------------+-------+------------+-------------+---------------+------------+-----------+------+------+----------+--------------------------------------+
1 row in set, 1 warning (0.00 sec)
|
4：对于多列索引，不是使用的第一部分(第一个)，索引失效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| mysql> drop index name on users;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index email on users;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#
mysql> create index name_email_age on users(name, email,age);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#
mysql> explain select * from users where name = '赵四'and age = 51;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | users | NULL | ref | name_email_age | name_email_age | 1023 | const | 1 | 10.00 | Using index condition |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#
mysql> explain select * from users where email = '1232@tt.com' and age = 51;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 7.14 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
|
5：数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，索引失效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| mysql> explain select * from users where name = 111;
+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | name_email_age | NULL | NULL | NULL | 14 | 10.00 | Using where |
+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
mysql> explain select * from users where name = "111";
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | name_email_age | name_email_age | 1023 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
|
6：当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效
7：索引列进行运算.需要建立函数索引
