• READ UNCOMMITED (未提交读)

  在 read uncommited级别, 事务中的修改,即使没有提交,对其他事务也都是可见的, 事务可以读取未提交的数据,这也被称为脏读(DIrty Read).这个级别会导致很多问题,从性能上说, Read Uncommited 不会比其他的级别好太多,但缺乏其他级别的很多好处,除非真的非常必要的理由,在实际应用中一般很少使用

• READ COMMITED (提交读)

  大多数数据库系统的默认隔离级别都是 Read Commited (但MySQL不是), Read Commited满足前面提到的隔离性的简单定义: 一个事务开始时,只能 "看见" 已经提交的事务所做的修改, 换句话说, 一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的. 这个级别有时候也叫做不可重复读 (nonrepeatable read),因为两次执行同样的查询,可能会得到不一样的结果

• Repeatable Read (可重复读)

  Repeatable Read 解决了脏读的问题,该级别保证了同一个事务多次读取同样的记录的结果是一致的, 但是理论上,可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题, 所谓幻读, 指的是当某个事务再次读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行(Phantom Row), InnoDB 和 XtraDB 存储引擎通过多版本并发控制(MVCC)解决了幻读的问题

  可重复读是 MySQL的默认事务隔离级别

• Serializable (可串行化)

  Serializable 是最高的隔离级别, 它通过强制事务串行执行, 避免了前面说的幻读的问题,简单来说,Serializable会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用的问题,实际应用中也很少用到这个隔离级别,只有在非常需要确保数据的一致性而且可以接受没有并发的情况下,才考虑采用该级别

  叶落山城秋: 总是说上面这些东西,今天才认真看了下,结果看懵逼了.. 脏读很好理解,就是可能读取了别的事务还没提交的数据(这数据可能是无用的数据,可能要回滚的). 不可重复读也好理解,就是两次读的数据结果可能不一样,比如我正在事务里查询记录,第一次查 是2条数据,然后我还没提交事务,另外一个事务插入了新的数据,然后我再查了一遍,发现这时候是三条数据了.. 此时我事务还没提交! 这就是不可重复读! 可重复读正好相反,只要我查询还在同一个事务里,结果永远是一样的!不管真实数据是否已经修改了..

  不过这个幻读.. 我看了书还有各种说法,说法不一..

  一种说法是 A查询数据是两条,B查询也是两条,然后A插入了一条数据, B还在之前的事务里,因为是可重复读,所以B再次查询还是原来的数据,但是B想插入第三条(id = 3)的数据,发现插入不了,但是查询还是两条数据..

  另外一种说法是 A把数据表某个字段的数据都从"1"改成了"2",与此同时,B往表里插入了新的数据,这个字段的值依然是"1"插入的,等A更新完后,再次查询,发现表里还有数据未改变..

  参考:

  go 夜读

  高性能MySQL

  MySQL的四种事务隔离级别

• InnoDB支持事务,Myisam不支持事务
• InnoDB支持行级锁,Myisam支持表级锁
• InnoDB支持外键,Myisam不支持
• InnoDB不支持全文索引(这个看版本),Myisam支持

• InnoDB是索引组织表,Myisam是堆表

  叶落山城秋: Myisam和InnoDB索引结构都是B+tree, 来,了解一下 树,二叉树,平衡二叉树,红黑树,B树等逐步深入了解 Myisam的索引叶子节点data里存的是数据记录的地址,数据在另外的地方,数据查询先根据索引找到数据记录的位置,再去位置找到数据记录! 这种好处就是写入操作性能比较好,但是查询得先查询数据位置,再查找数据,至少需要两次io

  InnoDB的索引叶子节点data里存的就是数据本身,这样不好的地方在于所有的插入都可能会更新树结构,性能会有些影响,但是查询会快很多,只有找到索引也就找到了数据..

  写到这里, 我特地翻了一下书(以前从未翻过), 先来说一下主键索引和非主键索引, InnoDB的主键索引上所有的叶子节点存的是 主键对应的数据, 直接就是数据, InnoDB的非主键索引存的是当前索引键和对应的主键, 比如 非主键索引是 "name",先根据name查询对应的主键,然后再去主键索引的树上找对应的值,所以这时候是需要两次IO的! Myisam的主键索引和非主键索引叶子节点存的都是索引对应的数据记录的地址,获得地址后,再去硬盘上该地址去获取数据,所以一般是需要两次IO的..

  以上正好就是 聚簇索引和非聚簇索引的区别了..

10s

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助 选择更好的索引和写出更优化的查询语句。

Profile用来分析SQL性能的消耗分布情况。当用explain无法解决慢SQL的时 候，需要用profile来对SQL进行更细致的分析，找出SQL所花的时间大部分消耗在 哪个部分，确认SQL的性能瓶颈。

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。

视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。

创建视图：create view XXX as XXXXXXXXXXXXXX;

对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 delete删除表中数据，可以加where字句。

• DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

• 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

• 一般而言，drop > truncate > delete

• 应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view

• TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

• truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

• delete语句为DML（Data Manipulation Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

• truncate、drop是DDL（Data Define Language),操作立即生效，原数据不放到 rollback segment中，不能回滚

• 在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老是想触发trigger,还是用delete。

• Truncate table 表名 速度快,而且效率高,因为: truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

• TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

• 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

例如有两个表

表A:

表B:

表A的ID与表B的UID业务关联

注意: 表A的AID=3 在 表B里没有关联

• 内连接( join或者 inner join)

  返回符合条件的tableA和tableB的数据

   Select * from tableA inner join tableB where tableA.AID = tableB.UID
  

  结果:

• 左连接(left join)

  左连接是如果表A和表B查询的时候,先获得表A所有的数据,然后根据表A的每一条数据匹配B里的数据,如果没有,就列为空值(null)

   select * from tableA left join tableB on tableB.UID = tableA.AID
  

  结果:

左表的数据全部查到,如果在表B里没有找到,那么表B的字段列为空值

• 右连接 (right join)

  与左连接正好相反,获取表B的数据,然后匹配A表的数据,如果没有的,列为空值(null)

   select * from tableA right join tableB on tableB.UID = tableA.AID
  

  结果:

右表的数据全部查到,如果在表A里没有找到,那么表A的字段列为空值

• 完整外部连接

  MySQL不支持完整外部连接

• 交叉连接 (cross join)

  不带where条件的查询条件会产生连接所涉及的表的笛卡尔积

   select * from tableA cross join tableB
  

  等同于:

   Select * from tableA,tableB
  

  结果:

1. 第一范式（1NF）

   在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。 所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。

2. 第二范式（2NF）

   第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。 第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

3. 第三范式（3NF）

   满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。（我的理解是消除冗余）

   总结一下:

   ​ 第一范式：要求有主键，并且要求每一个字段原子性不可再分

   ​ 第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖

   ​ 第三范式：所有非主键字段和主键字段之间不能产生传递依赖

叶落山城秋: 我看了一些文章和书籍,感觉还是 高性能MySQL 这本书的目录总结的好,下面是我个人的感觉的总结....... 可能不准确啊.. 说的很宽泛

• 创建高性能的索引
  • 这里面就多了,根据业务设计符合业务的索引
• 查询性能优化
  • 查询之前,设计表的时候应该根据范式和业务结合设计一个好的表
  • 查询语句使用得当,根据索引能获得较大的效果
• 优化服务器设置
  • 书还没看到这.. 根据目录大约是 根据使用情况修改MySQL默认的配置文件
  • 根据实际情况可做一些负载的设置
  • 配置内存使用等等

触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。

触发器是在一个修改了指定表中的数据时执行的存储过程。

通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。

触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

定义: 如果一个线程(进程)集合中的每一个线程(进程)都在等待只能由该线程(进程)集合中的其他线程(进程)才能引发的事件,那么该线程(进程)集合是死锁的!

预防死锁:

• 破坏占有等待条件
  • 对于需要获取多个资源的线程,一次性获取全部资源,而不是依次获取各个资源
• 破坏环路等待条件
  • 死锁集合的线程按照等占有线程和等待线程可以组成有向环图,那么如果对所有资源进行排序,所有线程按照资源顺序获取资源

https://segmentfault.com/a/1190000003072424

https://tech.meituan.com/performance_tunning.html

• 待补充

• 当一个表有多条索引可走,那么会根据优化器的查询成本来选择走哪个索引

• 联合索引的本质:

  • 当创建(a,b,c)联合索引时,相当于创建了(a)单列索引,(a,b)联合索引以及(a,b,c)联合索引
  • 想要索引生效,只能使用 a和a,b和a,b,c三种组合,a,c也可以用,但是只用到了a

• 需要加索引的字段,要在where条件中

• 数据量少的字段不需要加索引,因为建索引有一定开销,如果数据量小则没有必要建索引(速度反而慢)
• 如果where条件中有OR关系,加索引不起作用(此条针对联合索引,如果OR两边是单独的索引..可就不一样了)
• 联合索引对比每个列分别建立索引更有优势,因为索引建立的越多就越占磁盘空间,在更新数据的时候速度会更慢,另外,建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高

• and 条件

  当条件是and的时候,并不会三个索引都会去命中,一般会命中其中一个!具体是哪个不一定,MySQL优化器会评估三个条件的索引哪个最高,它就会优先选择最佳的索引去使用

  不过得注意,如果其中一个是主键索引,应该走的就是主键...但是如果有走了覆盖索引(不用回表,估计更快,没测试..猜测...).. 那就不一定了....

  如果只有 where a=x1 and b=x2 只有一个索引生效(是哪个不一定,看MySQL优化器的选择了)

• or 条件

  当条件是or的时候,那情况就相当复杂了...

  如果 abc是联合索引,用or会导致索引失效,但是如果是单列索引,可能三个索引都会用上

  用explain可以看到 Extra显示Using union(a_index,b_index,c_index); Using where,我MySQL版本是5.6

  查询了下网上,MySQL5.0版本之前,使用or只会使用一个索引,但是5.0之后,开始引入index_merge索引合并优化

  index_merge(explain里type的值)作用:

  • 索引合并是把几个索引的范围扫描合并成一个索引
  • 索引合并的时候,会对索引进行并集,交集或者先交集再并集操作,以便合成一个索引
  • 这些需要合并的索引只能是一个表的,不能对多表进行索引合并

• 另外,注意如果数据量非常小,可能就不走索引了...还不够费劲的

这个是同事刚需要在orm里写原生SQL的时候用到了MySQL内置函数,那么我就想试试,内置函数+索引字段 会不会走索引

比如: select max(a) from table

explain后 Extra显示 Select tables optimized away,优化到已经不能再优化了..

理解意思是MySQL的索引数据结构是B+数,一般叶子节点存的就是已经排序好的数据,这时候获取最大的a值,其实直接在叶子节点拿即可,不需要走索引,优化器已到最优

所以explain的时候 type显示的是 NULL

不过在我测试这个的时候,发现一个有意思的事情,如果where条件后有个范围查询,查询的字段也建立了索引,当数据量大到一定程度(貌似是总数一半)后,就会扫描全表

如果达不到,就走索引!(我简单测试了下,是这样的..具体是不是请自行测试吧..)

• 不要使用in操作符，这样数据库会进行全表扫描

  在业务密集的SQL当中尽量不采用IN操作符

• not in 使用not in也不会走索引

  用not exists或者（外联结+判断为空）来代替

• <> 操作符（不等于） 使用<>同样不会使用索引，因此对它的处理只会产生全表扫描

  用其它相同功能的操作运算代替，如 a<>0 改为 a>0 or a<0

  a<>改为 a>

• IS NULL 或IS NOT NULL操作（判断字段是否为空

  判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。

  推荐方案：用其它相同功能的操作运算代替，如

  a is not null 改为 a>0 或a>``等。

• (大于或小于操作符)>及< 操作符

  大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，

  如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。

  那么执行A>2与A>=3的效果就有很大的区别了，因 为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

  叶落山城秋:我个人测试了一下mysql,发现在数据大于总数一半左右的时候,就会扫描全表,并不会走索引... 我个人估计是这时候扫描全表比索引还快,所以不走索引了

• LIKE操作符

  LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，

  如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE '5400%'则会引用范围索引。

  可以采用substr(column,1,4)='5400'

• UNION操作符

  UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

  实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

  select * from gc_dfys

  union

  select * from ls_jg_dfys

  这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

  采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

 > 摘自: [https://www.cnblogs.com/wujianchao1988/archive/2012/05/16/2504221.html](https://www.cnblogs.com/wujianchao1988/archive/2012/05/16/2504221.html)

• 如果不清楚的人估计第一反应都是用 varchar 来存! 方便直观
• 不过在MySQL高性能这本书里推荐的是(unsigned int) 来存储,相比字符串:
  • 节省空间,不管是数据存储空间还是索引存储空间
  • 便于使用范围查询,且效率快
  • 就是在数据库里直观看,不是很方便
• IPv4用inet_aton 来将IP转成int,用 inet_ntoa 将int转回IP
• IPv6用inet6_aton 来将IP转成INT,用 inet6_ntoa 将int转回IP

关于

MySQL XA 事务通常用于分布式事务处理当中。比如在分库分表的场景下，当遇到一个用户事务跨了多个分区，需要使用XA事务 来完成整个事务的正确的提交和回滚，即保证全局事务的一致性。

XA 事务在分库分表场景的使用

下图是个典型的分库分表场景，前端是一个Proxy后面带若干个MySQL实例，每个实例是一个分区。

假设一个表test定义如下，Proxy根据主键”a”算Hash决定一条记录应该分布在哪个节点上：

create table test(a int primay key, b int) engine = innodb;

应用发到Proxy的一个事务如下：

begin;
insert into test values (1, 1);
update test set b = 1 where a = 10;
commit;

Proxy收到这个事务需要将它转成XA事务发送到后端的数据库以保证这个事务能够安全的提交或回滚，一般的Proxy的处理步骤 如下：

1. Proxy先收到begin，它只需要设置一下自己的状态不需要向后端数据库发送
2. 当收到 insert 语句时Proxy会解析语句，根据“a”的值计算出该条记录应该位于哪个节点上，这里假设是“分库1”
3. Proxy就会向分库1上发送语句xa start ‘xid1’，开启一个XA事务，这里xid1是Proxy自动生成的一个全局事务ID；同时原来 的insert语句insert into values(1,1)也会一并发送到分库1上。
4. 这时Proxy遇到了update语句，Proxy会解析 where条件主键的值来决定该条语句会被发送到哪个节点上，这里假设是“分库2”
5. Proxy就会向分库2上发送语句xa start ‘xid1’，开启一个XA事务，这里xid1是Proxy之前已经生成的一个全局事务ID；同时原来 的update语句update test set b = 1 where a = 10也会一并发送到分库2上。
6. 最后当Proxy解析到commit语句时，就知道一个用户事务已经结束了，就开启提交流程
7. Proxy会向分库1和分库2发送 xa end ‘xid1’;xa prepare ‘xid1’语句，当收到执行都成功回复后，则继续进行到下一步，如果任何一个分 库返回失败，则向分库1和分库2 发送 xa rollback ‘xid1’，回滚整个事务
8. 当 xa prepare ‘xid1’都返回成功，那么 proxy会向分库1和分库2上发送 xa commit ‘xid1’，来最终提交事务。

这里有一个可能的优化，即在步骤4时如果Proxy计算出update语句发送的节点仍然是“分库1”时，在遇到commit时，由于只涉 及到一个分库，它可以直接向“分库1”发送 xa end ‘xid1’; xa commit ‘xid1’ one phase来直接提交该事务，避免走 prepare阶段来提高效率。

XA对事务安全的影响分析

从以上分库分表场景下分布式事务的处理过程来看，整个分布式事务的安全性依赖是XA Prepare了的事务的可靠性，也就是在 数据库节点上 XA Prepare了的事务必须是持久化了的，这样当XA Commit发来时才可以提交。设想如下场景：

1. Proxy已经向分库1和分库2上发送完了 xa prepare ‘xid1’语句，并得到了成功的回复
2. Proxy向分库1上发送了 ‘xa commit ‘xid1’语句，并已经成功返回
3. 当 Proxy向分库2上发送 ‘xa commit ‘xid1’时，网络断开了，或者分库2的数据库实例被kill了
4. 当网络恢复（这时相关的Session已经退出了）或数据库实例再启动后（或切换到备库），XA prepare了的事务已经回滚了， 当Proxy XA commit ‘xid1’发过来后数据库实例根本找不到xid1这个xa事务

上面的过程就导致了分布式事务的不一致：分库1提交了事务，分库2回滚了事务，整个事务提交了一半，回滚了一半。

在MySQL 5.6中以上过程是可能发生的，因为xa prepare并没有严格的持久化，当Session断开，数据库崩溃等情况下这些事务 会被回滚掉，而且的当一个主库配置了SemiSync的备库时xa prepare了的事务也不会被发送的备库，如果主库切换到备库这些 事务也会丢失。

MySQL 5.7 XA可靠性改进

MySQL 5.7解决了 xa prepare了的事务的严格持久化问题，也就是在session断开和实例崩溃重启情况下这些事务不丢，同时在 xa prepare ‘xid1’返回之前XA事务也会同步到备库。下面将通过在5.6和5.7上分别执行xa prepare并对binlog event进行分析 来演示这个改进。

断开连接对xa prepare的事务影响

在5.6和5.7上分别执行如下sql然后断开连接，再重新连接使用的xa recover验证 XA 事务是否回滚了。

xa start 'xid1';
insert into test values(1, 1);
xa end 'xid1';
xa prepare 'xid1';
-- 这里断开再连上新连接执行 xa recover

在 5.6 的版本上将返回空的结果，在 5.7 的版本上返回：

mysql> xa recover;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
|        1 |            4 |            0 | xid1 |
+----------+--------------+--------------+------+
1 row in set (0.00 sec)

说明断开连接后 5.7的prepare了的xa事务没有丢失。

XA 事务的 Binlog events 异同

在5.6和5.7上分别执行如下事务，然后用 show binlog events 查看两者binlog的不同：

xa start 'xid1';
insert into test values(1, 1);
xa end 'xid1';
xa prepare 'xid1';
xa commit 'xid1';

5.6的结果：

mysql-bin.000001 | 304 | Gtid           |      3706 |         352 | SET @@SESSION.GTID_NEXT= 'uuid:2'
mysql-bin.000001 | 352 | Query          |      3706 |         424 | BEGIN
mysql-bin.000001 | 424 | Table_map      |      3706 |         472 | table_id: 71 (test.test)
mysql-bin.000001 | 472 | Write_rows     |      3706 |         516 | table_id: 71 flags: STMT_END_F
mysql-bin.000001 | 516 | Query          |      3706 |         589 | COMMIT

5.7的结果：

mysql-bin.000001 |  544 | Gtid           |      3707 |         592 | SET @@SESSION.GTID_NEXT= 'uuid:3'
mysql-bin.000001 |  592 | Query          |      3707 |         685 | XA START X'78696431',X'',1
mysql-bin.000001 |  685 | Table_map      |      3707 |         730 | table_id: 74 (test.t) 
mysql-bin.000001 |  730 | Write_rows     |      3707 |         774 | table_id: 74 flags: STMT_END_F
mysql-bin.000001 |  774 | Query          |      3707 |         865 | XA END X'78696431',X'',1 
mysql-bin.000001 |  865 | XA_prepare     |      3707 |         905 | XA PREPARE X'78696431',X'',1
mysql-bin.000001 |  905 | Gtid           |      3707 |         953 | SET @@SESSION.GTID_NEXT= 'uuid:4' |
mysql-bin.000001 |  953 | Query          |      3707 |        1047 | XA COMMIT X'78696431',X'',1

可以看到 MySQL 5.6 XA 事务和普通事务的binlog是一样的，并没有体现 xa prepare。而到了 MySQL 5.7 XA 事务的binlog和 普通的事务是完全不同的，XA Prepare有单独的Log event类型，有自己的Gtid，当开启semi-sync的情况下，MySQL 5.7 执行 XA prepare 时会等备库回复后才返回结果给客户端，这样XA prepare执行完就是安全的。

这个是重点

在MySQL 5.7.7 之前，MySQL一直存在一个"bug"。在事务达到PREPARED状态后，客户端断开与MySQL的连接，MySQL 会自动回滚该事务，这个行为不符合分布式事务的规范，MySQL将PREPARED的事务丢失了。之所以MySQL这么实现是因为MySQL 5.7.7 之前PREPARED的事务并不会记录到binlog中。客户端退出后会丢失该信息，如果允许再提交，那么binlog缺少事务信息，会造成主从不一致。

在MySQL 5.7.7 之后，MySQL 新增了一个XA_prepare_log_event的事件，会把xa start到xa prepare中间的操作记录到Binlog中。Slave读取Relay log 进行回放，当SQL Thread读取到PREPARED的事务后，在读取xa commit或者xa rollback前，会进行一个类似客户端断开的操作，继续读取后续的事务信息，不会阻塞SQL Thread的执行。从以上的结果看，Oracle在MySQL 5.7.7 上确实完美的解决了MySQL XA一直存在的一个"bug"。

以上摘自:

MySQL XA 介绍

MySQL · 特性分析 · 浅谈 MySQL 5.7 XA 事务改进

DDL（Data Definition Language）数据库定义语言statements are used to define the database structure or schema.

DDL是SQL语言的四大功能之一。 用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束 DDL不需要commit.

• CREATE(创建)
• ALTER (修改)
• DROP (删除)
• TRUNCATE (清除)
• COMMENT (注释)
• RENAME (重命名)

DML（Data Manipulation Language）数据操纵语言statements are used for managing data within schema objects.

由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。

DML分成交互型DML和嵌入型DML两类。

依据语言的级别，DML又可分成过程性DML和非过程性DML两种。

需要commit.

• SELECT (查询)
• INSERT (插入)
• UPDATE (更新)
• DELETE (删除)
• MERGE (合并)(这个没用过.. 居然还是个引擎....)
• CALL (调用存储过程)
• 先定义一个 存储过程,然后用call去调用这个过程..
• EXPLAIN PLAN (性能分析)
• LOCK TABLE (锁表)

DCL（Data Control Language）数据库控制语言 授权，角色控制等

• GRANT (授权)
• REVOKE (取消授权)

TCL（Transaction Control Language）事务控制语言

• SAVEPOINT (设置保存点)
• ROLLBACK (回滚)
• SET TRANSACTION (设置事务)

SQL主要分成四部分：

（1）数据定义。（SQL DDL）用于定义SQL模式、基本表、视图和索引的创建和撤消操作。

（2）数据操纵。（SQL DML）数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。

（3）数据控制。包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。

（4）嵌入式SQL的使用规定。涉及到SQL语句嵌入在宿主语言程序中使用的规则。

摘自: cnblogs

1、从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式，

2、从应用层次来分：普通索引，唯一索引，复合索引

3、根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引。

平时讲的索引类型一般是指在应用层次的划分。

• 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
• 复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
• 唯一索引：索引列的值必须唯一，但允许有空值

B-tree：因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；

Hash：虽然可以快速定位，但是没有顺序，IO复杂度高。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

不使用平衡二叉树的原因如下：

最大原因：深度太大(因为一个节点最多只有2个子节点)，一次查询需要的I/O复杂度为O(lgN),而b+tree只需要O(log_mN),而其出度m非常大，其深度一般不会超过4 平衡二叉树逻辑上很近的父子节点，物理上可能很远，无法充分发挥磁盘顺序读和预读的高效特性。

MyISAM

B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。 索引文件和数据文件是分离的

InnoDB

• InnoDB 的 B+Tree 索引分为主索引（聚集索引）和辅助索引(非聚集索引)。一张表一定包含一个聚集索引构成的 B+ 树以及若干辅助索引的构成的 B+ 树。
• 辅助索引的存在并不会影响聚集索引，因为聚集索引构成的 B+ 树是数据实际存储的形式，而辅助索引只用于加速数据的查找，所以一张表上往往有多个辅助索引以此来提升数据库的性能。
• 就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。 例如：b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2,就可以，因为优化器会自动调整a,b的顺序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。

最左匹配原则的原理

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引.最左匹配原则都是针对联合索引来说的

• 我们都知道索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。 例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

优点：最左前缀原则的利用也可以显著提高查询效率，是常见的MySQL性能优化手段。

• 主键约束（Primay Key Coustraint） 唯一性，非空性
• 唯一约束 （Unique Counstraint）唯一性，可以空，但只能有一个
• 检查约束 (Check Counstraint) 对该列数据的范围、格式的限制
• 默认约束 (Default Counstraint) 该数据的默认值
• 外键约束 (Foreign Key Counstraint) 需要建立两表间的关系并引用主表的列

1. 客户端通过TCP连接发送连接请求到mysql连接器，连接器会对该请求进行权限验证及连接资源分配
2. 查缓存。（当判断缓存是否命中时，MySQL不会进行解析查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。）
3. 语法分析（SQL语法是否写错了）。 如何把语句给到预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义。
4. 优化。是否使用索引，生成执行计划。
5. 交给执行器，将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。

• 第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
• 第二范式：要求实体的属性完全依赖于主关键字。所谓完全 依赖是指不能存在仅依赖主关键字一部分的属性。
• 第三范式：任何非主属性不依赖于其它非主属性。

MySQL 支持多种存储引擎,比如 InnoDB,MyISAM,Memory,Archive 等等.在大多数的情况下,直接选择使用 InnoDB 引擎都是最合适的,InnoDB 也是 MySQL 的默认存储引擎。

MyISAM 和 InnoDB 的区别有哪些：

• InnoDB 支持事务，MyISAM 不支持
• InnoDB 支持外键，而 MyISAM 不支持
• InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高；MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
• Innodb 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高；
• InnoDB 不保存表的具体行数，MyISAM 用一个变量保存了整个表的行数。
• MyISAM 采用表级锁(table-level locking)；InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
• 候选键：是最小超键，即没有冗余元素的超键。
• 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
• 外键：在一个表中存在的另一个表的主键称此表的外键。

• NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
• UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
• PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
• FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
• CHECK: 用于控制字段的值范围。

char 是一个定长字段,假如申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用 10 个字符,而 varchar 是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间.

在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用 char,否则应该尽量使用 varchar.例如存储用户 MD5 加密后的密码,则应该使用 char。

MySQL中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用in和exists差别不大。 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。 not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

存储过程是一些预编译的 SQL 语句。

1、更加直白的理解：存储过程可以说是一个记录集，它是由一些 T-SQL 语句组成的代码块，这些 T-SQL 语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

2、存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量 T_SQL 语句 ，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全

但是,在互联网项目中,其实是不太推荐存储过程的,比较出名的就是阿里的《Java 开发手册》中禁止使用存储过程,我个人的理解是,在互联网项目中,迭代太快,项目的生命周期也比较短,人员流动相比于传统的项目也更加频繁,在这样的情况下,存储过程的管理确实是没有那么方便,同时,复用性也没有写在服务层那么好。

innodb 事务日志包括 redo log 和 undo log。

undo log 指事务开始之前，在操作任何数据之前，首先将需操作的数据备份到一个地方。redo log 指事务中操作的任何数据，将最新的数据备份到一个地方。

事务日志的目的：实例或者介质失败，事务日志文件就能派上用场。

redo log

redo log 不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入 redo 中。具体的落盘策略可以进行配置 。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 MySQL 服务的时候，根据 redo log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。RedoLog 是为了实现事务的持久性而出现的产物。

undo log

undo log 用来回滚行记录到某个版本。事务未提交之前，Undo 保存了未提交之前的版本数据，Undo 中的数据可作为数据旧版本快照供其他并发事务进行快照读。是为了实现事务的原子性而出现的产物,在 MySQL innodb 存储引擎中用来实现多版本并发控制。

MySQL的 binlog 是记录所有数据库表结构变更（例如 CREATE、ALTER TABLE）以及表数据修改（INSERT、UPDATE、DELETE）的二进制日志。binlog 不会记录 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看 MySQL 执行过的所有语句。

MySQL binlog 以事件形式记录，还包含语句所执行的消耗的时间，MySQL 的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

binlog 有三种格式，各有优缺点：

• statement： 基于 SQL 语句的模式，某些语句和函数如 UUID, LOAD DATA INFILE 等在复制过程可能导致数据不一致甚至出错。

• row： 基于行的模式，记录的是行的变化，很安全。但是 binlog 会比其他两种模式大很多，在一些大表中清除大量数据时在 binlog 中会生成很多条语句，可能导致从库延迟变大。

• mixed： 混合模式，根据语句来选用是 statement 还是 row 模式。

尽量不要在同一个事务中使用多种存储引擎，MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。

如果在事务中混合使用了事务型和非事务型的表（例如InnoDB和MyISAM表）,在正常提交的情况下不会有什么问题。

但如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定。所以，为每张表选择合适的存储引擎非常重要。

对于 InnoDB ，聚簇索引记录中包含 3 个隐藏的列：

• ROW ID：隐藏的自增 ID，如果表没有主键，InnoDB 会自动按 ROW ID 产生一个聚集索引树。
• 事务 ID：记录最后一次修改该记录的事务 ID。
• 回滚指针：指向这条记录的上一个版本。

我们拿上面的例子，对应解释下 MVCC 的实现原理，如下图：

如图，首先 insert 语句向表 t1 中插入了一条数据，a 字段为 1，b 字段为 1， ROW ID 也为 1 ，事务 ID 假设为 1，回滚指针假设为 null。当执行 update t1 set b=666 where a=1 时，大致步骤如下：

• 数据库会先对满足 a=1 的行加排他锁；
• 然后将原记录复制到 undo 表空间中；
• 修改 b 字段的值为 666，修改事务 ID 为 2；
• 并通过隐藏的回滚指针指向 undo log 中的历史记录；
• 事务提交，释放前面对满足 a=1 的行所加的排他锁。

在前面实验的第 6 步中，session2 查询的结果是 session1 修改之前的记录，这个记录就是来自 undolog 中。

因此可以总结出 MVCC 实现的原理大致是：

InnoDB 每一行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本，这个历史版本存放在 undo log 中。如果要执行更新操作，会将原记录放入 undo log 中，并通过隐藏的回滚指针指向 undo log 中的原记录。其它事务此时需要查询时，就是查询 undo log 中这行数据的最后一个历史版本。

MVCC 最大的好处是读不加锁，读写不冲突，极大地增加了 MySQL 的并发性。通过 MVCC，保证了事务 ACID 中的 I（隔离性）特性。

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

行级锁

• 行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
• 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁

• 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
• 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁

• 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁
• 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁

从锁的类别上来讲，有共享锁和排他锁。

• 共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

• 排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

用上面的例子来说就是用户的行为有两种，一种是来看房，多个用户一起看房是可以接受的。 一种是真正的入住一晚，在这期间，无论是想入住的还是想看房的都不可以。

锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁，表级锁。

他们的加锁开销从大到小，并发能力也是从大到小。

在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

• 使用较低的隔离级别
• 设计索引，尽量使用索引去访问数据，加锁更加精确，从而减少锁冲突
• 选择合理的事务大小，给记录显示加锁时，最好一次性请求足够级别的锁。列如，修改数据的话，最好申请排他锁，而不是先申请共享锁，修改时在申请排他锁，这样会导致死锁
• 不同的程序访问一组表的时候，应尽量约定一个相同的顺序访问各表，对于一个表而言，尽可能的固定顺序的获取表中的行。这样大大的减少死锁的机会。
• 尽量使用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
• 不要申请超过实际需要的锁级别
• 数据查询的时候不是必要，不要使用加锁。MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能：MVCC只在committed read（读提交）和 repeatable read （可重复读）两种隔离级别
• 对于特定的事务，可以使用表锁来提高处理速度活着减少死锁的可能。

分表

比如你单表都几千万数据了，你确定你能扛住么？绝对不行，单表数据量太大，会极大影响你的 sql执行的性能，到了后面你的 sql 可能就跑的很慢了。一般来说，就以我的经验来看，单表到几百万的时候，性能就会相对差一些了，你就得分表了。

分表就是把一个表的数据放到多个表中，然后查询的时候你就查一个表。比如按照用户 id 来分表，将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内，比如每个表就固定在 200 万以内。

分库

分库就是你一个库一般我们经验而言，最多支撑到并发 2000，一定要扩容了，而且一个健康的单库并发值你最好保持在每秒 1000 左右，不要太大。那么你可以将一个库的数据拆分到多个库中，访问的时候就访问一个库好了。

这就是所谓的分库分表。

这个其实就是看看你了解哪些分库分表的中间件，各个中间件的优缺点是啥？然后你用过哪些分库分表的中间件。

比较常见的包括：

• cobar
• TDDL
• atlas
• sharding-jdbc
• mycat

cobar

阿里 b2b 团队开发和开源的，属于 proxy 层方案。早些年还可以用，但是最近几年都没更新了，基本没啥人用，差不多算是被抛弃的状态吧。而且不支持读写分离、存储过程、跨库 join 和分页等操作。

TDDL

淘宝团队开发的，属于 client 层方案。支持基本的 crud 语法和读写分离，但不支持 join、多表查询等语法。目前使用的也不多，因为还依赖淘宝的 diamond 配置管理系统。

atlas

360 开源的，属于 proxy 层方案，以前是有一些公司在用的，但是确实有一个很大的问题就是社区最新的维护都在 5 年前了。所以，现在用的公司基本也很少了。

sharding-jdbc

当当开源的，属于 client 层方案。确实之前用的还比较多一些，因为 SQL 语法支持也比较多，没有太多限制，而且目前推出到了 2.0 版本，支持分库分表、读写分离、分布式 id 生成、柔性事务（最大努力送达型事务、TCC 事务）。而且确实之前使用的公司会比较多一些（这个在官网有登记使用的公司，可以看到从 2017 年一直到现在，是有不少公司在用的），目前社区也还一直在开发和维护，还算是比较活跃，个人认为算是一个现在也可以选择的方案。

mycat

基于 cobar 改造的，属于 proxy 层方案，支持的功能非常完善，而且目前应该是非常火的而且不断流行的数据库中间件，社区很活跃，也有一些公司开始在用了。但是确实相比于 sharding jdbc 来说，年轻一些，经历的锤炼少一些。

MySQL 实际上在有两个同步机制，一个是半同步复制，用来 解决主库数据丢失问题；一个是并行复制，用来 解决主从同步延时问题。

• 半同步复制，也叫 semi-sync 复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。
• 并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

• 优化shema、sql语句+索引；
• 第二加缓存，memcached, redis；
• 主从复制，读写分离；
• 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
• 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select * from table where age > 20 limit 1000000,10 这种查询其实也是有可以优化的余地的. 这条语句需要 load1000000 数据然后基本上全部丢弃,只取 10 条当然比较慢. 当时我们可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也 load 了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快。

解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.

在阿里巴巴《Java开发手册》中,对超大分页的解决办法是类似于上面提到的第一种.

【推荐】利用延迟关联或者子查询优化超多分页场景。

说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

正例：先快速定位需要获取的id段，然后再关联：

SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

在业务系统中，除了使用主键进行的查询，其他的我都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么？ 是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？

所以优化也是针对这三个方向来的，

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

• 访问数据太多导致查询性能下降
• 确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
• 确认MySQL服务器是否在分析大量不必要的数据行
• 查询不需要的数据。解决办法：使用limit解决
• 多表关联返回全部列。解决办法：指定列名
• 总是返回全部列。解决办法：避免使用SELECT *
• 重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存
• 是否在扫描额外的记录。解决办法： 使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化： 使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。
• 改变数据库和表的结构，修改数据表范式
• 重写SQL语句，让优化器可以以更优的方式执行查询。

• 确定ON或者USING子句中是否有索引。
• 确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引。

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

1. 将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

1. 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

1. 增加冗余字段

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：

冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 MySQLd 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 MySQLd 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

类似的问题：某个表有近千万数据，CRUD比较慢，如何优化？分库分表了是怎么做的？分表分库了有什么问题？有用到中间件么？他们的原理知道么？

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

• 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
• 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
• 缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑；
• 通过分库分表的方式进行优化，主要有垂直分表和水平分表。

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

图片来源：https://www.javazhiyin.com/40232.html

B-Tree索引（MySQL使用B+Tree）

B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

B+Tree索引

是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

B+tree性质：

• n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。

• 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

• 所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

• B+ 树中，数据对象的插入和删除仅在叶节点上进行。

• B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

B-tree： 从两个方面来回答

• B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

• 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

Hash：

• 虽然可以快速定位，但是没有顺序，IO复杂度高；

• 基于Hash表实现，只有Memory存储引擎显式支持哈希索引 ；

• 适合等值查询，如=、in()、<=>，不支持范围查询 ；

• 因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序 ；

• Hash索引在查询等值时非常快 ；

• 因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找 ；

• 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题 。

二叉树： 树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树： 树的高度随着数据量增加而增加，IO代价高。

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select score from student where score > 90的查询时，在索引的叶子节点上，已经包含了score 信息，不会再次进行回表查询。

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

具体原因为:

MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

因为可能我们索引的字段非常长，这既占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果。但是，我们需要注意，order by不支持前缀索引 。

流程是：

先计算完整列的选择性 :select count(distinct col_1)/count(1) from table_1

再计算不同前缀长度的选择性 :select count(distinct left(col_1,4))/count(1) from table_1

找到最优长度之后，创建前缀索引 :create index idx_front on table_1 (col_1(4))

MySQL 5.6引入了索引下推优化。默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。

• 有了索引下推优化，可以在减少回表次数

• 在InnoDB中只针对二级索引有效

官方文档中给的例子和解释如下：

在 people_table中有一个二级索引(zipcode，lastname，address)，查询是SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

• 如果没有使用索引下推技术，则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断数据是否符合条件

• 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。