MySQL 8.0 information_schema系統(tǒng)庫做了哪些改進?
目錄
information_schema有何用？
mysql8.0 之前的查詢方式
mysql8.0 開始的查詢方式
測試5.7和8.0不同版本訪問I_S庫的性能
結論
information_schema有何用？
通過I_S獲取mysql的一些元數(shù)據(jù)信息
獲取表的數(shù)據(jù)文件、索引文件的大小、碎片情況、表行數(shù)、自增列增長情況等
獲取正在運行的事務有那些，是否有阻塞等
獲取當前mysql的連接processlist等等
mysql8.0 之前的查詢方式
會在查詢information_schema 某個表時創(chuàng)建臨時表
來自文件的元數(shù)據(jù)，掃描文件系統(tǒng)獲取FRM文件的表定義
存儲引擎的詳細信息，例如動態(tài)表統(tǒng)計信息
來自MySQL服務器中全局數(shù)據(jù)結構的數(shù)據(jù)
在表的數(shù)量很多時，每次查詢I_S會從文件系統(tǒng)中讀取每個單獨的FRM文件，使用更多的CPU周期來打開表并準備相關的內存數(shù)據(jù)結構
mysql8.0 開始的查詢方式
引入了基于InnoDB的本地數(shù)據(jù)字典表
表中不在有FRM表定義文件
所有數(shù)據(jù)庫表的元數(shù)據(jù)都存儲在事務數(shù)據(jù)字典表中
I_S中表被設計為數(shù)據(jù)字典表上的VIEW（有些還是臨時表0_0）
消除了以下成本
查詢INFORMATION_SCHEMA時創(chuàng)建的臨時表
掃描文件系統(tǒng)目錄以查找FRM文件
改進
利用MySQL優(yōu)化器的全部功能，使用數(shù)據(jù)字典表上的索引來更好的查詢
mysql5.7中表文件
ll test*
Jul 10 10:52 testse.frm
Jul 10 10:52 testse.ibd
mysql8.0中表文件
ll test*
Jul 10 10:25 testse.ibd
mysql5.7 和mysql8.0 I_S 中tables 表的存在方式
mysql5.7.22
show?create?table?information_schema.tables\G
*************************** 1.?row?***************************
???????Table: TABLES
Create?Table: CREATE?TEMPORARY?TABLE?`TABLES`?(
??`TABLE_CATALOG`?varchar(512) NOT?NULL?DEFAULT?'',
??`TABLE_SCHEMA`?varchar(64) NOT?NULL?DEFAULT?'',
??`TABLE_NAME`?varchar(64) NOT?NULL?DEFAULT?'',
??`TABLE_TYPE`?varchar(64) NOT?NULL?DEFAULT?'',
??`ENGINE`?varchar(64) DEFAULT?NULL,
mysql8.0.15
??show?create?table?information_schema.tables\G
*************************** 1.?row?***************************
????????????????View: TABLES
?????????Create?View: CREATE?ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost`?SQL?SECURITY?DEFINER VIEWmysql5.7 中I_S中tables表是以臨時表的形式存在的（查詢該表就會創(chuàng)建臨時表，創(chuàng)建的臨時表過多，可能會導致mysql占用的內存暴漲，出現(xiàn)OOM）
mysql8.0 中I_S中tables表以視圖的形式存在（查詢該視圖，不會創(chuàng)建臨時表，會使用到視圖中表的索引）
mysql5.7中獲取表大小情況
SELECT???table_name, CONCAT(FORMAT(data_length / 1024?/ 1024, 2), 'M') AS?dbdata_size, CONCAT(FORMAT(index_length / 1024?/ 1024, 2), 'M') AS?dbindex_size, CONCAT( FORMAT((data_length + index_length) / 1024?/ 1024?/ 1024,2), 'G') AS?`db_size(G)`, AVG_ROW_LENGTH, table_rows, update_time FROM???information_schema.tables WHERE?table_schema = 'test'???AND?table_name = 'testse';
+------------+-------------+--------------+------------+----------------+------------+---------------------+
| table_name | dbdata_size | dbindex_size | db_size(G) | AVG_ROW_LENGTH | table_rows | update_time |
+------------+-------------+--------------+------------+----------------+------------+---------------------+
| testse | 0.02M | 0.02M | 0.00G | 862 | 19 | 2019-07-10 10:52:02 |
+------------+-------------+--------------+------------+----------------+------------+---------------------+
執(zhí)行計劃中出現(xiàn)了where，未用到索引（存儲引擎檢索數(shù)據(jù)后，server層進行過濾）
desc SELECT???table_name, CONCAT(FORMAT(data_length / 1024?/ 1024, 2), 'M') AS?dbdata_size, CONCAT(FORMAT(index_length / 1024?/ 1024, 2), 'M') AS?dbindex_size, CONCAT( FORMAT(data_length + index_length / 1024?/ 1024?/ 1024, 2???), 'G') AS?`db_size(G)`, AVG_ROW_LENGTH, table_rows, update_time FROM???information_schema.tables WHERE?table_schema = 'test'???AND?table_name = 'testse';
+----+-------------+--------+------------+------+---------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
| 1 | SIMPLE | tables | NULL | ALL | NULL | TABLE_SCHEMA,TABLE_NAME | NULL | NULL | NULL | NULL | Using where; Open_full_table; Scanned 0 databases |
+----+-------------+--------+------------+------+---------------+-------------------------+---------+------+------+----------+---------------------------------------------------+mysql8.0中獲取表大小情況
SELECT table_name, CONCAT(FORMAT(data_length / 1024?/ 1024, 2), 'M') AS dbdata_size, CONCAT(FORMAT(index_length / 1024?/ 1024, 2), 'M') AS dbindex_size, CONCAT( FORMAT((data_length + index_length) / 1024?/ 1024?/ 1024,2), 'G') AS `db_size(G)`, AVG_ROW_LENGTH, table_rows, update_time FROM information_schema.tables WHERE table_schema = 'test'???AND table_name = 'testse';
+------------+-------------+--------------+------------+----------------+------------+---------------------+
| TABLE_NAME |?dbdata_size | dbindex_size |?db_size(G) | AVG_ROW_LENGTH |?TABLE_ROWS | UPDATE_TIME |
+------------+-------------+--------------+------------+----------------+------------+---------------------+
| testse |?0.02M | 0.02M |?0.00G | 862 |?????????19?| 2019-07-10 10:25:16 |
+------------+-------------+--------------+------------+----------------+------------+---------------------+
能使用到數(shù)據(jù)字典表的索引
desc SELECT table_name, CONCAT(FORMAT(data_length / 1024?/ 1024, 2), 'M') AS dbdata_size, CONCAT(FORMAT(index_length / 1024?/ 1024, 2), 'M') AS dbindex_size, CONCAT( FORMAT((data_length + index_length) / 1024?/ 1024?/ 1024,2), 'G') AS `db_size(G)`, AVG_ROW_LENGTH, table_rows, update_time FROM information_schema.tables WHERE table_schema = 'test'???AND table_name = 'testse';
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
| id |?select_type | table |?partitions | type |?possible_keys | key |?key_len | ref |?rows | filtered |?Extra |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
|??1?| SIMPLE |?cat | NULL |?index | PRIMARY |?name | 194 |?NULL | 1 |???100.00?| Using index |
| 1 |?SIMPLE | sch |?NULL | eq_ref |?PRIMARY,catalog_id | catalog_id |?202?????| mysql.cat.id,const |????1?| 100.00 |?Using index |
|??1?| SIMPLE |?tbl | NULL |?eq_ref | schema_id |?schema_id | 202 |?mysql.sch.id,const | 1 |???100.00?| Using where |
| 1 |?SIMPLE | stat |?NULL | const |?PRIMARY | PRIMARY |?388?????| const,const |????1?| 100.00 |?NULL |
|??1?| SIMPLE |?ts | NULL |?eq_ref | PRIMARY |?PRIMARY | 8 |?mysql.tbl.tablespace_id | 1 |???100.00?| NULL |
| 1 |?SIMPLE | col |?NULL | eq_ref |?PRIMARY | PRIMARY |?8???????| mysql.tbl.collation_id |????1?| 100.00 |?Using index |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+測試5.7和8.0不同版本訪問I_S庫的性能
機器
cat?/etc/redhat-release | xargs echo?'版本 '?&& dmidecode -s system-product-name | xargs echo?'是否虛擬化 '?&& cat?/proc/cpuinfo |grep?"processor"|wc -l?| xargs echo?'cpu核數(shù) '
版本 CentOS Linux release 7.5.1804?(Core)
是否虛擬化 KVM
cpu核數(shù) 41、分別在mysql5.7和mysql8.0 中創(chuàng)建5個庫，每個庫中30個表（共1500個表），每個表記錄數(shù)為10000
user=admin
passwd=admin
port=57222
host=127.0.0.1
#創(chuàng)建5個庫,分別在每個庫中創(chuàng)建30個表
for?i in?{1..5};do
mysql -u$user?-p$passwd?-P$port?-h$host<
EOF
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql_storage_engine=innodb --table-size=10000 --tables=30 --mysql-db=test_${i}?--mysql-user=$user?--mysql-password=$passwd?--mysql-port=$port??--mysql-host=$host?--threads=8 --time=10 --report-interval=1 --events=0 --db-driver=mysql prepare
done;2、自定義訪問I_S.tables表的sysbench腳本
cat tests/mytest.lua
require("oltp_common")
function?thread_init(thread_id)
?drv=sysbench.sql.driver()
?con=drv:connect()
end
local function?get_rand_db()
???return?sysbench.rand.uniform(1, 5)
end
function?event(thread_id)
local vid1
local dbprefix
vid1=get_rand_db()
dbprefix = "test_"
-- 生成5個db中的隨機一個db,如 test_1 ,test_5
-- vid2=string.format("'%s%s'",test_,vid1)
vid2="'"?.. dbprefix .. vid1 .. "'"
con:query("SELECT table_name, CONCAT(FORMAT(data_length / 1024 / 1024, 2), 'M') AS dbdata_size, CONCAT(FORMAT(index_length / 1024 / 1024, 2), 'M') AS dbindex_size, CONCAT( FORMAT((data_length + index_length) / 1024 / 1024 / 1024,2), 'G') AS `db_size(G)`, AVG_ROW_LENGTH, table_rows, update_time FROM information_schema.tables WHERE table_schema ="?.. vid2)
end
function?thread_done()
?con:disconnect()
end3、腳本通過sysbench測試訪問I_S.tables的qps
cat test_I_S_access.sh
require("oltp_common")
function?thread_init(thread_id)
?drv=sysbench.sql.driver()
?con=drv:connect()
end
local function?get_rand_db()
???return?sysbench.rand.uniform(1, 5)
end
function?event(thread_id)
local vid1
local dbprefix
vid1=get_rand_db()
dbprefix = "test_"
-- 生成5個db中的隨機一個db,如 test_1 ,test_5
-- vid2=string.format("'%s%s'",test_,vid1)
vid2="'"?.. dbprefix .. vid1 .. "'"
con:query("SELECT table_name, CONCAT(FORMAT(data_length / 1024 / 1024, 2), 'M') AS dbdata_size, CONCAT(FORMAT(index_length / 1024 / 1024, 2), 'M') AS dbindex_size, CONCAT( FORMAT((data_length + index_length) / 1024 / 1024 / 1024,2), 'G') AS `db_size(G)`, AVG_ROW_LENGTH, table_rows, update_time FROM information_schema.tables WHERE table_schema ="?.. vid2)
end
function?thread_done()
?con:disconnect()
end4、mysql5.7和mysql8.0 主要配置
mysql5.7.22
5.7.22-log
innodb_buffer_pool_size?128M
innodb_log_buffer_size??64M
innodb_log_file_size????48M
binlog_format???ROW
innodb_flush_log_at_trx_commit??1
sync_binlog?????1
mysql8.0.15
8.0.15
innodb_buffer_pool_size?128M
innodb_log_buffer_size??64M
innodb_log_file_size????48M
binlog_format???ROW
innodb_flush_log_at_trx_commit??1
sync_binlog?????15、mysql5.7開啟測試
bash test_I_S_acess.sh 57222 ?60
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
mysql version
5.7.22-log
Variable_name Value
Created_tmp_tables 1
sysbench 1.1.0?(using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads:?40
Report intermediate results every 10?second(s)
Initializing random number?generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds:?40?tps:?4765.73?qps:?4765.73?(r/w/o: 4765.73/0.00/0.00) lat?(ms,95%): 18.95?err/s:?0.00?reconn/s:??????????????????????????????????????????????????????????????????????????????????????????????????????????????0.00
[ 20s ] thds:?40?tps:?5409.00?qps:?5409.00?(r/w/o: 5409.00/0.00/0.00) lat?(ms,95%): 17.95?err/s:?0.00?reconn/s:??????????????????????????????????????????????????????????????????????????????????????????????????????????????0.00
[ 30s ] thds:?40?tps:?5154.45?qps:?5154.45?(r/w/o: 5154.45/0.00/0.00) lat?(ms,95%): 18.61?err/s:?0.00?reconn/s:??????????????????????????????????????????????????????????????????????????????????????????????????????????????0.00
[ 40s ] thds:?40?tps:?5383.50?qps:?5383.50?(r/w/o: 5383.50/0.00/0.00) lat?(ms,95%): 17.63?err/s:?0.00?reconn/s:??????????????????????????????????????????????????????????????????????????????????????????????????????????????0.00
[ 50s ] thds:?40?tps:?5456.11?qps:?5456.11?(r/w/o: 5456.11/0.00/0.00) lat?(ms,95%): 17.63?err/s:?0.00?reconn/s:??????????????????????????????????????????????????????????????????????????????????????????????????????????????0.00
[ 60s ] thds:?40?tps:?5458.66?qps:?5458.66?(r/w/o: 5458.66/0.00/0.00) lat?(ms,95%): 17.63?err/s:?0.00?reconn/s:??????????????????????????????????????????????????????????????????????????????????????????????????????????????0.00
SQL statistics:
????queries performed:
????????read: 316322
????????write: 0
????????other: 0
????????total:???????????????????????????316322
????transactions:????????????????????????316322?(5270.99?per sec.)
????queries:?????????????????????????????316322?(5270.99?per sec.)
????ignored errors:??????????????????????0??????(0.00?per sec.)
????reconnects:??????????????????????????0??????(0.00?per sec.)
Throughput:
????events/s (eps): 5270.9892
????time elapsed: 60.0119s
????total number?of events:??????????????316322
Latency (ms):
?????????min: 0.42
?????????avg:????????????????????????????????????7.59
?????????max: 858.18
?????????95th?percentile: 17.95
?????????sum: 2399738.58
Threads fairness:
????events (avg/stddev): 7908.0500/53.41
????execution time (avg/stddev): 59.9935/0.00
mysql version
5.7.22-log
Variable_name Value
Created_tmp_tables 316327cpu使用率接近100%
mysql5.7.22 40個并發(fā)線程 壓力測試60秒，訪問I_S.tables 平均qps為5k，并創(chuàng)建了31w個臨時表!
6、mysql8.0開啟測試
Shutting down MySQL.. SUCCESS!
Starting MySQL.... SUCCESS!
mysql version
8.0.15
Variable_name Value
Created_tmp_tables 1
sysbench 1.1.0?(using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads:?40
Report intermediate results every 10?second(s)
Initializing random number?generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds:?40?tps:?1283.76?qps:?1283.76?(r/w/o: 1283.76/0.00/0.00) lat?(ms,95%): 41.10?err/s:?0.00?reconn/s:?0.00
[ 20s ] thds:?40?tps:?1286.43?qps:?1286.43?(r/w/o: 1286.43/0.00/0.00) lat?(ms,95%): 39.65?err/s:?0.00?reconn/s:?0.00
[ 30s ] thds:?40?tps:?1333.00?qps:?1333.00?(r/w/o: 1333.00/0.00/0.00) lat?(ms,95%): 37.56?err/s:?0.00?reconn/s:?0.00
[ 40s ] thds:?40?tps:?1280.79?qps:?1280.79?(r/w/o: 1280.79/0.00/0.00) lat?(ms,95%): 40.37?err/s:?0.00?reconn/s:?0.00
[ 50s ] thds:?40?tps:?1313.57?qps:?1313.57?(r/w/o: 1313.57/0.00/0.00) lat?(ms,95%): 38.94?err/s:?0.00?reconn/s:?0.00
[ 60s ] thds:?40?tps:?1222.95?qps:?1222.95?(r/w/o: 1222.95/0.00/0.00) lat?(ms,95%): 42.61?err/s:?0.00?reconn/s:?0.00
SQL statistics:
????queries performed:
????????read: 77248
????????write: 0
????????other: 0
????????total:???????????????????????????77248
????transactions:????????????????????????77248??(1286.95?per sec.)
????queries:?????????????????????????????77248??(1286.95?per sec.)
????ignored errors:??????????????????????0??????(0.00?per sec.)
????reconnects:??????????????????????????0??????(0.00?per sec.)
Throughput:
????events/s (eps): 1286.9486
????time elapsed: 60.0242s
????total number?of events:??????????????77248
Latency (ms):
?????????min: 1.88
?????????avg:???????????????????????????????????31.08
?????????max: 692.98
?????????95th?percentile: 40.37
?????????sum: 2400505.33
Threads fairness:
????events (avg/stddev): 1931.2000/51.17
????execution time (avg/stddev): 60.0126/0.00
mysql version
8.0.15
Variable_name Value
Created_tmp_tables 2cpu使用率接近100%
mysql8.0.15 40個并發(fā)線程，壓力測試60秒，訪問I_S.tables 平均qps為1.2k，并創(chuàng)建了1個臨時表!
結論
mysql8.0開始查詢I_S中表不會再從文件系統(tǒng)(FRM, TRG，OPT文件)和myisam、innodb系統(tǒng)表讀取元數(shù)據(jù)信息（8.0開始元數(shù)據(jù)信息統(tǒng)一存放在數(shù)據(jù)字典表中）
mysql8.0 訪問I_S.tables不會創(chuàng)建臨時表，這減少了內存暴漲的可能，但訪問I_S.tables的qps大約是mysql5.7.22的1/5，訪問速度沒有mysql5.7.22的快
mysql8.0 訪問I_S.tables 用戶空間cpu消耗沒有mysql5.7的高（沒有創(chuàng)建臨時表的緣故吧），但系統(tǒng)空間cpu消耗高于mysql5.7！
作者：jiaxin
出處：cnblogs.com/YangJiaXin/
評論
圖片
表情