MySQL技术总结
- MySQL索引
- 什么是索引
- 平衡二叉树
- 二叉搜索树
- 红黑树
- 特征
- 红黑树较于二叉树的优点
- 红黑树无法做索引数据结构的原因
- B树
- B+树（B树变种）
- myisam
- innoDB
- innoDB和myisam的区别
- Innodb为什么推荐使用自增ID作为主键？
- 聚集索引
- 非聚集索引
- 联合索引
- MySQL面试题
- 数据库查询优化
- 数据库事务
- 数据库设计三范式
- 数据库五大约束
# MySQL相关总结
MySQL索引
什么是索引
索引是帮助数据库高效获取数据的排好序的数据结构。
平衡二叉树
二叉搜索树
二叉树中对于比中间节点小的数据放在树的左边，比中间节点大的放在右边。
红黑树
特征
- 节点是红色或是黑色
- 根节点是黑色
- 所有叶子都是黑色（叶子是NUIL节点）
- 每个红色节点的两个子节点都是黑色（从每个叶子到根的所有路径上不能有两个连续的红色节点）
- 从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点
红黑树较于二叉树的优点
- 二叉树会存在左节点或右节点过长的情况
红黑树无法做索引数据结构的原因
存在树深度过长的情况
B树
- 叶子节点具有相同的深度，叶子节点的指针为空
- 所有索引的元素不重复
- 节点中的数据索引从左到右递增排列
B树相较于红黑树在每个节点上存储的数据量更多，使得能够在深度不是太多的情况下保存更多的数据
B+树（B树变种）
- 非叶子节点不存储data，只存储索引（在不同的叶子节点存储父节点的索引），可以放更多的索引
- 叶子节点包含所有的索引
- 叶子节点用指针连接，提高区间访问的性能
myisam
- .frm文件 ->数据库表结构文件
- .MYD -> 数据库表数据文件
- .MYI -> 数据库表索引文件
innoDB
innoDB和myisam的区别
- InnoDB支持事务，myisam不支持事务。InnoDB每一条SQL语句都默认封装成事务，自动提交，这样会影响速度，所以最好把多个SQL语句放在begin和commit之间。
- InnoDB支持外键，myisam不支持外键，对于一个含有外键的InnoDB表转换成myisam会失败。
- Innodb是聚集索引，使用的是B+树作为索引结构数据文件和索引文件绑定在一起，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后通过主键查询到数据，因此，主键的不应该过大，因为主键过大可能会导致其他的索引也都会很大。
myisam是非聚集索引，也是使用B+树索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的 - InnoDB不保存表的具体行数，在执行select count(*) 语句时会进行全表遍历，而myisam用一个变量保存整个表的行数。（InnoDB不支持的原因是事务性）
- InnoDB不支持全文检索，而myisam支持全文检索，PS：在5.7之后InnoDB开始支持全文检索。
- Myisam表格可以被压缩之后可以进行查询。
- InnoDB支持表、行（默认）级锁，而Myisam只支持表级锁。（InnoDB的行锁实际上是在索引上的，而不是锁在物理记录行上，潜台词时，如果未命中索引，就无法使用行锁，退化为表锁）
- InnoDB表必须要有主键（用户没有指定的话会自己找或生成一个主键），而myisam可以没有。
- Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
- Innodb：frm是表定义文件，ibd是数据文件
- Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
Innodb为什么推荐使用自增ID作为主键？
自增id可以保证每次插入时B+树索引是向右扩展的，可以避免树的频繁合并和分裂。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。搜索二叉树在新增时会进行数据之间的比较，如果是字符串作为主键，存在比较复杂的情况。
聚集索引
- 定义：数据行的物理顺序与列（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引
非聚集索引
- 定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
联合索引
- 索引最左前缀原则
MySQL面试题
- 为什么要一定设置主键
如果定义了主键，那么InnoDB会选择主键作为聚集索引，如果没有定义主键，MySQL会选择第一个不含NULL的值的唯一索引作为主键索引，如果也没有这样的索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引。 - 自增主键用完了怎么办
这个问题我们没有遇到过，我们一般把主键的数据类型设置为bigint，在数据量过大的时候我们考虑的是使用分库分表，用redis来生成主键。 - 时间用什么类型
时间一般使用的是datetime类型，可以存储1000-01-01 00:00:00 ~ 9999-12-31 23:59:59之间的时间，但是一旦时区发生改变数据库的值不会发生改变。如果时间定义为TIMESTAMP，每当行被更改时，时间戳字段将获取当前时间戳。 - 为什么不直接存储图片、音频、视频等大量内容？
虽然在MySQL中支持BLOB类，但是在实际生产环境中不会使用，原因如下：
- MySQL内存临时表不支持TEXT,BLOB这样的大数据类型，如果查询中存在这样的字段，会导致查询十分缓慢。
- 数据库特别大，内存占用高，维护比较麻烦。
- binlog太大，如果是主从同步的结构，会导致主从同步效率问题。
- char和varchar的区别
- char：定长，效率高，一般存储固定长度的数据，最大长度为255，如果设置了长度，会用空格补足剩余的字段。
- varchar: 不定长，效率偏低，最大长度为65535，不会进行空格的补全。
- InnoDB推荐使用varchar
- MySQL如何选择float, double, decimal
- 浮点数如果不写经度和标度，会按照实际精度值保存，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；定点数如果不写精度和标度，则按照默认值decimal(10,0) 来操作，如果数据超过了精度和标度值，系统会报错。
- BLOB和TEXT有什么区别？
- BLOB和TEXT类型之间的唯一区别在于对BLOB值进行排序和比较时区分大小写，对TEXT值不区分大小写。
数据库查询优化
- where语句的执行顺序是怎样的？
where语句执行的顺序是从左到右执行的，在数据量小的时候不用去考虑，但是在数据量多的时候要考虑先后顺序，此时遵循一个原则：排除越多的条件放在第一个。 - 创建索引的规则
- 在经常搜索的列上添加索引，可以加快搜索速度
- 在经常用在连接的列上，可以加快搜索速度
- 在经常需要范围搜索的列上创建索引，因为索引已经排序，其指定范围是连续的
- 在经常需要排序的列上创建索引，因为索引已经排序，可以加快搜索速度
- 在经常使用where子句的列上创建索引，加快条件判断的速度
- MySQL联合索引
联合索引是两个或者两个以上列上的索引。对于联合索引MySQL从左到右的使用索引中的字段，一个查询只使用索引中的一部分，但只能是最左侧部分。例如索引是key index(a,b,c),可以支持a,a b,a b c三种查询，但是不支持b c这种查询，当最左侧字段是常量引用时，索引就十分有效。
数据库事务
- 事务的概念
Transactions are not a law of nature; they were created with a purpose, namely to simplify the programming model for applications accessing a database. By using transactions, the application is free to ignore certain potential error scenarios and concurrency issues, because the database takes care of them instead (we call these safety guarantees).
- 事务并不是自然规律， 创建它们的目的是简化用于访问数据库的应用程序的编程模型。 通过使用事务，应用程序可以自由地忽略某些潜在的错误情况和并发问题，因为数据库将代替它们来处理它们（我们将其称为安全保证）。
- 事务的特性
- 原子性：对于事务的所有的操作，要么全部成功，要么全部失败。
- 一致性：在事务执行前后，应用系统的状态是一致的。
- 隔离性：多个事务之间不会相互影响。
- 持久性：事务提交成功后，所有的数据都会持久保存到磁盘中。
对于事务的一致性的理解:应用系统从一个正确的状态到另一个正确的状态.而ACID就是说事务能够通过AID来保证这个C的过程.C是目的,AID都是手段.
- 脏读、不可重复读、幻读
- 脏读：是指一个事务中读取到了另一个事务中未提交的数据
- 不可重复读：在一个事务内根据相同的查询条件进行多次查询，查询出来的结果是不一样的。原因是由于别的事务修改了一部分数据。
- 幻读：在一个事务内多次查询返回的结果集不一致（比如新增和删除了部分数据）
- 事务的隔离级别
- 读未提交：所有的事务都可以读取到别的事务未提交的执行结果。
- 读已提交：一个事务只能看到已经提交的事务所作的改变。（Oracle默认的事务级别）
- 可重复读：能确保用一个事务的多个实例在并发读取数据时，会看到相同的数据行。这会导致另一个棘手的问题：“幻读”。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC）机制解决了该问题
- 串行读： 这是最高的事务隔离级别，使得所有的操作都是串行执行，并发情况下会有性能问题。
脏读 | 不可重复读 | 幻读 | |
读未提交 | √ | √ | √ |
读已提交 | √ | √ | |
可重复读 | √ | ||
串行读 |
数据库设计三范式
- 第一范式：表中的每一列必须是不可拆分的最小单元，确保每一列的原子性。满足第一范式时关系模型规范化的最低要求，否则，将有很多基本操作在这些关系模式中无法实现。（所有的关系型数据库都满足第一范式）
- 第二范式：关系模式必须满足第一范式，并且所有非主属性都完全依赖主码。
- 主码：在数据表中有属性K，假设在K确定的情况下，该表中除K以外的所有属性值随之确定，那么K就是主码（K相当于主键）。
- 主属性：包含在任何一个码中的属性称为主属性。
- 非主属性：除了主属性之外的属性。
- 第三范式：满足2NF后，要求：表中的每一列都要与主键直接相关，而不是间接相关（表中的每一列只能依赖于主键）。
第二范式与第三范式的区别：是否存在多张表，第三范式描述的是多张表之间的关系，一张表中只能有另一张表的主码，而不能有别的信息。
注意：必须先满足第一范式才能满足第二范式，必须同时满足第一第二范式才能满足第三范式。
数据库五大约束
- 主键约束：唯一性，非空性
- 唯一约束：唯一性，可以为空，但是只能有一个
- 默认约束：该数据的默认值
- 外键约束：需要建立两张表之间的关系
- 非空约束：设置非空约束，表示改字段不能为空。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
