下面说的都是在where或者where...order by的基础上。
如:
我在一张表建立了联合索引.
ALTER TABLE `A` ADD INDEX `abc` (`a`,`b`,`c`);
由于某种需要,还需要对某个字段建立普通索引
ALTER TABLE `A` ADD INDEX `a` (`a`);
ALTER TABLE `A` ADD INDEX `b` (`b`);用explain 观察
possible_keys 可能会有a,b,abc
key 可能是a、b或abc
Extra 是Using where; Using filesort
虽然type是ref级别。
但是从Extra中的Using filesort看,语句还是多做了一次排序,比较影响性能。象这样的情况,怎么处理?删除某个索引?在实际环境上,如:explain SELECT btitle.id,btitle.hit,btitle.bbstop,btitle.sign,btitle.huati,btitle.P_display,btitle.bbs_img,btitle.title,btitle.redate,btitle.rep,btitle.picture,btitle.name FROM bbstitle btitle WHERE btitle.BoardID_1='A_6' AND btitle.BoardID_2='A_6_4' AND btitle.deleted=0 ORDER BY bbstop desc,SIGN DESC,huati desc,redate desc LIMIT 0,30+----+-------------+--------+------+-----------------------------+-----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-----------------------------+-----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | btitle | ref | deleted,BoardID_2,BoardID_1,bbd | BoardID_2 | 1002 | const | 1001 | Using where; Using filesort |
+----+-------------+--------+------+-----------------------------+-----------+---------+-------+------+----------
如:
我在一张表建立了联合索引.
ALTER TABLE `A` ADD INDEX `abc` (`a`,`b`,`c`);
由于某种需要,还需要对某个字段建立普通索引
ALTER TABLE `A` ADD INDEX `a` (`a`);
ALTER TABLE `A` ADD INDEX `b` (`b`);用explain 观察
possible_keys 可能会有a,b,abc
key 可能是a、b或abc
Extra 是Using where; Using filesort
虽然type是ref级别。
但是从Extra中的Using filesort看,语句还是多做了一次排序,比较影响性能。象这样的情况,怎么处理?删除某个索引?在实际环境上,如:explain SELECT btitle.id,btitle.hit,btitle.bbstop,btitle.sign,btitle.huati,btitle.P_display,btitle.bbs_img,btitle.title,btitle.redate,btitle.rep,btitle.picture,btitle.name FROM bbstitle btitle WHERE btitle.BoardID_1='A_6' AND btitle.BoardID_2='A_6_4' AND btitle.deleted=0 ORDER BY bbstop desc,SIGN DESC,huati desc,redate desc LIMIT 0,30+----+-------------+--------+------+-----------------------------+-----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-----------------------------+-----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | btitle | ref | deleted,BoardID_2,BoardID_1,bbd | BoardID_2 | 1002 | const | 1001 | Using where; Using filesort |
+----+-------------+--------+------+-----------------------------+-----------+---------+-------+------+----------
第一个(a)
第二个(a,b)
第三个(a,b,c)
索引建立了(a,b,c)后又建立(a)索引显然重复了
在组合索引中,应该考虑了order by的字段。
如:
... where a=? order by b desc,c desc limit 0,10
但是加了普通索引之后,在key,变成了b或c,而不是abc
LZ需要权衡利弊,如果对排序使用了索引,就可能要舍弃WHERE子句可用的索引~
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-----------------------------+-----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | btitle | ref | deleted,BoardID_2,BoardID_1,bbd | BoardID_2 | 1002 | const | 1001 | Using where; Using filesort |
+----+-------------+--------+------+-----------------------------+-----------+---------+-------+------+------show index from bbstitle
索引有
1、deleted
2、BoardID_1
3、BoardID_2
4、bbd