举例说明：上面给出一个多列索引(username,password,last_login)，当三列在where中出现的顺序如(username,password,last_login)、 (username,password)、(username)才能用到索引，如下面几个顺序(password,last_login)、(passwrod)、(last_login)---这三者不 从username开始，(username,last_login)---断层，少了password，都无法利用到索引。因为B+tree多列索引保存的顺序是按照索引创 建的顺序，检索索引时按照此顺序检索
测试：以下测试不精确，这里只是说明如何才能正确按照最左前缀原则使用索引。还有的是以下的测试用的时间0.00sec看不出什么时间区 别，因为数据量只有20003条，加上没有在实体机上运行，很多未可预知的影响因素都没考虑进去。当在大数据量，高并发的时候，最左前 缀原则对与提高性能方面是不可否认的。
Ps：最左前缀原则中where字句有or出现还是会遍历全表
能正确的利用索引
Where子句表达式 顺序是(username)
mysql> explain select * from one where username='abgvwfnt';
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 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 in set (0.00 sec)
Where子句表达式 顺序是(username,password)
mysql> explain select * from one where username='abgvwfnt' and password='123456';
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
| 1 | SIMPLE | one | ref | username | username | 43 | const,const | 1 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
Where子句表达式 顺序是(username,password, last_login)
mysql> explain select * from one where username='abgvwfnt' and password='123456'and last_login='1338251170';
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
| 1 | SIMPLE | one | ref | username | username | 83 | const,const,const | 1 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)
上面可以看出type=ref 是多列索引，key_len分别是24、43、83，这说明用到的索引分别是(username), (username,password), (username,password, last_login );row分别是5、1、1检索的数据行都很少，因为这三个查询都按照索引前缀原则，可以利用到 索引。
不能正确的利用索引
Where子句表达式 顺序是(password, last_login)
mysql> explain select * from one where password='123456'and last_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 in set (0.00 sec)
Where 子