康盛ucenter 的uchome表 索引问题

spark_zhang

用的discuz 7.0 和ucenter 1  开始uchome_pic 好像没有索引，后来加了几个 但效果不是很理想

表有800w 记录  执行 sql 效率很差，请高手看下，加大索引是否合理 感谢

表结构

mysql> desc uchome_pic;
+----------+-----------------------+------+-----+---------+----------------+
| Field    | Type                  | Null | Key | Default | Extra          |
+----------+-----------------------+------+-----+---------+----------------+
| picid    | mediumint(          | NO   | PRI | NULL    | auto_increment |
| albumid  | mediumint( unsigned | NO   | MUL | 0       |                |
| uid      | mediumint( unsigned | NO   | MUL | 0       |                |
| dateline | int(10) unsigned      | NO   | MUL | 0       |                |
| postip   | char(20)              | NO   |     |         |                |
| filename | char(100)             | NO   |     |         |                |
| title    | char(150)             | NO   |     |         |                |
| type     | char(20)              | NO   |     |         |                |
| size     | int(10) unsigned      | NO   |     | 0       |                |
| filepath | char(60)              | NO   |     |         |                |
| thumb    | tinyint(1)            | NO   |     | 0       |                |
| remote   | tinyint(1)            | NO   |     | 0       |                |
+----------+-----------------------+------+-----+---------+----------------+


现在uchome_pic 的索引信息
mysql> show index from uchome_pic;
+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| uchome_pic |          0 | PRIMARY         |            1 | picid       | A         |     8064619 |     NULL | NULL   |      | BTREE      |         |
| uchome_pic |          1 | albumid         |            1 | albumid     | A         |      161292 |     NULL | NULL   |      | BTREE      |         |
| uchome_pic |          1 | albumid         |            2 | dateline    | A         |     4032309 |     NULL | NULL   |      | BTREE      |         |
| uchome_pic |          1 | dateline        |            1 | dateline    | A         |     4032309 |     NULL | NULL   |      | BTREE      |         |
| uchome_pic |          1 | ind_albumid_uid |            1 | albumid     | A         |      161292 |     NULL | NULL   |      | BTREE      |         |
| uchome_pic |          1 | ind_albumid_uid |            2 | uid         | A         |      168012 |     NULL | NULL   |      | BTREE      |         |
| uchome_pic |          1 | ind_uid         |            1 | uid         | A         |       26528 |     NULL | NULL   |      | BTREE      |         |
| uchome_pic |          1 | ind_uid_picid   |            1 | uid         | A         |       26528 |     NULL | NULL   |      | BTREE      |         |
| uchome_pic |          1 | ind_uid_picid   |            2 | picid       | A         |     8064619 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

慢查询中出现在 4条关于uchome_pic的语句

SELECT * FROM uchome_pic WHERE albumid='0' AND uid='39706' AND picid<10096588 ORDER BY picid DESC LIMIT 1
SELECT * FROM uchome_pic WHERE albumid='441736' ORDER BY dateline DESC LIMIT 1640,20;
SELECT * FROM uchome_pic WHERE uid='300967'  ORDER BY picid DESC  LIMIT 120,20;
SELECT * FROM uchome_pic WHERE picid='4385852' AND uid='419518' LIMIT 1;




各个语句的执行计划
mysql> explain SELECT * FROM uchome_pic WHERE albumid='0' AND uid='39706' AND picid<10096588 ORDER BY picid DESC LIMIT 1;
+----+-------------+------------+-------+-------------------------------------------------------+---------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys                                         | key     | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+-------------------------------------------------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | uchome_pic | range | PRIMARY,albumid,ind_albumid_uid,ind_uid,ind_uid_picid | PRIMARY | 4       | NULL | 6017629 | Using where |
+----+-------------+------------+-------+-------------------------------------------------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> explain  SELECT * FROM uchome_pic WHERE albumid='441736' ORDER BY dateline DESC LIMIT 1640,20;
+----+-------------+------------+------+-------------------------+---------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys           | key     | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+-------------------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | uchome_pic | ref  | albumid,ind_albumid_uid | albumid | 3       | const | 1617 | Using where |
+----+-------------+------------+------+-------------------------+---------+---------+-------+------+-------------+


mysql> explain SELECT * FROM uchome_pic WHERE uid='300967'  ORDER BY picid DESC  LIMIT 120,20;
+----+-------------+------------+------+-----------------------+---------------+---------+-------+-------+-------------+
| id | select_type | table      | type | possible_keys         | key           | key_len | ref   | rows  | Extra       |
+----+-------------+------------+------+-----------------------+---------------+---------+-------+-------+-------------+
|  1 | SIMPLE      | uchome_pic | ref  | ind_uid,ind_uid_picid | ind_uid_picid | 3       | const | 39668 | Using where |
+----+-------------+------------+------+-----------------------+---------------+---------+-------+-------+-------------+


mysql> explain SELECT * FROM uchome_pic WHERE picid='4385852' AND uid='419518' LIMIT 1;
+----+-------------+------------+-------+-------------------------------+---------+---------+-------+------+-------+
| id | select_type | table      | type  | possible_keys                 | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+-------+-------------------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | uchome_pic | const | PRIMARY,ind_uid,ind_uid_picid | PRIMARY | 4       | const |    1 |       |
+----+-------------+------------+-------+-------------------------------+---------+---------+-------+------+-------+

飞鸿无痕

回复 1# spark_zhang

可否提供下面语句的结果？
select count(*)  from uchome_pic WHERE uid='39706' ;

spark_zhang

8,090,001

飞鸿无痕

回复 3# spark_zhang


    那么大啊，那就看下select count(*)  from uchome_pic WHERE albumid='0' AND uid='39706' ; 查询出是多少，如果不大的话，制定albumid，uid联合索引，应该会快一点！