作者:jawshan212 | 来源:互联网 | 2018-07-10 07:37
mysql全文索引使用得到,对性能提升有一定帮助;但是,若使用不得到,将会是异常灾难;mysql全文索引对整个优化器的索引选择都有干扰。看我生产环境下优化过的一
mysql全文索引使用得到,对性能提升有一定帮助;但是,若使用不得到,将会是异常灾难;mysql全文索引对整个优化器的索引选择都有干扰。看我生产环境下优化过的一
mysql全文索引使用得到,对性能提升有一定帮助;但是,若使用不得到,将会是异常灾难;mysql全文索引对整个优化器的索引选择都有干扰。看我生产环境下优化过的一条sql
SELECT DISTINCT pc.products_id, pd.products_name,p.products_date_added,pso.products_id
FROM products_to_categories AS pc
LEFT JOIN products_description AS pd ON pd.products_id=pc.products_id
LEFT JOIN products AS p ON p.products_id=pd.products_id
LEFT JOIN specials AS sps ON sps.products_id=p.products_id
LEFT JOIN temp_products_7days_orders_amount AS 7days ON 7days.products_id=pc.products_id
LEFT JOIN products_realtime_quantity AS prq ON prq.sku_or_poa = p.products_model
LEFT JOIN products_stockout AS pso ON pso.products_id=pd.products_id
WHERE p.products_status=1 AND (prq.msg != 'Temporary out stock.' OR ISNULL(prq.msg))
AND pc.categories_id IN ( 153,323,1055,1241,1431)
AND MATCH(pd.products_name) AGAINST('*iphone*' IN BOOLEAN MODE) AND MATCH(pd.products_name)
AGAINST('*c*' IN BOOLEAN MODE) ORDER BY 7days.orders_sum DESC
这条语句执行非常慢,经常出现卡住情况,有时候发现执行需要几分钟,而结果才几条,该语句也为涉及到大结果运算,各种连表条件上上都有索引。唯一特殊的地方就是pd.products_name为全文索引,而且执行的过程中pc.categories_id优先级高于pd.products_name全文索引,导致使用了pc.categories_id索引。按理来讲,这样也没有多大关系。但是explain后发现了问题
+----+-------------+-------+----------+-----------------------+-------------------+---------+---------------------------+------+----------------------------------------------+
| id | select_type | table | type
| possible_keys
| key
| key_len | ref
| rows | Extra
|
+----+-------------+-------+----------+-----------------------+-------------------+---------+---------------------------+------+----------------------------------------------+
| 1 | SIMPLE
| pc | range | PRIMARY,categories_id | categories_id
| 4
| NULL
| 307 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE
| pd | fulltext | PRIMARY,products_name | products_name
| 0
|
| 1 | Using where
|
| 1 | SIMPLE
| p
| eq_ref | PRIMARY
| PRIMARY
| 4
| banggood.pd.products_id | 1 | Using where
|
| 1 | SIMPLE
| sps | ref
| products_id
| products_id
| 4
| banggood.pd.products_id | 16 | Using index
|
| 1 | SIMPLE
| 7days | ref
| PRIMARY
| PRIMARY
| 4
| banggood.p.products_id | 1032 |
|
| 1 | SIMPLE
| prq | ref
| ix_prg_sku_or_poa
| ix_prg_sku_or_poa | 152
| banggood.p.products_model | 10 | Using where
|
| 1 | SIMPLE
| pso | eq_ref | PRIMARY
| PRIMARY
| 4
| banggood.pd.products_id | 1 | Using index
|
+----+-------------+-------+----------+-----------------------+-------------------+---------+---------------------------+------+----------------------------------------------+
我们发现驱动表示pc表,使用了categories_id索引,可能优化器优先选择了它,但是再看pd表,
按理来讲,这个时候pd表应该使用products_id索引,也就是这个表的primary key,但是优化器却选择了products_name全文索引,坑爹了!
profiling这条语句,执行时间为2分钟以上
+-------------------------+------------+-----------+------------+--------------+---------------+
| Status
| Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+-------------------------+------------+-----------+------------+--------------+---------------+
| starting
| 0.000415 | 0.000000 | 0.000000 |
0 |
0 |
| checking permissions | 0.000011 | 0.000000 | 0.000000 |
0 |
0 |
| checking permissions | 0.000004 | 0.000000 | 0.000000 |
0 |
0 |
| checking permissions | 0.000002 | 0.000000 | 0.000000 |
0 |
0 |
| checking permissions | 0.000003 | 0.000000 | 0.000000 |
0 |
0 |
| checking permissions | 0.000056 | 0.001000 | 0.000000 |
0 |
0 |
| checking permissions | 0.000006 | 0.000000 | 0.000000 |
0 |
0 |
| checking permissions | 0.000009 | 0.000000 | 0.000000 |
0 |
0 |
| Opening tables
| 0.000225 | 0.000000 | 0.000000 |
0 |
0 |
| System lock
| 0.000029 | 0.000000 | 0.000000 |
0 |
0 |
| init
| 0.000138 | 0.000000 | 0.000000 |
0 |
0 |
| optimizing
| 0.000046 | 0.000000 | 0.000000 |
0 |
0 |
| statistics
| 0.001115 | 0.001000 | 0.000000 |
0 |
0 |
| preparing
| 0.001246 | 0.002000 | 0.000000 |
0 |
0 |
| FULLTEXT initialization | 0.000088 | 0.000000 | 0.000000 |
0 |
0 |
| Creating tmp table
| 0.000057 | 0.000000 | 0.000000 |
0 |
0 |
| executing
| 0.000005 | 0.000000 | 0.000000 |
0 |
0 |
| Copying to tmp table | 120.430834 | 81.227651 | 38.749110 |
1112 |
0 |
| Sorting result
| 0.000058 | 0.000000 | 0.000000 |
0 |
0 |
| Sending data
| 0.000026 | 0.000000 | 0.000000 |
0 |
0 |
| end
| 0.000007 | 0.000000 | 0.000000 |
0 |
0 |
| removing tmp table
| 0.000015 | 0.000000 | 0.000000 |
0 |
0 |
| end
| 0.000041 | 0.001000 | 0.000000 |
0 |
0 |
| query end
| 0.000007 | 0.000000 | 0.000000 |
0 |
0 |
| closing tables
| 0.000023 | 0.000000 | 0.000000 |
0 |
0 |
| freeing items
| 0.008546 | 0.000000 | 0.007999 |
0 |
0 |
| logging slow query
| 0.000008 | 0.000000 | 0.000000 |
0 |
0 |
| logging slow query
| 0.000007 | 0.000000 | 0.000000 |
0 |
0 |
| cleaning up
| 0.000008 | 0.000000 | 0.000000 |
0 |
0 |
+-------------------------+------------+-----------+------------+--------------+---------------+
看到Copying to tmp table占据了大量的cpu运算。
看来,mysql优化器太弱了,又要我们强制使用索引了!force index(primary) ,强制使用pd表的主键