三、大数据之Mysql进阶

三、Mysql进阶

一、事务

所谓事务，简而言之，就是一条或者是一组语句组成一个单元，这个单元要么全部执行，要么全不执行。

1、事务的四大特性

原子性(Atomicity)

指事务是一个不可分割的，要么都执行，要么都不执行。

一致性(Consistency)

事务必须使得数据库从一个一致性状态，到例外一个一致性状态。

隔离性(Isolation)

一个事务的执行，不能被其他的事务所干扰

持久性(Durability)

指一个事务一旦提交了之后，对数据库的改变就是永久的

2、事务的并发问题

(1)数据的脏读

例如：事务T1和事务T2，如果T1读了一条数据，这条数据是T2更新的但没有提交，突然T2觉得不适合进行事务回滚了，也就是不提交了。此时T1读的数据就是无效数据。

(2)不可重复读

事务T1和事务T2 ，如果T1读了一条数据，之后T2更新了这条数据，T1再次读取就会发现值变了。

(3)数据的幻读

事务T1和事务T2 ，如果T1读了一条数据，之后T2插入了一些新的数据，T1再次读取就会多出一些数据。

3、隔离级别

事务的隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read	否	否	是
串行化（serializable）	否	否	否

注意：Oracle默认不可重复读，而且不支持脏读。Mysql默认可重复读。

二、索引

首先，索引的本质就是一种数据结构，对于MySql的高效运行是很重要的。索引可以大大提高MySql的检索速度。

另外，索引也是一张表，该表保存了主键与索引的字段，并指向实体表的记录。

1、索引的存储类型

一般来说，索引存储主要为Btree(B树)和Hash。

2、索引类型

分为：单值索引、唯一索引、复合索引、主键索引。

3、建立索引的情况

(1) 主键自动建立唯一索引

(2) 频繁作为查询条件的字段应该创建索引

(3) 查询中与其他表关联的字段，外检关系建立索引

(4) 单键/组合索引的选择问题，组合索引性价比更高

(5) 查询中排序的字段，排序字段若通过索引访问将显著提高偶爱徐速率

(6) 查询中统计或者分组字段

4、索引的缺点

(1) 虽然索引提高查询的速度，但同时也降低了更新表的速度。如对表进行 insert、update和delete操作时。

(2) 在更新表时，MySql不仅要保存数据，还要保存索引文件。建立所以你会占用磁盘的索引空间。

三、数据库的优化

1、选取最适用的字段属性

例如邮政编码这只为varchar(6),而不是varchar(255)。另外，应该尽量把字段设置为NOTNULL，这样在进行后续的查询时，数据库不用去比较NULL值。

2、使用(JOIN)来代替子查询(Sub-Queries)

3、使用联合(UNION)来代替手动创建的临时表

4、锁定表

由于事务执行的过程中，数据库会被锁定，因此其他的用户请求只能暂时等待直到该事务结束。使用锁定表的功能一方面可以保证事务的完整性，也可以避免死锁的情况。

5、使用外键

6、使用索引

7、优化查询语句

四、SQL的优化

对查询进行优化时，应尽量避免全表扫描，首先应该考虑在where 及 order by涉及的列上建立索引。

1、where后慎用语法，避免全表扫描

(1) 避免使用 != 或 <>操作符

(2) 用num=0代替num is null(前提给num的默认值为0)

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

(3) 使用union代替or 进行条件的表关联

select id from t where num=10
union all
select id from t where num=20

(4) in和not in慎用

select id from t where num in(1,2,3)

对于上述的查询中，连续数值可以用between就不要用in

select id from t where num between 1 and 3

(5) where子句慎用参数

例如：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

(6) 慎用对字段进行表达式的操作

例如：

select id from t where num/2=100

应改为：

select id from t where num=100*2

(7) 避免对字段进行函数操作

select id from t where substring(name,1,3)='abc'#name以abc开头的id
select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id

应改为：

select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

(8) 用exists代替in

select num from a where num in(select num from b)

用下面语句替换：

select num from a where exists(select 1 from b where num=a.num)

2、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的。档索引列有大量重复数据时，SQL查询可能不会去利用索引。

假如：一表中有字段 sex、male、female几乎各一半，那么即使在sex上建立索引也对查询效率起不了作用

3、索引并不是越多越好，索引固然可以提高相应的select效率，但同时也降低了insert和update的效率。因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

4、尽可能避免更新操作clus

五、存储过程

存储过程(Stored Procedure) 是在大型数据库中，一组为了能完成特定功能的SQL语句集，它存储早数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程时数据库中的一个重要对象。在数据特别庞大的情况下利用存储过程能达到的倍速的效率提升。