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 表和大小？的详细内容。