推薦：MySQL order by性能優(yōu)化方法實例
這篇文章主要介紹了MySQL order by性能優(yōu)化方法實例,本文講解了MySQL中order by的原理和優(yōu)化order by的三種方法,需要的朋友可以參考下 前言 工作過程中,各種業(yè)務需求在訪問數(shù)據(jù)庫的時候要求有order by排序。有時候不必要的或者不合理的排序操作很可能導致數(shù)據(jù)庫系統(tǒng)崩

 　　這篇文章主要介紹了MySQL Index Condition Pushdown(ICP)性能優(yōu)化方法實例,本文講解了概念介紹、原理、實踐案例、案例分析、ICP的使用限制等內容,需要的朋友可以參考下

　　一 概念介紹

　　Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一種在存儲引擎層使用索引過濾數(shù)據(jù)的一種優(yōu)化方式。

　　a 當關閉ICP時,index 僅僅是data access 的一種訪問方式，存儲引擎通過索引回表獲取的數(shù)據(jù)會傳遞到MySQL Server 層進行where條件過濾。

　　b 當打開ICP時,如果部分where條件能使用索引中的字段,MySQL Server 會把這部分下推到引擎層,可以利用index過濾的where條件在存儲引擎層進行數(shù)據(jù)過濾,而非將所有通過index access的結果傳遞到MySQL server層進行where過濾.

　　優(yōu)化效果:ICP能減少引擎層訪問基表的次數(shù)和MySQL Server 訪問存儲引擎的次數(shù),減少io次數(shù)，提高查詢語句性能。

　　二 原理

　　Index Condition Pushdown is not used:

　　1 Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row.

　　2 Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

　　Index Condition Pushdown is used

　　1 Get the next row s index tuple (but not the full table row).

　　2 Test the part of the WHERE condition that applies to this table and can be checked using only index columns.

　　If the condition is not satisfied, proceed to the index tuple for the next row.

　　3 If the condition is satisfied, use the index tuple to locate and read the full table row.

　　4 est the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

　　三 實踐案例

　　a 環(huán)境準備

　　數(shù)據(jù)庫版本 5.6.16

　　關閉緩存

　　代碼如下:

　　set query_cache_size=0;

　　set query_cache_type=OFF;

　　測試數(shù)據(jù)下載地址

　　b 當開啟ICP時

　　代碼如下:

　　mysql> SET profiling = 1;

　　Query OK, 0 rows affected, 1 warning (0.00 sec)

　　mysql> select * from employees where first_name='Anneke' and last_name like '%sig' ;

　　+--------+------------+------------+-----------+--------+------------+

　　| emp_no | birth_date | first_name | last_name | gender | hire_date |

　　+--------+------------+------------+-----------+--------+------------+

　　| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |

　　+--------+------------+------------+-----------+--------+------------+

　　1 row in set (0.00 sec)

　　mysql> show profiles;

　　+----------+------------+--------------------------------------------------------------------------------+

　　| Query_ID | Duration | Query |

　　+----------+------------+--------------------------------------------------------------------------------+

　　| 1 | 0.00060275 | select * from employees where first_name='Anneke' and last_name like '%sig' |

　　+----------+------------+--------------------------------------------------------------------------------+

　　3 rows in set, 1 warning (0.00 sec)

　　此時情況下根據(jù)MySQL的最左前綴原則, first_name 可以使用索引，last_name采用了like 模糊查詢，不能使用索引。

　　c 關閉ICP

　　 代碼如下:

　　mysql> set optimizer_switch='index_condition_pushdown=off';

　　Query OK, 0 rows affected (0.00 sec)

　　mysql> SET profiling = 1;

　　Query OK, 0 rows affected, 1 warning (0.00 sec)

　　mysql> select * from employees where first_name='Anneke' and last_name like '%sig' ;

　　+--------+------------+------------+-----------+--------+------------+

　　| emp_no | birth_date | first_name | last_name | gender | hire_date |

　　+--------+------------+------------+-----------+--------+------------+

　　| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |

　　+--------+------------+------------+-----------+--------+------------+

　　1 row in set (0.00 sec)

　　mysql> SET profiling = 0;

　　Query OK, 0 rows affected, 1 warning (0.00 sec)

　　mysql> show profiles;

　　+----------+------------+--------------------------------------------------------------------------------+

　　| Query_ID | Duration | Query |

　　+----------+------------+--------------------------------------------------------------------------------+

　　| 2 | 0.00097000 | select * from employees where first_name='Anneke' and last_name like '%sig' |

　　+----------+------------+--------------------------------------------------------------------------------+

　　6 rows in set, 1 warning (0.00 sec)

　　當開啟ICP時 查詢在sending data環(huán)節(jié)時間消耗是 0.000189s

　　 代碼如下:

　　mysql> show profile cpu,block io for query 1;

　　+----------------------+----------+----------+------------+--------------+---------------+

　　| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

　　+----------------------+----------+----------+------------+--------------+---------------+

　　| starting | 0.000094 | 0.000000 | 0.000000 | 0 | 0 |

　　| checking permissions | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |

　　| Opening tables | 0.000025 | 0.000000 | 0.000000 | 0 | 0 |

　　| init | 0.000044 | 0.000000 | 0.000000 | 0 | 0 |

　　| System lock | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |

　　| optimizing | 0.000021 | 0.000000 | 0.000000 | 0 | 0 |

　　| statistics | 0.000093 | 0.000000 | 0.000000 | 0 | 0 |

　　| preparing | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |

　　| executing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |

　　| Sending data | 0.000189 | 0.000000 | 0.000000 | 0 | 0 |

　　| end | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |

　　| query end | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |

　　| closing tables | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |

　　| freeing items | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |

　　| cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |

　　+----------------------+----------+----------+------------+--------------+---------------+

　　15 rows in set, 1 warning (0.00 sec)

　　當關閉ICP時 查詢在sending data環(huán)節(jié)時間消耗是 0.000735s

　　 代碼如下:

　　mysql> show profile cpu,block io for query 2;

　　+----------------------+----------+----------+------------+--------------+---------------+

　　| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

　　+----------------------+----------+----------+------------+--------------+---------------+

　　| starting | 0.000045 | 0.000000 | 0.000000 | 0 | 0 |

　　| checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |

　　| Opening tables | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |

　　| init | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |

　　| System lock | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |

　　| optimizing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |

　　| statistics | 0.000049 | 0.000000 | 0.000000 | 0 | 0 |

　　| preparing | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |

　　| executing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |

　　| Sending data | 0.000735 | 0.001000 | 0.000000 | 0 | 0 |

　　| end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |

　　| query end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |

　　| closing tables | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |

　　| freeing items | 0.000023 | 0.000000 | 0.000000 | 0 | 0 |

　　| cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |

　　+----------------------+----------+----------+------------+--------------+---------------+

　　15 rows in set, 1 warning (0.00 sec)

　　從上面的profile 可以看出ICP 開啟時整個sql 執(zhí)行時間是未開啟的2/3，sending data 環(huán)節(jié)的時間消耗前者僅是后者的1/4。

　　ICP 開啟時的執(zhí)行計劃 含有 Using index condition 標示 ，表示優(yōu)化器使用了ICP對數(shù)據(jù)訪問進行優(yōu)化。

　　 代碼如下:

　　mysql> explain select * from employees where first_name='Anneke' and last_name like '%nta' ;

　　+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+

　　| 1 | SIMPLE | employees | ref | idx_emp_fnln | idx_emp_fnln | 44 | const | 224 | Using index condition |

　　+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+

　　1 row in set (0.00 sec)

　　ICP 關閉時的執(zhí)行計劃顯示use where.

　　 代碼如下:

　　mysql> explain select * from employees where first_name='Anneke' and last_name like '%nta' ;

　　+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+

　　| 1 | SIMPLE | employees | ref | idx_emp_fnln | idx_emp_fnln | 44 | const | 224 | Using where |

　　+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+

　　1 row in set (0.00 sec)

　　案例分析

　　以上面的查詢?yōu)槔�關閉ICP 時,存儲引擎通前綴index first_name 訪問表中225條first_name 為Anneke的數(shù)據(jù),并在MySQL server層根據(jù)last_name like '%sig' 進行過濾

分享：MySQL slave_net_timeout參數(shù)解決的一個集群問題案例
這篇文章主要介紹了MySQL slave_net_timeout參數(shù)解決的一個集群問題案例,問題日志請見正文,本文使用slave_net_timeout參數(shù)解決了這個問題,需要的朋友可以參考下 【背景】 對一套數(shù)據(jù)庫集群進行5.5升級到5.6之后,alter.log 報warning異常。 復制代碼 代碼如下: 2015-02-