列出按大小排序的 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 表和大小？的詳細內容。更多資訊請關注PHP中文網其他相關文章！