深入浅出MySQL数据库开发、优化与管理维护（第2版）读书笔记

社大小树同学
1,131 阅读55分钟

基础篇

SQL基础

SQL分类

  1. DDL（Data Definition Languages）：数据定义语句，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。create、drop、alter等。
  2. DML（Data Manipulation Language）：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。insert、delete、update和select等。
  3. DCL（Data Control Language）：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户访问权限和安全级别。grant、revoke等。

DDL语句

它和DML语句最大的区别是DML只是对表内部数据操作，而不涉及表的定义、结构的修改，更不会涉及其他对象。

1. 创建数据库
CREATE DATABASE dbname
2. 删除数据库
DROP DATABASE dbname
3. 创建表
CREATE TABLE tablename (
column_name_1 column_type_1 constraints,
column_name_2 column_type_2 constraints,
...
column_name_n column_type_n constraints)

MySQL的表名是以目录的形式存在于磁盘上的，所以表名的字符可以用任何目录名允许的字符。

4.删除表
DROP TALBE tablename
5. 修改表
-- 修改表类型
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST|AFTER col_name]
-- 增加表字段
ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST|AFTER col_name]
-- 删除表字段
ALTER TABLE tablename DROP [COLUMN] col_name
-- 字段改名
ALTER TALBE tablename CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
--  更改表名
ALTER TABLE tablename RENAME [TO] new_tablename

注意：

  1. change和modify都可以修改表的定义，不同的是change后面需要写两次列名，不方便。但change的优点是可以修改列名称，modify不能。
  2. CHANGE/FIRST|AFTER COLUMN这些关键字都属于MySQL在标准SQL上的扩展，在其他数据库上不一定适用。

DML语句

1. 插入记录
INSERT INTO tablename(field1,field2,...,fieldn) VALUES(value1,value2,...,valuen)
2. 更新记录
UPDATE tablename SET field1=value1,field2=value2,...,fieldn=valuen [WHERE CONDITION]
3. 删除记录
DELETE FROM tablename [WHERE CONDITION]

在MySQL中可以一次删除多个表的数据，语法如下：

DELETE t1,t2,...,tn FROM t1,t2,...,tn [WHERE CONDITION]

如果from后面的表名用别名，则delete后面也要用相应的别名，否则会提示语法错误。

4. 查询记录
-- 排序
SELETE * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC], field2 [DESC|ASC],...,fieldn [DESC|ASC]]
-- 限制
SELECT ... [LIMIT offset_start,row_count]
-- 聚合
SELECT [field1,field2,...,fieldn] fun_name FROM tablename [WHERE where_condition] [GROUP BY field1,field2,...,fieldn] [WITH ROLLUP] [HAVING having_condition]

注意：

  1. WITH ROLLUP是可选语法，表明是否对分类聚合后的结果再进行汇总。
  2. having和where的区别在于，having是对聚合后的结果进行条件的过滤，而where是在聚合前就对记录进行过滤，如果逻辑允许，尽可能使用where先过滤记录，这样因为结果集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用having进行再过滤。

DCL语句

DCL语句主要是DBA用来管理系统中的对象权限时使用，一般的开发人员很少使用。以下通过例子说明。

-- 创建一个数据库用户z1，具有对sakila数据库中所有表的SELECT/INSERT权限
GRANT select,insert ON sakila.* to 'z1'@'localhost' identified by '123';
-- 收回INSERT权限
REVOKE insert ON sakila.* FROM 'z1'@'localhost';

MySQL支持的数据类型

数值类型

整数类型 字节 最小值 最大值
TINYINT 1 有符号 -128
无符号 0		 有符号 127
无符号 255
SMALLINT 2 有符号 -32768
无符号 0		 有符号 32767
无符号 65535
MEDIUMINT 3 有符号 -8388608
无符号 0		 有符号 8388607
无符号 1677215
INT、INTEGER 4 有符号 -2147483648
无符号 0		 有符号 2147483647
无符号 4294967295
BIGINT 8 有符号 -9223372036854775808
无符号 0		 有符号 9223372036854775807
无符号 18446744073709551615
浮点类型 字节 最小值 最大值
FLOAT 4 ±1.175494351E-38 ±3.402823466E+38
DOUBLE 8 ±2.2250738585072014E-308 ±1.7976931348623157E+308
位类型 字节 最小值 最大值
BIT(M) 1~8 BIT(1) BIT(64)
定点类型 字节 描述
DEC(M,D),
DECIMAL(M,D)		 M+2 最大值范围与DOUBLE相同，给定DECIMAL的有效取值范围由M和D决定

对于整型数据，MySQL还支持在类型名称后面的小括号内指定显示宽度，例如int(5)表示当数值宽度小于5位的时候在数字前面填满宽度，如果不显示指定宽度则默认为int(11)。一般配合zerofill使用，顾名思义，zerofill就是用"0"填充的意思，也就是在数字位数不够的空间用字符"0"填充。

如果一列指定为zerofill，则MySQL自动为该列添加UNSIGNED属性。

日期时间类型

日期时间类型 字节 最小值 最大值
DATE 4 1000-01-01 9999-12-31
DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59
TIMESTAMP 4 19700101080001 2038年的某个时刻
TIME 3 -838:59:59 838:59:59
YEAR 1 1901 2155

TIMESTAMP有一个重要的特点，就是和时区相关。当插入日期时，会先转换为本地时区后存放；而从数据库里面取出来时，也同样需要将日期转换为本地时区后显示。

字符串类型

字符串类型 字节 描述及存储需求
CHAR(M) M M为0~255之间的整数
VARCHAR(M) M为0~65535之间的整数，值的长度为+1字节
TINYBLOB 允许长度0~255字节，值的长度+1字节
BLOB 允许长度0~65535字节，值的长度+2字节
MEDIUMBLOB 允许长度0~167772150字节，值的长度+3字节
LONGBLOB 允许长度0~4394967295字节，值的长度+4字节
TINYTEXT 允许长度0~255字节，值的长度+2字节
TEXT 允许长度0~65535字节，值的长度+3字节
MEDIUMTEXT 允许长度0~167772150字节，值的长度+3字节
LONGTEXT 允许长度0~4394967295字节，值的长度+4字节
VARBINARY(M) 允许长度0~M字节的变长字节字符串，值的长度+1个字节
BINARY(M) M 允许长度0~M字节的变长字节字符串

在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格。

枚举类型

它的值范围需要在创建表时通过枚举方式显式指定，对1~255个成员的枚举需要1个字节存储；对于255~65535个成员，需要2个字节存储。最多允许有65535个成员。

SET类型

SET和ENUM类型非常类似,也是一个字符串对象,里面可以包含0~64个成员。

SET和ENUM除了存储之外，最主要的区别在于SET类型一次可以选取多个成员，而ENUM则只能选一个。

常用函数

字符串函数

函数 功能
CONCAT(S1,S2,...,Sn) 字符串连接
INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr
LOWER(str) 转小写
UPPER(str) 转大写
LEFT(str,x) 返回字符串str最左边的x个字符
RIGHT(str,x) 返回字符串str最右边的x个字符
LPAD(str,n,pad) 用字符串pad对str最左边进行填充，知道长度为n个字符长度
RPAD(str,n,pad) 用字符串pad对str最右边进行填充，知道长度为n个字符长度
LTRIM(str) 去掉字符串str左侧的空格
RTRIM(str) 去掉字符串str右侧的空格
REPEAT(str,x) 返回str重复x次的结果
REPLACE(str,a,b) 用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2) 比较字符串s1和s2
TRIM(str) 去掉字符串行尾和行头的空格
SUBSTRING(str,x,y) 返回从字符串str x位置起y个字符长度的字串

数值函数

函数 功能
ABS(x) 绝对值
CEIL(x) 返回大于x的最小整数
FLOOR(x) 返回小于x的最大整数
MOD(x,y) 返回x/y的模
RAND() 返回0~1内的随机值
ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y) 返回数字x截断为y位小数的结果

日期和时间函数

函数 功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前的日期和时间
UNIX_TIMESTAMP(date) 返回日期date的UNIX时间戳
FROM_UNIXTIME 返回UNIX时间戳的日期值
WEEK(date) 返回日期date为一年中的第几周
YEAR(date) 返回日期date的年份
HOUR(time) 返回time的小时值
MINUTE(time) 返回time的分钟值
MONTHNAME(date) 返回date的月份名
DATE_FORMAT(date,fmt) 返回按字符串fmt格式化日期date值
DATE_ADD(date,INTERVAL expr type) 返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2) 返回起始时间expr和结束时间expr2之间的天数

流程函数

函数 功能
IF(value,t,f) 如果value为真，返回t，否则返回f
IFNULL(value1,value2) 如果value1不为空，则返回value1，否则返回value2
CASE WHEN [value1] THEN [result1]...ELSE [default] END 如果value1是真，返回result1，否则返回default
CASE [expr] WHEN [value1] THEN [result1]...ELSE [default] END 如果expr等于value1，返回result，否则返回default

常用其他函数

函数 功能
DATABASE() 返回当前数据库名
VAERSION() 当前数据库版本
USER() 当前登录用户名
INET_ATON(IP) 返回IP地址的数字表示
INET_NTOA(mum) 返回数字表示的IP地址
PASSWORD(str) 返回字符串str的加密版本
MD5() 返回字符串str的MD5值

开发篇

表类型（存储引擎）的选择

存储引擎概述

MySQL5.0支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。

查看当前默认存储引擎，可以使用如下命令。

show varivales like 'table_type';

查询当前数据库支持的存储引擎，可以使用以下两种方式。

SHOW ENGINES\G;
SHOW VARIABLES LIKE 'have%';

各种存储引擎的特性

特点 MyISAM InnoDB MEMORY MERGE NDB
存储限制 有 64TB 有 没有 有
事务安全 支持
锁机制 表锁 行锁 表锁 表锁 行锁
B树索引 支持 支持 支持 支持 支持
哈希索引 支持 支持
全文索引 支持
集群索引 支持
数据缓存 支持 支持 支持
索引缓存 支持 支持 支持 支持 支持
数据可压缩 支持
空间使用 低 高 N/A 低 低
内存使用 低 高 中等 低 高
批量插入的速度 高 低 高 高 高
支持外键 支持

MyISAM

MyISAM不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。

每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但扩展名分别是：

  • .frm 存储表定义
  • .MYD MYData，存储数据
  • .MYI MYIdex，存储索引

数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。

InnoDB

InnoDB存储引擎提供了具有提交、回滚和崩溃回复能力的事务安全。

自动增长列

可以通过ALTER TABLE *** AUTO_INCREMENT = n;语句强制设置自动增长列的初始值，默认从1开始，但是该强制的默认值是保留在内存中的，如果该值在使用之前数据库重新启动，那么这个强制的默认值就会丢失，就需要在数据库启动以后重新设置。

对于InnoDB，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于MyISAM表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引的前面几列进行排序后递增的。

外键约束

MySQL支持外键的存储引擎只有InnoDB,在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

CREATE TABLE country(
	country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
	country VARCHAR(50) NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE city(
	city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  city VARCHAR(50) NOT NULL,
  country_id SMALL UNSIGNED NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (city_id),
  KEY idx_fk_country_id (country_id),
  CONSTRAINT 'fk_city_country' FOREIGN KEY (country_id) REFERENCES country(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

可以指定在删除、更新父表时，对子表进行的相应操作，包括RESTRICT、CASCADE、SET NULL和NO ACTION。其中RESTRICT和NO ACTION相同，是指限制在子表有关联记录的情况下父表不能更新；CASCADE表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL则表示父表在更新或者删除的时候，子表的对应字段被SET NULL。

存储方式

InnoDB存储表和索引有以下两种方式。

  1. 使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。
  2. 使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中。如果是个分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO均匀分布在多个磁盘上。

MEMORY

MEMORY存储引擎使用存在于内存中的内容创建表。每个MEMORY表只实际对应一个磁盘文件，格式是.frm。MEMORY类型的表访问非常快，因为它的数据是放在内存中的，并且默认使用HASH索引,但是一旦服务关闭,表中的数据就会丢失掉.

MERGE

MERGE存储引擎是一组MyISAM组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。

MERGE表在磁盘上保留两个文件，文件名以表的名字开始，一个.frm文件存储表定义，另一个.MRG文件包含组合表的信息，包括MERGE表由哪些表组成、插入新的数据时的依据。

字符集

MySQL支持的字符集

查看所有可用的字符集的命令是show character set;

或者查看information_schema.character_set，可用显示所有的字符集和该字符集默认的校对规则。

MySQL字符集的设置

MySQL的字符集和校对规则有4个级别的默认设置：服务器级、数据库级、表级和字段级。

服务器字符集和校对规则

  • 可以在my.cnf中设置

  • [mysqld]
character-set-server=gbk

  • 或者在启动选项中指定

  • mysqld --character-set-server=gbk

  • 或者在编译时指定

  • shell> cmake . -DEFALUT_CHARSET=gbk

    可以使用show variables like 'character_set_server'命令查询当前服务器的字符集和校对规则。

    show variables like 'character_set_server';
show variables like 'collation_server';
数据库字符集和校对规则

数据库的字符集和校对规则在创建数据库的时候指定，也可以再创建完数据库后通过alter database命令进行修改。需要注意的是，如果数据库里已经存在数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据的内容。

要显示当前数据库的字符集和校对规则，可以使用show variables like 'character_set_database'和show variables like 'collation_database'命令查看。

show variables like 'character_set_database';
show variables like 'collation_database';
表字符集和校对规则

表的字符集和校对规则在创建表的时候指定，可以通过alter table命令进行修改，同样，如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新的字符集进行存放。表的字段仍然使用原来的字符集。

要显示表的字符集和校对规则，可以使用show create table命令查看。

show create table z1\G;
列字符集和校对规则

MySQL可以定义列级别的字符集和校对规则，主要是针对相同的表不同字段需要使用不同的字符集的情况，应该说一般遇到这种情况的几率比较小，这只是MySQL提供给我们一个灵活设置的手段。

连接字符集和校对规则

对于客户端和服务器的交互操作，MySQL提供了3中不同的参数：character_set_client、character_set_connection和character_set_results，分别代表客户端、连接和返回结果的字符集。通常情况下，这3个字符集应该是相同的，才可以确保用户写入的数据可以正确地读出。

通常情况下，不会单个设置这3个参数，可以通过以下命令:

SET NAMES ***

这个命令可以同时修改这3个参数的值。使用这个方法修改连接的字符集和校对规则，需要应用每次连接数据库后都执行这个命令。

另一个更简便的方法，是在my.cnf中设置以下语句：

[mysql]
default-character-set=gbk

字符集的修改步骤

(1) 导出表结构

  1. mysqldump -uroot -p --default-character-set=gbk -d databasename > create.sql

    其中--default-character-set=gbk表示设置以什么字符集连接，-d表示只导出表结构，不导出数据。

(2) 手工修改create.sql中表结构定义中的字符集为新的字符集。

(3) 确保记录不再更新，导出所有记录。

  1. msyqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 databasename > data.sql
    • --quick：该选项用于转储大的表，它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行，并在输出前将它缓存到内存中。
    • --extended-insert：使用包含几个VALUES列表的多行INSERT语法，这样使转储文件更小，重载文件时可以加速插入。
    • --no-craete-info：不导出每个转储表的CREATE TABLE语句。
    • --default-character-set=latin1：按照原有的字符集导出所有数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码。

(4) 打开data.sql，将SET NAMES latin1修改成SET NAMES gbk。

(5) 使用新的字符集创建新的数据库

create database databasename default charset gbk;

(6) 创建表，执行create.sql

mysql -uroot -p databasename < craete.sql

(7) 导入数据，执行data.sql

mysql -uroot -p databasename < data.sql

索引的设计和使用

索引的概述

MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引。MySQL目前还不支持函数索引，但是支持前缀索引，即对索引的前N个字符创建索引。前缀索引的长度跟存储引擎相关，对于MyISAM存储引擎的表，索引的前缀长度可以达到1000字节长，而对于InnoDB存储引擎的表，索引的前缀长度最长是767字节。请注意前缀的限制应以字节为单位进行测量，而CREATE TABLE语句中的前缀长度解释为字符数。

创建索引的语法

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type]
ON table_name (index_col_name,...)
index_col_name: col_name [(length)] [ASC|DESC]

索引的删除语法为

DROP IDNEX index_name ON tab_namne

设计索引的原则

  • 最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。
  • 使用唯一索引。索引的列的基数越大，索引的效果越好。
  • 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。较小的索引涉及的磁盘IO较少，较短的值比较起来更快。
  • 利用最左前缀。
  • 不要过度索引。
  • 对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，所以InnoDB表尽量自己指定主键。InnoDB表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果。

BTREE索引和HASH索引

HASH索引有一些重要的特征需要在使用的时候特别注意，如下所示。

  • 只用于使用=或者<=>操作符的等式比较。
  • 优化器不能使用HASH索引来加速ORDER BY操作
  • MySQL不能确定在两个值之间大约有多少行。如果将一个MyISAM表改为HASH索引的MEMORY表，会影响一些查询的执行效率。
  • 只能使用整个关键字来搜索一行。

而对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE 'pattern'（其中'pattern'不以通配符开始）操作符时，都可以使用相关列上的索引。

视图

什么是视图

视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。

视图操作

创建或者修改视图

创建视图需要有CREATE VIEW的权限，并且对于查询涉及的列有SELECT权限。如果使用CREATE OR REPLACE或者ALTER修改视图，那么还需要该视图的DROP权限。

-- 创建视图
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH[CASCADED|LOCAL]CHECK OPTION]
-- 修改视图
ALTER [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH[CASCADED|LOCAL]CHECK OPTION]

视图的可更改性和视图中查询的定义有关系，以下类型的视图是不可更新的。

  • 包含以下关键字的SQL语句：聚合函数（SUM、MIN、MAX、COUNT等）、DISTINCT、GROUP BY、HAVING、UNION、UNION ALL
  • 常量视图
  • SELECT中包含子查询
  • JOIN
  • FROM一个不能更新的视图
  • WHERE子句的子查询引用了FROM子句中的表
删除视图

用户可以一次删除一个或者多个视图，前提是必须有该视图的DROP权限。

DROP VIEW [IF EXISTS] view_name[,view_name]...[RESTRICT|CASCADE]
查看视图

从MySQL5.1版本开始，使用SHOW TABLES命令的时候不仅显示表的名字，同时也会显示视图的名字。

SHOW TABLES

同样，在使用SHOW TABLES STATUS命令的时候，不但可以显示表的信息，同时也可以显示视图的信息。

SHOW TABLES STATUS [FROM db_name] [LIKE 'pattern']

存储过程和函数

什么是存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的。

事务控制和锁定语句

LOCK TALBE 和 UNLOCK TABLE

LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定位置。

UNLOCK TALBES可以释放当前线程获得的任何锁定。当前线程执行另一个LOCK TABLES时，或当服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁。

LOCK TABLES
	tal_name [AS alias] {READ[LOCAL]|[LOW_PRIORITY] WRITE}
	[,tal_name [AS alias] {READ[LOCAL]|[LOW_PRIORITY] WRITE}]...
UNLOCK TABLES

事务控制

MySQL通过SET AUTOCOMMIT、START TRANSACTION、COMMIT和ROLLBACK等语句支持本地事务，具体语法如下：

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN][[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN][[NO] RELEASE]
SET AUTOCOMMIT={0|1}

默认情况下，MySQL是自动提交（AutoCommit）的，如果需要通过明确的Commit喝Rollback来提交和回滚事务，那么就需要通过明确的事务控制命令来开始事务，这是和Oracle事务管理明显不同的地方。

  • START TRANSACTION或BEGIN语句可以开始一项新的事务
  • COMMIT和ROLLBACK用来提交或者回滚事务
  • CHAIN和RELEASE子句分别用来定义在事务提交或者回滚之后的操作，CHAIN会立即启动一个新事务，并且和刚才的事务具有相同的隔离级别，RELEASE则会断开和客户端的连接。
  • SET AUTOCOMMIT可以修改当前连接的提交方式，如果设置了SET AUTOCOMMIT=0，则设置之后的所有事务 0需要通过明确的命令进行提交或者回滚。

在锁表期间，用 START TRANSACTION命令开始一个新事务，会造成一个隐含地UNLOCK TABLES被执行。

MySQL分区

分区概述

分区引入了分区键（partition key）的概念，分区键用来根据某个区间值（或者范围值）、特定值列表或者HASH函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象变成一些小对象。

可以通过SHOW VARIABLES命令来确定当前的MySQL是否支持分区

SHOW VARIABLES LIEK '%partition%'

分区类型

在MySQL5.1中可用的分区类型，主要有以下4种。

  • RANGE分区：基于一个给定连续区间范围，把数据分配到不同的分区。
  • LIST分区：类似RANGE分区，区别在LIST分区是基于枚举出的值列表分区，RANGE是基于给定的连续区间范围分区。
  • HASH分区：基于给定的分区个数，把数据分配到不同的分区。
  • KEY分区：类似于HASH分区。

在MySQL5.1版本中，RANGE分区、LIST分区、HASH分区都要求分区键必须是INT类型，或者通过表达式返回INT类型。无论哪种MySQL分区类型，不能使用主键/唯一键字段之外的其他字段分区。

RANGE分区

CREATE TABLE emp(
	id INT NOT NULL,
	store_id INT NOT NULL
)PARTITION BY RANGE(store_id)(
	PARTITION p0 VALUES LESS THEN (10),
	PARTITION p1 VALUES LESS THEN (20),
	PARTITION p2 VALUES LESS THEN (30)
);

LIST分区

CREATE TABLE expenses(
	expense_date DATE NOT NULL,
	category INT
)PARTITION BY LIST(category)(
	PARTITION p0 VALUES IN(3, 5),
	PARTITION p1 VALUES IN(1, 10),
	PARTITION p2 VALUES IN(4, 9),
  PARTITION p3 VALUES IN(2),
  PARTITION p4 VALUES IN(6)
);

COLUMNS分区

Columns分区是MySQL5.5引入的分区类型，引入Columns分区解决了MySQL5.5版本之前RANGE分区和LIST分区只支持整型分区，从而导致需要额外的函数计算得到整数或通过额外的转换表来转换为整数再分区的问题。Columns分区可以细分为RANGE Columns分区和LIST Columns分区，RANGE Columns分区和LIST Columns分区都支持整数、日期时间、字符串三大数据类型。

对比RANGE分区和LIST分区，Columns分区的亮点除了支持数据类型增加之外，另外，一大亮点是Columns分区还支持多列分区。

CREATE TABLE r3(
	a INT,
	b INT
)PARTITION BY RANGE COLUMNS(a, b)(
	PARTITION p01 VALUES LESS THAN (0, 10),
	PARTITION p02 VALUES LESS THAN (10, 10),
	PARTITION p03 VALUES LESS THAN (10, 20),
	PARTITION p04 VALUES LESS THAN (10, 35),
	PARTITION p05 VALUES LESS THAN (10, MAXVALUE),
	PARTITION p06 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

HASH分区

HASH分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。

MySQL支持两种HASH分区，常规HASH分区和线性HASH分区；常规HASH使用的是取模算法，线性HASH分区使用的是一个线性的2的幂的运算法则。

-- 常规HASH分区
CREATE TABLE emp(
	id INT NOT NULL,
	store_id INT NOT NULL
)PARTITION BY HASH(store_id) PARTITIONS 4;

-- 线性HASH
CREATE TABLE emp(
	id INT NOT NULL,
	store_id INT NOT NULL
)PARTITION BY LINEAR HASH(store_id) PARTITIONS 4;

KEY分区

按照Key进行分区非常类似于按照HASH进行分区，只不过HASH分区允许使用用户自定义的表达式，而Key分区不允许使用用户自定义的表达式，需要使用MySQL服务器提供的HASH函数；同时HASH分区只支持整数分区，而Key分区支持使用除BLOB或Text类型外其他类型的列作为分区键。

CREATE TABLE emp(
	id INT NOT NULL,
	store_id INT NOT NULL,
  job VARCHAR(30) NOT NULL
)PARTITION BY KEY (job) PARTITIONS 4;

创建Key分区表的时候，可以不指定分区键，默认会首先选择使用主键作为分区键，在没有主键的情况，会选择非空唯一键作为分区键。

MySQL分区处理NULL值的方式

MySQL不禁止在分区键值上使用NULL，分区键可能是一个字段或者一个用户定义的表达式。一般情况下，MySQL的分区把NULL当做零值，或者是一个最小值进行处理。

注意：RANGE分区中，NULL值会被当作最小值来处理；LIST分区中，NULL值必须出现在枚举列表中，否则不被接受；HASH/KEY分区中，NULL值会被当作零值来处理。

优化篇

SQL优化

优化SQL语句的一般步骤

  1. 通过 show status 命令了解各种SQL的执行频率
    show [session|global] status

    下面的命令显示了当前session中所有统计参数的值：

    mysql> show status like 'Com_%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Com_admin_commands                  | 0     |
| Com_assign_to_keycache              | 0     |
| Com_alter_db                        | 0     |
| Com_alter_event                     | 0     |
| Com_alter_function                  | 0     |
| Com_alter_instance                  | 0     |
| Com_alter_procedure                 | 0     |
| Com_alter_resource_group            | 0     |
| Com_alter_server                    | 0     |
| Com_alter_table                     | 0     |
| Com_alter_tablespace                | 0     |
| Com_alter_user                      | 0     |
| Com_alter_user_default_role         | 0     |
| Com_analyze                         | 0     |
| Com_begin                           | 0     |
| Com_binlog                          | 0     |
| Com_call_procedure                  | 0     |
| Com_change_db                       | 1     |
| Com_change_master                   | 0     |
| Com_change_repl_filter              | 0     |
| Com_check                           | 0     |
...

    Com_xxx表示每个xxx语句执行的次数，通常比较关心的是以下几个统计参数：

    • Com_select：执行SELECT操作的次数，一次查询只累加1.
    • Com_insert：执行INSERT操作的次数，对于批量插入的INSERT操作，只累加一次。
    • Com_update：执行UPDATE操作的次数。
    • Com_delete：执行DELETE操作的次数。

    上面这些参数对于所有存储引擎的表操作都会进行累加。下面这几个参数只是针对InnoDB存储引擎的，累加的算法也略有不同。

    • Com_rows_read：SELECT查询返回的行数。
    • Com_rows_inserted：执行INSERT操作插入的行数。
    • Com_rows_updated：执行UPDATE操作更新的行数。
    • Com_rows_deleted：执行DELETE操作删除的行数。

    通过以上几个参数，可以很容易地了解当前数据库是以插入更新为主还是以查询操作为主。

    对于事务型的应用，可以通过Com_commit和Com_rollback可以了解事务和回滚的情况。

    通过以下几个参数可以了解数据库的基本情况：

    • Connections：试图连接MySQL服务器的次数。
    • Uptime：服务器工作时间
    • Slow_queries：慢查询的次数。
  2. 定位执行效率较低的SQL语句
    • 通过慢查询日志定位那些执行效率较低的SQL语句，用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
    • 慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。
  3. 通过EXPLAIN分析低效SQL的执行计划
    • select_type：表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。
    • table：输出结果集的表
    • type：表示MySQL在表中找到所需行的方式，或者叫访问类型
      • type = ALL，全表扫描，MySQL遍历全表来找到匹配的行。
      • type = index，索引扫描，MySQL遍历整个索引来找到匹配的行。
      • type = range，索引范围扫描，常见于<、<=、>、>=、between等
      • type = ref，使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行。
      • type = eq_ref，类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是多表连接中使用primary key或者unique index作为关联条件。
      • type = const/system，单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键primary key或者唯一索引unique index进行的查询。
      • type = NULL，MySQL不用访问表或者索引，就能直接得到结果。
      • type还有其他值，如ref_or_null（与ref类似，区别在于条件中包含对NULL的查询）、index_range（索引合并优化）、unique_subquery（in的后面是一个查询主键字段的子查询）、index_subquery（与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询)
    • possible_keys：表示查询时可能使用的索引。
    • key：表示实际使用的索引。
    • key_len：使用到的索引字段的长度
    • rows：扫描行的数量
    • Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

    MySQL4.1开始引入explain extended命令，通过explain extended加上show warnings，能够看到SQL真正被执行之前优化器做了哪些SQL改写。

    MySQL5.1开始支持分区功能，同时explain命令也增加了对分区的支持。可以通过explain partitions命令查看SQL所访问的分区。

  4. 通过show profile分析SQL

    通过have_profiling参数，查看当前MySQL是否支持profile。

    select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+

    默认profiling是关闭的，可以通过set语句在session级别开启profiling：

    select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0           |
+-------------+
set profiling = 1;

    通过show profiles语句，查看执行SQL的Query ID

    show profiles;
+----------+----------+-----------------------------+
| Query_ID | Duration | Query                       |
+----------+----------+-----------------------------+
| 1        | 8.7e-05  | SHOW WARNINGS               |
| 2        | 0.000141 | select @@profiling          |
| 3        | 6.8e-05  | SHOW WARNINGS               |
| 4        | 0.000324 | select * from customer_part |
| 5        | 5.2e-05  | SHOW WARNINGS               |
| 6        | 4.4e-05  | SHOW WARNINGS               |
| 7        | 0.000199 | select @@have_profiling     |
| 8        | 7.5e-05  | SHOW WARNINGS               |
+----------+----------+-----------------------------+

    通过show profile for query {queryId}语句能够看到执行过程中线程的每个状态和消耗时间

    show profile for query 4;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000077 |
| Executing hook on transaction  | 0.000009 |
| starting                       | 0.000008 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000033 |
| init                           | 0.000006 |
| System lock                    | 0.000009 |
| optimizing                     | 0.000005 |
| statistics                     | 0.000013 |
| preparing                      | 0.000014 |
| executing                      | 0.000052 |
| end                            | 0.000005 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000008 |
| closing tables                 | 0.000008 |
| freeing items                  | 0.000024 |
| cleaning up                    | 0.000044 |
+--------------------------------+----------+

    MySQL支持进一步选择all、cpu、block io、context、switch、page faults等明细类型来查看MySQL在使用什么资源上耗费了过高的时间

    show profile cpu for query 1;
  5. 通过trace分析优化器如何选择执行计划

    MySQL5.6提供了对SQL跟踪trace。

    使用方式：首先打开trace，设置格式为JSON，设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。

    SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

    接下来执行想要trace的SQL语句

    select rental_id from rental where rental_date >= '2005-05-25 04:00:00' and rental_date <= '2005-05-25 05:00:00' and inventory_id = 5566;

    最后检查INFORMATION_SCHEMA.OPTIMIZER_TRACE就可以知道MySQL是如何执行SQL的

    SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;

索引问题

索引的存储分类

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。MySQL目前提供以下4种索引：

  • B-Tree索引
  • HASH索引：只有Memory引擎支持
  • R-Tree索引（空间索引）：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型。
  • Full-text（全文索引）：全文索引也是MyISAM的一个特殊索引类型。

前缀索引也有缺点，在排序Order By和分组Group By操作的时候无法使用。

MySQL如何使用索引
MySQL中能够使用索引的典型场景
  1. 匹配全值（Match the full value），对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件。
  2. 匹配值的范围查询（Match a range of values），对索引的值能够进行范围查找。
  3. 匹配最左前缀（Match a leftmost prefix），仅仅使用索引中的最左边列进行查找。
  4. 仅仅对索引进行查询（Index only query），当查询的列都在索引的字段中时，查询的效率更高。
  5. 匹配列前缀（Match a column prefix），仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。
  6. 能够实现索引匹配部分精确而其他部分进行范围匹配（Match one part exactly and match a range on another part）。
  7. 如果列名是索引，那么使用column name is null就会使用索引（区别于Oracle）。
  8. MySQL5.6引入了Index Condition Pushdown(ICP)的特性，进一步优化了查询。Pushdown表示操作下放，某些情况下的条件过滤操作下放到存储引擎。之前操作是通过索引回表获取记录后，再根据条件来过滤出最后的查询结果；下放后，在索引返回指针的时候已经过滤掉了，这样能够降低不必要的IO访问。
存在索引不能使用索引的典型场景
  1. 以%开头的LIKE查询不能利用B-Tree索引
  2. 数据类型出现隐式转换的时候也不会使用索引
  3. 复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则Leftmost，是不会使用复合索引。
  4. 如果MySQL估计使用索引比全表扫描慢，则不使用索引。
  5. 使用or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会被用到。因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加IO访问，一次全表扫描过滤条件就足够了。
查看索引使用情况

如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。

show status like 'Handler_read%';
两个简单实用的优化方法

  1. 定期分析表和检查表

    -- 分析表
ANLYZE [LOCAL|NO_WRITE_TO_BINLOG] TALBE tbl_name[,tbl_name]...

-- 检查表
CHECK TALBE tbl_name[,tbl_name]...[option]...option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}

    在分析期间，使用一个读取锁定对表进行锁定。

  2. 定期优化表

    OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]...

    如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行了很多更改，则应实用OPTIMIZE TABLE命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的表空间浪费，但OPTIMIZE TABLE命令只对MyISAM、BDB和InnoDB表起作用。

常用SQL的优化
大批量插入

当用load命令导入数据的时候，适当的设置可以提高导入的速度。

对于MyISAM存储引擎的表，可以通过以下方式快速地导入大量的数据。

ALTER TALBE tbl_name DISABLE KEYS;
loading the data
ALTER TABLE tbl_name ENABLE KEYS;

DISABLE KEYS和ENABLE KEYS用来打开或者关闭MyISAM表非唯一索引的更新。这种方式是对MyISAM表进行数据导入时的优化措施。

有以下几种方式提高InnoDB表的导入效率。

  1. 因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
  2. 在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
  3. 如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
优化INSET语句

当进行数据INSERT的时候，可以考虑采用以下几种优化方式。

  1. 如果同时从同一客户插入很多行，应尽量使用多个值表的INSERT INTO tlb_name VALUES(), (), ..., ()语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个INSERT语句快（在大部分情况下，使用多个值表的INSERT语句能比单个INSERT语句快上好几倍）。
  2. 如果从不同客户插入很多行，可以通过使用ISNERT DELAYED语句得到更高的速度。DELAYED的含义是让INSERT语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快得多；LOW_PRIORITY刚好相反，在所有其他用户对表的写完成后才进行插入。
  3. 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）
  4. 如果进行批量插入，可以通过增加bulk_insert_buffer_size变量值得方法来提高速度，但是，这只能对MyISAM表使用。
  5. 当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。
优化ORDER BY语句

MySQL中有两种排序方式

第一种通过有序索引顺序扫描直接返回有序数据，这种方式在使用explain分析查询的时候显示为Using index，不需要额外的排序，操作效率较高。

第二种是通过对返回数据进行排序，也就是通常说的FileSort排序，所有不是通过索引直接返回排序结果的排序都叫Filesort排序。Filesort并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或临时表等，则取决于MySQL服务器对排序参数的设置和需要排序数据的大小。

Filesort是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。sort_buffer_size设置的排序区是每个线程独占的，所以同一时刻，MySQL中存在多个sort_buffer_size排序区。

优化GROUP BY语句

如果查询包括GROUP BY但用户想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。

优化嵌套查询

在某些情况下，子查询可以被更有效率的连接(JOIN)替代。

连接(JOIN)之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

MySQL如何优化OR条件

对于含有OR的查询子句，如果要利用索引，则OR之间的每个条件都必须用到索引；如果没有索引，则应该考虑增加索引。

优化分页查询

  1. 第一种优化思路

    在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。

  2. 第二种优化思路

    把LIMIT查询转换为某个位置的查询。例如，分页的时候，增加last_page_record，用来记录上一页最后一行的id。

    注意，这样把LIMIT m, n转换成LIMIT n的查询，只适合在排序字段不会出现重复值的特定环境下，能够减轻分页翻页的压力；如果排序字段出现大量重复值，而仍进行这种优化，那么分页结果可能会丢失部分记录，不适用这种方式优化。

适用SQL提示

  1. USE INDEX

    在查询语句中表名的后面，添加USE INDEX来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

    mysql> explain select count(*) from rental use index(idx_rental_date)；

  2. IGNORE INDEX

    让MySQL忽略一个或者多个索引，则可以使用IGNORE INDEX作为HINT。

    mysql> explain select count(*) from rental ignore index(idx_rental_date);

  3. FORCE INDEX

    强制MySQL使用一个特定的索引，可在查询中使用FORCE INDEX作为HINT。

    mysql> explain select * from rental force index(idx_fk_inventory_id) where inventory_id > 1;

优化数据库对象

优化表的数据类型

在MySQL中，可以使用函数PROCEDURE ANALYSE()对当前应用的表进行分析，该函数可以对数据表中列的数据类型提出优化建议。

SELECT * FROM tbl_name PROCEDURE ANALYSE();
-- 不要为那些包含的值多于16个或者256个字节的ENUM类型提出建议
SELECT * FROM tbl_name PROCEDURE ANALYSE(16, 256);

通过拆分提高表的访问效率

  1. 垂直拆分

    如果一张表中某些列常用，某些列不常用，可以使用垂直拆分，另外，垂直拆分可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少I/O次数。

    缺点是，需要管理冗余列，查询所有数据需要联合(JOIN)操作。

  2. 水平拆分

    根据一列或多列数据的值把数据行放到两个独立的表中。

逆规范化

反规范的好处是降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目，相应带来的问题是可能出现数据的完整性问题。加快查询速度，但会降低修改速度。

常用的范规范技术有增加冗余列、增加派生列、重新组表和分割表。

  • 增加冗余列：指在多个表中具有相同的列，它常用来在查询时避免连接操作。
  • 增加派生列：指增加的列来自其他表中的数据，由其他表中的数据经过计算生成。
  • 重新组表：指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。
  • 分割表

另外，逆规范化技术需要维护数据的完整性。常用的方法是批处理维护、应用逻辑和触发器。

使用中间表提高统计查询速度

对于数据量较大的表，在其上进行统计查询通常会效率很低，并且要考虑统计查询是否会对在线的应用产生负面影响。通常在这种情况下，使用中间表可以提高统计查询的效率。

锁问题

MySQL锁概述

MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；DBD存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下采用行级锁。

MySQL这3种锁的特性可大致归纳如下：

  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  • 页面锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

MyISAM表锁

MyISAM存储引擎只支持表锁。

查询表级锁争用情况
mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 3     |
| Table_locks_waited         | 0     |
+----------------------------+-------+

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

MySQL表级锁的锁模式

MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

如何加表锁

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预。

显示加锁与解锁

-- 加读锁
lock table tbl_name read [local];
lock talbes tbl1_name read local, tbl2_name read local;
-- 写锁
lock table tbl_name write;
-- 解锁
unlock tables;
  • local选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录。
  • 在用LOCK TABLES给表显式加锁时，必须同时取得所有涉及表的锁，并且MySQL不支持锁升级。
  • 当用LOCK TABLES时，不仅需要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错！
并发插入

MyISAM表也支持查询和插入操作的并发进行。

MyISAM存储引擎有一个系统变量concurrent_insert，专门用于控制其并发插入的行为，其值分别可以为0、1或2。

  • 0：不允许并发插入。
  • 1：如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从尾部插入记录。这也是MySQL的默认设置。
  • 2：无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
MyISAM的锁调度

MyISAM存储引擎的读锁和写锁都是互斥的，读写操作是串行的。那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一个表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求更重要。这也是MyISAM表不太适合有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得写锁，从而可能永远阻塞。

可以通过一些设置来调节MyISAM的调度行为。

  • 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
  • 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
  • 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

InnoDB锁问题

InnoDB支持事务，采用了行级锁。

事务

事务是由一组SQL语句组成的逻辑处理单元，事务具有4个属性：原子性(Atomicity)、一致性(Consistent)、隔离性(Isolation)、持久性(Durable)。

并发事务带来的问题

  • 更新丢失（Lost Update）:当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题。
  • 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交之前，这个记录的数据就处于不一致状态；另一个事务读取到了这些“脏”数据，就会产生读未提交的数据。
  • 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变或某些记录已经被删除了！这种现象就叫“不可重复读”。
  • 幻读（Phantom Reads）：一个事务按照相同的查询提交重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

事务隔离级别

数据库实现事务隔离的方式，基本上可分为以下两种。

  • 在读取数据前，对其加锁，阻止其他事务对数据进行修改。
  • 不加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制(Multi Version Concurrency Control，简称MVCC或MCC)，也经常成为多版本数据库。

4种隔离性比较

隔离级别\读数据一致性及并发副作用 读数据一致性 脏读 不可重复读 幻读
未提交读（Read uncommitted） 最低级别，只能保证不读取物理上损坏的数据 是 是 是
已提交读（Read committed） 语句级 否 是 是
可重复读（Repeatable read） 事务级 否 否 是
可序列化（Serializable） 最高级别，事务级 否 否 否
获取InnoDB行锁争用情况

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+

如果锁争用情况比较严重，InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，可以通过查询 infomation_schema 数据库中相关的表来查看锁情况，或通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

InnoDB的行锁模式及加锁方法

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

  • 共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  • 排他锁(X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同的数据集的共享读锁和排他写锁。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁(Intention Locks)，这两种意向锁都是表锁。

  • 意向共享锁(IS)：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  • 意向排他锁(IX)：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

上述锁模式的兼容情况具体如下:

当前锁模式\是否兼容\请求锁模式 X IX S IS
X 冲突 冲突 冲突 冲突
IX 冲突 兼容 冲突 兼容
S 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁(X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

  • 共享锁(S)：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
  • 排他锁(X)：SELECT * FROM table_name WHERE ... FOR UPDATE

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

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB行锁分为3种情形。

  • Record lock：对索引加锁。
  • Gap lock：对索引之间的“间隙”、第一条记录前的“间隙”或最后一条记录的“间隙”加锁。
  • Next-key lock：前两种的组合，对记录及其前面的间隙加锁。

InnoDB这种行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。

注意以下几点：

  1. 在不通过索引条件查询时，InnoDB会锁定表中的所有记录。
  2. 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
  3. 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
  4. 即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL不使用索引，这种情况下InnoDB也会对所有记录加锁。因此，在分析锁冲突时，别忘记了检查SQL的执行计划，以确认是否真正使用了索引。
Next-Key锁

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

举例来说，假如emp表中只有101条记录，其empid的值分别是1、2、...、100、101，下面的SQL:

select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用Next-Key锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求；另一方面，是为了满足其恢复和复制的需要。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用Next-Key锁外，如果使用相等条件请求一个不存在的记录加锁，InnoDB也会使用Next-Key锁！

恢复和复制的需要，对InnoDB锁机制的影响

MySQL通过BINLOG记录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复制。MySQL 5.6 支持3种日志格式，即基于语句的日志格式SBL、基于行的日志格式RBL和混合格式。还支持4种复制模式。

  1. 基于SQL语句的复制SBR：这也是MySQL最早支持的复制模式。
  2. 基于行数据的复制RBR：这是MySQL5.1以后开始支持的复制模式，主要优点是支持对非安全SQL的复制。
  3. 混合复制模式：对安全的SQL语句采用基于SQL语句的复制模式，对于非安全的SQL语句采用居于行的复制模式。
  4. 使用全局事务ID（GTIDs）的复制：主要是解决主从自动同步一致性问题。

对基于语句日志格式（SBL）的恢复和复制而言，由于MySQL的BINLOG是按照事务提交的先后顺序记录的，因此要正确恢复或复制数据，就必须满足：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。这已经超过了ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到Next-Key锁的原因，比如在用范围条件更新记录时，无论实在Read Commited或是Repeatable Read隔离级别下，InnoDB都要使用Next-Key锁，但这并不是隔离级别要求的。

ISNERT ... SELECT ... 和 CREATE TALBE ... SELECT ..语句，可能会阻止对源表的并发更新。如果查询比较复杂，会造成严重的性能问题，读者在应用中应尽量避免使用。实际上，MySQL将这种SQL叫做不确定(non-deterministic)的SQL，属于“Unsafe SQL”，不推荐使用。

什么时候使用表锁
  • 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
  • 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。

在InnoDB下，使用表锁要注意以下两点。

  • 使用LOCK TABLES虽然可以给InnoDB叫表锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层——MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能自动识别涉及表锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。
  • 在用LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表锁，必须用UNLOCK TABLES释放表锁。
关于死锁

MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB中发生死锁是有可能的。

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，设置拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

避免死锁的常用方法：

  1. 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
  2. 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
  3. 在事务中，如果需要更新记录，应该直接申请足够级别的锁，即排他锁，而不应该先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
  4. 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT ... FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。将隔离级别改成READ COMMITTED，就可避免问题。
  5. 当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT ... FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果出现第3个线程又来申请排他锁，也会出现死锁。
avatar
文章
阅读
粉丝