Changes between Version 7 and Version 8 of jazz/MySQL

Timestamp:

Feb 26, 2018, 2:27:20 PM (7 years ago)

Author:

jazz

Comment:

--

jazz/MySQL

@@ -95 +95 @@
 mysql> RENAME TABLE old_table TO tmp_table, new_table TO old_table, tmp_table TO new_table;
 }}}
+* 顯示 InnoDB 跟 MyISAM 各自儲存了多少資料量
+{{{
+mysql> SELECT IFNULL(B.engine,'Total') "Storage Engine",
+CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
+SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
+FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
+SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
+FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
+SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
+FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
+SUM(data_length+index_length) TSize FROM information_schema.tables
+WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
+AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
+(SELECT 3 pw) A ORDER BY TSize;
+}}}
 
 == 踩到的雷 ==