MySQL的INFORMATION_SCHEMA使用_information schema使用案例

--查看创建的索引的CARDINALITY比率
--通常cardinality达到表数据的10%左右建索引会有意义
--如果是一个组合索引，索引第一位的cardinality表示第一个列的cardinality大小，第二列表示第一列和第二列共同的cardinality值
SELECT
    T1.TABLE_SCHEMA,
    T1.TABLE_NAME,
    T2.INDEX_NAME,
    ROUND(T2.CARDINALITY / T1.TABLE_ROWS * 100, 2) AS RATE
FROM
    INFORMATION_SCHEMA.TABLES T1,
    INFORMATION_SCHEMA.STATISTICS T2
WHERE
    T1.TABLE_SCHEMA = T2.TABLE_SCHEMA
        AND T1.TABLE_NAME = T2.TABLE_NAME
        AND T2.SEQ_IN_INDEX = (SELECT
            MIN(T3.SEQ_IN_INDEX)
        FROM
            INFORMATION_SCHEMA.STATISTICS T3
        WHERE
                T2.TABLE_NAME = T3.TABLE_NAME
                AND T2.TABLE_SCHEMA = T3.TABLE_SCHEMA
                AND T2.INDEX_NAME = T3.INDEX_NAME)
AND T1.TABLE_SCHEMA NOT IN ('MYSQL','PERFORMANCE_SCHEMA','INFORMATION_SCHEMA','SYS')
AND T1.TABLE_ROWS >=100
ORDER BY RATE;
 
 
 
 
--查看锁阻塞
 
-- 查看锁的SQL
SELECT
    t3.trx_id waiting_trx_id,
    t3.trx_mysql_thread_id waiting_thread,
    t3.trx_query waiting_query,
    t2.trx_id blocking_trx_id,
    t2.trx_mysql_thread_id blocking_thread,
    t2.trx_query blocking_query
FROM
    information_schema.innodb_lock_waits t1,
    information_schema.innodb_trx t2,
    information_schema.innodb_trx t3
WHERE
    t1.blocking_trx_id = t2.trx_id
        AND t1.requesting_trx_id = t3.trx_id;
 
 
 
 
--查询出哪些表不是InnoDB引擎的
 
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_TYPE,
    ENGINE,
    CREATE_TIME,
    UPDATE_TIME,
    TABLE_COLLATION
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA NOT IN ('information_schema' , 'mysql', 'performance_schema', 'sys')
        AND ENGINE <> 'InnoDB';
 
 
 
--生成修改存储引擎的语句
 
SELECT
    -- TABLE_SCHEMA,
    -- TABLE_NAME,
    -- TABLE_TYPE,
    -- ENGINE,
    -- CREATE_TIME,
    -- UPDATE_TIME,
    -- TABLE_COLLATION,
     CONCAT('alter table ', TABLE_SCHEMA,'.',TABLE_NAME, ' engine=InnoDB;') AS alter_sql
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA NOT IN
       ('information_schema', 'mysql', 'performance_schema', 'sys')
   AND ENGINE <> 'InnoDB';
 
 
 
 
--  查看指定数据库的表信息
 
SET @table_schema='employees';
SELECT
    table_name,
    table_type,
    engine,
    table_rows,
    avg_row_length,
    data_length,
    index_length,
    table_collation,
    create_time
FROM
    information_schema.tables
WHERE
    table_schema = @table_schema
ORDER BY table_name;
 
 
 
-- 查看会话连接信息
 
SELECT
    THREAD_ID,
    name,
    type,
    PROCESSLIST_ID,
    PROCESSLIST_USER AS user,
    PROCESSLIST_HOST AS host,
    PROCESSLIST_DB AS db,
    PROCESSLIST_COMMAND AS cmd,
    PROCESSLIST_TIME AS time,
    PROCESSLIST_STATE AS state,
    PROCESSLIST_INFO AS info,
    CONNECTION_TYPE AS type,
    THREAD_OS_ID AS os_id
FROM
    performance_schema.threads
WHERE
    type = 'FOREGROUND'
ORDER BY THREAD_ID;
 
 
 
-- CHARACTER_SETS 查看数据库支持的字符集
SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS
WHERE CHARACTER_SET_NAME LIKE 'utf%';
 
SHOW CHARACTER SET LIKE 'utf%';
 
 
-- COLLATIONS  字符序
-- 用于指定数据集如何排序，以及字符串的比对规则
SELECT * FROM INFORMATION_SCHEMA.COLLATIONS
WHERE COLLATION_NAME LIKE 'utf%';
 
SHOW COLLATION LIKE 'utf%';
-- 查看表结构定义信息
 
SELECT
    table_name,
    COLUMN_NAME,
    ordinal_position,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    column_type,
    column_key,
    character_set_name,
    collation_name
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = 'employees'
        AND table_schema = 'employees';
show columns from employees from employees;
 
 
desc employeees.employees;
 
 
 
 
-- 查看支持的引擎
SELECT *  FROM INFORMATION_SCHEMA.ENGINES;
show ENGINES;
 
 
-- 查看数据库的数据文件信息
 
SELECT
    FILE_ID,
    FILE_NAME,
    FILE_TYPE,
    TABLESPACE_NAME,
    FREE_EXTENTS,
    TOTAL_EXTENTS,
    ((TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE) / 1024 / 1024 AS MB_used,
    EXTENT_SIZE,
    INITIAL_SIZE,
    MAXIMUM_SIZE,
    AUTOEXTEND_SIZE,
    DATA_FREE,
    STATUS,
    ENGINE
FROM
    INFORMATION_SCHEMA.FILES;
 
 
 
-- 查看指定表的约束
 
SELECT
    constraint_schema,
    table_name,
    constraint_name,
    column_name,
    ordinal_position,
    CONCAT(table_name,
            '.',
            column_name,
            ' -> ',
            referenced_table_name,
            '.',
            referenced_column_name) AS list_of_fks
FROM
    information_schema.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_SCHEMA = 'employees'
        AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME , COLUMN_NAME;
 
 
 
-- 查看指定分区表信息
 
SELECT
    TABLE_SCHEMA,
    table_name,
    partition_name,
    subpartition_name sub_par,
    partition_ordinal_position par_position,
    partition_method method,
    partition_expression expression,
    partition_description description,
    table_rows
FROM
    information_schema.PARTITIONS
WHERE
    table_schema = 'test'
        AND table_name = 't';
 
 
 
-- 查看支持的插件
 
SELECT
  PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE,
  PLUGIN_LIBRARY, PLUGIN_LICENSE
FROM INFORMATION_SCHEMA.PLUGINS;
 
SHOW PLUGINS;
 
 
 
-- 查看数据库连接信息
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
 
SHOW FULL PROCESSLIST;
 
 
-- 查看数据库中的存储过程、函数等
 
SELECT
    ROUTINE_SCHEMA,
    routine_name,
    ROUTINE_TYPE,
    data_type,
    routine_body,
    routine_definition,
    routine_comment
FROM
    INFORMATION_SCHEMA.ROUTINES
WHERE
    ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_SCHEMA="employees";
 
 
 
-- 查看存在的数据库及字符集信息
 
SELECT
    SCHEMA_NAME,
    DEFAULT_CHARACTER_SET_NAME,
    DEFAULT_COLLATION_NAME
FROM
    INFORMATION_SCHEMA.SCHEMATA;
 
SHOW DATABASES;
 
 
 
-- 查看索引信息
 
SELECT
    table_schema,
    table_name,
    index_name,
    COLUMN_NAME,
    COLLATION,
    CARDINALITY,
    index_type
FROM
    INFORMATION_SCHEMA.STATISTICS
WHERE
    table_name = 'employees'
        AND table_schema = 'employees';
 
SHOW INDEX FROM employees FROM employees;
 
 
 
-- 查看数据库大小
 
SELECT
    table_schema 'database',
    CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024),
                    2),
            'M') size
FROM
    information_schema.TABLES
WHERE
    ENGINE in ('MyISAM','InnoDB')
GROUP BY table_schema;
 
 
 
-- 查看表大小
 
SELECT
CONCAT(table_schema, '.', table_name) table_name,
CONCAT(ROUND(data_length / (1024 * 1024), 2),
'M') data_length,
CONCAT(ROUND(index_length / (1024 * 1024), 2),
'M') index_length,
CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024),
2),
'M') total_size,
engine
FROM
information_schema.TABLES
WHERE
table_schema NOT IN ('information_schema' , 'performance_schema', 'sys', 'mysql')
ORDER BY data_length DESC;
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342