MySQL特性有哪些

這篇文章主要介紹“MySQL特性有哪些”，在日常操作中，相信很多人在MySQL特性有哪些問題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”MySQL特性有哪些”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

成都創(chuàng)新互聯(lián)是一家專注于網(wǎng)站建設(shè)、成都網(wǎng)站建設(shè)與策劃設(shè)計(jì),江源網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)十年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:江源等地區(qū)。江源做網(wǎng)站價(jià)格咨詢:18980820575

ICP 測(cè)試

首先，咱們來(lái)看一下打開 ICP 與關(guān)閉 ICP 之間的性能區(qū)別，以下是測(cè)試過程：

準(zhǔn)備數(shù)據(jù)：

create table icp(id int, age int, name varchar(30), memo varchar(600)) engine=innodb; alter table icp add index aind(age, name, memo); --let $i= 100000 while ($i)
{ --eval insert into icp values($i, 1, 'a$i', repeat('a$i', 100)) --dec $i }

PS: MySQL 有一個(gè)叫profile的東東，可以用來(lái)監(jiān)視 SQL 語(yǔ)句在各個(gè)階段的執(zhí)行情況，咱們可以使用這個(gè)工具來(lái)觀察 SQL 語(yǔ)句在各個(gè)階段的運(yùn)行情況，關(guān)于 profile 的詳細(xì)說明可以參考官方文檔。

打開 ICP 的性能測(cè)試：

set profiling=on;
set optimizer_switch="index_condition_pushdown=on”; （default enabled）
mysql>  select * from icp where age = 999 and name like '%999%';       
+------+------+------+------+
| id   | age  | name | memo |
+------+------+------+------+
| NULL |  999 | 999  | 999  |
+------+------+------+------+
1 row in set (0.00 sec)


mysql> explain select * from icp where age = 999 and name like '%999%'; 
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | icp   | ref  | aind          | aind | 5       | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)


mysql> show profiles;
+----------+------------+-----------------------------------------------------------------+
| Query_ID | Duration   | Query                                                           |
+----------+------------+-----------------------------------------------------------------+
|        1 | 0.00043550 | select * from icp where age = 999 and name like '%999%'         |
|        2 | 0.00043250 | explain select * from icp where age = 999 and name like '%999%' |
+----------+------------+-----------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)


mysql> show profile cpu,block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000084 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000064 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000046 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000020 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000082 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000022 | 0.000000 |   0.000000 |            0 |             0 |
| explaining           | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000022 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000031 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000013 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
13 rows in set, 1 warning (0.00 sec)


mysql> show session status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 2     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 4     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 1     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 42    |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 39    |
+----------------------------+-------+
18 rows in set (0.00 sec)

關(guān)閉 ICP 的性能測(cè)試：

mysql> set optimizer_switch="index_condition_pushdown=off”;
mysql> select * from icp where age = 1 and memo like '%9999%';
mysql>  select * from icp where age = 999 and name like '%999%';       
+------+------+------+------+
| id   | age  | name | memo |
+------+------+------+------+
| NULL |  999 | 999  | 999  |
+------+------+------+------+
1 row in set (0.00 sec)


mysql> explain select * from icp where age = 999 and name like '%999%';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | icp   | ref  | aind          | aind | 5       | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)


mysql> show profiles;
+----------+------------+-----------------------------------------------------------------+
| Query_ID | Duration   | Query                                                           |
+----------+------------+-----------------------------------------------------------------+
|        1 | 0.00043550 | select * from icp where age = 999 and name like '%999%'         |
|        2 | 0.00043250 | explain select * from icp where age = 999 and name like '%999%' |
|        3 | 0.00081350 | show session status like '%handler%'                            |
|        4 | 0.00010350 | set optimizer_switch="index_condition_pushdown=off"             |
|        5 | 0.00036525 | select * from icp where age = 999 and name like '%999%'         |
|        6 | 0.00032950 | explain select * from icp where age = 999 and name like '%999%' |
+----------+------------+-----------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)


mysql> show profile cpu,block io for query 5;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000068 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000020 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000088 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000017 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.000049 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000024 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)


mysql> show session status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 4     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 8     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 2     |
| Handler_read_last          | 0     |
| Handler_read_next          | 2     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 58    |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 54    |
+----------------------------+-------+
18 rows in set (0.00 sec)

測(cè)試結(jié)論：由以上測(cè)試情況可以看到，在二級(jí)索引是復(fù)合索引且前面的條件過濾性較低的情況下，打開 ICP 可以有效的降低 server 層和 engine 層之間交互的次數(shù)，從而有效的降低在運(yùn)行時(shí)間。

ICP 原理

5.6 之前，在 SQL 語(yǔ)句的執(zhí)行過程中，server 層通過 engine 的 api 獲取數(shù)據(jù)，然后再進(jìn)行 where_cond 的判斷（具體判斷邏輯在: evaluate_join_record），每一條數(shù)據(jù)都需要從engine層返回server層做判斷。我們回顧一下上面把 ICP 關(guān)掉的測(cè)試，可以看到 Handler_read_next 的值陡增，其原因是第 1 個(gè)字段區(qū)分度不高，且 memo 字段無(wú)法使用索引，造成了類似 index 掃描的的情況，性能較低。

5.6 之后，在利用索引掃描的過程中，如果發(fā)現(xiàn) where_cond 中含有這個(gè) index 相關(guān)的條件，則將此條件記錄在 handler 接口中，在索引掃描的過程中，只有滿足索引與handler接口的條件時(shí)，才會(huì)返回到 server 層做進(jìn)一步的處理，在前綴索引區(qū)分度不夠，其它字段區(qū)分度高的情況下可以有效的減少 server & engine之間的開銷，提升查詢性能。

ICP 源碼實(shí)現(xiàn)

我們?cè)谏闲」?jié)提到，index condition down 所用的條件是記在handler接口中的，咱們分析一下“記錄”的過程是如何實(shí)現(xiàn)的。

首先，優(yōu)化器計(jì)算代價(jià)后會(huì)生成一個(gè) JOIN_TAB 的左支樹，每一個(gè) JOIN_TAB 包含相關(guān)表的指針、表的讀取方式、訪問表所包含的索引等信息，優(yōu)化器會(huì)在make_join_readinfo 中對(duì)JOIN_TAB中表的訪問方式進(jìn)行相應(yīng)的修正，并進(jìn)一步將 where cond 中和索引相關(guān)的條件記錄到 table 的句柄中，堆棧如下：

#0 make_cond_for_index (cond=0x2b69680179e8, table=0x2b6968012100, keyno=0, other_tbls_ok=true)
#1 in push_index_cond (tab=0x2b696802aa48, keyno=0, other_tbls_ok=true, trace_obj=0x2b696413ec30)
#2 in make_join_readinfo (join=0x2b6968017db0, options=0, no_jbuf_after=4294967295)
#3 in JOIN::optimize (this=0x2b6968017db0)
#4 in mysql_execute_select (thd=0x3176760, select_lex=0x3179470, free_join=true)

其次， make_cond_for_index 是一個(gè)遞歸的過程，對(duì) where_cond中的每一個(gè)條件進(jìn)行判斷，對(duì)滿足條件的 cond 重新組合成一個(gè)新的cond，最后將新的 cond 掛在table->file 下面（table->file 指的是操作物理表的接口函數(shù)，此變量為thd下私有的，不共享，共享的是tab->table->s），詳細(xì)參考make_cond_for_index 的詳細(xì)實(shí)現(xiàn)，設(shè)置的堆棧如下：

#0 ha_innobase::idx_cond_push (this=0x2b696800e810, keyno=0, idx_cond=0x2b69680179e8)
#1 0x0000000000a60a55 in push_index_cond (tab=0x2b696802aa48, keyno=0, other_tbls_ok=true, trace_obj=0x2b696413ec30)
#2 0x0000000000a6362f in make_join_readinfo (join=0x2b6968017db0, options=0, no_jbuf_after=4294967295)
#3 0x0000000000d9b8bd in JOIN::optimize (this=0x2b6968017db0 #4 0x0000000000a5b9ae in mysql_execute_select (thd=0x3176760, select_lex=0x3179470, free_join=true)

再次，server 層根據(jù)生成的 JOIN_TAB 讀取engine層的內(nèi)容，在engine讀取的時(shí)候，會(huì)進(jìn)行index_condition_pushdown的調(diào)用，即 ICP 的調(diào)用，堆棧如下：

#0 Item_func_like::val_int (this=0x2b6978005a28)
#1 0x0000000001187b66 in innobase_index_cond (file=0x2b696800e810)
#2 0x0000000001393566 in row_search_idx_cond_check (mysql_rec=0x2b69680129f0 <incomplete sequence="" \361>, prebuilt=0x2b69680130f8, rec=0x2b692b56e4cf "\200", offsets=0x2b697008d450)
#3 0x0000000001397e2b in row_search_for_mysql (buf=0x2b69680129f0 <incomplete sequence="" \361>, mode=2, prebuilt=0x2b69680130f8, match_mode=1, direction=0)
#4 0x00000000011696b9 in ha_innobase::index_read (this=0x2b696800e810, buf=0x2b69680129f0 <incomplete sequence="" \361>, key_ptr=0x2b697800a660 "", key_len=5, find_flag=HA_READ_KEY_EXACT)
#5 0x00000000006ecc58 in handler::index_read_map (this=0x2b696800e810, buf=0x2b69680129f0 <incomplete sequence="" \361>, key=0x2b697800a660 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
#6 0x00000000006d6bb4 in handler::ha_index_read_map (this=0x2b696800e810, buf=0x2b69680129f0 <incomplete sequence="" \361>, key=0x2b697800a660 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
#7 0x00000000009a1870 in join_read_always_key (tab=0x2b697800a1b8)
#8 0x000000000099d480 in sub_select (join=0x2b6978005df0, join_tab=0x2b697800a1b8, end_of_records=false)
#9 0x000000000099c6c0 in do_select (join=0x2b6978005df0)
#10 0x00000000009980a4 in JOIN::exec (this=0x2b6978005df0)
#11 0x0000000000a5bac0 in mysql_execute_select (thd=0x32801a0, select_lex=0x3282eb0, free_join=true)

可見在 ICP 的判斷是調(diào)用相關(guān)item的函數(shù)的，雖然同是調(diào)用 server 層的函數(shù)，但是沒有 ICP 的調(diào)用需要根據(jù)主建找到記錄，然后再匹配，而有了 ICP 可以省略一次主鍵查找數(shù)據(jù)的過程，進(jìn)而提升效率。

ICP 使用限制及問題

只支持 select 語(yǔ)句；
5.6 中只支持 MyISAM 與 InnoDB 引擎;
ICP的優(yōu)化策略可用于range、ref、eq_ref、ref_or_null 類型的訪問數(shù)據(jù)方法；
不支持主建索引的 ICP；
當(dāng) SQL 使用覆蓋索引時(shí)但只檢索部分?jǐn)?shù)據(jù)時(shí)，ICP 無(wú)法使用，詳細(xì)的分析可以參考 bug#68554 中 Olav Sandst?的分析，代碼實(shí)現(xiàn)部分可以參考 make_join_readinfo；

在查詢的時(shí)候即使正確的使用索引的前Ｎ個(gè)字段（即遵循前綴索引的原則），還是會(huì)用到 ICP，無(wú)故的多了 ICP 相關(guān)的判斷，這應(yīng)該是一個(gè)退化的問題，例：

 mysql> explain select * from icp where age = 999 and name like '999%';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | icp   | range | aind          | aind | 98      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

PS: engine condition pushdown 是 NDB 使用的，其它引擎不支持。

補(bǔ)充：

如：
root@read 02:28:07>show status like ‘Handler_read%’;
+———————–+——-+
| Variable_name         | Value |
+———————–+——-+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 61    |
+———————–+——-+
6 rows in set (0.41 sec)
Handler_read_first 代表讀取索引頭的次數(shù)，如果這個(gè)值很高，說明全索引掃描很多。
Handler_read_key代表一個(gè)索引被使用的次數(shù)，如果我們新增加一個(gè)索引，可以查看Handler_read_key是否有增加，如果有增加，說明sql用到索引。
Handler_read_next 代表讀取索引的下列，一般發(fā)生range scan。
Handler_read_prev 代表讀取索引的上列，一般發(fā)生在ORDER BY … DESC。
Handler_read_rnd 代表在固定位置讀取行，如果這個(gè)值很高，說明對(duì)大量結(jié)果集進(jìn)行了排序、進(jìn)行了全表掃描、關(guān)聯(lián)查詢沒有用到合適的KEY。
Handler_read_rnd_next 代表進(jìn)行了很多表掃描，查詢性能低下。

到此，關(guān)于“MySQL特性有哪些”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！

本文題目：MySQL特性有哪些
網(wǎng)頁(yè)網(wǎng)址：http://bm7419.com/article38/jddhpp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供用戶體驗(yàn)、ChatGPT、品牌網(wǎng)站設(shè)計(jì)、關(guān)鍵詞優(yōu)化、做網(wǎng)站、網(wǎng)站收錄

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容