MySql使用手册--进阶篇
MySql存储引擎
什么是存储引擎？
简而言之，存储引擎就是指表的类型，数据库的存储引擎决定了表在计算机中的存储方式。存储引擎的概念是MySql的特点，而且是一种插入式的存储引擎概念，这决定了MySQL数据库中的表可以用不同的方式存储，用户可以根据自己不同的要求，来选择不同的存储方式、是否进行事务处理等。
- 查询方法：SHOW ENGINES；
InnoDB存储引擎
简介
InnoDB是MySQL的默认事务引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期事务，短期事务大部分情况是正常提交的，很少会被回滚（当事务中的某条语句出错是，整个事务并不会成功执行，而是会退回到事务中所有语句执行之前，这里先大概了解，后面会更新文章讲解回滚的机制以及实现原理）。
InnoDB概览
- InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。在MySql4.1之后的版本中，InnoDB可以将每个表的数据和索引存放在单独的文件中。
- InnoDB采用MVCC（MVCC是为了实现事务的隔离性，通过版本号，避免同一数据在不同事务间的竞争，你可以把它当成基于多版本号的一种乐观锁。当然，这种乐观锁只在事务级别未提交锁和已提交锁时才会生效。）来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ（可重复读），并通过间隙锁防止幻读的出现。
- InnoDB表是基于聚簇索引建立的，InnoDB的索引结构和MySQL的其他存储引擎有很大的不同，聚簇索引对主键查询有很高的性能。
MyISAM存储引擎
简介
在MySql5.1及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数等，但是MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是就是崩溃后无法安全恢复。
存储
MyISAM会将表存储在两个文件中；数据文件和索引文件，分别以 .MYD 和 .MYI为扩展名。MyISAM表可以包含动态或者静态（长度固定）行。MySQL会根据表的定义来决定采用何种行格式。MyISAM表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。
MyISAM特性
作为MySQl最早的存储引擎之一，MyISAM有一些已经开发出来很多年的特性，可以满足用户的实际需求。
- 加锁与并发：MyISAM对整张表加锁而不是针对行。
- 修复：对于MyISAM表，MySql可以手工或者自动执行检查和修复操作，但是这里说的修复和事务恢复以及崩溃恢复是不同的概念。
- 索引特性：对于MyISAM表，即使是BLOB和TEXT等长字段，也可以基于其前500个字符创建索引。
- 延迟更新索引键：创建MyISAM表时，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引写入到磁盘。
MyISAM压缩表
如果表在创建并导入数据后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。
可以使用myisampack对MyISAM表进行压缩。压缩表是不能进行修改的（除非先将表解除压缩，修改数据，再次压缩）。压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的。
MySql内建的其他存储引擎
- Archive引擎：只支持INSERT和SELECT操作，在5.1之前不支持索引，比MyISAM表的磁盘I/O更少。支持行级锁和专用的缓冲区，可以实现高并发。
- Blackhole引擎：没有实现任何存储机制，会丢弃所有的插入数据，不做任何保存。只是服务器简单的记录到日志。
- CSV引擎：可以将普通的CSV文件作为MySQL的表来处理，不支持索引。
- Federated引擎：是访问其他MySQl服务器的一个代理，会创建一个到远程MySql服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。
- Memory引擎：比MyISAM表要快一个数量级，因为所有的数据都保存在内存中，不需要的进行磁盘I/O。
索引
索引简介
索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录，由表中的一列或者多列组合而成，创建在表上，是对数据库表中一列或者多列的值进行排序的一种结构，可以提高查询速度。
索引的分类
- 1.普通索引：创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。
- 2.唯一性索引：创建唯一性索引时，限制该索引的值必须是唯一的。使用UNIQUE参数设置。
- 3.全文索引：只能创建在CHAR、VARCHAR或者TEXT类型字段上。使用FULLTEXT参数设置。
- 4.单列索引：只根据该字段进行索引，可以是普通或唯一性索引，还可以是全文索引，只要保证该索引只对应一个字段。
- 5.在多个字段上创建一个索引。只有查询条件中使用了这些字段中的第一个字段时，索引才会被引用。
- 6.空间索引：基本不用，只能建立在空间数据类型（GEOMETRY、POINT、LINESTRING、POLYGON）上。
总的来说，其实可以把索引归为单列索引、多列索引、全文索引和空间索引四种。
索引的存储类型
B-Tree索引
一般来说，如果没有特别指明，那么多半说的是B-Tree索引。它使用B-Tree数据结构来存储数据。
B-Tree通常意味着所有的值都是按顺序存储，并且每一个叶子页到根的距离相同。下图便抽象表示了B-Tree的结构。
其中，根节点中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点。这些指针实际定义了子节点页中值的上下限，最终存储引擎要么是找到对应的值，要么不存在。
B-Tree索引的限制
- 如果不是按照索引的最左列开始查找，则无法使用索引
- 不能跳过索引中的列
- 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。
哈希索引
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个比较小的值，并且不同键值的行计算出来的哈希码也不一样，哈希索引将所有的哈希码储存在索引中，同时在哈希表中保存指向每个数据行的指针。
如果多列哈希值想相同，索引会以链表的方式放多个记录指针到同一个哈希目录中。
哈希索引的限制
- 只包含哈希值和行指针，不存储字段，所以不能使用索引中的值来避免读取行。
- 哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序。
- 不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
- 只支持等值查询，不支持任何范围查询。
- 访问速度快（除非出现哈希冲突，此时要遍历链表）
索引的设计原则
- 选择唯一性索引
- 为经常需要排序、分组和联合操作的字段建立索引
- 为常作为查询条件的字段建立索引
- 限制索引的数目
- 尽量使用数据量少的索引
- 尽量使用前缀来索引
- 删除不再使用或者很少使用的索引
视图
视图的含义和作用
**含义：**视图就是从一个或多个表中导出来的表，是一个虚拟存在的表，就像是一个窗口，通过这个窗口用户可以看到系统专门提供的数据。这样，可以使用户操作更加方便，还可以保障数据库系统的安全性。
作用：
- 使操作简单化
- 增加数据的安全性
- 提高表的逻辑独立性
创建、查看、修改和删除视图的语法形式
创建视图
查看视图：
修改视图：
删除视图：
触发器
触发器的含义
触发器是由INSERT、UPDATE和DELETE等事件来触发某种特定操作，满足触发器的触发条件时，数据库就会执行触发器中定义的语句，可以保证某些操作之间的一致性。
例如，当学生表中增加一个学生信息，学生总数必须同时改变。可以在里创建一个触发器，每次增加一个学生的记录，就执行一次计算学生总数的操作。这样就可以保证每次增加学生记录后，学生总数与记录保持一致。触发器的执行语句可能有一个，也可能有多个。
如何创建和查询触发器
创建只有一个执行语句的触发器
语法：
实例：
mysql> CREATE TRIGGER dept_trig1 BEFORE INSERT ON test1 FOR EACH ROW
INSERT INTO trigger_time VALUES(NOW());
Query OK, 0 rows affected (0.36 sec)
插入一条语句，查看结果：
mysql> SELECT * FROM trigger_time;
Empty set (0.01 sec)
mysql> INSERT test1 VALUES(1,'ko','男');
Query OK, 1 row affected (1.88 sec)
mysql> SELECT * FROM trigger_time;
+-----------+
| exec_time |
+-----------+
| 21:46:33 |
+-----------+
1 row in set (0.00 sec)
创建有多个执行语句的触发器
语法：
实例：
mysql> DELIMITER &&
mysql> CREATE TRIGGER dept_trig2 AFTER DELETE
-> ON test1 FOR EACH ROW
-> BEGIN
-> INSERT trigger_time VALUES('21:55:01');
-> INSERT trigger_time VALUES('21:55:06');
-> END
-> &&
Query OK, 0 rows affected (0.25 sec)
mysql> DELIMITER ;
结果如下：
mysql> SELECT * FROM trigger_time;
+-----------+
| exec_time |
+-----------+
| 21:46:33 |
+-----------+
1 row in set (0.00 sec)
mysql> DELETE FROM test1 WHERE id=1;
Query OK, 1 row affected (1.93 sec)
mysql> SELECT * FROM trigger_time;
+-----------+
| exec_time |
+-----------+
| 21:46:33 |
| 21:55:01 |
| 21:55:06 |
+-----------+
3 rows in set (0.00 sec)
查看触发器
1.SHOW TRIGGERS语句
2.在trigeers表中查看触发器信息(详细信息)
也可以使用：SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME=‘触发器名’;
mysql> SELECT * FROM information_schema.triggers\G;
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: sys
TRIGGER_NAME: sys_config_insert_set_user
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: sys
EVENT_OBJECT_TABLE: sys_config
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2021-01-24 16:42:04.42
SQL_MODE:
DEFINER: mysql.sys@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
*************************** 2. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: sys
TRIGGER_NAME: sys_config_update_set_user
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: sys
EVENT_OBJECT_TABLE: sys_config
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2021-01-24 16:42:04.59
SQL_MODE:
DEFINER: mysql.sys@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
*************************** 3. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: test_db1
TRIGGER_NAME: dept_trig1
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test_db1
EVENT_OBJECT_TABLE: test1
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: INSERT INTO trigger_time VALUES(NOW())
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2021-03-13 21:43:50.31
SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
*************************** 4. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: test_db1
TRIGGER_NAME: dept_trig2
EVENT_MANIPULATION: DELETE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test_db1
EVENT_OBJECT_TABLE: test1
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
INSERT trigger_time VALUES('21:55:01');
INSERT trigger_time VALUES('21:55:06');
END
ACTION_ORIENTATION: ROW
ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2021-03-13 21:54:36.17
SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
4 rows in set (0.13 sec)
触发器的使用
在MySQL中，触发器的执行顺序是BERORE触发器，表操作和AFTER触发器。
实例：
mysql> CREATE TRIGGER before_insert BEFORE INSERT
-> ON test1 FOR EACH ROW
-> INSERT INTO trigger_test VALUES(null,'before_insert');
Query OK, 0 rows affected (0.35 sec)
mysql> CREATE TRIGGER after_insert AFTER INSERT
-> ON test1 FOR EACH ROW
-> INSERT INTO trigger_test VALUES(null,'after_insert');
Query OK, 0 rows affected (0.45 sec)
结果：
mysql> SELECT * FROM trigger_test;
Empty set (0.00 sec)
mysql> INSERT test1 VALUES(1,'pl','男');
Query OK, 1 row affected (0.51 sec)
mysql> SELECT * FROM trigger_test;
+----+---------------+
| id | info |
+----+---------------+
| 2 | before_insert |
| 3 | after_insert |
+----+---------------+
2 rows in set (0.00 sec)
存储过程和函数
存储过程和函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句，这样可以避免开发人员重复编写相同的SQL语句，并且，存储过程函数是在MySQL服务器中存储和执行的，可以减少客户端和服务端的数据传输。
存储过程
语法：
存储函数
语法：
这里与上面创建存储过程类似
调用
存储过程：
CALL sp_name([parameter[…]]);
sp_name 为名称，paramter 为参数
存储函数： 这里与MySQL内部函数的使用方法是一样的，一个性质。
光标的使用
查询语句可能有多条记录，在存储过程和函数中使用光标来逐条读取查询中的记录。
语法：
创建：
DECLARE cursor_name CURSOR FOR select_statement;
第一个参数表示名称，第二个为SELECT语句
打开：
OPEN cursor_name;
使用：
FETCH cur_name INTO var_name…(多个)
这里var_name 必须在声明光标之前就定义好
关闭：
CLOSE cursor_name;
注意：
光标必须声明在处理程序之前，并且声明在变量和条件之后。
事务
事务的概念以及ACID
什么是事务？
事务就是一组原子性的SQL查询，或者说一个独立的工作单元。也就是说事务内的语句，要么全部都执行成功，要么全都执行失败。
在MySQL中，如果事务中的一条语句执行失败，那么将会回滚到事务开始执行前的状态。不会对数据库产生影响。
ACID
ACID表示原子性（atomicity）、一致性（consistency）、隔离性（islation）和持久性（durability）。一个运行良好的事务处理系统给，必须具备这些标准特征。
- 原子性（atomicity）：一个事务必须被视为一个不可分割的最小工作单元。对韵一个事务来说，不可能只执行其中的一部分。
- 一致性（consistency）：数据库总是从一个一致性的状态转化到另外一个一致性的状态。例如，一个事务在执行到中间某条语句时系统崩溃，但由于事务没有提交，所以事务中的修改也不会保存在数据库。
- 隔离性（isolation）：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。
- 持久性（durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中。
隔离级别
- READ UNCOMMITTED（未提交读）：在这个级别中，食物中的修改，即使没有提交，对其他事务都是可见的。事务可以读取未提交的数据，这也称为脏读（Dirty Read）。
- READ COMMITTED（提交读）：大多数数据系统默隔离级别都是READ COMMITTED（但MySQL不是）。这个级别满足隔离性的简单定义：一个事务开始时，只能“看见”已经提交的事务所作的修改。这个级别有时也称为不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。
- REPEATABLE READ（可重复读）：这个级别解决了脏读的问题。可是，它无法解决另一个幻读（Phantom Read）的问题。所谓幻读：指当某个事物在读取某个范围内的记录时，另外一个事务又在范围内插入新的记录，当之前的事务再次读取还范围的记录时，会产生幻行。
这一级别是MySQL的默认事务隔离级别。 - SERIALIZABLE（可串行化）：是最高的隔离级别。通过强制事务串行执行，避免了前面说的幻读问题。简单来说，他会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。
死锁
死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也会产生死锁。这样说可能不太好理解，我们举个例子：
试想，上面两个事务同时处理表，假设，两个事务都执行了第一条UPDATE语句，更新了一行数据，同时也锁定了该行数据，这时，每个事务都尝试去执行第二条语句，却发现该行已被对方锁定，然后两个事务都等待对方释放锁，又同时持有对方的锁，则陷入死循环，除非由外部因素介入才可能解除死锁。
死锁的解决办法： 为了解决这种问题，数据库实现了各种死锁检测和死锁超时机制。越复杂的系统，比如InnoDB存储引擎，越能检测到思死锁的循环依赖，并立即返回一个错误。
还有一种解决方式是，查询的时间达到锁等待超时的设定后放弃请求，这种方式通常来说不太好。
InnoDB目前的处理死锁方式：将持有最少行级排他锁的事务进行回滚（这是相对比较简单的死锁回滚算法）。
死锁发生之后，只有部分或者完全回滚其中一个事务，才能打破死锁。对于事务型的系统，这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。
MySQL中的事务
MySQL中提供了两种事务型的存储引擎：InnoDB和NDB Cluster。
自动提交（AUTOCOMMIT）
MySQL默认采用自动提交（AUTOCOMMIT）模式。
如果不是显式的开启一个事物，则每个查询都被当作一个事务执行提交操作。在当前连接中，可以通过设置AUTOCOMMIT变量来启动或者自动提交模式。
1或者ON表示启用，0或者OFF表示禁用。当为0时，所有查询在一个事务中，只有显式地执行COMMIT提交或者ROLLBACK回滚，该事务结束，同时又开始了另一个新事物。
修改AUTOCOMMIT对非事务型的表，比如MyISAM或者内存表，不会有任何影响，对这类表，没有COMMIT或者ROLLBACK的概念，也可以说相当于一直处于AUTOCONNIT启用的模式。
参考资料
[1].MySQl5.6官方手册https://dev.mysql.com/doc/refman/5.6/en/preface.html
[2].黄缙华 《MySQL入门很简单》
[3].《高性能MySQL》第三版
扫一扫
2626
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
