MySQL数据库常见面试题系列一

最新推荐文章于 2024-03-14 18:02:52 发布

置顶心南南心

最新推荐文章于 2024-03-14 18:02:52 发布

阅读量660

点赞数 1

分类专栏： MySQL数据库系列文章标签：数据库 mysql mysql优化

本文链接：https://blog.csdn.net/nan1996jiang/article/details/119356064

版权

MySQL数据库系列专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1、MySQL 索引使用有哪些注意事项呢？

可以分为三种情况来看：索引在哪种情况会失效、索引不适合哪些场景和索引规则

索引在哪种情况会失效

在索引列上使用mysql得函数或者做运算会导致索引失效
sql条件数据类型转换会导致索引失效
查询条件包含or或者索引字段使用is null和is not null，可能导致索引失效
联合索引，查询条件列不是联合索引中得第一个列，索引失效
前导模糊查询不能使用索引例如：select id from t where name like ‘%a’;
连接查询得时候表之间得字符集编码不一致会导致索引失效
查询得时候mysql优化器觉得全表扫描比走索引快，这时针对这条SQL查询条件索引失效

索引不适合哪些场景

更新比较频繁得字段不适合加索引
数据量少或者数据要进行函数计算不适合加索引

索引规则

Using Index（索引覆盖）
Using Where（索引回表）
Using Index Condition（索引下推）
Using Flesort（表示mysql需额外排序操作，不能通过索引顺序达到排序效果）

2、MySQL 遇到过死锁问题吗，你是如何解决的？

通过多终端模拟并发事务，复现死锁
通过show engine innodb status；可以查看事务与锁的信息
通过explain查看执行计划

3、日常工作中你是怎么优化SQL的？

写SQL的时候尽量不使用select *，而是指定需要查询的字段，如果查的字段都有索引就会索引覆盖
查SQL的时候已经预知该记录只会返回一条结果，那么就是用select id from t where name = ‘nj’ limit 1，就不会造成查到之后继续遍历查找
查询的时候减少or的使用改为union all

select * from t where id = 0 or id = 10
改为如下：
select * from t WHERE id = 0
UNION ALL
select * from t where id = 10

优化limit分页，当偏移量特别大的时候，查询效率就变得低

select id,c,d from t limit 10000,10
返回上次查询的最大记录(偏移量)
select id,c,d from t where id > 10000 limit 10

优化like语句，减少前导模糊查询写法
避免在索引上使用MySQL内置函数以级计算，可以把函数写在结果那边就可以走索引
内连接，左外连接和右外连接使用

inner join:内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集
left join:在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。
right join:在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

避免在where子语句中使用!=或者<>操作符，否则MySQL引擎会放弃索引直接全表扫描
使用联合索引时，注意索引列的顺序，遵循最左匹配原则
对经常要进行where条件的参数以级order by字段加索引，尽量避免全表扫描
使用覆盖索引，减少会聚簇索引表查所有数据
如果要进行删除或者修改操作的时候数据量大，建议分批执行，不要一次性操作减少大事务发生
表字段设计的时候尽量都赋予默认值减少为null
减少in的操作，使用exits查询
索引不宜建立在有大量数据重复的字段以级频繁修改的字段
避免向客户端返回大量数据，可以根据分页条件查询相应数据
提高group by的效率，尽量在where 后面过滤掉所有条件select id,c,d,name from t where id > 0 and name like ‘1%’ GROUP BY NAME

4、MySQL的引擎InnoDB与MyISAM的区别

MySQL引擎

支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。有缓冲池，用于缓冲数据和索引，写缓冲（change buffer），日志缓冲池用于出错恢复数据。innodb事务日志包括redo log和undo log。
redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎么样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
undo log用来回滚记录到某个版本，undo log一般是逻辑日志，根据每行记录进行记录。

MyISAM引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务，不支持外键。
插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用

5、数据库索引的原理，为什么要用 B+树，为什么不用二叉树？

对于MyISAM存储引擎来说，B+树的数据结构中存储的内容实际是实际数据的地址值，也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引成为非聚簇索引。
InnoDB引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际数据，这种索引被称为聚簇索引。
Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
并且和MyISAM不同，InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率

6、聚集索引与非聚集索引的区别

聚簇索引：

表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。
聚簇索引的叶子节点就是数据节点。
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

非聚簇索引：

表数据存储顺序与索引顺序无关。对于非聚集索引，叶子结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。
非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

如何选择合适的分布式主键方案呢？

数据库自增序列
UUID
Redis生成ID
Twitter的snowflake算法（雪花算法）
利用zookeeper生成唯一ID

7、事务的隔离级别有哪些？MySQL的默认隔离级别是什么？

读未提交（read unCommitted）
读已提交（read committed）
可重复读（repeatable read）
串行化（serializable）
Mysql默认的事务隔离级别是可重复读(repeatable read)
Oracle默认的事务隔离级别是读已提交(read committed)

8、什么是幻读、脏读、不可重复读

脏读：事务A读取了事务B未提交的数据。
不可重复读：同一事务中，完全相同的查询同一条结果集，执行两次查询结果不一致。因为别的事务可能更新了被查询的结果集。
幻读：事务A查询了一个范围的结果集，另一个并发事务B往这个范围中插入/删除了数据，并提交了，然后事务A再次查询的相同的范围，两次返回的结果集不同就是幻读

9、高并发情况下如何做到安全的修改同一行数据？

保证一个线程在修改时其他线程无法更新这行记录，一般有悲观锁和乐观锁两种方案。

悲观锁

select id,c,d,name from t where name = 'nj' for update

使用for update会将表t中符合的数据都锁定起来，事务提交之后才会释放行锁。

乐观锁

一般是通过为数据库表添加一个version字段来实现读取数据时，将此版本号一同读出。之后更新时对版本号加1，在更新过程中会对版本号进行比较，如果是一致的没有发生改变，则会执行本次操作，如果版本号不一致则会更新失败。

10、select … for update会锁表还是锁行呢(id为主键)

普通select id,c,d,name from t是不会加锁的
select id,c,d,name from t where id = 1 for update是会加行锁
select id,c,d,name from t where name like '%张三%'是会加表锁

11、MySQL事务四大特性以级实现原理

MySQL事务的四大特性：原子性、一致性、隔离性、持久性。

（Atomicity）原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
（Consistency）一致性：事务在执行前后，必须使所有的数据库表中数据保持一致。
（Isolation）隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
（Durability）持久性：事务完成操作并提交之后，事务对该数据做的更改，都持久保存在数据库中。

事务ACID实现的思想

原子性：使用undo log实现，如果执行事务出错或者自己执行rollback，系统会通过undo log日志返回事务开始的状态。
持久性：使用redo log来实现，只要redo log日志持久化了，当系统崩溃可以通过redo log将数据恢复。
隔离性：通过锁以级MVCC，使事务相互隔离开。
一致性：通过回滚、恢复以级并发情况下的隔离性从而实现一致性。

12、如何写SQL能够有效的使用到符合索引

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  'name' varchar(64) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
alter table t add index `idx_composition` (`c`,`d`,'name');

select id,c,d,name from t where c = 1 and d = 2 and name = 'njtest';
--需要关注查询Sql条件的顺序，确保最左匹配原则有效，同时可以删除不必要的冗余索引。

复合索引也叫做组合索引，用户可以在多个列上建立索引。当创建(c,d,name)的组合索引就相当于创建了三个索引：©、(c,d)、(c,d,name)，这就是最左匹配原则。

13、MySQL中in和exists的区别

select * from A where id in (select aId from B);
--先查询表B,select aId from B
--再查表A，select * from A where A.ID = B.aId

select * from A where exists(select aId from B where A.id = B.aId);
-- exists查询就是先执行主查询获取数据后，在放到子查询中做条件验证，根据验证结果(true/false)，来决定主查询数据是否保留

MySQL优化原则，小表驱动大表，小的数据集驱动大的数据集。也就是B的数据量小于A适合于in，反之适合用exists。

14、数据库自增会遇到什么问题？

使用自增主键对数据库分库分表，可能出现诸如主键重复问题。
自增主键会产生表锁，从而引发问题
自增主键会用完

15、数据库中间件

Mycat 是基于 Proxy，它复写了 MySQL 协议，将 Mycat Server 伪装成一个 MySQL 数据库，而 Sharding-JDBC 是基于 JDBC 接口的扩展，是以 jar 包的形式提供轻量级服务的。
sharding-jdbc目前是基于jdbc驱动，无需额外的proxy，因此也无需关注proxy本身的高可用。

16、大表查询优化

优化SQL以级索引
主从复制，可以读写分离，减少主库操作压力

17、数据库连接池

在内部对象池当中，维护一定数量的数据库连接，并对外暴露数据库连接的获取和返回方法。
程序和数据库建立连接过程
通过TCP协议三次握手建立连接；发送数据库账户密码验证身份；完成后可提交SQL语句到数据库执行；关闭连接，TCP四次挥手。
资源重复利用，减少创建资源以级收回消耗；
统一管理，避免数据库连接泄露

18、MySQL索引策略

索引覆盖，减少索引回表少一次查询
索引下推
最左前缀原则

19、一条SQL执行时间过长如何优化

首先看全表的数据量有多大
查看SQL条件是否都有索引，以级尤特殊操作导致查全表
使用explain 分析SQL语句，查看执行计划，或者强制走索引

20、blob和text的区别

blob用于存储二进制数据，而text用于存储大字符串
blob值为二进制字符串（字节字符串）,它们没有字符集，并且排序和比较基于列值中的字节的数值。
text值被视为非二进制字符串（字符字符串）。它们有一个字符集，并根据字符集的排序规则对值进行排序和比较。

21、money有关字段在MySQL当中如何存储

money在数据库中常用Decimal和Numric类型表示；
salary Decimal(9,2)，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。存储在salary列中的值的范围是从-9999999.99到9999999.99。
Decimal和Numric值作为字符串存储，而不是作为二进制浮点数，以便保存那些值的小数精度。

22、MySQL的InnoDB默认B+树索引和hash索引区别

B+树可以进行范围查询，hash索引不支持
B+树支持联合索引最左匹配原则，hash索引不支持
B+树支持order by排序，hash索引不支持。
hash索引在等值查询上比B+树效率更高，因为hash索引就相当于key-value结果所以等值查询效率高
InnoDB默认的B+树索引查询的时候，一直查某些数据会自适应生成hash

23、mysql 的内连接、左连接、右连接有什么区别

inner join内连接，两张表查询的时候只保留两张表当中完全匹配的结果集；
left join左外连接，两张表查询的时候会返回左表所有的行，即使右表当中没有与之对应的数据默认为Null；
right join右外连接，两张表查询的时候会返回右表所有的行，即使左表当中没有与之对应的数据默认为Null；
使用join连接的时候：
1、如果是Index Nested-Loop Join算法，应该选择小表做驱动表；
2、如果是Block Nested-Loop Join算法：
在join_buffer_size足够大的时候是一样的；
在join_buffer_size不够大的时候，选择小表做驱动表

24、MySQL基础架构图来源于极客时间MySQL45讲

MySQL基础架构图

连接器：负责跟客户端建立连接、获取权限、维持和管理连接。
分析器：对SQL进行语法语义分析；
优化器：对SQL使用索引优先级判断；
执行器：执行SQL返回结果。

25、MySQL有关权限的表有几个呢？

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里。这些权限表分别columns_priv，db，host，tablep_riv和user。

columns_priv：记录数据列级的操作权限；
db：记录各个账户在各个数据库上的操作权限；
host：配合db权限表对给定主机上数据库操作权限更细致的控制，这个权限表不受GRANT和REVOKE语句的影响；
tablep_riv：记录数据表级的操作权限；
user：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。

26、MySQL的binlog的三种格式，有什么区别

statement：每一条修改数据的SQL都会记录在binlog里面。不需要记录每一行的变化，减少了binlog日志量，减少IO，提供性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
row：不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
mixed：一种折中方案，普通操作使用statement记录，当无法使用statement的时候使用row。

27、索引的优缺点

唯一索引可以保证数据库表种每一行数据的唯一性
建立好的索引可以加快查询速度，减少查询时间
创建索引和维护索引需要时间
索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
表中的数据进行增删改的时候也需要对索引进行维护

28、索引类型

主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。
组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并。
全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
覆盖索引：查询列要被所建的索引覆盖，不必读取数据行。

29、创建索引原则

频繁作为查询条件的字段才去创建索引，频繁更新的字段不适合创建索引。
索引列不能参与计算，不能有函数操作。
在order by或者group by子句中，创建索引需要注意顺序。
优先考虑扩展索引，而不是新建索引，避免不必要的索引。
区分度低的数据列不适合做索引列(如性别）。

30、创建索引方式

创建表的时候直接创建索引：

set global innodb_thread_concurrency=3;
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  'name' varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name`(`name`) USING BTREE
) ENGINE=InnoDB;

使用alter table命令为表添加索引

ALTER TABLE t ADD INDEX idx2_name ('name');

使用creat index命令创建索引：

CREATE INDEX idx3_name ON t ('name');

你知道的越多你不知道的越多

心南南心

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库常见面试题系列一

1、MySQL 索引使用有哪些注意事项呢？可以分为三种情况来看：索引在哪种情况会失效、索引不适合哪些场景和索引规则索引在哪种情况会失效在索引列上使用mysql得函数或者做运算会导致索引失效sql条件数据类型转换会导致索引失效：select id from查询条件包含or或者索引字段使用is null和is not null，可能导致索引失效联合索引，查询条件列不是联合索引中得第一个列，索引失效前导模糊查询不能使用索引例如：select id from t where name like ‘
复制链接

扫一扫