数据库总结-mysql

参考文献：

• http://blog.csdn.net/flyingfalcon/article/details/53045672

mysql系统命令

手动解锁

SHOW PROCESSLIST;
kill 816934

查询大小

查询数据库

select truncate(sum(data_length)/1024/1024,2) as data_size,
truncate(sum(index_length)/1024/1024,2) as index_size,
truncate(sum(index_length+data_length)/1024/1024,2) as total,
table_schema 
from information_schema.tables group by table_schema 

查询表

select TABLE_NAME,(DATA_LENGTH+INDEX_LENGTH)/1024/1024 
from information_schema.tables 
where table_schema='QualityEva';

语句执行方式

mysql语句的书写顺序

mysql语言的书写顺序为.

1. select[distinct].
2. from.
3. where.
4. group by.
5. having.
6. union.
7. order by

mysql中语句的执行顺序

1. FORM: 对FROM的join左边的表和join右边的表计算笛卡尔积。产生虚表VT1
2. ON: 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。
3. JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, 如果from子句中包含两个以上的join表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
4. WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
5. GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
6. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
7. HAVING： 对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。
8. SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
9. DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
10. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
11. LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回

例子1

select a.schdate, a.adept, a.delay_cnt, b.total_cnt, cast(a.delay_cnt as float)/b.total_cnt as delay_ratio from
(
select schdate, adept, count(*) as delay_cnt from OLAP_FLIGHT_TIMEDATA_DELAY WHERE real_delay_flag='1' group by schdate, adept
) a
join
(
select schdate, adept, count(*) as total_cnt from OLAP_FLIGHT_TIMEDATA_DELAY group by schdate, adept
) b 
on a.adept=b.adept and a.schdate=b.schdate

存储引擎

InnoDB和MyISAM

基础数据类型都是b+树。数据存储在叶子节点上。多路查找。有兄弟指针可以进行范围查询。

myISAM的数据和索引是分开存储的。而innodb数据是按照聚集索引（一般是主键）在B+树上进行存储的。除了聚集索引，其他索引单独存储，但是叶子节点不是指向真实的内容，而是指向其聚集索引的ID，这样还需要再查找聚集索引的B+树来进行查找。

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个存储引擎。
基本的差别为：

• MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。
• MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。
• INNODB在内存中保存索引和数据，而MyISAM只保存索引。这意味着，如果内存足够大，INNODB的性能会超过MyISAM。一般而言，对于只运行mysql的服务器，建议将内存设置为总内存的的80%。

memory存储引擎

• 同时支持hash和btree索引
• 当内存表中的数据大于max_heap_table_size设定的容量大小时，mysql会转换超出的数据存储到磁盘上，因此这是性能就大打折扣了，所以，请保证表的数据量不会超过内存的最大值。

CREATE TABLE lookup
(id INT, INDEX USING HASH (id))
ENGINE = MEMORY;
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;

能用redis请使用redis

NDB存储引擎

• 分布式：分布式存储引擎，可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分
• 支持事务：和Innodb一样，支持事务
• 可与mysqld不在一台主机：可以和mysqld分开存在于独立的主机上，然后通过网络和mysqld通信交互
• 内存需求量巨大：新版本索引以及被索引的数据必须存放在内存中，老版本所有数据和索引必须存在与内存中

集群

mysql主从备份实现

• master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events，可以通过show binlog events进行查看）；
• slave将master的binary log events拷贝到它的中继日志(relay log)；
• slave重做中继日志中的事件，将改变反映它自己的数据。

如果你能将自己伪装为一个mysql slave，就可以欺骗mysql master给自己推送中继日志，从而实现增量获取事件的功能。阿里开源的canel就是这样做的。

性能检测

日志功能

打开慢日志功能

show VARIABLES like 'slow%'   
show VARIABLES like '%log_output%' 

控制输出位置。如果输出到表格，表格为mysql.slow_log，注意，输出到表格会影响查询速度。

show variables like 'long_query_time%';   

控制输出的阈值