MySQL笔记(全)

PunkLu 2020年01月29日 281次浏览
MySQL支持的数据类型

基础

SQL基础

SQL分类

SQL语句主要可划分为以下3个类别:

  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语句

mysql -u用户名 -p密码

create database 数据库名;

安装mysql时系统自动创建的四个数据库:

  1. information_schema

    主要存储了系统中的一些数据库对象信息,比如用户表信息、列信息、权限信息、字符集信息、分区信息等。

  2. cluster

    存储了系统的集群信息

  3. mysql

    存储了系统的用户权限信息

  4. test

    系统自动创建的测试数据库

use 数据库名:选择要操作的数据库

show tables:查看当前数据库下的所有表

drop database 数据库名:删除数据库

create table 表名(字段名 字段类型,...):创建数据库表

DESC 表名:查看表的定义

show create table 表名:查看建表的具体SQL

ALTER TABLE 表名 MODIFY 字段名 字段类型:修改表中的字段的类型。

ALTER TABLE 表名 ADD COLUMN 字段名 字段类型:增加字段

ALTER TABLE 表名 DROP COLUMN 字段名:删除字段

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 字段类型:修改字段名及字段类型

ALTER TABLE 表名 ADD 字段名 字段类型  FIRST/AFTER 字段名1:在字段名1的前面或后面添加一个字段类型,名为字段名的字段。

ALTER TABLE 表名 MODIFY 字段名 字段类型 FIRST:修改字段的字段类型并将其排在列的最前边

ALTER TABLE 表名 rename 表名1:修改表名

DML语句

DML操作是指对数据库中表记录的操作。

insert into 表名 (字段1,字段2,字段3) values (值1,值2,值3),(值a,值b,值c):插入数据

update 表a a,表 b set a.字段名 = '值a',b.字段名 = '值b' where a.字段名 = b.字段名:同时更新多表数据,多用在根据一个表的字段来动态更新另外一个表的数据

DELETE FROM 表名 WHERE 条件:删除,也可以像更新一样,动态删除多表中的数据

SELECT DISTINCT 字段名 FROM 表名:将表中的的某个字段名去掉重复后查出来

DESC:降序,ASC:升序 ,默认按升序排列,ORDER BY 之后可以跟多个不同的排序字段,并且每个排序字段可以有不同的排序顺序。如果第一个排序字段的值一样,则值相同的字段按照第二个排序字段进行排序,依次类推。如果只有一个排序字段,则这些字段相同的记录将会无序排列。

SELECT * FROM 表名 ORDER BY 字段名 LIMIT 3:查询3条结果

SELECT * FROM 表名 ORDER BY 字段名 LIMIT 1,3:显示从第二条记录开始的3条记录

聚合操作:

1、聚合函数,常用的有sum(求和)、count(*)记录数、max(最大值)、min(最小值)

2、GROUP BY:要进行分类聚合的字段,比如要按照部门分类统计员工数量,部门就应该写在group by后面

3、WITH ROLLUP:可选语法,表名是否对分类聚合后的结果进行再汇总

4、HAVING表示对分类后的结果再进行条件的过滤

having和where的区别在于,having是对聚合后的结果进行条件的过滤,而where是在聚合前就对记录进行过滤,如果逻辑允许,尽可能用where先过滤记录,这样因为结果集减小,将对聚合的效率大大提高,最后再根据逻辑看是否用having进行再过滤。

比如:

select count(1) from emp是查询公司的总人数

select deptno,count(1) from emp group by deptno:统计各个部门的人数

select deptno,count(1) from emp group by deptno with rollup:既统计各部门人数,又统计总人数

select deptno,count(1) from emp group by deptno having count(1) > 1:统计人数大于1的部门

表连接分为内连接和外连接,它们之间的最主要区别是,内连接仅选出两张表中互相匹配的记录,而外连接会选出其他不匹配的记录。

外连接分为左连接和右连接:

1、左连接

包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录

2、右连接

包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录

使用union和union all可以将两个表的数据按照一定的条件查询出来后,将结果合并到一起显示出来。

UNION和UNION ALL的主要区别是UNION ALL是把结果集直接合并在一起,而UNION是将UNION ALL后的结果进行一次DISTINCT,去除重复记录后的结果。

DCL语句

DCL语句主要是DBA用来管理系统中的对象权限时使用。

grant seect,insert on sakila.* to 'z1'@'localhost' identified by '123':创建一个数据库用户z1,具有对sakila数据库中所有表的SELECT/INSERT权限

revoke insert on sakila.* from 'z1'@'localhost':收回上面授予的z1用户对sakila数据的insert权限。

数据类型

数值类型

MySQL支持所有标准SQL中的数值类型,其中包括严格数值类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL、和DOUBLE PRECISION),并在此基础上做了扩展。扩展后增加了TINYINT、MEDIUMINT和BIGINT这三种长度不同的整型,并增加了BIT类型,用来存放位数据。

日期时间类型

MySQL中日期数据类型的区别:

  1. 如果表示年月日,通常用DATE来表示
  2. 如果用来表示年月日时分秒,通常用DATETIME表示
  3. 如果只用来表示时分秒,通常用TIME表示

TIMESTAMP有一个重要特点,就是和时区相关。当插入TIMESTAMP数据类型的数据时,会先转换为本地时区后存放。而从数据库里取出时,也同样需要将日期转换为本地时区后显示。这样,两个不同的时区的用户看到的同一个日期可能是不一样的。并且TIMESTAMP的取值范围是19700101080001到2038年的某一天,因此不适合存放比较久远的日期。当超出这个范围时,系统会自动置为零值。

字符串类型

CHAR和VARCHAR类型,二者的区别在于:

  1. CHAR列的长度固定为创建表时声明的长度,长度可以为0-255的任何值。而VARCHAR的值为可变长字符串,长度可以指定为0-255或者65535之间的值。
  2. 在检索的时候,CHAR列删除了尾部的空格,而VARCHAR则保留这些空格。

MySQL中的运算符

算术运算符

  1. /
  2. % 取余

比较运算符

  1. =
  2. <>或!=
  3. <=> NULL安全的等于
  4. < 小于 同理 > 为大于
  5. <= 小于等于 同理 >= 为大于等于
  6. BETWEEN 存在于指定范围。包含两侧的值
  7. IN 存在于指定集合
  8. IS NULL 为NULL
  9. IS NOT NULL 不为NULL
  10. LIKE
  11. REGEXP或RLIKE 正则表达式匹配

逻辑运算符

  1. NOT 或 ! 逻辑非
  2. AND 或&& 逻辑与
  3. OR或 || 逻辑或
  4. XOR 逻辑异或

常用函数

字符串函数

  1. CONCAT(S1,S2,...Sn):连接S1,S2,....Sn为一个字符串
  2. INSERT(str,x,y,instr):将字符串str从第x位置开始,y个字符长的子串替换为字符串instr
  3. LOWER(str):将字符串str中所有字符变为小写
  4. UPPER(str):将字符串str中所有字符变成大写
  5. LEFT(str,x):返回字符串str最左边的x个字符
  6. RIGHT(str,x):返回字符串str最右边的x个字符
  7. LPAD(str,n,pad):用字符串pad对str最左边进行填充,直到长度为n个字符长度
  8. RPAD(str,n,pad):用字符串pad对str最右边进行填充,直到长度为n个字符长度
  9. LTRIM(str):去掉字符串str左侧的空格
  10. RTRIM(str):去掉字符串str行尾的空格
  11. REPEAT(str,x):返回str重复x次的结果
  12. REPLACE(str,a,b):用字符串b替换字符串str中所有出现的字符串a
  13. STRCMP(s1,s2):比较字符串s1和s2
  14. TRIM(str):去掉字符串行尾和行头的空格
  15. SUBSTRING(str,x,y):返回从字符串str x位置起y个字符长度的字串

数值函数

  1. ABS(x):返回x的绝对值
  2. CEIL(x):返回大于x的最小整数值
  3. FLOOR(x):返回小于x的最大整数值
  4. MOD(x,y):返回x/y的模
  5. RAND():返回0-1内的随机值
  6. ROUND(x,y):返回参数x的四舍五入的有y位小数的值
  7. TRUNCATE(x,y):返回数字x截断为y位小数的结果,只舍不入

日期和时间函数

  1. CURDATE():返回当前日期
  2. CURTIME():返回当前时间
  3. NOW():返回当前的日期和时间
  4. UNIX_TIMESTAMP(date):返回日期date的unit时间戳
  5. FROM_UNIXTIME:返回UNIX时间戳的日期值
  6. WEEK(date):返回日期date为一年中的第几周
  7. YEAR(date):返回日期date的年份
  8. HOUR(time):返回time的小时值
  9. MINUTE(time):返回time的分钟值
  10. MONTHNAME(date):返回date的月份名
  11. DATE_FORMAT(date,fmt):返回按照字符串fmt格式化日期date值
  12. DATE_ADD(date,INTERVAL expr type):返回一个日期或时间值加上一个时间间隔的时间值
  13. DATEDIFF(expr,expr2):返回起始时间expr和结束时间expr2之间的天数

流程函数

  1. IF(value,t,f):如果value是真,返回t,否则返回f
  2. IFNULL(value1,value2):如果value1不为空,返回value1,否则返回value2
  3. CASE WHEN [VALUE1] THEN [RESULT]....ELSE[DEFAULT] END:如果value1是真,返回RESULT,否则返回DEFAULT
  4. CASE [EXPR] WHEN [VALUE1] THEN [RESULT] ... ELSE[DEFAULT] END:如果EXPR等于VALUE1,返回RESULT,否则返回DEFAULT

开发

存储引擎的选择

和大多数数据库不同,MySQL中有一个存储引擎的概念,针对不同的存储需求可以选择最优的存储引擎。

创建新表时如果不指定存储引擎,系统会使用默认存储引擎,MySQL5.5之后默认存储引擎改为了InnoDB。

SHOW ENGINES:可以查看当前数据库支持的存储引擎。

可以使用ALTER TABLE 表名 engine = 存储引擎名的方式修改表的存储引擎。

MyISAM

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

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

  1. .frm(存储表定义)
  2. .MYD(MYData,存储数据)
  3. .MYI(MYIndex,存储索引)

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

要指定索引文件和数据文件的路径,需要在创建表的时候通过DATA DIRECTORY 和 INDEX DIRECTORY语句指定,也就是说不同MyISAM表的索引文件和数据文件可以放置到不同的路径下。文件路径需要是绝对路径,并且具有访问权限。

InnoDB

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引。

存储引擎为InnoDB的表在使用过程中不同于使用其他存储引擎的表的特点:

  1. 自动增长列

    InnoDB表的自动增长列可以手工插入,但是插入的值如果是空或者0,则实际插入的将是自动增长后的值。

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

  2. 外键约束

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

选择合适的数据类型

CHAR和VARCHAR

CHAR和VARCHAR类型类似,都用来存储字符串,CHAR属于固定长度的字符类型,而VARCHAR属于可变长度的字符类型。且如果MySQL运行在严格模式,超过列长度的值不会保存,并且会出现错提示。

由于CHAR是固定长度的,所以它的处理速度比VARCHAR快得多,但是其缺点是浪费存储空间,程序需要对行尾空格进行处理,所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用CHAR类型来存储。另外,随着MySQL版本的不断升级,VARCHAR数据类型的性能也在不断改进并提高。

在MySQL中,不同的存储引擎对CHAR和VARCHAR的使用原则有所不同:

  1. MyISAM存储引擎:建议使用固定长度的数据列代替可变长度的数据列
  2. MEMORY存储引擎:没有区别,两者都是作为CHAR类型处理
  3. InnoDB存储引擎:建议使用VARCHAR类型。对InnoDB数据表,内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),因此在本质上,使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而,主要的性能因素shi是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR,因此使用VARCHAR最好。

浮点数和定点数

MySQL中float、double用来表示浮点数。

定点数不同于浮点数,定点数实际上是以字符串形式存放的,所以定点数可以更精确地保存数据。MySQL中,decimal表示定点数。

定点数存在误差,对货币等对精度敏感的数据,应该用定点数表示或存储。在编程中,如果用到浮点数,要特别注意误差问题,并尽量避免做浮点数比较。

TEXT和BLOB

一般在保存少量字符串的时候,会选择CHAR或者VARCHAR。而在保存较大文本时,通常会选择使用TEXT或者BLOB。二者之间的主要差别是BLOB能用来保存二进制数据,比如照片;而TEXT只能保存字符数据。

日期类型选择

MySQL提供的常用日期类型有DATE、TIME、DATETIME、TIMESTAMP。

选择日期类型的原则:

  1. 根据实际需要选择能满足应用的最小存储的日期类型,如果应用只需要记录“年份”,那么用1个字节来存储的YEAR类型完全可以满足。
  2. 如果要记录年月日时分秒,并且记录的年份比较久远,那么最好使用DATETIME,而不要使用TIMESTAMP。因为TIMESTAMP表示的日期范围比DATETIME短得多。
  3. 如果记录的日期要让不同时区的用户使用,那么最好使用TIMESTAMP,TIMESTAMP可以和实际时区相对应。

字符集选择

  1. 如果需要处理各种而样的文字,或者将发布到不同语言的国家或地区,就应该选择UTF-8
  2. 如果只需要支持一般中文,数据量很大,性能要求很高,就应该选择双字节定长编码的中文字符集,比如GBK。因为,相对于UTF-8而言,GBK比较“小”,每个汉字只占两个字节,而UTF-8汉字编码需要3个字节,这样可以减少磁盘I/O、数据库CACHE及网络传输的时间,从而提高性能。相反,如果应用主要处理英文字符,仅有少量汉字数据,选择UTF-8更好。因为GBK的西文字符编码都是2个字节,会造成不必要的开销。

索引的使用

所有的MySQL列类型都可以被索引,对相关列使用索引是提高SELECT操作性能的最佳途径。MyISAM存储引擎和InnoDB存储引擎的表创建的都是BTREE索引。

创建索引:create  index cityname on city (city(10));
删除索引:drop index 索引名 on 表名

索引的设计原则:

  1. 最适合索引的列是出现在WHERE 子句中的列,或连接子句中指定的列,而不是SELECT 后的列
  2. 使用唯一索引。考虑某列中值的分布。索引的列的基数越大,索引的效果越好。
  3. 使用短索引。例如,有一个CHAR(200)列,如果在前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行索引,对前10个或20个字符进行索引能节省大量索引空间,使查询更快。
  4. 利用最左前缀。在创建一个n列的索引时,实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
  5. 不要过度索引,索引需要占据额度的磁盘空间,并降低写操作的性能。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。
  6. 对于InnoDB存储引擎的表,记录默认会按照一定的顺序保存,如果有明确定义的主键,则按照主键顺序保存。如果没有主键,但是有唯一索引,那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引,那么表中会自动生成一个内部列,按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的,所以InnoDB尽量自己指定主键,当表中同时有几个列都是唯一的,都可以作为主键的时候,要选择最常作为访问条件的列作为主键,提高查询效率。另外,还需要注意,InnoDB表的普通索引都会保存主键的键值,所以主键要尽可能选择较短的数据类型,可以减少磁盘占用,提高索引的缓存效果。

事务控制

MySQL支持对MyISAM和MEMORY存储引擎的表进行表级锁定,对InnoDB存储引擎的表进行行级锁定。默认情况下,表锁和行锁都是自动获得的,不需要额度的命令。但在某些情况下,用户需要明确地进行锁表或者进行事务的控制,以便确保整个事务的完整性,这样就需要使用事务控制和锁定语句来完成。

LOCK TABLES 和 UNLOCK TABLES

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

UNLOCK TABLES可以释放当前线程获得的任何锁定。当前线程执行另一个LOCK TABLES时,或当与服务器的连接被关闭时,所有由当前线程锁定的表被隐含地解锁。当某个表锁被当前线程锁定时,其他线程更新时会被阻塞等待。

事务控制

MySQL通过SET AUTOCOMMIT、START TRANSACTION、COMMIT和ROLLBACK等语句支持本地事务。

默认情况下,MySQL是自动提交(Autocommit)的,如果需要明确的Commit和Rollback来提交和回滚事务,那么就需要通过明确的事务控制命令来开始事务。

  1. START TRANSACTION或BEGIN语句可以开始一项新的事务
  2. COMMIT和ROLLBACK用来提交或者回滚事务
  3. CHAIN和RELEASE分别用来定义在事务提交或者回滚之后的操作,CHAIN会立即启动一个新事务,并且和刚才的事务具有相同的隔离级别,RELEASE则会断开和客户端的连接。使用方法:commit and chain(会将之前的SQL提交,并以相同的隔离级别开启一个新事务)
  4. SET AUTOCOMMIT = 1(或0)可以修改是否默认提交事务。

如果在锁表期间,用start transaction命令开始一个新事务,会造成一个隐含的unlock tables被执行。对lock方式加的表锁,不能通过rollback进行回滚。因此,在同一个事务中,最好不使用不同存储引擎的表,否则rollback时需要对非事务类型的表进行特别的处理,因为COMMIT、ROLLBACK只能对事务类型的表进行提交和回滚。

优化

优化SQL语句的步骤

了解SQL的执行频率

MySQL客户端连接成功后,通过show [session|global] status命令可以提供服务器状态信息,也可以在操作系统上使用mysqladmin extended-status命令获得这些消息。show [session|global] status可以根据需要加上参数“session”或者"global"来显示session级(当前连接)的统计结果和global级(自数据库上次启动至今)的统计结果。如果不写,默认使用的参数是“session“。

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

mysql> show status like 'Com_%';

查询结果中的Com_xxx表示每个xxx语句执行的次数,通常比较关心的是以下几个统计参数:

  1. Com_select

    执行SELECT操作的次数,一次查询只累加1。

  2. Com_insert

    执行INSERT操作的次数,对于批量插入的INSERT操作,只累加一次

  3. Com_update

    执行UPDATE操作的次数

  4. Com_delete

    执行DELETE操作的次数

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

  1. Innodb_rows_read

    SELECT查询返回的行数

  2. Innodb_rows_inserted

    执行INSERT操作插入的行数

  3. Innodb_rows_updated

    执行UPDATE操作更新的行数

  4. Innodb_rows_deleted

    执行DELETE操作删除的行数

通过这些参数,可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主,以及各种类型的SQL大致的执行比例是多少。对于更新操作的计数,是对执行次数的计数,不论提交还是回滚都会进行累加。

对于事务型的应用,通过Com_commit和Com_rollback可以了解事务提交和回滚的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题。

此外,以下几个参数便于用户了解数据库的基本情况:

  1. Connections

    试图连接MySQL服务器的次数

  2. Uptime

    服务器工作时间

  3. Slow_quires

    慢查询的次数

定位执行效率低的SQL语句

可以通过以下两种方式定位执行效率较低的SQL语句:

  1. 通过慢查询日志定位那些执行效率较低的SQL语句,用--log-slow-queries[=file_name]选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
  2. 慢查询日志在查询结束以后才会记录,所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用show processlist命令查看当前MySQL在进行的线程,包括线程的状态,是否锁表等,可以实时地查看SQL的执行情况。同时对一些锁表操作进行优化。

使用EXPLAIN分析低效SQL

通过以上步骤查询到效率低的SQL语句后,可以通过EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。

语法格式:

mysql> explain + SQL语句

查询结果中各列的说明:

  1. select_type

    表示SELECT的类型,常见的取值有SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SELECT)等。

  2. table

    输出结果集的表

  3. type

    表示MySQL在表中找到所需行的方式,或者叫访问类型,常见类型如下,从上到下,性能由最差到最好

    1. ALL

      全表扫描,MySQL遍历全表来找到匹配的行

    2. INDEX

      索引全扫描,MySQL遍历整个索引来查询匹配的行

    3. range

      索引范围扫描,常见于<、<=、>、>=、between等操作符

    4. ref

      使用非唯一索引扫描或唯一索引的前缀扫描,返回匹配某个单独值的记录行

    5. eq_ref

      类似ref,区别在于使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配。简单来说,就是多表连接中使用primary key或者unique index作为关联条件

    6. const,system

      单表中最多有一个匹配行,查询起来非常迅速,所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理,例如,根据主键primary key或者唯一索引unique index进行的查询

    7. NULL

      不用访问表或者索引,就能得到结果

    8. ref_or_null

      与ref类似,区别在于条件中包含对NULL的查询

    9. index_merge

      索引合并优化

    10. unique_subquery

      in的后面是一个查询主键字段的子查询

    11. index_subquery

      与unique_subquery类似,区别在于in的后面是查询非唯一索引字段的子查询

  4. possible_keys

    表示查询时可能使用的索引

  5. key

    表示实际使用的索引

  6. key_len

    使用到索引字段的长度

  7. rows

    扫描行的数量

  8. Extra

    执行情况的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息

MySQL 4.1 开始引入了explain extended命令,通过explain extended加上show engines,能看到SQL在真正被执行之前优化器做了哪些改写:

mysql> explain extended + SQL语句

通过show profile分析SQL

MySQL从5.0.37版本开始增加了对show profiles和show profile语句的支持。通过have_profiling参数,能够看到当前MySQL是否支持profile。

mysql> select @@have_profiling;

默认profiling是关闭的,可以通过set语句在Session级别开启profiling:

mysql> select @@profiling;

通过profile,能够更清楚地了解SQL执行地过程。

比如,在执行完一条SQL后,可以使用以下命令来显示历史的记录:

mysql> show profiles;

查询出来的多个结果都分别有其Query_ID,可以再使用以下SQL来查看执行过程中线程的每个状态和消耗的时间:

mysql> show profile for query Query_ID;

因为Sending data状态表示MySQL线程开始访问数据行并把结果返回给客户端,而不仅仅是返回结果给客户端。由于在Sending data状态下,MySQL线程往往需要做大量的磁盘读取操作,所以经常是整个查询结果中耗时最长的状态。

在获取到最消耗时间的线程状态后,MySQL进一步支持选择all、cpu、block io、context switch、page faults等明细类型来查看MySQL在使用什么资源上耗费了过高的时间,例如,选择查看CPU的耗费时间:

mysql> show profile cpu for query 4;

索引问题

索引是在MySQL的存储引擎层中实现的,而不是在服务器层实现的,所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型。MySQL提供了4种索引:

  1. B-Tree索引

    最常见的索引类型,大部分引擎都支持B树索引。

  2. HASH索引

    只有Memory引擎支持,使用场景简单

  3. R-Tree索引(空间索引)

    空间索引是MyISAM的一个特殊索引类型,主要用于地理空间数据类型,通常很少使用

  4. Full-text(全文索引

    全文索引也是MyISAM的一个特殊索引类型,主要用于全文索引,InnoDB从5.6版本开始提供对全文索引的支持。

MySQL不支持函数索引,但是能对列的前面某一部分进行索引,例如标题title字段,可以只取title的前10个字符进行索引,这个特性可以大大缩小索引文件的大小,但前缀索引也有缺点,在排序Order by和分组Group by操作的时候无法使用。

创建前缀索引的例子:

mysql> create index idx_title on film(title(10));

MyISAM、InnoDB、Memory这3个常用引擎支持的索引类型:

索引MyISAM引擎InnoDB引擎Memory引擎
B-Tree索引支持支持支持
HASH索引不支持不支持支持
R-Tree索引支持不支持不支持
Full-text索引支持暂不支持不支持

比较常用到的就是B-Tree索引和HASH索引。HASH索引相对简单,只有Memory/Heap引擎支持HASH索引,适用于Key-Value查询,只有在“=”的条件下才会使用索引。

MySQL如何使用索引

B-Tree索引是最常见的索引,构造类似二叉树,能根据键值提供一行或者一个行集的快速访问,通常只需要很少的读操作就可以找到正确的行。B-Tree索引中的B不代表二叉树(binary),而是代表平衡树(balanced)。

B-Tree的结构图:

22

MySQL中能使用索引的场景

  1. 匹配全值

    对索引中所有列都指定具体值,即是对索引中的所有列都有等值匹配的条件。

  2. 匹配值的范围查询

    对索引的值能够进行范围查找

  3. 匹配最左前缀

    仅仅使用索引中的最左边列进行查找,比如在col1+col2+col3字段上的联合索引能够被包含col1、(col1+col2)、(col1+col3)、(col1+col2+col3)的等值查询利用到,可是不能够被col2、(col2+col3)的等值查询利用到。

    最左匹配原则可以算是MySQL中B-Tree索引使用的首要原则。

  4. 仅仅对索引进行查询

    当查询的列都在索引的字段中时,查询的效率更高。

  5. 匹配列前缀

    仅仅使用索引中的第一列,并且只包含索引第一列的开头一部分进行查找。

  6. 能够实现索引匹配部分精确而其他部分进行范围匹配

    即索引中的一个字段为精确匹配,其他字段为范围匹配

  7. IS NULL使用索引

    如果列名是索引,那么使用column_name is null就会使用索引

有索引但不可用索引的场景

  1. 以%开头的LIKE查询不能够利用B-Tree索引
  2. 数据类型出现隐式转换的时候也不会使用索引,特别是当列类型是字符串,在where条件中必须把字符常量值用引号引起来,否则即使这个列上有索引,也不会用到。
  3. 复合索引的情况下,假如查询条件不包含索引列最左边部分,即不满足最左原则,是不会使用复合索引的。
  4. 如果MySQL估计使用索引比全表扫描更慢,则不使用索引

查看索引使用情况

可以使用以下SQL语句查看:

mysql> show status like 'Handler_read%';

如果索引正在工作,查询结果中的Handler_read_key的值将很高。另外,Hander_read_rnd_next的值高则意味着查询运行低效,应该建立索引补救。

常用SQL的优化

索引是优化查询SQL的,但是对于INSERT、UPDATE等语句还应进行其他优化:

优化INSERT语句

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

  1. 如果从同一个客户端插入很多行,应尽量使用多个值表的INSERT语句,这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗。

    insert into test values(1,2),(1,3),(1,4)...
    
  2. 如果是从不同客户端插入很多行,可以通过使用INSERT DELAYED语句得到更高的速度。DELAYED的含义是让INSERT语句马上执行,其实数据都被存放在内存的队列中,并没有真正写入磁盘。LOW_PRIORITY刚好相反,在所有其他用户对表的读写完成后才进行写入。

  3. 将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项)。

  4. 如果进行批量插入,可以通过增加bulk_insert_buffer_size变量值的方法来提高速度,但是,这只能对MyISAM表使用。

优化ORDER BY语句

MySQL排序方式

  1. 通过有序索引顺序扫描直接返回有序数据
  2. 通过对返回数据进行排序

了解了MySQL排序的方式,优化目标就清晰明了了:

尽量减少额外的排序,通过索引直接返回有序数据。WHERE条件和ORDER BY使用相同的索引,并且ORDER BY的顺序和索引顺序相同,并且ORDER BY的字段都是升序或者都是降序。否则肯定需要额外的排序操作。

优化嵌套查询

MySQL 4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。但是效率不如连接(JOIN)操作,因为MySQL不需要在内存中创建临时表ll爱完成这个逻辑上需要两步的查询操作。

MySQL优化OR条件

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

使用SQL提示

SQL提示是优化SQL的一个重要手段,简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

常用的SQL提示:

  1. USE INDEX

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

    mysql> SELECT COUNT(*) FROM RENTAL USE INDEX (IDX_RENTAL_DATE)
    
  2. IGNORE INDEX

    可以使用IGNORE INDEX可以让MySQL忽略一个或者多个索引

    mysql> SELECT COUNT(*) FROM RENTAL IGNORE INDEX(idx_rental_date)
    
  3. FORCE INDEX

    强制MySQL使用一个指定的索引

    mysql> SELECT * FROM RENTAL WHERE INVENTORY_ID > 1
    

优化数据库对象

拆分提高效率

这里所说的拆分,指对数据库表进行拆分。有两种拆分方法:

  1. 垂直拆分

    把主码和一些列放到一个表,把主码和另外的列放到另一个表中

    如果一个表中某些列常用,而另一些列不常用,则可以采用垂直拆分,另外,垂直拆分可以使得数据行变小,一个数据页就能存放更多的数据,在查询时就会减少I/O次数。其缺点是需要管理冗余列,查询所有数据需要联合(JOIN)操作。

  2. 水平拆分

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

    水平拆分通常在以下几种情况下使用:

    1. 表很大,分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,提高查询速度。
    2. 表中的数据本来就有独立性,例如,表中分别记录各个地区的数据或不同时期的数据,特别是有些数据常用,而另外一些数据不常用。
    3. 需要把数据存放到多个介质上。

    水平拆分会给应用增加复杂度,它通常在查询时需要多个表名,需要UNION操作。这种复杂性会超过它带来的优点,因为只要索引关键字不大,则在索引用于查询时,表中增加2-3倍数据量时,查询时也就增加读一个索引层的磁盘次数,所以水平拆分要考虑数据量的增长速度,根据实际情况决定是否需要水平拆分。

逆规范化

规范化越高,产生的关系就越多,导致表之间的连接操作越频繁,而表之间的连接操作是性能较低的操作,直接影响到查询的速度,所以,对于查询较多的应用,就要根据实际情况通过逆规范化来提高查询的性能。

反规范化的好处是降低连接操作的需求,降低外码和索引的数目,还可能减少表的数目,相应带来的问题是可能出现的数据的完整性问题。加快查询速度,但会降低修改速度。因此,决定做反规范时,要仔细分析需求和实际的性能特点,好的索引和其他方法经常能够解决性能问题,而不必采用反规范这种方法。

常用的反规范技术有:

  1. 增加冗余列

    指在多个表中具有相同的列,它常用来在查询时避免连接操作。

  2. 增加派生列

    指增加的列来自其他表中的数据,由其他表中的数据经过计算生成。增加的派生列其作用是在查询时减少连接操作,避免使用集函数。

  3. 重新组表

    指如果许多用户需要查看两个表连接出来的结果数据,则把这两个表重新组成一个表来减少连接而提高性能。

  4. 分割表

锁问题

MySQL锁概述

相对于其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁,InnoDB存储引擎既支持行级锁,也支持表级锁,但默认情况下采用行级锁。

MySQL这3种锁的特性:

  1. 表级锁

    开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低

  2. 行级锁

    开销大,加锁慢;会出现死锁;锁定力度最小,发生锁冲突的概率最低,并发度也最高。

  3. 页面锁

    开销和加锁时间界于表锁和行锁之间,会出现死锁;锁定力度介于表锁和行锁之间,并发度一般。

表级锁更适合以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用。

MyISAM表锁

查询表级锁争用情况

使用以下SQL语句分析系统上的表锁定争夺:

mysql> show status like 'table%';

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

表级锁的锁模式

对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;对MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作;MyISAM表的读操作和写操作之间,以及写操作之间是串行的。

加表锁

MyISAM在执行查询语句前,会自动给涉及的所有表加读锁,在执行更新、删除、插入操作前,会自动给涉及的表加写锁,这个过程不需要用户干预,显式加锁基本上都是为了在一定程度上模拟事务操作,实现对某一时间点多个表的一致性读取。

MyISAM总是一次获得SQL语句所需要的全部锁,这也正是MyISAM表不会出现死锁的原因。

并发插入

MyISAM表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM表也支持查询和插入操作的并发进行。

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

  1. 当concurrent_insert设置为0时,不允许并发插入
  2. 当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。
  3. 当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。

MyISAM的锁调度

MyISAM存储引擎的读锁和写锁是互斥的,读写操作是串行的。MyISAM总是会优先让写锁先获得锁,即使读请求先到锁等待队列,写请求后到,因为MySQL认为写请求一般比读请求要重要。但是可以通过一些设置调节MyISAM的调度行为:

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

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

InnoDB锁问题

InnoDB与MyISAM最大的不同有两点:

  1. 支持事务(TRANSACTION)
  2. 采用了行级锁

事务

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。

  1. 原子性(Atomicity)

    事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行

  2. 一致性(Consistent)

    在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构也都必须是正确的。

  3. 隔离性(Isolation)

    数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。

  4. 持久性(Durable)

    事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

并发事务带来的问题

相对于串行处理来说,并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持更多的用户。但并发事务也会带来一些问题:

  1. 更新丢失

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

  2. 脏读

    一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏数据”,并据此做进一步的处理,就会产生未提交的数据依赖关系。

  3. 不可重复读

    一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变或者已被删除

  4. 幻读

    一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据。

事务隔离级别

并发事务处理带来的问题中,”更新丢失“通常是应该完全避免的。但防止更新丢失,并不能单靠数据库事务控制器来解决,需要应用程序对要更新的数据加必要的锁来解决,因此,防止更新丢失应该是应用的责任。

”脏读“、”不可重复读“和”幻读“,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式,基本上可分为以下两种:

  1. 一种是在读取数据前,对其加锁,组织其他事务对数据进行修改
  2. 另一种是不用加任何锁,通过一定机制生成一个数据请求时间点的一致性数据快照,并用这个快照来提供一点级别(语句级或事务级)的一致性读取。这种技术叫做数据多版本并发控制(MVVC),也经常称为多版本数据库。

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上”串行化“进行,这与并发是矛盾的。同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对”不可重复读“和”幻读“并不敏感,可能更关心数据并发访问的能力。

为了解决”隔离“与”并发“的矛盾,ISO/ANSI SQL92定义了4个事务隔离级别,每个级别的隔离程度不同,允许出现的副作用也不同。

读数据一致性脏读不可重复读幻读
未提交读(Read uncommitted)最低级别,只能保证不读取物理上损坏的数据
已提交读(Read commited)语句级
可重复读(Repeatable read)事务级
可序列化(Serializable)最高级别,事务级

获取InnoDB行锁争用情况

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

mysql> show status like 'innodb_row_lock%';

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

  1. 通过查询information_schem数据库中的表了解锁等待情况:

    mysql> select * from innodb_locks
    
  2. 通过设置InnoDB Monitors观察锁冲突情况:

    mysql> create table innodb_monitors(a INT) ENGINE=INNODB
    

    然后就可以用下面的语句来进行查看:

    mysql> show engine innodb status
    

    监视器可以通过以下SQL来停止:

    mysql> DROP TABLE innodb_monitor;
    

InnoDB的行锁及加锁方法

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

  1. 共享锁(S)

    允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁

  2. 排他锁(X)

    允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁

另外,为了允许行锁与表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁,这两种意向锁都是表锁:

  1. 意向共享锁(IS)

    事务打算给数据行加共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。

  2. 意向排他锁(IX)

    事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁

意向锁表明“某个事务正在某些行持有了锁、或该事务准备去持有锁”。

意向锁的存在是为了协调行锁和表锁的关系,支持多粒度(表锁与行锁)的锁并存。

例子:事务A修改user表的记录r,会给记录r上一把行级的排他锁(X),同时会给user表上一把意向排他锁(IX),这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。

意向锁必须是表级锁:

当我们需要加一个排他锁时,需要根据意向锁去判断表中有没有数据行被锁定(行锁);

  1. 如果意向锁是行锁,则需要遍历每一行数据去确认;
  2. 如果意向锁是表锁,则只需要判断一次即可知道有没数据行被锁定,提升性能。

意向锁怎么支持表锁和行锁并存?

  1. 首先明确并存的概念是指数据库同时支持表、行锁,而不是任何情况都支持一个表中同时有一个事务A持有行锁、又有一个事务B持有表锁,因为表一旦被上了一个表级的写锁,肯定不能再上一个行级的锁。
  2. 如果事务A对某一行上锁,其他事务就不可能修改这一行。这与“事务B锁住整个表就能修改表中的任意一行”形成了冲突。所以,没有意向锁的时候,让行锁与表锁共存,就会带来很多问题。于是有了意向锁的出现,数据库不需要在检查每一行数据是否有锁,而是直接判断一次意向锁是否存在即可,能提升很多性能。

意向锁相互兼容,因为IX、IS只是表明申请更低层次级别元素(比如 page、记录)的X、S操作。

因为上了表级S锁后,不允许其他事务再加X锁,所以表级S锁和X、IX锁不兼容

上了表级X锁后,会修改数据,所以表级X锁和 IS、IX、S、X(即使是行排他锁,因为表级锁定的行肯定包括行级速订的行,所以表级X和IX、行级X)不兼容。

上了行级X锁后,行级X锁不会因为有别的事务上了IX而堵塞,一个mysql是允许多个行级X锁同时存在的,只要他们不是针对相同的数据行。

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

  1. 共享锁(S)

    SELECT * FROM TABLE_NAME WHERE ... LOCK IN SHARE MODE

  2. 排他锁(X)

    SELECT * FROM TABLE_NAME WHERE ... FOR UPDATE

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

InnoDB行锁实现方式

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

  1. Record lock

    对索引项加锁

  2. Gap lock

    对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁

  3. Next-key lock

    前两种的组合,对记录及其前面的间隙加锁

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

在实际应用中,要特别注意InnoDB行锁的这一特性,否则可能导致大量的锁冲突,从而影响并发性能,例如:

  1. 在不通过索引条件查询时,InnoDB会锁定表中的所有记录
  2. 由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。
  3. 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
  4. 即便在条件中使用了索引字段,但是MySQL可能认为全表扫描效率更高,它就不会使用索引,这种情况下也会对所有记录加锁
  5. 如果检索值的数据类型与索引字段不同,虽然MySQL能进行数据类型转换,但却不会使用索引,从而导致InnoDB对所有记录加锁。

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锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求,对于上面的例子,要是不使用间隙锁,如果其他事务插入了empid大于100的任何记录,那么本事务如果再次执行上述语句,就会发生幻读;另一方面,是为了满足其恢复和复制的需要。

很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在应用开发中,要尽量优化逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。

InnoDB在不同隔离级别下的一致性读及锁的差异

锁和多版本数据是InnoDB实现一致性读和ISO/ANSI SQL92隔离级别的手段,因此,在不同的隔离级别下,InnoDB处理SQL时采用的一致性读策略和需要的锁是不同的。

InnoDB存储引擎中不同SQL在不同隔离级别下锁比较
EC856C4D-CDA8-499f-879D-36A8C4FCD03F

可以看出,对于许多SQL,隔离级别越高,InnoDB给记录集加的锁就越严格(尤其是使用范围条件的时候),产生锁冲突的可能性也就越高,从越发影响性能。因此,在应用中,应该尽量使用较低的隔离级别,以减少锁争用的机率。实际上,通过优化事务逻辑,大部分应用使用Read Commited隔离级别就足够了。对于一些确实需要更高隔离级别的事务,可以通过在程序中执行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ或SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE动态改变隔离级别的方式满足需求。

什么时候使用表锁

对于InnoDB表,绝大多数情况下都应该使用行级锁,因为事务和行锁往往是选择InnoDB表的理由。但在个别特殊事务中,也可以考虑使用表级锁。

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

当然,应用中这两种事务不能太多,否则,就应该考虑使用MyISAM表了。在InnoDB下,使用表锁要注意以下两点:

  1. 使用LOCK TABLES虽然可以给InnoDB加表级锁,但表锁不是由InnoDB存储引擎层管理的,而是由其上一层——MySQL Server负责的,仅当autocommit=0、innodb_table_locks=1(默认设置)时,InnoDB层才能知道MySQL加的表锁,MySQL Server也才能感知InnoDB加的行锁,这种情况下,InnoDB才能自动识别涉及表级锁的死锁。否则,InnoDB将无法自动检测并处理这种死锁。

  2. 在用LOCK TABLES对InnoDB表加锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前,不要用UNLOCK TABLES释放表锁,因为UNLOCK TABLES会隐含地提交事务;COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁,必须用UNLOCK TABLES释放表锁。正常的方式如下:

    SET AUTOCOMMIT = 0;
    LOCK TABLES T1 WRITE,T2 READ,...;
    ....
    COMMIT;
    UNLOCK TABLES;
    

死锁

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

发生死锁后,InnoDB一般都能自动检测到,并使一个事务释放锁并回退,另一个事务获得锁,继续完成事务。但在涉及外部锁或涉及表锁的情况下,InnoDB并不能完全自动检测到死锁,这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。这个参数不止可以解决死锁,还可以用来解决高并发时大量事务因为无法立即获取所需的锁而被挂起,严重影响性能的问题。

避免死锁的常用方法:

  1. 在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。

  2. 在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。

  3. 在REPEATABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT... FOR UPDATE加排他锁,在没有符合该条件记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新纪录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITED,就可避免问题。

  4. 当隔离级别为READ COMMITED时,如果两个线程都先执行SELECT... FOR UPDATE,判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第一个线程提交后,第二个线程会因主键重复出错,但虽然这个线程出错了,却会获得一个排他锁。这时如果有第三个线程又来申请排他锁,也会出现死锁。

    对于这种情况,可以直接做插入操作,然后再捕获主键重复异常,或者在遇到主键重复错误时,总是执行ROLLBACK释放获得排他锁。

​ 虽然通过以上措施可以大大减少死锁,但死锁很难完全避免,因此,在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。

​ 如果出现死锁,可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因。