荐秋招准备之——MySQL复习

程序员文章站 2022-04-15 16:59:38

秋招复习笔记系列目录（不断更新中）：1.数据结构全系列2.计算机网络知识整理（一）3.计算机网络知识整理（二）4. Java虚拟机知识整理5.计算机操作系统6.深入理解HashMap7.深入理解ConcurrentHashMap8.MySQL继续来分享自己的整理的MySQL的知识。本篇博客主要是自己在看《高性能MySQL》的过程中做的笔记，其中夹杂着一些对一些知识的深入的解释，这些解释大多是从别人的博客中搬运过来综合整理后得到的，这部分内容的来源会在文中标注出来，建议大家支持原作者...

秋招复习笔记系列目录（不断更新中）：

继续来分享自己的整理的MySQL的知识。本篇博客主要是自己在看《高性能MySQL》的过程中做的笔记，其中夹杂着一些对一些知识的深入的解释，这些解释大多是从别人的博客中搬运过来综合整理后得到的，这部分内容的来源会在文中标注出来，建议大家支持原作者，我这里搬运过来主要是为了以后看着方便不用在网页间跳来跳去。

一、MySQL的逻辑架构

1.1 逻辑架构

MySQL的逻辑架构分为三层：

① 第一层主要负责连接管理和安全性：每个客户端连接在服务器中都拥有一个线程；当客户端连接到MySQL服务器时，服务器会对其进行用户名密码、证书、权限等的一系列认证
② 第二层包含所有跨存储引擎的功能，以及缓存查询、分析器、优化器：查询时，MySQL会解析查询，创建解析树，然后对其进行各种优化。同时，对SELECT语句，解析查询前服务器会先检查缓存（要查询语句完全一致，所以命中率不高）。
③ 第三层为存储引擎层

1.2 并发控制

1.读写锁

读锁是共享的，互不阻塞，多个用户在同一时刻可以同时读取某个资源而互不干扰。写锁是排他的，写锁会阻塞其他的写锁和读锁。

2.锁粒度

为了考虑并发性能，需要综合锁的开销和数据的安全性。MySQL有多种锁粒度的选择：

表锁： 锁住整张表，开销最小，并发性最差，适合只读
行锁： 可以最大程度支持并发处理，但锁的开销和很高

荐
秋招准备之——MySQL复习

3.显式和隐式锁定

在存储引擎中，会根据隔离级别，自动地进行隐式锁定，但是也可以设置显式锁定（第一行是共享锁，第二行是排它锁）：
荐
秋招准备之——MySQL复习
上面的两个语句是在事务内起作用的，所涉及的概念是行锁。它们能够保证当前session事务所锁定的行不会被其他session所修改(这里的修改指更新或者删除)。两个语句不同的是，一个是加了共享锁而另外一个是加了排它锁.

4. 间隙锁

荐
秋招准备之——MySQL复习
示例：

荐
秋招准备之——MySQL复习

为了防止间隙锁，锁的力度应该尽可能小，选择时要尽可能精细选择。

1.3 多版本并发控制（摘抄于这篇博客）

1. redo log 和 undo log

(1) redo log

对数据的修改操作会先直接修改内存中的 Page，但这些页不会立刻同步磁盘，这时内存中的数据已经和磁盘上的不一致了，我们称这种 Page 为脏页。为了保证数据的安全性，在修改内存中的 Page 之后 InnoDB 会写 redo log，然后，InnoDB 会在事务提交前将 redo log 保存到磁盘中。这里所说的 redo log 是物理日志而非逻辑日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页（恢复数据页，且只能恢复到最后一次提交的位置）。

(2) undo log

与 redo log 不同，undo log 一般是逻辑日志，根据每行记录进行记录。例如当 DELETE 一条记录时，undo log 中会记录一条对应的 INSERT 记录，反之亦然当 UPDTAE 一条记录时，它记录一条对应反向 UPDATE 记录。通过 undo log 一方面可以实现事务回滚，另一方面可以根据 undo log 回溯到某个特定的版本的数据，实现 MVCC 的功能。

redo log 由两部分组成，一部分是内存中的 redo log buffer，这部分是易失的，重启就没了；二是磁盘上的 redo log file，是持久化的。

InnoDB 通过 force log at commit 技术来实现事务的持久化特性。为了保证每次 redo log 都能写入磁盘上的日志文件中，每次将内存中的 redo log buffer 内容同步磁盘时都会调用一次 fsync。

2. 实现机制

InnoDB在每行数据都增加三个隐藏字段，一个唯一行号，一个记录创建的版本号，一个记录回滚的版本号：

荐
秋招准备之——MySQL复习

其中：

DB_TRX_ID：用来标识用来标识最近一次对本行记录做修改(insert|update)的事务的标识符，即最后一次修改(insert|update)本行记录的事务id。至于delete操作，在innodb看来也不过是一次update操作，更新行中的一个特殊位将行表示为deleted，并非真正删除。
DB_ROLL_PTR：表示指向该行回滚段（rollback segment）的指针，大小为 7 个字节，InnoDB 便是通过这个指针找到之前版本的数据。该行记录上所有旧版本，在 undo 中都通过链表的形式组织。
DB_ROW_ID：包含一个随着新行插入而单调递增的行ID，当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。

上文提到，在多个事务并行操作某行数据的情况下，不同事务对该行数据的 UPDATE 会产生多个版本，然后通过回滚指针组织成一条 Undo Log 链。事务 A 对值 x 进行更新之后，该行即产生一个新版本和旧版本。假设之前插入该行的事务 ID 为 100，事务 A 的 ID 为 200，该行的隐藏主键为 1。

事务 A 的操作过程为：

对 DB_ROW_ID = 1 的这行记录加排他锁
把该行原本的值拷贝到 undo log 中，DB_TRX_ID 和 DB_ROLL_PTR 都不动
修改该行的值这时产生一个新版本，更新 DATA_TRX_ID 为修改记录的事务 ID，将 DATA_ROLL_PTR 指向刚刚拷贝到 undo log 链中的旧版本记录，这样就能通过 DB_ROLL_PTR 找到这条记录的历史版本。如果对同一行记录执行连续的 UPDATE，Undo Log 会组成一个链表，遍历这个链表可以看到这条记录的变迁
记录 redo log，包括 undo log 中的修改

那么 INSERT 和 DELETE 会怎么做呢？其实相比 UPDATE 这二者很简单，INSERT 会产生一条新纪录，它的 DATA_TRX_ID 为当前插入记录的事务 ID；DELETE 某条记录时可看成是一种特殊的 UPDATE，其实是软删，真正执行删除操作会在 commit 时，DATA_TRX_ID 则记录下删除该记录的事务 ID。

3. 如何实现一致性读——ReadView

在 RU 隔离级别下，直接读取版本的最新记录就 OK，对于 SERIALIZABLE 隔离级别，则是通过加锁互斥来访问数据，因此不需要 MVCC 的帮助。因此 MVCC 运行在 RC 和 RR这两个隔离级别下，当 InnoDB 隔离级别设置为二者其一时，在 SELECT 数据时就会用到版本链。InnoDB 为了解决这个问题，设计了 ReadView（可读视图）的概念。ReadView是事务开启时，当前所有事务的一个集合（理解这句话），这个数据结构中存储了当前ReadView中最大的ID及最小的ID

(1) RR下ReadView的生成

在 RR 隔离级别下，每个事务 touch first read 时（本质上就是执行第一个 SELECT语句时，后续所有的 SELECT 都是复用这个 ReadView，其它 update, delete, insert 语句和一致性读 snapshot 的建立没有关系），会将当前系统中的所有的活跃事务拷贝到一个列表生成ReadView。

下图中事务 A 第一条 SELECT 语句在事务 B 更新数据前，因此生成的 ReadView 在事务 A 过程中不发生变化，即使事务 B 在事务 A 之前提交，但是事务 A 第二条查询语句依旧无法读到事务 B 的修改。

荐
秋招准备之——MySQL复习
下图中，事务 A 的第一条 SELECT 语句在事务 B 的修改提交之后，因此可以读到事务 B的修改。但是注意，如果事务 A 的第一条 SELECT 语句查询时，事务 B 还未提交，那么事务 A 也查不到事务 B 的修改
荐
秋招准备之——MySQL复习

(2) RC下ReadView的生成

在 RC 隔离级别下，每个 SELECT 语句开始时，都会重新将当前系统中的所有的活跃事务拷贝到一个列表生成 ReadView。二者的区别就在于生成 ReadView 的时间点不同，一个是事务之后第一个 SELECT 语句开始、一个是事务中每条 SELECT 语句开始。

ReadView 中是当前活跃的事务 ID 列表，称之为 m_ids，其中最小值为 up_limit_id，最大值为 low_limit_id，事务 ID 是事务开启时 InnoDB 分配的，其大小决定了事务开启的先后顺序，因此我们可以通过 ID 的大小关系来决定版本记录的可见性，具体判断流程如下：

如果被访问版本的 trx_id 小于 m_ids 中的最小值 up_limit_id，说明生成该版本的事务在 ReadView 生成前就已经提交了，所以该版本可以被当前事务访问。
如果被访问版本的 trx_id 大于 m_ids 列表中的最大值 low_limit_id，说明生成该版本的事务在生成 ReadView 后才生成，所以该版本不可以被当前事务访问。需要根据 Undo Log 链找到前一个版本，然后根据该版本的 DB_TRX_ID 重新判断可见性。
如果被访问版本的 trx_id 属性值在 m_ids 列表中最大值和最小值之间（包含），那就需要判断一下 trx_id 的值是不是在 m_ids 列表中。如果在，说明创建 ReadView 时生成该版本所属事务还是活跃的，因此该版本不可以被访问，需要查找 Undo Log 链得到上一个版本，然后根据该版本的 DB_TRX_ID 再从头计算一次可见性；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。
此时经过一系列判断我们已经得到了这条记录相对 ReadView 来说的可见结果。此时，如果这条记录的 delete_flag 为 true，说明这条记录已被删除，不返回。否则说明此记录可以安全返回给客户端。

荐
秋招准备之——MySQL复习

1.4 事务

1. ACID

良好事务处理系统，必须具备ACID特征：

原子性： 事务是不可分割的最小工作单元，一个事务要么成功，要么失败
一致性： 数据库总是从一个一致状态转到另一个一致状态。
隔离性： 一个事务中的操作在提交前在另一个事务中不可见
持久性： 一旦事务提交，其所作的更改就会永久保存到数据库中

2. 并发带来的问题

脏读： 当一个事务允许读取另一个事务修改但未提交的数据时，就可能发生脏读。
不可重复读： 一个事务读取某一记录后，该数据被另一个事务修改提交，再次读取该记录时结果发生了改变
幻读： 一个事务第一次读取数据后，另一个事务增加或者删除了某些数据，再次读取时结果的数量发生了变化。
更新丢失： 第一类：当两个事务更新相同的数据时，如果第一个事务被提交，然后第二个事务被撤销；那么第一个事务的更新也会被撤销。第二类：当两个事务同时读取某一记录，然后分别进行修改提交；就会造成先提交的事务的修改丢失。

3. 事务的隔离级别

读未提交： 级别最低，不能避免脏读
读提交： 能避免脏读，不能避免不可重复读
可重复读： 能避免不可重复读，但不能避免幻读，InnoDB通过间隙所来防止幻读
串行化： 能避免幻读

荐
秋招准备之——MySQL复习

4. 死锁

指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，谁都不释放，导致恶性循环。解决这种问题的方式，一种是死锁检测，另一个是死锁超时。
荐
秋招准备之——MySQL复习

1.5 存储引擎

1. InnoDB引擎

数据存储在表空间tablespace中，表空间中又分为Segment、Extent、page
支持事务，通过MVCC来实现乐观锁，通过行锁实现悲观锁，间隙锁防止部分幻读
基于聚簇索引建立，聚簇索引对主键的查询有很高的性能，但是二级索引必须包含主键，查询二级索引后，需要回表去聚簇索引中查

2. MyISAM

不支持事务和行级锁，只支持表锁
MyISAM将表存在两个文件中：数据文件（扩展名.MYD）和索引文件（扩展名.MYI），表可存储的行记录数，一般受限于可用的磁盘空间或者操作系统中单个文件的最大尺寸
支持全文索引

荐
秋招准备之——MySQL复习

二、Schema与数据类型优化

2.1 数据类型优化

更小的通常更好： 使用可以正确存储数据的最小数据类型

MySQL中的数据类型可以分为：整型（tinyint、smallint、mediumint、bigint、int）、浮点型（double、float、DECIMAL）、字符串（char和varchar）、日期（date、time、datetime、timestamp（自动存储记录修改时间））、二进制几个大类。
简单就好： 尽量用简单的数据类型，能用整型的就不用字符串
尽量避免NULL： 最好设置列值为NOT NULL，除非真的需要NULL值
使用枚举代替字符串类型： 枚举可以防止重复的存储相同的字符串

2.2 范式和反范式

1.三范式

①第一范式： 每一个列都是原子的，不可再分
②第二范式： 没有包含在主键中的列必须全部依赖于全部主键，而不能只依赖于主键的一部分而不依赖全部主键
③第三范式： 不能存在传递依赖，即不能存在：非主键列m既依赖于全部主键，又依赖于非主键列n的情况

2. 范式的优缺点

优点： 更新操作通常更快、冗余数据少
缺点： 通常需要大量的表关联，代价太大

3. 反范式的优缺点

优点： 一定程度上避免关联
缺点： 数据冗余

4. 混合使用范式和反范式

为了提高查询效率，不一定所有的表要满足三范式，可以适当混用范式和反范式。最常见的反范式数据的方法是复制或缓存，在不同的表中存储已有的特定列。可以使用触发器，定期更新缓存值

2.3 缓存表和汇总表

缓存表： 用于存储那些可以比较简单的从schema其他表获取数据，但是每次获取的速度比较慢的表
汇总表： 保存的是使用Group by语句聚合数据的表，如要统计网站一天的访问量，可以每个小时统计一次，最后对这些汇总表再统计

1.物化视图

视图和物化视图的区别：
- 视图： 仅仅相当于一个虚拟表，本省不存储数据，当sql在操作视图时所有数据都是从其他表中查询出来的，其主要作用是简化设计，清晰编码的，并不是提高性能的。
- 物化视图： 物化视图，它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，从而快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL 语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。通常通过结合触发器实现。

2. 计数器表

在遇到计数的需求时，一种解决方案是，建立一个表，里面有一行数据，然后每次都更新这一行数据，但是这种在高并发情况下，很容易导致阻塞，解决方法是，在表中插入固定行数的数据，每次更新时，随机挑去一行更新，要计算总数的时候，计算sum即可。

三、创建高性能的索引

3.1 索引基础

1. 索引类型

1.B-Tree索引

特点：
- MyISAM使用B树索引，InnoDB使用B+树索引
- 使用B树索引，可以用于全值匹配（查询列和索引列一样）、最左前缀匹配（从左开始匹配，匹配最左边的列）、匹配列前缀（只匹配某一列的值的开头部分）、匹配范围值（因为B树其实是带排序的，所以用于匹配范围值也会很方便），精确匹配左边列并范围匹配另一个列、只访问索引的查询（覆盖索引）。
- 索引还可用于查询中的Order By，因为索引本身有序
限制：
- 如果不是按照索引的最左列开始查找，则无法使用索引
- 不能跳过索引中的列
- 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询
为什么使用B树索引：
- 磁盘存取数据的原理
  
  如上所示，磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元 。
  
  每个扇区又被划分成相同大小的小块，称为页。在读取数据时，会预读（读页的整数倍放到内存中）。
- 为什么使用B树
  
  数据库存储巧妙利用磁盘预读原理，让B树的每个Node的大小等于一个页的大小，在分配node 的时候也会分配给node一页的大小。这样一个node只需要一次I/O。
2. 哈希索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，只有Memory引擎支持哈希索引，其通过拉链法实现哈希表

限制：
- 哈希索引只包含哈希值和行指针，不能避免回表
- 无法排序
- 不支持部分索引匹配查找
- 只支持等值比较
- 如果哈希冲突很多，索引的维护代价会很高
自己实现哈希

可以借鉴哈希的思想，在表中生成一列，作为哈希列，通过自定义的哈希函数来计算哈希值。查询时，可以先通过哈希值来过滤。自定义哈希值需要触发器配合来更新哈希。

2.索引的作用

大大减少服务器需要扫描的数据量
可以帮助服务器避免排序和临时表
将随机I/O变成顺序I/O

3.2 高性能索引策略

1.独立的列

始终将索引列单独放在比较符号的一侧。如下面的查询就不能用到索引：

荐
秋招准备之——MySQL复习

2.前缀索引和索引选择性

1.是否要为所有的查询建立索引： 不是。一方面，索引要占用额外的存储空间（在InnoDB中，如果主键很长，那索引多了占用的存储空间将非常大）。另一方面，由于索引需要维护，所以过多的索引会造成插入删除修改等操作性能的降低。
2. 不建议使用索引的两种情况：
- ①表记录比较少（小于2000）,不必建索引，直接全表扫描速度更快
- ②索引的选择性比较低，即字段中存在大量重复的值
3. 前缀索引

当索引很长时，可以选择字段开始的部分字符串，节约空间。在选择前缀的长度时，既不能太长，又要保证较高的选择性。有两种做法：
- 可以先统计出最常见的出现的值，然后再不断去尝试取不同长度的前缀索引，看前缀索引取多长的时候，能和最常见的出现的值大致匹配。
- 首先计算全表的选择性，然后取不同长度的前缀，看选择性什么时候接近全表的选择性

3.多列索引

下面情况下，MySQL会进行索引合并

对多个索引做相交操作时（有多个AND条件）时，通常意味需要一个包含所有相关列的多列索引
对多个索引做联合操作时（有多个OR条件），有时候不如写成UNION语句

4.选择合适的索引列顺序

应该遵循一个原则，越前面的索引，应该越能大量的过滤不符合条件的数据

当不需要考虑排序和分组时，将选择性最高的列放在最前面
数据量较少的选择性较高的放在前面，这样就能很快的过滤出符合条件的列

5.聚簇索引

使用聚簇索引时，数据行存放在索引的叶子页。一个表只能有一个聚簇索引，InnoDB通过主键聚集数据。

荐
秋招准备之——MySQL复习

优点：

①将相关的数据保存在一起，如电子邮件数据库，根据用户ID聚集，就能将同一用户的全部邮件聚集在一起

②数据访问更快

③使用覆盖索引扫描的查询可以直接使用页节点的中的主键值

缺点：

①插入速度严重依赖插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。如果不是按照主键顺序加载数据，那在加载完数据后最好使用OPTIMIZE TABLE命令重新组织表

②更新聚簇索引列的代价很高，会强制将每个被更新的行移动到新的位置

③基于聚簇索引的表在插入新行，或者主键被更新导致要移动行的时候，可能导致页分裂，会占用表占用更多的磁盘空间

④如果主键很长，二级索引会非常大。

⑤二级索引访问需要两次索引查找

6.主键的选择和插入优化

选择主键时，最好选择一个与业务无关的自增整数作为主键。原因有以下两点：

1.占用空间小： 因为辅助索引的data部分存储的是主键索引，所以主键一定不能太长。使用自增整数满足这个条件。
2.数据在磁盘中不会太乱： InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

荐
秋招准备之——MySQL复习

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：
荐
秋招准备之——MySQL复习

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，*-后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

7. 覆盖索引

可以直接通过索引获取需要查询的数据，而不需要回表，能极大提高性能。有时候，当查询语句无法使用到覆盖索引，可以使用延迟关联的方式，来进行查询。

延迟关联：可以先利用覆盖索引，将符合条件的主键全都查询出来，再统一利用主键去join主表。

9. 使用索引扫描来做排序

只有当索引的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查找的限制是一样的：需要满足索引的最左前缀的要求，否则无法利用索引排序。

10. 索引和锁

使用InnoDB，只有在访问行时才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少行的数量。但是这种情况下，如果索引无法过滤掉无效的行，会导致间隙锁，并且只有在事务提交后才能释放锁。如下面的查询语句：
荐
秋招准备之——MySQL复习

这条查询仅仅会返回2~4之间的行，但是实际上获取了1-4之间的排它锁，会锁住第一行，因为是通过actor_id<5来获取，没有排除第一行，所以会锁住所有actor_id小于5的行。

11.最左前缀原理与相关优化

情况1——全列匹配： 当按照索引中的所有列进行精确匹配（=或in）时，索引可以被用到。其次，由于MySQL的优化，这种情况下，where中条件的顺序其实可以可以颠倒，MySQL会对这种情况下的条件进行重排，使其和索引顺序一致。
情况2——最左前缀匹配： 当索引由多个字段组成，但查找时，只用到多个字段中最左边的前几个字段。这种情况下，会进行最左前缀匹配的索引。
情况3——查询条件用到了索引中列的精确匹配，但是中间某个条件未提供： 比如索引是(col1,col2,col3)，查询的where条件是col1='value' and col2='value2'，这样只能用到col1的索引，不会用到col3的索引。
情况4——查询条件没有指定索引第一列： 由于不是最左前缀，用不到索引。
情况5——范围查询： 在where中使用>、<等范围条件，范围列能用到索引， 但是范围列后面的列无法用到索引。
情况7——查询条件中含有函数或表达式： 使用函数或表达式，不会建立索引。

12. 索引失效的一些情况

荐
秋招准备之——MySQL复习

除了上面的一些情况，使用 !、<>、is null、is not null也会导致索引失效。

3.3 MyISAM和InnoDB索引的区别

MyISAM使用的是B树索引，InnDB使用的是B+树索引，其表的表示方式如下所示：

荐
秋招准备之——MySQL复习

注意： 在InnoDB中，主键最好是一个int类型的自增数字，因为占用空间小，插入时不需要调整索引结构

3.4 全文索引（摘抄自这篇博客）

1 定义方式

建表的时候

create table fulltext_test (
    id int(11) NOT NULL AUTO_INCREMENT,
    content text NOT NULL,
    tag varchar(255),
    PRIMARY KEY (id),
    FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

通过create增加

create fulltext index content_tag_fulltext
    on fulltext_test(content,tag);

通过alter增加

alter table fulltext_test
    add fulltext index content_tag_fulltext(content,tag);

2.使用方式

不同于like ,全文索引有自己的语法，需要用match和against 关键字

select * from fulltext_test 
    where match(content,tag) against('xxx xxx');

注意： match() 函数中指定的列必须和全文索引中指定的列完全相同，否则就会报错，无法使用全文索引，这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引，请单独为该列创建全文索引。

3. 最小搜索长度

MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。

4.两种全文索引

(1) 自然语言索引

自然语言搜索引擎的为默认搜索引擎

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。上面提到的，测试表中必须有 4 条以上的记录，就是这个原因。

这个机制也比较好理解，比如说，一个数据表存储的是一篇篇的文章，文章中的常见词、语气词等等，出现的肯定比较多，搜索这些词语就没什么意义了，需要搜索的是那些文章中有特殊意义的词，这样才能把文章区分开。

(2) 布尔全文索引

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

MySQL 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册

必须包含该词
- 必须不包含该词
> 提高该词的相关性，查询的结果靠前
< 降低该词的相关性，查询的结果靠后
(*)星号 通配符，只能接在词后面

布尔全文索引举例如下：

3.5 创建高性能索引的一些建议

如果一个列只有有限的几个值（如性别），可以将其放在索引前列用于过滤数据，查询时，加上这个字段的查询即可
经常会用到范围查询的字段，放在索引的后面，防止索引被中断，让查询用到尽可能多的索引列
避免多个范围条件
优化排序：这里涉及到一个limit优化的问题，主要的优化手段有以下几种：
- ① （推荐）使用延迟关联的方法，先通过覆盖索引将数据查出来，最后再去查询，举例如下：
```
select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)
```
- ②给表增加一个自增索引，直接告诉查询从哪里开始数

四、查询性能优化

4.1 优化数据访问

1.避免向数据库请求不需要的数据

在请求数据的时候，只请求需要的数据，主要有以下情况：

①查询不需要的记录：只需要10行，但将查询的结果全部返回，让前端去处理。这种情况下，使用limit来限制查询结果
②返回全部列：避免使用select *，特别是在多表关联时
③重复查询相同的数据：使用缓存避免重复查询

2.避免扫描额外的数据

通过explain中的rows属性，可大致知道需要扫描的行数，这个数字越小越好。有以下常见的手段：

使用覆盖索引扫描，这样就无需回表就能查询
改变库表结构，例如使用单独的汇总表
增加索引
重构查询语句

4.2 重构查询方式

重构查询方式，有以下策略

1. 用多个简单查询代替复杂查询 ：MySQL的连接和断开连接都比较轻量级，所以可以考虑将一个复杂查询拆分成多个简单查询。
2. 切分查询：对于一个大查询，可以分而治之，将其分为几个小查询。如在删除数据时，可能会一下锁住很多数据，但将切分为多个小查询，会极大提升性能。
3. 分解关联查询：将多个关联查询分解成一个关联查询，有以下好处：
- ① 缓存效率更高
- ②分解后，会减少锁竞争
- ③减少冗余记录的查询

4.3 查询执行的基础

1. MySQL客户端/服务端通信协议

通信是半双工的，同一时间要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，两个动作不能同时发生。

2.查询缓存

在解析查询语句之前，如果查询缓存打开，那先去查询是否这个查询是否命中缓存。如果命中缓存，会跳过所有其他阶段，直接从缓存中取结果返回给客户端。缓存是否命中的查询，是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询，即使只有一个字节不同，也不会匹配缓存结果。

3. 查询优化处理

MySQL的排序： 如果需要排序的数据量小于"排序缓冲区"，MySQL直接使用快速排序进行排序。否则，先将数据分块，对每个独立的块使用快速排序，然后将排好序的块放在磁盘上，再利用归并排序进行合并。MySQ有两种排序算法：
- ①两次传输排序（旧版本使用）： 读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取需要的数据行。这种方式需要两次数据传输。
- ②单次传输排序（新版本使用）： 先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。
优化的细节： MySQL会对查询进行一系列的优化，包括重新定义关联表的顺序，将外连接转换成内连接等等。

4. 查询优化器的局限性

(1) 关联子查询

当WHERE条件中包含IN()的子查询时，MySQL会将外边的表压入子查询中进行查询。如

荐
秋招准备之——MySQL复习

会被解析成：

荐
秋招准备之——MySQL复习

(2) UNION限制

有时MySQL无法将限制条件从外层"下推"到内层，这会使原本能限制部分返回结果的条件无法应用到内存查询的优化上，如：

荐
秋招准备之——MySQL复习

这里的LIMIT 20只有在所有数据都查出来以后，才会限制。可以改写成这样：

荐
秋招准备之——MySQL复习

(3) 最大值最小值优化

对于MAX()和MIN()查询，MySQL优化的并不好，如下面的查询，会进行一个全表扫描：

荐
秋招准备之——MySQL复习

可以尝试使用LIMIT来优化

荐
秋招准备之——MySQL复习

5.优化特定类型的查询

COUNT： 建议直接使用COUNT(*)来统计行数
关联查询的优化： 需要做到以下几点：
- ①确保ON或者USING子句中的列上有索引
- ②确保GROUP BY和ORDER BY中的表达式只涉及一个表中的列
优化GROUP BY：
- ①优化GROUP BY：尽量对索引列使用Group BY
- ②如果不能直接对索引列进行GROUP BY，那可以先对索引进行GROUP BY，然后再内连接，例子如下：
  
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ov2IwzDF-1594904003497)(高性能MySQL.assets/image-20200701201037139.png)]
优化LIMIT分页： 尽可能用覆盖索引的延迟关联，然后再通过关联实现

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7HkW9wCf-1594904003498)(高性能MySQL.assets/image-20200701201237535.png)]

也可以通过在WHERE中增加限制条件来实现：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bM9PTr3b-1594904003499)(高性能MySQL.assets/image-20200701201427866.png)]

优化UNION查询： 由于MySQL总是通过创建并填充临时表方式来执行UNION，因此很多优化策略在UNION查询中没法很好的使用，经常需要手工地将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中，以便优化器可以充分利用这些条件优化。

4.4 使用Explain进行分析（摘抄自这篇博客）

Explain查询结果的各字段的意义：

荐
秋招准备之——MySQL复习

id： 查询的id，遵循以下规则
- ①id相同，执行顺序从上到下
- ②id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
select_type： 常见的值有以下几种

分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
- SIMPLE 简单的select查询，查询中不包含子查询或者UNION
- PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
- SUBQUERY 在SELECT或WHERE列表中包含了子查询
- DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
- UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
- UNION RESULT 从UNION表获取结果的SELECT
table：表示当前执行的表
type： type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种：

这些type，从最好到最差依次是：

system > const > eq_ref > ref > range > index > all

一般来说，得保证查询至少达到range级别，最好能达到ref。
- system： 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
- const： 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
- eq_ref： 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
- ref： 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
- range： 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
- index： Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
- All： 全表扫描，将遍历全表以找到匹配的行
possible_keys 和 key
- possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
- 查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中
key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref： 显示索引的哪一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值
rows： 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好
Extra
- Using filesort： 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
- Using temporary： 使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
- Using index： 表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
- Using where： 表明在使用了索引后，需要回表去查询（即先通过辅助索引查找数据，查找到后，根据辅助索引中的主键再去表中查找）
- Using join buffer： 表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
- impossible where： where子句的值总是false，不能用来获取任何元组
- distinct： 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

五、MySQL的高级特性

5.1 分区表

1.原理

分区表就是把一张表分开，对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。分区表的每一个分区都是有索引的独立表。

2.分区表发挥作用的场景

（1）表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。

（2）分区表的数据更容易维护。

（3）分区表的数据可以分布在不同的物理设备上。

（4）可以使用分区表来避免某些特殊的瓶颈，例如InnoDB单个索引的互斥访问。

（5）如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

3. 分区示例

①range分区：需要基于连续的范围值
②List分区： 需要基于确定值的范围
③Hash分区： hash分区指的是根据hash运算的模，最终确定在哪一个分区。比如2020/4=0，就落在分区0上
④线性Hash分区： 线性hash指的是使用2的幂运算法则。运算起来比较麻烦。但是优点是可以使得数据分布均匀。举个例子。假设分区个数num=6，N表示数据最终存储的分区：
- 第一步：第一步：V = power(2, ceiling(log(2, num)))，log是计算NUM以2为底的对数，ceiling()向上取整，power()是取2的次方值；
- 第二步：N=values&(V-1)，&位与运算，
- 第三步：while N>=num，此时N =N & (CEIL(V/ 2) - 1)
  
  比如插入2020-01-20，V=8，N=（2020）& （8-1）=4。4<6，所以保存在分区4。
  
  需要将by hash换成by linear hash
⑤根据key分区：根据键值进行分区，以减少InnoDB的互斥竞争

5.2 视图

视图本身是一个虚拟表，不存放任何数据，在使用SQL语句访问视图的时候，返回的数据是MySQL从其他表中生成的。与表不同的是，视图不能创建触发器。

在基于视图查询的时候，会有两种方法，一种是合并算法，一种是临时表算法。

荐
秋招准备之——MySQL复习

**可更新的视图：**指可以通过这个视图来更新视图涉及的相关表。只要指定了合适的条件，就可更新、删除、甚至向视图中写入数据，如下所示
视图的限制： 无法创建触发器、不支持物化视图、无法创建索引

5.3 外键约束

InnoDB是唯一支持外键约束的存储引擎，其优缺点如下：

优点
- 如果想确保两个相关表始终有一致性，使用外键约束要比在应用中检查一致性的性能高很多
- 外键在相关数据的删除和更新上，也比在应用中维护要高效
缺点
- 每次修改数据都要在另一张表中多执行一次查找操作，虽然强制外键使用索引，但仍然无法消除约束检查的开销
- 访问别的表的时候，需要额外的锁，也需要额外的性能

5.4 存储过程、函数、触发器（摘抄自这篇博客）

1.存储过程

语法： CREATE PROCEDURE 创建的存储过程名字(OUT|IN|INOUT 参数名数据类型,...,...) 特征过程体
使用：
- 列出存储过程的详细列表：show procedure status;
- 删除：drop procedure 要删除的存储过程的名字;
- 查看创建存储过程的信息：show create procedure 创建的存储过程名字;
- 查看所有存储过程：show procedure status \G;
示例：

创建存储过程

调用存储过程

2. 函数

和存储过程相比，函数有返回值，且参数类型只能是in

语法： CREATE FUNCTION 创建的存储函数名字(参数名称参数类型,...,...) RETURNS 返回值得类型函数体;
使用：
- 调用存储函数：select 函数名字([参数]);//根据定义函数的形参，形参与实参保持一致
- 查看创建函数信息：show create function 函数名字 /G;
- 查看所有自定义函数：show function status \G;
删除存储函数：drop function 函数名字;
示例

创建函数

调用函数

3. 触发器

语法：

CREATE TRIGGER 创建的触发器名字 BEFFOR|AFTER  INSERT|UPDATE|DELETE

ON 表名字 FOR  触发器的执行间隔   触发器的SQL语句

荐
秋招准备之——MySQL复习

使用：
- 查看所有的触发器：show triggers \G;
- 删除触发器：drop trigger 触发器名字;
示例：

创建一个插入型触发器

在表中执行插入的时候，就会触发触发器，然后更新balance值

5.5 事件

事件类似于Linux中的定时任务，可以指定MySQL在某个时候执行一段SQL代码，或者每隔一个时间间隔执行一段SQL代码。通常将复杂的SQL都封装在存储过程中，然后再事件中调用。要使用首先要通过SET GLOBAL event_scheduler=1来开启定时器。

例子1：每分钟往表中插入一条数据

create event test.event_minute on schedule every  1 minute
do insert into events_list values('event_now', now());

例子2：从2017年的给定时间起，每过一年调用一次存储过程ClearHis(2)

CREATE  EVENT `e_ClearHis` ON SCHEDULE EVERY 1 YEAR STARTS '2017-03-07 02:00:00'
ON COMPLETION PRESERVE ENABLE 
DO call ClearHis(2);

5.6 绑定变量

当创建一个绑定变量 SQL 时，客户端会向服务器发送一个SQL语句的原型。服务器端收到这个SQL语句框架后，解析并存储这个SQL语句的部分执行计划，返回个客户端一个 SQL 语句处理句柄。以后每次执行这类查询，客户端都指定使用这个句柄。绑定变量的SQL，使用问号标记可以接受参数的位置，当真正需要执行具体查询的时候，则使用具体值来替代这些问号。例如，下面是一个绑定变量的SQL语句：

INSERT INTO tb1(col1, col2, col3) VALUES(?,?,?);

六、主从复制

主从复制解决的基本问题是让一台服务器的数据与其他服务器保持同步，一台主库的数据可以同步到多台备库上，备库本身也可以配置成为另外一台服务器的主库。主库和备库之间可以有多种不同的组合方式。MySQL有两种复制方式：①基于语句的复制②基于行的复制。其基本原理都是，通过在主库上记录二进制日志，在备库中重放日志的方式实现的。

6.1 主从复制的常见用途

数据分布： 将数据分布在不同服务器
负载均衡： 将读写操作分布到多个服务器上，实现对读密集型应用的优化
备份： 保证数据的安全性
高可用性和故障切换： 主库故障后，从库可以晋升为主库

6.2 主从复制的工作过程

1.复制的步骤

① 在主库上把数据更改记录到二进制（Binary Log）日志中
②备库将主库上的日志复制到自己的中继日志（Relay Log）中
③备库读取中继日志中的事件，将其重放到备库数据之上

2. 复制的原理

(1) 基于语句的复制

此模式下，主库会记录那些造成数据库更改的查询，当备库读取并重放这些时间时，实际上是将主库上执行过的SQL再执行一遍。

优点：

简单
二进制日志的时间更加紧凑，不会占用太多带宽
出现问题容易定位

缺点：

主库的数据更新除了执行的语句外，还可能依赖其他因素，如时间戳，当前用户等，这些在SQL语句中无法传递
更新必须是串行的，这需要更多的锁

(2) 基于行的复制

这种方式将实际数据记录在二进制日志中，然后直接替换行

优点：

正确复制每一行
能更高效地复制数据

缺点：

如果做全表更新，那更新的行会很多，二进制日志事件会非常庞大
会给主库记录日志和复制增加额外的负载，会降低并发度

6.3 主从复制拓扑

可以在任意主库和备库之间建立复制，只有一个限制：每个备库只能有一个主库。有以下常见的主从复制的拓扑：

1. 一主库多备库

只有一个主库，但有多个备库，多个备库之间没有交互。应用于少量写大量读的场景。有以下用途：

为不同的角色使用不同的备库
把一台备库当作代用的主库，除了复制没有其他数据传输
将一台备库放到远程数据中心，用于灾难恢复

2.主动-主动模式

主-主复制包含两台服务器，每一个都被配置成对方的主库和备库。常用于两个不同地理位置的办公室，都需要一份可写的数据拷贝的场景。这种配置最大的问题在于如何解决冲突
荐
秋招准备之——MySQL复习

如果为每个主库增加一个备库，能够消除单站点失效的问题：

荐
秋招准备之——MySQL复习

3. 主动-被动模式

是主-主模式的变体，解决了冲突的问题。其中，被动服务器是只读的。这种拓扑下，反复切换主动和被动服务器非常方便。可以在不关闭服务器的情况下执行维护、优化表、升级操作系统等任务

比如，在执行ALTER TABLE的时候，可能会锁住整张表，阻止对表的读写，在这种配置下，可以先停止主动服务器的备库复制线程，然后再被动服务器上执行ALTER TABLE操作，然后交换角色，最后在主动服务器上启动复制线程，更新数据。
荐
秋招准备之——MySQL复习

4.环形复制

环形结构可以有三个或更多的主库，每个服务器都是在它之前的服务器的备库，是在它之后的服务器的主库
荐
秋招准备之——MySQL复习

可以通过为每个节点增加备库的方式减少环形复制的风险。
荐
秋招准备之——MySQL复习

七、备份与恢复

备份方式有两种，第一种是逻辑备份，第二种是物理备份。

逻辑备份： 是SQL级别的备份机制，其将数据表导成SQL脚本文件，然后相当于在另一台服务器上执行一遍备份的SQL语句
- 操作语句： 使用mysqldump语句来实现，具体语句为：
```
mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql
```
- 优点： 恢复简单、与存储引擎无关，消除了底层数据存储的不同，有助于避免数据损坏
- 缺点： 必须有数据库完成逻辑工作，需要更多地CPU周期，且逻辑备份还原慢
物理备份： 是基于文件的物理备份，比较类似于拷贝数据库的文件，然后复制到另一台服务器加载
- 优点： 容易跨平台、跨操作系统和MySQL版本，且恢复起来很快
- 缺点： 文件比较大，不总是可以跨平台、操作系统和MySQL版本

八、JDBC

8.1 初始化驱动

通过Class.forName("com.mysql.jdbc.Driver");这句代码，完成了将MySQL的驱动注册到DriverManager中去。由于加载类时，会执行该类的static块中的内容，所以这句代码接着就会执行static代码块中的内容对驱动进行注册：

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
        	//1. 新建一个mysql的driver对象
        	//2. 将这个对象注册到DriverManager中
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

public static void registerDriver(java.sql.Driver driver) throws SQLException {
    //它实际调用了自身的registerDriver方法
    registerDriver(driver, null);
}

private final static CopyOnWriteArrayList<DriverInfo> registeredDrivers = new CopyOnWriteArrayList<>();

public static void registerDriver(java.sql.Driver driver, DriverAction da)
    throws SQLException {

    /* Register the driver if it has not already been added to our list */
    if (driver != null) {
        //如果该驱动尚未注册，那么将他添加到 registeredDrivers 中去。这是一个支持并发情况的特殊ArrayList
        registeredDrivers.addIfAbsent(new DriverInfo(driver, da));
    } else {
        // This is for compatibility with the original DriverManager
        throw new NullPointerException();
    }

    println("registerDriver: " + driver);

}

8.2 获取连接

通过下面的语句来获取连接

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");

其中，getConnection函数的实现为：

private static Connection getConnection(
    String url, java.util.Properties info, Class<?> caller) throws SQLException {
    for (DriverInfo aDriver : registeredDrivers) {
        Connection con = aDriver.driver.connect(url, info);
    }
}

可以看到它对上文提到的特殊ArrayList进行了遍历，调用了connect(url, info); 方法，这是一个接口，由各个不同的驱动自己实现。

8.3 执行SQL的过程

1.直接执行语句

执行SQL，先要通过Connection获取Statement，再通过Statement来执行SQL语句。查询结束后，要对Statement和Connection关闭。关闭方式有两种，一种是像下面一样，在finally语句中显式关闭，另一种则是直接用try-with-resource方式关闭。

public class TestJDBC {
    public static void main(String[] args) {
 
        Connection c = null;
        Statement s = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
 
            c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root",
                    "admin");
 
            s = c.createStatement();
 
            String sql = "insert into hero values(null," + "'提莫'" + "," + 313.0f + "," + 50 + ")";
 
            s.execute(sql);
 
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 数据库的连接时有限资源，相关操作结束后，养成关闭数据库的好习惯
            // 先关闭Statement
            if (s != null)
                try {
                    s.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            // 后关闭Connection
            if (c != null)
                try {
                    c.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
    }
}

2. 预编译语句

使用PreparedStatement可以执行预编译的SQL语句。

String sql = "insert into hero values(null,?,?,?)";
try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");
    // 根据sql语句创建PreparedStatement
    PreparedStatement ps = c.prepareStatement(sql);
) {

    // 设置参数
    ps.setString(1, "提莫");
    ps.setFloat(2, 313.0f);
    ps.setInt(3, 50);
    // 执行
    ps.execute();

} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

优点：

参数设置： 通过set的方式进行参数设置，可读性高，维护性好
性能表现： 有预编译机制，性能比Statement快，特别是需要多次执行SQL的时候
防止SQL注入： 使用参数的方式运行，能防止SQL注入

8.4 获取结果集

通过ResultSet来获取结果集，获取时，可以通过列名获取，也可以通过列所在的索引进行获取

ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
    int id = rs.getInt("id");// 可以使用字段名
    String name = rs.getString(2);// 也可以使用字段的顺序
    float hp = rs.getFloat("hp");
    int damage = rs.getInt(4);
    System.out.printf("%d\t%s\t%f\t%d%n", id, name, hp, damage);
}

本文地址：https://blog.csdn.net/qq_31709249/article/details/107393314

荐 秋招准备之——MySQL复习

秋招复习笔记系列目录（不断更新中）：

一、MySQL的逻辑架构

1.1 逻辑架构

1.2 并发控制

1.读写锁

2.锁粒度

3.显式和隐式锁定

4. 间隙锁

1.3 多版本并发控制（摘抄于这篇博客）

1. redo log 和 undo log

(1) redo log

(2) undo log

2. 实现机制

3. 如何实现一致性读——ReadView

(1) RR下ReadView的生成

(2) RC下ReadView的生成

1.4 事务

1. ACID

2. 并发带来的问题

3. 事务的隔离级别

4. 死锁

1.5 存储引擎

1. InnoDB引擎

2. MyISAM

二、Schema与数据类型优化

2.1 数据类型优化

2.2 范式和反范式

1.三范式

2. 范式的优缺点

3. 反范式的优缺点

4. 混合使用范式和反范式

2.3 缓存表和汇总表

1.物化视图

2. 计数器表

三、创建高性能的索引

3.1 索引基础

1. 索引类型

2.索引的作用

3.2 高性能索引策略

1.独立的列

2.前缀索引和索引选择性

3.多列索引

4.选择合适的索引列顺序

5.聚簇索引

6.主键的选择和插入优化

7. 覆盖索引

9. 使用索引扫描来做排序

10. 索引和锁

11.最左前缀原理与相关优化

12. 索引失效的一些情况

3.3 MyISAM和InnoDB索引的区别

3.4 全文索引（摘抄自这篇博客）

1 定义方式

2.使用方式

3. 最小搜索长度

4.两种全文索引

(1) 自然语言索引

(2) 布尔全文索引

3.5 创建高性能索引的一些建议

四、查询性能优化

4.1 优化数据访问

1.避免向数据库请求不需要的数据

2.避免扫描额外的数据

4.2 重构查询方式

4.3 查询执行的基础

1. MySQL客户端/服务端通信协议

2.查询缓存

3. 查询优化处理

4. 查询优化器的局限性

(1) 关联子查询

(2) UNION限制

(3) 最大值最小值优化

5.优化特定类型的查询

4.4 使用Explain进行分析（摘抄自这篇博客）

五、MySQL的高级特性

5.1 分区表

1.原理

2.分区表发挥作用的场景

3. 分区示例

荐秋招准备之——MySQL复习

荐秋招准备之——MySQL复习

荐秋招准备之——MySQL复习