带你了解mysql

Mysql 架构与执行流程

• 查询语句执行流程

  客户端 --> 查询缓存 --> 解析器 --> 预处理器 --> 优化器 --> 执行计划 --> 执行器 --> 存储引擎(innodb) --> 磁盘

1. 客户端发送一条执行语句给服务端.
2. 服务端会先检查查询缓存,查询到返回数据,没有命中缓存执行下一步操作.
3. 服务器端进行sql解析,预处理,再由优化器生成对应的执行计划.
4. mysql根据优化器生成的执行计划,调用存储引擎API来执行查询.
5. 将结果返回给客户端.

• 更新语句执行流程

1. 从磁盘或者bufferpool中拿到数据写到内存(加载数据页)
2. 修改内容写到内存缓冲区
3. Innodb将记录写到redolog和undolog
4. server层 写入到 binlog
5. commit 写入到磁盘

redolog用于异常停止服务保证数据不丢失(大小有限制) 固定大小,4个一组,每个1G
undolog是用于回滚删除之前提交内容
binlog用于数据恢复(大小无限制,归档日志,逻辑日志,追加写入)

Innodb存储引擎会先把记录写到redolog 和undolog,Innodb在适当时候写到磁盘

查询语句执行和更新语句执行的区别不大

MyISAM与InnoDB存储引擎的区别

1. Mysql在5.1之前的版本中默认的搜索引擎是MyISAM,从MySQL5.5之后的版本中,默认的搜索引擎变更为InnoDB.
2. MyISAM存储引擎的特点是:表级锁、不支持事务和全文索引,适合一些CMS内容管理系统作为后台数据库使用,但是使用大并发、重负荷生产系统上,表锁结构的特性就显得力不从心; InnoDB存储引擎的特点是: 行级锁、事务安全、支持外键.InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎.InnoDB是为处理巨大量时拥有最大性能而设计的.它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的.
3. 随着CPU核数的增加,InnoDB的吞吐量反而越好,而MyISAM,其吞吐量几乎没有什么变化,显然MyISAM的表锁定机制降低了读和写的吞吐量.
4. MyISAM是一种非事务性的引擎,使得MyISAM引擎的MYSQL可以提供高速存储和检索,以及全文搜索能力,适合数据仓库等查询频繁的应用. InnoDB是事务安全的.事务是一种高级的处理方式,如在一些列增删改中只要哪个出错还可以回滚还原,而MyISAM就不可以了.
5. 每个MYISAM在磁盘上存储成三个文件:1.表名开始,.frm结尾2.数据文件,扩展名为.MYD3.索引文件,扩展名为.MYI. InnoDB表的大小只受限于操作系统文件的大小,一般为2GB.
6. 锁的问题,MyISAM表进行读操作的时候,不阻塞其他用户对同一表读操作,阻塞对同一表写操作.MyISAM表进行写操作的时候,会阻塞其他用户对同一表的读和写操作.InnoDB行锁是通过索引项加锁来实现的.只有通过索引条件检索数据,InnoDB才使用行级锁,否则将使用表锁!
7. InnoDB中不保存表的具体行数,查询行数是通过扫描整个表来计算有多少行,MyISAM只要简单的读出保存好的行数即可.
8. MyIsam 使用的是非聚集索引,不能在叶子节点上找到数据,叶子节点存数的是路径,InnoDB使用的是聚集索引,可以直接在叶子节点找到数据,比MyIsam少了一次I/O操作

MyISAM适合:
1. 做很多count的计算;
2. 插入不频繁,查询非常频繁,如果执行大量的SELECT,MyISAM是更好的选择.
3. 没有事务.
InnoDB适合:
1. 可靠性要求比较高,或者要求事务;
2. 表更新和查询都相当的频繁,并且表锁定的机会比较大的情况制定数据引擎的创建;
3. 如果你的数据执行大量的insert或update,出于性能方面的考虑,应该使用InnoDB表;
4. Delete from table时,InnoDB不会重新建立表,而是一行一行的删除.
5.load table from master操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表.但是对于使用的额外的InnoDB特性(例如外键)的表不适用.

InnoDB建议使用趋势递增整数作为PK，而不宜使用较长的列作为PK

Mysql 索引深入解析

索引的本质是数据结构

为什么mysql数据库使用B+Tree

• 数组的特点: 查询快插入慢(需要修改下标)
• 二叉树: 极端的情况下所有节点位于同一侧,时间度为O(n)
• 平衡二叉树: 会自动左旋右旋,深度最大差值为1.每个叶子节点占16K,但是叶子节点存储数据太少,浪费空间,而且树的高度过高,IO次数过多.
• B树 平衡多路查找树: 特点是分裂合并.
  每个根节点至多可以拥有m棵子树.非根节点的关键字数量大于等于ceil(m/2),所有叶子节点均在同一层,搜索可能在非叶子节点结束
• B+树 相比于B树,所有数据都存储在叶子节点,叶子节点数据有序,并且每个叶子节点增加一个指向相邻叶子节点的指针.

索引的类型:

1 普通索引
2 唯一索引
3 主键索引
4 复合索引

创建索引的技巧

1. 在用于where判断,order排序,join的(on) group by 字段上创建索引.
2. 索引的个数不要过多
3. 区分度低的字段,例如性别,不要创建索引
4. 频繁更新的值,不要作为主键或者索引
5. 不建议用无序的值(例如身份证.uuid)作为索引
6. 复合索引把离散性高(区分度高)的值放在前面
7. 创建复合索引,而不是修改单列索引
8. 过长的字段,建立前缀索引.

索引的优点:
索引大大减少服务器需要扫描的数据量.
索引帮助服务器避免排序和临时表.
索引可以将随机IO变成顺序IO
索引对InnoDB支持行级锁,是搜索引擎实现的.

索引的缺点:
索引降低更新表的速度.
建立索引会占用磁盘空间的索引文件.
如果某个数据列包含许多重复内容,为它建立索引没有太大实际效果.

对非常小的表,大部分情况下,全局扫描更高效.
应该只为最经常查询和最经常排序的数据列建立索引.
Mysql中同一个数据表里的索引总数限制16个.

Mysql 事务与锁详解

事务的特性:

• 原子性 : 是一个不可分割的单元
• 隔离性 : 事务互不干扰
• 一致性 : 事务执行前后数据保持一致
• 持久性 : 事务提交后,数据库改变是永久的

事务的隔离级别:

• 读未提交(Read uncommitted): 解决了更新丢失,但还是可能会出现脏读.
• 读提交(Read committed): 解决了更新丢失和脏读问题.
• 可重复读取(Repeatable read): 解决了更新丢失、脏读、不可重复读、但是还会出现幻读
• 可序化(Serializable):解决了更新丢失、脏读、不可重复读、幻读(虚读)

并发访问引发的问题:

• 脏读 : 读到其他事务未提交的数据.
• 不可重复读 : 一个事务中,多次读到的数据不同.
• 幻读 : 一个事务中多次读取到数据量不一致.

InnoDB的MVCC(多版本并发控制)

概述: MVCC是一种并发控制的方法,Mysql中MVCC只能在Repeatable Read(读可重复读)、Read Committed(读可提交)这两个隔离级别下工作.

用途: MVCC实现的是普通读取不加锁,并且读写不冲突.

原理: MVCC是通过保存了数据库某个时间的快照来实现的.也就是说当几个事务开启的时间不同,可能会出现同一时刻、不同事物读取同一张表的不同一行记录是不一样的.这个机制也是可重复的的实现.

开启数据库事务 : begin / start transaction

结束事务: commit / rollback

怎么加锁?

自动: delete / update / insert 自动加排它锁

手动: selete 的 where条件后跟 for update

锁的原理:
Mysql InnoDB 的锁到底锁住了什么?

表不建索引: for update 锁全表(表锁)
表建立索引: for update 锁行(行锁)

为什么锁住唯一索引后,主键索引也会被锁住?
InnoDB 中辅助索引中存储的是二级索引和主键id,唯一索引加锁后会根据主键id去找到主键索引,也锁定之.
通过主键索引检索数据加锁,则只会锁住主键索引.

为什么表没有索引,表里所有数据都会被锁定?
表上没有索引的时候,InnoDB会为每行创建一个隐藏的主键作为聚合索引, 隐藏主键是六个字节的列,该列的值随着数据插入自增.
当不通过索引检索数据的时候,Mysql会使用全表扫描,所有行的索引都会被锁定,行锁升级表锁.

MyISAM表
(1)共享读锁和排他写锁是串行的.
(2)在一定条件下,MyISAM允许查询和插入并发执行,利用这一点可以解决应用中对同一表查询和插入的锁争用问题.
(3)MyISAM默认的锁调度机制是写优先.
(4)由于表锁的锁定粒度大,读写之间又是串行的,因此,如果更新操作较多,MyISAM表可能会出现严重的锁等待,可以考虑采用InnoDB表来减少锁冲突.

InnoDB表
(1)InnoDB的行锁是基于索引实现的,如果不通过索引访问数据,InnoDB会使用表锁.
(2)在不同隔离级别下,InnoDB的锁机制和一致性读策略不同.
 - 尽量使用较低的隔离级别;精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会.
 - 选择合理的事务大小,小事务发生锁冲突的几率也更下.
 - 给记录集显式加锁时,最好一次性请求足够级别的锁.
 - 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序取表中的行.这样可以大大减少死锁的机会.
 - 尽量用相等条件访问数据,这样可以避免间隙所对并发插入的影响;不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁.
 - 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能.

Mysql 性能优化

1. 硬件
    CPU和硬盘
2. 架构
    选择更优的存储方式(redis,ES),主从复制,读写分离,负载均衡
3. 表结构
    冗余字段,分库分表
4. mysql配置
    缓存大小,内存分配
5. 索引
    遵循索引匹配的原则,根据Explain优化sql

Mysql 数据库三范式

一范式 : 每个字段都是原子级别,不可分割

二范式 : 有主键.非主键字段依赖主键

三范式 : 非主键字段不能相互依赖.(一个数据库表中不包含已在其它表中包含的非主键字段.)

反三范式 : 没有冗余的数据库未必是好数据库.