邢栋博客

====进入终端=====
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';

创建表+插入测试数据

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;

锁分类
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、功能：将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

(一)各种树结构
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

物理设计
数据类型的选择
如何选择 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

慢查询日志介绍

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

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
    使用场景
        偶尔的统计分析与手工查询