MySQL--八股文（一）

一、什么是MySQL二、MySQL常用的储存引擎有什么它们有什么区别三、数据库的三大范式有哪些四、MySQL的数据类型有哪些五、索引六、B树和B树一、什么是MySQLMySQL是一种开放源代码的关系型数据库管理系统RDBMS使用最常用的数据库管理语言--结构化查询语言SQL进行数据库管理。MySQL是开放源代码的因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。二、MySQL常用的储存引擎有什么它们有什么区别1InnoDB 是MySQL的默认存储引擎支持事务、外键等操作。2MyISAM 是MySQL5.1版本前的默认存储引擎MyISAM的并发性比较差不支持事务和外键等操作默认的锁的粒度为表级锁。InnoDBMyISAM外键支持不支持事务支持不支持锁支持表锁和行锁支持表锁可恢复性根据事务日志进行恢复无事务日志表结构数据和索引是集中存储的.ibd 和 .frm数据和索引是分开存储的数据.MYD索引.MYI查询性能一般情况相比于MyISAM较差一般情况相比于InnoDB较差索引聚簇索引非聚簇索引三、数据库的三大范式有哪些1第一范式确保每列保存原子性数据表中的所有字段都是不可再分的原子值。2第二范式确保表中的每列都和主键相关。3第三范式确保每列都和主键列直接相关而不是间接相关。四、MySQL的数据类型有哪些1整数TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别占用8、16、32、64位存储空间。注意INT(10)中的10只是表示显示字符的个数并无实际意义一般要和UNSIGNED ZEROFILL配合使用才有实际意义例如数据类型INT(3)属性为UNSIGNED ZEROFILL如果插入的数据为3的话实际存储的数据为003。2浮点数FLOAT、DOUBLE、及DECLMAL为浮点类型DECIMAL是利用字符串进行处理的能存储精确的小数。相比于FLOAT和DOUBLEDECIMAL的效率更低些。FLOAT、DOUBLE及DECIMAL都可以指定列宽例如FLOAT(5,2)表示一共5位两位存储小数部分三位存储整数部分。3字符串字符串常用的主要有CHAR和VARCHARVARCHAR主要用于存储可变长字符串相比于定长CHAR更节省空间。CHAR是定长的根据定义的字符串长度分配空间。应用场景对于经常变更的数据使用CHAR更好CHAR不容易产生碎片。对于非常短的列也是使用CHAR更好些CHAR相比于VARCHAR在效率上更高些。一般避免使用TEXT/BLOB等类型因为查询时会使用临时表造成严重的性能开销。4日期比较常用的有year、time、date、datetime、timestamp等datetime保存从1000年到9999年的时间精度到秒使用8字节的存储空间与时区无关。timestamp和UNIX的时间戳相同保存从1970年1月1日午夜到2038年的时间精度到秒使用四个字节的存储空间并且与时区相关。应用场景尽量使用timestamp相比于datetime它有着更高的空间效率。五、索引1什么是索引索引是对数据库表的一列或者多列的值进行排序的一种结构使用索引可以快速访问数据表的特定信息。2优点大大加快数据检索的速度将随机I/O变成顺序I/O因为B树的叶子节点是连接在一起的加快表与表之间的连接。3缺点从空间角度考虑建立索引需要占用物理空间从时间角度考虑创建和维护索引都需要花费时间例如对数据进行增删改查的时候都需要维护索引。4索引的数据结构索引的数据结构主要有B树和哈希表对应的索引分别为B树索引和哈希索引。InnoDB引擎的索引类型有B树索引和哈希索引默认的索引类型为B树索引。5什么是聚簇索引什么是非聚簇索引聚簇索引和非聚簇索引最主要的区别是数据和索引是否分开存储。聚簇索引将数据和索引放到一起存储索引结果和叶子节点都保留了数据行。非聚簇索引将数据和索引分开存储索引叶子结点存储的是指向数据行的地址。在InnoDB存储引擎中默认的索引为B树索引利用主键创建的索引为主索引也是聚簇索引在主索引之上创建的索引为辅助索引也就是非聚簇索引。为什么说辅助索引是在主索引之上创建的呢因为辅助索引中的叶子结点存储的是主键。在MYISAM存储引擎中默认的也是B树索引但主索引和复制索引都是非聚簇索引也就是说索引结果的叶子结点存储的都是一个指向数据行的地址并且使用辅助索引检索无需访问主键索引。非聚簇索引一定会进行回表查询吗上面是说了非聚簇索引的叶子节点存储的是主键也就是说要先通过非聚簇索引找到主键再通过聚簇索引找到主键所对应的数据后面这个再通过聚簇索引找到主键对应的数据的过程就是回表查询那么非聚簇索引就一定会进行回表查询吗答案是不一定的这里涉及到一个索引覆盖的问题如果查询的数据在辅助索引上完全能获取到便不需要回表查询。例如有一张表存储着个人信息包括 id、name、age 等字段。假设聚簇索引是以 ID 为键值构建的索引非聚簇索引是以 name 为键值构建的索引select id,name from user where name zhangsan;这个查询便不需要进行回表查询因为通过非聚簇索引已经能全部检索出数据这就是索引覆盖的情况。如果查询语句是这样select id,name,age from user where name zhangsan;则需要进行回表查询因为通过非聚簇索引不能检索出 age 的值。那应该如何解决那呢只需要将索引覆盖即可建立 age 和 name 的联合索引再使用select id,name,age from user where name zhangsan;进行查询即可。所以通过索引覆盖能解决非聚簇索引回表查询的问题。索引的使用场景有哪些对于中大型表建立索引非常有效对于非常小的表一般全部表扫描速度更快些。对于超大型的表建立和维护索引的代价也会变高这时可以考虑分区技术。如表的增删改非常多而查询需求非常少的话那就没有必要建立索引了因为维护索引也是需要代价的。一般不会出现在 where 条件中的字段就没有必要建立索引了。多个字段经常被查询的话可以考虑联合索引。字段多且字段值没有重复的时候考虑唯一索引。字段多且有重复的时候考虑普通索引。索引的设计原则1最适合索引的列是在 where 后面出现的列或者连接句子中指定的列而不是出现在 SELECT 关键字后面的选择列表中的列。2索引列的基数越大索引的效果越好换句话说就是索引列的区分度越高索引的效果越好。比如使用性别这种区分度很低的列作为索引效果就会很差因为列的基数最多也就是三种大多不是男性就是女性。3尽量使用短索引对于较长的字符串进行索引时应该指定一个较短的前缀长度因为较小的索引涉及到的磁盘 I/O 较少并且索引高速缓存中的块可以容纳更多的键值会使得查询速度更快。4尽量利用最左前缀。5不要过度索引每个索引都需要额外的物理空间维护也需要花费时间所以索引不是越多越好。哈希索引哈希索引是基于哈希表实现的一种数据库索引结构核心逻辑如下实现原理对每一行数据的索引列通过哈希算法计算得到哈希码。哈希算法会尽量保证不同列值生成不同哈希码以减少冲突。将哈希码作为哈希表的key将指向数据行的指针作为哈希表的value。核心优势查找效率极高时间复杂度为 O (1)适合精确等值查询场景。典型局限不支持排序、范围查询、模糊查询及最左前缀匹配。存在哈希冲突时性能会下降稳定性不如 B 树索引。Hash 索引和 B 树的区别因为两者数据结构上的差异导致它们的使用场景也不同哈希索引一般多用于精确的等值查找B 树索引则多用于除了精确的等值查找外的其他查找。在大多数情况下会选择使用 B 树索引。哈希索引不支持排序因为哈希表是无序的。哈希索引不支持范围查找。哈希索引不支持模糊查询及多列索引的最左前缀匹配。因为哈希表中会存在哈希冲突所以哈希索引的性能是不稳定的而 B 树索引的性能是相对稳定的每次查询都是从根节点到叶子节点。索引的类型有哪些MySQL 主要的索引类型主要有FULLTEXT、HASH、BTREE、RTREE。FULLTEXT全文索引即全文索引MyISAM 存储引擎和 InnoDB 存储引擎在 MySQL 5.6.4 以上版本支持全文索引。一般用于查找文本中的关键字而非直接比较是否相等多在CHAR、VARCHAR、TEXT等数据类型上创建。主要用来解决WHERE name LIKE %zhang%这类针对文本的模糊查询效率低的问题。HASH哈希索引即哈希索引多用于等值查询时间复杂度为 O (1)效率非常高。不支持排序、范围查询及模糊查询等。BTREEB 树索引即 B 树索引是 InnoDB 存储引擎默认的索引类型。支持排序、分组、范围查询、模糊查询等并且性能稳定。RTREE空间数据索引即空间数据索引多用于地理数据的存储。相比于其他索引空间数据索引的优势在于范围查找。索引的种类有哪些主键索引数据列不允许重复不能为 NULL一个表只能有一个主键索引。组合索引由多个列值组成的索引。唯一索引数据列不允许重复可以为 NULL索引列的值必须唯一如果是组合索引则列值的组合必须唯一。全文索引对文本的内容进行搜索。普通索引基本的索引类型列值可以为 NULL。六、B树和B树的区别面试常考1.主要有两点1B树中的内部节点和叶子节点均存放键和值而B树的内部节点只有键没有值叶子节点存放所有的键和值。2B树的叶子节点是通过相连在一起的方便顺序检索。2. 数据库为什么使用B树而不是B树?1B树适用于随机检索而B树适用于随机检索和顺序检索。2B树的空间利用率更高因为B树的每个节点要存储键和值而B树的内部节点只存储键这样B树的一个节点就可以存储更多的索引从而使树的高度变低减少了I/O次数使得数据检索速度更快。3B树的叶子结点都是连接在一起的所以范围查找顺序查找更加方便。4B树的性能更加稳定因为B树中每次查询都是从根节点到叶子节点而在B树中要查询的值可能不在叶子结点在内部节点就已经找到。什么情况下用B树因为B树的内部节点也可以存储值所以可以把一些频繁访问的值放在距离根节点比较近的地方这样就可以提高查找效率。