MySQL-javaguide

MySQL基础

什么是关系型数据库？

就是一种建立在关系型模型（表示存储数据之间的联系 一对一、一对多、多对多）上面的数据库。

什么是SQL?

结构化查询语言，专门用来与数据库打交道的

什么是MySQL?

它是开源免费的关系型数据库，主要用于持久化存储我们系统中的一些数据比如用户信息。

MySQL有什么优点？

免费开源 成熟稳定，功能完善 支持事务 多种引擎 文档丰富 社区活跃 支持分表分库、读写分离、高可用

MySQL字段类型

三大类：

数值型：

• 整型：tinyint smallint mediumint int bigint
• 浮点型：float double
• 定点型：decimal

字符串类型：

• 常用：CHAR VARCHAR
• TEXT类：tinytext text mediumtext longtext
• BLOB类：tinyblob blob mediumblob longblob

日期时间类型：

• 常用：year time date datetime timestamp

整数类型的unsigned属性有什么用？

tinyint unsigned 取值范围是0~255

int unsigned 取值范围0~4294967295

整数类型可以使用可选的unsigned属性来表示不允许负值的无符号整数。使用unsigned属性可以将正整数的上限提升一倍

CHAR VARCHAR 的区别

定长字符串 用空格填充以达到指定的长度 eg：身份证 性别

变长字符串 eg：用户昵称、文章标题

VARCHAR(100) VARCHAR(10) 的区别

100 表示能存储100个字符

相同字符串的话，占用的磁盘存储空间是一样的

但是100会消耗更多的内存。因为varchar在排序的时候，在内存上面会占用100个长度的内存

decimal 和 float double 的区别是什么

decimal 是 定点数 存储精确的小数值

float / double 是浮点数 存储近似的小数值

eg：decimal存储具有精确要求的小数，例如与货币相关的数据，可以避免浮点数带来的精度损失

在java中，mysql中的decimal类型对应的是java中的java.math.BigDecimal

MYSQL索引失效的场景

1. 不遵守最左匹配原则，就是在一个联合索引中 abc，如果没有a 那么就会索引失效
2. in not in 如果查询的范围超过数据30%，就会范围过大，造成索引失效
3. 在条件中，使用了函数计算和表达式计算，就会造成索引值不匹配，就会索引失效
4. like % 进行匹配的时候 %在左边就会因为范围过大，造成索引失效
5. 使用or表达式 左右两边都必须是索引，如果有一边不是索引，就会造成索引失效
6. order by 排序的时候，这是mysql自身优化的问题，就是会进行回表，mysql就会认为全表查询会更优，就会造成索引失效

为什么不推荐使用Text和Blob？

不推荐

DATETIME 和 TIMESTAMP 的区别是什么？

datatime 没有时区信息 timestamp 和时区相关

datatime需要耗费8个字节的存储空间 timestamp只需要使用4个字节的存储空间

DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

NULL和''的区别是什么？为什么 MySQL 不建议使用 NULL 作为列默认值？

''不占用空间，null是需要占用空间的

null代表一个不确定的值。两个null不一定相等。例如select null = null结果为false，但是我们使用distinct group by order by时，null又被认为是相等的

null会影响到聚合函数的结果。sum avg min max会忽略null值

查询null值，必须使用is null或者is not null，而不能使用= ！= > <之类的运算符，而''是可以使用这些比较运算符的

Boolean类型如何表示？

mysql并没有专门的布尔类型，而是用tinyint(1)类型来表示布尔值。

tinyint(1)类型可以存储0或1，分别对应false或true

MySQL基础架构

以后来收你，先等着

MySQL支持哪些存储引擎？默认使用哪个？

MyISAM InnoDB 5.5.5 之前是使用前者作为默认引擎的，5.5.5 之后是使用后者作为默认引擎的

使用select version()来查看你的MySQL版本

使用show variables like '%storage_engine%'命令直接查看MySQL当前默认的存储引擎

MySQL 存储引擎架构了解吗？

以后来收你，先等着

MyISAM 和 InnoDB 有什么区别？

MyISAM 性能特别好 最大的缺陷是崩溃之后无法安全恢复

InnoDB 支持事务 行级锁

1. 是否支持行级锁 m 只有表级 i 有行和表级
2. 是否支持事务

i支持事务 实现了SQL定义的四个隔离标准 具有提交commit和回滚rollback事务的能力

innodb默认是使用的repeatable-read可重读隔离级别是可以解决幻读问题发生的（基于MVCC和Next-Key Lock）

3. 是否支持外键

m不支持 i支持

阿里开发手册禁止使用外键，一切外键概念必须在应用层解决。因为它不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库插入速度；

4. 是否支持数据库异常崩溃后的安全恢复

m不支持 i支持

i崩溃后，重新启动的时候保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于redo log

5. 是否支持MVCC

不了解

6. 索引实现不一样

都是用B+树作为索引结构的

非叶子节点存放的是索引，叶子节点放的是索引+数据

7. 性能有差别

i性能比m更强大

8. 数据缓存策略和机制实现不同

不了解

MyISAM 和 InnoDB 如何选择？

innodb 在学习高性能的时候，看到一张图片就是i性能是远远超过m的

而且innodb有这些特别好的特性，有什么理由不选择它呢

MYSQL索引

重点

MYSQL查询缓存

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。

MySQL 5.6 开始，查询缓存已默认禁用。MySQL 8.0 开始，已经不再支持查询缓存了

MYSQL日志

重点

MYSQL事务

何为事务，就是一组事情要全部执行，要么都不执行

何为数据库事务？

数据库事务有什么用呢？要么全部执行成功，要么全部不执行

关系型数据库事务都有ACID特性：

• A atomicity 原子性 - 要么全部执行，要么全部不执行
• C consistency 一致性 - 执行事务前后，数据保证发生前后一致
• I isolation 隔离性 - 并发访问时，一个事务不会被其他事务所打扰
• D durability 持久性 - 一个事务被提交后，它对数据库中的数据改变是持久的

只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

并发事务带来了哪些问题?

脏读（Dirty read）

第一个事务读取数据并对数据进行了修改，但是这个修改对其他事务来说是可见的。

此时第二个事务读取了这个还未提交的数据，但是第一个事务突然回滚。

此时第二个事务读取的数据就是脏数据。

丢失修改（Lost to modify）

第一个事务读取数据，第二个事务也读取了数据，然后第一个事务修改了数据，第二事务也修改了数据。

然后事务都进行了提交，那么第一次的修改就会被第二次的修改所覆盖，此时就发生了丢失修改。

不可重复读（Unrepeatable read）

一个事务多次读取同一个数据。

一个事务访问该数据获取到了数据，然后另一个事务也访问该数据并对这个数据进行修改，此时第一个事务又读取了一次这个数据，结果就发生了两次读取不一样的情况，就发生了不可重复读。

幻读（Phantom read）

与不可重复读类似。

一个事务读取了多行的数据，接着另一个事务插入了一些数据，然后第一个事务又一次读取了这个数据，事务读取这个范围的数据发现相比与第一次读取的结果多了新的数据

不可重复读和幻读有什么区别？

• 不可重复读 内容修改或者记录减少，发现查到的某些记录值被修改了
• 幻读 记录新增 多次执行同一条查询语句,发现查到的记录增多了

并发事务的控制方式有哪些？

锁 + MVCC 重点 以后来了解

SQL标准定义了哪些事务隔离级别？

• read-uncommitted 读取未提交 允许读取尚未提交的数据变更 脏读 不可重复读 幻读 最低的隔离级别
• read-committed 读取已提交 允许读取并发事务已经提交的数据 不可重复读 幻读
• repeatable-read 可重复读 对同一字段的多次读取结果都是一致的 幻读
• serializable 可串行化 最高的隔离级别，完全服从 ACID 的隔离级别

MySQL 的隔离级别是基于锁实现的吗？

是基于锁和MVCC机制共同实现的

MySQL 的默认隔离级别是什么?

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

MYSQL锁

重点

MYSQL性能优化

能用 MySQL 直接存储文件（比如图片）吗？

不要在数据库中存储文件，会严重影响数据库性能，消耗过多的存储空间

数据库只存储文件地址信息，文件由文件存储服务负责存储。

优先选择存储文件路径或 URL 的方式，以提高性能和可维护性。

MySQL 如何存储 IP 地址？

• inet_aton：将ip转为无符号整型（4-8位）
• inet_ntoa：将整型的ip转为地址

插入数据前，先用inet_aton把ip地址转为整型，显示数据时，使用inet_ntoa将整型的ip转换未地址显示就行

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address INT UNSIGNED
);

-- 插入数据
INSERT INTO example (ip_address) VALUES (INET_ATON('192.168.1.1'));

-- 查询数据
SELECT id, INET_NTOA(ip_address) AS ip_address FROM example;

有哪些常见的 SQL 优化手段？

使用Explain进行分析

使用explain来分析select查询语句，我们开发人员就是可以用select结果来优化查询语句

• select_type：查询类型，有简单查询、联合查询、子查询等
• key：使用的索引
• rows：扫描的行数

SQL优化

1. 避免使用Select *

• 增加查询解析器的成本
• 不走覆盖索引会产生大量的回表查询
• 查询不需要的字段会浪费CPU、内存资源

2. 小表驱动大表

学生表 20条数据 ， 分数表 80w条数据

left join 小表驱动大表

3. 用连接查询代替子查询

4. 提升group by的效率

• 对进行排序的字段添加索引

5. 批量操作

• 1w+数据进行批量插入

6. 使用Limit

• 提高查询效率
• 避免过度提取数据
• 优化分页查询
• 简化查询效果

7. 使用union all代替union

• union all 获取所有数据但是数据不去重，包含重复数据
• union 获取所有数据且数据去重，不包含重复数据 union去重会消耗性能和资源

8. join的表不宜过多

• 查询效率会下降
• 系统负载增加
• 维护难度加大

SQL遵循的原则

1. 减少数据扫描
2. 返回更少数据
3. 减少交互次数
4. 减少服务器CPU及内存开销

如何分析 SQL 的性能？

使用explain命令来分析SQL的执行计划

执行计划就是指一条SQL语句经过mysql查询优化器优化后，具体的执行方式

type：表的访问方法

• system
• const：表中最多只有一行匹配的记录，一次查询就可以找到
• eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。
• ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
• index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
• range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
• index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
• ALL：全表扫描

key：实际用到的索引

• key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

extra：这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。

• Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
• Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
• Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
• Using index condition:表示查询优化器选择使用了索引条件下推这个特性。
• Using where:表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
• Using join buffer (Block Nested Loop):连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

这是explain命令分析性能最重要的三个参数

读写分离和分库分表了解吗？

重点

深度分页如何优化？

重点

数据冷热分离如何做？

重点

MySQL 性能怎么优化？

点 - 线 - 面

现阶段最重要的是前两个去实践一下

1. 慢SQL定位与分析

• 监控工具：mysql慢查询日志
• explain命令

2. 由点及面：索引、表结构和SQL优化

• 索引优化
• 表结构优化
• SQL 优化

3. 进阶方案：架构优化

• 读写分离
• 分库分表
• 数据冷热分离
• 缓存机制

4. 其他优化手段

• 连接池配置
• 硬件配置