MySQL

优化

在MySQL中，如何定位慢查询？

通常情况下，慢查询出现在聚合查询，多表查询，表数据量过大，深度分页查询等情况下，导致页面加载时间过慢，接口测试响应时间过长。

我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟；我们系统中当时采用了运维工具 ( Skywalking )，可以监测出哪个接口，最终确定是因为sql的问题；然后在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中(调试阶段)

慢查询日志记录了所有执行时间超过指定参数 (long_query_time，单位:秒，默认10秒)的所有SQL语句的日志

如果要开启慢查询日志，需要在MySQL的配置文件(/etc/my.cnf)中配置

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

配置完毕之后，通过指令/var/lib/mysql/localhost-slow.log重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息

接口测试响应时间过长。如何确定就是MySQL的问题？如何找到？

接口测试响应时间过长，可以通过调试工具或者在MySQL中开启慢日志查询来确定是MySQL的问题。

开源调试工具Arthas，Promethus，Skywalking

MySQL自带慢日志，把一些执行速度慢的sql记录到一个日志文件，

MySQL自带的慢日志记录了所有执行时间超过指定参数的所有SQL语句的日志；开启慢查询日志的话，需要在MySQL的配置文件中配置。

这条SQl执行的很慢，如何优化呢?

先通过SQL执行计划看能不能找到SQL执行的慢的原因，如果是聚合查询可以尝试增加一个临时表去解决，如果是多表查询可以优化sql的结构，表数据量过大的话可以添加索引；如果是深度分页就要使用覆盖索引

如果一条sql执行很慢的话，我们通常会使用mysql自带的执行计划explain来去查看这条sql的执行情况，比如在这里面可以通过key和key len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了,可以尝试添加索引或修改返回字段来修复

SQL执行计划

MySQL自带的分析工具EXPLAIN

通过key和key_len检查是否命中了索引(索引本身存在是否有失效的情况)
通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描
通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

语法：直接在select语句之前加上关键字 explain / desc，会返回一个表

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

possible key当前sql可能会使用到的索引
key 当前sql实际命中的索引
key_len 索引占用的大小
通过它们两个查看是否可能会命中索引
Extra额外的优化建议

Using where; Using Index：查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数护
Using index condition：查找使用了索引，但是需要回表查询数据

type：这条sql的连接的类型，性能由好到差为NULL、system、const、eg ref、ref、range、 index、all

了解过索引吗?

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

因为创建索引的时候就需要排序了

索引是帮mysql高效获取数据的数据结构，MySQL除了存储数据，数据库系统还维护着B+树这种数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法；这种数据结构就是索引。

索引的底层数据结构了解过嘛?

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

因为二叉树不太稳定，可能退化成链表

红黑树可以保证平衡，但是红黑树也是二叉树，每个节点只能有两个分支，数据量过大，树的高度过高，查找数据时叶需要找很多层级，效率不高

b树是一种多叉路平衡查找树，每个节点可以有多个分支

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，lnnoDB存储引擎就是用B+Tree实现其索引结构

【以一棵最大度数max degree为5（5阶）的bTree为例，这个b树每个节点最多存储4个key；5阶b树，5个指针存4个数】

B树与B+树对比:

磁盘读写代价B+树更低：非叶子节点只存储指，不查数据了

查询效率B+树更加稳定：查找路径差不多

B+树便于扫库和区间查询：叶子结点之间用指针连接，进行范围查询时更方便，不需要再次从根节点再查询一次

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

聚簇索引(聚集索引):数据与索引放到一起，B+树的叶子节点保存了整行数据，有且只有一个，必须有

非聚簇索引(二级索引):数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个

聚集索引选取规则:

如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一 (UNIQUE)索作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

知道什么是回表查询嘛 ?

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

单独给字段创建的索引大部分是二级索引

在其他地方比如name添加了索引，非聚簇索引，在树的叶子结点只存储name和id，查询gender时就查不到，需要用id回聚集索引的树中查询gender，就是回表

知道什么是覆盖索引吗?

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到

使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *

MySQL超大分页分页如何处理？

可以使用覆盖索引解决

先分页查询获取表中的id，对表的id进行排序，就能筛选出分页之后的id集合，因为id是覆盖索引，操作id的时候，效率相对来说比较高，最后通过id集合到原来的表中做关联查询，效率就会得到提升

select * from tb limit 0,10;
select * from tb limit 900000000000,10;

因为，当在进行分页查询时，如果执行 limit 9000000,10，此时需要MySQL排序前9000010 记录，仅仅返回9000000 - 9000010 的记录，其他记录丢弃，查询排序的代价非常大。

一般分页查询时，通过创建覆盖索引提高性能，可以通过覆盖索引加子查询进行优化

select * from tb t
	(select id from tb order by id limit 900000000000,10) a
where t.id = a.id

索引创建原则有哪些？

数据量较大，且查询比较频繁的表（重要）

常作为查询条件、排序、分组的字段字段（重要）

内容区分度高

内容较长，使用前缀索引

尽量联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。（重要）

要控制索引的数量，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。（重要）

如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它

针对于数据量较大，且查询比较频繁的表建立索引。单表超过10万数据 (增加用户体验)

针对于常作为查询条件 (where)、排序 (order by)、分组(group by) 操作的字段建立索引。

尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

【给tb_seller创建联合索引tb_seller_index，字段顺序: name，status，address】可能会失效

要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪索引最有效地用于查询。

什么情况下索引会失效?

违反最左前缀法则

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

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

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

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

索引失效的情况有很多，可以说一些自己遇到过的，不要张口就得得得说一堆背诵好的面试题(适当的思考一下，回想一下，更真实)

给tb_seller创建联合索引tb_seller_index，字段顺序: name，status，address

违反最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引:

explain select * from tb_seller where status ='1' and address ='北京市';

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

根据前面的两个字段 name，status 查询是走索引的，但是最后一个条件address 没有用到索引。

explain select * from tb_seller where name ='小米科技 and status >'1' and address ='北京市';

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

explain select * from tb_seller where substring(name,3,2) ='科技';

字符串不加单引号，造成索引失效。

由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效

explain select * from tb_seller where status =1;

模糊查询可能会造成索引失效

以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

explain select sellerid, name from tb_seller where name like '%张';

谈一谈你对sql的优化的经验

表的设计优化
索引优化:创建原则和索引失效
SQL语句优化
主从复制、读写分离
分库分表

表的设计优化

(参考阿里开发手册《嵩山版》
比如设置合适的数值 (tinyint int bigint)，要根据实际情况选择
比如设置合适的字符串类型 (char和varchar) char定长效率高，varchar可变长度，效率稍

SQL语句优化

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

for(i=0;i<3;i++){
    for(j=0;j<1000;j++){
        
    }
}

主从复制，读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响可以采用读写分离的架构。
读写分离解决的是，数据库的写入，影响了查询的效率。

MySQL其他面试题

事务的特性是什么?可以详细说一下吗?

嗯，这个比较清楚，

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务的特性是ACID，分别指的是: 原子性、一致性、隔离性、持久性:我举个例子:A向B转账500，转账成功，A扣除500元，B增加500元，

原子操作体现在要么都成功，要么都失败；

在转账的过程中，数据要一致，A扣除了500，B必须增加500

在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰；

持久性体现在事物提交后，要把数据持久化

原子性( Atomicity )、一致性( Consistency)、隔离性(lsolation)、持久性( Durability )

并发事务带来哪些问题?--怎么解决这些问题呢?-- MySQL的默认隔离级别是?

脏读、不可重复读、幻读

解决方案：对事物进行隔离

隔离级别:读未提交、读已提交、可重复读、串行化

脏读：一个事务读到另外一个事务还没有提交的数据

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

隔离级别	脏读	不可重复读	幻读
Read uncommitted 未提交读
Read committed 读已提交	1
Repeatable Read(默认)可重复读	1	1
Serializable串行化	1	1	1

串行化是一个事物提交之后其他的事物才能接着运行，几乎放弃了并发，效率低

事务隔离级别越高，数据越安全，但是性能越低，默认可重复读

undo log和redo log的区别?

都属于MySQL的日志文件

redo log记录的是数据页的物理变化，服务宕机可用来同步数据；

undo log记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据；

redolog保证了事务的持久性，undolog保证了事务的原子性和一致性

缓冲池(buffer pool):主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存)，以一定频率刷新到磁盘，从而减少磁盘10，加快处理速度

数据页(page):是lnnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。页中存储的是行数据

增删改数据的时候，会先在【内存结构】缓冲池Buffer pool中记录，然后同步到【磁盘结构】磁盘数据页(xxx.ibd)中，先操作Buffer pool，看有没有需要操作的数据，没有的话就从磁盘把数据加载到内存，把某一页的数据加入到缓冲池，操作内存效率高，操作完后按一定频率把数据同步到磁盘，减少磁盘IO，加快处理速度。

当前已经操作完的数据，在内存中存储的页还没有存储到磁盘，被称为脏页，脏页需要同步到磁盘中才算是持久化了，但是同步过程中服务器宕机了，同步失败，内存中的数据可能会消失，数据就可能丢失，已经操作完的数据也丢失了，就违背了持久化，

redo log重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲(redo og buffer)以及重做日志文件(redo og fle)，前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘,发生错误时，进行数据恢复使用。

当有增删改的时候，buffer pool已经发生了变化，同时redolog buffer中记录这些数据页的变化，一旦redolog buffer发生了变化，就会同步的把数据记录到磁盘文件redolog file中；如果同步失败，就会从redolog中恢复数据

为什么不直接从buffer pool同步到磁盘？

在操作时可能会有多条记录，如果同步刷新，在保存数据到磁盘的时候，都是随机的磁盘IO，性能比较低，使用redo log之后，进行数据同步的时候是顺序的磁盘IO，因为日志文件都是追加的，顺序的磁盘IO性能就提升了很多

以上也叫WAL机制，write ahead logging

当脏页数据能正常同步到磁盘时，redolog就没什么用了，每隔一段时间清理一下，磁盘中有两份redolog日志，是循环写的

undo log回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和 MVCC(多版本并发控制)。它是逻辑日志

可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然

当update一条记录时，它记录一条对应相反的update记录。当执行rolback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

undo log可以实现事务的一致性和原子性

好的，事务中的隔离性是如何保证的呢?

事务的隔离性是由锁和mvcc实现的。

排他锁(如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁)

主从同步原理，主从如何进行同步的

MySQL主从复制的核心就是二进制日志binlog日志，记录了所有的 DDL(数据定义语言)DML(数据操纵语言)语句，但不包括数据查询(SELECT、SHOW)语句.

主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中，此时从库有一个IOthread线程专门从主库的binlog日志中读取数据，写入到从库的中继日志Relay Log。再由从库的SQLthread线程读取中继日志的文件，把里面的命令重新执行一下，然后就保持同步了

你们项目用过分库分表吗？

业务介绍
1，根据自己简历上的项目，想一个数据量较大业务（请求数多或业务累积大）

2，达到了什么样的量级(单表1000万或超过20G)

具体拆分策略
1，水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题

2，水平分表，解决单表存储和性能的问题

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

4，垂直分表，冷热数据分离，多表互不影响

同时大量访问数据库，主从复制读写分离只是减轻访问压力，不能解决存储压力

分库分表，解决存储压力

时机