mysql存储引擎整理（索引+事务+表优化）

1.什么是MySQL?

mysql 是一种关系型数据库，开源方便拓展，并且阿里巴巴等公司也有大量的使用，稳定性有保障；

2.存储引擎

• 一些常用命令：
  • 登录：
    mysql -u root -p :xxxx
  • 查看所有存储引擎：
    show engines;
    
    可以看出该版本下的mysql默认存储引擎是InnoDB,支持事务，行级锁和外键。也是所有存储引擎中唯一支持事务的引擎。
  • 数据库版本：STATUS;
    
  • 数据库默认存储引擎 show variables like ‘%storage_engine%’;
    
  • 查看数据表使用的存储引擎
    show table status like ‘%table_like_name%’
    

3.MyISAM和InnoDB区别

MyISAM 是5.5版本之前默认的存储引擎，性能极佳，支持全文索引等，但是不支持事务和行级锁和外键，且崩溃后无法安全恢复。
- 是否支持事务：
- MYISAM强调的是性能，每次查询具有原子性，效率比InnoDB快，但是不支持事务。
- InnoDB支持事务，外键等高级特性，支持事务（commit）回滚（rollback），事务安全（transaction-safe ），崩溃恢复能力（crash recovery capabilities）
- 是否支持外键：InnoDB支持外键，MyISAM不支持
- 是否支持行级锁：InnoDB支持，MyISAM不支持
- 崩溃后是否可以安全退出

注：《MySQL高性能》上面有一句话这样写到: ‘MyISAM比InnoDB快’，不是绝对的，在很多场景中，InnoDB速度比MYISAM速度快很多，尤其是聚簇索引或者需要访问的数据可以全部放在内存中。

4.字符集

字符集指的是一种从二进制编码到某类字符符号的映射

5.索引

• mysql支持B树索引：B树中B+树

• 哈希索引：哈希表（绝大多数查询为单记录查询时可以使用，查询速度快）

• InnoDB和MYISAM的索引区别

  • MYISAM（非聚簇索引）的数据文件和索引文件是分开的，索引文件中B+树的data域是数据的地址，根据地址再去数据文件读取记录数据
  • InnoDB(聚簇索引)，数据文件本身是索引文件，是按照B+数组织的一个索引结构：数据文件的key是记录的主键，叶子节点的data域是完整的记录；其余索引是辅助索引，先去找寻对应的key的主索引，再根据主索引获取data域中的完整数据。不建议使用字段过长做主键，非单调字段会造成主索引频繁分裂。

6.什么是事务?

事务是逻辑上的一组操作，要么都执行要么都不执行。转账等，-1000，+1000，要么都执行要么都不执行。

7.事务的四大特性(ACID)

• 原子性：事务是最小的执行单位，不允许分割。原子性保证动作要么都执行，要么都不执行。
• 一致性：事务执行前后，数据是一致的。多个事务对数据的读取是一致的。
• 隔离性：多个事务并发执行时，单个事务不被其他事务影响，各事务间独立。
• 永久性：事务执行后对数据的影响是永久的，即使数据库发生故障也不影响数据的永久改变。

8.并发事务带来哪些问题?

• 脏读
  当事务A一个数据正在访问数据并对改数据进行修改时，在修改提交到数据库之前，另一个事务也访问了数据，然后又使用了该数据，读的数据就是”脏数据“，使用该数据做的操作也可能是不正确的。
• 修改丢失：2个事务同时访问某一数据，事务1修改数据，事务2也修改了数据，这样第一个事务我修改的数据就会丢失。data=20，A-1,B-1，最后结果是19，事务Ade修改丢失。
• 不可重复读
  同一个事务多次读取同一个数据时，另一个事务修改了该数据，导致同一个事务多次读取的数据不一致，因为不可重复读。
• 幻读
  同一个事务读取几行记录时，另一个事务新增或修改了几行数据，导致多次读取的行数不一致。称为幻读。

不可重复读和幻读区别：
不可重复读和幻读都是多次读取发生的，前者是数据的字段，后者是记录的行数。

9.事务隔离级别有哪些?MySQL的默认隔离级别是?

• READ_UNCOMMITTED（读取未提交）：最低的隔离级别，允许读取未提交的数据。可产生脏读，幻读和不可重复读
• READ_COMMITTED（读取已提交）：允许读取已提交的数据，避免了脏读，但是可能产生不可重复读和幻读
• REPEATABLE-READ (可重复读)：对同一字段的读取结果一致，除非是自己本身事务做了修改，避免了脏读和不可重复读，可能产生幻读。
  SERIALIZABLE（可串行化):最高的隔离级别，完全服从ACID的隔离级别，所有事务逐次执行。事务之间完全不会干扰。

隔离级别	       脏读	不可重复读	幻读
READ-UNCOMMITTED	√	    √       	√
READ-COMMITTED  	×   	√       	√
REPEATABLE-READ 	×	    ×	       √
SERIALIZABLE        ×   	×       	×

查看数据库隔离级别
show variables like ‘%isolation%’(8.0已弃用tx_isolation,transaction_isolation)

InnoDB存储引擎在REPEATABLE-READ隔离级别情况下使用的是NEXT-KEY locking机制，所以可以避免幻读。与其他数据库系统是不同的。
REPEATABLE-READ隔离级别可以完全达到事务的隔离性要求，在分布式事务中一般会使用到可串行化隔离级别。
大部分的数据库系统的隔离级别都是 读取已提交,隔离级别越少，请求的锁也会越少，但InnoDb的默认隔离级别-REPEATABLE-READ（可重读）也是没有性能损失的。

10.大表优化

• 1. 限定数据的范围
     禁止不带任何不带限制条件的查询，如查询历史订单，限制一个月内的查询。
• 2. 读/写分离
     主库负责写，从库负责读
• 3. 垂直分区
  • 定义：根据数据库里面的数据的相关性进行拆分，例如：用户表里既有用户基本信息，又有用户的登录信息，就可以做成2个表，或者放在不同的库中。
    简单的说就是一个表的列根据相关性拆分成多张表。
  • 优点：可以使列数据变小，简化表的结构，易于维护。并且减少block的次数和I/O的次数
  • 缺点：相关列出现冗余，垂直分区可以让事务变得复杂。
    
• 4. 水平分区
  • 定义：保持表的结构一致，通过某种策略存储数据分片。对数据表行的拆分，表行数超过200w行就会变慢，可以拆成多个库多个表进行查询，可以支撑非常大的数据量。
  • 优点：分表是为了解决数据量大的问题，最好分库，否则并发情况下在同库（同机器）上并没有多大的影响。可以支持非常大的数据量，应用端改造也小
  • 缺点： 分片事务难以解决，多节点join性能差，逻辑复杂。尽量不分片，在数据表优化得当的情况下支撑千万以下没问题。如果选择分片可以选择客户端分片架构，减少一次和中间件的网络I/O.

10.锁机制与InnoDB锁算法

https://blog.youkuaiyun.com/qq_34337272/article/details/80611486

11.分库分表之后,id 主键如何处理？

生成全局 id 有下面这几种方式

• UUID：不适合做主键，长，无序不可读，查询效率低
• 数据库自增：
• 利用 redis 生成 id ：性能好，不依赖数据库。但引入新的组件性能低，复杂读高，增加了系统的成本。
• Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake
• 美团的Leaf分布式ID生成系统 https://tech.meituan.com/2017/04/21/mt-leaf.html 。

12.一条SQL语句在MySQL中如何执行的

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

13.MySQL高性能优化规范建议

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

14.一条SQL语句执行得很慢的原因有哪些？

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