MYSQL优化问题

1.事务

1. 特性
   1. 原子性<数据库事务不可分割的单位。要么全部成功，要么全部失败>
   2. 隔离性<事务是相互不可见的>
   3. 一致性<事务的操作是不会改变数据库的状态，比如说唯一约束>
   4. 持久性<事务一旦提交，即使宕机也是可以恢复的>
2. 分类
   1. 扁平事务<使用最频繁的事务。要么都成功提交，要么都失败回滚>
   2. 带有扁平点的扁平事务<允许事务回滚到同一个事务中比较早的一个状态>
   3. 链事务<会滚到最近的一个保存点，在所有的事务都提交之后，才会释放锁，并且下一个事务开始需要上一个事务来进行通知>
   4. 嵌套事务<树结构，只有当父级事务提交之后子级事务才会提交，任意一个父级事务回滚都会导致下面子级事务的回滚>
   5. 分布式事务<操作两个不同的数据库，使其实现的数据同步>
3. 隔离级别
   1. 脏读（read uncommited）:事务A读取了事务B更新的数据，然后事务B回滚，那么事务A读到的就是脏数据。
   2. 不可重复读（read commited）：事务A多次读取同一数据，在读取的过程中，事务B对事务A读取的数据进行更新并提交，导致事务A多次读取数据，结果不一样。
   3. 幻读（repeatable read）：系统管理员A将将数据库中所有学生的具体成绩改为按照ABCD分等级，但是系统管理员B就在这个时候新增一条具体分数的记录，当系统管理员A操作结束后，发现还有一条数据没有改过来，就像是发生幻觉一样，这就是幻读。
   4. serializable：锁表，不会出现意外情况

2.锁

1. lock

共享锁：允许事务读取一行数据
排它锁：允许事务更新或者删除一条数据
意向共享锁：事务想要获取一张表某几行的共享锁
意向排它锁：事务想要获得一张表的某几行的排它锁

2.latch：轻量级锁，锁的时间非常短，用来操作临界资源

3.一致性的非锁定读

4.死锁

3.sql优化原则

1.选择需要优化的SQL

2.Explain和Profile入手
        1.任何sql的优化，都从Explain开始；Explain语句能够得到数据库执行sql选择的执行计划；

        2.首先明确需要的执行计划，在使用Explain检查；

        3.使用Profile明确SQL的问题和优化的结果；

3.永远用小结果集驱动大的结果集；

4.在索引中完成排序

5.使用最小的Columns；

6.使用最有效的过滤条件；

7.避免复杂的JOIN和自查询；

4.JOIN原理

1.JOIN的原理：

        在mysql中使用Nested Loop Join来实现join；A join B：通过A表的结果集作为循环基础，一条一条的通过结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果集。

2.JOIN的优化原则：

        1.尽可能的减少join语句中的Nested Loop 的循环总次数，用小结果集驱动大结果集。

        2.优先优化Nested Loop的内层循环；

        3.保证Join语句中被驱动表上Join条件字段已经被索引；

        4.扩大Join buffer的大小

5.执行计划和执行明细

1.Explain：可以让我们查看MYSQL执行一条SQL所选择的执行计划

2.Profile：可以用来准确定位一条SQL的性能瓶颈

6.执行流程

1.查询缓存

2.解析器生成解析树

3.预处理再次生成解析树

4.查询优化器

5.查询执行计划

6.查询执行引擎

7.查询数据返回结果

7.表结构对性能的影响

1.冗余数据的处理：可以提高系统整体的查询性能（三范式）

        1.每一列只能有一个值

        2.每一行可以被唯一区分

        3.不包含其他表的已包含的非关键信息

2.大表拆小表

        1.一般不会设计属性过多的表

        2.一般不会超过500-1000万数据的表

        3.有大数据的列单独拆成小表

3.根据需求展示更加合理的表结构

4.常用属性分离为小表

8.索引

1.类型

        1.Normal：普通的索引，允许一个索引值后面关联多个行值

        2.unique：唯一索引，允许一个索引后面只能有一个行值；之前对列添加唯一索引其实就是为这列添加个unique索引；当我们为一个表添加一个主键的时候，其实就是为这个表主键列设置了非空约束，并为主键列添加了一个唯一索引。

        3.Fulltext：全文检索，mysql的全文检索只能用Myisa引擎，并且性能较低，不建议使用。

2.方法

        1.b-tree：是一棵树，（二叉树、平衡二叉树、平衡树）；使用平衡树实现索引，是mysql中使用最多的索引类型，在innodb中，存在两种索引类型，第一种是主键索引（primary key），在索引内容中直接保存数据的地址；第二种是其他索引，在索引的内容中保存的是指向主键索引的引用，所以在使用innodb的时候，尽量使用逐渐索引，速度非常快。

        2.hash：把索引的值做hash运算，并存放到hash表中，使用较少，一般在memory引擎中使用；优点：因为使用hash表存储，按照常理，hash表的性能比B-TREE的性能高很多。缺点：1.hash索引只能适用于精确的值比较，=、<>、in；无法使用范围查询；2.无法使用索引排序；3.组合hash索引无法使用部分索引；4.如果大量索引hash值相同，性能较低。

3.创建

        1.较频繁的作为查询条件的字段应该创建索引

        2.唯一性太差的字段不适合单独创建索引，即使用频繁作为查询条件，作为索引的列不能有效的区分数据，那么这个列就不适合做索引。

        3.更新非常频繁的列不适合做索引，因为索引有维护成本

        4.不会出现在where子句中的字段不该创建索引

        5.索引不是越多越好，只为必要的列创建索引。不管有多少个索引，一次查询至多采用一个索引（索引和索引之间是相互独立的）；因为索引之间是相互独立的，所以说每个索引都需要单独维护，即在数据的crud中，都需要对所有的索引进行单独维护。