MySQL事务与索引优化-优快云博客

本文链接：https://blog.youkuaiyun.com/Dark_orange/article/details/89892680

MySQL事务处理的特性

原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。
隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务的并发问题

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

　　小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

索引

索引是什么：索引是为了加快查找速度的一种数据结构。

索引的作用：高效查找数据。

索引的结构：MyISAM和InnoDB都是采用B+树作为数据结构。

下面是B+tree的建树过程

依次插入
6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5

虽说MyISAM与InnoDB的数据结构都是B+树，但是其实现方式缺截然不同，具体的索引实现详见博客：https://blog.youkuaiyun.com/b_x_p/article/details/86434387

*MYSQL为什么最终要去选择B+TREE？***

1、B+TREE是B TREE的变种，B TREE能解决的问题，B+TREE也能够解决（降低树的高度，增大节点存储数据量）

2、 B+TREE扫库和扫表能力更强，如果我们要根据索引去进行数据表的扫描，对B TREE进行扫描，需要把整棵树遍历一遍，而B+TREE只需要遍历他的所有叶子节点即可（叶子节点之间有引用）。

3、B+TREE磁盘读写能力更强，他的根节点和支节点不保存数据区，所有根节点和支节点同样大小的情况下，保存的关键字要比B TREE要多。而叶子节点不保存子节点引用。所以，B+TREE读写一次磁盘加载的关键字比B TREE更多。

4、B+TREE排序能力更强，如上面的图中可以看出，B+TREE天然具有排序功能。

5、B+TREE查询效率更加稳定，每次查询数据，查询IO次数一定是稳定的。当然这个每个人的理解都不同，因为在B TREE如果根节点命中直接返回，确实效率更高。

什么情况适合建立索引：

表的主键和外键需要建立索引；
数据量超过300的需要建立索引；
经常出现在where字句中的字段；
经常用于连接两张表的列；
经常需要范围查找的列，因为索引已经排序，所以指定的范围是连续的；
经常需要排列的列，理由同上；

索引的缺点：

更新数据时需要对索引进行更新；
索引需要占用一定的空间。

数据库优化的思路

这个我借鉴了慕课上关于数据库优化的课程。

1.SQL语句优化

1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

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

select id from t where num=0

3）很多时候用 exists 代替 in 是一个好的选择

4）用Where子句替换HAVING 子句因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

2.索引优化

看上文索引

3.数据库结构优化

1）范式优化：比如消除冗余（节省空间。。）

2）反范式优化：比如适当加冗余等（减少join）

3）拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。

4）拆分其实又分垂直拆分和水平拆分：案例：简单购物系统暂设涉及如下表： 1.产品表（数据量10w，稳定） 2.订单表（数据量200w，且有增长趋势） 3.用户表（数据量100w，且有增长趋势）以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万垂直拆分：解决问题：表与表之间的io竞争不解决问题：单表中数据量增长出现的压力方案：把产品表和用户表放到一个server上订单表单独放到一个server上水平拆分：解决问题：单表中数据量增长出现的压力不解决问题：表与表之间的io争夺