MySQL

Some problems：

什么是MySQL？

MySQL是一种关系型数据库，在java企业级开发中非常常用，由于MySQL是免费开源的，并且扩展方便。阿里巴巴数据库系统也大量用到了MySQL，因此它的稳定性是有保障的。MySQL默认端口是3306。

 

存储引擎：

查看MySQL所提供的所有存储引擎：show engines;

 

从上图我们可以看出MySQL默认的引擎是InnoDB，且只有InnoDB是事务性存储引擎，也就是说只有InnoDB支持事物。

 

InnoDB和MyISAM的区别？

MyISAM是MySQL5.5版本之前的默认数据库引擎，虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事物和行级锁，而且最大的缺陷就是崩溃后无法恢复。不过5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL5.5版本后默认的存储引擎是InnoDB。

大多数时候我们使用的都是InnoDB存储引擎，但是在某些情况下使用MyISAM也是合适的，比如读密集的情况下（如果不介意MyISAM崩溃恢复的话）。

两者对比：

1）是否支持行级锁：MyISAM只支持表级锁，而InnoDB既支持表级锁又支持行级锁，默认为行级锁；

2）是否支持事物和崩溃后的恢复：MyISAM强调的是性能，每次查询具有原子性，其执行速度比InnoDB更快，但不支持事物。InnoDB提供支持事物、外部键等高级数据库功能。具有事物（Transaction）、回滚（rollback）和崩溃修复能力（crash recovery capabilities）的事物安全（transaction-safe（ACID））型表；

3）是否支持外键：MyISAM不支持，InnoDB支持；

4）是否支持MVCC（Multi-Version Concurrency Control）：仅InnoDB支持。应对高并发事物，MVCC比单存的加锁更高效；MVCC只在read commited和repeatable read两个隔离级别下工作；MVCC可以使用乐观锁和悲观锁来实现；各数据库中MVCC实现并不统一。

MySQL-InnoDB-MVCC多版本并发控制 ： https://segmentfault.com/a/1190000012650596

一般情况下我们选择InnoDB都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事物支持，也不在乎崩溃后的恢复问题的话，MyISAM是一个不错的选择。

【MVCC（Multi-Version Concurrency Control）：多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事物内存。

如果有人从数据库中读取数据的同时，有另外的人写入数据，有可能读数据的人会看到【半写】或者不一致的数据。有很多种方法来解决这个问题，叫做并发控制方法。最简单的方法是加锁，通过加锁，让所有读者等待写者工作完成，但是这样效率会很差。MVCC使用了一种不同的手段，每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照，写者写操作造成的变化在写操作之前（或者数据库事物提交之前）对于其他读者来说是不可见的。

当一个MVCC数据库需要更新一条数据记录的时候，它不会直接用新数据覆盖旧数据，而是将旧数据标记为过时，并在别处增加新版本的数据。这时就会有存储多个版本的数据，但只有一个是最新的。这种方式允许读者读取在他读之前已经存在的数据，即使这些在读的过程中半路被别人修改了、删除了，也对正在读的用户没有影响。这种多版本的方式避免了修改删除操作在内存和磁盘存储结构造成的空洞的开销，但是需要系统周期性整理以删除老的、过时的数据。对于面向文档的数据库来说，这种方式允许系统将整个文档写到磁盘的一块连续区域上，当需要更新的时候，直接重写一个版本，而不是对文档的某些比特位、分片切除，或者维护一个链式的、非连续的数据库结构。】

 

索引：

MySQL索引使用的数据结构主要有BTree索引和哈希索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

MySQL的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。

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

InnoDB：其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其他的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索的时候，直接找到key所在的节点即可取出数据；在根据辅助索引查找的时候，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

 

什么是事物？

事物是逻辑上的一组操作，要么都执行，要么都不执行。

事物最经典也最经常被说的例子就是转账了。假如小宇要给小美转账1000，这个转账会涉及到两个关键操作：将小宇的余额减少1000，将小美的余额增加1000。万一在这两个操作之间突然出现错误，比如银行系统崩溃，导致小宇余额减少而小美余额没有增加，这就不对了。事物就是要保证这两个操作要么都成功，要么都失败。

事物的四大特性：ACID

原子性：事物是最小的执行单位，不允许分割。事物的原子性要确保操作要么全部完成，要么全部不起作用；

一致性：执行事物前后，数据保持一致，多个事物对同一个数据读取的结果是想同的；

隔离性：并发访问数据库时，一个用户的事物不被其他用户所干扰，各并发事物之间数据库是独立的；

持久性：一个事物被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

 

并发事物带来哪些问题？

在典型的应用程序中，多个事物并发运行，经常会操作相同的数据库来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题：

1）脏读：当一个事物正在访问数据并对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事物也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事物读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的；

2）丢失数据：指在一个事物读取一个数据时，另外一个事物也访问了该数据，那么在第一个事物中修改了这个数据后，第二个事物也修改了这个数据，这样第一个事物内的修改结果就被丢失，因此称为数据丢失。例如事物1读取某表中的数据A=20，事物2也读取A=20，事物1修改A=A-1，事物2也修改A=A-1，最终结果A=19，事物1 的修改被丢失；

3）不可重复读：指在一个事物内多次读同一个数据。在这个事物还没结束时，另一个事物也访问该数据。那么，在第一个事物中的两次读数据之间，由于第二个事物的修改，导致第一个事物两次读取的结果可能不太一样。这就发生了在一个事物内两次读到的数据是不一样的情况，因此称为不可重复读；

4）幻读：幻读与不可重复读类似。它发生在一个事物T1读取了几行数据，接着另一个并发事物T2插入了一些数据时。在随后的查询中，第一个事物T1就发现多了一些原本不存在的数据，就好像幻觉一样，所以称为幻读。

不可重复读与幻读的区别：

不可重复读的重点是修改，比如多读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除，比如多次读取一条记录发现记录增多或减少了。

 

事物的隔离级别有哪些？MySQL的默认隔离级别是什么？

SQL标准定义了四个隔离级别：

READ-UNCOMMITTED（读取未提交）：最低的隔离级别，允许读取尚未提交的数据，可能会导致脏读、幻读，不可重复读；

READ-COMMITTED（读取已提交）：允许读取并发事物已经提交的数据，可以阻止脏读、但是幻读或不可重复读仍有可能发生；

REPEATABLE-READ（可重复读）：对同一字段的多次读取结果都是一致的，除非数据是被本身事物自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生；

SERIALIZABLE（可串行化）：最高的隔离级别，完全服从ACID的隔离级别。所有的事物依次逐个执行，这样事物之间就完全不可能产生干扰，也就是说，该级别可以防止脏读，不可重复读以及幻读。

MySQL InnoDB存储引擎默认支持的隔离级别是REPEATABLE-READ（可重复读）。

这里需要注意的是：与SQL标准不同的地方在于InnoDB存储引擎在**REPEATABLE-READ（可重复读）事物隔离级别下使用的是Next-Key Lock锁算法，因此可以避免幻读的产生，这与其他数据库系统（SQL-Server）是不同的。所以InnoDB存储引擎默认支持的隔离级别是REPEATABLE-READ（可重复读），已经可以保证事物的隔离性要求，即达到了SQL标准的SERIALIZABLE（可串行化）隔离级别。

因为隔离级别越低，事物请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED（读取提交内容）。

InnoDB存储引擎在分布式事物的情况下一般会用到SERIALIZABLE（可串行化）隔离级别。

 

锁机制与InnoDB锁算法：

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

　　MyISAM采用表级锁（table-level locking）；

　　InnoDB支持行级锁（row-level locking）和表级锁，默认行级锁。

表级锁和行级锁对比：

表级锁：MySQL中锁定 粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和InnoDB引擎都支持表级锁；

行级锁：MySQL中锁定 粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减小数据库操作的冲突，其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

参考：MySQL锁机制  https://blog.youkuaiyun.com/qq_34337272/article/details/80611486

InnoDB存储引擎锁算法有三种：

　　Record lock：单个行记录上的锁；

　　Gap lock:间隙锁，锁定一个范围，不包括记录本身；

　　Next-key lock：record+gap 锁定一个范围，包含记录本身。

相关知识点：

1）InnoDB对于行的查询使用next-key lock；

2）next-key lock为了解决幻读问题；

3）当查询的索引含有唯一属性时，将next-key lock降级为 record；

4）Gap锁设计的目的是为了阻止多个事物将记录插到同一范围内，而这可能会产生幻读；

5）有两种方式可以显示关闭Gap锁：（除了外键约束和唯一性检查外，其余情况均使用record lock）A：将事物隔离级别设置为RC；B：将参数innodb_locks_unsafe_for_binlog设置为1。

 

大表优化：

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

1）限定数据的范围：

务必禁止不带任何限定数据范围条件的查询语句。比如：当用户查询历史订单的时候，限定一个月的范围内；

2）读写分离：

经典的数据库拆分方案，主库负责写，从库负责读；

3）垂直分区：

根据数据库里面数据表的相关性进行拆分。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分为两个单独的表，甚至放到单独的库做分库。

简单来说，垂直拆分是指数据表 列的拆分，把一张列比较多的表拆分为多张表。

垂直拆分的优点：可以使得列数据变小，在查询时减少Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起join操作，可以在应用层进行join来解决。此外，垂直分区会让事物变得更加复杂。

4）水平分区：

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

需要注意一点的是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。

水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事物难以解决，跨节点join性能较差，逻辑复杂。

补充一下数据库分片的两种常见方案：

1）客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。当当网的Sharding-JDBC、阿里的TDDL是两种比较常见的实现；

2）中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。我们现在谈的Mycat，360的Atlas，网易的DDB等等都是这种架构的实现。

参考：MySQL大表优化方案： https://segmentfault.com/a/1190000006158186

 

一条SQL语句在MySQL中是如何执行的？

https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485097&idx=1&sn=84c89da477b1338bdf3e9fcd65514ac1&chksm=cea24962f9d5c074d8d3ff1ab04ee8f0d6486e3d015cfd783503685986485c11738ccb542ba7&token=79317275&lang=zh_CN#rd

 

MySQL高性能优化建议？

https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485117&idx=1&sn=92361755b7c3de488b415ec4c5f46d73&chksm=cea24976f9d5c060babe50c3747616cce63df5d50947903a262704988143c2eeb4069ae45420&token=79317275&lang=zh_CN#rd

 

一条SQL语句执行很慢的原因？

https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485185&idx=1&sn=66ef08b4ab6af5757792223a83fc0d45&chksm=cea248caf9d5c1dc72ec8a281ec16aa3ec3e8066dbb252e27362438a26c33fbe842b0e0adf47&token=79317275&lang=zh_CN#rd

 

MyISAM和InnoDB的主要区别和应用场景？

主要区别：

• 【事物】MyISAM是非事物安全型的，而InnoDB是事物安全型的，默认开启自动提交，宜合并事物，一同提交，减少数据库多次提交导致的开销，大大提高性能；
• 【锁】MyISAM锁的粒度是表级，而InnoDB支持行级锁；
• 【全文索引】MyISAM支持全文类型索引，而InnoDB不支持全文索引；
• 【查询效率】MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM；
• 【外键】MyISAM不支持外键，InnoDB支持；
• 【count】MyISAM保有表的总行数，InnoDB只能遍历；
• MyISAM 表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储 会省去不少麻烦；
• InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事物表到事物表（alter table tablename type=InnoDB）；
• MyISAM索引和数据分离，InnoDB在一起，MyISAM是非聚簇索引，最多有一个unique的性质，InnoDB的数据文件本身就是主索引文件，这样的索引被称为“聚簇索引”；
• InnoDB提供多版本数据支持，MyISAM不支持；
• 两者都仅支持B+Tree树索引，不支持hash索引。

应用场景：

1）MyISAM管理非事物表。它提供高效存储和检索，以及全文搜索能力。如果应用中需要执行大量的select查询，那么MyISAM是更好的选择；

2）InnoDB用于事物处理应用程序，具有众多特性，包括ACID事物支持。如果应用中需要执行大量的Insert或update操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

https://www.cnblogs.com/y-rong/p/8110596.html

 

 

你经常使用什么样的查询模式？

在写多读少的应用中还是InnoDB插入性能更稳定，在并发情况下也是；如果对读取速度要求比较快的应用还是MyISAM。

 

MySQL出现死锁的原因？

MySQL行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条SQL语句操作了主键索引，那么MySQL就会锁定这个主键索引，如果MySQL操作的是非主键索引，那么MySQL会先锁定这个非主键索引，再去锁定主键索引。

在update和delete操作时MySQL不仅会锁定所有where条件扫描过的索引，还会锁定相邻的键值。

死锁举例分析：

表Test：（ID，State，Time）；主键索引：ID；非主键索引：State

当执行"update state = 1001 where state = 1000;"语句的时候会锁定State索引，由于state索引不是主键索引，所以MySQL还会去请求锁定ID索引；

当另一个SQL语句与上一个SQL几乎同时执行的时候，"update state = 1100 where ID = 1;"对于这个SQL，MySQL会先锁定ID索引，由于此SQL还操作了state字段，所以MySQL还会请求锁定state索引。这时，彼此锁定着对方需要的索引，又都在等待在对方释放锁定，所以出现了“死锁”的情况。

 

MySQL查询优化：

 

https://www.cnblogs.com/wangning528/p/6388538.html

https://ariyue.iteye.com/blog/553541

 

 

 

 

转载于:https://www.cnblogs.com/Rain1203/p/11201795.html