MySql相关

优化

InnoDB、MyISAM存储引擎的区别

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务； 

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败； 

3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

 MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

       也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

5. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

6. MyISAM表格可以被压缩后进行查询操作

7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

定位慢查询

1.使用运维工具（Skywalking），检测出接口的查询速度

2.在MySQL中开启慢查询日志，设置2秒一旦sql超过2秒会记录在日志当中（在调试阶段开启）

开启MySQL慢日志查询开关
slow_query_log=1
设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=1
 

SQL语句执行很慢，如何分析

可以采用MySQL自带的分析工具explain来去查看这条sql的执行情况
● 通过key和key_len检查是否命中了索引（索引本身存在是否有失效的可能）
● 通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描
● 通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

什么是索引

● 索引是帮助MySQL高效获取数据的数据结构（有序）
● 提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）
● 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引的底层结构了解过嘛

● MySQL默认的存储引擎InnoDB采用的B+树来存储索引
● B+树阶数更多，路径更短
● 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据
● B+树偏于扫库和区间查询，叶子节点是一个双向链表

B树和B+树的区别是什么

在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有数据都会出席那在叶子节点中，在查询的时候，B+树查找效率更加稳定
在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，而且叶子节点是一个双向链表

什么是聚簇索引什么是非聚簇索引

聚簇索引（聚集索引）：数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个
非聚簇索引（二级索引）：数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个

什么是回表查询

通过二级索引找多对应的主键值，到聚簇索引中查找整行数据，这个过程就是回表

什么是覆盖索引

覆盖索引是指查询使用了索引，返回的列，必须在索引中全部被找到
● 使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高
● 如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *

MySQL超大分页如何处理

问题：在数据量比较大时，limit分页查询，需要对数据进行排序，效率低
解决方案：覆盖索引+子查询
select * from tb_sku limit 9000000,10
这条sql语句可以优化为
select * from tb_sku t,(select id from tb_sku order by id limit 9000000,10) a where t.id = a.id

索引创建原则有哪些

1. 数据量较大（超过10万以上），且查询比较频繁的表
2. 常作为查询条件、排序、分组的字段
3. 字段内容区分度高
4. 内容较长，使用前缀索引
5. 尽量联合索引
6. 要控制索引的数量
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它

什么情况下索引会失效

违反最左前缀法则

范围查询右边的列，不能使用索引

不要在索引列上进行运算操作，索引将失效

字符串不加单引号，造成索引失效。（类型转换）

以%开头的Like模糊查询，索引失效

对sql优化的

表的设计优化

1. 比如设置合适的数值（tinint int bigint），要根据实际情况选
2. 比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低

SQL语句优化

select语句务必指明字段名称（避免直接使用select *）
SQL语句要避免照成索引失效的写法
尽量用union all代替union ，union会多一次过滤，效率低
避免在where子句中对字段进行表达式操作
Join优化 能用innerjoin就不用left join right join，如必须使用 一定要以小表为驱动
内连接会对两个表进行优化，优先把小表放在外边，把大表放在里边。但left join和right join,不会重新调整顺序

其他

请解释MySQL中的InnoDB和MyISAM存储引擎的区别

InnoDB存储引擎：

• 事务支持：InnoDB支持ACID事务（原子性、一致性、隔离性、持久性），这意味着它可以很好地处理并发操作，确保数据的完整性。
• 行级锁定：InnoDB使用行级锁定机制，这允许在大量并发写入时提供更好的性能，因为它只锁定必要的行而不是整个表。
• 外键约束：InnoDB支持外键约束，这有利于维护引用完整性。
• MVCC（多版本并发控制）：InnoDB实现了MVCC来提高读取操作的性能，它允许多个事务看到不同版本的数据，从而避免了读-写和读-读之间的锁争用。
• 数据恢复能力：由于InnoDB使用了重做日志（redo log），即使在系统崩溃的情况下也可以保证数据的恢复。
• 缓冲池：InnoDB有自己的缓冲池，用来缓存数据和索引，减少磁盘I/O操作。

举例说明：如果你正在构建一个电子商务网站，需要处理大量的并发订单和支付事务，InnoDB将是更好的选择，因为它提供了事务的完整性和行级锁定的优势

MyISAM存储引擎：

• 不支持事务：MyISAM不支持事务处理，这使得它不适合需要高事务完整性的应用。
• 表级锁定：MyISAM仅支持表级锁定，对于含有大量并发写入的应用来说可能会成为一个瓶颈。
• 全文索引：MyISAM支持全文索引，这对于执行全文搜索很有用。
• 较小的内存足迹：MyISAM相对于InnoDB来说占用更少的内存和磁盘空间。
• 无外键约束：MyISAM不支持外键，这意味着所有的参照完整性需由应用逻辑来保证。
• 数据恢复：MyISAM表易于维护，因为它们不包含事务日志，但在系统崩溃后难以恢复数据。

 举例说明：如果你有一个主要是读取操作的大型数据集，比如一个只读的文档存储库或公共信息数据库，MyISAM可能是合适的选择，因为它可以提供快速的读取性能且管理起来较为简单。

总的来说，如果你需要事务支持和行级锁定以处理复杂的并发操作，InnoDB通常是更好的选择。而如果你的应用不需要事务，并且更注重读取速度和全文搜索功能，MyISAM可能更加适合。

 脏读、不可重复读、幻读

脏读

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

脏读最大的问题就是可能会读到不存在的数据。比如在上图中，事务B的更新数据被事务A读取，但是事务B回滚了，更新数据全部还原，也就是说事务A刚刚读到的数据并没有存在于数据库中。简单说，脏读事务A读出了一条不存在的数据，这个问题是很严重的。

不可重复读

不可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据出现不一致的情况。

事务 A 多次读取同一数据，但事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

幻读

幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读

假设有张用户表,这张表的 id 是主键。表中一开始有4条数据。

我们再来看下出现 幻读 的场景

最终 事务A 提交事务，发现报错了。这就很奇怪，查的时候明明没有这条记录，但插入的时候 却告诉我 主键冲突，这就好像幻觉一样。这才是所有的幻读。

不可重复读侧重表达 读-读，幻读则是说 读-写，用写来证实读的是鬼影。

事务的隔离级别

• 上述所说的"脏读"，“不可重复读”，"幻读"这些问题，其实就是数据库读一致性问题，必须由数据库提供的事务隔离机制来进行解决。
• 首先说读未提交，它是性能最好，也可以说它是最野蛮的方式，因为它压根儿就不加锁，所以根本谈不上什么隔离效果，可以理解为没有隔离。
• 再来说串行化。串行化就相当于上面所说的，处理一个人请求的时候，别的人都等着。读的时候加共享锁，也就是其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读。
• 最后说读提交和可重复读。这两种隔离级别是比较复杂的，既要允许一定的并发，又想要兼顾的解决问题。MySQL默认事务隔离级别为可重复读(RR),oracle默认事务隔离级别为读已提交(RC),
• 数据库的事务隔离越严格，并发副作用越小，但付出的代价越大；因为事务隔离本质就是使事务在一定程度上处于串行状态，这本身就是和并发相矛盾的。
• 同时，不同的应用对读一致性和事务隔离级别是不一样的，比如许多应用对数据的一致性没那么个高要求，相反，对并发有一定要求。

请解释MySQL中的事务隔离级别 

1.读未提交（Read Uncommitted）：

读未提交是最低的隔离级别，它允许一个事务读取另一个事务尚未提交的数据。这种隔离级别可能导致脏读（Dirty Read），即读取到其他事务修改后但尚未提交的数据。

-- 设置事务隔离级别为读未提交
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

 2.读已提交（Read Committed）：

读已提交是默认的隔离级别，它要求一个事务只能读取另一个事务已经提交的数据。这种隔离级别可以避免脏读，但仍可能导致不可重复读（Non-repeatable Read），即在一个事务内多次读取同一行数据时，结果可能不一致。

-- 设置事务隔离级别为读已提交
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

3.可重复读（Repeatable Read）：

可重复读是一种较高的隔离级别，它要求一个事务在整个过程中可以多次读取同一行数据，并且结果保持一致。这种隔离级别可以避免脏读和不可重复读，但仍可能导致幻读（Phantom Read），即在一个事务内执行两次相同的查询时，可能会得到不同的结果集。

-- 设置事务隔离级别为可重复读
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4.串行化（Serializable）：

串行化是最高的隔离级别，它要求对数据的访问必须按照事务的顺序进行，确保所有事务都能按照顺序执行，避免脏读、不可重复读和幻读。这种隔离级别具有最高的数据一致性，但性能开销也最大。

-- 设置事务隔离级别为串行化
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

总结：

• 读未提交（Read Uncommitted）允许读取未提交的数据，可能导致脏读；
• 读已提交（Read Committed）只允许读取已提交的数据，避免了脏读，但仍可能导致不可重复读；
• 可重复读（Repeatable Read）允许多次读取同一行数据，结果保持一致，避免了脏读和不可重复读，但仍可能导致幻读；
• 串行化（Serializable）要求按事务顺序访问数据，避免了脏读、不可重复读和幻读，具有最高的数据一致性，但性能开销最大。

 undo log 和redo log的区别

• redo log：记录的是数据页的物理变化，服务宕机可用来同步数据
• undo log :记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据
• redo log：保证了事务的持久性，undo log保证了事务的原子性和一致性

MySQL中的多版本并发控制（MVCC）是什么

多版本并发控制（MVCC）是一种用于提高数据库系统并发性能的技术。在MySQL的InnoDB存储引擎中，MVCC允许读取操作在不加锁的情况下进行，即使其他事务正在修改数据。这通过保留数据的不同版本来实现，使读取操作可以访问数据的早期版本。

1.隐藏字段：trx_id(事务ID)，记录每次操作的事务ID，是自增的、roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址

2.undo log：回滚日志，存储老版本的数据、版本链，多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表

3.readView：解决的是事务查询选择版本的问题

主从同步原理

• 主库在事务提交时，会把记录变更记录在二进制文件中Binlog中
• 从库读取二进制文件（Bin Log），写到中继日志中（Relay Log）
• 从库从中继日志中获取改变的数据，同步到自己的数据中

分库分表

前提是：业务数据增多，业务发展迅速，单表的数据量达到了1000W条或者20G以后

水平分库：将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题（MyCat）

水平分表：解决单表存储和性能的问题（MyCat）

垂直分库： 根据业务进行拆分，高并发下提高磁盘IO和网络连接数

垂直分表：冷热数据分离，多表互补影响