mysql 面试题 总结

mysql 查询过程是怎么样的

取得链接，使用使用到 MySQL 中的连接器。查询缓存，key 为 SQL 语句，value 为查询结果，如果查到就直接返回。不建议使用次缓存，在 MySQL 8.0 版本已经将查询缓存删除，也就是说 MySQL 8.0 版本后不存在此功能。分析器，分为词法分析和语法分析。此阶段只是做一些 SQL 解析，语法校验。所以一般语法错误在此阶段。优化器，是在表里有多个索引的时候，决定使用哪个索引；或者一个语句中存在多表关联的时候（join），决定各个表的连接顺序。执行器，通过分析器让 SQL 知道你要干啥，通过优化器知道该怎么做，于是开始执行语句。执行语句的时候还要判断是否具备此权限，没有权限就直接返回提示没有权限的错误；有权限则打开表，根据表的引擎定义，去使用这个引擎提供的接口，获取这个表的第一行，判断 id 是都等于 1。如果是，直接返回；如果不是继续调用引擎接口去下一行，重复相同的判断，直到取到这个表的最后一行，最后返回

mysql 存储引擎有哪些

礪 InnoDB 和MyISAM

InnoDB 和MyISAM 有什么区别

礪 MyISAM

礪mysql 5.1 之前的版本 MyISAM 是默认的存储引擎 而在5.5版本以后 默认使用了 InnoDB 存储引擎礪索引文件和数据文件是分开礪MyISAM 不支持行级锁 只支持表锁礪MyISAM 不支持事务 MyISAM 支持读多 count 有专门存储的地方 索引结构都是B+tree 礪 InnoDB

礪而在5.5版本以后 默认使用了 InnoDB 存储引擎礪索引文件和数据文件是在一块的礪InnoDB 支持行级锁 和表锁礪InnoDB 支持事务 InnoDB 支持读写 count 全表扫描 索引结构都是 B+tree

mysql 查询的时候如何提高性能 (问索引)

索引，类似于书籍的目录，想找到一本书的某个特定的主题，需要先找到书的目录，定位对应的页码。

MySQL 中存储引擎使用类似的方式进行查询，先去索引中查找对应的值，然后根据匹配的索引找到对应的数据行

为什么可以用利用索引提高查询效率了?

礪 提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。

礪 降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本。

常见的索引的数据结构有哪些

礪 B+Tree礪 HASH mysql 内部是 hash 我们没有办法使用礪 R-Tree礪 FullTest

7 常见的索引有哪些

礪 主键索引 特殊的唯一索引 不可有空值礪 普通索引 最基本的索引 没有任何约束礪 复合索引 将多个列组合在一起创建索引 可以覆盖多个列礪 唯一索引 与普通索引类似 但具有唯一性约束礪 外键索引 只有InnoDB 类型的表才可以使用外键索引 保证数据的一致性 完整性和实现级联操作礪 全文检索 我的建议是使用 ES 和Solr 等

8 B-Tree 和 B+Tree 有什么区别

礪 B-Tree 它的的非叶子 节点存储数据 树会越变越高 效率低下礪 B+Tree 非叶子节点只存储键值信息。所有叶子节点之间都有一个链指针。数据记录都存放在叶子节点中。效率更高

单值索引和组合索引的区别 组合索引遵循什么原则

礪 我理解的单值索引 就是一个字段查询建议的索引礪 我理解的组合索引就是多个字段查询所建议的索引 遵守最左原则

如何观察sql 语句用没有用到索引了?

EXPLAIN 显示了 MYSQL 如何使用索引来处理 SELECT 语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在 SELECT 语句前加上 EXPLAIN 就可以了

explain 后面正常接sql语句 命令 通过慢查询语句进行查询 看看有不有用到索引

索引在什么情况下会失效?

礪 在模糊查询中 like % 在前面的 索引会失效礪 在范围查询当中 只有范围查询条件用到了索引 其他列都失效了 范围查询 < 或 > 可以推荐使用 BETWEEN ‘10001’ AND ‘10020’ 这样可以避免其他索引失效礪 应尽量避免在 WHERe 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

join 怎么进行优化?

礪 第一种算法 Simple Nested-Loop Join 效率是最低的礪 第二种算法 Index Nested-Loop Join on 里面条件 走table 2 的索引 效率高 有很大的提升礪 小表驱动大表 Block Nested-Loop Join 缓存块嵌套循环连接礪 在选择Join算法时，会有优先级，理论上会优先判断能否使用INLJ、BNLJ：礪 Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join

order by 怎么进行优化?

礪 双路排序 ：Mysql4.1之前是使用双路排序，字面的意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对数据输出。也就是从磁盘读取排序字段，在buffer进行排序，再从磁盘读取其他字段。文件的磁盘IO非常耗时的。礪 单路排序 在Mysql4.1之后，出现了第二种算法，就是单路排序。从磁盘读取查询所需要的所有列，按照ORDER BY在buffer对它进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在了内存里。礪加大 max_length_for_sort_data 参数；礪 去掉不必要的返回字段；

mysql 中varchar 与char 的区别? varchar(30)中的30代表的含义 ?

1、varchar 与 char 的区别，char 是一种固定长度的类型，varchar 则是一种可变长度的类型。2、varchar(50) 中 50 的涵义最多存放 50 个字符。varchar(50) 和 (200) 存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 ORDER BY col 采用 fixed_length 计算 col 长度(memory引擎也一样)

数据库三方式与反范式

礪 原子性 字段不可再分 否则就不是关系数据库礪 唯一性 一个表只说明一个事务礪 每列都与主键有直接关系 不存在传递依赖反模式 范式可以避免数据 沉余 减少数据库的空间 减轻维护数据完整性的麻烦

16.说说是什么是MVCC
- 礪 多版本并发控制（MVCC=Multi-Version Concurrency Control），是一种用来解决读 - 写冲突的无锁并发控制。也就是为事务分配单向增长的时间戳，为每个修改保存一个版本。版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照（复制了一份数据）。这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

说说MVCC 的实现原理
礪 就是多版本并发控制 在数据库中的实现 就是为了解决读写冲突 它的实现原理主要是依赖记录中的3个隐式字段 undo 日志 Read View 来实现的

MyISAM 索引与 InnoDB 索引的区别？

礪 InnoDB 索引是聚簇索引，MyISAM 索引是非聚簇索引。
礪 InnoDB 的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
礪 MyISAM 索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
礪 InnoDB 非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

什么时候不要使用索引?

礪  经常增删该的列不要建议索引
礪 有大量重复的列不要建议索引
礪 表记录大少 不要建立索引

请说说 MySQL 数据库的锁

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

说说什么是锁升级

MySQL 行锁只能加在索引上，如果操作不走索引，就会升级为表锁。因为 InnoDB 的行锁是加在索引上的，如果不走索引，自然就没法使用行锁了，原因是 InnoDB 是将 primary key index 和相关的行数据共同放在 B+ 树的叶节点。InnoDB 一定会有一个 primary key，secondary index 查找的时候，也是通过找到对应的 primary，再找对应的数据行。
当非唯一索引上记录数超过一定数量时，行锁也会升级为表锁。测试发现当非唯一索引相同的内容不少于整个表记录的二分之一时会升级为表锁。因为当非唯一索引相同的内容达到整个记录的二分之一时，索引需要的性能比全文检索还要大，查询语句优化时会选择不走索引，造成索引失效，行锁自然就会升级为表锁。

谈谈 悲观锁和乐观锁

悲观锁
因此在整个数据修改过程中，将数据处于锁状态。悲观的实现往往是依靠数据库提供的锁机制，也只有数据库层面提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统汇总实现了加锁机制，也是没有办法保证系统不会修改数据。在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。乐观锁相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（Version）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。