mysql 日常检查

MySQL数据库实例中存储空间的概况
SELECT a.schema_name db_name,
IFNULL(ROUND((SUM(b.data_length)+SUM(b.index_length))/1024/1024,2),0.00)
total_size_mb,
IFNULL(ROUND(((SUM(b.data_length)+SUM(b.index_length))-
SUM(b.data_free))/1024/1024,2),0.00) data_used_mb,
IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00) data_free_mb,
IFNULL(ROUND((((SUM(b.data_length)+SUM(b.index_length))-SUM(b.data_free))/
((SUM(b.data_length)+SUM(b.index_length)))*100),2),0) pct_used,
COUNT(table_name) tables
FROM information_schema.schemata a
LEFT JOIN information_schema.tables b ON a.schema_name = b.table_schema
WHERE a.schema_name != 'information_schema'
GROUP BY a.schema_name
ORDER BY 1;

 

查看opdb各表数据量

mysql> select table_name,table_rows,round(data_length/1024/1024,0) data_M,round(index_length/1024/1024,0) idx_M,round((data_length+index_length)/1024/1024,0) all_M from information_schema.tables where table_schema='opdb' and table_type='base table' order by data_length+index_length;

如有单表的数据量大于1G，应注意

数据库字符集
select * from information_schema.schemata  limit 30;
表属性
select * from information_schema.tables
索引信息
select TABLE_SCHEMA,TABLE_NAME,INDEX_SCHEMA,INDEX_NAME,INDEX_TYPE from information_schema.statistics limit 20;

MySQL何时使用索引

对一个键码使用>, >=, =, <, <=, IF NULL和BETWEEN
SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;
SELECT * FROM table_name WHERE key_part1 IS NULL;

当使用不以通配符开始的LIKE
SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'

在进行联结时从另一个表中提取行时
SELECT * from t1,t2 where t1.col=t2.key_part

找出指定索引的MAX()或MIN()值
SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10

一个键码的前缀使用ORDER BY或GROUP BY
SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3

在所有用在查询中的列是键码的一部分时间
SELECT key_part3 FROM table_name WHERE key_part1=1