MySql面试题

ens
125 阅读34分钟

1694420209626.png

MySQL中，如何定位慢查询?

  • 聚合查询
  • 多表查询
  • 表数据量过大查询
  • 深度分页查询

表象：页面加载过慢、接口压测响应时间过长（超过1s）

方案一：开源工具
调试工具：Arthas 
运维工具：Prometheus 、Skywalking

方案二：MySQL自带慢日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志
如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

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

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

image.png

候选人：

嗯~，我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统Skywalking ，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题

如果，项目中没有这种运维的监控系统，其实在MySQL中也提供了慢日志查询的功能，可以在MySQL的系统配置文件中开启这个慢日志的功能，并且也可以设置SQL执行超过多少时间来记录到一个日志文件中，我记得上一个项目配置的是2秒，只要SQL执行的时间超过了2秒就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的SQL了。

那这个SQL语句执行很慢, 如何分析呢？

1694420077557.png

可以采用EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息

image.png

image.png

possible_key 当前sql可能会使用到的索引
key 当前sql实际命中的索引
key_len 索引占用的大小
Extra 额外的优化建议
image.png 通过key 和 key_len查看是否可能会命中索引

type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all
system：查询系统中的表
const：根据主键查询
eq_ref：主键索引查询或唯一索引查询
ref：索引查询
range：范围查询
index：索引树扫描
all：全盘扫描

eq_ref 比 ref 通常更有效率，因为它可以直接定位到唯一的行记录，而 ref 可能需要进一步筛选匹配的行。

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

MYSQL支持的存储引擎有哪些, 有什么区别 ?

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

在mysql中提供了很多的存储引擎，比较常见有InnoDB、MyISAM、Memory

  • InnoDB存储引擎是mysql5.5之后是默认的引擎，它支持事务、外键、表级锁和行级锁
  • MyISAM是早期的引擎，它不支持事务、只有表级锁、也没有外键，用的不多
  • Memory主要把数据存储在内存，支持表级锁，没有外键和事务，用的也不多

1694419236524.png

存储引擎在mysql的体系结构哪一层，主要特点是什么

1694419295768.png

1694419690801.png

索引

面试官： 了解过索引吗？（什么是索引）

候选人： 嗯，索引在项目中还是比较常见的，它是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗

面试官： 索引的底层数据结构了解过嘛 ?

候选人： MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是：
第一：阶数更多，路径更短
第二：磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据
第三：B+树便于扫库和区间查询，叶子节点是一个双向链表

1694410555911.png

1694410593371.png

1694410687234.png

面试官： B树和B+树的区别是什么呢？

候选人：
第一：在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定

第二：在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表

面试官： 什么是聚簇索引什么是非聚簇索引 ?

候选人：

好的~，聚簇索引主要是指数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个，一般情况下主键在作为聚簇索引的

非聚簇索引（二级索引）指的是数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个，一般我们自己定义的索引都是非聚簇索引

image.png

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

面试官： 知道什么是回表查询嘛 ?

1694412924731.png

1694412988282.png

候选人： 嗯，其实跟刚才介绍的聚簇索引和非聚簇索引是有关系的，回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表

【备注：如果面试官直接问回表，则需要先介绍聚簇索引和非聚簇索引】

面试官： 知道什么叫覆盖索引嘛 ?

1694413668704.png 候选人： 嗯~，清楚的

覆盖索引是指select查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *，尽量在返回的列中都包含添加索引的字段

面试官： MYSQL超大分页怎么处理 ?

1694414970210.png

1694415009115.png

候选人： 嗯，超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决

先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了

因为查询id的时候，走的覆盖索引，所以效率可以提升很多

面试官： 索引创建原则有哪些？

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

候选人： 嗯，这个情况有很多，不过都有一个大前提，就是表中的数据要超过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。

还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sql的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。

如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。

面试官： 什么情况下索引会失效 ?

  1. 违反最左前缀法则
  2. 范围查询右边的列，不能使用索引
  3. 不要在索引列上进行运算操作， 索引将失效
  4. 字符串不加单引号，造成索引失效。(类型转换)
  5. 以%开头的Like模糊查询，索引失效

1694415879928.png 1694415810410.png 1694415932052.png 1694415959360.png 1694415998094.png

候选人： 嗯，这个情况比较多，我说一些自己的经验，以前遇到过的

比如，索引在使用的时候没有遵循最左匹配法则，第二个是，模糊查询，如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。

我们之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效

所以，通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析

面试官： sql的优化的经验

  1. 表的设计优化
  2. 索引优化（参考优化创建原则和索引失效）
  3. SQL语句优化
  4. 主从复制、读写分离
  5. 分库分表（后面有专门章节介绍）

候选人： 嗯，这个在项目还是挺常见的，当然如果直说sql优化的话，我们会从这几方面考虑，比如建表的时候、使用索引、sql语句的编写、主从复制，读写分离，还有一个是如果量比较大的话，可以考虑分库分表

面试官： 创建表的时候，你们是如何优化的呢？

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

候选人： 这个我们主要参考的阿里出的那个开发手册《嵩山版》，就比如，在定义字段的时候需要结合字段的内容来选择合适的类型，如果是数值的话，像tinyint、int 、bigint这些类型，要根据实际情况选择。如果是字符串类型，也是结合存储的内容来选择char和varchar或者text类型

面试官： 那在使用索引的时候，是如何优化呢？

候选人： 【参考索引创建原则 + 索引失效 进行描述】

面试官： 你平时对sql语句做了哪些优化呢？

  • SQL语句优化
    ① SELECT语句务必指明字段名称（避免直接使用select * ）
    ② SQL语句要避免造成索引失效的写法
    ③ 尽量用union all代替union，union会多一次过滤（过滤重复数据），效率低
    ④ 避免在where子句中对字段进行表达式操作
    ⑤ Join优化 能用inner join 就不用left join、right join，如必须使用一定要以小表为驱动。内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序
    ⑥减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代
    ⑦or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好)
    ⑧应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
    ⑨应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0
    ⑩字段是字符串类型时，查询一定要给值加引号，否则索引失效

候选人： 嗯，这个也有很多，比如SELECT语句务必指明字段名称，不要直接使用select * ，还有就是要注意SQL语句避免造成索引失效的写法；如果是聚合查询，尽量用union all代替union ，union会多一次过滤，效率比较低；如果是表关联的话，尽量使用innerjoin ，不要使用用left join right join，如必须使用一定要以小表为驱动

面试官： 事务的特性是什么？可以详细说一下吗？

候选人： 嗯，这个比较清楚，ACID，分别指的是：原子性、一致性、隔离性、持久性；我举个例子：

A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要么都成功，要么都失败
在转账的过程中，数据要一致，A扣除了500，B必须增加500
在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰
在转账的过程中，持久性体现在事务提交后，要把数据持久化（可以说是落盘操作）

面试官：并发事务带来哪些问题？

脏读：读未提交
不可重复度：同一条记录，两次读取记录不一样
幻读：两次读取数据，数据条数不一样

1694422719020.png

候选人：

我们在项目开发中，多个事务并发进行是经常发生的，并发也是必然的，有可能导致一些问题

第一是脏读， 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

第二是不可重复读：比如在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

第三是幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

面试官：怎么解决这些问题呢？MySQL的默认隔离级别是？

1694422691650.png 候选人：解决方案是对事务进行隔离

MySQL支持四种隔离级别，分别有：

第一个是未提交读（read uncommitted）它解决不了刚才提出的所有问题，一般项目中也不用这个。
第二个是读已提交（read committed）它能解决脏读的问题的，但是解决不了不可重复读和幻读。
第三个是可重复读（repeatable read）它能解决脏读和不可重复读，但是解决不了幻读，这个也是mysql默认的隔离级别。
第四个是串行化（serializable）它可以解决刚才提出来的所有问题，但是由于让是事务串行执行的，性能比较低。
所以，我们一般使用的都是mysql默认的隔离级别:可重复读

面试官：undo log和redo log的区别

1694423966977.png

1694424054009.png

1694424079784.png

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

候选人：好的，其中redo log日志记录的是数据页的物理变化，服务宕机可用来同步数据，而undo log 不同，它主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据，比如我们删除一条数据的时候，就会在undo log日志文件中新增一条delete语句，如果发生回滚就执行逆操作；

redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

面试官：事务中的隔离性是如何保证的呢？(你解释一下MVCC)

候选人：事务的隔离性是由锁和mvcc实现的。

其中mvcc的意思是多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为了三个部分，第一个是隐藏字段，第二个是undo log日志，第三个是readView读视图

1694506887694.png 隐藏字段是指：在mysql中给每个表都设置了隐藏字段，有一个是trx_id(事务id)，记录每一次操作的事务id，是自增的；另一个字段是roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址

undo log主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表

  • undo log
    回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。 而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除。

  • undo log 版本链

    1694507646129.png

readView解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问那个版本的数据，不同的隔离级别快照读是不一样的，最终的访问的结果不一样。如果是rc隔离级别，每一次执行快照读时生成ReadView，如果是rr隔离级别仅在事务中第一次执行快照读时生成ReadView，后续复用。

1694507837123.png

ReadView中包含了四个核心字段： image.png

1694508068124.png

1694508760295.png

1694508809616.png

MVCC 可以为数据库解决什么问题？

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。同时还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题。

面试官 ：MySQL主从同步原理

候选人：MySQL主从复制的核心就是二进制日志(DDL（数据定义语言）语句和 DML（数据操纵语言）语句)，它的步骤是这样的：

1694425016545.png

第一：主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
第二：从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
第三：从库重做中继日志中的事件，将改变反映它自己的数据

面试官：你们项目用过MySQL的分库分表吗？

分库分表的时机：
1，前提，项目业务数据逐渐增多，或业务发展比较迅速（单表的数据量达1000W或20G以后）
2，优化已解决不了性能问题（主从读写分离、查询索引…）
3，IO瓶颈（磁盘IO、网

image.png

image.png

image.png

image.png

image.png

候选人：

嗯，因为我们都是微服务开发，每个微服务对应了一个数据库，是根据业务进行拆分的，这个其实就是垂直拆分。

面试官：那你之前使用过水平分库吗？

1694426989720.png 候选人：

嗯，这个是使用过的，我们当时的业务是(xxx)，一开始，我们也是单库，后来这个业务逐渐发展，业务量上来的很迅速，其中(xx)表已经存放了超过1000万的数据，我们做了很多优化也不好使，性能依然很慢，所以当时就使用了水平分库。

我们一开始先做了3台服务器对应了3个数据库，由于库多了，需要分片，我们当时采用的mycat来作为数据库的中间件。数据都是按照id（自增）取模的方式来存取的。

当然一开始的时候，那些旧数据，我们做了一些清洗的工作，我们也是按照id取模规则分别存储到了各个数据库中，好处就是可以让各个数据库分摊存储和读取的压力，解决了我们当时性能的问题

MySQL 如何做到高可用方案？

MySQL 高可用，意味着不能一台 MySQL 出了问题，就不能访问了。

  1. MySQL 高可用：分库分表，通过 MyCat 连接多个 MySQL
  2. MyCat 也得高可用：Haproxy，连接多个 MyCat
  3. Haproxy 也得高可用：通过 keepalived 辅助 Haproxy

MySQL数据库引擎有哪些

如何查看mysql提供的所有存储引擎

mysql> show engines;

image.png

存储引擎的特点

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表而不是基于库的，所以存储引擎也可以被称为表引擎。
默认存储引擎是InnoDB。

相关操作：

-- 查询建表语句
show create table account;
-- 建表时指定存储引擎
CREATE TABLE 表名(    
...
) ENGINE=INNODB;
-- 查看当前数据库支持的存储引擎
show engines;

InnoDB

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎。

特点：

  • DML 操作遵循 ACID 模型，支持事务
  • 行级锁，提高并发访问性能
  • 支持外键约束，保证数据的完整性和正确性

文件：

  • xxx.ibd: xxx代表表名，InnoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

参数：innodb_file_per_table，决定多张表共享一个表空间还是每张表对应一个表空间

知识点：

查看 Mysql 变量：
show variables like 'innodb_file_per_table';

从idb文件提取表结构数据：
（在cmd运行）
ibd2sdi xxx.ibd

InnoDB 逻辑存储结构：

image.png

MyISAM

MyISAM 是 MySQL 早期的默认存储引擎。

特点：

  • 不支持事务，不支持外键
  • 支持表锁，不支持行锁
  • 访问速度快

文件：

  • xxx.sdi: 存储表结构信息
  • xxx.MYD: 存储数据
  • xxx.MYI: 存储索引

Memory

Memory 引擎的表数据是存储在内存中的，受硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：

  • 存放在内存中，速度快
  • hash索引（默认）

文件：

  • xxx.sdi: 存储表结构信息

存储引擎特点

特点InnoDBMyISAMMemory
存储限制64TB有有
事务安全支持--
锁机制行锁表锁表锁
B+tree索引支持支持支持
Hash索引--支持
全文索引支持（5.6版本之后）支持-
空间使用高低N/A
内存使用高低中等
批量插入速度低高高
支持外键支持--

存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

  • InnoDB: 如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，则 InnoDB 是比较合适的选择
  • MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那这个存储引擎是非常合适的。
  • Memory: 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。Memory 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性

电商中的足迹和评论适合使用 MyISAM 引擎，缓存适合使用 Memory 引擎。

InnoDB与MyISAM的区别

InnoDB和MyISAM是两种常见的MySQL存储引擎，它们在功能和性能方面有一些重要的区别。以下是InnoDB和MyISAM之间的主要区别：

  1. 事务支持：

    • InnoDB支持事务，具有ACID（原子性、一致性、隔离性、持久性）特性，可以执行提交和回滚操作。这使得InnoDB适合于要求数据完整性和事务控制的应用。
    • MyISAM不支持事务，它以表级锁定的方式来处理并发请求，因此不提供像InnoDB那样的事务支持。这使得MyISAM适合于读密集型的应用，但不适用于要求数据完整性和复杂事务的应用。

  2. 锁定级别：

    • InnoDB使用行级锁定，允许多个事务同时访问同一表的不同行数据，提高了并发性。
    • MyISAM使用表级锁定，这意味着当一个事务对表进行写操作时，其他事务无法读取或写入同一表的数据，因此可能会导致锁定争用和性能下降。

  3. 外键支持：

    • InnoDB支持外键，可以定义和维护表之间的引用完整性，这对于建立复杂的数据关系非常有用。
    • MyISAM不支持外键，因此无法定义外键约束。

  4. 崩溃恢复：

    • InnoDB具有崩溃恢复功能，可以在数据库崩溃后自动恢复数据完整性。这使得InnoDB更可靠，并且适用于关键业务系统。
    • MyISAM的崩溃恢复能力相对较弱，可能需要手动修复表，因此在崩溃后的数据完整性维护方面不如InnoDB强大。

  5. 性能特性：

    • InnoDB通常在高并发写入负载下表现较好，因为它使用行级锁定，并且支持更复杂的事务处理。
    • MyISAM通常在读密集型应用中具有较好的性能，因为它的锁定粒度较大，不涉及事务控制的开销。

  6. 索引：

    • InnoDB的索引实现更加强大，支持聚簇索引（也称为主键索引）和次要索引。次要索引实际上是在底层存储引擎中的B-tree中实现的。
    • MyISAM的索引较简单，支持全文搜索索引，但不支持聚簇索引。MyISAM的主键索引与数据文件分开存储。

  7. 存储空间管理：

    • InnoDB的数据文件通常较大，需要更多的磁盘空间。但InnoDB支持数据和索引的压缩，可以减小磁盘占用。
    • MyISAM的数据文件通常较小，但不支持数据压缩。

综上所述，选择InnoDB还是MyISAM取决于应用程序的需求。如果需要事务支持、数据完整性、高并发写入、外键约束和复杂查询，那么InnoDB可能更适合。如果应用程序主要是读取操作，并且对写入性能要求不高，那么MyISAM可能是一个合适的选择。但需要根据具体的场景进行权衡和选择。另外，MySQL的新版本中还引入了其他存储引擎，如MEMORY、NDB Cluster、TokuDB等，每种引擎都有不同的优点和适用场景。

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

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id来支持。
生成全局 id 有下面这几种方式：

  1. UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
  2. 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
  3. 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系 统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
  4. Twitter的snowflake算法 ：Github 地址：github.com/twitter-arc…
  5. 美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋 势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据 库、Zookeeper等中间件。感觉还不错。美团技术团队的一篇文章：tech.meituan.com/2017/04/21/… 。

说说在 MySQL 中一条查询 SQL 是如何执行的？

比如下面这条SQL语句：

  1. 取得链接，使用使用到 MySQL 中的连接器。
select name from t_user where id=1

  1. 查询缓存，key 为 SQL 语句，value 为查询结果，如果查到就直接返回。不建议使用缓存，在 MySQL 8.0 版本已经将查询缓存删除，也就是说 MySQL 8.0 版本后不存在此功能。

  2. 分析器，分为词法分析和语法分析。此阶段只是做一些 SQL 解析，语法校验。所以一般语法错误在此阶段。

  3. 优化器，是在表里有多个索引的时候，决定使用哪个索引；或者一个语句中存在多表关联的时候（join），决定各个表的连接顺序。

  4. 执行器，通过分析器让 SQL 知道你要干啥，通过优化器知道该怎么做，于是开始执行语句。执行语句的时候还要判断是否具备此权限，没有权限就直接返回提示没有权限的错误；有权限则打开表，根据表的引擎定义，去使用这个引擎提供的接口，获取这个表的第一行，判断 id 是都等于 1。如果是，直接返回；如果不是继续调用引擎接口去下一行，重复相同的判断，直到取到这个表的最后一行，最后返回。

MySQL 中 varchar 与 char 的区别？varchar(30) 中的 30代表的涵义？

  1. varchar 与 char 的区别，char 是一种固定长度的类型，varchar 则是一种可变长度的类型。
  2. varchar(30) 中 30 的涵义最多存放 30 个字符。varchar(30) 和 (130) 存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 ORDER BY col 采用fifixed_length 计算 col 长度（memory 引擎也一样）。
  3. 对效率要求高用 char，对空间使用要求高用 varchar。

为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢？

对于 SELECT COUNT(*) FROM table 语句，在没有 WHERE 条件的情况下，InnoDB 比 MyISAM 可能会慢很多，尤其在大表的情况下。因为，InnoDB 是去实时统计结果，会全表扫描；而 MyISAM内部维持了一个计数器，预存了结果，所以直接返回即可。

什么时候不要使用索引？

  1. 经常增删改的列不要建立索引；
  2. 有大量重复的列不建立索引；
  3. 表记录太少不要建立索引。

请说说 MySQL 数据库的锁？

MySQL 中有共享锁和排它锁，也就是读锁和写锁。

  1. 共享锁：不堵塞，多个用户可以同一时刻读取同一个资源，相互之间没有影响。
  2. 排它锁：一个写操作阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。
  3. 表锁：系统开销最小，会锁定整张表，MyISAM 使用表锁。
  4. 行锁：容易出现死锁，发生冲突概率低，并发高，InnoDB 支持行锁（必须有索引才能实现，否则会自动锁全表，那么就不是行锁了）。

说说什么是锁升级？

  • MySQL 行锁只能加在索引上，如果操作不走索引，就会升级为表锁。因为 InnoDB 的行锁是加在索引上的，如果不走索引，自然就没法使用行锁了，原因是 InnoDB 是将 primary key index和相关的行数据共同放在 B+ 树的叶节点。InnoDB 一定会有一个 primary key，secondary index 查找的时候，也是通过找到对应的 primary，再找对应的数据行。

  • 当非唯一索引上记录数超过一定数量时，行锁也会升级为表锁。测试发现当非唯一索引相同的内容不少于整个表记录的二分之一时会升级为表锁。因为当非唯一索引相同的内容达到整个记录的二分之一时，索引需要的性能比全文检索还要大，查询语句优化时会选择不走索引，造成索引失效，行锁自然就会升级为表锁

说说悲观锁和乐观锁

悲观锁和乐观锁是用于处理并发访问数据库的两种不同策略，用于确保数据的一致性和并发性。它们的核心思想和应用场景有所不同：

悲观锁：

  1. 核心思想： 悲观锁假设在并发环境中会发生冲突，因此在访问数据之前会将其锁定，以确保其他事务不能同时访问相同的数据。
  2. 实现方式： 悲观锁通常通过数据库的锁定机制实现，例如在MySQL中可以使用SELECT...FOR UPDATE语句来锁定行或表。
  3. 应用场景： 悲观锁适用于高并发写入的场景，其中多个事务需要修改相同的数据时，使用悲观锁可以避免并发冲突，但可能导致性能下降，因为其他事务需要等待锁的释放。

乐观锁：

  1. 核心思想： 乐观锁假设在并发环境中冲突较少，因此在访问数据时不立即锁定，而是在更新数据之前检查是否发生了冲突。如果没有冲突，更新操作继续进行；如果发生冲突，需要回滚或重试操作。
  2. 实现方式： 乐观锁通常通过在数据表中引入一个版本号字段或时间戳字段来实现。每次更新前，检查版本号是否与预期值匹配，如果匹配，则进行更新，否则认为发生了冲突。
  3. 应用场景： 乐观锁适用于读多写少或写冲突较少的场景，它不会引入显式的锁定，因此性能可能更高。但需要处理冲突检测和重试逻辑。

总结：

  • 悲观锁在访问数据之前会进行锁定，适用于高并发写入场景，但可能导致性能下降和死锁问题。
  • 乐观锁不进行锁定，而是在更新时检查冲突，适用于读多写少或写冲突较少的场景，但需要处理冲突检测和重试逻辑。
  • 选择悲观锁还是乐观锁应根据具体的应用场景和性能需求来决定，有些情况下也可以结合使用两种锁策略。

主键与索引有什么区别？

主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键；
主键不允许为空值，唯一索引列允许空值；
一个表只能有一个主键，但是可以有多个唯一索引；
主键可以被其他表引用为外键，唯一索引列不可以；
主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本质区别

索引有什么有优缺点

索引是数据库中用于提高查询性能的重要工具，但它们也有自己的优点和缺点。以下是索引的主要优缺点：

优点：

  1. 提高查询性能： 索引允许数据库引擎更快地定位和检索数据，尤其是在大型数据表中。它们可以大幅提高SELECT查询的速度。
  2. 加速排序和分组： 索引可以加速ORDER BY、GROUP BY和DISTINCT等操作，因为它们允许数据库引擎在索引上执行这些操作，而无需全表扫描。
  3. 唯一性约束： 索引可以强制数据表中的数据列具有唯一性，这有助于维护数据的一致性和完整性。
  4. 支持外键关联： 索引通常用于支持外键关联，从而确保表之间的引用完整性。
  5. 加速连接操作： 在连接多个表时，索引可以加速JOIN操作，提高关联查询的性能。

缺点：

  1. 占用存储空间： 索引占用额外的存储空间。对于大型表和多个索引的情况，这可能会导致存储成本增加。
  2. 降低写入性能： 插入、更新和删除操作需要维护索引，这可能会导致写入性能下降，尤其是在频繁更新的表上。
  3. 维护成本： 索引需要定期维护，以确保它们保持高效。这包括索引重建和统计信息更新。
  4. 不适用于小表： 对于非常小的数据表，使用索引可能没有太多性能提升，并且可能浪费存储空间。
  5. 选择不当的索引可能降低性能： 如果选择不当，创建太多或不必要的索引可能会导致性能下降，因此需要谨慎选择索引。

总的来说，索引是数据库性能优化的关键工具，但在使用时需要权衡其优点和缺点。正确选择和设计索引是数据库管理和开发中的重要任务，需要根据具体的数据表和查询需求来做出决策。最佳实践是根据查询模式和数据写入模式来创建适当数量的索引，以平衡读取和写入性能。

avatar
文章
阅读
粉丝