ps -ef|grep mysql 查到mysql 的进程
192.168.31.112 mysql 进入方法
cd /usr/local/mysql/bin/
./mysql -u root -p
123456
** 1.Mysql架构 **
插件式的 存储引擎 架构 将
查询处理 和 其他的 系统任务 以及 数据的存储 提取相分离。
四层架构: 连接层 服务层 引擎层 存储层
**存储引擎 **
查询 mysql 提供了 什么 存储引擎
show engines;
查询 当前 myql 使用 哪个 存储引擎
show variables like ‘%storage_engine%’;
** 索引 优化 **
-
SQL 性能 下降 原因: 执行时间长 等待时间长
1.1 查询语句写的烂
1.2 索引失效 : 单值索引 复合索引
1.3 关联查询 太多 join (设计缺陷 或 不得已行为)
1.4 服务器调优 各个 参数设置 (缓冲 线程数) -
SQL 执行顺序加载
2.1 人为手写过程
2.2 机器读取顺序
2.3 总结
-
七种 JOIN 理论
** 2.索引 是 什么 ? **
索引 是 一种 快速查找 和 检索数据的 数据结构,
可以理解成 排好序的 快速查找数据结构。
索引的目的在于 提高查询效率 , 可以 类比字典。
索引会影响到 where 后面的条件查询 和 order by 排序。
在 MySQL 中,MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构,但是,两者的实现方式不太一样。
一般来说 , 索引本身也很大 ,不可能 全部 存储在内存中 ,索引 往往 以 索引文件的方式 存储在 磁盘上。
索引 官方含义 :
数据本身之外, 数据库还维护着 一个满足 特定查找算法 的 数据结构,
这种 数据结构 以某种方式 指向 数据 (指针), 这样 就可以在这些数据结构的基础之上 实现 高级查找算法, 这种 数据结构 就是 索引。
** 索引 优势 劣势 **
优势:
1.类似于大学图书馆的索引,提高了数据检索的 效率 ,降低了数据库 IO成本
2. 通过 索引 对 数据的 排序 ,降低了 数据排序的 成本 ,降低了 CPU 的消耗
mysql的IO是指数据库文件的读写,也就是检索数据和插入数据。
劣势:
1.实际上 索引也是 一张表 ,该表 保存了 主键 和 索引字段 ,该索引列 也需要 占用磁盘空间。
2.虽然 索引 大大 提高了 查询效率, 但会 降低 更新 表的速度 ,如 增删改。
(因为 在 更新 表 的 时候 , Mysql 不仅要 保存 数据 ,还要 保存 索引文件 每次 更新 添加 了 的 索引列的字段 , 然后 都会 调整 因为 更新 所带来的 键值变化 后 的 索引信息。)
3.索引 需要 大量的 维护 , 如 花时间 建立 最优秀的索引 ,或者 优化查询。
** 索引 的 分类 **
单值索引 复合索引 唯一索引
复合索引 优于 单值索引
一张表 索引 最多 不要建立 超过 5个索引。
单值索引 : 一个索引 只包含 单个列 , 一个表 可以 有多个 单值索引。
唯一索引 : 索引列 必须唯一 ,但允许 为 空值。
复合索引: 一个索引 包含 多个列。
[] 代表可以省略
基本语法 :
创建:
create [ UNIQUE ] index indexName(索引名) on mytable(表名) (column (length)) (字段)
- CREATE INDEX t_dept_Name ON t_dept ( deptName );
alter table mytable(表名 )add [ UNIQUE ] index [ indexName](索引名) (column (length)) (字段)
- ALTER TABLE t_dept ADD INDEX t_dept_Name ( deptName );
删除:
drop index [indexName] on mytable
- DROP INDEX t_dept_Name ON t_dept;
查看:
show index from table_name\G(表名)
- show index from t_dept;
CREATE INDEX t_dept_Name ON t_dept ( deptName );
DROP INDEX t_dept_Name ON t_dept;
ALTER TABLE t_dept ADD INDEX t_dept_Name ( deptName );
ALTER TABLE t_dept ADD INDEX t_dept_Name_address ( deptName ,address);
show index from t_dept;
使用 alter 创建 4种索引
alter table t_table add primary key (column_list)
该语句添加一个主键 意味着索引值 唯一 不能为null
alter table t_table add unique index_name (column_list)
创建索引的值 必须唯一 (除了 null 外 ,null 可能多次出现)
alter table t_table add index index_name (column_list)
添加普通索引 , 索引值 可能 多次出现
alter table t_table add index_name (column_list,column_list2)
添加复合索引
alter table t_table add fulltext index_name (column_list)
添加全文索引
** mysql 索引结构**
BTree索引 Hash索引 full-text索引 R-Tree索引
BTree树 索引 原理:
** 哪些情况适合建索引**
适合建立索引情况:
1.主键自动建立索引
2.频繁作为查询添加的字段 应该建立索引
3.查询中 与其他表关联的字段 如 外键关系建立索引
4.频繁更新的字段不适合建立索引
(因为每次更新 不单单更新了数据 还会更新索引)
5.where 条件 用不到 的 字段 不建立索引
6.单键/复合索引 (高并发情况下 建议 复合索引)
7.查询中的字段 , 排序字段 如果 通过索引访问 将 大大提供 排序速度
8.查询中统计 或者 分组字段
** 哪些情况不适合建索引**
不适合建索引情况:
1.表记录太少 (高于三百万的数据)
2.经常增删改的表
(因为 在 更新 表 的 时候 , Mysql 不仅要 保存 数据 ,还要 保存 索引文件 每次 更新 添加 了 的 索引列的字段 , 然后 都会 调整 因为 更新 所带来的 键值变化 后 的 索引信息。)
3.数据重复且发布平均的表字段
(如 国籍 大家都是中国人 性别 男或者女)
**MySql 性能分析 **
1.Mysql Query Optimizer (Mysql 在服务层 自带的 优化器)
-
Mysql 常见瓶颈
CPU CPU饱和的时候 一般发生在 数据装入内存或从磁盘读取数据的时候
IO 磁盘 I/O 瓶颈 发生 在 装入数据 远大于 内存容量 的 时候
服务器硬件的性能瓶颈 — top free iostat vmstat 查看 系统的性能状态 -
Explain
** Explain **(重点 可以的话背下来吧)
含义 :
使用EXPLIAIN 关键字 可以 模拟 优化器执行 SQL 查询语句 , 从而知道Mysql 是 如何 处理你的SQL 语句。
分析 你的 查询语句 或是 表结构的 性能瓶颈
基本用法:
explain + sql 语句
作用:
1.表的读取顺序 (可以通过 id 看优先级执行)
2.数据读取操作的操作类型 (可以通过 select_type 查看操作类型)
3.哪些索引可以使用 (可以通过 possible_keys 查看 哪些索引可以被使用)
4.哪些索引被实际使用 (可以 通过 keys 查看 实际中索引被使用)
5.表之间的引用 (可以通过 ref 查看 表之间的引用 如 test.t2.id)
6.每张表有多少行被优化器查询 (可以通过 rows 查询 表多少行被优化器查询)
explain 的表头 (也称为 指标)
id select_type table type possible_keys key key_len ref rows Extra
** explain 之 id 介绍
select 查询的 序列号 , 包含 一组数字 , 表示 查询中 执行 select 子句 或 操作表的顺序。
三种情况:
id 相同 , 执行顺序 由上往下 (图一)
id 不同, 如果是子查询 , id的序号 会 递增 , id值越大优先级越高,越先执行 (图二)
id 相同不同 ,同时存在。 (图三)
id如果相同, 可以认为是一组,执行顺序 由上往下
在所有组中, id值越大,优先级越高 ,越先执行
--------------------------------------(图一)-------------------------------------------------------
--------------------------------------(图二)-------------------------------------------------------
--------------------------------------(图三)-------------------------------------------------------
** explain 之 select_type 介绍
simple primary subquery derived(临时表 衍生) union union result
有哪些:
作用:
查询的类型 主要用于 区别
普通查询 联合查询 子查询等复杂查询
各种操作类型含义 :
simple : 简单的select查询 查询中不包含 子查询 或 union
primary :查询中若包含复杂的子部分 最外层查询被标记 (如 图二中的 id)
subquery :在 select 或 where 中 包含 子查询
derived(临时表 衍生):
在 from 列表中 包含的 子查询 被标记为 derive(衍生)
mysql 会递归 执行 这些 子查询 把结果放在 临时表中 (如图二中 的 derived2)
union :如果 第二个 select 出现 在 union 后 , 被标记 union
如果 union 包含 from 子句 的 子查询中 ,外层 select 标记为 derived
union result : 从 union 表 获取 结果的 select
** explain 之 table介绍
含义:
显示这一行的 数据 是关于 哪个表
** explain 之 type 介绍
类型 all index range ref eq_ref const,system null
显示查询使用了何种 类型
从最好依次到最差
system>const>eq_ref>ref>range>index>All
一般来说 , 得保证 查询 至少达到 range 级别, 最好到达 ref
system :表只有一行数据 (等于 系统表) 这是 const 类型的 特例, 可以忽略
const : 表示 通过 索引 一次 就能找到 , const 用于 比较 primary key 或者
unique (唯一)索引。
因为 只匹配 一行数据 很快。
(如 将主键 置于 where 中, mysql就能 将查询转换成 一个常量)
eq_ref : 唯一性索引扫描 对于每个索引键 表中 只有一个记录与之匹配
常见: 主键 或者 唯一索引扫描
ref : 非唯一性 索引扫描 返回匹配某个单独值的所有行
本质上 是一种 索引访问 返回所有匹配 某个单独值的行
然而 可能会找到多个符合条件的行,所以属于 查找和扫描的混合体
range :只 检索给定范围的行 使用一个索引来选择行
key列显示使用哪个索引
一般 在 where 出现 between > < in 等查询
范围查询 比 全表查询 好 因为它只需要 开始于索引的 一点 ,而结束语一点 ,不用 扫描全部索引
index : index 与 all 的区别 在于 只遍历索引树,通常 比 all 快 。
因为索引文件通常 比 数据文件小 (index是从索引读取, all从硬盘读取)
All : 将遍历全表以 找到 匹配的行。
** explain 之 possible_keys key key_len介绍
possible_keys : 显示可能应用在这表中的索引 一个或者多个
查询涉及到的字段上若存在索引 , 则该索引被列出,但不一定被查询实际使用
key : 实际使用的索引。 如果为null ,则没有使用索引
查询中若 使用了覆盖索引,则该索引仅出现在key列表中。
key_len: 表示 索引中使用的 字节数, 可通过该列计算查询中使用的索引长度。
在不损失精度的情况下,长度越短越好。
key_len显示的值 为 索引字段的最大可能长度,并非实际长度
即key_len根据表定义计算而得,不是通过表内检索出。
** explain 之 ref 介绍
: 显示索引的哪一列 被使用了 , 可能是常数 。
哪些列 或 常量 被用于 查找索引列上的值。
** explain 之 rows介绍
:
根据表统计信息 以及 索引选取情况 ,大致 估算出 找到所需记录需要读取的行数
** explain 之 Extra 介绍
group by 和 order by 顺序 最好 与 索引顺序一致
: 包含不适合在其他列中 显示 但 十分重要的 额外信息
- Using filesort : 说明 mysql 会对 数据使用 一个外部的索引排序,
而不是按照 表内索引顺序进行 读取。
mysql中无法利用索引完成 排序的操作 称为文件排序
(需要 优化)
2.Using temporary :使用了 临时表 保存 中间结果, mysql 在对 查询结果 排序的时候 使用了 临时表。
常见于(group by 和 order by )
(立马优化)
- Using index (好事):
- 表示 相应的 select 操作中 使用了 覆盖索引 ,避免了表的数据行,效率好
- 如果 同时出现 using where , 表明 索引 被用来执行 索引键值 的 查找
- 如果没有 同时 出现 using where, 表明 索引被用来读取数据 而不是执行查找动作
覆盖索引
(索引覆盖)
select 的 数据列 只用 从 索引中 获取, 不必读取 数据行,
mysql 可以 利用索引 返回 select 列表中的字段,而不必 根据 索引再次读取 数据文件 , 换句话说 查询列要被所建立的索引覆盖
如 索引 (1,2, 3) 查询的列 为 (1,2, 3)
-
Using where :表明 使用 where 过滤
-
using join buffer:使用了连接缓存
6.impossible where : where 子句值为false,不能用来获取任何元组
(如 名字不可能两个吧 name =‘1’ and name =‘2’)
-
select tables optimized away:
-
distinct
** 索引优化 ** (重点 面试让你写一个索引)
**单表:
EXPLAIN
select id , author_id from article
where caregory_id= '1'
and comments > 1
ORDER BY views DESC
LIMIT 0,1;
show index FROM article;
DROP INDEX article_caregory_id_comments_views ON article;
范围以后索引会失效
···
第一种 索引
create index article_caregory_id_comments_views on article(caregory_id,comments,views);
alter table article add index article_caregory_id_comments_views (caregory_id,comments,views);
- EXPLAIN select id , author_id from article where caregory_id= ‘1’
and comments > 1 ORDER BY views DESC LIMIT 0,1;
结论:
1. type 变成了 range 但是 extra 出现了 using filesort
2. 因为根据 Btree树原理
3. 先排序 caregory_id 在排序 comments ,遇到相同的 comments 在排序 views
4. 当comments 字段 在 联合 索引 里 处于 中间位置
5. 因为 comments >1 是 范围值
Mysql 无法 利用 索引 在对 views 部分 进行 检索, 即 range 类型 查询字段后面 索引失效
第二种 索引
create index article_caregory_id_comments_views on article(caregory_id,views);
alter table article add index article_caregory_id_comments_views (caregory_id,views);
结论: 去掉了 comments 这个范围值 , type 变成了 ref , extra 的 using filesort 也消失了
**两表:
分析以下 sql语句
EXPLAIN SELECT
*
FROM
class
LEFT JOIN book ON class.card = book.card;
左表 : class 右表:book
create index Y on book(card); 建立右表索引
drop INDEX Y on book;
alter table class add index Z (card); 建立左表索引
DROP index Z on class;
结论:
1.这是 由 左连接特性决定的 , left join 用于 确定如何从右表搜索行,左边一定有
2.所以 右边 是 我们的 关键点 , 一定 要 建立 索引
3.right join 用于 确定如何从左表搜索行,左表一定需要建立索引
EXPLAIN SELECT
*
FROM
class
RIGHT JOIN book ON class.card = book.card;
alter table class add index Z (card); 建立左表索引
DROP index Z on class;
结论 : 左连接 建立 右表索引 , 右连接 建立 左表索引
** 三表
explain SELECT * FROM class c
LEFT JOIN book b ON c.card = b.card
LEFT JOIN phone p ON b.card = p.card
结论:
1.全是all 优化
2. 根据 左连接 建立 右表 book索引 建立 右表 phone索引
create index Y on book(card);
alter table phone add index Z(card);
结论: 后两行的 type 都是 ref ,row 优化 很好 。
因为 索引最好设置在经常需要查询的字段中。
大结论:
join的优化
1.尽可能减少 join 语句中的 NestedLoop(嵌套循环)的循环总次数:
“永远 小结果 驱动 大结果” (小表 驱动 大表) (书籍类 驱动 书)
2.优先优化 NestedLoop 的 内层循环
3.当无法保证 被驱动表 的 join 条件字段 被索引且内存资源充沛下,
不要吝啬 joinbutter 设置
** 索引 失效 (应该避免)**
** 索引失效
-
全值匹配 我最爱
索引(1,2, 3) 查询顺序 (1,2, 3)
··· EXPLAIN SELECT *
FROM staffs WHERE NAME = ‘李四’ AND age = ‘22’ AND pos = ‘经理’··· -
最左前缀原则
(重中之重)
如果索引使用了 多列 要遵循最左前缀法则 指的是 查询从 索引的最左前列 开始 并且 不跳过 索引中的列
索引的最左前列 带头大哥
索引中的列: 中间兄弟EXPLAIN SELECT * FROM staffs WHERE NAME = '李四' AND age = '22' AND pos = '经理' (带头大哥,中间兄弟都在 ,索引生效 且配合 全值匹配) EXPLAIN SELECT * FROM staffs WHERE NAME = '李四' AND pos = '经理' (带头大哥在,中间兄弟不在 ,索引生效 但只会部分查询 索引NAME ) EXPLAIN SELECT * FROM staffs WHERE NAME = '李四' AND age = '22' (带头大哥,中间兄弟在 ,最后兄弟没了,索引生效 不会部分查询 ) EXPLAIN SELECT * FROM staffs WHERE age = '22' AND pos = '经理' (带头大哥不在,中间兄弟在 ,最后兄弟在,索引失效 全表查询 all )
-
不在索引列上做任何操作(计算 函数 (自动or手动)类型转换),
会导致 索引失效 而转向 全表查询EXPLAIN SELECT * FROM staffs WHERE left(pos,2) = '经理' (索引失效,使用了 )
-
存储引擎 不能 使用 索引中 范围条件 右边的列
范围之后全失效
EXPLAIN SELECT * FROM staffs WHERE NAME = '李四' AND age > '22' AND pos='经理'
-
尽量使用
覆盖索引
(只访问 索引的查询 (索引列和查询列一致
)),减少使用 select *
(或者是查询列 的 一部分 和 索引列的一部分吻合)
EXPLAIN SELECT NAME , age, pos FROM staffs WHERE NAME = '李四' AND age = '22' AND pos = '经理' (或者是查询列 的 一部分 和 索引列的一部分吻合) EXPLAIN SELECT NAME FROM staffs WHERE NAME = '李四' AND age = '22' AND pos = '经理' EXPLAIN SELECT age, pos FROM staffs WHERE NAME = '李四' AND age = '22' AND pos = '经理'
-
mysql 在使用 (!= <>) 的时候 无法使用 索引 会 导致 全表查询
-
is null , is not null 无法使用索引
-
like 以 通配符 开头(' %abc.. ') mysql 索引失效 会 变成 全表查询
(重点)
解决 like% 字符串% 时 索引不被引用的 方法?
建立 覆盖索引 ,最好 建的索引 个数 顺序 最好吻合
注意 主键自带索引 -
字符串 不加 单引号 索引失效
varchar类型 必须 加 单引号 -
少用 or , 用它 连接 会索引失效
总结
【优化总结口诀】
全值匹配我最爱,最左前缀要遵守;
头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
Like百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用;
VAR引号不可丢,SQL高级也不难!
索引面试题
定值(常量) 范围 还是 排序 , 一般 order by 给范围
group by 基本上 都需要 进行排序 ,会有临时表产生。
query (查询)
一般性建议:
单值索引, 尽量选择 针对 当前 query 过滤性更好的索引
组合索引, 当前 query 中 过滤性 最好的 字段 在 索引字段顺序中 , 位置越靠左越好
组合索引 ,尽量选择 可以 能够 包含 当前 query中的 where字句中 更多字段的索引
尽可能 分析统计信息 和 调整 query 的写法 来达到 合适索引
**3. 查询截取分析 **
1.查询优化
2. 慢查询 的开启 并捕获
3. explain+慢SQL分析
4. 4.show profile 查询 sql 在mysql服务器里面的执行细节和生命周期情况
5.sql 数据库 服务器的 参数调优
** 查询优化
永远 小表 驱动 大表
类似于 嵌套循环 Nested Loop
基本语法:
select ... from table where exists (subquery 子查询)
将主查询 的 数据 ,放到 子查询验证 ,
根据 验证结果 (true 或者 false) 来决定查询的数据是否保留
示例:
SELECT * FROM t_dept t
WHERE t.id IN ( SELECT id FROM t_emp p );
等价于
SELECT * FROM t_dept t
WHERE EXISTS ( SELECT * FROM t_emp p WHERE t.id = p.id );
(子查询的 select ‘X’ 也无所谓 因为已经忽视了查询结果,
而是 T表 数据是否存在于 P表)
** order by 关键字优化
一.order by 子句 尽量使用 index 方式 排序 , 避免使用 Filesort 方式排序
mysql 支持 两种 排序 – filesort 和 index
指的是 mysql 扫描索引 本身 完成排序 。filesort本身效率低
二. 尽可能 在 索引列上完成排序 , 按照 索引建的 最左前缀原则
order by 两种情况 出现 index排序
1.order by 语句 满足 索引 最左前缀原则
2. where 子句 和 order by 子句 满足 索引最左前缀原则
三. 如果 不在 索引列 上 ,filesort 两种算法:
mysql 启动 双路排序 和 单路排序
buffer(缓冲区)
**双路排序:
Mysql 4.1 之前 使用的是 双路排序 ,经过 两次磁盘扫描,最终得到数据。
读取行指针和 order by 列 ,进行排序 ,然后扫描已经排序好的列表,
按照列表中的值查询从列表中读取对应数据输出
总结: 从磁盘取排序字段 , 在buffer进行排序,再从磁盘取其他字段。
缺点:
取一批数据 ,要对磁盘进行两次扫描, I/O 很耗时,
所以在 4.1之后出现单路算法
**单路排序:
从磁盘读取查询需要的所有列, 按照 order by 排序 在 buffer 进行排序,
然后扫描排序后的列表进行输出,效率快,避免了 二次读取数据。
并且把随机 I/O 变成了 顺序 I/O ,但会 使用更多内存空间,
因为每一行都存储在 内存中。
单路排序 出现的问题:
四. 优化策略
1. 增大 sort_buffer_size 参数设置
2. 增大 sort_length_for_data参数设置
3. 原因:
总结: 为排序使用索引
Mysql 两种 排序 :
文件排序(Using firesort )
| | 扫描有序索引排序(Using index)
Mysql 能为 排序和查询使用相同的索引
**group by 关键字优化
group by 实际上 是 先排序后分组 , 遵循 最左前缀原则
当索引列无法使用的时候 ,
增大 sort_buffer_size 参数设置 ,增大 sort_length_for_data参数设置
where 高于 having , 能写 在 where 限定条件 就不使用 having
** 慢查询日志
总结 :
Mysql 慢查询日志 是 mysql 提供的 一种日志记录,
用来 记录 在 mysql 中 响应 时间 超过 阙值的语句 ,
具体指的是 运行时间 超过 long_query_time 值的sql,会被记录到 慢查询日志中
long_query_time(阙值) 默认值 为 10 ,即 运行时间 超过10秒也是的sql语句
结合 sql 超过 我们的 最大忍耐时间值 , 可以结合 explain 进行 全面分析
流程 :
1.查询慢查询日志状态 :show VARIABLES like ‘%slow%’;
- 使用 set global slow_query_log=1
- 开启慢查询日志 但 只对当前数据库生效
mysql 重启后失效
长期开启:
- 如何 知道 sql 被记录到了 慢查询日志中 ?
-
查看超过多少秒算是慢查询 : show VARIABLES like ‘long_query_time’;
-
设置 慢的 阙值时间 : set global long_query_time =3;
(设置为3秒, 原本为 10秒)
2.1 重新设置后, 需要重新连接 mysql 或者 开一个新会话 查看
show VARIABLES like ‘long_query_time’;
2.2 新命令 查看 阙值时间
show global VARIABLES like ‘long_query_time’;
-
如何 记录 慢 sql 并 后续分析
3.1 select sleep(4);3.2 进入文件位置 : cd /usr/local/mysql/data/
3.3 查看
vim MiWiFi-R4CM-srv-slow.log
或者
cat MiWiFi-R4CM-srv-slow.log -
查询
当前
系统中 有 多少条 慢查询记录
show global status like ‘%Slow_queries%’;
** mysql 提供了 日志分析工具 mysqldumpslow
命令 mysqldumpslow --help
工作例子
** show Proflie 优化
含义:
mysql 提供 可以 用来 分析 当前会话中 语句执行的 资源消耗情况 ,
可以 用于 sql 的 调优 的 测量
默认情况下:
参数处于 关闭状态 , 只保存 最近15次 运行结果
分析步骤:
1.查看是否支持
show variables like ’ profiling ';
2.开启
set profiling = ‘ON’;
show variables like ‘profiling’;
show profiles;
常用 诊断SQL
show profile cpu,block,io for query ‘id’
如 show profile cpu ,block io FOR QUERY 618;
show profile all FOR QUERY 675;
日常开发 注意 结论:
全局查询日志
(测试环境下)
- 配置文件
2.命令行
**4. MySql 锁机制 (重中之重) **
锁 是计算机协调多个进程或线程并发访问某一资源的机制。
锁的分类 :
1.从对 数据库操作的类型 : 读锁 写锁
读锁 /(共享锁) 针对同一份数据 , 多个读操作 可以同时进行 而不会互相影响
写锁(排它锁) 当前操作没有完成前, 它会 阻断其他写锁和读锁。
2.从对 数据库操作的粒度分 : 表锁 行锁
** 三锁
手动增加锁:基本语法
lock table 表名 read(write),表名2 read(write),其他;
查看表上加过的锁 : show open tables;
释放锁: unlock tables;
表锁 (偏读)
特点:
偏向MyISAM 存储引擎, 开销小, 加锁快 ; 无死锁,锁定粒度大;
发生锁冲突概率最大,并发度低
总结:
在 MyISAM 引擎下的情况:
读锁
: lock table mylock read; unlock tables; show open tables;
会话1
如果 对 某表 进行了 读锁操作 , 只能 查询 该表的 数据 ,不能进行其他操作,如 增删改 ; 也不能 查询其他未锁定表的数据
。 (增删改也不可以,查都不行,改个毛)
会话2
可以 对 该表 进行 查询操作 , 也可以 查询或 更新 其他未锁定的表; 但如果 对 加了读锁操作的表 进行 更新 插入 删除操作, 将会 处于 堵塞等待状态,
必须等待 会话1 进行了 解锁操作 才能 对该表进行 更新操作。
写锁
lock table mylock write; unlock tables; show open tables;
会话1
如果 对 某表 进行了 写锁操作, 可以对该表 进行 增删改 操作; 但不能 对其他 表 进行 查询操作。
会话2
如果 对 该表 进行 查询操作, 将会 处于 堵塞等待状态
,
必须等待 会话1 进行 解锁操作;
但可以 对 其他表 进行 增删改查 操作
。
MyISAM 大总结
MyISAM (表锁)
在执行 查询语句(select) 前, 会自动 给 涉及的 所有表 加 读锁
;
在执行 增删改 操作前, 会 自动 给 涉及的 所有表 加 写锁
。
MySql 的 表级锁 有 两种 模式 :
····表共享读锁 ···· 表独占写锁
对 MyISAM 引擎下的表:
- 进行 读锁 操作 , 不会 堵塞 其他 进程对 同一表 的 读请求 , 但会 堵塞 对 同一表的 写请求 。 只有在 释放 读锁 的时候 才会 执行 其他 进程 写操作。
- 进行 写锁 操作 , 会 堵塞其他线程 对 同一表的 读和写 操作 ,
只有 释放 写锁 的时候 ,才会 执行 其他进程 的 读写操作。
读锁会堵塞写,但不会 堵塞读; 写锁 会 堵塞 读 和 写 操作。
分析表锁
MyISAM 的 读写锁 调度 是 写优先 ,这也是 MyISAM 不适合 做 写为主表的引擎。 因为写锁以后,其他线程不能做 大量任何操作, 大量的 更新 会使得 很难得到 锁, 从而出现 永久堵塞。
行锁 (偏写)
特点:
偏向 InnoDB 存储引擎, 开销大 ,加锁慢 ; 会出现 死锁;
锁粒度最小, 发生锁冲突 概率 最低 ,并发度 也 最高。
**行锁定基本演示 :
1.关闭 ;自动提交 (即 关闭事务自动提交): set autocommit=0;
总结:
在 InnoDB引擎下的情况:
关闭了 事务自动提交 (可重复读 隔离性失效)
会话1
更新某表的某条数据, 但没有 手动提交 commit
如果 会话2
同时更新该条数据 ,会处于 堵塞等待状态,
必须等待 会话1
提交事务 , 才会更新数据。
会话1
如果更新了 某表的某条数据1,但没有 手动提交 commit;
会话2
如果同时 更新 改变 另外某条数据2,不会处于 堵塞等待状态。
** 无索引 锁 升级为 表锁 (行锁 变 表锁)
varchar 类型 的 时候 没有写 单引号(’ ') ,导致使用了 类型转换 ,可能会导致 索引失效 引发 行锁 变成了 表锁。
** 间隙锁 危害
含义:
当我们 用 范围条件 而不是 相等条件 检索数据,并 请求 共享或排它锁 时,
InnoDB 会给 条件的 已有数据记录 的 索引项 加锁;
对于 键值在 条件范围内 但并不存在的 记录 ,叫做 间隙。
【危害】
因为在 query 执行过程中 通过 范围查找 的话, 会锁定 整个范围内的 所有
索引键值 , 即使 这个 键 并不存在。
间隙锁 弱点: 就是 当锁定一个范围键值 之后 ,即使 某些 不存在的键值 也会自动锁定 ,而 造成 锁定的时候 无法 插入锁定键值范围内 的 任何数据。
会给性能 带来很大危害。
好处 : 解决了 部分范围的 幻读问题。
**面试题: 如何锁定一行 (begin for update)
(人工上 行锁)
select xx … for update 锁定 某一行 后 , 其他 操作 会被 堵塞等待
直到 锁定行 提交commit(释放)才执行。
** 行锁 大总结:
优化建议:
- 尽可能 让 所有数据检索 通过 索引 完成 ,避免 索引失效 造成行锁变表锁
- 合理设计 索引 ,尽量 缩小 锁的范围
- 尽可能 较少 检索 条件 , 避免 间隙锁
- 尽可能 控制 事务大小 ,减少 锁定 资源量和 时间长度
- 尽可能 低级别 事务隔离
分析行锁
行锁就是一锁锁一行或者多行记录,mysql的行锁是基于索引加载的,所以行锁是要加在索引响应的行上,即命中索引
** 页锁
开销和加锁时间 界于 表锁和 行锁 之间, 会出现 死锁:
锁定 锁度界于 表锁 和 行锁 之间 ,并发性 一般。
5. 主从复制
基本原理 :
slave 会从 master 读取 binlong 来 进行 数据同步
三步骤+原理图
复制的基本原则
1.每个slave 只有 一个 master
2. 每个slave 只能 有一个 唯一的 服务器id
3. 每个 master 可以 有多个 slave
复制最大问题: 延时
AOF解决 延时问题
一主一从 常见配置
不想行写, 自己看视频了。