基础知识

mysql

1、Mysql中的sql执行过程

• 查询缓存：
  
  • MySQL在执行查询语句前，会先去查询缓存查看之前是否执行过这条语句。执行结果一般会以key-value形式缓存在内存中，key是SQL语句，value是查询结果集，如果命中，会直接将value返回给客户端。显然，查询命中缓存就无需执行后面的复杂操作，效率会很高。然而这个功能实际上弊大于利，它最大问题在于失效频繁，只要表上有更新，这个表上所有查询缓存就都会被清空，所以对于更新频繁的表来说，查询缓存的命中率很低。除非是很长时间才会更新一次的静态表，比如系统配置表，才适合使用。因此大多数情况下建议不要使用MySQL的查询缓存，我们可以通过修改query_cache_type配置关闭这个功能。如果想缓存结果，可以在客户端实现，例如利用mybatis自带的缓存功能，或者使用缓存中间件redis。
  • mysql 8.0之后取消缓存查询。
• 分析器：
  
  • MySQL根据SQL语句来理解你的意图，首先需要对SQL语句做解析，主要是包括词法解析和语法分析这两步操作。词法解析对语句分词，解析出关键字；语法分析在词法解析的基础上基于语法规则进行检验。我们在执行SQL时经常碰到的提示“You have an error in your SQL syntax”，就是分析器检查出来的错误。
• 优化器：
  
  • 经过分析器解析后，在真正执行前还会经过优化器处理。优化器可以选择不同的执行计划来提升执行的效率，例如选择使用哪个索引、决定各个表连接的顺序，它们最终执行的逻辑结果是一样，但是执行的效率会有不同。
• 执行器
  
  • 这个阶段才开始执行语句，在执行语句前会先判断对表有没有执行权限，如果有权限则打开表执行，执行器会使用对应存储引擎提供的接口来操作。

2、存储过程

• 可以理解为对SQL语句的封装，即包含SQL语句的函数，可以直接通过存储过程名调用。

  CREATE PROCEDURE 存储过程名称([参数列表])

  BEGIN

  需要执行的语句

  END

• 优点：
  
  • 一次编译多次使用，提升了SQL的执行效率。
  • 减少开发工作量的同时，还能保证代码的结构清晰。
  • 存储过程的安全性强，可以设置对用户的使用权限。
  • 减少网络传输量。
• 缺点：
  
  • 移植性差，存储过程不能跨数据库移植。
  • 调试困难。
  • 存储过程的版本管理很困难。
  • 不适合高并发的场景。

3、视图

• 相当于是一张表或多张表的数据结果集。

  // 创建视图语句

  CREATE VIEW player_height_grades AS

  SELECT p.player_name, p.height, h.height_level

  FROM player as p JOIN height_grades as h

  ON height BETWEEN h.height_lowest AND h.height_highest

  // 查询视图中的数据

  SELECT * FROM player_height_grades WHERE height >= 1.90 AND height <= 2.08

• 优点：
  
  • 安全性强。
  • 简单清晰。
• 缺点：
  
  • 修改限制
  • 操作视图会比直接操作基础表要慢

4、游标

• 可以让我们从数据结果集中每次提取一条数据记录进行操作。
• 常用步骤：
  
  • 定义游标：DECLARE cursor_name CURSOR FOR select_statement
  • 打开游标：OPEN cursor_name
  • 从游标中取得数据：FETCH cursor_name INTO var_name ...
  • 关闭游标：CLOSE cursor_name
  • 释放游标：DEALLOCATE PREPARE
• 优点：
  
  • 灵活性强，可以解决复杂的数据处理问题，对数据行进行逐行扫描处理
• 缺点：
  
  • 使用游标的过程中会对数据进行加锁，当业务并发量大的时候，会影响到业务的效率。同时游标是在内存中进行处理，会消耗系统资源，容易造成内存不足。

5、数据库有哪些数据库类型

5.1 类型分类

• 数值类型

有包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示 1 字节、2 字节、3 字节、4 字节、8 字节的整数类型。

1）任何整数类型都可以加上 UNSIGNED 属性，表示无符号整数。

2）任何整数类型都可以指定长度，但它不会限制数据的合法长度，仅仅限制了显示长度。

还有包括 FLOAT、DOUBLE、DECIMAL 在内的小数类型。

• 字符串类型

包括 VARCHAR、CHAR、TEXT、BLOB。

注意：VARCHAR(n) 和 CHAR(n) 中的 n 并不代表字节个数，而是代表字符的个数。

• 日期和时间类型

常用于表示日期和时间类型为 DATETIME、DATE 和 TIMESTAMP。

尽量使用 TIMESTAMP，空间效率高于 DATETIME。

5.2 CHAR 和 VARCHAR 区别

1）首先可以明确的是 CHAR 是定长的，而 VARCHAR 是可以变长。

CHAR 会根据声明的字符串长度分配空间，并会使用空格对字符串右边进行尾部填充。所以在检索 CHAR 类型数据时尾部空格会被删除，如保存的是字符串 'char '，但最后查询到的是 'char'。又因为长度固定，所以存储效率高于 VARCHAR 类型。

VARCHAR 在 MySQL 5.0 之后长度支持到 65535 字节，但会在数据开头使用额外 1~2 个字节存储字符串长度（列长度小于 255 字节时使用 1 字节表示，否则 2 字节），在结尾使用 1 字节表示字符串结束。

2）再者，在存储方式上，CHAR 对英文字符（ASCII）占用 1 字节，对一个汉字使用用 2 字节。而 VARCHAR 对每个字符均使用 2 字节。

虽然 VARCHAR 是根据字符串长度分配存储空间的，但在内存中依旧使用声明长度进行排序等作业，故在使用时仍需综合考量字段长度。/

5.3 CHAR和VARCHAR如何选择

1）对于经常变更的数据来说，CHAR 比 VARCHAR更好，因为 CHAR 不容易产生碎片。

2）对于非常短的列或固定长度的数据（如 MD5），CHAR 比 VARCHAR 在存储空间上更有效率。

4）使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。

4）尽量避免使用 TEXT/BLOB 类型，查询时会使用临时表，导致严重的性能开销。

5.4 CHAR，VARCHAR 和 Text 的区别？

1）长度区别

Char 范围是 0～255。

Varchar 最长是 64k（注意这里的 64k 是整个 row 的长度，要考虑到其它的 column，还有如果存在 not null 的时候也会占用一位，对不同的字符集，有效长度还不一样，比如 utf-8 的，最多 21845，还要除去别的column），但 Varchar 在一般情况下存储都够用了。

如果遇到了大文本，考虑使用 Text，最大能到 4G（其中 TEXT 长度 65,535 bytes，约 64kb；MEDIUMTEXT 长度 16,777,215 bytes，约 16 Mb；而 LONGTEXT 长度 4,294,967,295 bytes，约 4Gb）。

2）效率区别

效率来说基本是 Char > Varchar > Text，但是如果使用的是 Innodb 引擎的话，推荐使用 Varchar 代替 Char。

3）默认值区别

Char 和 Varchar 支持设置默认值，而 Text 不能指定默认值。