1.3-MySQL（关系型数据库）-高级

1、事务

（1）首先什么是事务？

事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。

事务的结束有两种，当事务中的所以步骤全部成功执行时，事务提交。如果其中一个步骤失败，将发生回滚操作，撤消之前到事务开始时的所以操作。

（2）事务的 ACID？

事务具有四个特征：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durability）

1）原子性。事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

2）一致性。事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。

3）隔离性。一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

4）持续性。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

（3）事务的并发问题

1）脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

【一个事务读取了另一个事务未提交的数据】

2）不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

【不可重复读的重点是修改，同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改；】

3）幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

【幻读的重点在于新增或者删除，同样条件下两次读出来的记录数不一样。】

4）丢失更新：一个事务的更新覆盖了另一个事务的更新；

（4）Mysql的四种隔离级别

隔离级别用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

1）读未提交（READ UNCOMMITTED）：最低级别的隔离，通常又称为dirty read，它允许一个事务读取另一个事务还没 commit 的数据，这样可能会提高性能，但是会导致脏读问题；

2）读已提交（READ COMMITTED）：在一个事务中只允许对其它事务已经 commit 的记录可见，该隔离级别不能避免不可重复读问题；

3）可重复读（REPEATABLE READ）：在一个事务开始后，其他事务对数据库的修改在本事务中不可见，直到本事务 commit 或 rollback。但是，其他事务的 insert/delete 操作对该事务是可见的，也就是说，该隔离级别并不能避免幻读问题。在一个事务中重复 select 的结果一样，除非本事务中 update 数据库。

4）串行化（SERIALIZABLE）：最高级别的隔离，只允许事务串行执行。

（5）MySQL默认的隔离级别是可重复读（REPEATABLE READ）

MySql 的事务支持：MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关：

MyISAM：不支持事务，用于只读程序提高性能；

InnoDB：支持ACID事务、行级锁、并发；

Berkeley DB：支持事务。

2、MySQL索引

（1）索引的概念：

索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。类似于书籍的索引。

（2）索引的特点：

• 索引可以加快数据库的检索速度
• 索引降低了数据库插入、修改、删除等维护任务的速度
• 索引创建在表上，不能创建在视图上
• 索引既可以直接创建，也可以间接创建
• 可以在优化隐藏中，使用索引
• 使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引 ……

（3）索引的优点：

• 创建唯一性索引，保证数据库表中每一行数据的唯一性
• 大大加快数据的检索速度，这也是创建索引的最主要的原因
• 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

（4）索引的缺点

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

（5）底层数据结构是什么，为什么使用这种数据结构？

• 底层数据结构是B+树： 在数据结构中，我们最为常见的搜索结构就是二叉搜索树和AVL树(高度平衡的二叉搜索树，为了提高二叉搜索树的效率，减少树的平均搜索长度)了。然而，无论二叉搜索树还是AVL树，当数据量比较大时，都会由于树的深度过大而造成I/O读写过于频繁，进而导致查询效率低下，因此对于索引而言，多叉树结构成为不二选择。特别地，B-Tree的各种操作能使B树保持较低的高度，从而保证高效的查找效率。
• 使用B+树的原因： 查找速度快、效率高，在查找的过程中，每次都能抛弃掉一部分节点，减少遍历个数。（B+树见下图）【B+树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入，这与二叉树恰好相反】

（6）MySql索引的原理：

• 通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
• 索引是通过复杂的算法，提高数据查询性能的手段。从磁盘io到内存io的转变。

（7）MySql索引的类型：

• 普通索引index：加速查找
• 唯一索引：①、主键索引：primary key：加速查找+主键唯一约束且不为空。

                         ②、唯一索引：unique：加速查找+主键唯一约束。

• 联合索引：①、primary key(id,name)：联合主键索引。

                         ②、unique(id,name)：联合唯一索引。

                         ③、unique(id,name）：联合普通索引。

• 全文索引fulltext：用于搜索很长一篇文章的时候，效果最好。
• 空间索引spatial：了解就好，几乎不用

（8）创建索引的原则：

• 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
• =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
• 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
• 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

（9）索引优化：

• 索引分析：①、单表：有范围时，后边的索引失效。②、双表：左连接为右表建索引。③、三表：参考②。

                        结论：永远用小的结果集驱动大的结果集。

                        优先优化NestedLoop内层循环。

                        保证Join语句中被驱动表上Join条件字段已被索引。

                        当无法保证上述join字段时，当内存允许的条件下，不要太吝啬joinBuffer字段的设置。

• 索引失效（应该避免）：

        ①全值匹配我最爱。最佳左前缀法则。

        ②不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描。

        ③存储引擎不能使用索引中范围条件右边的列

        ④尽量使用覆盖索引（只访问索引的查询），减少select *。

        ⑤MySQL在使用不等于（！=或<>）的时候无法使用索引会导致全表扫描。

        ⑥is null,is not null也无法使用索引。

        ⑦like已通配符开头，MySQL索引会失效会变成全表扫描。（%右边的是会进行rang索引的同是不同于>它会后面的索引不会失效。同时当使用左%时，想使用索引直接从索引缓存中查询即覆盖索引）

        ⑧字符串不添加单引号索引会失效。

        ⑨少用or,用它来连接时会索引失效。

总结：定值、范围还是排序，一般order by是给个范围。group by基本上都是需要排序，会有临时表产生（如果错乱时）

• 一般性建议：在where查询条件中条件不安装索引的顺序排列查找和顺序排列查找的效果相同，原因是：MySQL自身会对sql进行优化。（都是常量的提前）

        ①对于单间索引，尽量选择针对当前query过滤性更好的索引。

        ②在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠左越好。

        ③在选择组合索引的时候，尽量选择可以包含当前query中的where子句中更多字段的索引。

        ④尽可能通过分析统计信息和调整query的写法来达到选择合适索引的母的

（10）创建索引的注意点？

• 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
• 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

（11）聚集索引和非聚集索引的区别

• “聚簇”：就是索引和记录紧密在一起。聚簇索引：物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列

CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH ALLOW_DUP_ROW(允许有重复记录的聚簇索引)

• “非聚簇索引”：索引文件和数据文件分开存放，索引文件的叶子页只保存了主键值，要定位记录还要去查找相应的数据块。

CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)