请教一个sql优化.

Question

一个文章表article,表类型InnoDB，26个字段，编码是utf8_general_ci。当前的数据行约12万左右，除自增ID外还建有两个索引，分别为：index_sort（字段类型varchar）/index_lock（类型tinyint）最近感觉查询速度明显变慢。SELECT id,title,pdate,pintro FROM article WHERE islock=1 AND author='1' ORDER BY id DESC LIMIT 0,12其中islock是索引字段，
这条sql在网页上竟然查到超时（30秒），放在mysql命令行里竟然也花了21.313smysql版本为5.1.30请高手指点一下，告知有哪些地方可以改善的，谢谢！

Answer 1

WHERE后面的字段加上索引你这条语句才能够用得上索引啊！

Answer 2

需要建立一个复合索引 (islock,author)
如果 (islock,author) 的重复项仍很多，则需要 (islock,author,id) 索引。

Answer 3

index_sort（字段类型varchar）/index_lock（类型tinyint） 
索引index_sort为全文索引吧?
数据量很大时,可能考虑使用分区http://hi.baidu.com/jeakccc/blog/item/03c8e9250435633ac995597c.html
另外:分区不支持全文索引

Answer 4

谢谢acmain_chm 老师，因为一直认为索引是越少越好,所以都没用上复合索引.
请问如用复合索引是不是每个where后有多个条件的都需加上复合索引？
那这样岂不是要建上十几个复合索引了？这样子对效率会不会有所影响呢？另我搜索到一篇文章说如字段内有包含null的话则索引会变无效率，这是真的吗？

Answer 5

估计“ islock=1 ”的重复记录比较多，所以，扫描的磁盘的IO也较多而造成的。
另外建议你贴出下面的结果好分析：show index from tb_name;explain SELECT id,title,pdate,pintro FROM article WHERE islock=1 AND author='1' ORDER BY id DESC LIMIT 0,12;另外，你执行下面语句，估计对你的速度也有提高：analyze table tb_name;
optimize table tb_name;

Answer 6

谢谢 vinsonshen.islock=1的重复记录是很多的，这字段默认就是1，为2的没几个，仅是表示文章是否锁定。
author='1' 的记录则很少，不知这样如何改善show index from tb_name;
的结果是：
article 0 PRIMARY 1 id A 110139 BTREE
article 1 index_sort 1 sortid A 3 YES BTREE
article 1 index_lock 1 islock A 4 YES BTREE
当前只建了两条单独的索引。
explain SELECT id,title... 的结果是：
1 SIMPLE article index index_lock PRIMARY 4 24 Using where
以上结果不知能否帮助到分析原因？我没什么用复合索引，是因为查询的条件太多了，几个页面共十几个查法，想建都不知怎么建。
例如：
WHERE id>80511 AND islock=1 AND author='1'
WHERE id>80511 AND find_in_set(27,sortid) AND islock=1
……
如每个多条件的查询都要建复合索引的话估计要建十几个…不知这想法是否正确？

Answer 7

比如这个查询，WHERE id>80511 AND islock=1 AND author='1'， 你就需要一个 (islock,author,id) 的索引。否则你的 islock 只有 3种，author只有4种。索引的意义就不大了。而 WHERE id>80511 AND find_in_set(27,sortid) AND islock=1 则只能利用索引 (islock,id)

Answer 8

article 1 index_sort 1 sortid A 3 YES BTREE 
article 1 index_lock 1 islock A 4 YES BTREE 
----------------------------------------------
这2列的值的唯一值记录太少了，单是从这2列用上索引来说，效果也没多大的作用
关键是“article 0 PRIMARY 1 id A 110139 BTREE ”这一项，所以，结合你的查询条件的，考虑尽量利用这一列作为复合索引（多列建立索引），要知道， 在mysql中，同一SQL语句中，一个表的每引用一次，只能用一个索引的，不能同时用多个索引的。

Answer 9

谢谢二位，找到原因了。估计是因为其中一个字段由原先的varchar(800)给我改成text(0)了，pintro这个字段是放文章提要的。因后台录入时老是超过所以才改成text。我试着取消这个字段后速度就明显提高。可是这个字段不能没有的，我尝试使用 CAST(pintro AS VARCHAR(2000)) AS pintro 也没效果，不知有没有别的方法？

Answer 10

请问，像下面这些条件我要建几个复合索引才合适呢？然后在sql语句及复合索引中字段的前后排序有没有关系？//查询
WHERE id>80547 AND islock=1 AND isbest='2' ORDER BY id DESCWHERE id>80547 AND find_in_set('27',sortid) AND islock=1 ORDER BY id DESC
WHERE id>80547 AND find_in_set('24',sortid) AND islock=1 AND topid='2' ORDER BY id DESC
WHERE id>80547 AND find_in_set('22',sortid) AND islock=1 AND attid='1' ORDER BY id DESCWHERE find_in_set('22',sortid) AND islock=1 AND attid='1'WHERE id>80547 AND islock=1 AND author='2' ORDER BY id DESC
WHERE islock=1 AND author='1' ORDER BY id DESC其中id为自增ID，id>80547这个条件是为了省略过去查更多的数据。