MYSQL-知识点

1、数据库隔离级别有哪些，各自的含义是什么，MYSQL 默认的隔离级别是是什么。

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞
MYSQL默认是RepeatedRead级别

2、MYSQL 有哪些存储引擎，各自优缺点。

MyISAM： 拥有较高的插入，查询速度，但不支持事务 
InnoDB ：5.5版本后Mysql的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定 
BDB： 源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性 
Memory ：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失 
Merge ：将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用 
Archive ：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差 
Federated： 将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用 
Cluster/NDB ：高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用 
CSV： 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.CSV文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引。 
BlackHole ：黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继 
另外，Mysql的存储引擎接口定义良好。有兴趣的开发者通过阅读文档编写自己的存储引擎。 
http://baike.baidu.com/item/%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E

3、高并发下，如何做到安全的修改同一行数据。

 ♀ 使用悲观锁：悲观锁本质是当前只有一个线程执行操作，排斥外部请求的修改。遇到加锁的状态，就必须等待。结束了唤醒其他线程进行处理。虽然此方案的确解决了数据安全的问题，但是，我们的场景是“高并发”。也就是说，会很多这样的修改请求，每个请求都需要等待“锁”，某些线程可能永远都没有机会抢到这个“锁”，这种请求就会死在那里。同时，这种请求会很多，瞬间增大系统的平均响应时间，结果是可用连接数被耗尽，系统陷入异常。（细节参考5）
     ♂ 也是通过FIFO（First Input First Output，先进先出）缓存队列思路：直接将请求放入队列中，就不会导致某些请求永远获取不到锁。看到这里，是不是有点强行将多线程变成单线程的感觉哈。
      
       然后，我们现在解决了锁的问题，全部请求采用“先进先出”的队列方式来处理。那么新的问题来了，高并发的场景下，因为请求很多，很可能一瞬间将队列内存“撑爆”，然后系统又陷入到了异常状态。或者设计一个极大的内存队列，也是一种方案，但是，系统处理完一个队列内请求的速度根本无法和疯狂涌入队列中的数目相比。也就是说，队列内的请求会越积累越多，最终Web系统平均响应时候还是会大幅下降，系统还是陷入异常。
      ♀ 使用乐观锁：这个时候，我们就可以讨论一下“乐观锁”的思路了。乐观锁，是相对于“悲观锁”采用更为宽松的加锁机制，大都是采用带版本号（Version）更新。实现就是，这个数据所有请求都有资格去修改，但会获得一个该数据的版本号，只有版本号符合的才能更新成功，其他的返回抢购失败。这样的话，我们就不需要考虑队列的问题，不过，它会增大CPU的计算开销。但是，综合来说，这是一个比较好的解决方案。（具体参考5）

4、悲观所和乐观锁

乐观锁（Optimistic Concurrency Control，缩写”OCC”）：是一种并发控制的方法。乐观的认为多用户并发的事务在处理时不会彼此互相影响，各事务能够在使用锁的情况下处理各自的数据。在提交更新数据之前，每个事务会先检查该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。不过，当需求多为更新数据时，就会增大数据之间的冲突，也就增大CPU的计算开销，此时不建议使用。
     ♪ 数据是否修改的标准是：在表中的数据进行操作时，先给数据表最新的数据加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,修改完数据后准备提交之前,先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。
     

1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods
set status=2,version=version+1
where id=#{id} and version=#{version};
      ♫ 悲观锁（Pessimistic Concurrency Control，缩写”PCC”）：与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。所以悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

     
       〓 要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。set autocommit=0;

#0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
#1.查询出商品信息
select status from t_goods where id=1 for update;
#2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
#3.修改商品status为2
update t_goods set status=2;
#4.提交事务
commit;/commit work;
     InnoDB的标准行级锁有哪2种：
     ♬共享锁：共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁，在执行语句后面加上lock in share mode就代表对某些资源加上共享锁。
     ♬排它锁：排它锁与共享锁相对应，就是指对于多个不同的事务，对同一个资源只能有一把锁。在需要执行的语句后面加上for update就可以了。对于update,insert,delete语句会自动加排它锁。
     总结：乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机智的其实都是提供的乐观锁。 相反，如果经常发生冲突，上层应用会不断进行 retry，这样反而降低了性能，所以这种情况下用悲观锁比较合适
 

5、乐观锁的实现（hibernate实现乐观锁）

https://www.cnblogs.com/sheseido/p/5038562.html

6、MySQL高级索引优化分析

https://blog.csdn.net/zhengzhaoyang122/article/details/79463583

7、sql执行顺序

①、手写join语句如下：

            

            ②、机读join语句的顺序如下

            

           ③、结论：

           

8、数据库会死锁吗，举一个死锁的例子，mysql怎么解决死锁

-- 创建表test1 
CREATE TABLE test1 ( 
id int(11) NOT NULL AUTO_INCREMENT, 
name varchar(10) NOT NULL, 
PRIMARY KEY (id) 
); 
insert into test1 values('hello'); 
  
-- 创建表test2 
CREATE TABLE test2 ( 
id int(11) NOT NULL AUTO_INCREMENT, 
name varchar(10) NOT NULL, 
PRIMARY KEY (id) 
); 
  
-- Transcation 1 
begin; 
insert into test2 select * from test1 where id = 1; 
delete from test1 where id = 1; 
  
-- Transcation 2 
begin; 
insert into test2 select * from test1 where id = 1;

要解决死锁首先要了解产生死锁的原因：

①、系统资源不足。

②、进程运行推进的顺序不合适。

③、资源分配不当等。
    ㊧、如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。
 ▶产生死锁的四个必要条件：

①、互斥条件：一个资源每次只能被一个进程使用。
②、请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
③、不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
④、循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
 ▶这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。
 ▶这里提供两个解决数据库死锁的方法：
    1）重启数据库。
    2）杀掉抢资源的进程：先查哪些进程在抢资源：

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
     ☎、杀掉抢资源的进程：

Kill trx_mysql_thread_id;
 

8、MySql的索引原理，索引的类型有哪些，如何创建合理的索引，索引如何优化。

MySql索引的原理：

1）、通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
2）、索引是通过复杂的算法，提高数据查询性能的手段。从磁盘io到内存io的转变。
      MySql索引原理参考博客：https://blog.csdn.net/u013235478/article/details/50625677
      MySql索引的类型：

            1）、普通索引index：加速查找
            2）、唯一索引：①、主键索引：primary key：加速查找+主键唯一约束且不为空。
                                       ②、唯一索引：unique：加速查找+主键唯一约束。
            3）、联合索引：①、primary key(id,name)：联合主键索引。
                                       ②、unique(id,name)：联合唯一索引。
                                       ③、unique(id,name）：联合普通索引。
            4）、全文索引fulltext：用于搜索很长一篇文章的时候，效果最好。
            5）、空间索引spatial：了解就好，几乎不用
      创建索引的原则：1）、最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
        2）、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
       3）、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
       4）、索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
       5）、尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
      索引如何优化，领出来单独写了一篇博客：https://blog.csdn.net/zhengzhaoyang122/article/details/79463583

9、聚集索引和非聚集索引的区别。

     “聚簇”：就是索引和记录紧密在一起。
     “非聚簇索引”：索引文件和数据文件分开存放，索引文件的叶子页只保存了主键值，要定位记录还要去查找相应的数据块。

10、select for update 是什么含义，会锁表还是锁行或是其他。

        select for update 语句是我们经常使用手工加锁语句。借助for update子句，我们可以在应用程序的层面手工实现数据加锁保护操作。属于并发行锁，这个我们上面在悲观锁的时候也有介绍。

11、为什么要用Btree实现，它是怎么分裂的，什么时候分裂，为什么是平衡的。

       Key 超过1024才分裂
       因为随着数据的增多，一个结点的key满了，为了保持B树的特性，就会产生分裂，就向红黑树和AVL树为了保持树的性质需要进行旋转一样！

12、数据库的ACID是什么。

      ☛ A，atomic，原子性，要么都提交，要么都失败，不能一部分成功，一部分失败。
      ☛ C，consistent，一致性，事物开始及结束后，数据的一致性约束没有被破坏
      ☛ I，isolation，隔离性，并发事物间相互不影响，互不干扰。
      ☛ D，durability,持久性，已经提交的事物对数据库所做的更新必须永久保存。即便发生崩溃，也不能被回滚或数据丢失。

13、某个表有近千万数据，CRUD比较慢，如何优化。

    ◥ 数据千万级别之多，占用的存储空间也比较大，可想而知它不会存储在一块连续的物理空间上，而是链式存储在多个碎片的物理空间上。可能对于长字符串的比较，就用更多的时间查找与比较，这就导致用更多的时间。
   1）、作为关系型数据库，是什么原因出现了这种大表？是否可以做表拆分，减少单表字段数量，优化表结构。
   2）、在保证主键有效的情况下，检查主键索引的字段顺序，使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致。
   3）、在程序逻辑中采用手动事务控制，不要每插入一条数据就自动提交，而是定义一个计数器，进行批量手动提交，能够有效提高运行速度。

14、Mysql怎么优化table scan的。

    ☈ 避免在where子句中对字段进行is null判断。
    ☈ 应尽量避免在where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
    ☈ 避免在where 子句中使用or 来连接条件。
    ☈ in 和not in 也要慎用。
    ☈ Like查询（非左开头）。
    ☈ 使用NUM=@num参数这种。
    ☈ where 子句中对字段进行表达式操作num/2=XX。
    ☈ 在where子句中对字段进行函数操作。

15、如何写sql能够有效的使用到复合索引。

    由于复合索引=组合索引，类似多个木板拼接在一起，如果中间断了就无法用了，所以要能用到复合索引，首先开头(第一列)要用上，比如index(a,b) 这种，我们可以select table tname where a=XX 用到第一列索引 如果想用第二列 可以 and b=XX 或者and b like‘TTT%’。

16、mysql中in 和exists 区别。

   ♒mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。
   ㊤、如果查询的两个表大小相当，那么用in和exists差别不大。
   ㊥、如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
   ㊦、not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
   ▁▂▃ EXISTS只返回TRUE或FALSE，不会返回UNKNOWN。
   ▁▂▃ IN当遇到包含NULL的情况，那么就会返回UNKNOWN。

17、数据库自增主键可能的问题。

      【1】、使用自增主键对数据库做分库分表，可能出现一些诸如主键重复等的问题。
      【2】、数据库导入的时候，可能会因为主键出现一些问题。
       可参考博客：https://yq.aliyun.com/articles/38438

18、MVCC的含义，如何实现的。

        MVCC：Multi-Version Concurrency Control 多版本并发控制，MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事务内存。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。

19、你做过的项目里遇到分库分表了吗，怎么做的，有用到中间件么，比如sharding jdbc等,他们的原理知道么。

       垂直分表：垂直分表在日常开发和设计中比较常见，通俗的说法叫做“大表拆小表”，拆分是基于关系型数据库中的“列”（字段）进行的。通常情况，某个表中的字段比较多，可以新建立一张“扩展表”，将不经常使用或者长度较大的字段拆分出去放到“扩展表”中，如下图所示：
      
       垂直分库：垂直分库在“微服务”盛行的今天已经非常普及了，基本思路是按照业务模块划分不同的数据库，而不是像早起一样将所有的数据库表都放到同一个库中。
        
      水平分表：水平分表也称为横向分表，比较容易理解，就是将表中不同的数据按照一定规律分布到不通的数据库表中，这样来降低单表的数据量，优化查询性能，水平分表能降低单表数据量，一定程度上可以缓解查询性能的瓶颈，但本质上这些表保存在同一个库中，所以库级别还是会有io瓶颈。
      水平分库分表：平分库分表与上面讲到的水平分表思路相同，唯一不同就是将这些拆分出来的表保存在不同的数据库中。
     
     sharding jdbc实现博客：https://blog.csdn.net/faye0412/article/details/78351414

20、MYSQL的主从延迟怎么解决。

       ❤ 实际上主从同步延迟根本没有什么一招制敌的办法，因为所有的SQL必须都要在从服务器里面执行一遍，但是主服务器如果不断的有更新操作源源不断的写入， 那么一旦有延迟产生，那么延迟加重的可能性就会原来越大。 当然我们可以做一些缓解的措施。
      a）、最简单的减少slave同步延时的方案就是在架构上做优化，尽量让主库的DDL快速执行。还有就是主库是写，对数据安全性较高，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也 可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave。
      b）、把一台从服务器当作备份使用， 而不提供查询， 这样他的负载就下来了， 执行relay log 里面的SQL效率自然就高了。 
      c）、增加从服务器，这个目的还是分散读的压力， 从而降低服务器负载。

21、

11、

12、

13、

14、

15、

16、

17、

18、

19、

20、

21、

22、