MySQL进阶及优化

1. MySQL安装及配置:

5.4 - 5.x : MySQL整合了三方公司的新存储引擎 (使用 5.5)

Linux (CentOS x) 安装:

安装: rpm -ivh rpm软件名
- rpm -ivh MySQL-server-5.5.58-1.el6.x86_64.rpm
- rpm -ivh MySQL-client-5.5.58-1.el6.x86_64.rpm
启动: service mysql start
关闭: service mysql stop
重启: service mysql restart
可能遇到的错误:
- 如果安装时与某个软件 xxx 冲突, 则需要将冲突的软件卸载掉: yum -y remove xxx
- 如果提示 "GPG keys ..." 安装失败, 解决方案: rpm -ivh rpm软件名 --force --nodoeps
- 在重启Linux 之后, 登录MySQL, 可能报错: "/var/lib/mysql/mysql.sock不存在"; 原因: 是MySQL的服务没有启动
  - 解决1: 每次使用前, 手动启动服务: /etc/init.d/mysql start
  - 解决2: 设置开机自启: chkconfig mysql on; (关闭自启: chkconfig mysql off)
  - 检查是否开机自启: ntsysv
- 安装完成后, 日志有提示修改密码: /usr/bin/mysqladmin -u root password 'new_password'

MySQL 数据存放目录

通过命令来查看: ps -ef | grep mysql
MySQL 核心目录
- /usr/share/mysql : 配置文件目录
- /usr/bin : 命令目录 (mysqladmin, mysqldump 等命令)
- 安装及数据存放目录: datadir = /var/lib/mysql
- MySQL的启停脚本: /etc/init.d/mysql

MySQL 配置文件

mysql 在初始化时会有很多配置文件, 包括: my-huge.cnf, my-large.cnf, my-medium.cnf, my-small.cnf 等等, 这些配置文件是根据服务器的性能来决定使用的, 但是MySQL不支持这些配置文件; MySQL默认只支持 /etc/my.cnf; 因此需要将上述的配置文件复制一份为 /etc/my.cnf
命令: cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
- 注意: mysql5.5默认配置文件/etc/my.cnf; mysql5.6默认配置文件/etc/mysql-default.cnf

MySQL 字符编码

在登录MySQL后执行(SQL语句)即可查看MySQL的编码: show variables like '%char%';
注意: 修改编码只对修改之后创建的数据库生效, 所以在MySQL安装完毕后, 第一时间修改编码
在/etc/my.cnf 中修改:
- 在 [mysql] 中增加: default-character-set=utf8
- 在 [client] 中增加: default-character-set=utf8
- 在[mysqld] 中增加:
  - character_set_server=utf8
  - character_set_client=utf8
  - collation_server=utf8_general_ci
重启 MySQL 服务: service mysql start

2. MySQL 原理

MySQL 分层

MySQL 逻辑分层: 连接层(处理与客户端的连接) --> 服务层(使用接口, SQL优化) --> 引擎层(InnoDB, MyISAM) --> 存储层
- InnoDB (MySQL默认) : 事务优先, 适合高并发, 使用的是行锁(即加锁时锁一行数据)
- MyISAM : 性能优先, 使用的是表锁(一次锁一张表)
引擎相关的SQL命令:
- 查看MySQL支持那些引擎: show engines;
- 查看当前使用的引擎: show variables like '%storage_engine%';
在创建数据库或者表时可以指定使用的引擎及字符编码

-- 创建表时指定引擎为 MyISAM, 指定字符编码为utf8
create table tb(
	id int(4) auto_increment primary key,
	name varchar(5),
	dept varchar(5)
)ENGINE=MyISAM 
 DEFAULT CHARSET=utf8;

3. SQL 优化

原因: 性能低, 执行时间长, 等待时间长, SQL语句欠佳(尤其是连接查询), 索引失效, 服务器参数设置不合理(缓冲区及线程数)

SQL 语句优化:

sql语句编写过程: select dinstinct .. from .. join .. on .. where .. group by ... having .. order by .. limit ..
sql语句解析过程: from .. on .. join .. where .. group by .. having .. select dinstinct .. order by .. limit ..
SQL 语句优化: 主要是优化索引
- 索引:(相当于书的目录) index 是帮助MySQL高效获取数据的数据结构; 索引是数据结构(树: B树(MySQL默认), Hash树 ...)
- 索引的弊端:
  - 索引本身很大, 可以存放在内存/硬盘(通常为硬盘)
  - 索引不是所有情况均适用: 少量数据, 频繁更新的字段, 很少使用的字段不适合使用索引
  - 索引会降低增删改的效率
- 索引的优势:
  - 提高查询的效率(降低IO的使用率)
  - 降低CPU使用率(如: order by desc; 因为B树索引本身就是一个排好序的索引, 因此在使用索引时, 可以直接使用)
- B 树:(使用的是B+树)
  - 3层的B树可以存放上百万条数据
  - B 树一般指的是 B+ 树, 数据全部存放在叶子节点
  - B+ 树中查询的任意数据次数: n次 (n代表B树的高度)

4. 索引

索引的分类

单值索引 : 使用单列作为索引, 一个表可以有多个单值索引
主键索引 : 不能重复, 一般使用 id, 且不能为 null
唯一索引 : 索引不能重复, 唯一索引一般使用 id; 唯一索引可以是 null
复合索引 : 多个列构成的索引(相当于书的二级目录); 注意复合索引不一定要求所有列都中

创建索引

语法(创建索引方式一): create 索引类型索引名 on 表(字段)
语法(创建索引方式二): alter table 表名 add 索引类型索引名 (字段)
注: 如果一个字段是 primary key, 则该字段默认就是主键索引
例子1(创建索引方式一):
- 单值索引(直接写index, 索引类型就是单值索引): create index dept on tb(dept);
- 唯一索引(unique index): create unique index name on tb(name);
- 复合索引(也用index, 和单值索引的区别在后面的列名处): create index dept_name_index on tb(dept, name);
例子2(创建索引方式二):
- 单值索引: alter table tb add index dept (dept);
- 唯一索引: alter table tb add unique index name (name);
- 复合索引: alter table tb add index dept_name_index (dept, name);

删除索引

语法: drop index 索引名 on 表;
例子:
- drop index name on tb;

查看索引

语法: show index from 表;
例子:
- show index from tb;

SQL 性能问题

分析SQL的执行计划: 关键字 explain : 可以模拟SQL优化器执行SQL语句, 从而让开发人员知道自己编写的SQL状况
MYSQL 查询优化器会干扰我们的优化
执行查询计划:
- 语法: explain SQL语句
- 例如: explain select * from tb;

explain + SQL 的执行结果表头字段说明:

字段值: id(编号); select_type(查询类型); table(表); type(类型); possible_keys(预测用到的索引); key(实际使用的索引); key_len(实际使用索引的长度); ref(表之间的引用); rows(通过索引查询到的数据量); Extra(额外的信息)
id :
- id 值相同(多表查询): 从上往下顺序执行; 表的执行顺序因数量的个数改变而改变(原因:笛卡尔积); 数据小的表, 优先查询
- id 值不同(子查询): id值越大越优先查询 (原因: 在嵌套子查询时, 先查内层,再查外层)
- id 值有相同, 又有不同: id 值越大越优先, id值相同, 从上往下顺序执行
select_type :
- PRIMARY : 包含子查询SQL中的主查询, (最外层)
- SUBQUERY : 包含子查询SQL中的子查询 (非最外层)
- SIMPLE : 简单查询 (不包含子查询, 不包含 unique连接查询)
- DERIVED : 衍生查询 (使用到了临时表)
  - 在 from 子查询中只有一张表(如: explain select cr.cname from (select * from course where tid in (1,2)) cr;)
  - 在 from 子查询中, 如果有 table1 union table2, 则 table1 就是 derived(衍生表), table2 就是 union
    - 如: explain select cr.cname from (select * from course where tid = 1 union select * from course where tid = 2) cr;
- UNION : 如上例 table2 就是 union
- UNION RESULT : 告知开发人员那些表之间存在 union 查询
type : 索引类型
- 结果值从好到坏排序: system > const > eq_ref > ref > range > index > all
- 其中: system, const 只是理想情况; 实际能达到的在 ref 后面
- system : 只有一条数据的系统表; 或衍生表只有一条数据的主查询
- const : 仅仅能查到一条数据的SQL, 用于 primary key 或 unique 索引 (查询类型与索引类型有关)
- eq_ref : 唯一性索引(前提该字段唯一, 且为该字段添加了索引; 常用于主键索引或唯一索引): 对于每个索引键的查询, 返回匹配唯一行数据(有且只有1个, 不能多, 不为0)
  - 增加主键语法: alter table 表名 add constraint pk_主键字段 primary key(主键字段);
  - 增加唯一约束语法: alter table 表名 add constraint uk_唯一字段 unique index(唯一字段);
  - 增加外键约束语法: alter table 表1 add constraint fk_表1_表2_字段 foreign key(外键字段) references 表2(外键字段);
- ref: 非唯一性索引, 对于每个索引键的查询, 返回匹配的所有行(0个或多个)
- range : 检查指定范围的行, where 后面是一个范围查询(between, >, <, >=, in(有时候会失效, 从而转为all) )
- index : 查询全部索引中的数据
  - explain select tid from teacher; (tid已经是索引, 所以前面这条SQL语句的type是index)
- all : 查询全部表数据
  - explain select cid from course; (cid 不是索引, 需要全表扫描, 即需要所有表中的所有数据)
possible_keys : 可能用到的索引, 是一种预测; 如果值是NULL, 则说明没用索引
key: 实际使用到的索引
key_len : 索引的长度; 作用: 用于判断复合索引是否被完全使用
- 在MySQL中ut8是一个字符占3个字节; (GDK:1个字符2个字节; latin:一个字符一个字节)
- 如果索引字段可以为NULL, 则会使用1个字节用于标识
- MySQL使用两个字节用来标识可变长度(varchar)
ref: 用于指明当前表所参照的字段
rows: 被索引优化查询的数据的个数(实际通过索引而查询到的数据的个数)
extra:
- using filesort: 性能消耗大; 需要 "额外" 一次排序(查找) (常见于 order by)
  - 对于单索引, 如果排序和查找的是同一个字段, 则不会出现 using fileout; 如果排序和查找的不是同一个字段, 则会出现 using fileout; 避免: where 那个字段, 就order by 那个字段
  - 复合索引: 不能跨列(最佳左前缀) : where 和 order by 按照复合索引的顺序使用, 不要跨列或无序使用
- using temporary: 性能损耗较大, 用到了临时表 (常见于 group by 语句中); 所以: 查询哪些列, 就根据那些列来 group by..
- using index: 性能提升; 索引覆盖(覆盖索引: 使用到的列全在索引中); 原因: 不读取原文件, 只从索引文件中获取数据(不需要回表查询)
  - 如果用到了索引覆盖(using index时), 会对 possible 和 key 造成影响
    - 如果没有 where, 则索引只出现在key中
    - 如果有 where,则索引会出现在 possible 和 key 中
- using where : 需要回原表查询
- impossable where : where 子句永远为 false
- using join buffer : MySQL引擎使用的连接缓存, 说明SQL写的太差了, 需要优化
- 总结:
  - 如果 (a, b, c, d) 复合索引, 和使用的顺序全部一致(且不跨列使用), 则复合索引全部使用, 如果部分一致(且不跨列使用), 则使用部分索引
  - where 和 order by 拼起来, 不要跨列使用

explain 查询结果总结

system/const: 结果只有一条数据
eq_ref: 结果多条, 但是每条数据是唯一的
ref: 结果多条, 但是每条数据是0或多条

优化案例

单表优化

创建表增加数据

create table book (
	bid int(4) primary key,
	name varchar(20) not null,
	authorid int(4) not null,
	publicid int(4) not null,
	typeid int(4) not null
);

insert into book values(1, 'tjava', 1, 1, 2);
insert into book values(2, 'tc', 2, 1, 2);
insert into book values(3, 'wx', 3, 2, 1);
insert into book values(4, 'math', 4, 2, 3);

查询语句

explain select bid from book where authorid=1 and typeid in (2, 3) order by typeid desc;

优化: 加索引
- alter table book add index idx_bta (bid, typeid, authorid); (删除: drop index idx_bta on book;)
  - 执行查询语句的结果: type: index, Extra:using filesort; Using where; Backward index scan; Using index
- 进一步优化, 按照SQL的执行顺序增加索引: alter table book add index idx_tab (typeid, authorid, bid);
  - 执行查询语句的结果: type: index Extra: Using where; Backward index scan; Using index
- 进一步优化: 思路: 因为范围查询in有时候会失效, 所以索引也应该按照atb的形式来建
  - drop index idx_tab on book;
  - 创建: alter table book add index idx_atb (authorid, typeid, bid);
总结:
- 最佳左前缀, 保持索引的定义和使用顺序一致
- 索引需要逐步优化
- 将含 in 的范围查询放在where条件的最后, 防止失效
- 上例中: Extra:Using where(需要回原表); Using index(不需要回原表); 原因: SQL 查询语句中 authorid 在索引中(直接在索引中能查到), 但是 typeid in 会使索引失效, 因此相当于没有 typeid 这个索引, 所以需要回原表( using where)

双表优化

创建表及增加数据

	create table teacher2 (
		tid int(4) primary key,
		cid int(4) not null
	);

	insert into teacher2 values(1,2);
	insert into teacher2 values(2,1);
	insert into teacher2 values(3,3);

	create table course2 (
		cid int(4) primary key,
		cname varchar(20)
	);

	insert into course2 values (1, 'java');
	insert into course2 values (2, 'pythton');
	insert into course2 values (3, 'kotlin');

SQL查询语句

	select * from teacher2 t left join course2 c
	on t.cid=c.cid where c.cname='java';

	explain select * from teacher2 t left outer join course2 c
	on t.cid=c.cid where c.cname='java';

优化: 加索引
- 原则: 1. (小表驱动大表) select ... where 小表=大表; 2. 索引建立在经常使用的字段上[一般情况下左外连接给坐表加索引, 右外连接给右表加连接]
- 加索引: alter table teacher2 add index index_teacher2_cid (cid);
- 加索引: alter table course2 add index index_course2_cname (cname);

三表优化

小表驱动大表
索引建立在经常查询的字段上

7. 避免使索引失效的一些原则

复合索引, 不要跨列或无序使用(最佳左前缀)
- 如索引: (a, b, c); 使用时: where a ... and b ... order by c;
复合索引, 尽量使用全索引匹配
- 如索引: (a, b, c); 使用的时候索引全部使用, 不要使用两个, 或一个
不要在索引上进行任何操作(计算, 函数, 类型转换), 否则索引失效
复合索引不能使用不等于(!= <>) 或 is null (is not null), 否则自身以及右侧索引全部失效
尽量不要使用类型转换(显示, 隐式), 如: varchar字段使用了int类型去查询, 则索引失效
where语句中尽量不要使用 or, 使用 or 但是没有把 or 的所有字段加上索引会使索引失效
where 语句中使用 not in, in(有时候会失效)
like 模糊查询尽量以 "常量" 开头, 不能使用 "%" 开头, 否则索引失效
综上: 尽量使用索引覆盖(using index);(查询的字段全部在索引里面)

一些其他的优化方法

exists 和 in 优化
- 如果主查询的数据集大, 则使用 in; 如果子查询的数据集大, 则使用 exists
- exist 语法: select ... from table where exists (子查询);
  - 说明: 将主查询的结果, 放在子查询结果中互进行条件校验(看子查询是否有数据, 如果有数据则校验成功), 如果符合校验, 则保留数据; 否则不保留数据
  - 如: select tname from teacher where exists (select * from teacher); (等价于: select tname from teacher;)
- in 语法: select ... from table where 字段 in (子查询):
  - 如: select tcid from teacher where tcid in (select tcid from teacherCard);
order by 优化
- 使用order by 时, 经常在Extra中会出现: using filesort; using filesort 底层有两种算法(根据IO算法): 双路排序, 单路排序; (mysql 4.1之前默认双路, MySQL4.1之后默认是单路)
- 双路排序: 1. 第一次从磁盘读取排序字段, 对排序字段进行排序(在Buffer中进行排序); 2. 第二次扫描其他字段
- 单路排序: 只读取一次(全部字段), 在Buffer中进行排序, 但这种单路排序会有一定的隐患 (不一定真的是 "单路|1次IO", 有可能多次IO); 原因: 如果数据量特别大, 无法将所有字段的数据一次性读取完毕, 因此会进行 "分片" 读取
  - 单路排序会比双路排序占用更多的Buffer; 单路排序在使用时, 如果数据大, 可以考虑调大Buffer的容量大小
  - 调大Buffer的SQL: set max_length_for_sort_data = 1024;
  - 如果 set max_length_for_sort_data 设置的值太低(需要排序的列的总大小超过了max_length_for_sort_data定义的字节数), MySQL会自动从单路切换到双路;
- 提高 order by 查询的策略
  - 选择使用单路, 双路; 调整buffer的容量大小
  - 避免使用: select * ...
  - 复合索引不要跨列使用, 避免出现 using filesort
  - 保证全部的排序字段, 排序的一致性(全升或全降)

9. SQL排查-慢查询日志

慢查询日志: MySQL提供的一种日志记录, 用于记录MySQL各种响应时间超过阀值的SQL语句(long_query_time, 默认10秒)
默认情况下, 慢查询日志是关闭的, 所以开发调优时打开, 而最终部署时关闭
检查是否开启慢查询日志SQL: show variables like '%slow_query_log%';

开启慢查询日志

临时开启(MySQL服务关闭时关闭慢查询) : set global slow_query_log = 1; -- 在内存里面开启; 1是开启, 0是关闭
永久开启: 编辑: /etc/my.conf 配置文件, 在[mysqld]下面增加
- slow_query_log=1
- slow_query_log_file=/var/lib/mysql/localhost-slow.log

慢查询阀值:

查看慢查询日志的阀值: show variables like '%long_query_time%';
设置慢查询日志的阀值:
- 临时设置阀值(重新登录后生效): set global long_query_time = 5;
永久开启: 编辑: /etc/my.conf 配置文件, 在[mysqld]下面增加
- long_query_time = 5

查询超过阀值的SQL：show global status like '%slow_queries%';

慢查询的sql被记录在日志中, 可以通过查看日志, 检查具体的慢SQL
通过mysqldumpslow工具查看慢SQL: 可以i通过一些过滤条件, 快速查找出需要定位的慢SQL
mysqldumpslow 常用参数
- s : 排序方式
- r : 逆序
- i : 锁定时间
- g : 正则匹配模式

mysqldumpslow 实用例子:

mysqldumpslow 语法: mysqldumpslow 各种参数慢查询日志文件的位置
获取返回记录最多的3个SQL

-　mysqldumpslow　－ｓ r -t 3 /var/lib/mysql/localhost-slow.log
获取访问次数最多的3个SQL

-　mysqldumpslow　－ｓｃ -t 3 /var/lib/mysql/localhost-slow.log
按照时间排序, 前10条包含left join查询语句的SQL
- mysqldumpslow　－ｓ t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

10. 分析海量数据

模拟海量数据, 使用存储过程(无 return)/存储函数(有 return)

	create database testdata;

	use testdata;

	create table dept (
		dno int(5) primary key default 0,
		dname varchar(20) not null default '',
		loc varchar(30) default ''
	)engine=innodb default charset=utf8;

	create table emp (
		eid int(5) primary key,
		ename varchar(20) not null default '',
		job varchar(20) not null default '',
		deptno int(5) not null default 0
	) engine=innodb default charset=utf8;

通过存储函数插入海量数据:

创建存储函数:

	-- 创建randstring() 存储函数用于模拟员工的名称
	delimiter $
	create function randstring(n int) returns varchar(255)
	begin 
		declare all_str varchar(100) default 'abcdefghigklmnopqrstubwxyzABCDEFGHIGKLMNOPQRSTUVWXZY';
		declare return_str varchar(255) default '';
		declare i int default 0;
		while i < n 
		do 
			set return_str = concat(return_str, substring(all_str, FLOOR(1+rand()*52), 1));
			set i = i+1;
		end while;
		return return_str;
	end$


	-- 产生随机整数
	create function ran_num() returns int(5)
	begin 
		declare i int default 0;
		set i = floor(rand()*100);
		return i;

	end$

存储函数报错了解:
- You have an error in your SQL syntax; 说明SQL语句错误, 修改SQL语句
- This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled ; 是因为存储过程/存储函数在创建时与之前的开启慢查询日志冲突了
- 临时解决: (开启 log_bin_trust_function_creators )
  - 查看是否开启: show variables like '%log_bin_trust_function_creators%';
  - 开启: set global log_bin_trust_function_creators = 1;
- 永久解决: 修改 /etc/my.conf; 在 [mysqld]后面追加
  - log_bin_trust_function_creators = 1
创建存储过程插入海量数据, emp表

	create procedure insert_emp(in eid_start int(10), in data_times int(10))
	begin	
		declare i int default 0;
		set autocommit = 0;

		repeat 
			insert into emp values(eid_start + i, randstring(5), 'other', ran_num());
			set i=i+1;
			until i=data_times
		end repeat;
		commit ;
	end $

创建存储过程插入海量数据, dept表

	create procedure insert_dept(in dno_start int(10), in data_times int(10))
	begin	
		declare i int default 0;
		set autocommit = 0;

		repeat 
			insert into dept values(dno_start + i, randstring(6), randstring(8));
			set i=i+1;
			until i=data_times
		end repeat;
		commit ;
	end $

插入数据
- 将SQL的语句结束符改回来: delimiter ;
- 调用存储函数给emp表增加80万条数据: call insert_emp(1000, 800000);
- 调用存储过程给dept表增加30条数据: call insert_dept(10, 30);

分析海量数据

profiles

查看是否开启(默认是关闭的): show variables like '%profiling%';
开启: set profiling = on;
show profiles: 在打开 profiling之后, 会记录全部SQL查询语句所花费的时间; 缺点: 只能看到总共花费的时间, 但是不能看到各个硬件消费的时间(如: cpu, io)

精确分析: sql诊断

诊断全部信息SQL命令(其中query_Id是show profiles查询到的): show profile all for query query_Id;
- 如: show profile all for query 1;
诊断CPU, IO相关的信息的命令: show profile cpu, block io for query query_id;
- 如: show profile cpu, block io for query 1;

全局的查询日志

记录开启之后的全部SQL语句, (这些全局的记录操作, 仅仅在调优, 开发过程中打开, 在实际部署时一定要关闭)
开启后会记录所有的SQL: 记录在 mysql.general_log表中或文件中
查看全局日志: show variables like '%general_log%';
开启全局日志:
- 将SQL记录在mysql.general_log 表中
  - set global general_log = 1;
  - set global log_output='table';
- 将SQL记录在本机文件中
  - set global log_output='file';
  - set global general_log = 1;
  - set global general_log_file='/tmp/general.log';

11. 锁机制

解决因资源共享而造成的并发问题

锁分类

操作类型:
- 读锁(共享锁): 对同一个数据, 多个读操作可以同时进行, 互不干扰
- 写锁(互斥锁): 如果当前写操作没有完毕, 则无法进行其他的读操作, 写操作
操作范围
- 表锁: 一次性对一张表整体加锁; 如: MySIAM 存储引擎使用的是表锁, 开销小, 加锁快; 无死锁, 但锁的范围大, 容易发生锁冲突, 并发度低
- 行锁: 一次性对一条数据加锁; 如: InnoDB 使用的就是行锁, 开销大, 加锁慢, 容易出现死锁, 但锁的范围小, 不易发生锁冲突, 并发度高(很小概率发生并发问题: 脏读, 幻读, 不可重复读, 丢失更新等问题)
- 页锁

表锁:

创建示例表并增加数据

	create table tablelock (
		id int primary key auto_increment,
		name varchar(20)
	) engine=myisam default charset=utf8;

	insert into tablelock(name) values('a1');
	insert into tablelock(name) values('a2');
	insert into tablelock(name) values('a3');
	insert into tablelock(name) values('a4');
	insert into tablelock(name) values('a5');

增加读写锁语法: lock table 表1 read/write, 表2 read/write, ...
查看加锁的表(1代表加锁): show open tables;
分析表锁定的严重程度: show status like 'table%';
- Table_locks_immediate: 即可能获取到的表数
- Table_locks_waited : 需要等待的表锁数(如果该值越大, 说明存在越大的锁竞争)
- 一般: Table_locks_immediate/Table_locks_waited > 5000 , 则使用InnoDB 引擎, 否则使用MyISAM引擎

锁示例

加读锁(当前会话0的效果: 只能读当前加锁的表, 不能对其他表进行任何操作): lock table tablelock read;
- 对会话0的影响: 如果某一个会话对A表进行了read锁, 则该会话可以对A表进行读操作, 不能进行写操作, 且该会话不能对其他表进行读/写操作
- 对其他会话的影响: 会话0给A表加锁, 其他会话的操作: 1. 可以对其他表(A表以外的表)进行读/写操作; 2. 对A表, 可以读操作, 但是写操作需要等待会话0释放锁
- 释放锁: unlock tables;
加写锁: lock table tablelock write;
- 对当前会话0的影响: 可以对加了锁的表进行任何操作(增删改查); 但是不能对其他表进行任何操作
- 对其他会话的影响: 对会话0中加写锁的表可以进行增删改查的前提是: 等待会话0释放写锁; 对会话0除加锁以外的表可以进行任意的操作(增删改查)

行锁

创建示例表并增加数据

	create table linelock (
		id int primary key auto_increment,
		name varchar(20)
	) engine=InnoDB default charset=utf8;

	insert into linelock(name) values('a1');
	insert into linelock(name) values('a2');
	insert into linelock(name) values('a3');
	insert into linelock(name) values('a4');
	insert into linelock(name) values('a5');

mysql 默认是自动commit, 而Oracle默认不会自动commit, 需要手工commit; 为了学习行锁, 暂时关闭MySQL的自动commit
- 关闭自动提交: set autocommit = 0;
- 开启自动提交: set autocommit = 1;
结论:
- 如果会话x对某条数据a进行DML操作(需要在关闭自动提交的情况下), 则其他会话必须等到该会话结束事务(执行 commit/rollback操作后)后, 才能对数据a进行操作
- 表锁是通过 unlock tables 或事务来释放锁, 行锁是通过事务释放锁
- InnoDB 默认使用的是行锁; 缺点: 比表锁性能损耗大, 优点: 并发能力强, 效率高; 因此: 高并发使用Innodb, 否则使用MyISAM
注意:
- 如果没有索引, 则行锁会转为表锁
- 间隙锁: 行锁的一种特殊情况: 值在范围内, 但却不存在; 对于间隙: MySQL会给间隙枷锁, 就称为间隙锁

行锁分析

show status like '%innodb_row_lock%';
- Innodb_row_lock_current_waits : 当前正在等待锁的数量
- Innodb_row_lock_time : 等待总时长, 从系统启动到现在一共等待的时间
- Innodb_row_lock_time_avg : 平均等待时长, 从系统启动到现在平均等待的时间
- Innodb_row_lock_time_max : 最大等待时长, 从系统启动到现在最大一次等待的时间
- Innodb_row_lock_waits : 等待次数, 从系统启动到现在最大一次等待的时间

# mysql

1. MySQL安装及配置:

Linux (CentOS x) 安装:

MySQL 数据存放目录

MySQL 配置文件

MySQL 字符编码

2. MySQL 原理

MySQL 分层

3. SQL 优化

SQL 语句优化:

4. 索引

索引的分类

创建索引

删除索引

查看索引

SQL 性能问题

explain + SQL 的执行结果表头字段说明:

explain 查询结果总结

优化案例

单表优化

双表优化

三表优化

7. 避免使索引失效的一些原则

一些其他的优化方法

9. SQL排查-慢查询日志

开启慢查询日志

慢查询阀值:

查询超过阀值的SQL：show global status like '%slow_queries%';

mysqldumpslow 实用例子:

10. 分析海量数据

通过存储函数插入海量数据:

分析海量数据

profiles

精确分析: sql诊断

全局的查询日志

11. 锁机制

锁分类

表锁:

锁示例

行锁

行锁分析

数据库连接池以及ApacheCommonDBUtils工具类使用

Maven 配置