????????????????:
?????????????????????????????????????1????????????????????
?????????????????????????????????????????????
????????????SQL?????????
# Query_time: 70.472013 Lock_time: 0.000078 Rows_sent: 7915203 Rows_examined: 15984089 Rows_affected: 0
# Bytes_sent: 1258414478
use js_sku;
SET timestamp=1465850117;
SELECT
ss_id?? ss_sa_id?? ss_si_id?? ss_av_zid?? ss_av_fid?? ss_artno??
ss_av_zvalue?? ss_av_fvalue?? ss_av_zpic?? ss_av_fpic?? ss_number??
ss_sales?? ss_cprice?? ss_price?? ss_stock?? ss_orderid?? ss_status??
ss_add_time?? ss_lastmodify
FROM js_sgoods_sku
WHERE ss_si_id = 0 AND ss_status > 0
ORDER BY
ss_orderid DESC?? ss_av_fid ASC;
?????????????????? mysql slow log ??????????????????? 70s????
?????????????????????????????? ??????????????? ??????????????????? ??????????????м????
mysql> explain SELECT
-> ss_id?? ss_sa_id?? ss_si_id?? ss_av_zid?? ss_av_fid?? ss_artno??
-> ss_av_zvalue?? ss_av_fvalue?? ss_av_zpic?? ss_av_fpic?? ss_number??
-> ss_sales?? ss_cprice?? ss_price?? ss_stock?? ss_orderid?? ss_status??
-> ss_add_time?? ss_lastmodify
-> FROM js_sgoods_sku
-> WHERE ss_si_id = 0 AND ss_status > 0
-> ORDER BY
-> ss_orderid DESC?? ss_av_fid ASC;
+----+-------------+---------------+------+---------------+----------+---------+-------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+----------+---------+-------+---------+-----------------------------+
| 1 | SIMPLE | js_sgoods_sku | ref | ss_si_id | ss_si_id | 4 | const | 9516091 | Using where; Using filesort |
+----+-------------+---------------+------+---------------+----------+---------+-------+---------+-----------------------------+
1 row in set (0.00 sec)
????????????????????????????????????赽?л??????????? ????????????????????????????????м?????????
???????????????????????
????KEY `ss_si_id` (`ss_si_id`??`ss_av_zid`??`ss_av_fid`) USING BTREE??
????`ss_si_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '???js_sgoods_info.si_id'??
??????????? ???????????????????????????????? ??? ss_si_id ????????????? goods_info ??????????????????????????????????????????
????????????????????????????? ???????????????????к?? ss_si_id=0 ???￡??????????????????????????????
????1. ??????????????????????????????????
????2. ?????????count ss_si_id=0 ???????ж??????????????????????????
???????????????????????????????????
xiean@localhost:js_sku 03:27:42>show index from js_sgoods_sku;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| js_sgoods_sku | 0 | PRIMARY | 1 | ss_id      | A | 18115773 | NULL | NULL | | BTREE | | |
| js_sgoods_sku | 1 | ss_si_id | 1 | ss_si_id   | A  | 1811577  | NULL | NULL | | BTREE | | |
| js_sgoods_sku | 1 | ss_si_id | 2 | ss_av_zid | A | 6038591  | NULL | NULL | | BTREE | | |
| js_sgoods_sku | 1 | ss_si_id | 3 | ss_av_fid | A | 18115773 | NULL | NULL | | BTREE | | |
| js_sgoods_sku | 1 | IDX_001 | 1 | ss_sa_id | A | 3623154   | NULL | NULL | | BTREE | | |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+