MySQL 75道面试题及答案.docx

MySQL面试题及答案 MySQL索引使用有哪些注意事项？可以从三个维度回答这个问题：索引哪些情况会失效，索引不适合哪些场景，索引规则。 索引哪些情况会失效： 1. 查询条件包含or，可能导致索引失效 2. 字段类型是字符串，where时一定用引号括起来，否则索引失效 3. like通配符可能导致索引失效 4. 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效 5. 在索引列上使用MySQL的内置函数，索引失效 6. 对索引列运算（如,+、-、*、/），索引失效 7. 索引字段上使用（！=或者<>，not in）时，可能会导致索引失效 8. 索引字段上使用is null,is not null，可能导致索引失效 9. 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效 10. MySQL估计使用全表扫描要比使用索引快，则不使用索引 索引不适合哪些场景： 1. 数据量少的不适合加索引 2. 更新比较频繁的也不适合加索引 3. 区分度低的字段不适合加索引（如性别） 索引的一些潜规则： 1. 覆盖索引 2. 回表 3. 索引数据结构（B+树） 4. 最左前缀原则 5. 索引下推 MySQL遇到过死锁问题吗？你是如何解决的？ 我排查死锁的一般步骤是： 1. 查看死锁日志show engine innodb status; 2. 找出死锁Sql 3. 分析sql加锁情况 4. 模拟死锁案发 5. 分析死锁日志 6. 分析死锁结果 日常工作中你是怎么优化SQL的？可以从这几个维度回答这个问题： 1. 加索引 2. 避免返回不必要的数据 3. 适当分批量进行 4. 优化sql结构 5. 分库分表 6. 读写分离 分库与分表的设计： 分库分表方案： 1. 水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。 2. 水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。 3. 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。 4. 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。 常用的分库分表中间件： 1. sharding-jdbc（当当） 2. Mycat 3. CTDDL（淘宝） 4. Oceanus（58同城数据库中间件） 5. vitess（谷歌开发的数据库中间件） 6. Atlas（Qihoo 360） 分库分表可能遇到的问题： 1. 事务问题：需要用分布式事务啦 2. 跨节点Join的问题：解决这一问题可以分两次查询实现 3. 跨节点的count,order by,group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并 4. 数据迁移，容量规划，扩容等问题 5. ID问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑UUID 6. 跨分片的排序分页问题（后台加大pagesize处理？） InnoDB与MyISAM的区别： 1. InnoDB支持事务，MyISAM不支持事务 2. InnoDB支持外键，MyISAM不支持外键 3. InnoDB支持MVCC（多版本并发控制），MyISAM不支持 4. select count(*)from table 时，MyISAM更快，因为它有一个变量保存了整个表的总行数，可以直接读取，InnoDB就需要全表扫描 5. InnoDB不支持全文索引，而MyISAM支持全文索引（5.7以后的InnoDB 也支持全文索引） 6. InnoDB支持表、行级锁，而MyISAM支持表级锁 7. InnoDB表必须有主键，而MyISAM可以没有主键 8. InnoDB表需要更多的内存和存储，而MyISAM可被压缩，存储空间较小，。 9. InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，与MyISAM比InnoDB写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引 数据库索引的原理，为什么要用B+树，为什么不用二叉树？ 可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数，为什么不是二叉树，为什么不是平衡二叉树，为什么不是B树，而偏偏是B+树呢？ 为什么不是一般二叉树？如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。 为什么不是平衡二叉树呢？我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。 那为什么不是B树？B+树可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。