从MySQL 5 开始 , 你可以看到多了一个系统数据库 information_schema information_schema 数据库跟 performance_schema 一样,都是 MySQL 自带的信息数据库。其中 performance_schema 用于性能分析,而 information_schema 用于存储数据库元数据(关于数据的数据),例如数据库名、表名、列的数据类型、访问权限等。information_schema 存贮了其他所有数据库的信息。 information_schema是一个虚拟数据库,并不物理存在。Mysql的INFORMATION_SCHEMA数据库包含了一些表和视图,提供了访问数据库元数据的方式,这台MySQL服务器上,到底有哪些数据库、各个数据库有哪些表,每张表的字段类型是什么,各个数据库要什么权限才能访问,等等信息都保存在information_schema表里面. 让我们来看看几个使用这个数据库的例子
查询慢查询
select CONCAT('KILL ',ID,';') from information_schema.processlist WHERE STATE = 'Sending data';
查询用户下的连接信息
select `USER`,COUNT(`USER`) AS CNT from information_schema.PROCESSLIST GROUP BY `USER` ORDER BY CNT DESC;
查询当前ip的连接信息
select substring_index(host,':',1) 'ip',count(*) from information_schema.processlist group by substring_index(host,':',1);
查看数据库下连接信息 (kill id)
select id,host,time,state,info from information_schema.processlist where COMMAND != 'Sleep' and DB='数据库名' and info not like '%information_schema.processlist%';
查看表下的索引信息
SELECT TABLE_NAME,INDEX_NAME,GROUP_CONCAT(DISTINCT COLUMN_NAME) index_column,INDEX_TYPE,CASE NON_UNIQUE WHEN 1 THEN '否' ELSE '是' END '唯一索引' FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = '数据库名' and INDEX_NAME!='PRIMARY' and TABLE_NAME='表名' GROUP BY TABLE_NAME,INDEX_NAME;
统计某个数据库中有多少张表
SELECT count(*) TABLES, table_schema FROM information_schema.TABLES where table_schema = '数据库名称' GROUP BY table_schema;
查看数据库各个表数据占用空间大小,各个表的数据条数
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') as data FROM information_schema.tables WHERE TABLE_SCHEMA='数据库名称' ORDER BY DATA_LENGTH+INDEX_LENGTH desc;
查看数据库中各个表字段个数
SELECT TABLE_NAME,count(TABLE_NAME) field_num from information_schema.COLUMNS where TABLE_SCHEMA='数据库名称' GROUP BY TABLE_NAME;
查询数据库dj214中表数据超过 1000 行的表
select concat(table_schema,'.',table_name) as table_name,table_rows from information_schema.tables where table_rows > 1000 and table_schema = 'dj214' order by table_rows desc;
查询数据库dj214 中所有没有主键的表
SELECT CONCAT(t.table_schema,".",t.table_name) as table_name
FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS tc
ON t.table_schema = tc.table_schema
AND t.table_name = tc.table_name
AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_name IS NULL
AND t.table_type = 'BASE TABLE' AND t.table_schema = 'dj214' ;
查询所有数据库中10张最大表
SELECT concat(table_schema,'.',table_name) '表名称', concat(round(data_length/(1024*1024),2),'M') '表大小'
FROM information_schema.TABLES ORDER BY data_length DESC LIMIT 10;
查看MYSQL数据库下所有的数据库
SELECT SCHEMA_NAME AS 'database' FROM INFORMATION_SCHEMA.SCHEMATA LIMIT 0 , 30
列出指定数据库中的所有表名称
SELECT table_name, table_type, engine FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'dj214' AND table_type='BASE TABLE'
SELECT table_name,table_type,engine FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db' and table_name like 'bms_%' AND table_type='BASE TABLE'
列出指定数据库下指定表的表结构
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'systemlog' AND table_schema = 'dj214'
SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'systemlog'
SELECT table_name FROM information_schema.columns
WHERE table_name = 'systemlog' AND table_schema = 'dj214' AND column_name = 'operator'
一段MYSQL存储过程 [ 删除指定库中所有的空表 ]
begin
/*局部变量的定义,默认值为空 */
declare tmpName varchar(200) default '' ;
/*定义游标*/
DECLARE reslutList Cursor FOR select table_name from information_schema.tables where table_rows <1 and table_schema = 'sz8_news' order by table_rows desc;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;
OPEN reslutList;/*打开游标*/
FETCH reslutList into tmpname; -- 取数据
/* 循环体 */
WHILE ( tmpname is not null) DO
set @sql = concat('drop table sz8_news.',tmpname,";");
PREPARE stmt1 FROM @sql ;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
/*游标向下走一步*/
FETCH reslutList INTO tmpname;
END WHILE;
CLOSE reslutList; /*关闭游标*/
end
一段 MYSQL存储过程 [ 删除指定库下所有表中的空列,即表中的任何一条记录该列都没有值 ]
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE cTbl varchar(64);
DECLARE cCol varchar(64);
DECLARE cur1 CURSOR FOR
select TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='sz8_news' and IS_NULLABLE='YES' order by TABLE_NAME;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
set @sqlDrop='';
OPEN cur1;
FETCH cur1 INTO cTbl, cCol;/*得到表名及列名*/
WHILE done = 0 DO
set @x=0;
/*主要改进了这里把空值也纳入判断条件中去,即如果字段为null或空*/
set @sql=concat('select 1 into @x from ',cTbl,' where ',cCol,' is not null and ',cCol,' !="" limit 1');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
if @x=0 then
set @sqlDrop=concat('alter table `',cTbl,'` drop COLUMN`',cCol,'`;');
PREPARE stmt1 FROM @sqlDrop;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
end if ;
set done = 0;
FETCH cur1 INTO cTbl, cCol;
END WHILE;
CLOSE cur1;
END
定期清理释放空间
1.查询数据库空间碎片:
select table_name,data_free,engine from information_schema.tables where table_schema='yourdatabase' and data_free>0;
2.对数据表优化:
optimize table `table_name`;
每当MySQL从你的列表中删除了一行内容,该段空间就会被留空。而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大。
当MySQL对数据进行扫描时,它扫描的对象实际是列表的容量需求上限,也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作,MySQL将尝试利用这些留空的区域,但仍然无法将其彻底占用。
OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表。
mysql中count(*)和information_schema.tables结果值不同
mysql官方文档说:针对 MyISAM引擎的表,行数是确定的值;
但针对InnoDB引擎来说(我们平常的库都是用这个引擎),行数就是个大概值,误差最大可能会差距在40%-50%的,所以还是用count(*)统计其真实行数。
mysql.user
MySQL是通过权限表来控制用户对数据库访问的,权限表存放在mysql数据库中,主要的权限表有以下几个:user,db,host,table_priv,columns_priv和procs_priv,先带你了解的是user表。
mysql中所有的用户都是存放在user表中的,这些字段可以分为4类:(mysql 5.7为例)用户列
.权限列
.安全列
.资源控制列
.
用户列
- Host:主机名,双主键之一,值为%时表示匹配所有主机。
- User:用户名,双主键之一。Password:密码名。
权限列
权限列决定了用户的权限,描述了用户在全局范围内允许对数据库和数据库表进行的操作,字段类型都是枚举Enum,值只能是Y或N,Y表示有权限,N表示没有权限。
- Select_priv 确定用户是否可以通过SELECT命令选择数据
- Insert_priv 确定用户是否可以通过INSERT命令插入数据
- Update_priv 确定用户是否可以通过UPDATE命令修改现有数据
- Delete_priv 确定用户是否可以通过DELETE命令删除现有数据
- Create_priv 确定用户是否可以创建新的数据库和表
- Drop_priv 确定用户是否可以删除现有数据库和表、视图的权限,包括truncate table命令
- Reload_priv 确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令,包括日志、权限、主机、查询和表,拥有该权限的用户可以使用FLUSH语句
- Shutdown_priv 确定用户是否可以关闭MySQL服务器。在将此权限提供给root账户之外的任何用户时,都应当非常谨慎
- Process_priv 确定用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程
- File_priv 确定用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令
- Grant_priv 确定用户是否可以将已经授予给该用户自己的权限再授予其他用户
- References_priv 目前只是某些未来功能的占位符;现在没有作用
- Index_priv 确定用户是否可以创建和删除表索引
- Alter_priv 确定用户是否可以重命名和修改表结构
- Show_db_priv 确定用户是否可以查看服务器上所有数据库的名字,包括用户拥有足够访问权限的数据库
- Super_priv 确定用户是否可以执行某些强大的管理功能,例如通过KILL命令删除用户进程,使用SET GLOBAL修改全局MySQL变量,执行关于复制和日志的各种命令
- Create_tmp_table_priv 确定用户是否可以创建临时表
- Lock_tables_priv 确定用户是否可以使用LOCK TABLES命令阻止对表的访问/修改
- Execute_priv 确定用户是否可以执行存储过程
- Repl_slave_priv 确定用户是否可以读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中,有利于主机和客户机之间的通信
- Repl_client_priv 确定用户是否可以确定复制从服务器和主服务器的位置
- Create_view_priv 确定用户是否可以创建视图
- Show_view_priv 确定用户是否可以查看视图或了解视图如何执行
- Create_routine_priv 确定用户是否可以更改或放弃存储过程和函数
- Alter_routine_priv 确定用户是否可以修改或删除存储函数及函数
- Create_user_priv 确定用户是否可以执行CREATE USER命令,这个命令用于创建新的MySQL账户
- Event_priv 确定用户能否创建、修改和删除事件
- Trigger_priv 确定用户能否创建和删除触发器
- Create_role_priv 允许创建角色
- Drop_role_priv 允许删除角色
安全列
资源控制列
查看demo 用户在数据库call下有没有drop权限:
select Drop_priv from mysql.db where user = 'demo' and Db='call';
user里面是登录到mysql数据库用户及其拥有的权限,db里面是针对数据库某个用户拥有的权限。mysql usage权限就是空权限,默认create user的权限,只能连库,啥也不能干
GRANT USAGE ON *.* TO `user`@`%`
新建数据库后使用的是user权限