MySQL技术总结


# MySQL相关总结

MySQL索引

什么是索引

索引是帮助数据库高效获取数据的排好序的数据结构。

平衡二叉树

二叉搜索树

二叉树中对于比中间节点小的数据放在树的左边，比中间节点大的放在右边。

红黑树

特征

1. 节点是红色或是黑色
2. 根节点是黑色
3. 所有叶子都是黑色（叶子是NUIL节点）
4. 每个红色节点的两个子节点都是黑色（从每个叶子到根的所有路径上不能有两个连续的红色节点）
5. 从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点

红黑树较于二叉树的优点

1. 二叉树会存在左节点或右节点过长的情况

红黑树无法做索引数据结构的原因

存在树深度过长的情况

B树

• 叶子节点具有相同的深度，叶子节点的指针为空
• 所有索引的元素不重复
• 节点中的数据索引从左到右递增排列
  B树相较于红黑树在每个节点上存储的数据量更多，使得能够在深度不是太多的情况下保存更多的数据

B+树（B树变种）

• 非叶子节点不存储data，只存储索引（在不同的叶子节点存储父节点的索引），可以放更多的索引
• 叶子节点包含所有的索引
• 叶子节点用指针连接，提高区间访问的性能

myisam

• .frm文件 ->数据库表结构文件
• .MYD -> 数据库表数据文件
• .MYI -> 数据库表索引文件

innoDB

innoDB和myisam的区别

1. InnoDB支持事务，myisam不支持事务。InnoDB每一条SQL语句都默认封装成事务，自动提交，这样会影响速度，所以最好把多个SQL语句放在begin和commit之间。
2. InnoDB支持外键，myisam不支持外键，对于一个含有外键的InnoDB表转换成myisam会失败。
3. Innodb是聚集索引，使用的是B+树作为索引结构数据文件和索引文件绑定在一起，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后通过主键查询到数据，因此，主键的不应该过大，因为主键过大可能会导致其他的索引也都会很大。
   myisam是非聚集索引，也是使用B+树索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的
4. InnoDB不保存表的具体行数，在执行select count(*) 语句时会进行全表遍历，而myisam用一个变量保存整个表的行数。（InnoDB不支持的原因是事务性）
5. InnoDB不支持全文检索，而myisam支持全文检索，PS：在5.7之后InnoDB开始支持全文检索。
6. Myisam表格可以被压缩之后可以进行查询。
7. InnoDB支持表、行（默认）级锁，而Myisam只支持表级锁。（InnoDB的行锁实际上是在索引上的，而不是锁在物理记录行上，潜台词时，如果未命中索引，就无法使用行锁，退化为表锁）
8. InnoDB表必须要有主键（用户没有指定的话会自己找或生成一个主键），而myisam可以没有。
9. Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

• Innodb：frm是表定义文件，ibd是数据文件
• Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

Innodb为什么推荐使用自增ID作为主键？

自增id可以保证每次插入时B+树索引是向右扩展的，可以避免树的频繁合并和分裂。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。搜索二叉树在新增时会进行数据之间的比较，如果是字符串作为主键，存在比较复杂的情况。

聚集索引

• 定义：数据行的物理顺序与列（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引

非聚集索引

• 定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

联合索引

• 索引最左前缀原则

MySQL面试题

1. 为什么要一定设置主键

   如果定义了主键，那么InnoDB会选择主键作为聚集索引，如果没有定义主键，MySQL会选择第一个不含NULL的值的唯一索引作为主键索引，如果也没有这样的索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引。

2. 自增主键用完了怎么办

   这个问题我们没有遇到过，我们一般把主键的数据类型设置为bigint，在数据量过大的时候我们考虑的是使用分库分表，用redis来生成主键。

3. 时间用什么类型

   时间一般使用的是datetime类型，可以存储1000-01-01 00:00:00 ~ 9999-12-31 23:59:59之间的时间，但是一旦时区发生改变数据库的值不会发生改变。如果时间定义为TIMESTAMP，每当行被更改时，时间戳字段将获取当前时间戳。

4. 为什么不直接存储图片、音频、视频等大量内容？

   虽然在MySQL中支持BLOB类，但是在实际生产环境中不会使用，原因如下：

   1. MySQL内存临时表不支持TEXT,BLOB这样的大数据类型，如果查询中存在这样的字段，会导致查询十分缓慢。
   2. 数据库特别大，内存占用高，维护比较麻烦。
   3. binlog太大，如果是主从同步的结构，会导致主从同步效率问题。

5. char和varchar的区别

   • char：定长，效率高，一般存储固定长度的数据，最大长度为255，如果设置了长度，会用空格补足剩余的字段。
   • varchar: 不定长，效率偏低，最大长度为65535，不会进行空格的补全。
   • InnoDB推荐使用varchar

6. MySQL如何选择float, double, decimal

   • 浮点数如果不写经度和标度，会按照实际精度值保存，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；定点数如果不写精度和标度，则按照默认值decimal(10,0) 来操作，如果数据超过了精度和标度值，系统会报错。
     

7. BLOB和TEXT有什么区别？

   • BLOB和TEXT类型之间的唯一区别在于对BLOB值进行排序和比较时区分大小写，对TEXT值不区分大小写。

数据库查询优化

1. where语句的执行顺序是怎样的？
   where语句执行的顺序是从左到右执行的，在数据量小的时候不用去考虑，但是在数据量多的时候要考虑先后顺序，此时遵循一个原则：排除越多的条件放在第一个。
2. 创建索引的规则
   • 在经常搜索的列上添加索引，可以加快搜索速度
   • 在经常用在连接的列上，可以加快搜索速度
   • 在经常需要范围搜索的列上创建索引，因为索引已经排序，其指定范围是连续的
   • 在经常需要排序的列上创建索引，因为索引已经排序，可以加快搜索速度
   • 在经常使用where子句的列上创建索引，加快条件判断的速度
3. MySQL联合索引
   联合索引是两个或者两个以上列上的索引。对于联合索引MySQL从左到右的使用索引中的字段，一个查询只使用索引中的一部分，但只能是最左侧部分。例如索引是key index(a,b,c),可以支持a,a b,a b c三种查询，但是不支持b c这种查询，当最左侧字段是常量引用时，索引就十分有效。

数据库事务

1. 事务的概念

   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).

   事务并不是自然规律， 创建它们的目的是简化用于访问数据库的应用程序的编程模型。 通过使用事务，应用程序可以自由地忽略某些潜在的错误情况和并发问题，因为数据库将代替它们来处理它们（我们将其称为安全保证）。

2. 事务的特性

   • 原子性：对于事务的所有的操作，要么全部成功，要么全部失败。
   • 一致性：在事务执行前后，应用系统的状态是一致的。
   • 隔离性：多个事务之间不会相互影响。
   • 持久性：事务提交成功后，所有的数据都会持久保存到磁盘中。
     对于事务的一致性的理解:应用系统从一个正确的状态到另一个正确的状态.而ACID就是说事务能够通过AID来保证这个C的过程.C是目的,AID都是手段.

3. 脏读、不可重复读、幻读

   • 脏读：是指一个事务中读取到了另一个事务中未提交的数据
   • 不可重复读：在一个事务内根据相同的查询条件进行多次查询，查询出来的结果是不一样的。原因是由于别的事务修改了一部分数据。
   • 幻读：在一个事务内多次查询返回的结果集不一致（比如新增和删除了部分数据）

4. 事务的隔离级别

   • 读未提交：所有的事务都可以读取到别的事务未提交的执行结果。
   • 读已提交：一个事务只能看到已经提交的事务所作的改变。（Oracle默认的事务级别）
   • 可重复读：能确保用一个事务的多个实例在并发读取数据时，会看到相同的数据行。这会导致另一个棘手的问题：“幻读”。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC）机制解决了该问题
   • 串行读： 这是最高的事务隔离级别，使得所有的操作都是串行执行，并发情况下会有性能问题。

	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交		√	√
可重复读			√
串行读

数据库设计三范式

• 第一范式：表中的每一列必须是不可拆分的最小单元，确保每一列的原子性。满足第一范式时关系模型规范化的最低要求，否则，将有很多基本操作在这些关系模式中无法实现。（所有的关系型数据库都满足第一范式）

• 第二范式：关系模式必须满足第一范式，并且所有非主属性都完全依赖主码。

  • 主码：在数据表中有属性K，假设在K确定的情况下，该表中除K以外的所有属性值随之确定，那么K就是主码（K相当于主键）。
  • 主属性：包含在任何一个码中的属性称为主属性。
  • 非主属性：除了主属性之外的属性。

• 第三范式：满足2NF后，要求：表中的每一列都要与主键直接相关，而不是间接相关（表中的每一列只能依赖于主键）。

  第二范式与第三范式的区别：是否存在多张表，第三范式描述的是多张表之间的关系，一张表中只能有另一张表的主码，而不能有别的信息。
  注意：必须先满足第一范式才能满足第二范式，必须同时满足第一第二范式才能满足第三范式。

数据库五大约束

• 主键约束：唯一性，非空性
• 唯一约束：唯一性，可以为空，但是只能有一个
• 默认约束：该数据的默认值
• 外键约束：需要建立两张表之间的关系
• 非空约束：设置非空约束，表示改字段不能为空。