MySQl 常见面试题

一、SQL语句

问题、SQL语句有哪些类型，每种类型有哪些常用关键字？

DDL：数据定义语句。常用关键字有create、drop、alter。

DML：数据操作语句。常用关键字有insert、select、update、delete。

DCL：数据控制语句。常用关键字有grant、revoke。

问题、drop、delete和truncate的区别是什么？

（1）drop删除表，delete和truncate删除记录。

（2）delete属于DML语言，需要事务管理，commit之后才能生效。drop和truncate属于DDL语言，操作立刻生效，不可回滚。

（3）删除速度drop最快，delete最慢。

问题、select语句的执行顺序是怎么样的？

from -> join -> where -> group by -> avg、sum、max -> having -> select -> distinct -> order by -> limit

问题、union和union all的区别是什么？

union和union all的主要用来合并多个select语句获取的结果集，它们的区别是union all不会消去重复的记录，而union会消去重复的记录。

问题、建议列属性设置not null的原因是什么？

（1）null列需要更多的存储空间。

（2）count函数统计时，null列不会计入统计。

（3）concat函数拼接时，有字段为null会造成拼接结果为null。

（4）not in、!=等负向条件查询在有NULL值的情况下返回空行的结果集

问题、尽量避免使用join的原因是什么？

（1）让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。

（2）将查询分解后，执行单个查询可以减少锁的竞争。

（3）在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。

二、字段类型

问题、char和varchar的区别是什么？

（1）char是定长字符串，varchar是变长字符串。

（2）char占用空间比varchar多。

（3）char最多能存放255个字符，varchar最多能存放65532个字符。

（4）char存取速度比varchar快。

三、存储引擎

问题、MySQL有哪些存储引擎？

MyISA、InnoDB、Memory、Merge、NDB

问题、MyISAM和InnoDB的区别是什么？

（1）InnoDB支持事务，MyISAM不支持事务。

（2）InnoDB即支持行锁也支持表锁，MyISAM只支持表锁。

（3）InnoDB支持外键，MyISAM不支持外键。

（4）InnoDB不支持全文索引，MyISAM支持全文索引。

（5）MyISAM表可以被压缩，InnoDB表不能被压缩。

（6）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用。

（7）InnoDB不保存表的具体行数，执行select count(*)时需要全表扫描。MyISAM用变量保存了表的行数，执行上述语句只需要读出该变量即可，速度快。如果添加where条件，执行效率一样。

（8）MyISAM创建表生成三个文件：.frm（数据表结构）、.myd（数据文件）、.myi（索引文件）。InnoDB生成两个文件：.frm（数据表结构）、.ibd（数据索引文件）。

问题、InnoDB存储引擎的有几哪大特性?

插入缓冲、二次写、自适应哈希索引、预读

问题、MySQL中有哪几种锁？

（1）表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率高，并发量低。

（2）行级锁：开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率低，并发量高。

（3）页面锁：介于表锁和行锁之间，会出现死锁。

四、事务

问题、什么是事务？

事务是由一组SQL语句组成的逻辑处理单元。

问题、请描述下事务的ACID原则。

（1）原子性：事务里的所有操作要么全部做完，要么全部不做。

（2）一致性：事务完成，该调整的数据都调整，事务失败，该调整的数据都不调整。

（3）隔离性：并发执行的事务不会相互影响。

（4）持久性：事务完成后所做数据修改都会永久保存在数据库里。

问题、请描述下事务在并发执行时会出现什么问题？

（1）更新丢失：两个事务对同一条记录做修改，后面的事务会覆盖掉前面的事务的数据。

（2）脏读：一个事务读取到另一个事务还没有提交的数据。

（3）不可重复读：一个事务读取某条记录两次，之间有另一个事务对该记录做了修改，导致两次读取的数据不一样。

（4）幻读：一个事务读取记录列表两次，之间有另一个事务对列表插入了数据，导致两次读取的数据不一样。

问题、请描述下事务的隔离级别。

（1）未提交读：在一个事务中，可以读取到其他事务未提交的数据变化，这种读取其他会话还没提交的事务，叫做脏读现象，在生产环境中切勿使用。

（2）已提交读：在一个事务中，可以读取到其他事务已提交的数据变化，这种读取也就做不可重复读，因为两次同样的查询可能会得到不一样的结果。

（3）可重复读：MySQL默认隔离级别，在一个事务中，直到事务结束前，都可以反复读取到事务刚开始时看到的数据，并一直不会发生变化，避免了脏读、不可重复读现象，但是它还是无法解决幻读的问题。

（4）可串行化：这是最高的隔离级别，它强制事务串行执行，避免前面说的幻读现象，简单来说，它会在读取的每一行数据上都加锁，所以可能会导致大量的超时和锁争用问题。

问题、什么是乐观锁，什么是悲观锁？

乐观锁思想认为：一个事务读取数据的同时别的事务不会更新数据，更新数据的同时别的事务可能会更新数据。更新数据时需要对数据进行冲突验证，当发生冲突的时候，返回错误信息，让用户决定如何去做。

悲观锁思想认为：一个事务读取或更新数据的同时别的事务必定会更新数据。在读取或更新数据之前借助于数据库锁机制，先对数据进行加锁处理。

问题、什么是共享锁，什么是排他锁？

共享锁（读锁）：一个事务读取数据的同时别的事务可以读取数据，但是不能更新数据。

排他锁（写锁）：一个事务更新数据的同时别的事务不能读取数据也不能更新数据。

五、索引

问题、什么是索引？

索引是一种数据结构，可以帮助我们快速的进行数据的查找，可以分为Hash索引和B+树索引

问题、Hash索引和B+树索引的区别是什么？

（一）底层原理

Hash索引底层是Hash表，进行查找时，调用一次Hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。

B+树索引底层是多路平衡查找树，对于每一次的查询都是从根节点触发，找到到子节点方可获得所查键值，然后根据查询判断是否需要回表查询数据。

（二）区别

（1）Hash索引进行等值查询要比B+树索引来的快。

（2）Hash索引不能进行范围查询，B+树索引可以。

（3）Hash索引不支持使用索引进行排序，B+树索引可以。

（4）Hash索引不支持模糊查询以及组合索引的最左前缀匹配。

（5）Hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件的时候可以只通过索引完成查询。

（6）Hash索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生Hash碰撞，此时效率可能极差。B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，树的高度较低。

问题、索引有哪些类型？

普通索引、唯一索引、主键索引、全文索引、前缀索引、组合索引

问题、唯一索引与主键索引的区别是什么？

（1）主键索引是特殊的唯一索引。

（2）表里可以有多个唯一索引，但只能有一个主键索引。

（3）唯一索引可以有空值，主键索引不能有空值。

问题、建立字段为a，b，c的组合索引，where条件为c = 1 and b = 2 and a = 3的select语句在MySQL里会用到索引么，原因是什么？

会用到索引。MySQL优化器会对等值条件进行优化，使得索引可以被识别。

问题、建立字段为a，b，c的组合索引，where条件为a = 1 and b > 2 and c = 3的select语句在MySQL里会用到索引么，原因是什么？

会用到索引，但是只用了部分索引。根据最左前缀匹配原则：MySQL会一直向右匹配直到遇到非等值条件就停止匹配，故c是用不到索引。

问题、建立索引的时候需要考虑哪些因素？

（1）经常作为条件进行查询的字段比较适合创建索引。

（2）更新频繁的字段不建议创建索引。

（3）选择性低的字段不建议创建索引，比如“性别”，“状态”，“类型”。

（4）创建组合索引的话，要考虑组合索引中字段的顺序以及字段的数量。

（5）单张表的索引数量不宜过多。

（6）高并发场景不建议使用唯一索引。

问题、什么情况下字段创建了索引但是没有被用到？

（1）MySQL估计使用索引比全表扫描慢。

（2）or关键字两边的条件只有一个条件有索引。

（3）组合索引中第一个字段未被匹配。

（4）模糊查询like的值以“%”开始。

（5）where条件里字符串类型的值被隐式转换。

问题、怎么判断SQL语句有没有用到索引？

使用explain命令来查看语句的执行计划。

六、数据库优化

问题、提升MySQL的性能有哪些手段？

建立索引，优化SQL语句，优化数据库对象，优化MySQL Server，优化应用层

问题、说一说您在SQL语句优化方面做过哪些工作？

（1）select、insert语句显示的指明字段名称。

（2）插入多条数据使用insert的多值插入方式。

（3）order by的字段尽量使用where条件里创建过索引的字段，排序顺序与索引顺序相同。

（4）group by后面加上order by null。

（5）嵌套查询在某些情况下被更有效的join代替。

（6）对超大数据表分页查询语句进行优化。

问题、超大数据表分页查询怎么优化？

原始SQL语句为：select id, name, age from table limit 1000000, 10;

（1）在索引上完成排序分页的操作，然后根据主键关联回原表查询所需要的其他列内容。

例：select id, name, age from table where id in (select id from table limit 1000000, 10);

（2）把LIMIT查询转换成某个位置的查询。

例：select id, name, age from table where id > 1000000 limit 10;

问题、说一说您在数据库对象优化方面做过哪些工作？

（1）创建字段的时候选择合理的字段类型。

（2）逆规范化，添加数据冗余字段。

（3）分表分库。

问题、分库分表有哪几种类型？

（1）垂直分表：根据表中的字段活跃度及字段内容进行拆分，把活跃度低的，字段内容多的放在扩展表里。

（2）垂直分库：根据系统中的不同业务进行拆分，比如用户一个库，商品一个库，订单一个库。

（3）水平分表：将单张表的数据拆分到多张表。

（4）水平分库分表：将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表。

问题、分库分表有哪几种策略？

Hash取模、范围分片、地理位置分片、时间分片、类型分片

问题、分库分表后会出现哪些问题，有什么解决方案？

（一）分布式事务问题

解决方案：使用分布式事务中间件。

（二）跨库join问题

解决方案：1、添加冗余字段；2、分别查询所有，然后进行组装。

（三）结果集合并/排序问题

解决方案：使用缓存或者搜索引擎。

问题、请描述下您所知道的分布式事务的实现方案。

2pc（二阶段提交）、3pc（三阶段提交)、TCC（补偿事务）、消息通知

问题、为了提高MySQL性能，应用层您会做哪些优化工作？

使用连接池、使用缓存、主从复制读写分离、MySQL Cluster、尽量用一条select语句获取数据

问题、主从复制的执行流程是怎么样的？

从库创建I/O线程连接主库，主库随后创建Binlog Dump线程读取Binlog文件中的数据库事件并发送给I/O线程，I/O线程获取到数据库事件后更新到从库的中继日志Relay Log中去，之后从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用。

问题、Binlog文件支持哪几种格式？

（1）Statement：基于SQL语句级别的Binlog，每条修改数据的SQL都会保存到Binlog里。

（2）Row：基于行级别，记录每一行数据的变化，也就是将每行数据的变化都记录到Binlog里面，记录得非常详细，但是并不记录原始SQL；在复制的时候，并不会因为存储过程或触发器造成主从库数据不一致的问题，但是记录的日志量较Statement格式要大得多。

（3）Mixed：混合Statement和Row模式，默认情况下采用Statement模式记录，某些情况下会切换Row模式。

问题、数据库采用了主从复制进行了读写分离，那么获取实时性要求较高的数据，您是怎么处理的？

主数据库进行数据操作及实时性数据的获取，从数据库获取非实时性数据。

七、其他

问题、请描述下数据库的三大范式。

（1）表的字段不能再拆分。

（2）表的非主键字段必须完全依赖主键字段而不是依赖部分主键字段，针对于联合主键索引。

（3）表的非主键字段必须直接关联主键字段，针对于冗余字段。

问题、MySQL有哪几种日志？

错误日志、查询日志、慢查询日志、二进制日志

问题、MySQL怎么查看慢日志？

MySQL提供了日志分析工具mysqldumpslow