MySql

Mysql

一、  事务

事务就是一组原子性的SQL查询，或者说是一个独立的工作单元。

开启事务 START  TRANSACTION

提交事务 COMMIT

1、   事务原则ACID

1）A (atomicity)原子性

一个事务必须被视为一个不可分割的最小工作单元，整个事务中所有的操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不不可能只执行其中的一部分操作，这就是事务 的原子性。

2）C(consistency)一致性

3）I(isolation) 隔离性

通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。

4）D(durability)持久性

一旦事务提交，则其所做的修改就会永久保存到数据库中。

2、  事务并发访问的问题

1）  脏读

读取了未提交的事务，相当于读错了。

2） 不可重复读

每次的查询结果不一样，修改其中的一条数据。

3） 幻读

读取的数据多了一条。

3、隔离级别

1）   READ UNCOMMITTED（未提交读）

事务可以读取未提交的事务，也被成为脏读。

2） READ COMMITTED（提交读，也叫不可重复读）

产生不可重复读

3）  REPEATABLE READ（可重复读）

解决了了脏读的问题。该级别保证了了在同一事务中多次读取同样记录的结果是一致的。但无法解决幻读（Phantom Read）的问题。Mysql默认的隔离级别

4）  SERIALIZABLE（可串行化 几乎不用）

 

4、 模拟4种隔离级别

切记开始时，要关闭⾃自动提交
> set session autocommit=0;
> show variables like 'autocommit';

set session transaction isolation level read uncommitted;//设置隔离级别    select @@tx_isolation; //查询设定的隔离级别

use test; //A选择数据库

start transaction;//开启事务

commit ;//提交事务

5、 spring事务的传播行为

1）   REQUIRED

业务方法需要在一个事务中运执行。如果方法运行时，已经处在一个事务中，那么这个时候就会加入到该事务中，如果当前没有事务环境的话，就会为自己创建一个新的事务。

2）SUPPORTS

这一事务属性表明，如果业务方法A在某个事务范围内被调用，则方法成为事务的一部分。

3） MANDATORY

该属性指定业务方法只能在一个已经存在的事务中执行，业务方法不能发起自己的事务。如果业务方法在没有事务的环境下调用，容器就会抛出异常。一种比较强硬的方式。

4）REQUIRES_NEW

该属性表明不管当前是否存在事务，业务方法总会为自己发起一个新的事务。

5）NOT_SUPPORTED

声明方法不需要事务。

6）NEVER

指定业务方法绝对不能在事务范围内执行。如果业务方法在某个事务中执行，容器会抛出异常，只有业务方法没有关联到任何事务，才能正常执行。比较强硬的方式，就是不支持事务。

7）  NESTED

（嵌套事务）如果一个活动的事务存在，则当前方法运行在一个嵌套的事务中。 如果没有活动事务，就创建一个新的事务。它使用一个单独的事务，这个事务拥有多个可以回滚的保存点。内部事务的回滚不会对外部事务造成影响。外部事务回滚会导致内部事务的回滚。

二、索引

1、索引的定义

1）索引是按照特定的数据结构把数据表中的数据放在索引文件中，以便于快速查找；
2）索引存在于磁盘中，会占据物理空间。

2、索引的类型

1） 全文索引

目前只有MyISAM引擎⽀支持。它的出现是为了解决WHERE name LIKE“%word%"这类针对⽂文本的模糊查询效率较低的问题

2） HASH

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。 HASH索引可以一次定位，不不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高

3） BTREE

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常⽤用的索引类型。

4）RTREE

RTREE在MySQL很少使用，仅支持地理数据类型， Mysql几乎不用。

3、索引的类型

1）主键索引

2）唯一索引

3）普通索引

4）组合索引 INDEX(A,B,C)

只有 A AB ABC

4、索引的操作

三、存储引擎中索引的实现

1、索引和存储引擎的关系

1）索引是在存储引擎中实现的。

2）不同的存储引擎可能支持不同的索引类型

3） 不同的存储引擎对同一种索引可能有不同的实现方式。

2、 InnoDb 存储引擎

1） B加树

又根节点、树节点和叶子节点；非叶子节点不存数据。只存指针。叶子结点都是一个双向链表

 

InnoDB里面有两种索引，一种是主键索引

存的是id+数据，根据id查询时，采用二分法。

 另一种是非主键索引：如 name，存主键索引的id,拿到主键索引的id,有一次回表操作，拿到主键id再去查询。

 

2）B减树

B减树 叶子节点和非叶子节点都可以存数据

思考：InnoDB为什么用B加树 不用B减树

如果同样的数据用B减树，B减树数据的高度会变高，树的高低影响磁盘的IO，访问的时间会更高。

3）MyISAM索引实现

 

四、慢sql

1、 为什么要对慢sql进行治理

1） 每个SQL执行都需要消耗一定I/O资源， SQL执行行的快慢，决定资源被占用时间的长短。

2）  Sql时间长，意味着等待，用户体验差，优化的优先级主库>从库

2、排查慢sql

1)    找到慢sql

show variables like "%slow%"; 是否开启慢查询;
show status like "%slow%"; 查询慢查询SQL状况;
show variables like "long_query_time"; 慢查询时间

慢查询开启设置:
mysql> show variables like 'long_query_time'; 默认情况下， mysql认 为10秒才是一个慢查询
mysql> set long_query_time=1; 修改慢查询时间,只能当前会话有效;
mysql> set global slow_query_log='ON'; 启用慢查询 ,加上global，不不然会报错的

查询mysql的操作信息**show status** 显示全部mysql操作信息
show status like "com_insert%"; 获得mysql的插⼊入次数;
show status like "com_delete%"; 获得mysql的删除次数;
show status like "com_select%"; 获得mysql的查询次数;
show status like "uptime"; 获得mysql服务器器运行时间
show status like 'connections'; 获得mysql连接次数

2)    定位到mysql进行优化

explain select * from emp where name =

 

 

 

 

3、避免慢sql

1)    避免使用子查询

2)    避免函数索引

3)    用in代替or

4)    Like 双百分号无法用索引

5)    读取适当的记录limit 0,10

6)    分组统计禁止排序

7)    禁止不必要的orderBy排序

8)    正确使⽤用组合索引 index （a,b,c）。 a |a,b |a,b,c|，最左匹配原则。

五、分库分表

1、为什么要分库分表

当数据量达到2000W 或者 100G, 查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。为了减少数据库负担，缩短查询时间。

2、分库分表的方式

1）垂直（纵向）切分

1>包括垂直分库和垂直分表。

① 垂直分库：

把不同的业务库拆到不同的业务库里面。与"微服务治理"的做法相似，每个微服务使用单独的一个数据库。例如包括订单、商品等。

② 垂直分表：当一张表很多字段，五六十个字段，可以新建一张扩展表，大表拆小表。

2>优缺点

   优点：①解决业务系统层面的耦合，业务清晰, 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
        ② 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

缺点：

①   部分表无法join，只能通过接口聚合方式解决，提升了了开发的复杂度

②  分布式事务处理理复杂
依然存在单表数据量量过大的问题（需要水平切分）

2）水平（横向）切分

当单个表数据量过大时，需要横向切分，水平切分分为库内分表和分库分表，

水平切分的优缺点：

优点：① 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力。② 应⽤用端改造较小，不需要拆分业务模块

 缺点：① 跨分片的事务一致性难以保证，跨库的join关联查询性能较差 。 ② 数据多次扩展难度和维护量极大。 

3）分片规则

1>  根据数据范围

按照时间区间或ID区间来切分。

优点：①单表大小可控

②  便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可；

③  无需对其他分片的数据进行迁移

④  使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询的问题

  缺点：热点数据成为性能瓶颈

 

2>  根据数值取模

通过取模分到不同的表中。

优点：数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈。

缺点：① . 后期分片集群扩容时，需要迁移旧的数据（使用一致性hash算法能较好的避免这个问题）

一致性Hash 逻辑

对 2的32次幂取模，顺时针找到一个机器，尽可能小的影响数据，一致性Hash算法对于节点的增减都只需重定位环空间中的一小部分数据，有很好的容错性和可扩展性。

https://www.jianshu.com/p/528ce5cd7e8f

②  增加复杂度，如果根据其它字段查询，需要同时访问四张表。

如果查询条件中，不带Hash字段查询，如何查询：1）做数据冗余，在写数据的时候记一条对应关系，存到mysql或者redis里面，找到对应关系再去查询。

3、分库分表带来的问题

1）事务一致性问题

最终一致性，通过补偿机制。

2） 跨节点关联查询问题

解决方案：

1>  全局表，即数据库字典表

2>   字段冗余

3>   数据组装

在系统层面，分两次查询，第一次查询的结果集中找出关联数据id，然后根据id发起第二次请求得到关联数据。最后将获得到的数据进行字段拼装。

3） 跨节点分页、排序、函数问题

跨节点多库进行查询时，会出现limit分⻚页、 order by排序等问题。分页需要按照指定字段进行排序，当排序字段就是分片字段时，通过分片规则就比较容易定位到指定的分片；当排序字段非分片字段时，就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序，最终返回给⽤用户。

4、 全局主键避免重复问题

1）  uuid

2）  结合数据库维护主键id表

用两个数据库，数据库改为步长为2。

3）Snowflake分布式⾃自增ID算法（用的比较多）

 

①  第一位未使用

②   接下来41位是毫秒级时间， 41位的长度可以表示69年的时间

③  5位datacenterId， 5位workerId。 10位的长度最多⽀持部署1024个节点

④  最后12位是毫秒内的计数， 12位的计数顺序号⽀支持每个节点每毫秒产生4096个ID序列

     雪花算法的优缺点：

     优点：整个分布式系统不会产生ID碰撞，

不足就在于：强依赖机器时钟，如果时钟回拨，则可能导致生成ID重复。

美团的分布式ID生成策略，考虑到了高可用、容灾、分布式 推荐使用。

https://tech.meituan.com/2017/04/21/mt-leaf.html

 六、Mycat分库分表

1、常见应用场景:

1）单纯的读写分离，此时配置最为简单，支持读写分离，主从切换；
2分表分库，对于超过 1000 万的表进行分片，最大支持 1000 亿的单表分片；
多租户应用，每个应用一个库，但应用程序只连接 Mycat，从而不改造程序本身，实现多租户化；

2、具体步骤