(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
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'
|
暂无评论内容