where条件没有先后次序的说法
mysql> select * from mio_aaa where nums='321' AND id='222';
+-----+------+-------------+--------+
| id | nums | vars | static |
+-----+------+-------------+--------+
| 222 | 321 | aaaaaaaa222 | 1 |
+-----+------+-------------+--------+
1 row in set (0.00 sec)
mysql> select * from mio_aaa where id='222' AND nums='321';
+-----+------+-------------+--------+
| id | nums | vars | static |
+-----+------+-------------+--------+
| 222 | 321 | aaaaaaaa222 | 1 |
+-----+------+-------------+--------+
1 row in set (0.00 sec)
就算建立了索引 但范围太大时 同样不会使用索引
mysql> explain select * from mio_aaa where id>'5000000';
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | mio_aaa | ALL | PRIMARY | NULL | NULL | NULL | 6327761 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from mio_aaa where id>'6000000';
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | mio_aaa | range | PRIMARY | PRIMARY | 4 | NULL | 259257 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)
在索引上使用函数会导致索引失效
mysql> explain select * from mio_aaa where id='6000000';
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | mio_aaa | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from mio_aaa where abs(id)='6000000';
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | mio_aaa | ALL | NULL | NULL | NULL | NULL | 6327761 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
借用索引覆盖可以提高性能
mysql> explain select id from mio_aaa where id='6000000';
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | mio_aaa | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from mio_aaa where id='6000000';
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | mio_aaa | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
避免filesort排序
范围过大导致使用filesort排序
mysql> explain select * from mio_aaa WHERE id>'2000000' order by id;
+----+-------------+---------+------+---------------+------+---------+------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+---------+-----------------------------+
| 1 | SIMPLE | mio_aaa | ALL | PRIMARY | NULL | NULL | NULL | 6327761 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+---------+-----------------------------+
1 row in set (0.00 sec)
mysql> explain select * from mio_aaa WHERE id>'6000000' order by id;
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | mio_aaa | range | PRIMARY | PRIMARY | 4 | NULL | 259257 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)
没有借用索引导致filesort排序
mysql> explain select nums from mio_aaa order by id;
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
| 1 | SIMPLE | mio_aaa | ALL | NULL | NULL | NULL | NULL | 6327761 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.00 sec)
mysql> explain select id from mio_aaa order by id;
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | mio_aaa | index | NULL | PRIMARY | 4 | NULL | 6327761 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)
对order使用不不同索引
mysql> explain select id from mio_aaa WHERE id>'6500000' order by id ASC,nums DESC;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-----------------------------+
| 1 | SIMPLE | mio_aaa | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where; Using filesort |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
mysql> explain select id from mio_aaa WHERE id>'6500000' order by id ASC;
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | mio_aaa | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
上一篇:15款优秀的办公软件 下一篇:快递查询API