列出按大小排序的 MySQL 表和大小？

您可以在 information_schema.tables 的帮助下完成此操作。语法如下 –

SELECT TABLE_NAME, table_rows, data_length, index_length,round(((data_length + index_length) / 1024 / 1024),2) "MB Size"FROM information_schema.TABLES WHERE table_schema = "yourDatabaseName"ORDER BY (data_length + index_length) ASC;

为了理解上述语法，让我们为任何数据库实现它。在这里，我使用数据库 TEST。让我们看看数据库 TEST 的查询。

mysql> SELECT TABLE_NAME, table_rows, data_length, index_length,-> round(((data_length + index_length) / 1024 / 1024),2) "MB Size"-> FROM information_schema.TABLES WHERE table_schema = "test"-> ORDER BY (data_length + index_length) ASC;

以下是显示按大小排序的表格的输出。

+------------------------------------+------------+-------------+--------------+---------+| TABLE_NAME                         | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | MB Size |+------------------------------------+------------+-------------+--------------+---------+| empinfoview                        | 0          | 0           | 0            | 0.00    || lookuptable                        | 0          | 0           | 0            | 0.00    || view_student                       | 0          | 0           | 0            | 0.00    || empidandempname_view               | 0          | 0           | 0            | 0.00    || viewemployeeidandemployeename      | 0          | 0           | 0            | 0.00    || customers                          | 0          | 0           | 1024         | 0.00    || addingcurrencysymboldemo           | 4          | 16384       | 0            | 0.02    || allrecordswithactive               | 6          | 16384       | 0            | 0.02    || autostoredatetime                  | 0          | 16384       | 0            | 0.02    || bookdatedemo                       | 2          | 16384       | 0            | 0.02    || changecurrentautoincrementvalue    | 6          | 16384       | 0            | 0.02    || conditionalinsertdemo              | 4          | 16384       | 0            | 0.02    || datefromtimestamp                  | 4          | 16384       | 0            | 0.02    || decrementdemo                      | 6          | 16384       | 0            | 0.02    || differenceinhours                  | 2          | 16384       | 0            | 0.02    || fetchrowlasthourdemo               | 6          | 16384       | 0            | 0.02    || getfirstdayofmonth                 | 4          | 16384       | 0            | 0.02    || increaseanddecreasedemo            | 2          | 16384       | 0            | 0.02    || insertingnull                      | 0          | 16384       | 0            | 0.02    || isnulldemo                         | 6          | 16384       | 0            | 0.02    || newstable                          | 6          | 16384       | 0            | 0.02    || nthrecorddemo                      | 6          | 16384       | 0            | 0.02    || orderbyrandname                    | 8          | 16384       | 0            | 0.02    || pricedemo                          | 4          | 16384       | 0            | 0.02    || rowpositiondemo                    | 4          | 16384       | 0            | 0.02    || selectdataonyearandmonthdemo       | 4          | 16384       | 0            | 0.02    || sortcolumnzeroatlastdemo           | 6          | 16384       | 0            | 0.02    || studentdemo                        | 4          | 16384       | 0            | 0.02    || sumdemooncolumns                   | 4          | 16384       | 0            | 0.02    || tinyintdemo                        | 0          | 16384       | 0            | 0.02    || unixtime                           | 2          | 16384       | 0            | 0.02    || uppertabledemo                     | 4          | 16384       | 0            | 0.02    || wheredemo                          | 4          | 16384       | 0            | 0.02    || addingdaysdemo                     | 8          | 16384       | 0            | 0.02    || allrows                            | 4          | 16384       | 0            | 0.02    || averageontime                      | 4          | 16384       | 0            | 0.02    || booldemo                           | 0          | 16384       | 0            | 0.02    || changeenginetabledemo              | 0          | 16384       | 0            | 0.02    || databystringlength                 | 4          | 16384       | 0            | 0.02    || dateinsertdemo                     | 2          | 16384       | 0            | 0.02    || defaultdemo                        | 0          | 16384       | 0            | 0.02    || differenceinseconds                | 4          | 16384       | 0            | 0.02    || employee                           | 2          | 16384       | 0            | 0.02    || findlowercasevalue                 | 4          | 16384       | 0            | 0.02    || gettinglast5characters             | 4          | 16384       | 0            | 0.02    || increasevarchardemo                | 0          | 16384       | 0            | 0.02    || insertnulldemo                     | 3          | 16384       | 0            | 0.02    || jsonformatdemo                     | 4          | 16384       | 0            | 0.02    || lowercasedemo                      | 2          | 16384       | 0            | 0.02    || newtableduplicate                  | 3          | 16384       | 0            | 0.02    || nullandemptydemo                   | 7          | 16384       | 0            | 0.02    || orderbyrelevance                   | 4          | 16384       | 0            | 0.02    || primarykey1000demo                 | 4          | 16384       | 0            | 0.02    || rowwithsamevalue                   | 4          | 16384       | 0            | 0.02    || selectdistincttwocolumns           | 8          | 16384       | 0            | 0.02    |  | sortingstringdemo                  | 5          | 16384       | 0            | 0.02    || studentinformation                 | 5          | 16384       | 0            | 0.02    || sumwithifcondition                 | 6          | 16384       | 0            | 0.02    || toggledemo                         | 6          | 16384       | 0            | 0.02    || unixtimedemo                       | 0          | 16384       | 0            | 0.02    || userdateformat                     | 4          | 16384       | 0            | 0.02    || wholewordmatchdemo                 | 2          | 16384       | 0            | 0.02    || addoneday                          | 2          | 16384       | 0            | 0.02    || appendingdatademo                  | 2          | 16384       | 0            | 0.02    || base64demo                         | 2          | 16384       | 0            | 0.02    || booleandemo                        | 0          | 16384       | 0            | 0.02    || charsetdemo                        | 0          | 16384       | 0            | 0.02    || convertdatetimetodate              | 4          | 16384       | 0            | 0.02    || dateandtimetotimestamp             | 4          | 16384       | 0            | 0.02    || daterange                          | 8          | 16384       | 0            | 0.02    || deleteallfromtable                 | 0          | 16384       | 0            | 0.02    || differencetimestamp                | 2          | 16384       | 0            | 0.02    || employeedesignation                | 2          | 16384       | 0            | 0.02    || firsttabledemo                     | 2          | 16384       | 0            | 0.02    || gmailsignin                        | 4          | 16384       | 0            | 0.02    || incrementanddecrementvalue         | 2          | 16384       | 0            | 0.02    || insertwithmultipleandsigle         | 21         | 16384       | 0            | 0.02    || keywordsearchdemo                  | 6          | 16384       | 0            | 0.02    || maxlengthfunctiondemo              | 4          | 16384       | 0            | 0.02    || notempty                           | 0          | 16384       | 0            | 0.02    || nullatbottom                       | 6          | 16384       | 0            | 0.02    || orderindemo                        | 6          | 16384       | 0            | 0.02    || proceduredemotable                 | 2          | 16384       | 0            | 0.02    || safedeletedemo                     | 4          | 16384       | 0            | 0.02    || selectdomainnameonly               | 4          | 16384       | 0            | 0.02    || startautoincrement                 | 3          | 16384       | 0            | 0.02    || studentmodifytabledemo             | 3          | 16384       | 0            | 0.02    || tablealiasdemo                     | 4          | 16384       | 0            | 0.02    || toogledemo                         | 6          | 16384       | 0            | 0.02    || updatealldemo                      | 2          | 16384       | 0            | 0.02    || userrole                           | 4          | 16384       | 0            | 0.02    || yesterdaydatedemo                  | 2          | 16384       | 0            | 0.02    || agecalculatesdemo                  | 4          | 16384       | 0            | 0.02    || autoincrementdemo                  | 6          | 16384       | 0            | 0.02    |  | betweendatedemo                    | 6          | 16384       | 0            | 0.02    || casedemo                           | 2          | 16384       | 0            | 0.02    || columnwithspace                    | 4          | 16384       | 0            | 0.02    || countingdemo                       | 4          | 16384       | 0            | 0.02    || dateasstringdemo                   | 2          | 16384       | 0            | 0.02    || datesofoneweek                     | 4          | 16384       | 0            | 0.02    || deletedemo                         | 5          | 16384       | 0            | 0.02    || differentrows                      | 4          | 16384       | 0            | 0.02    || employeeinformation                | 6          | 16384       | 0            | 0.02    || firsttablemaxvalue                 | 6          | 16384       | 0            | 0.02    || groupbytwofieldsdemo               | 6          | 16384       | 0            | 0.02    || incrementby1                       | 6          | 16384       | 0            | 0.02    || int11demo                          | 2          | 16384       | 0            | 0.02    || lasthourrecords                    | 4          | 16384       | 0            | 0.02    || monthdemo                          | 12         | 16384       | 0            | 0.02    || notequaldemo                       | 7          | 16384       | 0            | 0.02    || onlyhourdemo                       | 3          | 16384       | 0            | 0.02    || originaltable                      | 4          | 16384       | 0            | 0.02    || queryresultdemo                    | 4          | 16384       | 0            | 0.02    || searchdemo                         | 6          | 16384       | 0            | 0.02    || sha256demo                         | 0          | 16384       | 0            | 0.02    || storedproctable                    | 2          | 16384       | 0            | 0.02    || studenttable                       | 3          | 16384       | 0            | 0.02    || tableview                          | 0          | 16384       | 0            | 0.02    || triggerdemo1                       | 3          | 16384       | 0            | 0.02    || updatedate                         | 4          | 16384       | 0            | 0.02    || uservariable                       | 2          | 16384       | 0            | 0.02    || zipcodepadwithzerodemo             | 4          | 16384       | 0            | 0.02    || _student_trackerdemo               | 0          | 16384       | 0            | 0.02    || aliasdemo                          | 2          | 16384       | 0            | 0.02    || autoincrementdemo1                 | 0          | 16384       | 0            | 0.02    || bigintandintdemo                   | 0          | 16384       | 0            | 0.02    || caseupdatedemo                     | 4          | 16384       | 0            | 0.02    || comparingdate                      | 4          | 16384       | 0            | 0.02    || creatingtable                      | 7          | 16384       | 0            | 0.02    || datecreatedemo                     | 4          | 16384       | 0            | 0.02    || datetimedemo                       | 6          | 16384       | 0            | 0.02    || deletemanyrows                     | 2          | 16384       | 0            | 0.02    || display                            | 2          | 16384       | 0            | 0.02    || employeetable                      | 2          | 16384       | 0            | 0.02    || firstworddemo                      | 2          | 16384       | 0            | 0.02    || groupmonthandyeardemo              | 4          | 16384       | 0            | 0.02    || incrementcounterdemo               | 6          | 16384       | 0            | 0.02    || intcurrencydemo                    | 5          | 16384       | 0            | 0.02    || lasttwocharacters                  | 2          | 16384       | 0            | 0.02    || moviecollectiondemo                | 0          | 16384       | 0            | 0.02    || notequalsdemo                      | 5          | 16384       | 0            | 0.02    || onlymonthandyear                   | 4          | 16384       | 0            | 0.02    || parsedatedemo                      | 4          | 16384       | 0            | 0.02    || renameviewdemo                     | 0          | 16384       | 0            | 0.02    || searchtextdemo                     | 0          | 16384       | 0            | 0.02    || simulatearraydemo                  | 6          | 16384       | 0            | 0.02    || stringreplacedemo                  | 2          | 16384       | 0            | 0.02    || stuedntinformation                 | 0          | 16384       | 0            | 0.02    || tblemployee                        | 0          | 16384       | 0            | 0.02    || triggerdemo2                       | 0          | 16384       | 0            | 0.02    || updatenumber1to3                   | 3          | 16384       | 0            | 0.02    || uservariableinlike                 | 5          | 16384       | 0            | 0.02    || _studenttrackerdemo                | 0          | 16384       | 0            | 0.02    || allcharacterbeforespace            | 4          | 16384       | 0            | 0.02    || autoincrementedprimary             | 4          | 16384       | 0            | 0.02    || bigintdemo                         | 0          | 16384       | 0            | 0.02    || changecellsdata                    | 4          | 16384       | 0            | 0.02    || concatenatetwocolumnsdemo          | 4          | 16384       | 0            | 0.02    || creatingtableusingviewstudent      | 0          | 16384       | 0            | 0.02    || dateequaltoday                     | 6          | 16384       | 0            | 0.02    || dayofweekdemo                      | 6          | 16384       | 0            | 0.02    || demo                               | 2          | 16384       | 0            | 0.02    || displayint                         | 5          | 16384       | 0            | 0.02    || rowexistdemo                       | 4          | 16384       | 0            | 0.02    || selectconcat                       | 5          | 16384       | 0            | 0.02    || sortbydateandtime                  | 4          | 16384       | 0            | 0.02    || sumcasedemo                        | 6          | 16384       | 0            | 0.02    || timetoseconddemo                   | 0          | 16384       | 0            | 0.02    || union_table2                       | 3          | 16384       | 0            | 0.02    || updatewithifcondition              | 2          | 16384       | 0            | 0.02    || constraintdemo                     | 0          | 16384       | 16384        | 0.03    || insertignoredemo                   | 2          | 16384       | 16384        | 0.03    || student                            | 2          | 16384       | 32768        | 0.05    |+------------------------------------+------------+-------------+--------------+---------+240 rows in set (22.56 sec)

以上就是列出按大小排序的 MySQL 表和大小？的详细内容，更多请关注创想鸟其它相关文章！