本文详细探讨了MySQL的存储引擎、索引优化和性能分析，包括索引的优势和劣势、不同类型的索引、如何利用EXPLAIN进行查询分析、优化案例以及锁机制。重点讲解了如何通过索引提升查询效率，避免索引失效，并提供了优化SQL的实用技巧和注意事项。
存储引擎
InnoDB和MyISAM性能对比对比
索引优化分析
SQL性能下降的原因
- 查询语句写的差。
- 索引失效：索引建了，但是没有用上。
- 关联 查询太多join（设计缺陷或者不得已的需求）。
- 服务器调优以及各个参数的设置（缓冲、线程数等）。
SQL机读执行顺序
select # 5
...
from # 1
...
where # 2
....
group by # 3
...
having # 4
...
order by # 6
...
limit # 7
[offset]
七种JOIN理论
-- 内连接 : 左右表共有的
select * from A inner join B on A.key = B.key
-- 左连接 : 左表独有的 + 左右表共有的
select * from A left join B on A.key = B.key
-- 右连接
select * from A right join B on A.key = B.key
-- 左表独有
select * from A left join B on A.key = B.key where B.key = null
-- 右表独有
select * from A right join B on A.key = B.key where A.key = null
-- 全连接 : 左表 + 右表所有
select * from A full outer join B on A.key = B.key
-- 左表独有 + 右表独有
select * from A full outer join B on A.key = B.key where A.key = null and B.key = null
索引
索引的简介
面试题 : 什么是索引?
索引是一种数据结构,目的在于提高查询效率
简单理解 : **索引是排好序的快速查找数据结构
索引影响的两大功能: 查找快(where的查询条件),排好序(order by)
除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的索引之外，还有哈希索引（Hash Index）等。
索引的优势
1.提高数据检索效率,降低数据库的IO成本
2.通过索引对数据列排好序,降低数据排序成本,降低CPU的消耗
索引的劣势
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
- 虽然索引大大提高了查询速度，但是同时会降低表的更新速度，例如对表频繁的进行INSERT、UPDATE和DELETE。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
- 索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引。
索引分类
- 单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
- 唯一索引：索引列的值必须唯一，但是允许空值(不允许为空字符串)。
- 复合索引：一个索引包含多个字段。
建议：一张表建的索引最好不要超过5个！
索引结构
BTree索引。
Hash索引。
Full-text全文索引。
R-Tree索引。
哪些情况需要建索引
- 主键自动建立主键索引（唯一 + 非空）。
- 频繁作为查询条件的字段应该创建索引。
- 查询中与其他表关联的字段，外键关系建立索引。
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
- 查询中统计或者分组字段（group by也和索引有关）。
- 在高并发下倾向创建组合索引
那些情况不要建索引
-
记录太少的表
-
经常增删改的表。
-
频繁更新的字段不适合创建索引。
-
Where条件里用不到的字段不创建索引。
-
重复且平均分配的值建索引的性能不大(假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的分布概率大约为50%，那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。)
性能分析
EXPLAIN简介
EXPLAIN是什么？
EXPLAIN：SQL的执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。
EXPLAIN怎么使用？
语法：explain + SQL语句。
EXPLAIN能干嘛？
可以查看以下信息：
id：表的读取顺序。
select_type：数据读取操作的操作类型。
possible_keys：哪些索引可以使用。
key：哪些索引被实际使用。
ref：表之间的引用。
rows：每张表有多少行被优化器查询。
EXPLAIN字段
id
id：反应表的读取和加载顺序。
其值有以下三种情况：
id相同，执行顺序由上至下。
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
id相同不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行。
select_type
select_type：数据查询的类型，主要是用于区别，普通查询、联合查询、子查询等的复杂查询。
- SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION 。
- PRIMARY：查询中如果包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
- SUBQUERY：在SELECT或者WHERE子句中包含了子查询。
- DERIVED：在FROM子句中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中。
- UNION：如果第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。
- UNION RESULT：从UNION表获取结果的SELECT。(两表的并集)
type
type：访问类型排列。
性能从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL。除了ALL没有用到索引，其他级别都用到索引了。
一般来说，得保证查询至少达到range级别，最好达到ref。
- system(忽略)：只有一行记录的系统表，这是const类型的特例，平时不会出现，这个也可以忽略不计。衍生表(子查询)只有哦一条数据的主查询(偶尔能达到).
create table test01(
tid int(3),
tname varchar(20)
);
insert into test01 values(1,'a');
commit;
-- 增加主键索引(约束)
alter table test01 add constraint tid_pk primary key(tid);
-- 衍生表 t(子查询)只有一条数据的主查询
explain select * from (select * from test01 limit 1) t where tid = 1;
- const：表示通过索引一次就找到了，const用于primary key或者unique索引(查询与索引类型有关,非指定索引即使只查到一条数据也不算)。因为仅仅能查到一条数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转化为一个常量。
explain select tid from test01 where tid=1;
/* 删除 primary 索引 */
alter table test01 drop primary key;
/* 修改索引为一般索引 */
create index test01_index on test01(tid);
- eq_ref：唯一性索引扫描，读取本表中和关联表表中的每行组合成的一行(通过当前表的索引键与关联表的索引键关联查询)，查出来唯一一条记录(有且只有1个,不能多,不能0)。除 了 system 和 const 类型之外, 这是最好的联接类型(但也很难达到,可遇不可求)。常见于惟一索引,主键索引.
alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid);
delete from teacher where tcid>3;
explain select t.tcid from teacher t, teacherCard tc where t.tcid = tc.tcid;
上述语句用到的索引是 teacher 表的 tcid 字段
如果 teacher 表的数据个数和连接查询的数据个数一致，才有可能满足 eq_ref 级别,因为有可能出现教师表查到的数据为a,b,c 教师卡表查到对应的数据为 d,e,f
-
ref：非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录。对于本表的每个索引键的查询,返回匹配所有行(可有0,多个)
-
range：只检索指定范围查询索引键的行，一般就是在WHERE语句中出现了
BETWEEN、< >、in等的查询(特殊: in有时候会失效,和数据量有关,当范围大于数据量一半就会转为无索引全表扫描)。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。
alter table teacher add index tid_index(tid);
explain select t.* from teacher t where t.tid <3;
-
index：Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。
也就是说虽然ALL和index都是读全表，查询全部索引中的数据,但是index是从索引中读的，ALL是从磁盘中读取的。 -
ALL：Full Table Scan，没有用到索引，查询全部表中的数据,全表扫描。
总结:
system/const : 结果只有一条数据
eq_ref : 结果多条,但每条数据唯一
ref : 结果多条,但每条数据0条或多条
-
possible_keys : 可能用到的索引,一种预测,但不一定被查询实际使用。
-
key : 实际使用到的索引,为null,则说明没索引
-
key_len : 索引的长度; 作用: 用于判断复合索引是否被完全使用
create table test_kl
(
name char(20) not null default ''
);
alter table test_kl add index index_name(name);
explain select * from test_kl where name='';
alter table test_kl add column name1 char(20);
alter table test_kl add index index_name1(name1);
explain select * from test_kl where name1='';
drop index index_name on test_kl;
drop index index_name1 on test_kl;
-- 增加一个复合索引
alter table test_kl add index name_name1_index (name, name1);
explain select * from test_kl where name1='';
alter table test_kl add column name2 varchar(20);
alter table test_kl add index name2_index(name2);
/* key_len=63 = 60+1(null)+2(varchar) */
explain select * from test_kl where name2='';
utf8 中，1 个字符占 3 个字节
char(20)，key_len = 60
gbk 中，1 个字符 2 个字节
latin 中，1 个字符 1 个字节
如果索引字段可以为 null，mysql 底层会用 1 个字节用于标识该字段
索引字段为 varchar，用 2 个字节标识可变长度
- ref
与 type 中的 ref 区分
指明当前表所参照的字段
select … where a.c=b.x
其中 b.x 可以是常量，const
- rows : 表示实际通过索引查询到的数据个数
Extra
Extra 字段 : 额外的字段
Using filesort
Using filesort: 性能消耗大，需要额外一次排序或查询
对于单索引:
- 如果排序和查找不是同一个字段，则会出现 Using filesort,反之,则不会
explain select * from test02 where a1 = ' ' order by a1;
/* 排序和查找不是同一个字段 Using filesort */
explain select * from test02 where a1 = ' ' order by a2; -- uusing fillsort
如何避免 Using filesort : where 哪些字段,order by 就跟着where的字段
对于复合索引:
- 不能跨列(最佳左前缀)
-- 删除索引
drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;
-- 添加复合索引
alter table test02 add index idx_a1_a2_a3(a1,a2,a3);
explain select * from test02 where a1=' ' order by a2;
explain select * from test02 where a1=' ' order by a3; -- Using filesort
explain select * from test02 where a2=' ' order by a3; -- Using filesort
explain select * from test02 where a3=' ' order by a1;
explain select * from test02 where a3=' ' order by a2; -- Using filesort
如何避免 : 从 where 到 order by 按照顺序使用,不要跨列或无序使用
Using temporary
- Using temporary : 性能消耗大,用到了多余的临时表,一般出现在group by 语句中
根据指定表查但不根据指定表分组,已经有表了,但不用
explain select a1 from test02 where a1 in ('1', '2', '3') group by a2;
如何避免 : 查哪些列(字段),就根据那些列group by
Using index
- Using index : 性能提升,覆盖索引(索引覆盖)
原因 : 不读取原文件,只从索引文件中国读取数据(不需要回表查询)
覆盖索引 : 只要select,where使用到的列,全都包含在复合索引中,就是索引覆盖
当索引无法覆盖查询的列,就不是 Using index
- 索引覆盖(Using index)对 possible_keys 和 key 造成的影响
- 如果没有 where，则索引只出现在 key 中
- 如果有 where，则索引出现在 key 和 possible_keys 中
注意：遇到以下情况，执行计划不会选择覆盖查询
1.select选择的字段中含有不在索引中的字段 ，即索引没有覆盖全部的列。
2.where条件中不能含有对索引进行like的操作。
Using where
- Using where : 既需要到索引表里查又需要回表查
当查询的字段不在索引的范围里,就会Using where,要回原表
impossible where
- impossible where : where 子句永远为false
优化案例
单表优化
建表
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, 'java', 1, 1, 2);
insert into book values(2, 'html', 2, 1, 2);
insert into book values(3, 'sql', 3, 2, 1);
insert into book values(4, 'C', 4, 2, 3);
commit;
优化一:
-- 优化一: 加覆盖索引
alter table book add index idx_bta(bid,typeid,authorid);
type索引级别有提升,但是因为跨列使用查询字段,所以仍有using filesort
优化二:
虽然可以回表查询bid,但是还是建议将bid放在索引中,可以提升使用using index(覆盖索引),减少回表查询
假设bid是主键索引,在使用普通索引查询bid的数据不需要回表查询
-- 根据SQL实际解析顺序: where->select->order by,调整索引顺序
-- 虽然可以回表查询bid,但是将bid放在索引中,可以提升使用using index(覆盖索引),减少回表查询,
alter table book add index idx_tab(typeid,authorid,bid);
注意 : 索引一旦升级优化,第一次使用新索引要将之前的废弃的索引删除,防止干扰操作
-- 删除旧索引
drop index idx_bta on book;
再次优化索引级别type,思路 : 以为where 中in函数范围查询有时会失效,若in范围查询放在复合索引的第一个位置,范围查询突然失效了,那索引就会跨列使用,不符合最佳左前缀原则,进而影响后面查询的索引
-- 再次优化,调整范围查询索引位置
-- 删除旧的索引
drop index idx_tab on book;
-- 调整索引位置
alter table book add index idx_atb(authorid,typeid,bid);
-- 把可能失效的索引放在后面
explain select bid from book where authorid=1 and typeid in(2,3) order by typeid desc;
本例中,同时出现Using where(回原表查询),和Using index(不回原表查询);原因是 : in范围查询使typeid的索引失效,相当于没有typeid这个索引,导致要回原表,索引这两个字段同时出现不冲突
下面这个例子可以证明:
bid作为查询结果没有算在正在使用索引里
小结:
- 使用索引不要跨列使用,要遵循最佳左前缀原则,保持索引定义和使用的一致性,
- 将含 in 的范围查询放到where条件的最后,防止索引失效,干扰其他索引使用
优化示例
where 子句若是等值查询,且符合最左前缀原则(不跨列),查询条件可以不同,SQL优化器可以自动调节顺序
create table test03
(
a1 int(4) not null,
a2 int(4) not null,
a3 int(4) not null,
a4 int(4) not null
);
-- 添加复合索引
alter table test03 add index idx_a1_a2_a3_4(a1, a2, a3, a4);
/* Using index */
/* 推荐按照复合索引的顺序写where条件查询 */
explain select a1, a2, a3, a4 from test03 where a1=1 and a2=2 and a3=3 and a4=4;
/* Using index */
/* 虽然where条件顺序和索引顺序不一致,但sql在真正执行前,经过 SQL 优化器调整后，效果与上一个查询语句一致,不建议这样写,运气活 */
explain select a1, a2, a3, a4 from test03 where a4=1 and a3=2 and a2=3 and a1=4;
通过key_len属性可以看到上两条语句都用了全部的复合索引 4个整型常量列 * 4 = 16字节
explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=3,order by a3;
以上SQL语句用到了复合索引中的a1,a2两个索引,所以该两个字段不需回表查询,导致产生Using index;而a4由于跨列使用(跨过了a3),造成了符合索引失效,需要回表查询,因此导致using where;也可根据key_len验证
explain select a1,a2,a3,a4 from test03 where a1=1 and a4=3 order by a3;
以上SQL语句出现了using filesort (文件内排序 : “多了一次额外的查找/排序”)
如何避免 : 不要跨列使用字段(where 和 order by 拼接起来不要跨列使用)
explain select a1,a2,a3,a4 from test03 where a1=1 and a4=3 order by a2,a3;
以上SQL语句不会出现using filesort,因为where 和 order by 拼接起来不会跨列
总结:
- 若(a,b,c,d) 复合索引,和使用的顺序完全一致且遵循最左前缀原则(不能跨列),则复合索引全部使用,若部分一致且不跨列使用 ,则使用不跨列的部分索引
- where 和 order by 不要跨列使用,否则会using filesort
双表优化
建表:
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),
cname varchar(20)
);
insert into course2 values(1, 'java');
insert into course2 values(2, 'python');
insert into course2 values(3, 'kotlin');
commit;
左外连接查询:
select * from teacher2 t left outer join course2 c
on t.cid=c.cid where c.cname='java';
索引加在哪张表?
结论: 遵循小表驱动大表
where 小表.x =大表.y;
假设小表有10条数据,大表有300条数据
小表驱动大表查询数据的循环逻辑
大表驱动小表查询数据的循环逻辑
以上2个FOR循环，最终都会循环3000次;但是对于双层循环来说:一般建议将数据小的循环放外层,数据大的循环放内层;因为编程语言的对程序的优化原则,外层循环越小,内层循环越大,对程序的性能越大,对资源消耗越小,程序速度越快,对于数据总量来说,外层小内层大
当编写 on t.cid = c.cid 连接条件时,将数据量小的表放左边
本次条件匹配查询可知,t,cid字段使用频繁,所以给该字段加索引(一般左外连接给左表加索引,右外连接给右表加索引)
给左表加了索引后的优化结果
-- 提高性能
alter table course2 add index idx_cname(cname);
- Using join buffer : SQL语句写的太差,MySQL引擎使用了连接缓存,Extra的一个字段
三表优化
原则:
1.小表驱动大表
2.索引建立在经常使用的字段上
避免索引失效的一些原则
复合索引
1.第一个索引不要使用范围查询
2.不要跨列或无序使用(不遵循最佳左前缀原则
- 例如(a,b,c) 不要where c = … and a=… order by b,或不要where c=… order by a
3.尽量使用全索引匹配(复合索引中每个索引都用上)
4.不要在索引上进行任何操作(计算,函数,类型转换),否则索引失效
5.左侧索引失效,右侧的全部索引跟着失效
- 例如: a,b,c 复合索引,b失效,b,c同时失效
注意 : 对于单值索引,多个索引同时使用,左边索引失效,不会影响右边索引
6.复合索引不能使用(!= , <> , < , >) 或 is null(is not null)(5.7版本之前),否则自身及右侧所有索引全失效,
7.同时使用多个单值索引用and连接时,只有一个生效(优先前者),多个单值索引用or连接,则都不生效,多个单值索引用and连接同时用!=则当前索引失效
SQL优化只是概率层面的,并非百分百,MySQL是否实际使用要通过explain预测
8.当查询数据量大的时候,当范围查询的数据和和全表差不多时数据库不会用索引
explain select * from book where authorid <1 and typeid =2;-- 复合索引只用到一个索引
explain select * from book where authorid <4 and typeid =2;-- 复合索引全失效
explain select * from book where authorid =1 and typeid >2;-- 复合索引全使用
一般而言: 在单值索引的情况下,范围查询,之后的索引失效
- 索引失效补救
尽量使用覆盖索引(using index)
复合索引 : idx_a_b_c(a,b,c)
select a,b,c where a = … and b = …;
9.where中使用like关键字,尽量以"常量"开头,不要以"%"开头,否则索引会失效
explain select * from teacher where tname like '%x%';
explain select * from teacher where tname like 'x%';
若必须以百分号开头,可以用覆盖索引查询挽救一部分
10.尽量不要使用类型转换(显示、隐式），否则索引失效
例如 : tname 字段是varchar类型的
正常使用索引
若将数字赋值给查询条件,索引会失效
原因 : 程序底层会将 123 -> ‘123’,即进行了类型转换,所以索引失效
11.单值索引尽量不要用or连接,否则or左边的索引也会失效
explain select * from teacher where tname = '' or tcid>1;
一些其他的优化方法
exists 和 in
若主查询数据集大于子查询,则用in
若子查询数据集大于主查询,则用exists
exists 语法 : 将主查询的结果,放到子查询结果中条件校验(判断是否有数据,若有数据,则校验成功),若符合校验,则保留数据;
例子:
select tname from teacher where exists(select * from teacher);
等价于
select tname from teacher;
意思是: 子查询可能有十列数据: id,name,age …,主查询要查name一列,并且name列存在于十列中,存在则校验成功,就能将name查出来
子查询数据不存在的情况
select tname from teacher where exists(select * from teacher where tid=9999);
等价于
select tname from teacher where exists(null);
in 的用法:
两张表A,B
select ...from table where tid in (1,3,5);
等价于
select * from A where id in (select id from B);
order by 优化
常出现的 Using filesort 字段中包含的两种算法 : 双路排序,单路排序(根据IO(访问硬盘文件的次数))
- 双路排序(MySQL 4.1 之前默认使用)
扫描两次磁盘(1.从磁盘中读取排序字段,在操作系统中的缓冲区buffer进行,对排序字段进行排序 2.扫描其他字段),IO较消耗性能
buffer是内存中一块空间,cache是CPU中的高速缓存
- 单路排序(MySQL 4.1 之后)
只读取一次(全部字段),在buffer中进行排序,隐患 : 不一定是单路(一次IO),有可能多次IO,原因 : 若数据量过大,无法将所有字段数据一次性读完,因此进行"分片读取,多次读取"
注意 : 单路排序比双路排序占用更多 buffer
用单路排序时,若数据量大,可考虑调大buffer的容量
set max_length_for_sort_data=1024
若set max_length_for_sort_data值太低(太低 :需要排序的列的总大小超过了set max_length_for_sort_data定义的字节数),则mysql会自动从单路->双路
提高 order by 效率的策略
- 选择使用单路,双路;调整buffer大小
- 避免 select * 因为 * 需要计算有哪些字段,而且很难用到覆盖索引
- 保证全部排序字段的一致性(要么全升序或降序)
SQL排查-慢查询日志
MySQL提供的一种日志记录,用于记录MySQL中响应时间超过阈值的SQL语句(可以查看哪条语句写的烂),(long_query_time 阈值默认 10 秒)
慢查询日志默认关闭,看不到的;建议 : 开发调优时打开,最终部署上线时关闭
检查是否开启了慢查询日志
show variables like '%slow_query_log%';
开启慢查询日志
- 临时开启(MySQL服务关闭后失效)
set global slow_query_log =1; -- 在内存中开启
- 永久开启
在my.cnf文件中追加配置 :vi /etc/my.cnf
在[mysqld]配置下追加一下命令
开启日志 : slow_query_log=1
日志文件写入路径 : slow_query_log_file=/var/lib/mysql/localhost-slow.log
修改日志响应时间阈值
-
临时修改
set global long_query_time=5;
重新登录账户后生效,无需重启服务 -
永久修改(立即生效)
vi /etc/my.cnf
[mysqld]下追加long_query_time=3
模拟慢查询SQL并查看日志
- 休眠模拟慢查询语句
select sleep(4); - 查询超过阈值的 SQL 数量
show global status like '%slow_queries%'; - 在 linux 命令行，通过日志查看慢查询 SQL 的详情
cat /var/lib/mysql/localhost-slow.log
通过 mysqldumpslow 工具更方便查看慢 SQL日志
mysqldumpslow命令常用参数
- s 排序方式
- r 逆序
- l 锁定时间
- g 正则匹配模式
命令使用标准语法
mysqldumpslow 各种参数 慢查询日志文件路径
/* 模拟慢查询 */
select sleep(5);
select sleep(4);
select sleep(3);
/* 获取返回记录最多的 3 个 SQL */
mysqldumpslow -s r -t 3 /var/lib/mysql/bigdata01-slow.log
/* 获取访问次数最多的 3 个 SQL */
mysqldumpslow -s c -t 3 /var/lib/mysql/bigdata01-slow.log
/* 按照时间排序，前 10 条包含 left join 查询语句的 SQL */
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/bigdata01-slow.log
模拟并通过 profiles 分析海量数据
模拟海量数据，存储过程/存储函数
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;
Copy to clipboardErrorCopied
创建存储函数
use testdata;
delimiter $
create function randstring(n int) returns varchar(255)
begin
declare all_str varchar(100) default 'abcdefghijklmnopqrestuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
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 $
冲突与解决
/* 开启慢查询日志，再创建存储过程/存储函数，报如下错误 */
/* ERROR 1418 (HY000):
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA
in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators variable) */
/* 临时解决 */
set global log_bin_trust_function_creators=1;
永久解决
vi /etc/my.cnf
[mysqld] 下追加 log_bin_trust_function_creators=1
通过存储函数插入随机整数
use testdata;
create function ran_num() returns int(5)
begin
declare i int default 0;
set i=floor(rand()*100);
return i;
end$
通过存储过程插入海量数据
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 $
插入数据
delimiter ;
call insert_emp(1000, 800000);
call insert_dept(10, 30);
/* 验证插入数据量 */
select count(1) from emp;
分析海量数据
show variables like '%profiling%';
/* profiling 影响性能，在部署实施前，应关闭此项 */
set profiling=on;
/* 记录 profiling 打开之后的所有 SQL 语句消耗的时间 */
show profiles;
/* 精确查询更多详情，Query_Id 参考上个语句的查询结果 */
show profile all for query 2;
show profile cpu, block io for query 2;
- 全局查询日志
show variables like '%general_log%';
/* 开启全局日志，记录开启之后的所有 SQL 语句 */
set global general_log=1;
/* 将日志记入表中 */
set global log_output='table';
/* 设置后执行一条查询 */
select count(1) from dept;
/* 显示日志信息 */
select * from mysql.general_log;
/* 将日志记入文件 */
set global log_output='file';
/* 通过默认保存地址查看日志文件 */
cat /var/lib/mysql/bigdata01.log;
- 开启 general_log 后，所有 SQL 会被记录到系统自带的
mysql.general_log表中
锁机制
解决因资源共享造成的并发问题
锁分类
按操作类型分
例子 : 两个人同时买最后一件衣服
读锁（共享锁）
对同一条数据(衣服)，多个读操作可以同时进行，互不干扰
写锁（排他锁）
如果当前写操作(买衣服的整个流程)没有完毕，则无法进行其他读操作,写操作
当衣服加写锁后被带进试衣间,另一个人在前台看不到
按操作范围分
冲突 : 有线程想进来,当前行或表被锁了,则需要等待
表锁
对整张表加锁
开销小，加锁快
无死锁
锁的范围(粒度)较大,容易发生锁冲突
同时操作一条数据的概率增高
并发度低
MyISAM 采用(自带)表锁
行锁
对一条数据加锁
开销大，加锁慢
容易出现死锁
锁的范围(粒度)较小，不易发生锁冲突(一行数据被操作的概率为1%)
并发度高(小概率发生高并发问题 : 脏读,幻读,不可重复读,丢失更新等)
InnoDB 行锁
页锁(少用)
表锁示范
表锁 : MYSQL/SQLSERVER 支持自增，Oracle 需要借助于序列来实现自增
建表
/* MYSQL/SQLSERVER 支持自增，Oracle 需要借助于序列来实现自增 */
create table tablelock
(
id int primary key auto_increment,
name varchar(20)
) engine myisam;
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');
查看加锁情况,加锁
/* 查看加锁情况 */
show open tables;
/* 加读锁 */
lock table tablelock read;
会话:session :每一个访问数据的dos命令行、数据库客户端工具,都是一个会话
结论:
同个会话内,对一个表加读锁,则该会话可以对该表读操作,不能写操作;且不能对其他表进行读写操作
其他会话,对加锁会话的表,可以读操作,不能写操作(当读锁解开后方可写);对其他表,可以读和写操作
行锁示范
会话1:
/* 加写锁 */
lock table tablelock write;
/* 不能对其他表进行任何操作 */
/* ERROR 1100 (HY000): Table 'dept' was not locked with LOCK TABLES */
select count(1) from dept;
当前会话可以对加了写锁的表进行任何操作(增删改查),但是不能操作(增删改查)其他表(可理解为只能操作一件衣服)
其他会话:
不能操作(增删改查)会话1加写锁的表,直到会话1的写锁释放
MySQL表级锁的锁模式
MyISAM在执行查询语句(SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（DML）前，会自动给涉及的表加写锁。
所以对My ISAM表进行操作，会有以下情况:
a、对MyISAM表的读操作（加读锁），不会阻塞其他进程（会话）对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
b、对MyISAM表的写操作（加写锁)，会阻塞其他进程（会话）对同一表的读和写操作,只有当写锁释放后，才会执行其它进程的读写操作。
表锁和行锁情况分析
分析表锁定:
查看哪些表加了锁:
show open tables; 1代表被加锁
分析表锁定的严重程度:
show status like '%table%';
Table_locks_immediate : 可能获取到的锁,意思是马上可以加锁的表锁数量
Table_locks_waited : 需要等待的锁或被n个线程加了锁,需要等待n次锁(若该值越大,说明存在的锁竞争越大)
一般建议:
Table_locks_immediate/Table_locks_waited> 5000
建议采用 InnoDB 引擎
否则使用 MyISAM 引擎
分析行锁定 : (默认INNODB)
建表
create table linelock
(
id int(5) primary key auto_increment,
name varchar(20)
)engine=innodb;
insert into linelock(name) values('1');
insert into linelock(name) values('2');
insert into linelock(name) values('3');
insert into linelock(name) values('4');
insert into linelock(name) values('5');
注意 : MySQL操作每一条SQL语句默认自动commit,oracle默认不会自动commit
所以为了研究行锁,需要暂时关闭自动commit,以后要手写commit;提交(会话级别的范围)
临时关闭自动commit
set autocommit = 0;start transaction;begin;
在会话1中 : 关闭自动提交后,插入一条数据不会提交到数据库中,而是在缓存中
会话2要操作数据会从数据库中找,两个会话互不干扰,会话2无法操作会话1中正在操作但未提交的数据,直到会话1释放写锁后,才能操作
对行锁的情况 :
操作同一条数据:
1.如果会话x对数据库的某条数据a进行DML操作(研究时关闭自动commit情况下),则其他会话必须等到会话x结束事务(commit/rollback)后,才能对数据a进行操作
2.表锁通过 unlock tables; 解锁也可以通过事务解锁,行锁通过事务解锁
操作不同条数据:
行锁,一次锁一条数据,操作不同数据,互不干扰
行锁的注意事项 :
1.若没有索引,行锁会转为表锁
示例 :
给name添加一个普通索引
show index from linelock;
/* 为 name 列增加索引 */
alter table linelock add index idx_linelock_name(name);
第一次操作,commit后
/* 当前会话操作 name='3' 的行 */
update linelock set name='a3x' where name='3';
/* 其他会话操作 name='4' 的行 */
/* name 列索引有效，不同的行操作互不影响 */
update linelock set name='a4x' where name='4';
第二次操作,数据被阻塞了(加上锁了)
会话1:
update linelock set name='a3x' where name=3;
会话2:
update linelock set name='a4x' where name=4;
阻塞原因 : name索引列发生了类型转换,则索引失效.索引在操作name=3时候失效,会从行锁转为表锁.其他会话不管操作其他什么数据都访问不了,整个表被锁住
间隙锁
行锁的一种特殊情况,值在范围内,但是不存在;意思是值本该在指定列的开区间范围内,但是被跳过了;
在关闭自动commit的情况下,会话1操作指定开区间范围内的数据,当区间内有间隙,MySQL会自动把有间隙的操作范围加间隙锁,相当于给间隙加了间隙锁,当想操作(增删改查)间隙值的时候,会话就会被阻塞
行锁:
innodb默认采用的锁
缺点 : 比表锁耗性能
优点 : 并发能力强,效率高
建议 : 高并发用innodb,否则用MYISAM
在查询语句末尾添加for update 即可给查询语句加当前行的写锁
select * from linelock where id=2 for update;
其他会话不能操作(增删改查)当前会话加锁的查询
主从复制
集群在数据库的一种实现
集群的特点:
负载均衡,失败迁移
在mysql集群中,一般分为主数据库和从数据库,在请求是先到主数据库进行增删改查,然后再将主数据库的增删改查的数据同步到从数据库,以后可以通过读写分离对于写操作全部访问主数据库,读操作全部访问从数据库,降低数据库压力
window安装MySQL连接linux远程数据库
当win端的MySQL远程链接时linux端MySQL时出现
说明win端和linux端的MySQL都要授权允许远程访问
MySQL 授权远程访问SQL语句
/* 在被访问的虚拟机 MySQL 中输入如下语句 */
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
-- 刷新权限,立刻生效
FLUSH PRIVILEGES;
若还是不成功,在linux下关闭防火墙
/* 如果仍然没有成功，在 linux 执行关闭防火墙 */
service iptables stop;
/* centos 7 以后使用 */
systemctl stop firewalld;
实现主从同步(主从复制)
主从同步的核心 : 二进制日志文件
文件作用 : 对数据库进行的增删改查操作都会在日志文件里记录
如何实现 :
1.master(主数据库)将改变的数据记录在本地的二进制日志文件(binary log)中,这个过程称为 : 二进制日志事件
2.slave(从数据库)通过IO线程,将master的binary log 拷贝(读取并写入)到自己库里的 relay log(中继日志文件)中
3.从节点的 SQL 线程检测到 relay-log 中新增加了内容后，会解析 relay-log 的内容，并在从数据库中执行。
MySQL 实现主从复制是异步，串行的，有延迟,因此,假如主库宕机了,从库不能完全同步主库的所有数据
主库和从库的比例是 1 : n
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
