MySQL索引
1．如果索引了多列，遵守最左前缀法则。所谓最左前缀，指的是查询从索引的最左前列开始，并且不跳过索引中的列。
mysql> explain select uid, fuid, name from ptb where uid=1378109017;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 33 | |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select uid, fuid, name from ptb where uid=1378109018 and fuid=1378109017;
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 8 | const,const | 1 | |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------+
1 row in set (0.00 sec)
由于….没有使用索引. 使用索引失败，导致MySQL采用ALL访问策略，即全表查询.在开发中，应该尽量避免全表查询。
mysql> explain select uid, fuid, name from ptb where fuid=1378109017;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | ptb | ALL | NULL | NULL | NULL | NULL | 15373 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.02 sec)
mysql> explain select uid, fuid, name from ptb where fuid=1378109017 and uid=1378109018;
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 8 | const,const | 1 | |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------+
1 row in set (0.00 sec)
2．当MySQL估计检查的行数可能会“太多”，范围查找优化将不会被采用。
mysql> explain select uid, fuid, name from ptb where uid>1478109017;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | ptb | range | uid_fuid | uid_fuid | 4 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select uid, fuid, name from ptb where uid>1278109017;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | ptb | ALL | uid_fuid | NULL | NULL | NULL | 15373 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
第2条语句使用了全表查询，它与第1条语句唯一的区别在于需要检查的行数远远多于第1条语句。在应用中，可能不会碰到这么大的查询，但是应该避免这样的查询出现: select uid from users where registered < 1295001384
3．索引列不应该作为表达式的一部分，即不能在索引列上使用函数。
mysql> explain select uid, fuid, name from ptb where uid=1478109017;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 1 | |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
1 row in set (0.02 sec)
mysql> explain select uid, fuid, name from ptb where abs(uid)=1478109017;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | ptb | ALL | NULL | NULL | NULL | NULL | 15373 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.02 sec)
mysql> explain select uid, fuid, name from ptb where uid * 1 =1478109017;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | ptb | ALL | NULL | NULL | NULL | NULL | 15373 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
第2和3条语句都有使用表达式，索引派不上用场。
4．尽量借用覆盖索引，减少select * from …语句的用法.
mysql> explain select uid, fuid from ptb where uid=1378109017;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 33 | Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select uid, fuid, id from ptb where uid=1378109017;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 33 | Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select fuid,uid, id from ptb where uid=1378109017;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 33 | Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select uid, fuid, name from ptb where uid=1378109017;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 33 | |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
1 row in set (0.00 sec)
第1句Extra中使用了Using index表示使用了覆盖索引。第2句和第3句也使用了覆盖索引,虽然ID不在索引uid_fuid索引列中，但是InnoDB二次索引(second index)叶子页的值就是PK值，不同于MyISAM。Extra部分的Using index表示应用了索引，不要跟type中的index混淆。第4句没有使用覆盖索引，因为name不在索引中。
???????????????????????????????????????????????????
mysql> explain select fuid,uid, id from ptb where fuid=1378109017;
+----+-------------+-------+-------+---------------+----------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+--------------------------+
| 1 | SIMPLE | ptb | index | NULL | uid_fuid | 8 | NULL | 15373 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)
5．ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序
MySQL支持二种方式的排序，FileSort和Index，后者效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。ORDER BY满足以下情况，会使用Index方式排序：
a) ORDER BY 语句使用索引最左前列。如下：
mysql> explain select uid from ptb order by uid;
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
| 1 | SIMPLE | ptb | index | NULL | uid_fuid | 8 | NULL | 15373 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
1 row in set (0.00 sec)
b) 使用Where子句与Order BY子句条件列组合满足索引最左前列。如下：
mysql> explain select uid from ptb where uid=1378109017 order by fuid;
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 33 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
1 row in set (0.64 sec)
以下情况，会使用FileSort方式的查询：
a) 检查的行数过多，且没有使用覆盖索引。如下句虽然跟前一句(explain select uid from ptb order by uid)一样，order by使用了索引最左前列uid，但依然使用了filesort方式排序，因为name并不在索引中，所以没办法只扫描索引。
mysql> explain select uid, name from ptb order by uid;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| 1 | SIMPLE | ptb | ALL | NULL | NULL | NULL | NULL | 15373 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.00 sec)
b) 使用了不同的索引，MySQL每次只采用一个索引。如下句中，order by出现二个索引，分别是uid_fuid和聚集索引(pk)
mysql> explain select uid from ptb order by uid, id;
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | ptb | index | NULL | uid_fuid | 8 | NULL | 15373 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)
c) 对索引列同时使用了ASC和DESC，通过where语句将order by中索引列转为常量，则除外。如下第1句和第2句在order by子句中，都出现了ASC和DESC排序。但是第1句却使用了filesort方式排序，是因为第2句where uid取出排序需要的数据，MySQL将其转为常量，它的ref列为const。
mysql> explain select uid from ptb order by uid ASC, fuid DESC;
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | ptb | index | NULL | uid_fuid | 8 | NULL | 15373 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)
mysql> explain select uid, name from ptb where uid=1478109017 order by uid ASC, fuid DESC;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 1 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
d) where语句与order by语句，使用了不同的索引。
mysql> explain select uid, fuid from ptb where uid=1278 order by name;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
1 row in set (0.01 sec)
mysql> explain select uid,name from ptb where uid=1278 order by id;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | ptb | index | uid_fuid | PRIMARY | 4 | NULL | 15373 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> explain select uid, name from ptb where uid=1278 order by id; ?????
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | ptb | index | uid_fuid | PRIMARY | 4 | NULL | 15373 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> explain select uid from ptb where uid=1278 order by id; ?????
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 1 | Using where; Using index; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------+
1 row in set (0.00 sec)
mysql> explain select id,uid, name from ptb where uid=1278 order by id; ???????
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | ptb | index | uid_fuid | PRIMARY | 4 | NULL | 15373 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> explain select id,uid from ptb where uid=1278 order by id; ????
+----+-------------+-------+------+---------------+----------+---------+-------+------+------------------------------ ---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 1 | Using where; Using index; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------+
1 row in set (0.00 sec)
mysql> explain select id,name from ptb where uid=1278 order by id; ???????
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | ptb | index | uid_fuid | PRIMARY | 4 | NULL | 15373 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
1 row in set (0.00 sec)
e) where语句或者ORDER BY语句中索引列使用了表达式，包括函数表达式。
mysql> explain select uid, name from ptb where uid*1=1478109017 order by uid;
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | ptb | ALL | NULL | NULL | NULL | NULL | 15373 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
1 row in set (0.02 sec)
mysql> explain select uid, name from ptb where uid=1378 order by abs(uid);
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)
f) where 语句与ORDER BY语句组合满足最左前缀，但where语句中使用了条件查询。虽然where与order by构成了索引最左有缀的条件，但是where子句中使用的是条件查询。如下：
mysql> explain select uid, name from ptb where uid>1478109017 order by fuid;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
| 1 | SIMPLE | ptb | range | uid_fuid | uid_fuid | 4 | NULL | 1 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
g) order by子句中加入了非索引列，且非索引列不在where子句中。
mysql> explain select uid, name from ptb where uid>1478109017 order by name;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
| 1 | SIMPLE | ptb | range | uid_fuid | uid_fuid | 4 | NULL | 1 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
h) order by或者它与where组合没有满足索引最左前列。如下where与order by组合，不满足索引最左前列. （uid, name)跳过了fuid
mysql> explain select uid, name from ptb where uid=1478109017 order by name;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)
mysql> explain select uid from ptb where fuid=1478109017 order by uid, name;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | ptb | ref | uid_fuid | uid_fuid | 4 | const | 1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)
mysql> explain select uid from ptb order by uid, name;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| 1 | SIMPLE | ptb | ALL | NULL | NULL | NULL | NULL | 15373 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.00 sec)
i) 当使用left join，使用右边的表字段排序。如下尽管user.uid是pk，依然会使用filesort排序。
create table puse(
uid int unsigned not null,
puid int unsigned not null default 0,
access tinyint unsigned not null default 0,
primary key(uid)
)
insert into puse(uid) values('1378109017'),('1378108952'),('1378108951');
mysql> explain select p.uid, p.name from ptb as p left join puse as u on p.uid=u.uid where p.uid=1378109017 order by u.uid;
+----+-------------+-------+-------+---------------+----------+---------+-------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+-------+------+---------------------------------+
| 1 | SIMPLE | p | ref | uid_fuid | uid_fuid | 4 | const | 33 | Using temporary; Using filesort |
| 1 | SIMPLE | u | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+-------+------+---------------------------------+
2 rows in set (0.00 sec)
6．慎用left join语句，避免创建临时表
使用left join语句的时候，避免出现创建临时表。尽量不要用left join，采用分而治之。非要使用时，要询问自己是不是真要必须使用。
mysql> explain select p.uid, p.name from ptb as p left join puse as u on p.uid=u.uid where p.uid=1378109017 order by p.uid, p.fuid,u.access;
+----+-------------+-------+-------+---------------+----------+---------+-------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+-------+------+---------------------------------+
| 1 | SIMPLE | p | ref | uid_fuid | uid_fuid | 4 | const | 33 | Using temporary; Using filesort |
| 1 | SIMPLE | u | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+---------------+----------+---------+-------+------+---------------------------------+
2 rows in set (0.00 sec)
7．高选择性索引列
尽量使用高选择性的索引来过滤数据。高选择性指Cardinality/#T 越接近1，选择性越高，其中Cardinality指表中索引列不重复值(行)的总数。PK和唯一索引，具有最高的选择性，即1。推荐可选性达到20%以上。
mysql> select id from ptb where uid = 1378109017;
+------+
| id |
+------+
| 2926 |
..
| 3215 |
| 3225 |
..
| 3268 |
+------+
33 rows in set (0.00 sec)
mysql> explain select id, uid, fuid from ptb where uid=1378109017 and id = 3225;
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | ptb | const | PRIMARY,uid_fuid | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
这里有二个索引可供使用,而MySQL选择PRIMARY，是因为它具有更高的选择性。
8．谨防where子句中的OR。where语句使用or，且没有使用覆盖索引，会进行全表扫描。应该尽量避免这样OR语句。尽量使用UNION代替OR
mysql> explain select uid, fuid from ptb where uid = 1578109017 or fuid=1478109017;
+----+-------------+-------+-------+---------------+----------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+--------------------------+
| 1 | SIMPLE | ptb | index | uid_fuid | uid_fuid | 8 | NULL | 15373 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select uid, name from ptb where uid = 1578109017 or fuid=1478109017;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | ptb | ALL | uid_fuid | NULL | NULL | NULL | 15373 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
第1句虽然使用了索引,但是查行时间依然不可以恭维，mysql要检查的行很多,但是返回的行却很少.Extra中的using where表示需要通过where子句扔弃不需要的数据行。
9．LIMIT与覆盖索引 limit子句，使用覆盖索引时比没有使用覆盖索引会快很多
create table ptb(
id int unsigned not null auto_increment,
uid int unsigned not null default 0,
fuid int unsigned not null default 0,
name varchar(50) not null default '',
age tinyint not null default 0,
fsex tinyint not null default 0,
primary key(id)
) engine=InnoDB AUTO_INCREMENT=1;
alter table ptb add index uid_fuid(uid, fuid);
delimiter //
drop procedure if exists proc_ptb //
create procedure proc_ptb()
begin
declare counter int default 0;
declare age int default 20;
declare tfuid int default 0;
while(counter < 5000) do
if(age > 30) then
select unix_timestamp() into tfuid;
set age=20;
end if;
set age=age+1;
set tfuid=tfuid+1;
set counter=counter+1;
insert into ptb(uid, fuid, age) values( tfuid, tfuid, age);
end while;
end
//
delimiter ;
