MySQL(4)

锁机制:

概述

锁按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的间隙锁。

表锁在操作数据时会锁定整张表，并发性能较差；

行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。

MySQL 中不同的存储引擎支持的锁是不一样的，例如 MyIsam 只支持表锁，而 InnoDB 同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

行锁

行级锁是 Mysql 中锁定粒度最细的一种锁，表示只针对当前操作的行进行 加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

优点：
锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

缺点:
开销大,加锁的频率高，加锁慢；会出现死锁；

间隙锁

锁的是一个区间,当我们用范围条件而不是相等条件检索数据，InnoDB 会给 符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记 录，叫做“间隙",InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key 锁）。

例如: update test set name=‘111’ where id >1 and id <10
会将id为2,3,4,5,6,7,8,9的区间锁住.

表锁

表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加 锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的 MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享锁与表排他锁。

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

排他锁（X）:

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

update,delete,insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型，如果加排他锁可以使用 select …for update 语句，加共享锁可以使用 select … lock in share mode 语句。

实际意义上的加锁,有事务进行写操作,其他事务不能执行.
执行新增,修改,删除操作时,会自动加锁.

查询语句如果有需要,可以加排它锁,例如:
SELECT * FROM test WHERE id = 5 FOR UPDATE；
就是给select语句后加 FOR UPDATE;

也可以加读锁:
SELECT * FROM test WHERE id = 5 LOCK IN SHARE MODE;
就是给select语句后加 LOCK IN SHARE MODE;

共享锁(S):

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

为一个事务查询语句添加共享后,其他事务也可以添加共享锁,
但是其他事务就不能再添加排他锁了

Sql 优化

为什么要对 SQL 进行优化 ?

项目上线初期，由于业务数据量相对较少，一些 SQL 的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断 SQL 对程序的运行效率有多大，故很少针对 SQL 进行专门的优化，而随着时间的积累，业务数据量的增多，SQL 的执行效率对程序的运行效率的影响逐渐增大，此时对 SQL 的优化就很有必要。

SQL 优化的一些方法

1. 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引(添加索引的原则).

2. 应尽量避免索引失效:
   2.1: 组合索引下,不满足最左前缀原则.
   2.2: like 模糊查询将导致全表扫描.
   2.3: 避免null为条件查询,否则将导致引擎放弃使用索 引而进行全表扫描.
   2.4: 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
   2.5: 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃 使用索引而进行全表扫描.如:select id from t where num=10 or num=20
   2.6: in 和 not in 也要慎用，否则会导致全表扫描.对于连续的数值，能用 between 就不要用 in 了.如: select id from t where num in(1,2,3) 应改为 select id from t where num between 1 and 3
   2.7: 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃 使用索引而进行全表扫描.如:select id from t where num/2=100 应改为: select id from t where num=100*2

3. 索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率. 因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑, 视具体情况而定.一个表的索引数最好不要超过 6 个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

4. 尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销.这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

5. 尽可能的使用 varchar 代替 char,因为首先变长字段存储空间小,可以节省存储空间, 其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

6. 任何地方都不要使用 select * from t ,用具体的字段列表代替" * ",不要返回用不到的任何字段.

7. 尽量避免 NULL,平时开发过程中,MySQL 字段的一般都会设置为 NOT NULL,原因如下：
   1.空值(‘ ’)是不占用空间的 MySQL 中的 NULL 其实是占用空间的.
   2.如果查询中包含可为 NULL 的列，不走索引
   3.count()统计某列的记录数的时候，如果采用的 NULL 值，会被系统自动忽略掉，但是空值是会进行统计到其中的。

执行计划

sql发送给服务器,在服务器内如何执行,执行流程是怎们样的,先执行谁,有没有用到索引等等…
使用 explain 关键字,添加到查询语句的前面, 输出sql执行过程的参数.

EXPLAIN

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL 是如何处理你的 SQL 语句的。

EXPLAIN 作用

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

EXPLAIN 使用

在 select 语句之前增加 explain 关键字,执行查询会返回执行计划的信息,而不是执行 SQL。
例如:
EXPLAIN SELECT * FROM USER WHERE id = 1

expain 出来的信息有 12 列，分别是: id, select_type, table, partitions,type, possible_keys, key, key_len, ref, rows, filtered,Extra.

概要描述

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

id

SELECT 识别符。这是 SELECT 的查询序列号 id 如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id 值越大，优先级越高，越先执行。

总结:
id : sql执行顺序
​ 例如有嵌套的子查询
​ 主查询id 1
​ 子查询id 2 表名子查询是先执行

select_type

表示查询中每个 select 子句的类型

1.SIMPLE(简单 SELECT，不使用 UNION 或子查询等)
2.PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的 select 被标记为 PRIMARY)
3.SUBQUERY(子查询中的第一个 SELECT，结果不依赖于外部查询)
4.DERIVED(派生表的 SELECT, FROM 子句的子查询)
5.UNION(UNION 中的第二个或后面的 SELECT 语句) 6.UNION RESULT(UNION 的结果，union 语句中第二个 select 开始后面所有 select)

总结
select_type
​ 表示查询结构
​ simple 简单查询
​ PRIMARY 主查询
​ SUBQUERY 子查询

type

对表访问方式，表示 MySQL 在表中找到所需行的方式，又称“访问类型”。
常用的类型有：system>const>eq_ref>ref>range>index>ALL（从左到右， 性能从好到差）.

system: 表只有一行记录（等于系统表），平时不会出现，这个也可以忽略不 计.

const: 表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。

eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见 于主键或唯一索引扫描.

ref: 非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访 问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行， 所以他应该属于查找和扫描的混合体.

range: 只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个 索引一般就是在你的 where 语句中出现了 between、<、>、in 等的查询这种 范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束 语另一点，不用扫描全部索引。

index: Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树。这通 常比 ALL 快，因为索引文件通常比数据文件小。也就是说虽然 all 和 Index 都是 读全表，但 index 是从索引中读取的，而 all 是从硬盘中读的）

All: Full Table Scan，将遍历全表以找到匹配的行(索引失效了).

一般来说，得保证查询至少达到 range 级别，最好能达到 ref.

总结:
type 查询性能指标
system>const>eq_ref>ref>range>index>ALL
system 表中只有一条记录
const 通过索引一次性可以找到
ref 使用了索引 例如姓名,查询出来可能会有多条数据
range 使用了索引 范围查询
index 类型只遍历索引树。
All 全表扫描 索引失效 查询所有数据了

possible_keys

显示可能应用在这张表中的索引，一个或多个。 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用.(可能用到的索引).

key

实际使用的索引。如果为 NULL，则没有使用索引或者索引失效.

ken_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失 精确性的情况下，长度越短越好.

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于 查找索引列上的值.

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

额外的信息说明

Using filesort: 当 Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操 作的时候,Mysql 无法利用索引完成排序的操作称为”文件排序”.

Using temporary: 使了用临时表保存中间结果，MySQL 在对查询结果排序时 使用临时表。常见于排序 order by 和分组查询 group by。

Using index: 表示相应的 select 操作中使用了索引，避免访问了表的数据行，效率不错！ 如果同时出现

using where: 表明索引被用来执行索引键值的查找；如果没 有同时出现 using where，表明索引用来读取数据而非执行查找动作。

Using where: 表示使用到了索引 , 但是也进行了 where 过滤.