由浅入深探究 MySQL索引结构原理、性能分析与优化(二)

(1.2)不能正确的利用索引

l Where子句表达式顺序是(password, last_login)

mysql> explain select* fromone wherepassword='123456'andlast_login='1338251170';
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table| type | possible_keys | key| key_len | ref | rows| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one | ALL| NULL| NULL| NULL| NULL| 20146 | Using where|
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row inset(0.00 sec)


l Where 子句表达式顺序是(last_login)

1

2

3

4

5

6

7

mysql> explain select* fromone wherelast_login='1338252525';

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| id | select_type | table| type | possible_keys | key| key_len | ref | rows| Extra |

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| 1 | SIMPLE | one | ALL| NULL| NULL| NULL| NULL| 20146 | Using where|

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row inset(0.00 sec)

以上的两条语句都不是以username开始,这样是用不了索引,通过type=all(全表扫描),key_len=null,rows都很大20146

Ps:one表里只有20003条数据,为什么出现20146,这是优化器对表的一个估算值,不精确的。

l Where 子句表达式虽然顺序是(username,password, last_login)或(username,password)但第一个是有范围’<’、’>’,’<=’,’>=’等出现

1

2

3

4

5

6

7

mysql> explain select* fromone whereusername>'abgvwfnt'andpassword='123456'andlast_login='1338251170';

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| id | select_type | table| type | possible_keys | key| key_len | ref | rows| Extra |

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| 1 | SIMPLE | one | ALL| username | NULL| NULL| NULL| 20146 | Using where|

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row inset(0.00 sec)

这个查询很明显是遍历所有表,一个索引都没用到,非第一列出现范围(password列或last_login列),则能利用索引到首先出现范围的一列,也就是“where username=’abgvwfnt’ and password >’123456′and last_login=’1338251170′;”或则“where username=’abgvwfnt’ and password >’123456′and last_login<’1338251170′;”索引长度ref_len=43,索引检索到password列,所以考虑多列索引的时候把那些查询语句用的比较的列放在最后(或非第一位)。

l 断层,即是where顺序(username, last_login)

1

2

3

4

5

6

7

mysql> explain select* fromone whereusername='abgvwfnt'andlast_login='1338252525';

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

| id | select_type | table| type | possible_keys | key| key_len | ref | rows| Extra |

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where|

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

1 row inset(0.00 sec)

注意这里的key_len=24=8*3(8是username的长度,3是utf8编码),rows=5,和下面一条sql语句搜索出来一样

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

mysql> select* fromone whereusername='abgvwfnt';

+-------+----------+----------+-------+------------+

| id | username | password| level| last_login |

+-------+----------+----------+-------+------------+

| 3597 | abgvwfnt | 234567 | 0 | 1338251420 |

| 7693 | abgvwfnt | 456789 | 0 | 1338251717 |

| 11789 | abgvwfnt | 456789 | 0 | 1338251992 |

| 15885 | abgvwfnt | 456789 | 0 | 1338252258 |

| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |

+-------+----------+----------+-------+------------+

5 rowsinset(0.00 sec)

 

mysql> select* fromone whereusername='abgvwfnt'andlast_login='1338252525';

+-------+----------+----------+-------+------------+

| id | username | password| level| last_login |

+-------+----------+----------+-------+------------+

| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |

+-------+----------+----------+-------+------------+

1 row inset(0.00 sec)

这个就是要的返回结果,所以可以知道断层(username,last_login),这样只用到username索引,把用到索引的数据再重新检查last_login条件,这个相对全表查询来说还是有性能上优化,这也是很多sql优化文章中提到的where 范围查询要放在最后(这不绝对,但可以利用一部分索引)

(1.3)如果一个查询where子句中确实不需要password列,那就用“补洞”。

1

2

3

4

5

6

7

8

9

10

mysql> selectdistinct(password) fromone;

+----------+

| password|

+----------+

| 234567 |

| 345678 |

| 456789 |

| 123456 |

+----------+

4 rowsinset(0.08 sec)

可以看出password列中只有这几个值,当然在现实中不可能密码有这么多一样的,再说数据也可能不断更新,这里只是举例说明补洞的方法

1

2

3

4

5

6

7

8

mysql> explain select* fromone whereusername='abgvwfnt'andpasswordin('123456','234567'<code style="font-family: Consolas, 'Bitstream Vera Sa

推荐博文