MySql整理

目录

1.如何定位慢查询？

2.如何分析和优化SQL？

3.什么是索引

1.索引：

2.索引的底层数据结构？

4.什么是聚簇索引，什么是非聚簇索引（二级索引）？什么是回表查询？

1.聚簇索引：

2.聚簇索引的选取规则：

3.非聚簇索引：

4.回表查询：

5.什么是覆盖索引？

6.如何处理超大分页问题？

7.索引创建的原则有哪些？

8.事务的特性

9.并发事务带来的问题？如何解决？

10.undo log 和 redo log的区别？

11.MySql主从同步原理？

12.MySql的分库分表

1.如何定位慢查询？

1.可以使用运维工具（例如SkyWalking）,可以监测出哪个接口，查看是否是sql的原因；

2.可以再mysql中开启慢查询日志，设置值为2秒，一但sql执行超过2s就会记录到日志中。

ps：slow_query_log = 1(开启慢查询日志） slow_query_log_file=(指定日志位置)

long_query_time = 2(指定查询时间为超过2秒)

2.如何分析和优化SQL？

可以使用mysql自带的分析工具EXPLAIN

explain显示的比较重要的字段：

（1）type：连接类型，表示mysql如何查找表中的行，常见的类型包括all（全盘扫描）、index（全索引扫描）、range（索引范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）、const（常量扫描）、system（表只有一行）

（2）key:实际使用的索引，key_len：使用的索引长度。

（3）extra：额外的信息，例如 using where（使用了where子句）、using index（使用了覆盖索引）

1.通过key 和 key_len检查是否命中了索引（索引本身存在是否有失效的情况）

2.通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或者全盘扫描（index或者all）

3.通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

3.什么是索引

1.索引：

        是帮助MySql高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

2.索引的底层数据结构？

MySQl的InnoDB引擎采用的B+树的数据结构来存储索引。        

1.阶数更多，路径更短。

2.磁盘读写代价B+树更低，非叶子节点值存储指针，叶子节点存储数据。(相比于B树，B+树只在叶子节点处存储记录，减少了磁盘I/O的次数）

3.B+树便于扫库和区间查询，叶子结点就是一个双向链表。(因为叶子节点处将数据用双向链表连接，便于范围查询)
4.B+树有大量的冗余节点，插入和删除时不会效率很高，比如删除根节点时不会像B树那样发生复杂的树的变化；

3.索引失效的几种情况？

1. 对索引使用左或者左右模糊匹配：因为索引B+树是按照索引值有序排列存储的，只能根据前缀进行比较，如果开头的左边是模糊的，就不能确定比较的起点；
2. 对索引使用函数：因为索引保存的是索引字段的原始值，而不是经过函数计算后的值；
3. 对索引进行表达式计算：原因与使用函数一样，索引保存的是索引字段的原始值，而不是索引运算后的值；
4. 对索引进行了隐式类型转换：mysql中在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较；本质是对索引项使用了cast函数进行了类型转换，导致索引失效；
5. 联合索引非最左匹配：在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同才会按照第二列排序；也就是说如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列；

4.什么是聚簇索引，什么是非聚簇索引（二级索引）？什么是回表查询？

1.聚簇索引：

        也叫聚集索引，数据与索引放到一块，B+树的叶子节点存储了整行数据，有且只有一个。

2.聚簇索引的选取规则：

1. 如果存在主键，主键索引就是聚簇索引；
2. 如果不存在主键，将使用第一个不包含null值的唯一（UNIQUE）索引作为聚簇索引；
3. 如果表没有主键，或没有找到合适的唯一索引，则InnoDB会自动生成一个唯一的rowId作为隐藏的聚簇索引；

3.非聚簇索引：

        也叫二级索引，数据与索引分开存储，B+树的叶子节点保存的是对应的主键，二级索引可以有多个。

4.回表查询：

        从二级索引中查找到主键值，再回到聚簇索引中查询到整行数据，这样的查找过程就叫回表查询。思考：从二级索引查找一定会触发回表吗？不一定，只要查询的列在二级索引中有储存，就不用回表，相当于实现了索引覆盖。

5.什么是覆盖索引？

覆盖索引是指查询使用了索引，返回的列必须在索引中能全部找到。当使用id查询时，直接走聚簇索引查询，一次扫描，全部返回，查询效率高；如果返回的列中没有创建索引，有可能会触发回表查询，所以在使用中尽量避免select *;

6.如何优化索引？

1. 前缀索引优化：使用某个字段中字符串的前几个字符建立索引，好处在于减小索引的大小，这样一个索引页中就可以存下更多的索引值，提高索引的查询速度；不过order by就无法使用前缀索引，

7.如何处理超大分页问题？

超大分页一般是都是在数据量较大时，我们使用了limit分页查询，并且需要对数据进行排序，这时效率较低，可以采用覆盖索引 + 子查询进行优化。具体做法是先查询数据的id字段，确定了id之后，再用子查询来进行过滤，只查询这个id列表就可以了。因为使用id查询，走的是覆盖索引，效率可以提升很多。

8.索引创建的原则有哪些？

1. 针对数据量比较大，查询比较频繁的表建立索引；
2. 针对于常作为查询条件where、排序order by、分组group by的字段建立索引；
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高；
4. 如果是字符串类型的索引，字段的长度较长，可以针对字段的特点，建立前缀索引；
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以形成覆盖索引，节省存储空间，避免回表，提高查询效率；
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引的代价就越大，也会影响增删改的效率；
7. 如果索引列不能存储null值，请在创建表时用not null进行约束，当优化器知道每列是否包含null值时，可以帮助它确定那个索引可以更好的用于查询；

9.事务的特性?InnoDB如何保证事务特点的正常实现？

1. 事务：是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销请求，即这些操作要么同时成功，要么同时失败；
2. 事务的特性，概括为ACID：原子性、一致性、隔离性、持久性

• A(Atomicity):事务是不可分割的最小单元，要么全部成功，要么全部失败；
• C(Consistency):事务完成时，必须使所有数据都保持一致状态；
• I(Isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的环境下独立运行；
• D(Durability):事务一旦提交或回滚，它对数据库中数据的改变就是永久的；

    3.原子性是通过undo log（回滚日志）来保证的；一致性是通过redo log（重做日志）来保证      的；隔离性是通过MVCC（多版本并发控制）或锁机制来保证的；一致性是通过持久化 + 原子性 + 隔离性来保证的； 

10.并发事务带来的问题？如何解决？

• 问题：

1. 脏读：一个事务读到了另一个事务还未提交的数据；
2. 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”（幻读侧重于读取的记录的数量，而不可重复读侧重于数据本身）；
3. 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同（因为其他事务对该条记录进行了更新并完成提交）；

   隔离级别	脏读	不可重复读	幻读
   读未提交	√	√	√
   读已提交	×	√	√
   可重复读（默认）	×	×	√
   串行化	×	×	×

• 读未提交：指一个事务还未提交时，它做的变更就能被其他事务看到；
• 读已提交：指一个事务提交之后，它做的变更才能被其他事务看到；
• 可重复读：指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，它也是mysql InnoDB引擎的默认隔离级别；
• 串行化：会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的情况，后访问的事务必须等待前一个事务执行完成，才能继续执行；
• 对于读未提交隔离级别的事务来说，因为可以读到未提交事务所修改的数据，所以直接读取最新的数据就好；
• 对于串行化隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
• 对于读提交和可重复读隔离级别的事务来说，它们是通过Read View来实现的，它们的区别在于创建Read View的时机不同，可以将Read View理解为数据快照，像相机拍照一样定格某一时刻的风景。读已提交隔离级别是在每个语句执行前，都会重新生成一个Read View，而可重复读隔离级别是启动事务时生成一个Read View，然后整个事务期间都在用这个Read View；

11.事务中的隔离性如何保证？（解释一下MVCC)

• MVCC:（multi-veison concurrency control)，多版本并发控制，指通过版本链来控制并发事务访问同一个记录时的行为；
• 隐藏字段:

1. trx_id(事务id)：记录每一次操作的事务id，是自增的；
2. roll_pointer(回滚指针)：指向上一个版本的事务版本记录地址；

• Read View:

1. creator_trx_id:创建该Read View的事务id；
2. m_ids:创建Read View时，当前数据库中活跃且未提交的事务id列表；
3. min_trx_id:创建Read View时当前数据库中活跃且未提交的事务中的最小事务id；
4. max_trx_id:创建Read View时当前数据库中应该给下一个事务的id值；

• undo log:

1. 回滚日志，存储老版本数据；
2. 版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表;
3. readView解决的是一个事务查询选择版本的问题。

12.undo log 和 redo log的区别？

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

1. 根据readView的匹配规则和当前的一些事务id判断该访问哪个版本的数据；
2. 不同的隔离级别快照读是不一样的，最终的访问结果不一样；

RC：每次快照读生成新的ReadView；RR：仅在事务中第一次执行快照读时生成ReadView，后续复用；

13.MySql主从同步原理？

MySql主从复制的核心就是二进制日志binLog(DDL数据定义语句和DML数据操作语句)，不包含查询语句。

1. 主库在事务提交时，会把数据变更记录在二进制日志文件BinLog中；
2. 从库新开线程读取主库的二进制日志文件BinLog，写入到从库的中继日志RelayLog中；
3. 从库重做中级日志中的事件，将改变反映它自己的数据；

14.MySql的分库分表

拆分策略：

1. 水平分库：将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题；
2. 水平分表：解决单表存储和性能问题；
3. 垂直分库：根据业务进行拆分，高并发下提升磁盘IO和网络连接数；
4. 垂直分表：冷热数据分离，多表互不影响；

15.mysql的存储引擎以及每种存储引擎的区别？

1.  MyISAM:不支持事务和外键约束，占用资源较小，访问速度快，表级锁定，支持全文索引，适用于不需要事务处理，单独写入或查询的应用场景；

2. InnoDB:支持事务处理，外键约束，缓存能力较好，支持行级锁定，读写并发能力较好；适用于一致性要求高，更新数据频繁的场景；