mysql及其优化

InnoDB 存储引擎

特点：行锁设计、支持外键、支持非锁定读 使用next-key-locking 的策略避免幻读现象 提供插入缓冲、二次写、自适应哈希索引、预读 采用聚集的方式存储表中数据

分布式中，主键一般设置为雪花算法全局唯一id，聚簇索引叶子节点包含数据，非聚簇索引叶子节点包含了聚簇索引的主键；

事务

事务的四种隔离级别

1. Read uncommitted 读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

2. Read committed 读已提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

3. Repeatable read

   可重复读，就是在开始读取数据（事务开启）时，不再允许修改操作，存在幻读，mysql的innodb使用next-key-locking 的策略避免幻读现象

4. Serializable 序列化 Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较 耗数据库性能，一般不使用。 在MySQL 数据库中， 支持上面四种隔离级别， 默认的为Repeatable read (可重复读)；而在Oracle 数据库中，只支持Serializable(串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed 级别。

springboot之事务机制

REQUIRED

spring的默认传播行为。 作用： 如果业务方法执行时在一个事务中，则加入当前事务，否则则重新开始一个事务。外层事务提交了，内层才会提交。内/外只要有报错，他俩会一起回滚。 栗子：

内层不存在事务，外层存在事务，即加入外层的事务，不管内层，外层报错，都会回滚事务。

只要内层方法报错抛出异常，即使外层有try-catch，该事务也会回滚！

条件：外层正常try-catch内层，内层出错。 结果：事务回滚，内层外层都回滚。

REQUIRES_NEW

作用： 每次都是创建一个新事物，如果当前已经在事务中了，会挂起当前事务。 内层事务结束，内层就提交了，不用等着外层一起提交。 外层报错回滚，不影响内层。 内层报错回滚，外层try-catch内层的异常，外层不会回滚。 内层报错回滚，然后又会抛出异常，外层如果没有捕获处理内层抛出来的这个异常，外层还是会回滚的。 栗子一： 内层正常，外层报错。 结果：内层提交，外层回滚。

NESTED

作用： 如果当前已经在一个事务中了，则嵌套在已有的事务中作为一个子事务。如果当前没在事务中则开启一个事务。 内层事务结束，要等着外层一起提交。

如果只是内层回滚，外层try-catch内层的异常，不影响外层。非try-catch则影响外层回滚

索引

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实 质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

从物理存储角度

1、聚集索引（clustered index） 2、非聚集索引（non-clustered index）

从逻辑角度

1、主键索引：主键索引是一种特殊的唯一索引，不允许有空值 2、普通索引或者单列索引 3、多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中 使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合 4、唯一索引或者非唯一索引 5、全文索引：全文索引是对空间数据类型的字段建立的索引，MYSQL 中的空间数 据类型有4 种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

sql

from--->where--->group by--->having--->计算所有的表达式--->order by-- ->select 输出

聚合函数：max()、min()、avg()、count()

SELECT gender, AVG(age) avg_age FROM staffs GROUP BY gender;

分组条件，只能使用HAVING，where执行顺序先于groupby，查询原表，不可使用聚合函数；

SELECT gender, COUNT(1) num FROM staffs where age>20 GROUP BY gender HAVING gender = 'male';

union和unionall

使用union和union all必须保证各个select 集合的结果有相同个数的列，并且每个列的类型是一样的，union和union all都是将两个结果集进行合并，区别是union会自动合并多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

**in，exists**

如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists会走子表索引，子查询表小的用in，会走外表索引；

其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 

是否走索引

exists，not exists 以外表驱动，会遍历外表，全表扫描，内表只判断是否匹配，无结果集，故内表可建立索引，走索引；

in和not in会根据数据大小自动决定是否走索引；

尽量用not exist不用not in，若子查询有null值结果可能为空；

sql优化及数据库优化

sql语句优化

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

2.应尽量避免索引失效，如：

在where 子句中使用!=或<>操作符，/,*运算符；

在where 子句中对字段进行null 值判断；

在where 子句中使用or 来连接条件；

使用like '%abc%'中的%；

in 和not in 也要慎用，对于连续的数值，能用between 就不要用in 了；

如果在where 子句中使用参数，也会导致全表扫描，可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num；

避免在where 子句中对字段进行函数操作；

复合索引查询遵从最左原则；

3.避免在大量重复列做索引，无意义；

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

什么时候建索引

1. 列类型尽量定义成数值类型，且长度尽可能短，如主键和外键，类型字段等等

2. 建立单列索引

3. 根据需要建立多列联合索引

4. 如果在多个列上建立索引，那么多个列的区分度就大多了，将会有显著的效率提高。 (1) 根据业务场景建立覆盖索引只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率 (2) 多表连接的字段上需要建立索引，这样可以极大提高表连接的效率 (3) where 条件字段上需要建立索引 (4) 排序字段上需要建立索引 (5) 分组字段上需要建立索引

mysql优化分片键 1.添加缓存，读写分离，延迟问题用（虽然很小）数据库同步方案（配置写操作同步完才可读），强制读主库（sharding-jdbc），等GTID 方案

首先介绍一下 GTID，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由MySQL 实例的uuid和一个整数组成，该整数由该实例维护，初始值是 1，每次该实例提交事务后都会加一。

MySQL 提供了一条基于 GTID 的命令，用于在从节点上执行，等待从库同步到了对应的 GTID（binlog文件中会包含 GTID），或者超时返回。elect wait_for_executed_gtid_set(gtid_set, timeout);MySQL 在执行完事务后，会将该事务的 GTID 会给客户端，然后客户端可以使用该命令去要执行读操作的从库中执行，等待该 GTID，等待成功后，再执行读操作；如果等待超时，则去主库执行读操作，或者再换一个从库执行上述流程。

2.如果使用分库分表，那么查询条件务必先走分片键，否则就成了全表查询，性能超低

3、优化缓存，分离冷热数据，对于大内存访问评率低的数据适当分离提高缓存命中率，命名问题