mysql 简单的索引优化问题

zhengdl126

只是单表查询，表pro_ru索引如下：

sn_class 是品名的分类id,关联class表中的ID
sn_no 是商品名称
ctime 是发布日期

由于搜索到用到这三个条件，所以做成如下索引【不知道是否合理】：





在php代码中尽量考虑索引的最左前缀，所以PHP查询条件代码如下实现：


                if (!empty($g_cgival['sn_class']))
{
$pro_ru->where.=" WHERE sn_class =".$g_cgival['sn_class']." ";

}else
{
$pro_ru->where = " WHERE 1 = 1 ";
}
if (!empty($g_cgival['keywords']))
{
$pro_ru->where.=" AND sn_no like '%".$g_cgival['keywords']."%' ";
}
if (!empty($g_cgival['ftime']))
{
$pro_ru->where.=" AND ctime > '".$g_cgival['ftime']."' ";
}
if (!empty($g_cgival['ttime']))
{
$pro_ru->where.=" AND ctime < '".$g_cgival['ttime']."' ";
}


SQL语句分析如下：


mysql> EXPLAIN select * from gll_pro_ru WHERE sn_class =7  limit 0,20;
共359029条记录
+----+-------------+------------+------+----------------+----------------+---------+-------+--------+-------+
| id | select_type | table      | type | possible_keys  | key            | key_len | ref   | rows   | Extra |
+----+-------------+------------+------+----------------+----------------+---------+-------+--------+-------+
|  1 | SIMPLE      | gll_pro_ru | ref  | class_ctime_no | class_ctime_no | 4       | const | 357645 |       |
+----+-------------+------------+------+----------------+----------------+---------+-------+--------+-------+
1 row in set (0.00 sec)


mysql> desc select * from gll_pro_ru WHERE sn_class =7 ORDER BY id DESC limit 0,20;
共359029条记录
+----+-------------+------------+-------+----------------+----------------+---------+------+--------+-----------------------------+
| id | select_type | table      | type  | possible_keys  | key            | key_len | ref  | rows   | Extra                       |
+----+-------------+------------+-------+----------------+----------------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | gll_pro_ru | range | class_ctime_no | class_ctime_no | 4       | NULL | 357645 | Using where; Using filesort |
+----+-------------+------------+-------+----------------+----------------+---------+------+--------+-----------------------------+
1 row in set (0.16 sec)





mysql> EXPLAIN select * from gll_pro_ru WHERE sn_class =7 AND sn_no='A2202-1' limit 0,20;
共359019条记录
+----+-------------+------------+------+----------------+----------------+---------+-------------+--------+-------------+
| id | select_type | table      | type | possible_keys  | key            | key_len | ref         | rows   | Extra       |
+----+-------------+------------+------+----------------+----------------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | gll_pro_ru | ref  | class_ctime_no | class_ctime_no | 771     | const,const | 357639 | Using where |
+----+-------------+------------+------+----------------+----------------+---------+-------------+--------+-------------+
1 row in set (0.03 sec)



mysql> desc select * from gll_pro_ru WHERE sn_class =7 AND sn_no='A2202-1' limit 0,20;
共359019条记录
+----+-------------+------------+------+----------------+----------------+---------+-------------+--------+-------------+
| id | select_type | table      | type | possible_keys  | key            | key_len | ref         | rows   | Extra       |
+----+-------------+------------+------+----------------+----------------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | gll_pro_ru | ref  | class_ctime_no | class_ctime_no | 771     | const,const | 357639 | Using where |
+----+-------------+------------+------+----------------+----------------+---------+-------------+--------+-------------+
1 row in set (0.00 sec)


索引基本被用到，但是页面执行总是很慢【页面执行时间: 28418.6 毫秒 ，页面执行时间: 40841.9 毫秒 】，为什么rows 的值那么大，是不是还需要怎样优化，还是我的PHP代码有问题？小弟对MYSQL优化不是很了解，望高手支招。


附另一个问题：没有使用limit的sql为什么无法实用索引？？

mysql> EXPLAIN select * from gll_pro_ru where sn_class =7 ORDER BY id DESC ;    +----+-------------+------------+------+----------------+------+---------+------+--------+-----------------------------+
| id | select_type | table      | type | possible_keys  | key   | key_len | ref  | rows   | Extra                       |
+----+-------------+------------+------+----------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | gll_pro_ru | ALL  | class_ctime_no | NULL | NULL     | NULL | 269667 | Using where; Using filesort |
+----+-------------+------------+------+----------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)


mysql> EXPLAIN select * from gll_pro_ru where sn_class =7 ORDER BY id DESC limit 0,10000;
+----+-------------+------------+-------+----------------+----------------+---------+------+--------+-----------------------------+
| id | select_type | table      | type  | possible_keys  | key             | key_len | ref  | rows   | Extra                       |
+----+-------------+------------+-------+----------------+----------------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | gll_pro_ru | range | class_ctime_no | class_ctime_no | 4        | NULL | 357645 | Using where; Using filesort |
+----+-------------+------------+-------+----------------+----------------+---------+------+--------+-----------------------------+
1 row in set (0.02 sec)

Coolriver

第一个问题,结果集太大.
sn_class  这个例不适合做索引.
或是你需要加时间列进行过虑.
show index from gll_pro_ru ;  可以去查看索引列的分布情况.

$pro_ru->where.=" AND sn_no like '%".$g_cgival['keywords']."%' ";
有在量搜索的情况该语句足以把MySQL搞的很慢.

最后那个LIMIT的问题,如果利用Order by没加LIMIT的情况,逻辑上走全表排序,再过滤.ID应该是主建吧.感觉执行计划不对.
analyze table gll_pro_ru;
然后再试一下.

zhengdl126

谢谢版主回复，晚上回去测试效果。:wink:

zhengdl126

mysql> show index from gll_pro_ru ;
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| gll_pro_ru |          0 | PRIMARY        |            1 | id          | A         |      359555 |     NULL | NULL   |      | BTREE      |         |
| gll_pro_ru |          1 | class_ctime_no |            1 | sn_class    | A         |        6658 |     NULL | NULL   |      | BTREE      |         |
| gll_pro_ru |          1 | class_ctime_no |            2 | sn_no       | A         |      179777 |     NULL | NULL   |      | BTREE      |         |
| gll_pro_ru |          1 | class_ctime_no |            3 | ctime       | A         |      359555 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)


mysql> analyze table gll_pro_ru;
+-----------------------+---------+----------+----------+
| Table                 | Op      | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| sq_gllutf8.gll_pro_ru | analyze | status   | OK       |
+-----------------------+---------+----------+----------+
1 row in set (2.03 sec)


-----------------------------修复以后的结果：





mysql> show index from gll_pro_ru ;
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| gll_pro_ru |          0 | PRIMARY        |            1 | id          | A         |      359555 |     NULL | NULL   |      | BTREE      |         |
| gll_pro_ru |          1 | class_ctime_no |            1 | sn_class    | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| gll_pro_ru |          1 | class_ctime_no |            2 | sn_no       | A         |         251 |     NULL | NULL   |      | BTREE      |         |
| gll_pro_ru |          1 | class_ctime_no |            3 | ctime       | A         |         539 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)


· Cardinality表示：
　　索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，
该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。

优化以后页面的执行时间指需要以前的1/2,可不可以具体说说Cardinality的变化对索引的影响

如何还能进一步优化页面执行的速率，Repair table gll_pro_ru;，Check table gll_pro_ru;，Optimize table gll_pro_ru;，Checksum table gll_pro_ru; 这些优化语句我都已经优化了。

聪明笨小孩

了解 一下