数据库总结-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中的记录按照进行排序操作，产生虚拟表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) ajoin(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是许多人在使用MySQL时最常用的两个存储引擎。
基本的差别为：
+ MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。
+ MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。

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通信交互
• 内存需求量巨大：新版本索引以及被索引的数据必须存放在内存中，老版本所有数据和索引必须存在与内存中

索引

索引值会加快查询速度，但是会降低写入速度。

索引类型

唯一性索引：
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
唯一性索引列允许空值，而主键列不允许为空值。
主键列在创建时，已经默认为空值 + 唯一索引了。
主键可以被其他表引用为外键，而唯一索引不能。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

聚类索引
每个表只能有一个聚类索引。
注意，主键也可以声明为非聚类索引！！但是默认是聚类索引。

复合索引，组合索引
用户可以在多个列上建立索引，这种索引叫做复合索引(组合索引)。
一般设计上，能使用窄索引就不要使用宽索引，这样可以使sql有更多的优化空间。

注意，复合索引是有顺序的。

主键
主键是表中的一个字段或多个字段，用来唯一地标识表中的一条记录。唯一性是主键最主要的特性。

外键
定义外键主要是为了保证数据的一致性。

触发键

BTree索引

Innodb和MyISAM默认的索引是Btree索引
MyISAM主索引如下图所示：

MyISAM的辅助索引：

myisam的主键索引的叶子节点只存放数据在物理磁盘上的指针，其他次索引也是一样的；myISAM的数据是在物理磁盘上是按照顺序存储的；MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

Innodb的主索引如下图所示：

Innodb的辅助索引如下图所示：

innodb的主键索引的叶子节点下面直接存放数据，其他次索引的叶子节点指向主键id；
innodb的数据是随机分布的，也就是说，文件存储顺序和主索引顺序没有关系。

hash索引

事务

数据库事务必须满足4个特性（ACID）

• 原子性，事务中的指令要不都完成，要不都不完成
• 一致性
• 隔离性
• 持久性

其中，隔离性有几个等级。而数据的一致性是最终的目的。

• 脏读：A事务读取了B事务已经更改但是没有提交的数据
• 不可重复读：A事务读取了B事务已经更改且已经提交的数据。也就是说A事务读取了数据，然后B事务更改数据并提交，而此时A事务还没有结束，如果A事务继续读取那个数据的话，会发现两次数据不相同
• 幻读：和上面类似，只是更改变成了新加。但是在编程上的实现肯定有很大的不同。防止不可重复读，只要让A事务对自己读取的行进行加行级别的锁就可以了。而防止幻读需要对整个表进行加锁。
• 丢失更新 两个事物读取同一数据，然后进行不同操作，然后提交。那么，后提交的数据会把先提交的数据覆盖掉。

根据上面可能出现的问题，SQL标准制定了四种隔离级别

• Read Uncommitted（读取未提交内容）
  简而言之，就是不做事务隔离，上面的所有问题都会发生。实际中很少使用。

• Read Committed（读取提交内容）
  就是可以抵挡住脏读了。此时，只有事务提交后，其他事务才能看到更改

• Repeatable Read（可重读）
  防止了不可重复读的现象，但还是允许幻读

• Serializable（可串行化）
  强行通过对事务排序。可以杜绝一切的事务不一致问题。但是效率也是最低的

JDBC则定义了五种事物隔离界别：

• RANSACTION_NONE JDBC： 驱动不支持事务

• TRANSACTION_READ_UNCOMMITTED 允许脏读、不可重复读和幻读，丢失更新。

• TRANSACTION_READ_COMMITTED 禁止脏读，但允许不可重复读和幻读，丢失更新。

• TRANSACTION_REPEATABLE_READ 禁止脏读和不可重复读，但允许幻读，丢失更新。

• TRANSACTION_SERIALIZABLE 禁止脏读、不可重复读和幻读，丢失更新

为了实现上述隔离界别，需要采用数据库的锁机制。

• 1、一级封锁协议 (对应 read uncommited) 　　
  一级封锁协议是：事务在对需要修改的数据上面（就是在发生修改的瞬间） 对其加共享锁（防止其它事物同时更改，但是，此时另一个事物如果要读取，就会导致“脏读”），直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。一级封锁协议不能避免 丢失更新，脏读，不可重复读，幻读！

• 2、二级封锁协议 （对应read commited)　
  二级封锁协议是：1）事务在对需要更新的数据 上（就是发生更新的瞬间） 加 排他锁 （直到事务结束） ， 防止其他事务读取未提交的数据，这样，也就避免了 “脏读” 的情况。2）事务 对当前被读取的数据 上面加共享锁（当读到时加上共享锁），一旦读完该行，立即 释放该 该行的共享锁 - 从数据库的底层实现更深入的来理解，既是，数据库会对游标当前的数据上加共享锁 ， 但是当游标离开当前行的时候，立即释放该行的共享锁。　
  二级封锁协议除防止了“脏读”数据，但是不能避免丢失更新，不可重复读，幻读 。

• 3、三级封锁协议 （对应reapetable read)
  三级封锁协议是：二级封锁协议加上事务(在读取数据的瞬间)必须先对其加共享锁 ，但是直到事务结束才释放 ，这样保证了可重复读（既是其他的事务职能读取该数据，但是不能更新该数据）。但是，这样，不能避免幻读，也不能避免丢失更新。为了防止丢失更新，需要程序员手动对读取的数据并要在之后进行更新的数据加上排他锁。

• 4、最强封锁协议（对应Serialization)
  四级封锁协议是对三级封锁协议的增强，其实现机制也最为简单，直接对 事务中所读取或者更改的数据所在的表加表锁，也就是说，其他事务不能 读写 该表中的任何数据。这样所有的 脏读，不可重复读，幻读 ，都得以避免！

事物传播行为：
七个事务的传播行为

• propagation_required 默认的Spring事务传播级别，使用该级别的特点是：如果上下文中已经存在事务，那么就加入到事务中执行；如果当前上下文中不存在事务，则新建事务执行。所以这个级别通常能满足处理大多数的业务场景。

• propagation_supports 如果一个事务存在，则支持当前事务，如果不存在，则非事务的方法运行

• propagation_mendatory 如果一个事务存在，则支持当前事务，如果存在，则抛出异常

• propagation_requires_new 总是要开启一个新的事务，如果事务存在，将该事务挂起

• propagation_not_supported 总是非事务方法运行，并挂起所有的事务

• propagation_never 总是非事务方法运行，如果事务存在则抛出异常

• propagation_nested 某一个事务存在，则运行在一个嵌套的事务中

锁

锁的分类

按照作用分：

• 共享锁 select语句会加入共享锁，数据库机制规定共享锁和排他锁不能同时添加到一个资源上。所以，一句select对资源加了共享锁，另一句select也可以对同一资源加共享锁，但是，一句update则必须等待，因为，他无法把排他锁加到一个已经上了共享锁的资源上。
• 排他锁 其它任何资源，既不能读，也不能更改
• 意向锁
• 计划锁
• 更新锁 为了解决共享锁和排他锁所带来的死锁问题。假设事物1对资源1加了共享锁，事物2对同一资源也加了共享锁。如果他们随后都想对该资源加入排他锁，就会陷入死锁。所以，事物1一开始应该对资源加入更新锁，表示我已经获取了取得该资源的排他锁资格，但是目前只对该资源加上了共享锁，你们可以读（加上共享锁），但是不要试图加上拍他锁。此时，事物2在试图对该资源加拍他锁后就会等待。

按照粒度分：

• 行级锁
• 页级锁
• 表级锁

java中的事务

Spring 同时支持编程式事务和声明式事务。编程式事务需要在代码中显式调用beginTransaction()、commit()、rollback()等事务管理相关的方法。Spring 的声明式事务管理在底层是建立在 AOP 的基础之上的，其本质是对方法前后进行拦截，然后在目标方法开始之前创建或者加入一个事务，在执行完目标方法之后根据执行情况提交或者回滚事务。事务增强也是AOP的一大用武之处。

因为现代架构由CS向BS架构演进，可信边界前移，所以，直接使用数据库锁机制的地方减少了。
当然，你仍然可以通过sql来使用数据库自己的锁机制

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

如果选择sesion参数，则本次会话所开启的事物全部使用该事务隔离级别。

如果使用global参数，则本次会话和所有新连接的会话都会使用该事务隔离级别

如果不使用任何参数，则只有本会话下一次开启的事物使用该隔离级别。

性能检测

日志功能

打开慢日志功能

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

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

show variables like 'long_query_time%';   

控制输出的阈值