MySQL常见面试题

标签：count 面试题常见查询索引 InnoDB MySQL 数据主键

使⽤索引⼀定可以提升效率吗？

索引就是排好序的,帮助我们进⾏快速查找的数据结构.简单来讲，索引就是⼀种将数据库中的记录按照特殊形式存储的数据结

构。通过索引，能够显著地提⾼数据查询的效率，从⽽提升服务器的性能。

索引的优势与劣势

优点

提⾼数据检索的效率,降低数据库的IO成本

通过索引列对数据进⾏排序,降低数据排序的成本,降低了CPU的消耗

缺点

创建索引和维护索引要耗费时间，这种时间随着数据量的增加⽽增加

除了数据表占⽤数据空间之外，每⼀个索引还要占⽤⼀定的物理空间

当对表中的数据进⾏增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

创建索引的原则

在经常需要搜索的列上创建索引，可以加快搜索的速度；在作为主键的列上创建索引，强制该列的唯⼀性和组织表中数据的排列结构；

在经常⽤在连接的列上，这些列主要是⼀些外键，可以加快连接的速度；

在经常需要根据范围进⾏搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利⽤索引的排序，加快排序查询时间；

在经常使⽤在WHERE⼦句中的列上⾯创建索引，加快条件的判断速度

说⼀下聚簇索引与⾮聚簇索引？

聚集索引与⾮聚集索引的区别是：叶节点是否存放⼀整⾏记录

索引有哪⼏种类型？

普通索引

唯⼀索引:与普通索引类似，不同的就是：索引字段的值必须唯一，但允许有空值。

主键索引：它是⼀种特殊的唯⼀索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有⼀个主键。

复合索引：用户可以在多个列上建⽴索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单⼀索引，相⽐多个单⼀索引复合索引所需的开销更⼩。

复合索引使⽤注意事项：
何时使⽤复合索引，要根据where条件建索引，注意不要过多使⽤索引，过多使⽤会对更新操作效率有很⼤影响。
如果表已经建⽴了(col1，col2)，就没有必要再单独建⽴（col1）；
如果现在有(col1)索引，如果查询需要col1和col2条件，可以建⽴(col1,col2)复合索引，对于查询有⼀定提⾼。

全⽂索引：查询操作在数据量⽐较少时，可以使⽤like模糊查询，但是对于⼤量的⽂本数据检索，效率很低。如果使⽤全⽂索引，查询速度会⽐like快很多倍。

MySQL从 5.6开始MyISAM和InnoDB存储引擎均⽀持。

全⽂索引⽅式有⾃然语⾔检索 IN NATURAL LANGUAGE MODE和布尔检索IN BOOLEAN MODE两种

介绍⼀下最佳左前缀法则

最左前缀法则：如果创建的是联合索引，就要遵循该法则。使用索引时，where后面的条件需要从索引的最左前列开始使用，并且不能跳过索引中的列使用。

比方说我们创建了一张表，构建了一个联合索引（name,age,level）,但是查询的时候顺序为select age,name,level from table ，它也是不受影响的，和select select name,age,level from table;效果是一样的，原因是因为MySQL底层对这种情况做了优化。

什么是索引下推？

解释：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

举个例子：比方说我有一张表user(id,name,age),其中id是主键索引，(name,age)是联合索引。现在我想查询姓张的，年龄大于18岁的，

select * from table where name like '%张' and age > 18;

在MySQL5.6之前，它会先查询出姓张的人，然后在根据主键，进行回表。在MySQL5.6以及之后，它会先查询出姓张的人，紧接着会根据age字段来判断年龄是否大于18岁，如果不是则舍弃掉，然后再进行回表。引入索引下推的好处就是为了减少回表的次数。

什么是⾃适应哈希索引？

InnoDB存储引擎会监控对表上索引页（二级索引，非主键的索引，比如在name列创建的索引）的查询，自动建立合适的Hash索引，提升数据页的访问效率。

特点：

哈希索引，查询消耗O(1)，非常高的
降低对二级索引树的频繁访问
自适应（不用开发者自己去维护，由InnoDB引擎去维护）

缺点：

Hash自适应索引会占用Buffer Pool的内存空间
只适合与等值查询
- select * from table where index_col = "郭德纲";
- 范围查询不可以

innodb存储引擎会监控对表上二级索引的查找，如果发现某个二级索引被频繁的访问（最近连续三次被访问的数据），innodb存储引擎就会给该索引字段简历自适应的hash索引。可以通过自适应hash索引能够直接找到想要的数据页，就不需要再通过主键进行回表操作了，提升了效率。

为什么LIKE以%开头索引会失效？

解决%出现在左边索引失效的⽅法，使⽤覆盖索引。

EXPLAIN SELECT user_name FROM users WHERE user_name LIKE '%jack%'; -- 也就是说查询的时候带上索引列就可以了。

原因：由于B+树的索引顺序，是按照⾸字⺟的⼤⼩进⾏排序。除了开始位置其他任何位置的字母都是没有顺序的。所以索引会失效。

⾃增还是UUID？数据库主键的类型该如何选择？

auto_increment的优点：

字段⻓度较uuid⼩很多，可以是bigint甚⾄是int类型，这对检索的性能
会有所影响。
在写的⽅⾯，因为是⾃增的，所以主键是趋势⾃增的，也就是说新增的
数据永远在后⾯，这点对于性能有很⼤的提升。
数据库⾃动编号，速度快，⽽且是增量增⻓，按顺序存放，对于检索⾮
常有利。
数字型，占⽤空间⼩，易排序，在程序中传递也⽅便。
auto_increment的缺点：
由于是⾃增，很容易通过⽹络爬⾍知晓当前系统的业务量。
⾼并发的情况下，竞争⾃增锁会降低数据库的吞吐能⼒。
数据迁移或分库分表场景下，⾃增⽅式不再适⽤。

UUID的优点：
不会冲突。进⾏数据拆分、合并存储的时候，能保证主键全局的唯⼀性
可以在应⽤层⽣成，提⾼数据库吞吐能⼒
UUID的缺点：
影响插⼊速度，并且造成硬盘使⽤率低。与⾃增相⽐，最⼤的缺陷就是
随机io，下⾯我们会去具体解释
字符串类型相⽐整数类型肯定更消耗空间，⽽且会⽐整数类型操作慢。

uuid是这个样子的：a3b55936-ba5a-4261-8f14-ce3fae4707bf

最好使用雪花算法。

InnoDB与MyISAM的区别？

innodb支持事务，MyISAM不支持事务，可以为经常查询表设置存储引擎为MyISAM。

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

B-Tree是⼀种平衡的多路查找树,B树允许⼀个节点存放多个数据. 这样可以在尽可能减少树的深度的同时,存放更多的数据(把瘦⾼的树变的矮胖)。

其中P1 P2 P3表示指向下一个节点的指针。

17 35 8 等等表示对应的整条数据记录。

B+树就是每个叶子结点也进行了连接。数据都存放在叶子节点中，非叶子节点不存放数据。

⼀个B+树中⼤概能存放多少条索引记录？

42928704

explain ⽤过吗，有哪些主要字段？

主要用来查询sql的执行计划。

参考我的博客：https://www.cnblogs.com/dongyaotou/p/14580054.html

如何进⾏分⻚查询优化？

方案一：通过索引进行优化。

假设ID是连续递增的,我们根据查询的⻚数和查询的记录数可以算出查询的id的范围，然后配合 limit使⽤

EXPLAIN SELECT * FROM user WHERE id >= 100001 LIMIT 100;

方案二：利用子查询进行优化

-- ⾸先定位偏移位置的id
SELECT id FROM user_contacts LIMIT 100000,1;
-- 根据获取到的id值向后查询.
EXPLAIN SELECT * FROM user_contacts WHERE id >=
(SELECT id FROM user_contacts LIMIT 100000,1) LIMIT 100;

原因：使⽤了id做主键⽐较(id>=)，并且⼦查询使⽤了覆盖索引进⾏优化

如何做慢查询优化？

默认情况下slow_query_log的值为OFF，表示慢查询⽇志是禁⽤的。我们可以开启慢查询。

然后就是参考自己的博客：https://www.cnblogs.com/dongyaotou/p/14580054.html

Hash索引有哪些优缺点？

MySQL中索引的常⽤数据结构有两种: ⼀种是B+Tree,另⼀种则是Hash.

优点：适合做等值查询。

缺点：哈希索引只包含哈希值和⾏指针，⽽不存储字段值，所以不能使⽤索引中的值来避免读取⾏。还有就是没有办法进行排序。

说⼀下InnoDB内存相关的参数优化？

说白了就是对Buffer Pool的参数进行优化。

缓冲池内存⼤⼩配置：show variables like '%innodb_buffer_pool_size%';

Page管理相关参数： show variables like '%innodb_page_size%';

什么是写失效？

InnoDB的⻚和操作系统的⻚⼤⼩不⼀致，InnoDB⻚⼤⼩⼀般为16K，操作系统⻚⼤⼩为4K，InnoDB的⻚写⼊到磁盘时，⼀个⻚需要分4次写。

如果存储引擎正在写⼊⻚的数据到磁盘时发⽣了宕机，可能出现⻚只写了⼀部分的情况，⽐如只写了4K，就宕机了，这种情况叫做部分写失效（partial page write），可能会导致数据丢失。

mysql借助双写缓存区将这个写失效的问题解决了。

什么是行溢出？

InnoDB存储引擎⽀持四种⾏格式：Redundant、Compact、Dynamic 和Compressed

MySQL中是以⻚为基本单位,进⾏磁盘与内存之间的数据交互的,我们知道⼀个⻚的⼤⼩是16KB,16KB = 16384字节.⽽⼀个varchar(m) 类型列最多可以存储65532个字节,⼀些⼤的数据类型⽐如TEXT可以存储更多. 如果⼀个表中存在这样的⼤字段,那么⼀个⻚就⽆法存储⼀条完整的记录.这时就会发⽣⾏溢出,多出的数据就会存储在另外的溢出⻚中.
总结: 如果某些字段信息过⻓，⽆法存储在B树节点中，这时候会被单独分配空间，此时被称为溢出⻚，该字段被称为⻚外列。

如何进⾏JOIN优化？

join用于多表联合查询。包括左连接、右链接、内连接查询。

驱动表：第一个被处理的表叫驱动表。在对最终的结果集没有影响的前提下,优先选择结果集最⼩的那张表作为驱动表。

总结：

永远⽤⼩结果集驱动⼤结果集(其本质就是减少外层循环的数据数量)
为匹配的条件增加索引(减少内层表的循环匹配次数)
增⼤join buffer size的⼤⼩（⼀次缓存的数据越多，那么内层包的扫表次数就越少）
减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

面试官可能会问你：为什么要用小表驱动大表？

我们通过sql 的执行计划的extra列可以看到，join查询底层使用到了nlp算法和join buffer ,问题就是出在了join buffer上面，小表可以一次性加载到join buffer中，然后逐行与大表匹配，而将大表作为驱动表，可能一次性无法将大表装载到join buffer中，需要分批次装入与大表进行匹配，这就浪费时间了。

索引哪些情况下会失效？

查询条件包含 or，会导致索引失效。
隐式类型转换，会导致索引失效，例如 age 字段类型是 int，我们where age = “1”，这样就会触发隐式类型转换
like 通配符会导致索引失效，注意:”ABC%” 不会失效，会⾛ range 索引，”% ABC” 索引会失效
联合索引，查询时的条件列不是联合索引中的第⼀个列，索引失效。
对索引字段进⾏函数运算。
对索引列运算（如，+、-、*、/），索引失效。
索引字段上使⽤（!= 或者 < >，not in）时，会导致索引失效。
索引字段上使⽤ is null， is not null，可能导致索引失效。
join 的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算
mysql 估计使⽤全表扫描要⽐使⽤索引快，则不使⽤索引。

什么是覆盖索引？

说白了就是要查询的字段都在索引中包含了。

介绍⼀下MySQL中事务的特性？

ACID:原⼦性（Atomicity）、⼀致性（Consistency）、隔离性（Isolation）和持久性（Durability）

说一下数据库锁的种类

MySQL数据库由于其⾃身架构的特点,存在多种数据存储引擎, MySQL中不同的存储引擎⽀持不同的锁机制。
MyISAM和MEMORY存储引擎采⽤的表级锁，InnoDB存储引擎既⽀持⾏级锁，也⽀持表级锁，默认情况下采⽤⾏级锁。
BDB采⽤的是⻚⾯锁，也⽀持表级锁

按照数据操作的类型分为

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

写锁（排他锁）：当前写操作没有完成之前，他会阻断其他写锁和读锁。

按照数据操作的粒度分
表级锁：开销⼩，加锁快；不会出现死锁；锁定粒度⼤，发⽣锁冲突的概率最⾼，并发度最低。
⾏级锁：开销⼤，加锁慢；会出现死锁；锁定粒度最⼩，发⽣锁冲突的概率最低，并发度也最⾼。
⻚⾯锁（页锁，锁定的是一组临近的记录）：开销和加锁时间界于表锁和⾏锁之间；会出现死锁；锁定粒度界于表锁和⾏锁之间，并发度⼀般

按照操作性能可分为乐观锁和悲观锁
乐观锁：⼀般的实现⽅式是对记录数据版本进⾏⽐对，在数据更新提交的时候才会进⾏冲突检测，如果发现冲突了，则提示错误信息。
悲观锁：在对⼀条数据修改的时候，为了避免同时被其他⼈修改，在修改数据之前先锁定。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

事务隔离级别

读未提交（RU）、读已提交(RC)、可重复读(RR)、可串行化。

InnoDB 的⾏锁是怎么实现的？

InnoDB⾏锁是通过对索引数据⻚上的记录加锁实现的，主要实现算法有 3种：Record Lock、Gap Lock 和 Next-key Lock。
RecordLock锁：锁定单个⾏记录的锁。（记录锁，RC、RR隔离级别都⽀持）
GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别⽀持）
Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别⽀持）

注意： InnoDB这种⾏锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使⽤⾏级锁，否则，InnoDB将使⽤表锁.

在RR隔离级别，InnoDB对于记录加锁⾏为都是先采⽤Next-Key Lock，但是当SQL操作含有唯⼀索引时，Innodb会对Next-Key Lock进⾏优化，降级为RecordLock，仅锁住索引本身⽽⾮范围。

1.主键加锁

update table set name = 'yaya' where id = 10 -- id是主键

加锁⾏为：仅在id=10的主键索引记录上加X锁。

2.唯⼀键加锁

update table set name = 'yaya' where idno = '130727xxxxxxx' -- idno是身份证号,idno字段上添加唯一索引。

加锁⾏为：现在唯⼀索引idno上加X锁，然后在id=10的主键索引记录上加X锁.

3.⾮唯⼀键加锁（说白了就是普通索引）

update table set name = 'lisi' where name = 'zhangsan' -- name 是姓名，name字段上添加普通索引。

加锁⾏为：对满⾜name = 'zhangsan'条件的记录和主键id分别加X锁，然后在对应的记录前后加间隙锁。

⽆索引加锁

加锁⾏为：表⾥所有⾏和间隙都会加X锁。（当没有索引时，会导致全表锁定，因为InnoDB引擎锁机制是基于索引实现的记录锁定）

并发事务会产⽣哪些问题

“更新丢失”、”脏读”、“不可重复读”和“幻读”等并发事务问题，其实都是数据库⼀致性问题，为了解决这些问题，MySQL数据库是通过事务隔离级别来解决的，数据库系统提供了以下 4 种事务隔离级别供⽤户选择。

说⼀下MVCC内部细节

MVCC（Multi Version Concurrency Control）被称为多版本并发控制，是指在数据库中为了实现⾼并发的数据访问，对数据进⾏多版本处理，并通过事务的可⻅性来保证事务能看到⾃⼰应该看到的数据版本。

总结⼀下
并发环境下，写-写操作有加锁解决⽅案，但为了提⾼性能，InnoDB存储引擎提供MVCC，⽬的是为了解决读-写，写-读操作下不加锁仍能安全进⾏。MVCC的过程，本质就是访问版本链，并判断哪个版本可⻅的过程。该判断算法是通过版本上的trx_id与快照ReadView的若⼲个信息进⾏对⽐。快照⽣成的时机因隔离级别不同，读已提交隔离级别下，每⼀次读取前都会⽣成⼀个快照ReadView；⽽可重复读则仅在⼀个事务中，第⼀次读取前⽣成⼀个快照。

说下mysql死锁产生原因和处理办法

表级死锁产生原因:

⽤户A访问表A（锁住了表A），然后⼜访问表B；另⼀个⽤户B访问表B（锁住了表B），然后企图访问表A；这时⽤户A由于⽤户B已经锁住表B，它必须等待⽤户B释放表B才能继续，同样⽤户B要等⽤户A释放表A才能继续，这就死锁就产⽣了。

出现这种情况一般是因为代码写的有问题，只能改代码了。

行级死锁

如果在事务中执⾏了⼀条没有索引条件的查询，引发全表扫描，把⾏级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执⾏后，就很容易产⽣死锁和阻塞，最终应⽤系统会越来越慢，发⽣阻塞或死锁。

解决⽅案：

SQL语句中不要使⽤太复杂的关联多表的查询；使⽤explain“执⾏计划"对SQL语句进⾏分析，对于有全表扫描和全表锁定的SQL语句，建⽴相应的索引进⾏优化

介绍一下mysql的体系架构

undo log、redo log、 binlog的作⽤是什么？

undo log是⼀种⽤于撤销回退的⽇志。在事务执行变更操作之前需要先将相反的操作写入undo log，通过它可以让事务回滚操作，undo log也是实现多版本控制（MVCC）的基础。

redo log：被称作重做⽇志。作为服务器异常宕机后事务数据自动恢复使用。

bin log: 主从复制日志。作为主从复制和数据恢复使用。

MySQL的binlog有⼏种⽇志格式？分别有什么区别？

ROW：⽇志中会记录每⼀⾏数据被修改的情况，但是会产生大量的日志。

STATMENT：记录每⼀条修改数据的SQL语句。不会产生大量日志，但是对于now（）函数这种，可能会产生数据不一致的情况。

MIXED:两者的结合。

count(列名)、count(1)和count(*)有什么区别?

count(*) 包括了所有的列,在统计时不会忽略列值为null的数据。

count(1) ⽤1表示代码⾏,在统计时,不会忽略列值为null的数据。

count(列名)在统计时,会忽略列值为空的数据,就是说某个字段的值为null时不统计（给面试官说一下，在mysql新版本中这个问题不存在了，也就是说null值的行也会统计上）。

执⾏效率上：

InnoDB引擎：count（字段) < count(1) = count(*)

InnoDB通过遍历最⼩的可⽤⼆级索引来处理select count(*) 语句，除⾮索引或优化器提示指示优化器使⽤不同的索引。如果⼆级索引

不存在，则通过扫描聚集索引来处理。InnoDB已同样的⽅式处理count(1)和count(*)。

MyISAM引擎：count（字段) < count(1) <= count(*)。

MyISAM存储了数据的准确⾏数，使⽤ count(*)会直接读取该⾏数，只有当第⼀列定义为NOT NULL时，count（1），才会执⾏该

操作，所以优先选择 count(*)。

count(列名) 会遍历整个表，但不同的是，它会先获取列，然后判断是否为空，然后累加，因此count(列名)性能不如前两者。

注意：count(*)，这是SQL92 定义的标准统计⾏数的语法，跟数据库⽆关，与NULL也⽆关。⽽count(列名) 是统计列值数量，不计NULL，相同列值算⼀个。

说说 MySQL 的主从复制？

主从复制的原理其实就是把主服务器上的 bin log日志复制到从服务器上执⾏⼀遍，这样从服务器上的数据就和主服务器上的数据相同了。前提是主库要开启binlog日志。

主库db的更新事件(update、insert、delete)被写到binlog
主库创建⼀个binlog dump thread，把binlog的内容发送到从库

3.从库启动之后，创建⼀个I/O线程，读取主库传过来的binlog内容并写⼊到relay log

4.从库启动之后，创建⼀个SQL线程，从relay log⾥⾯读取内容，执⾏读取到的更新事件，将更新内容写⼊到slave的db.

说⼀下 MySQL 执⾏⼀条查询语句的内部执⾏过程？

Mysql内部⽀持缓存查询吗？

mysql5.7⽀持内部缓存，8.0之后已废弃

SQL必须完全⼀致才会导致cache命中,原因：比方说你写了两个sql,一个是select * from girls, 一个是SELECT * from girls；这两个sql经过mysql的hash计算，得到的哈希值不一样，它认为不是同一条sql，就会将缓存给清空了。再者缓存是占用空间的，缓存的操作也是费时间的。想了想用处不大，后来的版本中就废弃了。

标签：count,面试题,常见,查询,索引,InnoDB,MySQL,数据,主键
From： https://www.cnblogs.com/dongyaotou/p/18408577