MySQL存储引擎，锁，优化简述
今天主要分享常见的存储引擎：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)等，以及最常用的MyISAM与InnoDB两个引擎 ，文章尾部有两者的详细比较。
MySQL常用存储引擎介绍
1.InnoDB 引擎(MySQL5.5以后默认使用)
MySQL 5.5 及以后版本中的默认存储引擎，他的优点如下：
- 灾难恢复性好
- 支持事务
- 使用行级锁
- 支持外键关联
- 支持热备份
- 对于InnoDB引擎中的表，其数据的物理组织形式是簇表（Cluster Table），主键索引和数据是在一起的，数据按主键的顺序物理分布
- 实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取
- 支持热备份
2.MyISAM引擎
特性如下：
- 不支持事务
- 使用表级锁，并发性差
- 主机宕机后，MyISAM表易损坏，灾难恢复性不佳
- 可以配合锁，实现操作系统下的复制备份、迁移
- 只缓存索引，数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的系统调用且效率不佳
- 数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能
3.MEMORY 存储引擎
提供内存表，也不支持事务和外键。显著提高访问数据的速度，可用于缓存会频繁访问的、可以重构的数据、计算结果、统计值、中间结果。
缺点如下：
- 使用表级锁，虽然内存访问快，但如果频繁的读写，表级锁会成为瓶颈
- 只支持固定大小的行。Varchar类型的字段会存储为固定长度的Char类型，浪费空间
- 不支持TEXT、BLOB字段。当有些查询需要使用到临时表（使用的也是MEMORY存储引擎）时，如果表中有TEXT、BLOB字段，那么会转换为基于磁盘的MyISAM表，严重降低性能
- 由于内存资源成本昂贵，一般不建议设置过大的内存表，如果内存表满了，可通过清除数据或调整内存表参数来避免报错
- 服务器重启后数据会丢失，复制维护时需要小心
MySQL存储引擎MyISAM与InnoDB如何选择
两种存储引擎的大致区别表现在：
1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
3）InnoDB支持外键，MyISAM不支持
4）从MySQL5.5.5以后，InnoDB是默认引擎
5）InnoDB不支持FULLTEXT类型的索引
6）InnoDB中不保存表的行数，如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表。
7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。
8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。
9）InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’
有人说MYISAM只能用于小型应用，其实这只是一种偏见。
如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，读写分离，而不是单纯地依赖存储引擎。
现在一般都是选用InnoDB了，主要是MyISAM的全表锁，读写串行问题，并发效率锁表，效率低，MyISAM对于读写密集型应用一般是不会去选用的。
总之：
1.MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。
2.MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。
MySQL悲观锁与乐观锁、行锁与表锁、共享锁
我们在操作数据库的时候，可能会由于并发问题而引起的数据的不一致性（数据冲突）。如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素，从这一角度来说，锁对于数据库而言就显得尤为重要。
悲观锁 和 乐观锁
（1）悲观锁
顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。
传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
（2）乐观锁
顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。
乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
（3）悲观锁 和 乐观锁的区别
两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。
MySQL锁概述
相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。
比如：
- MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
- InnoDB存储引擎既支持行级锁（ row-level locking），也支持表级锁，但默认情况下是采用行级锁。
MySQL主要的两种锁的特性可大致归纳如下:
- 表级锁： 开销小，加锁快；不会出现死锁(因为MyISAM会一次性获得SQL所需的全部锁)；锁定粒度大，发生锁冲突的概率最高,并发度最低。
- 行级锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
- 页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
行锁 和 表锁
1.主要是针对锁粒度划分的，一般分为：行锁、表锁、库锁
（1）行锁：访问数据库的时候，锁定整个行数据，防止并发错误。
（2）表锁：访问数据库的时候，锁定整个表数据，防止并发错误。
2.行锁 和 表锁 的区别：
- 表锁： 开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
- 行锁： 开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
共享锁
共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁。相当于对于同一把门，它拥有多个钥匙一样。就像这样，你家有一个大门，大门的钥匙有好几把，你有一把，你女朋友有一把，你们都可能通过这把钥匙进入你们家，这个就是所谓的共享锁。
刚刚说了，对于悲观锁，一般数据库已经实现了，共享锁也属于悲观锁的一种，那么共享锁在mysql中是通过什么命令来调用呢。通过查询资料，了解到通过在执行语句后面加上lock in share mode就代表对某些资源加上共享锁了。
什么时候使用表锁
对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。
- 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
- 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。
表锁和行锁应用场景：
- 表级锁使用与并发性不高，以查询为主，少量更新的应用，比如小型的web应用；
- 而行级锁适用于高并发环境下，对事务完整性要求较高的系统，如在线事务处理系统。
MySQL慢查询优化、索引优化、以及表等优化总结
MySQL优化概述
MySQL数据库常见的两个瓶颈是：CPU和I/O的瓶颈。
CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。
磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上。
我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能。
MySQL优化方案
Mysql的优化，大体可以分为三部分：索引的优化，sql语句的优化，表的优化
索引优化
1.索引
一般的应用系统，读写比例在10：1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的也是最容易出现问题的，还是一些复杂的查询操作，因此对查询语句的优化是重中之重，加速查询最好的方法就是索引。
索引：简单的说，相当于图书的目录，可以帮助用户快速的找到需要的内容。
在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。
总结：索引的目的在于提高查询效率，与我们查询图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小结，然后找到页数。相似的例子还有：查字典，查地图等。
2.索引类型
- 普通索引
是最基本的索引，它没有任何限制。
- 唯一索引
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
- 组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
- 主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引
- 全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
3.索引优化
- 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
- 尽量使用短索引，如果可以，应该制定一个前缀长度
- 对于经常在where子句使用的列，最好设置索引，这样会加快查找速度
- 对于有多个列where或者order by子句的，应该建立复合索引
- 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
- 尽量不要在列上进行运算（函数操作和表达式操作）
- 尽量不要使用not in和<>操作
SQL慢查询的优化
1.如何捕获低效sql
1）slow_query_log
这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。
2）ong_query_time
当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。
3）slow_query_log_file
记录日志的文件名。
4）log_queries_not_using_indexes
这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。
2.慢查询优化的基本步骤
1)先运行看看是否真的很慢，注意设置SQL_NO_CACHE
2）where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
3)explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
4)order by limit 形式的sql语句让排序的表优先查
5)了解业务方使用场景
6)加索引时参照建索引的几大原则
7)观察结果，不符合预期继续从1开始分析
2.优化原则
- 查询时，能不要*就不用*，尽量写全字段名
- 大部分情况连接效率远大于子查询
- 多使用explain和profile分析查询语句
- 查看慢查询日志，找出执行时间长的sql语句优化
- 多表连接时，尽量小表驱动大表，即小表 join 大表
- 在千万级分页时使用limit
- 对于经常使用的查询，可以开启缓存
数据库表优化
- 表的字段尽可能用NOT NULL
- 字段长度固定的表查询会更快
- 把数据库的大表按时间或一些标志分成小表
- 将表拆分
数据表拆分：主要就是垂直拆分和水平拆分。
水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。
垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。
总之：
Mysql的优化主要就在于：索引的优化，sql语句的优化，表的优化，在高并发网络环境下，除了优化数据库外，还会涉及到分布式缓存，CDN，数据库读写分离等高并发优化技术。
MySQL数据库主从同步的3种一致性方案实现，及优劣比较
数据主从同步的由来
互联网的很多业务，特别是在高并发的场景下，基本都是读远远大于写，如果数据库读和写的压力都同在一台主机上，这显然不太合理。
于是，把一台数据库主机分为单独的一台写主库（主要负责写操作），而把读的数据库压力分配给读的从库，而且读从库可以变为多台，这就是读写分离的典型场景如下：
为了进一步的降低数据库端的压力(高并发的瓶颈)，这个时候也会在业务层部署分布式缓存集群(redis、memcached)等，把读的压力转移给应用服务器端，其实与数据主从的设计是遵循同一个原则，降低后端数据库的压力。
问题：
读写分离提高了资源的利用效率的同时也引出了一个问题，就是由于延时（网络传输，操作）而引起的数据库主从不一致的问题，以下会详细谈相关的数据一致性解决方案。
数据同步一致性解决方案
1.半同步复制
办法就是等主从同步完成之后，等主库上的写请求再返回，这就是常说的“半同步复制”。
实现方案
mysql的半同步复制方案，下面我以mysql为例介绍。
MySQL半同步复制
MySQL的Replication默认是一个异步复制的过程，从MySQL5.5开始，MySQL以插件的形式支持半同步复制，我先谈下异步复制，这样可以更好的理解半同步复制。
1）异步复制
MySQL默认的复制是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从库上。
2）半同步复制
介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay
log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。
半同步复制原理：
- 事务在主库写完binlog后需要从库返回一个已接受，才放回给客户端
- mysql5.5版本以后，以插件的形式存在，需要单独安装
- 确保事务提交后binlog至少传输到一个从库
- 不保证从库应用完成这个事务的binlog
- 性能有一定的降低
- 网络异常或从库宕机，卡主库，直到超时或从库恢复
该方案优点：
利用数据库原生功能，比较简单
该方案缺点：
主库的写请求时延会增长，吞吐量会降低
2.数据库中间件
流程：
1）所有的读写都走数据库中间件，通常情况下，写请求路由到主库，读请求路由到从库
2）记录所有路由到写库的key，在主从同步时间窗口内（假设是500ms），如果有读请求访问中间件，此时有可能从库还是旧数据，就把这个key上的读请求路由到主库。
3）在主从同步时间过完后，对应key的读请求继续路由到从库。
相关的中间件有：
1）canal:是阿里巴巴旗下的一款开源项目，纯Java开发,基于数据库增量日志解析，提供增量数据订阅&消费，目前主要支持了MySQL。
2）otter：也是阿里开源的一个分布式数据库同步系统，尤其是在跨机房数据库同步方面，有很强大的功能。它是基于数据库增量日志解析，实时将数据同步到本机房或跨机房的mysql/oracle数据库。
两者的区别在于：
otter目前嵌入式依赖canal，部署为同一个jvm，目前设计为不产生Relay Log。
otter目前允许自定义同步逻辑，解决各类需求。
该方案优点
能保证绝对一致
该方案缺点：
数据库中间件的成本较高
缓存记录写key法
写流程：
1）如果key要发生写操作，记录在cache里，并设置“经验主从同步时间”的cache超时时间，例如500ms
2）然后修改主数据库
读流程：
1）先到缓存里查看，对应key有没有相关数据
2）有相关数据，说明缓存命中，这个key刚发生过写操作，此时需要将请求路由到主库读最新的数据。
3）如果缓存没有命中，说明这个key上近期没有发生过写操作，此时将请求路由到从库，继续读写分离。
该方案优点：
相对数据库中间件，成本较低
该方案缺点：
为了保证“一致性”，引入了一个cache组件，并且读写数据库时都多了缓存操作。
Redis缓存和MySQL数据一致性方案详解
需求起因
在高并发的业务场景下，数据库大多数情况都是用户并发访问最薄弱的环节。所以，就需要使用redis做一个缓冲操作，让请求先访问到redis，而不是直接访问MySQL等数据库。
这个业务场景，主要是解决读数据从Redis缓存，一般都是按照下图的流程来进行业务操作。
读取缓存步骤一般没有什么问题，但是一旦涉及到数据更新：数据库和缓存更新，就容易出现缓存(Redis)和数据库（MySQL）间的数据一致性问题。
不管是先写MySQL数据库，再删除Redis缓存；还是先删除缓存，再写库，都有可能出现数据不一致的情况。举一个例子：
1.如果删除了缓存Redis，还没有来得及写库MySQL，另一个线程就来读取，发现缓存为空，则去数据库中读取数据写入缓存，此时缓存中为脏数据。
2.如果先写了库，在删除缓存前，写库的线程宕机了，没有删除掉缓存，则也会出现数据不一致情况。
因为写和读是并发的，没法保证顺序,就会出现缓存和数据库的数据不一致的问题。
如来解决？这里给出两个解决方案，先易后难，结合业务和技术代价选择使用。
缓存和数据库一致性解决方案
第一种方案：采用延时双删策略
在写库前后都进行redis.del(key)操作，并且设定合理的超时时间。
伪代码如下
- public void write(String key,Object data){
- redis.delKey(key);
- db.updateData(data);
- Thread.sleep(500);
- redis.delKey(key);
- }
2.具体的步骤就是：
1）先删除缓存
2）再写数据库
3）休眠500毫秒
4）再次删除缓存
那么，这个500毫秒怎么确定的，具体该休眠多久呢？
需要评估自己的项目的读数据业务逻辑的耗时。这么做的目的，就是确保读请求结束，写请求可以删除读请求造成的缓存脏数据。
当然这种策略还要考虑redis和数据库主从同步的耗时。最后的的写数据的休眠时间：则在读数据业务逻辑的耗时基础上，加几百ms即可。比如：休眠1秒。
3.设置缓存过期时间
从理论上来说，给缓存设置过期时间，是保证最终一致性的解决方案。所有的写操作以数据库为准，只要到达缓存过期时间，则后面的读请求自然会从数据库中读取新值然后回填缓存。
4.该方案的弊端
结合双删策略+缓存超时设置，这样最差的情况就是在超时时间内数据存在不一致，而且又增加了写请求的耗时。
第二种方案：异步更新缓存(基于订阅binlog的同步机制)
1.技术整体思路：
MySQL binlog增量订阅消费+消息队列+增量数据更新到redis
1）读Redis：热数据基本都在Redis
2）写MySQL:增删改都是操作MySQL
3）更新Redis数据：MySQ的数据操作binlog，来更新到Redis
2.Redis更新
1）数据操作主要分为两大块：
- 一个是全量(将全部数据一次写入到redis)
- 一个是增量（实时更新）
这里说的是增量,指的是mysql的update、insert、delate变更数据。
2）读取binlog后分析 ，利用消息队列,推送更新各台的redis缓存数据。
这样一旦MySQL中产生了新的写入、更新、删除等操作，就可以把binlog相关的消息推送至Redis，Redis再根据binlog中的记录，对Redis进行更新。
其实这种机制，很类似MySQL的主从备份机制，因为MySQL的主备也是通过binlog来实现的数据一致性。
这里可以结合使用canal(阿里的一款开源框架)，通过该框架可以对MySQL的binlog进行订阅，而canal正是模仿了mysql的slave数据库的备份请求，使得Redis的数据更新达到了相同的效果。
当然，这里的消息推送工具你也可以采用别的第三方：kafka、rabbitMQ等来实现推送更新Redis。
