数据库原理(锁 索引 负载均衡 集群)

select version(); //数据库版本

show variables like '%engine%'; //数据库引擎

select @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation; //事务隔离级别

show variables like 'innodb_locks_unsafe_for_binlog'; //查看gap锁开启状态
//innodb_locks_unsafe_for_binlog：默认值为0，即启用gap lock。
//开启innodb_locks_unsafe_for_binlog的REPEATABLE-READ事务隔离级别，
	很大程度上已经蜕变成了READ-COMMITTED

版本号（记为version）

	就是给数据增加一个版本标识，在数据库上就是表中增加一个version字段，每次更新把这个字段加1，
	读取数据的时候把version读出来，更新的时候比较version，如果还是开始读取的version就可以更新了，
	如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号，
	这时候得到一个无法更新的通知，
	用户自行根据这个通知来决定怎么处理，比如重新开始一遍。
	这里的关键是判断version和更新两个动作需要作为一个原子单元执行，
	否则在你判断可以更新以后正式更新之前有别的事务修改了version，
	这个时候你再去更新就可能会覆盖前一个事务做的更新，造成第二类丢失更新，
	所以你可以使用update … where … and version=”old version”这样的语句，
	根据返回结果是0还是非0来得到通知，如果是0说明更新没有成功，
	因为version被改了，如果返回非0说明更新成功。
	
时间戳（timestamp）

	和版本号基本一样，只是通过时间戳来判断而已，
	注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间。
	
待更新字段

	和版本号方式相似，只是不增加额外字段，直接使用有效数据字段做版本控制信息，
	因为有时候我们可能无法改变旧系统的数据库表结构。
	假设有个待更新字段叫count,先去读取这个count,
	更新的时候去比较数据库中count的值是不是我期望的值（即开始读的值），
	如果是就把我修改的count的值更新到该字段，否则更新失败。
	java的基本类型的原子类型对象如AtomicInteger就是这种思想。

原子性(Atomic)

	事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
	通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，
	则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。
一致性(Consistency)

	事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。
	这种特性称为事务的一致性。假如数据库的状态满足所有的完整性约束，就说该数据库是一致的。
隔离性(Isolation)

	由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，
	到底是另一个事务执行之前的状态还是中间某个状态，相互之间存在什么影响，是可以通过隔离级别的设置来控制的。
持久性(Durability)

	事务结束后，事务处理的结果必须能够得到固化，即写入数据库文件中即使机器宕机数据也不会丢失，
	它对于系统的影响是永久性的。

第一类丢失更新（Update Lost）

	此种更新丢失是因为回滚的原因，所以也叫回滚丢失。此时两个事务同时更新count，
	两个事务都读取到100，事务一更新成功并提交，count=100+1=101，事务二出于某种原因更新失败了，
	然后回滚，事务二就把count还原为它一开始读到的100，此时事务一的更新就这样丢失了。
脏读（Dirty Read）

	此种异常时因为一个事务读取了另一个事务修改了但是未提交的数据。举个例子，事务一更新了count=101，
	但是没有提交，事务二此时读取count，值为101而不是100，然后事务一出于某种原因回滚了，
	然后第二个事务读取的这个值就是噩梦的开始。
不可重复读（Not Repeatable Read）

	此种异常是一个事务对同一行数据执行了两次或更多次查询，但是却得到了不同的结果，
	也就是在一个事务里面你不能重复（即多次）读取一行数据，如果你这么做了，
	不能保证每次读取的结果是一样的，有可能一样有可能不一样。造成这个结果是在两次查询之间有别的事务对
	该行数据做了更新操作。举个例子，事务一先查询了count，值为100，此时事务二更新了count=101，
	事务一再次读取count,值就会变成101，两次读取结果不一样。
第二类丢失更新（Second Update Lost）

	此种更新丢失是因为更新被其他事务给覆盖了，也可以叫覆盖丢失。举个例子，两个事务同时更新count，
	都读取100这个初始值，事务一先更新成功并提交，count=100+1=101，事务二后更新成功并提交，
	count=100+1=101,由于事务二count还是从100开始增加，事务一的更新就这样丢失了。
幻读（Phantom Read）

	幻读和不可重复读有点像，只是针对的不是数据的值而是数据的数量。此种异常是一个事务
	在两次查询的过程中数据的数量不同，让人以为发生幻觉，幻读大概就是这么得来的吧。
	举个例子，事务一查询order表有多少条记录，事务二新增了一条记录，
	然后事务一查了一下order表有多少记录，发现和第一次不一样，这就是幻读。

读未提交（Read Uncommitted）
	该隔离级别指即使一个事务的更新语句没有提交,但是别的事务可以读到这个改变，
	几种异常情况都可能出现。极易出错，没有安全性可言，基本不会使用。

读已提交（Read Committed）
	该隔离级别指一个事务只能看到其他事务的已经提交的更新，看不到未提交的更新，
	消除了脏读和第一类丢失更新，这是大多数数据库的默认隔离级别，如Oracle,Sqlserver。

可重复读（Repeatable Read）
	该隔离级别指一个事务中进行两次或多次同样的对于数据内容的查询，得到的结果是一样的，
	但不保证对于数据条数的查询是一样的，只要存在读改行数据就禁止写，
	消除了不可重复读和第二类更新丢失，这是Mysql数据库的默认隔离级别。

串行化（Serializable）
	意思是说这个事务执行的时候不允许别的事务并发执行.完全串行化的读，
	只要存在读就禁止写,但可以同时读，消除了幻读。这是事务隔离的最高级别，
	虽然最安全最省心，但是效率太低，一般不会用。	


级别\异常 第一类更新丢失 脏读 不可重复读 第二类丢失更新 幻读
读未提交 Y Y Y Y Y
读已提交 N N Y Y Y
可重复读 N N N N Y
串行化	 N N N N N

创建：
create [unique] index indexName on tableName (columnName...)
alter tableName add [unique] index [indexName] on (columnName...)

删除：
drop index [indexName] on tableName

查看：
show index from tableName

1. 主键，唯一索引
2. 经常用作查询条件的字段需要创建索引
3. 经常需要排序、分组和统计的字段需要建立索引
4. 查询中与其他表关联的字段，外键关系建立索引

1. 表的记录太少，百万级以下的数据不需要创建索引
2. 经常增删改的表不需要创建索引
3. 数据重复且分布平均的字段不需要创建索引，如 true,false 之类。
4. 频繁更新的字段不适合创建索引
5. where条件里用不到的字段不需要创建索引

+----+-------------+------------+-------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table      | type  | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+------------+-------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | t_t0_order | const | F_trade_id_2  | F_trade_id_2 | 93      | const |    1 |       |
+----+-------------+------------+-------+---------------+--------------+---------+-------+------+-------+

id

select 查询的序列号，包含一组可以重复的数字，表示查询中执行sql语句的顺序。一般有三种情况：
第一种：id全部相同，sql的执行顺序是由上至下；
第二种：id全部不同，sql的执行顺序是根据id大的优先执行；
第三种：id既存在相同，又存在不同的。先根据id大的优先执行，再根据相同id从上至下的执行。

select_type

select 查询的类型，主要是用于区别普通查询，联合查询，嵌套的复杂查询
simple：简单的select 查询，查询中不包含子查询或者union
primary：查询中若包含任何复杂的子查询，最外层查询则被标记为primary
subquery：在select或where 列表中包含了子查询
derived：在from列表中包含的子查询被标记为derived（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
union：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为：derived
union result：从union表获取结果的select

partitions

表所使用的分区，如果要统计十年公司订单的金额，可以把数据分为十个区，每一年代表一个区。这样可以大大的提高查询效率。

type

这是一个非常重要的参数，连接类型，常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。
性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all。
对java程序员来说，若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。

all：（full table scan）全表扫描无疑是最差，若是百万千万级数据量，全表扫描会非常慢。

index：（full index scan）全索引文件扫描比all好很多，毕竟从索引树中找数据，比从全表中找数据要快。

range：只检索给定范围的行，使用索引来匹配行。范围缩小了，当然比全表扫描和全索引文件扫描要快。
	sql语句中一般会有between，in，>，< 等查询。

ref：非唯一性索引扫描，本质上也是一种索引访问，返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事，匹配的结果是多个并非唯一值。

eq_ref：唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配。比如查询公司的CEO，匹配的结果只可能是一条记录，

const：表示通过索引一次就可以找到，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快，
	若将主键至于where列表中，MySQL就能将该查询转换为一个常量。

system：表只有一条记录（等于系统表），这是const类型的特列，平时不会出现，了解即可

possible_keys

显示查询语句可能用到的索引(一个或多个或为null)，不一定被查询实际使用。仅供参考使用。

key

显示查询语句实际使用的索引。若为null，则表示没有使用索引。

key_len

显示索引中使用的字节数，可通过key_len计算查询中使用的索引长度。
在不损失精确性的情况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度，
并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。

ref

显示索引的哪一列或常量被用于查找索引列上的值。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，值越大越不好。

extra

Using filesort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。

Using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。
常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。

Using index： 表示相应的select 操作中使用了覆盖索引（Covering index），
避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。
如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。

覆盖索引（Covering Index） ：也叫索引覆盖，就是select 的数据列只用从索引中就能够取得，
不必读取数据行，MySQL可以利用索引返回select 列表中的字段，而不必根据索引再次读取数据文件。

Using index condition： 在5.6版本后加入的新特性，优化器会在索引存在的情况下，
通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。

Using where： 表明使用了where 过滤。

Using join buffer： 表明使用了连接缓存。

impossible where： where 语句的值总是false，不可用，不能用来获取任何元素。

distinct： 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

filtered
一个百分比的值，和rows 列的值一起使用，可以估计出查询执行计划(QEP)中的前一个表的结果集，
从而确定join操作的循环次数。小表驱动大表，减轻连接的次数。

当master出现故障时，通过对比slave之间I/O thread 读取主库binlog的position号，选取最接近的slave作为被选主库。
缺点：需要开启linux系统的互信协议，对于系统安全有影响。

双主热备(这里2个都要设置backup，互为主备)

Keepalived基于VRRP(virtual redundent routing protocol)协议。

(基于Galera协议的Percona XtraDB Cluster实现真正意义上同步复制)

haproxy: 负载均衡

pxc 最小要3个mysql实例，三个实例之间不是主从模式，各自为主，不分从属。实现高性能

有6个mysql示例是最理想的。其中3主3从，实现真正的高可用

读写分离