MySQL高级

MySQL高级

存储引擎

数据库存储引擎是数据库底层软件组件，不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。

在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

MySQL5.6支持存储引擎有：InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE等。可以使用SHOW ENGINES语句查看系统所支持的引擎类型。

InnoDB

事务型数据库的首选引擎，支持事务，支持行锁定和外键。InnoDB是MySQL5.5.5后的默认引擎。

特性：1、提供具有提交、回滚和崩溃恢复的事务安全存储引擎。2、InnoDB为处理巨大数据量的最大性能设计。3、在主存中缓存数据和索引而维持它自己的缓冲池。4、支持外键完整性约束。5、一般运行在需要高性能的大型数据库站点上。

MyISAM

基于ISAM存储引擎，并扩展。Web、数据仓储和其他应用环境下最常用的存储引擎之一。较高的插入、查询速度，但不支持事务。MySQL5.5.5之前是默认存储引擎。

MEMORY

将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

存储引擎的选择

功能	MyISAM	Memory	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	×	×	√	×
支持全文索引	√	×	×	×
支持数索引	√	√	√	×
支持哈希索引	×	√	×	×
支持数据缓存	×	N/A	√	×
支持外键	×	×	√	×

InnoDB：如果要提供事务安全能力，并要求实现并发控制

MyISAM：数据表主要用来插入和查询记录，较高的处理效率

Memory：只是临时存放数据（如查询的中间结果），数据量不大，并且不需要较高的数据安全性

Archive：只有INSERT和SELECT操作，支持高并发的插入操作，不需要事务安全（如记录日志）

MySQL中的锁

在数据库中，并发控制是指在多个用户/进程/线程同时对数据库进行操作时，如何保证事务的一致性和隔离性的，同时最大程度地并发。当多个用户/进程/线程同时对数据库进行操作时，会出现3种冲突情形：

读-读，不存在任何问题

读-写，有隔离性问题，可能遇到脏读（会读到未提交的数据），幻影读等

写-写，可能丢失更新要解决冲突

一种办法是是锁，即基于锁的并发控制，比如2PL，这种方式开销比较高，而且无法避免死锁。

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

乐观并发控制（OCC）是一种用来解决写-写冲突的无锁并发控制，认为事务间争用没有那么多，所以先进行修改，在提交事务前，检查一下事务开始后，有没有新提交改变，如果没有就提交，如果有就放弃并重试，类似CAS操作。乐观并发控制适用于低数据争用，写冲突比较少的环境。

 

MySQL各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

在MySQL数据库中，使用表级锁定的主要是MyISAM，Memory，CSV等一些非事务性存储引擎，而使用行级锁定的主要是Innodb存储引擎和NDBCluster存储引擎，页级锁定主要是BerkeleyDB存储引擎的锁定方式。

MyISAM锁

MyISAM存储引擎采用表级锁，包括表共享读锁（TableRead Lock）和表独占写锁（Table Write Lock）。

共享读会阻塞同一表的写，独占写会阻塞所有读和写。

MyISAM在执行SELECT前会自动给涉及的表加读锁，执行UPDATE、DELETE、INSERT前会自动给涉及的表加写锁。不需要直接使用lock table命令，下面的显示加锁一般用于模拟事务操作：

lock table 表名 write;

lock table 表名 read;

unlock table

lock tables 表一 read local, 表二 read local，在LOCK TABLES时加了“local”选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录。MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

当concurrent_insert设置为0时，不允许并发插入。

当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

注意：读写操作是串行的，写操作优先级高于读锁。

InnoDB锁

参考：https://blog.csdn.net/soonfly/article/details/70238902

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

快照读与当前读

LBCC：Lock-Based Concurrency Control，基于锁的并发控制。

MVCC：Multi-Version Concurrency Control，基于多版本的并发控制协议。纯粹基于锁的并发机制并发量低，MVCC是在基于锁的并发控制上的改进，主要是在读操作上提高了并发量。

在MVCC并发控制中，读操作可以分成两类：

1）快照读(snapshot read)：读取的是记录的可见版本 (有可能是历史版本)，不用加锁（共享读锁s锁也不加，所以不会阻塞其他事务的写）。

2）当前读(current read)：读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

以MySQL InnoDB为例：

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外)

select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

下面语句都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

共享锁和排它锁

InnoDB实现了以下两种类型的行锁：

共享锁（s）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

排它锁（Ｘ）：又称写锁。允许获取排它锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排它写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

对于共享锁就是多个事务只能读数据不能改数据。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁，对于普通SELECT语句，InnoDB不会加任何锁。

事务可以通过以下语句显式给记录集加共享锁或排它锁：

共享锁（S）：SELECT * FROM table_name WHERE ...LOCK IN SHARE MODE。

排他锁（X）：SELECT * FROM table_name WHERE ...FOR UPDATE。

用SELECT ... INSHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

意向锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（IntentionLocks），这两种意向锁都是表锁。

意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排它锁（IX）：事务打算给数据行加排它锁，事务在给一个数据行加排它锁前必须先取得该表的IX锁。

意向锁是InnoDB自动加的，不需用户干预。

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

注意：

1、由于是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

2、即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

间隙锁（Next-Key锁）

当使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。

这种加锁机制会阻塞符合条件范围内键值的并发插入（防止幻读），这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

注意：InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等，这样，这些数据库操作语句就构成一个事务。

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

l  原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

l  一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

l  隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

l  持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

BEGIN或START TRANSACTION；显式地开启一个事务；

COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；

ROLLBACK；也可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；

RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier；把事务回滚到标记点；

SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MYSQL事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交

SET AUTOCOMMIT=1 开启自动提交

JDBC中，有5个常量可以用来描述事务隔离级别（了解）

事务的隔离级别

读取未提交：TRANSACTION_READ_UNCOMMITTED最低级别，任何情况都无法保证。

读取提交：TRANSACTION_READ_COMMITTED可避免脏读的发生。

可重读：TRANSACTION_REPEATABLE_READ可避免脏读、不可重复读的发生。

可串化：TRANSACTION_SERIALIZABLE可避免脏读、不可重复读、幻读的发生。

不支持事务常量：TRANSACTION_NONEJDBC。

	脏读	不可重复读	幻读
读取未提交	√	√	√
读取提交	×	√	√
可重读	×	×	√
可串行化	×	×	×

l  脏读：一个事务读取另一个事务尚未提交的数据。

l  不可重复读：对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。如事务T1读取了一行内容，T2修改了T1刚刚读取的那一行记录，然后T1再次读取该行记录发现与刚刚读取的不同。

l  幻读：事务非独立执行时发生的一种现象，幻读和不可重复读都是读取了另一条已经提交的事务（可重复读所有被select获取的数据都不能被修改，防止前后读取不一致，但可以增加数据，造成幻读），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

l  更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。

MySQL默认隔离级别为可重复读

MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。

索引

用于快速找出某个列中有一特定值的行。不使用索引，需要从第1行开始查找。索引是对数据库表中一列或多列的值进行排序的一种结构。

索引是一个单独的、存储在磁盘上的数据库结构，在存储引擎中实现，根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。

MySQL中索引的存储类型有两种：BTREE和HASH。InnoDB和MyISAM只支持BTREE索引，MEMORY/HEAP两种都支持。

设计原则：

1、索引并非越多越好，不然会影响更新记录的速度和占用更多空间，避免经常更新的表进行建立过多索引。

2、对于经常查询的字段建立索引

3、数据量小时没必要建立索引

4、不同值较多的列上建立索引，不同值少的列上不建索引（如性别男和女）

5、在频繁进行排序或分组的列上建立索引

索引优点：

1、通过创建唯一索引，可以保证数据库表中每一行数据的唯一性

2、加快查询速度，这是最主要原因

3、加速表和表之间的连接

4、加快使用分组和排序子句进行数据查询

缺点：

1、创建和维护索引消耗时间

2、占用物理空间

3、数据的变更增删改，索引也需要动态地维护。

索引分类

普通索引

基本索引类型，允许定义索引的列中插入重复值和空值。

添加索引时：

在创建表，使用INDEX 索引名（列名）创建索引

修改表时，ALTERTABLE 表名 ADD INDEX 索引名 （列名）

使用CREATE INDEX[UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名 (列名[length])[ASC|DESC]

删除索引时：

ALTER TABLE 表名 DROP INDEX 索引名

DROP INDEX 索引名ON 表名

下面不一一举例

唯一索引

索引列必须唯一，但允许有空值。如果是组合索引，则组合必须唯一。主键索引是特殊的唯一索引，不允许为空。

使用UNIQUE INDEX 索引名 （列名），如UNIQUE INDEX ‘UniqIdx’ (‘id’)

单列索引

一个索引只包含单个列，一个表可以有多个单列索引。

上面提到的都是单列索引，使用INDEX 索引名 （列名[(索引长度)]）

组合索引

在表的多个字段上创建索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引遵循最左前缀集合。最左前缀意思是利用索引中最左边的列集来匹配行，如列1，列2构成前缀可以使用索引，而列2，或者列3，列1等则不能使用局部索引。

使用INDEX 索引名 （列1，列2，…）

全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引，适合大型数据集。

索引总是对整个列进行，不支持局部（前缀）索引。

使用FULLTEXT INDEX 索引名 （列名）创建，注意需要将表的存储引擎设置为MyISAM（ENGINE=MyISAM），不然会报错。

空间索引

对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别为GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须为NOT NULL，只能在存储引擎为MyISAM的表中创建。

如：

CREATE TABLE t5

(g GEOMETRY NOT NULL,SPATIAL INDEXspatIdx(g))ENGINE=MyISAM;

 

存储过程和函数

简单来说，存储过程就是一条或者多条SQL语句的集合，可视为批文件，但其作用不仅限于批处理。

存储程序可以分为存储过程和函数。

存储过程

创建：

CREATEPROCEDUER 存储过程名称 （[参数列表]） [characteristics…] routine_body

其中参数列表形式为[IN|OUT|INOUT]param_nametype，IN表示输入参数，OUT表示输出参数，INOUT表示即可输入也可输出，type表示参数类型。

characteristics指定存储过程的特性，有以下取值：

l  LANGUAGE SQL ：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值

l  [NOT] DETERMINISTIC ：指明存储过程执行的结果是否正确。DETERMINISTIC 表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。[NOT] DETERMINISTIC 表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为[NOT] DETERMINISTIC

l  CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA：指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA：说明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINSSQL

l  SQL SECURITY { DEFINER | INVOKER } ：指明谁有权限来执行。DEFINER 表示只有定义者才能执行。INVOKER 表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。

l  COMMENT ‘string’ ：注释信息，可以用来描述存储过程或函数

routine_body是SQL代码的内容，可以用BEGIN…END来表示SQL代码的开始和结束。

如：

CREATE PROCEDURE AvgScore()

BEGIN

SELECT AVG(score) AS avgscore FROMstuscore;

END;

 

CREATE PROCEDURE Count(OUT param1 INT)

BEGIN

SELECT COUNT(*) INTO param1 FROM stu;//将结果放入参数param1中

END;

调用：

CALL 存储过程名（[参数列表]）

SHOW PROCEDURE STATUS [LIKE ‘pattern’];//查看存储过程状态

SHOW CREATE PROCEDURE 存储过程名;//查看存储过程状态

修改：

ALTER PROCEDURE 存储过程名 [characteristics…]

删除：

DROP PROCEDURE xxx;

函数

创建：

CREATEFUNCTION 函数名 （[参数列表]） RETURNS type [characteristics…] routine_body

参数列表、characteristics、routine_body和存储过程中的含义相同。

调用：

用户自己定义的存储函数和MySQL内部函数是一个性质的，可以直接使用。

如：myfunction(param1);

SHOW FUNCTION STATUS [LIKE ‘pattern’];//查看函数状态

SHOW CREATE FUNCTION 函数名;//查看函数状态

修改：

ALTER FUNCTION 函数名 [characteristics…]

删除：

DROP FUNCTION xxx;

变量

定义：DECLARE 变量名1,2… 变量类型 [默认值];

如DECLAREmypara INT DEFAULT 100;

赋值：SET 变量名 = expr[,变量名2=expr]…;

视图

视图是一个虚拟表。视图是从一个或者多个表导出的，视图的行为与表非常相似，可以使用SELECT/INSERT/UPDATE/DELETE。

视图定义后数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。这样可以使得想要的数据来自表的部分信息而不修改表的结构。

在单表上创建视图：

CREATE VIEW view_t AS SELECTsname,sno,score from student;

在多表上创建视图：

CREAT VIEW stu_glass(sno,name,glass) ASSELECT a.sno,a.sname,b.glass

FROM students a,stu_info b WHEREa.sno=b.sno;

查看视图：

DESCRIBE 视图名;

SHOW TABLE STATUS LIKE ‘视图名’;

SHOW CREATE VIRW 视图名;

修改视图（修改字段）：

CREATE OR REPLACE VIEW 视图名 AS SELECT…;

ALTER VIEW 视图名 AS SELECT …;

更新视图（更新数据，更新映射的基本表上的数据）：

UPDATE、INSERT INTO、DELETE基本用法。

删除视图：

DROP VIEW [IF EXISTS] 视图名;

触发器

触发器（trigger）是一个特殊的存储过程，不同的是，执行存储过程要使用CALL语句来调用，而触发器执行不需要手动启动，而是根据预定义事件发生的时候MySQL会自动调用（有点像监听器）。比如可以根据用户账户状态，控制是否允许插入新订单等。

创建：

CREATE TRIGGER trigger_name trigger_timetrigger_event

ON table_name FOR EACH ROW trigger_stmt

其中trigger_name为触发器名称，trigger_time为触发器时机（可选before或after），trigger_event为触发事件（包括INSERT、UPDATE、DELETE），table_name为建立触发器的表名，trigger_stmt为触发器程序体。触发器可以使用begin和end作为开始和结束。

查看触发器：

SHOW TRIGGERS;

SELECT * FROM INFORMATION_SCHEMA.TRIGGERSWHERE condition;

如：SELECT *FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME=’trig_update’;

删除触发器：

DROP TRIGGER [schema_name.]trigger_name;