{"id":10006115,"date":"2022-08-05T18:32:18","date_gmt":"2022-08-05T10:32:18","guid":{"rendered":"https:\/\/ityet.com\/?p=10006115"},"modified":"2022-08-05T18:32:18","modified_gmt":"2022-08-05T10:32:18","slug":"mysql%e7%bb%9f%e8%ae%a1%e4%bf%a1%e6%81%af%e4%b8%8d%e5%87%86%e5%af%bc%e8%87%b4%e7%9a%84%e6%80%a7%e8%83%bd%e9%97%ae%e9%a2%98","status":"publish","type":"post","link":"https:\/\/itmax.cn\/?p=10006115","title":{"rendered":"MySQL\u7edf\u8ba1\u4fe1\u606f\u4e0d\u51c6\u5bfc\u81f4\u7684\u6027\u80fd\u95ee\u9898"},"content":{"rendered":"

\u7b80\u4ecb\uff1a \u7edf\u8ba1\u4fe1\u606f\u4e0d\u51c6\u5bfc\u81f4\u9519\u8bef\u7684\u6267\u884c\u8ba1\u5212\uff0c\u5f15\u53d1\u6027\u80fd\u95ee\u9898
\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\u9519\u8bef\u5bfc\u81f4\u4f18\u5316\u5668\u9009\u62e9\u9519\u8bef\u7684\u6267\u884c\u8ba1\u5212\u3002
\u4e00\u4e2a\u5ba2\u6237\u7684\u6027\u80fd\u4f18\u5316\u6848\u4f8b: \u6ca1\u6709\u4fee\u6539\u6570\u636e\u5e93\u5b9e\u4f8b\u7684\u4efb\u4f55\u914d\u7f6e\u53c2\u6570\u4ee5\u53ca\u4e1a\u52a1\u4ee3\u7801\u6ca1\u6709\u53d8\u66f4\u7684\u60c5\u51b5\u4e0b\uff0c\u4e00\u6761 sql \u51fa\u73b0\u5927\u5e45\u6027\u80fd\u4e0b\u964d\u3002<\/p>\n

\u6211\u4eec\u6765\u770b\u770b\u51fa\u95ee\u9898\u7684sql \u4ee5\u53ca\u4ed6\u7684\u6267\u884c\u8ba1\u5212:<\/p>\n

mysql> explain
-> SELECT count(con.id) ,
-> MAX(DAYNAME(con.date)) ,
-> now() ,
-> pcz.type,
-> pcz.c_c
-> FROM con AS con
-> join orders o on con.o_id = o.id
-> JOIN pcz AS pcz ON o.d_p_c_z_id = pcz.id
-> left join c c on con.c_id = c.id
-> WHERE con.date = current_date() and pcz.type = “T_D”<\/p>\n\n\n\n\n\n\n\n\n\n
-> GROUP BY con.date, pcz.c_c, pcz.type;<\/th>\n<\/tr>\n<\/thead>\n
id<\/td>\nselect_type<\/td>\ntable<\/td>\npartitions<\/td>\ntype<\/td>\npossible_keys<\/td>\nkey<\/td>\nkey_len<\/td>\nref<\/td>\nrows<\/td>\nfiltered<\/td>\nExtra<\/td>\n<\/tr>\n
1<\/td>\nSIMPLE<\/td>\npcz<\/td>\nNULL<\/td>\nALL<\/td>\nPRIMARY<\/td>\nNULL<\/td>\nNULL<\/td>\nNULL<\/td>\n194<\/td>\n10.00<\/td>\nUsing where; Using temporary; Using filesort<\/td>\n<\/tr>\n
1<\/td>\nSIMPLE<\/td>\no<\/td>\nNULL<\/td>\nref<\/td>\nPRIMARY,dpcz_FK<\/td>\ndpcz_FK<\/td>\n9<\/td>\ncustom.pcz.id<\/td>\n1642<\/td>\n100.00<\/td>\nUsing index<\/td>\n<\/tr>\n
1<\/td>\nSIMPLE<\/td>\ncon<\/td>\nNULL<\/td>\nref<\/td>\nFK_order,IDX_date<\/td>\nFK_order<\/td>\n8<\/td>\ncustom.o.id<\/td>\n1<\/td>\n4.23<\/td>\nUsing where<\/td>\n<\/tr>\n
1<\/td>\nSIMPLE<\/td>\nc<\/td>\nNULL<\/td>\neq_ref<\/td>\nPRIMARY<\/td>\nPRIMARY<\/td>\n8<\/td>\ncustom.con.c_id<\/td>\n1<\/td>\n100.00<\/td>\nUsing index<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u6267\u884c\u8ba1\u5212\u663e\u793a rows examined = (19410%)1642(14.23%)=1347 \u67e5\u770b\u6267\u884c\u8ba1\u5212\u6211\u4eec\u5c31\u53d1\u73b0 where \u6761\u4ef6 con.date = current_date() \u3002\u8fd9\u4e2a\u6761\u4ef6\u770b\u8d77\u6765\u66f4\u9002\u5408\u4f5c\u4e3a\u7d22\u5f15\u8fc7\u6ee4\u6570\u636e\u3002\u4f46\u662f \u4e3a\u4ec0\u4e48 MySQL \u4f18\u5316\u5668\u4e0d\u9009\u62e9\u8be5\u7d22\u5f15\u5462\uff1f\u63a5\u4e0b\u6765\u4f7f\u7528 force index \u5f3a\u5236\u6267\u884c\u8ba1\u5212\u4f7f\u7528 con.date \u5b57\u6bb5\u4e0a\u7684\u7d22\u5f15\u3002\u6267\u884c\u8ba1\u5212\u5982\u4e0b:<\/p>\n

mysql> explain
-> SELECT count(con.id) ,
-> MAX(DAYNAME(con.date)) ,
-> now() ,
-> pcz.type,
-> pcz.c_c
-> FROM con AS con USE INDEX(IDX_date)
-> join orders o on con.o_id = o.id
-> JOIN p_c_z AS pcz ON o.d_p_c_z_id = pcz.id
-> left join c c on con.c_id = c.id
-> WHERE con.date = current_date() and pcz.type = “T_D”<\/p>\n\n\n\n\n\n\n\n\n\n
-> GROUP BY con.date, pcz.c_c, pcz.type;<\/th>\n<\/tr>\n<\/thead>\n
id<\/td>\nselect_type<\/td>\ntable<\/td>\npartitions<\/td>\ntype<\/td>\npossible_keys<\/td>\nkey<\/td>\nkey_len<\/td>\nref<\/td>\nrows<\/td>\nfiltered<\/td>\nExtra<\/td>\n<\/tr>\n
1<\/td>\nSIMPLE<\/td>\ncon<\/td>\nNULL<\/td>\nref<\/td>\nIDX_date<\/td>\nIDX_date<\/td>\n3<\/td>\nconst<\/td>\n110446<\/td>\n100.00<\/td>\nUsing temporary; Using filesort<\/td>\n<\/tr>\n
1<\/td>\nSIMPLE<\/td>\nc<\/td>\nNULL<\/td>\neq_ref<\/td>\nPRIMARY<\/td>\nPRIMARY<\/td>\n8<\/td>\ncustom.con.c_id<\/td>\n1<\/td>\n100.00<\/td>\nUsing index<\/td>\n<\/tr>\n
1<\/td>\nSIMPLE<\/td>\no<\/td>\nNULL<\/td>\neq_ref<\/td>\nPRIMARY,dpcz_FK<\/td>\nPRIMARY<\/td>\n8<\/td>\ncustom.con.o_id<\/td>\n1<\/td>\n100.00<\/td>\nUsing where<\/td>\n<\/tr>\n
1<\/td>\nSIMPLE<\/td>\npcz<\/td>\nNULL<\/td>\neq_ref<\/td>\nPRIMARY<\/td>\nPRIMARY<\/td>\n8<\/td>\ncustom.o.d_p_c_z_id<\/td>\n1<\/td>\n10.00<\/td>\nUsing where<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u95ee\u9898\u6765\u4e86 rows examined = 110446(1<\/em>10%)=11045 rows\u6839\u636e\u8ba1\u7b97\u8bc4\u4f30\uff0c \u7b2c\u4e00\u4e2a\u6267\u884c\u8ba1\u5212\u7684 1347 \u5927\u6982\u662f 110446 \u7684\u5341\u5206\u4e4b\u4e00 \uff0c\u81f3\u5c11\u4ece\u8868\u9762\u4e0a\u770b\u6765\u8fd9\u4e2a\u662fMySQL \u4f18\u5316\u5668\u9009\u62e9\u7b2c\u4e00\u4e2a\u6267\u884c\u8ba1\u5212\u7684\u539f\u56e0\u3002<\/p>\n

\u4f46\u662f\u5bf9\u6bd4\u5b9e\u9645\u7684\u67e5\u8be2\u7ed3\u679c\u7684\u54cd\u5e94\u65f6\u95f4\uff0c\u80af\u5b9a\u7c97\u95ee\u9898\u4e86\u3002\u56e0\u4e3a\u6267\u884c\u8ba1\u5212\u4e8c \u7684sql \u7684\u54cd\u5e94\u65f6\u95f4\u5728\u9884\u671f\u4e4b\u5185\uff0c\u4f46\u662f\u6267\u884c\u8ba1\u5212\u4e00\u5bf9\u5e94\u7684\u54cd\u5e94\u65f6\u95f4\u53cd\u800c\u66f4\u6162\u3002<\/p>\n

\u8fdb\u4e00\u6b65\u6765\u770b\u8868 orders \u7684\u521b\u5efa\u8bed\u53e5\u4ee5\u53ca\u6267\u884c\u8ba1\u52121\uff0c\u6211\u4eec\u53d1\u73b0 \u8868pcz\u7684\u786e\u6709194\u884c\u3002\u7136\u540e\u67e5\u770b \u7d22\u5f15 orders.dpcz_FK\uff0c\u8868 orders \u8fd4\u56de 1642\u884c \uff0c\u56e0\u4e3a\u5916\u952e\u7ea6\u675f orders_ibfk_10\u7684\u5b9a\u4e49\uff0c\u4e5f\u5c31\u610f\u5473\u7740 \u8868 orders \u7684\u8bb0\u5f55\u6570\u5e94\u8be5\u662f 194*1642=318548 \uff0c\u4f46\u662f\u5b9e\u9645\u7684\u884c\u6570\u662f 32508150\uff0c\u767e\u500d\u4e8e\u6267\u884c\u8ba1\u5212\u4f30\u8ba1\u7684\u503c 318548 \u3002<\/p>\n

CREATE TABLE orders<\/code> (
id<\/code> bigint(20) NOT NULL AUTO_INCREMENT,
…
d_p_c_z_id<\/code> bigint(20) DEFAULT NULL,
…,
PRIMARY KEY (id<\/code>),
…
KEY dpcz_FK<\/code> (d_p_c_z_id<\/code>),
…
CONSTRAINT orders_ibfk_10<\/code> FOREIGN KEY (d_p_c_z_id<\/code>) REFERENCES p_c_z<\/code> (id<\/code>) ON DELETE CASCADE ON UPDATE CASCADE,
…
) ENGINE=InnoDB ….<\/p>\n\n\n\n\n\n\n
mysql> select * from mysql.innodb_table_stats where database_name=’cutom’ and table_name=’orders’;<\/th>\n<\/tr>\n<\/thead>\n
database_name<\/td>\ntable_name<\/td>\nlast_update<\/td>\nn_rows<\/td>\nclustered_index_size<\/td>\nsum_of_other_index_sizes<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\n2022-03-03 21:58:18<\/td>\n32508150<\/td>\n349120<\/td>\n697618<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u5206\u6790\u81f3\u6b64\uff0c\u6211\u4eec\u53ef\u4ee5\u65ad\u5b9a orders.dpcz_FK \u7684\u7edf\u8ba1\u4fe1\u606f\u662f\u4e0d\u51c6\u786e\u7684\uff0c\u4e8e\u662f\u4e4e\u6211\u4eec\u4f7f\u7528\u5982\u4e0b\u8bed\u53e5\u786e\u8ba4\u5b83\u7684\u5b9e\u9645\u6570\u636e\u91cf:<\/p>\n

mysql> select * from mysql.innodb_index_stats where database_name=’cutom’ and table_name=’orders’ and index_name=’dpcz_FK’;<\/p>\n\n\n\n\n\n\n\n\n\n
mysql> select * from mysql.innodb_index_stats where database_name=’custom’ and table_name=’orders’ and index_name=’dpcz_FK’;<\/th>\n<\/tr>\n<\/thead>\n
database_name<\/td>\ntable_name<\/td>\nindex_name<\/td>\nlast_update<\/td>\nstat_name<\/td>\nstat_value<\/td>\nsample_size<\/td>\nstat_description<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-02-28 12:35:30<\/td>\nn_diff_pfx01<\/td>\n19498<\/td>\n50<\/td>\nd_p_c_z_id<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-02-28 12:35:30<\/td>\nn_diff_pfx02<\/td>\n32283087<\/td>\n128<\/td>\nd_p_c_z_id,id<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-02-28 12:35:30<\/td>\nn_leaf_pages<\/td>\n55653<\/td>\nNULL<\/td>\nNumber of leaf pages in the index<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-02-28 12:35:30<\/td>\nsize<\/td>\n63864<\/td>\nNULL<\/td>\nNumber of pages in the index<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n
mysql> select count(distinct d_p_c_z_id) from orders;<\/th>\n<\/tr>\n<\/thead>\n
count(distinct d_p_c_z_id)<\/td>\n<\/tr>\n
195<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Bingo\uff01\u4ece\u4e0a\u9762\u6765\u770b \u8868 orders \u5b57\u6bb5 d_p_c_z_id \u7684\u533a\u5206\u5ea6(\u4e0d\u4e00\u6837\u7684\u503c\u7684\u603b\u6570)\u4e3a 195 \u3002\u5728\u4fe1\u606f\u7edf\u8ba1\u8868\u91cc\u9762 dpcz_FK\u7684 stat_value \u503c\u662f 19498 \uff0c\u663e\u7136\u8fd9\u4e2a\u503c\u662f\u4e0d\u51c6\u786e\u7684\u5e76\u4e14\u6bd4\u5b9e\u9645\u503c\u5927\u7684\u591a\uff0c100\u500d \u3002\u7d22\u5f15\u7684 state_value \u503c\u5e94\u8be5\u7b49\u4e8e\u8fd9\u4e2a\u5b57\u6bb5\u7684\u5728\u8868\u91cc\u9762\u7684\u533a\u5206\u5ea6\u3002<\/p>\n

\u5982\u679c\u4f7f\u7528\u6b63\u786e\u7684 \u7d22\u5f15 dpcz_FK \u7684\u503c stat_value 195 \u53bb\u91cd\u65b0\u8bc4\u4f30\u6267\u884c\u8ba1\u5212\u7684\u6210\u672c\uff0c\u6211\u4eec\u5c06\u5f97\u5230\u6267\u884c\u8ba1\u52121 \u7684\u7ed3\u679c 32508150\/195=166708 \uff0c\u5e76\u4e14\u6267\u884c\u8ba1\u5212\u9884\u4f30\u7684\u626b\u63cf\u7684\u884c\u6570\u5e94\u8be5\u662f (19410%)<\/em>166708(1<\/em>4.23%)=136804\u3002\u56e0\u4e3a\u8be5\u503c\u662f10\u500d\u4e8e\u6267\u884c\u8ba1\u52122 \u7684\u503c 11045 \u3002MySQL \u5728\u6ca1\u6709\u4f7f\u7528force index\u7684\u60c5\u51b5\u4e0b\u5c31\u80fd\u8d70\u5230\u6b63\u786e\u7684\u6267\u884c\u8ba1\u5212 \u3002<\/p>\n

\u8fd9\u4e2asql\u7684\u95ee\u9898\u89e3\u51b3\u4e86\uff0c\u4f46\u662f\u4e3a\u4ec0\u4e48 MySQL \u7684\u7edf\u8ba1\u4fe1\u606f\u4f1a\u8ba1\u7b97\u9519\u8bef\uff0c\u6211\u4eec\u5982\u4f55\u4fee\u590d\u5b83\u5462\uff1f<\/p>\n

\u56de\u7b54\u8fd9\u4e2a\u95ee\u9898\u4e4b\u524d\uff0c\u6211\u4eec\u5148\u4e86\u89e3\u4e00\u4e0b MySQL \u662f\u5982\u4f55\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f\u4ee5\u53ca\u54ea\u4e9b\u53c2\u6570\u63a7\u5236 \u8fd9\u4e2a\u52a8\u4f5c\u3002<\/p>\n

InnoDB \u662f\u5982\u4f55\u6536\u96c6\u8868\u7684\u7edf\u8ba1\u4fe1\u606f
\u6211\u4eec\u53ef\u4ee5\u901a\u8fc7\u663e\u5f0f\u7684\u65b9\u5f0f\u6216\u8005\u7cfb\u7edf\u81ea\u52a8\u91c7\u96c6\u8868\u7684\u7edf\u8ba1\u4fe1\u606f \u3002<\/p>\n

\u901a\u8fc7\u5f00\u542f\u53c2\u6570innodb_stats_auto_recalc =on(\u9ed8\u8ba4\u4e5f\u662f\u6253\u5f00\u7684) \u4ee5\u4fbf\u5728\u8868\u7684\u6570\u636e\u53d1\u751f\u91cd\u5927\u53d8\u5316\u4ee5\u540e\u6765\u81ea\u52a8\u6536\u96c6\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\u3002\u6bd4\u5982\u5f53\u8868\u4e2d\u768410% \u7684\u884c\u53d1\u751f\u53d8\u5316 \uff0cInnoDB \u5c06\u91cd\u65b0\u8ba1\u7b97\u7edf\u8ba1\u4fe1\u606f\u3002\u6216\u8005\u6211\u4eec\u53ef\u4ee5\u4f7f\u7528ANALYZE TABLE\u663e\u5f0f\u5730\u91cd\u65b0\u8ba1\u7b97\u7edf\u8ba1\u4fe1\u606f\u3002<\/p>\n

InnoDB \u4f7f\u7528\u968f\u673a\u91c7\u6837\u6280\u672f\u7684\u65b9\u6cd5\u91c7\u96c6\u7edf\u8ba1\u4fe1\u606f– \u968f\u673a\u62bd\u53d6\u7d22\u5f15\u9875\uff0c\u4f30\u8ba1\u7d22\u5f15\u7684\u57fa\u6570\u3002\u53c2\u6570 innodb_stats_persistent_sample_pages \u63a7\u5236\u91c7\u6837\u9875\u9762\u7684\u6570\u91cf\u3002\u53c2\u8003 https:\/\/dev.mysql.com\/doc\/ref…<\/a><\/p>\n

\u6839\u636e\u4ee3\u7801\u548c\u63cf\u8ff0\uff0c\u968f\u673a\u62bd\u6837\u5e76\u4e0d\u662f\u5b8c\u5168\u968f\u673a\u7684\u3002\u91c7\u6837\u9875\u9762\u5b9e\u9645\u4e0a\u662f\u6839\u636e\u91c7\u6837\u7b97\u6cd5\u9009\u62e9\u7684\u3002\u6700\u7ec8\uff0c\u4e0d\u540c\u952e\u503c\u7684\u603b\u6570\uff0c\u5373\u7d22\u5f15\u7684 stat_value \u5c06\u901a\u8fc7\u4ee5\u4e0b\u516c\u5f0f\u8ba1\u7b97<\/p>\n

N R <\/em> N_DIFF_AVG_LEAF\u3002\u5176\u4e2d<\/p>\n

N : \u53f6\u9875\u6570
R : level LA\u4e0a\u4e0d\u540ckey\u503c\u7684\u4e2a\u6570\u4e0elevel LA\u4e0a\u8bb0\u5f55\u603b\u6570\u7684\u6bd4\u503c
N_DIFF_AVG_LEAF<\/code>\uff1a\u5728\u6240\u6709 A \u53f6\u9875\u4e2d\u627e\u5230\u7684\u4e0d\u540c\u952e\u503c\u7684\u5e73\u5747\u6570\u3002
\u91c7\u6837\u7b97\u6cd5\u4ee3\u7801\u7684\u8be6\u7ec6\u4fe1\u606f\u53ef\u4ee5\u5728\u94fe\u63a5\u4e2d\u627e\u5230\uff1ahttps:\/\/github.com\/mysql\/mysq…<\/a><\/p>\n

\u57fa\u4e8e\u4e0a\u9762\u7684\u4ecb\u7ecd\uff0c\u6211\u4eec\u77e5\u9053\u5f53\u4e00\u4e2a\u8868\u7684\u7d22\u5f15\u53d1\u751f\u5206\u88c2\u65f6\uff0c\u65e0\u8bba\u662f\u53f6\u5b50\u9875\u6570\uff08N\uff09\uff0c\u8fd8\u662f \u5c42LA \u4e0a\u4e0d\u540c\u952e\u503c\u7684\u4e2a\u6570\u5360 \u5c42LA \u603b\u8bb0\u5f55\u6570\u7684\u6bd4\u503c\uff08R ) \u53d8\u5f97\u8d8a\u6765\u8d8a\u4e0d\u51c6\u786e\uff0c\u56e0\u6b64 stat_value \u7684\u8ba1\u7b97\u53ef\u80fd\u4e0d\u6b63\u786e\u3002\u4e00\u65e6\u53d1\u751f\u8fd9\u79cd\u60c5\u51b5\uff0c\u9664\u975e\u66f4\u6539\u53c2\u6570innodb_stats_persistent_sample_pages\u6216\u91cd\u5efa\u7d22\u5f15\uff0c\u5426\u5219\u663e\u5f0f\u91cd\u65b0\u8ba1\u7b97\uff08\u624b\u52a8\u8fd0\u884c ANALYZE TABLE\uff09\u5c06\u65e0\u6cd5\u751f\u6210\u6b63\u786e\u7684 stat_value\u3002<\/p>\n

\u89e3\u51b3\u65b9\u6cd5
\u6211\u4eec\u600e\u4e48\u4fee\u6b63\u8868\u7684\u7edf\u8ba1\u4fe1\u606f \uff0c\u5e76\u4e14\u963b\u6b62\u8fd9\u7c7b\u60c5\u51b5\u8fdb\u4e00\u6b65\u53d1\u751f\u3002<\/p>\n

\u7ecf\u8fc7\u524d\u9762\u7684\u5206\u6790\u548c\u8ba8\u8bba\uff0c\u6211\u4eec\u77e5\u9053 \u6709\u4e24\u4e2a\u56e0\u7d20\u5f71\u54cd\u6570\u636e\u5e93\u6536\u96c6\u8868\u7684\u7edf\u8ba1\u4fe1\u606f \uff0c<\/p>\n

innodb_stats_persistent_sample_pages: A<\/p>\n

\u7d22\u5f15\u7684\u7ec4\u7ec7\u65b9\u5f0f<\/p>\n

\u4e3a\u4e86\u80fd\u591f\u8ba9 InnoDB \u5f97\u5230\u6b63\u786e\u7684 \u7edf\u8ba1\u4fe1\u606f\uff0c\u6211\u4eec\u9700\u8981 \u8c03\u6574 innodb_stats_persistent_sample_pages\u6216\u8005\u91cd\u5efa\u7d22\u5f15 \u3002<\/p>\n

1 \u901a\u8fc7\u547d\u4ee4 analyze table \u4e0d\u91cd\u5efa\u7684\u65b9\u5f0f\uff0c\u4fdd\u6301 innodb_stats_persistent_sample_pages =128\uff0cstat_value \u7565\u5fae\u66f4\u6539\u4e3a 19582\uff0c\u63a5\u8fd1\u539f\u59cb\u4e0d\u6b63\u786e\u7684 19498\uff0c\u4ecd\u7136\u5173\u95ed\u3002\u7d22\u5f15\u4e2d\u7684\u53f6\u5b50\u9875\u6570\u4ece 55653 \u7565\u5fae\u66f4\u6539\u4e3a 55891\uff0c\u7d22\u5f15\u4e2d\u7684\u9875\u6570\u4e5f\u4ece 63864 \u7565\u5fae\u66f4\u6539\u4e3a 64248<\/p>\n\n\n\n\n\n\n
mysql> show variables = ‘innodb_stats_persistent_sample_pages;<\/th>\n<\/tr>\n<\/thead>\n
Variable_name<\/td>\nValue<\/td>\n<\/tr>\n
innodb_stats_persistent_sample_pages<\/td>\n128<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n
mysql> analyze table orders;<\/th>\n<\/tr>\n<\/thead>\n
Table<\/td>\nOp<\/td>\nMsg_type<\/td>\nMsg_text<\/td>\n<\/tr>\n
custom.orders<\/td>\nanalyze<\/td>\nstatus<\/td>\nOK<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n\n\n
mysql> select * from mysql.innodb_index_stats where database_name=’custom’ and table_name=’orders’ and index_name=’dpcz_FK’;<\/th>\n<\/tr>\n<\/thead>\n
database_name<\/td>\ntable_name<\/td>\nindex_name<\/td>\nlast_update<\/td>\nstat_name<\/td>\nstat_value<\/td>\nsample_size<\/td>\nstat_description<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-03-03 21:58:18<\/td>\nn_diff_pfx01<\/td>\n19582<\/td>\n50<\/td>\nd_p_c_z_id<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-03-03 21:58:18<\/td>\nn_diff_pfx02<\/td>\n32425512<\/td>\n128<\/td>\nd_p_c_z_id,id<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-03-03 21:58:18<\/td>\nn_leaf_pages<\/td>\n55891<\/td>\nNULL<\/td>\nNumber of leaf pages in the index<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-03-03 21:58:18<\/td>\nsize<\/td>\n64248<\/td>\nNULL<\/td>\nNumber of pages in the index<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

2 ANALYZE TABLE \u4e0d\u91cd\u5efa\uff0c\u4f46\u5c06 innodb_stats_persistent_sample_pages \u4ece 128 \u589e\u52a0\u5230 512\uff0c\u4f7f stat_value \u5230192\u975e\u5e38\u63a5\u8fd1\u5b9e\u9645\u57fa\u6570 195\u3002\u7d22\u5f15\u4e2d\u7684\u53f6\u9875\u6570\u53d1\u751f\u4e86\u5f88\u5927\u53d8\u5316\uff0c\u4ece 55653 \u5230 44188\u3002\u7d22\u5f15\u4e2d\u7684\u9875\u6570\u4e5f\u4ece\u4e5f\u53d1\u751f\u4e86\u5de8\u5927\u53d8\u5316\uff0c\u4ece 63864 \u53d8\u4e3a 50304\u3002<\/p>\n\n\n\n\n\n\n
mysql> show variables like ‘%persistent_sample%’;<\/th>\n<\/tr>\n<\/thead>\n
Variable_name<\/td>\nValue<\/td>\n<\/tr>\n
innodb_stats_persistent_sample_pages<\/td>\n512<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n
mysql> analyze table orders;<\/th>\n<\/tr>\n<\/thead>\n
Table<\/td>\nOp<\/td>\nMsg_type<\/td>\nMsg_text<\/td>\n<\/tr>\n
custom.orders<\/td>\nanalyze<\/td>\nstatus<\/td>\nOK<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n\n\n
mysql> select * from mysql.innodb_index_stats where database_name=’custom’ and table_name=’orders’ and index_name=’dpcz_FK’;<\/th>\n<\/tr>\n<\/thead>\n
database_name<\/td>\ntable_name<\/td>\nindex_name<\/td>\nlast_update<\/td>\nstat_name<\/td>\nstat_value<\/td>\nsample_size<\/td>\nstat_description<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-03-09 06:54:29<\/td>\nn_diff_pfx01<\/td>\n192<\/td>\n179<\/td>\nd_p_c_z_id<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-03-09 06:54:29<\/td>\nn_diff_pfx02<\/td>\n31751321<\/td>\n512<\/td>\nd_p_c_z_id,id<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-03-09 06:54:29<\/td>\nn_leaf_pages<\/td>\n44188<\/td>\nNULL<\/td>\nNumber of leaf pages in the index<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-03-09 06:54:29<\/td>\nsize<\/td>\n50304<\/td>\nNULL<\/td>\nNumber of pages in the index<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

3 \u91cd\u5efa\u8868\uff0c\u4fdd\u6301 innodb_stats_persistent_sample_pages \u4e3a128\uff0c\u540c\u6837\u5f97\u5230\u4e86\u6b63\u786e\u7684 stat_value 187\uff0c\u63a5\u8fd1\u771f\u5b9e\u57fa\u6570195\u3002\u7d22\u5f15\u4e2d\u7684\u53f6\u5b50\u9875\u6570\u5927\u5e45\u53d8\u5316\uff0c\u4ece55653\u53d8\u4e3a43733\uff0c\u7d22\u5f15\u4e2d\u7684\u9875\u6570\u4e5f\u4ece63864\u53d8\u5316\u5230 50111\u3002<\/p>\n\n\n\n\n\n\n
mysql> show variables = ‘innodb_stats_persistent_sample_pages’;<\/th>\n<\/tr>\n<\/thead>\n
Variable_name<\/td>\nValue<\/td>\n<\/tr>\n
innodb_stats_persistent_sample_pages<\/td>\n128<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

mysql> alter table orders engine=innodb;
Query OK, 0 rows affected (11 min 16.37 sec)<\/p>\n\n\n\n\n\n\n\n\n\n
mysql> select * from mysql.innodb_index_stats where database_name=’custom’ and table_name=’orders’ and index_name=’dpcz_FK’;<\/th>\n<\/tr>\n<\/thead>\n
database_name<\/td>\ntable_name<\/td>\nindex_name<\/td>\nlast_update<\/td>\nstat_name<\/td>\nstat_value<\/td>\nsample_size<\/td>\nstat_description<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-03-07 18:44:43<\/td>\nn_diff_pfx01<\/td>\n187<\/td>\n128<\/td>\nd_p_c_z_id<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-03-07 18:44:43<\/td>\nn_diff_pfx02<\/td>\n31531493<\/td>\n128<\/td>\nd_p_c_z_id,id<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-03-07 18:44:43<\/td>\nn_leaf_pages<\/td>\n43733<\/td>\nNULL<\/td>\nNumber of leaf pages in the index<\/td>\n<\/tr>\n
custom<\/td>\norders<\/td>\ndpcz_FK<\/td>\n2022-03-07 18:44:43<\/td>\nsize<\/td>\n50111<\/td>\nNULL<\/td>\nNumber of pages in the index<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u5728\u66f4\u6b63\u8868\u7edf\u8ba1\u6570\u636e\u540e\uff0cMySQL \u4f18\u5316\u5668\u4e5f\u4f1a\u9009\u62e9\u6b63\u786e\u7684\u6267\u884c\u8ba1\u5212\uff1a<\/p>\n

mysql> explain
SELECT count(con.id) ,<\/p>\n

   MAX(DAYNAME(con.date)) ,\n   now()                  ,\n   pcz.type,\n   pcz.c_c<\/code><\/pre>\n
FROM con AS con<\/p>\n
     join orders o on con.order_id = o.id\n     JOIN p_c_z AS pcz ON o.d_p_c_z_id = pcz.id\n     left join c c on con.c_id = c.id<\/code><\/pre>\n
WHERE con.date = current_date()
and pcz.type = “T_D”<\/p>\n\n\n\n\n\n\n\n\n\n
GROUP BY con.date, pcz.c_c,  pcz.type;<\/th>\n<\/tr>\n<\/thead>\n
id<\/td>\nselect_type<\/td>\ntable<\/td>\npartitions<\/td>\ntype<\/td>\npossible_keys<\/td>\nkey<\/td>\nkey_len<\/td>\nref<\/td>\nrows<\/td>\nfiltered<\/td>\nExtra<\/td>\n<\/tr>\n
1<\/td>\nSIMPLE<\/td>\ncon<\/td>\nNULL<\/td>\nref<\/td>\nFK_order,IDX_date<\/td>\nIDX_date<\/td>\n3<\/td>\nconst<\/td>\n3074<\/td>\n100.00<\/td>\nUsing temporary; Using filesort<\/td>\n<\/tr>\n
1<\/td>\nSIMPLE<\/td>\nc<\/td>\nNULL<\/td>\neq_ref<\/td>\nPRIMARY<\/td>\nPRIMARY<\/td>\n8<\/td>\ncustom.con.c_id<\/td>\n1<\/td>\n100.00<\/td>\nUsing index<\/td>\n<\/tr>\n
1<\/td>\nSIMPLE<\/td>\no<\/td>\nNULL<\/td>\neq_ref<\/td>\nPRIMARY,dpcz_FK<\/td>\nPRIMARY<\/td>\n8<\/td>\ncustom.con.order_id<\/td>\n1<\/td>\n100.00<\/td>\nUsing where<\/td>\n<\/tr>\n
1<\/td>\nSIMPLE<\/td>\npcz<\/td>\nNULL<\/td>\neq_ref<\/td>\nPRIMARY<\/td>\nPRIMARY<\/td>\n8<\/td>\ncustom.o.d_p_c_z_id<\/td>\n1<\/td>\n10.00<\/td>\nUsing where<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
4 rows in set, 1 warning (0.01 sec)
\u7ed3\u8bba
MySQL\u4f18\u5316\u5668\u4f9d\u8d56\u4e8e\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\u7684\u51c6\u786e\u6027\u6765\u9009\u62e9\u6700\u4f18\u7684\u6267\u884c\u8ba1\u5212\u3002\u6211\u4eec\u53ef\u4ee5\u901a\u8fc7\u66f4\u6539\u53c2\u6570 innodb_stats_persistent_sample_pages \u6765\u63a7\u5236\u7cfb\u7edf\u91c7\u96c6\u8868\u7edf\u8ba1\u4fe1\u606f\u7684\u51c6\u786e\u6027\u3002<\/p>\n
\u6211\u4eec\u8fd8\u53ef\u4ee5\u9009\u62e9\u901a\u8fc7\u5728\u5bf9\u7d22\u5f15\u8fdb\u884c\u788e\u7247\u6574\u7406\u7684\u540c\u65f6\u91cd\u5efa\/\u91cd\u5efa\u8868\u6765\u5f3a\u5236\u91cd\u65b0\u8ba1\u7b97\u8868\u7edf\u8ba1\u4fe1\u606f\uff0c\u8fd9\u6709\u52a9\u4e8e\u63d0\u9ad8\u8868\u7edf\u8ba1\u4fe1\u606f\u7684\u51c6\u786e\u6027\u3002\u91cd\u6784\u8868\uff0c\u6211\u4eec\u53ef\u4ee5\u76f4\u63a5\u7528 alter table xx;\u4fee\u6539\u8868\u6216\u8005\u4f7f\u7528 pt-online-schema-change \u8fbe\u5230\u540c\u6837\u7684\u6548\u679c\u3002<\/p>\n
\u539f\u6587\u94fe\u63a5\uff1ahttps:\/\/click.aliyun.com\/m\/10…<\/a><\/p>\n
\u672c\u6587\u4e3a\u963f\u91cc\u4e91\u539f\u521b\u5185\u5bb9\uff0c\u672a\u7ecf\u5141\u8bb8\u4e0d\u5f97\u8f6c\u8f7d\u3002<\/p>\n
https:\/\/segmentfault.com\/a\/1190000042276632<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
\u7b80\u4ecb\uff1a \u7edf\u8ba1\u4fe1\u606f\u4e0d\u51c6\u5bfc\u81f4\u9519\u8bef\u7684\u6267\u884c\u8ba1\u5212\uff0c\u5f15\u53d1\u6027\u80fd\u95ee\u9898\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\u9519\u8bef\u5bfc\u81f4\u4f18\u5316\u5668\u9009\u62e9\u9519\u8bef\u7684\u6267\u884c\u8ba1\u5212\u3002\u4e00\u4e2a\u5ba2\u6237\u7684\u6027 […]<\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-10006115","post","type-post","status-publish","format-standard","hentry"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10006115","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10006115"}],"version-history":[{"count":0,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10006115\/revisions"}],"wp:attachment":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10006115"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10006115"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10006115"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}