合 MySQL常用SQL语句
MySQL表结构
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- MySQL表结构 SELECT a.TABLE_NAME "表", a.COLUMN_NAME "列", a.COLUMN_TYPE "类型", a.COLUMN_DEFAULT "默认值", a.IS_NULLABLE "是否为空", a.CHARACTER_SET_NAME "表字符集", a.COLLATION_NAME "校验字符集", CONCAT(a.COLUMN_COMMENT,a.COLUMN_KEY, a.EXTRA) "列备注", b.TABLE_COMMENT "表备注" , b.ENGINE "引擎" FROM information_schema.COLUMNS a,information_schema.TABLES b WHERE a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_SCHEMA='lhrdb' AND a.TABLE_NAME=b.TABLE_NAME; |
MySQL索引大小
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | select iis.database_name, iis.table_name, iis.index_name, round((iis.stat_value@@innodb_page_size)/1024/1024, 2) SizeMB, -- round(((100/(SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = iis.table_name and t.TABLE_SCHEMA = iis.database_name))(stat_value*@@innodb_page_size)), 2) Percentage, s.NON_UNIQUE, s.INDEX_TYPE, GROUP_CONCAT(s.COLUMN_NAME order by SEQ_IN_INDEX) COLUMN_NAME from (select * from mysql.innodb_index_stats WHERE index_name not in ('PRIMARY','GEN_CLUST_INDEX') and stat_name='size' order by (stat_value@@innodb_page_size) desc limit 10 ) iis left join INFORMATION_SCHEMA.STATISTICS s on (iis.database_name=s.TABLE_SCHEMA and iis.table_name=s.TABLE_NAME and iis.index_name=s.INDEX_NAME) GROUP BY iis.database_name,iis.TABLE_NAME,iis.INDEX_NAME,(iis.stat_value@@innodb_page_size),s.NON_UNIQUE,s.INDEX_TYPE order by (stat_value*@@innodb_page_size) desc; |
MySQL索引信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | -- MySQL索引信息 SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, INDEX_TYPE, CONCAT(COMMENT,INDEX_COMMENT) INDEX_COMMENT FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'lhrdb' ORDER BY TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX; -- MySQL索引信息 方便生成alter语句 SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, MAX(a.NON_UNIQUE) NON_UNIQUE, MAX(a.INDEX_TYPE) INDEX_TYPE, MAX(a.INDEX_COMMENT) INDEX_COMMENT, GROUP_CONCAT(a.COLUMN_NAME order by SEQ_IN_INDEX) COLUMN_NAME FROM (SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, INDEX_TYPE, CONCAT(COMMENT,INDEX_COMMENT) INDEX_COMMENT FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'lhrdb' ORDER BY TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX) a GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.INDEX_NAME; |