邢栋博客
邢栋博客,Action博客,记录工作和生活中的点点滴滴
如何查询MySQL数据库所占用的空间大小
标签:
mysql
====进入终端=====
mysql -uroot -p
show databases;
use information_schema;
====1.查看数据库总大小====
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from tables;
====2.查询某个数据库的大小====
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from tables where TABLE_SCHEMA='wordpress';
====3.查询某个数据库下某个表的大小====
select concat(round(DATA_LENGTH/1024/1024,2),'MB') as data from tables where TABLE_SCHEMA='wordpress' and TABLE_NAME='wp_options';
mysql -uroot -p
show databases;
use information_schema;
====1.查看数据库总大小====
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from tables;
====2.查询某个数据库的大小====
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from tables where TABLE_SCHEMA='wordpress';
====3.查询某个数据库下某个表的大小====
select concat(round(DATA_LENGTH/1024/1024,2),'MB') as data from tables where TABLE_SCHEMA='wordpress' and TABLE_NAME='wp_options';
sql日期截取以及数量累加
创建表+插入测试数据
CREATE TABLE `post` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(30) NOT NULL, `status` tinyint(1) NOT NULL, `cdate` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; insert into post (title,status,cdate)values('测试1',1,'2019-08-01 10:10:10'); insert into post (title,status,cdate)values('测试1',1,'2019-08-02 11:10:20'); insert into post (title,status,cdate)values('测试1',1,'2019-08-01 12:10:20'); insert into post (title,status,cdate)values('测试1',1,'2019-08-03 13:10:20'); insert into post (title,status,cdate)values('测试1',1,'2019-08-02 14:10:20'); insert into post (title,status,cdate)values('测试1',1,'2019-08-03 15:10:20'); insert into post (title,status,cdate)values('测试1',1,'2019-08-03 16:10:20');
sql练习
获取某个日期的2019-08-03 select * from post where left(cdate,10) ='2019-08-03'; //截取 select * from post where substring(cdate,1,10) ='2019-08-03';//截取 select * from post where DATE_FORMAT(cdate, '%Y-%m-%d')='2019-08-03';//格式化时间 //按照日期分组 select count(*),substring(cdate,1,10) from post group by substring(cdate,1,10) order by count(*) desc; //按照日期分组,优化下 select count(*) as count,substring(cdate,1,10) as cdate from post group by substring(cdate,1,10) order by count desc; //累计之间的数量 set @tmptotal := 0;select cdate,(@tmptotal :=@tmptotal+count) as total from (select count(*) as count,substring(cdate,1,10) as cdate from post group by substring(cdate,1,10) order by cdate) as tmpdatas group by cdate; //累计之间的数量,大于10的日期 set @tmptotal := 0;select cdate,(@tmptotal :=@tmptotal+count) as total from (select count(*) as count,substring(cdate,1,10) as cdate from post group by substring(cdate,1,10) order by cdate) as tmpdatas group by cdate having total > 10;
mysql锁简记
锁分类
1.读锁(共享锁,读操作不受影响,别的会话有插入操作会处于阻塞状态) 写锁(排他锁,在写操作完成之前,会阻断其他读和写操作)
2.表锁和行级锁
======1.表锁 偏读========
myisam存储引擎,开销小,加锁快,锁力度大,发生锁冲突的概率最高,并发度最低
//手动加锁
lock table 表名称 read(write),表名称2 read(write),其他;
//查看表上加过的锁
show open tables;
//删除表锁
unlock tables;
//分析表锁定
show status like 'table%';
Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 112 |//产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1
| Table_locks_waited | 0 | //出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值高则说明存在着较严重的表级锁争用情况。
| Table_open_cache_hits | 0 |
| Table_open_cache_misses | 6 |//在打开表时,表缓存不存在的次数。
| Table_open_cache_overflows | 0 |
======2.行锁 偏写========
行锁偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁,锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
InnoDB与MYISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。
事务特性:原子性Atomicity,一致性Consistent,隔离性lsolation,持久性Durable
1.set autocommit=0;update ......;commit;
2.间隙锁
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁,Innodb会给符合条件的已有数据记录的索引加锁;对于键值在条件范围内但不存在的记录,叫做间隙
(GAP);Innodb也会对这个间隙加锁,
危害:因为query执行过程中通过范围查找的话,他会锁定整个范围所有的索引值,即使这个键值并不存在。
行锁分析:
show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |当前正在等待锁定的数量
| Innodb_row_lock_time | 0 |从系统启动到现在锁定总时间长度 ***
| Innodb_row_lock_time_avg | 0 |每次等待所花平均时间 ***
| Innodb_row_lock_time_max | 0 |从系统启动到现在等待最长的一次所花时间
| Innodb_row_lock_waits | 0 |系统启动后到现在总共等待的次数 ***
优化建议
1.尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
2.合理设计索引,尽量缩小锁的范围
3.尽可能减少检索条件,避免间隙锁
4.尽量控制事务大小,减少锁定资源量和时间长度
5.s尽可能低级别事务隔离
======3.页面锁 偏写========
开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般
==================================
共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE。
InnoDB这种行锁实现特点:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
1.读锁(共享锁,读操作不受影响,别的会话有插入操作会处于阻塞状态) 写锁(排他锁,在写操作完成之前,会阻断其他读和写操作)
2.表锁和行级锁
======1.表锁 偏读========
myisam存储引擎,开销小,加锁快,锁力度大,发生锁冲突的概率最高,并发度最低
//手动加锁
lock table 表名称 read(write),表名称2 read(write),其他;
//查看表上加过的锁
show open tables;
//删除表锁
unlock tables;
//分析表锁定
show status like 'table%';
Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 112 |//产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1
| Table_locks_waited | 0 | //出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值高则说明存在着较严重的表级锁争用情况。
| Table_open_cache_hits | 0 |
| Table_open_cache_misses | 6 |//在打开表时,表缓存不存在的次数。
| Table_open_cache_overflows | 0 |
======2.行锁 偏写========
行锁偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁,锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
InnoDB与MYISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。
事务特性:原子性Atomicity,一致性Consistent,隔离性lsolation,持久性Durable
1.set autocommit=0;update ......;commit;
2.间隙锁
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁,Innodb会给符合条件的已有数据记录的索引加锁;对于键值在条件范围内但不存在的记录,叫做间隙
(GAP);Innodb也会对这个间隙加锁,
危害:因为query执行过程中通过范围查找的话,他会锁定整个范围所有的索引值,即使这个键值并不存在。
行锁分析:
show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |当前正在等待锁定的数量
| Innodb_row_lock_time | 0 |从系统启动到现在锁定总时间长度 ***
| Innodb_row_lock_time_avg | 0 |每次等待所花平均时间 ***
| Innodb_row_lock_time_max | 0 |从系统启动到现在等待最长的一次所花时间
| Innodb_row_lock_waits | 0 |系统启动后到现在总共等待的次数 ***
优化建议
1.尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
2.合理设计索引,尽量缩小锁的范围
3.尽可能减少检索条件,避免间隙锁
4.尽量控制事务大小,减少锁定资源量和时间长度
5.s尽可能低级别事务隔离
======3.页面锁 偏写========
开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般
==================================
共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE。
InnoDB这种行锁实现特点:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
mysql之group_concat()函数
1、功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
2、语法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
说明:通过使用distinct可以排除重复值;如果希望对结果中的值进行排序,可以使用order by子句;separator是一个字符串值,缺省为一个逗号。
3、举例:
表 table_a ,字段 id name cid
表 table_b ,字段 id title
表 table_b 的 id 对应 table_a中的cid
select b.*,(select group_concat(a.name) from table_a a where a.cid = b.id) names from table_b b
select b.*,(select group_concat(a.name order by a.id desc separator '_') from table_a a where a.cid = b.id) names from table_b b
mysql索引之聚簇索引和非聚簇索引
(一)各种树结构
1 搜索二叉树:每个节点有两个子节点,数据量的增大必然导致高度的快速增加,显然这个不适合作为大量数据存储的基础结构。
2 B树:一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足:┌m/2┐ - 1 <= j <= m - 1;一个节点的子节点数量会比关键字个数多1,这样关键字就变成了子节点的分割标志。一般会在图示中把关键字画到子节点中间,非常形象,也容易和后面的B+树区分。由于数据同时存在于叶子节点和非叶子结点中,无法简单完成按顺序遍历B树中的关键字,必须用中序遍历的方法。
3 B+树:一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足:┌m/2┐ - 1 <= j <= m;子树的个数最多可以与关键字一样多。非叶节点存储的是子树里最小的关键字。同时数据节点只存在于叶子节点中,且叶子节点间增加了横向的指针,这样顺序遍历所有数据将变得非常容易。
4 B*树:一棵m阶B树是一棵平衡的m路搜索树。最重要的两个性质是1每个非根节点所包含的关键字个数 j 满足:┌m2/3┐ - 1 <= j <= m;2非叶节点间添加了横向指针
B+树适合作为数据库的基础结构,完全是因为计算机的内存-机械硬盘两层存储结构。内存可以完成快速的随机访问(随机访问即给出任意一个地址,要求返回这个地址存储的数据)但是容量较小。而硬盘的随机访问要经过机械动作(1磁头移动 2盘片转动),访问效率比内存低几个数量级,但是硬盘容量较大。典型的数据库容量大大超过可用内存大小,这就决定了在B+树中检索一条数据很可能要借助几次磁盘IO操作来完成。
(二)存储引擎和索引
有两种常见的方法可以解决多个B+树访问同一套表数据的问题,一种叫做聚簇索引(clustered index ),一种叫做非聚簇索引(secondary index)。这两个名字虽然都叫做索引,但这并不是一种单独的索引类型,而是一种数据存储方式。对于聚簇索引存储来说,行数据和主键B+树存储在一起,辅助键B+树只存储辅助键和主键,主键和非主键B+树几乎是两种类型的树。对于非聚簇索引存储来说,主键B+树在叶子节点存储指向真正数据行的指针,而非主键。
InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。
MyISM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。
(三)聚簇索引的优势
1 由于行数据和叶子节点存储在一起,这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。
2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是,减少了当出现行移动或者数据页分裂时辅助索引的维护工作,使用主键值当作指针会让辅助索引占用更多的空间,换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置(实现中通过16K的Page来定位,后面会涉及)会随着数据库里数据的修改而发生变化(前面的B+树节点分裂以及Page的分裂),使用聚簇索引就可以保证不管这个主键B+树的节点如何变化,辅助索引树都不受影响。
原文地址:http://www.admin10000.com/document/5372.html
mysql表字段Note
标签:
mysql
物理设计
数据类型的选择
如何选择 VARCHAR和CHAR类型
1.使用最小的符合需求的长度
2.varchar(5) varchar(200) 存储mysql字符串性能不同
varchar的适用场景
1.字符串列的最大长度比平均长度大很多
2.字符串列很少被更新
3.使用了多字节字符集存储字符串
char类型的存储特点
1.char类型是定长的
2.字符串存储在char类型的列中会删除末尾的空格
3.char类型的最大宽度为255
char的适用场景
1.char类型适合存储长度近似的值
2.char类型适合存储短字符串
3.char类型适合存储经常更新的字符串列
如何存储日期数据
DATATIME 类型 占用8个字节的存储空间 字段名+datatime(6) 微秒
TIMESTAMP类型 占用4个字节 依赖于所指定的时区 ,在行的数据修改时可以自动修改timestamp列的值 字段名+timestamp(6) 微秒
date类型和time类型
date类型特点
1.使用date类型只需要占用3个字节
2.使用date类型还可以利用日期时间函数进行日期之间的计算
time类型特点 ,用于存储时间数据,格式为HH:MM:SS
mysqlSQL优化Note
慢查询日志介绍
slow_query_log //启动停止记录慢查询日志 show variables like 'slow_query_log'; set global slow_query_log = on;
slow_query_log_file 指定慢查询日志的存储路径及文件
long_query_time 指定记录慢查日志sql执行时间的阀值 // show variables like 'long_query_time';
log_queries_not_using_indexes 是否记录未使用索引的sql
常用的慢查询分析工具(mysqldumpslow)
mysqldumpslow -s r -t 10 slow_mysql.log
-s order (c,t,l,at,al,ar) {c:总次数 t:总时间 l:锁的时间 r:总数据行 at,al,ar平均数 at=总时间/总次数}
-t top 指定前几条作为结束输出
常用的慢查询分析工具pt-query-digest
pt-query-digest --explain h=127.0.0.1,u=root,p=p@1314 slow_mysql.log
实时获取有性能问题的SQL
select ID,USER,HOST,DB,COMMAND,TIME,STATE,INFO from information_schema.PROCESSLIST where TIME > 3;
mysql服务器处理查询请求的整个过程
1.客户端发送sql请求给服务器
2.服务器检查是否可以在查询缓存中命中该sql
3.服务器端进行sql解析,预处理,再由优化器生成对应的执行计划
4.根据执行计划,调用存储引擎api来查询数据
5.将结果返回给客户端
查询缓存对sql性能的影响
query_cache_type 设置查询缓存是否可用 ||demand 表示只有在查询语句中使用sql_cache和sql_no_cache来控制是否需要缓存on 开启off 关闭
query_cahce_size 设置查询缓存的内存大小
query_cache_limit 设置查询缓存可用存储的最大值
query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据
query_cache_min_res_unit 设置查询缓存分配的内存块最小单位
确定查询处理各个阶段所消耗的时间
1.使用profile
set profiling = 1;
执行查询
show profiles;
show profile for query N; //查询每个阶段所消耗的时间
show profile cpu for query 1;
2.使用performance_schema
update setup_instruments set enabled='YES' where name like 'stage%';
update setup_consumers set enabled='YES' where name like 'events%';
如何修改大表的表结构
pt-online-schema-change \
--alter="MODIFY c VARCHAR(150) NOT NULL DEFAULT ''" \
--user=root --password=1314 D=databasename,t=tablename \
--charset=utf8 --exexute
mysql常用存储引擎以及简单介绍
1.MyISAM
特性
并发性与锁级别
表损坏修复
check table tablename
repair table tablename
MyISAM表支持的索引类型
MyISAM表支持数据压缩
命令行:myisampack //压缩就只能进行读操作
使用场景
非事务型应用
只读类应用(支持压缩)
空间类应用
2.Innodb
特性
事务型存储引擎
完全支持事务的ACID特性
Redo Log 和 Undo Log
支持行级锁
行级锁可最大程度的支持并发
行级锁是有存储引擎层实现的
3.csv
特点
以CSV格式进行数据存储
所有列必须都是不能为null的
不支持索引
可以对数据文件直接编辑
文件系统存储特点
数据以文本方法存储在文件中
.csv文件存储表内容
.csm文件存储表的元数据如表状态和数据量
.frm文件存储表结构信息
适用场景
适合做为数据交换的中间表 电子表格-》CSV文件 -》mysql数据目录
4.Archive
文件系统存储特点
以ZLIB对表数据进行压缩,磁盘I/O更少
数据存储在ARZ为后缀的文件中
特点
只支持select 和insert操作
只支持在自增id上建立索引
使用场景
日志和数据采集类应用
5.Memory
文件系统存储特点
也称HEAP存储引擎,所以数据保存在内存中,只有.frm文件
功能特点
支持hash索引(等值查找)和Btree索引(范围查找)
所有字段都为固定长度 varchar(10) = char(10)
不支持BLOG和TEXT等大字段
使用表级锁
最大大小是由max_heap_table_size参数决定
create index idx_c1 on mymemory(c1);
create index idx_c2 using btree on mymemory(c2);
使用场景
用于查找或者是映射表,例如邮编和地区的对应表
用于保存数据分析中产生的中间表
用户缓存周期性聚合数据的结果表
6.Federated
特点
提供了访问远程Mysql服务器上表的方法
本地不存储数据,数据全部放到远程服务器上
本地需要保存表结构和远程服务器的连接信息
如何使用
默认禁止,启用需要在启动时增加federated参数
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name
使用场景
偶尔的统计分析与手工查询
特性
并发性与锁级别
表损坏修复
check table tablename
repair table tablename
MyISAM表支持的索引类型
MyISAM表支持数据压缩
命令行:myisampack //压缩就只能进行读操作
使用场景
非事务型应用
只读类应用(支持压缩)
空间类应用
2.Innodb
特性
事务型存储引擎
完全支持事务的ACID特性
Redo Log 和 Undo Log
支持行级锁
行级锁可最大程度的支持并发
行级锁是有存储引擎层实现的
3.csv
特点
以CSV格式进行数据存储
所有列必须都是不能为null的
不支持索引
可以对数据文件直接编辑
文件系统存储特点
数据以文本方法存储在文件中
.csv文件存储表内容
.csm文件存储表的元数据如表状态和数据量
.frm文件存储表结构信息
适用场景
适合做为数据交换的中间表 电子表格-》CSV文件 -》mysql数据目录
4.Archive
文件系统存储特点
以ZLIB对表数据进行压缩,磁盘I/O更少
数据存储在ARZ为后缀的文件中
特点
只支持select 和insert操作
只支持在自增id上建立索引
使用场景
日志和数据采集类应用
5.Memory
文件系统存储特点
也称HEAP存储引擎,所以数据保存在内存中,只有.frm文件
功能特点
支持hash索引(等值查找)和Btree索引(范围查找)
所有字段都为固定长度 varchar(10) = char(10)
不支持BLOG和TEXT等大字段
使用表级锁
最大大小是由max_heap_table_size参数决定
create index idx_c1 on mymemory(c1);
create index idx_c2 using btree on mymemory(c2);
使用场景
用于查找或者是映射表,例如邮编和地区的对应表
用于保存数据分析中产生的中间表
用户缓存周期性聚合数据的结果表
6.Federated
特点
提供了访问远程Mysql服务器上表的方法
本地不存储数据,数据全部放到远程服务器上
本地需要保存表结构和远程服务器的连接信息
如何使用
默认禁止,启用需要在启动时增加federated参数
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name
使用场景
偶尔的统计分析与手工查询