MySQL
下面记录了MYSQL学习过程中的一些理论知识笔记。
1、基础
- MySQL的逻辑架构
-
连接层:
Connection Pool
:管理用户连接,线程请求。 -
服务层:
SQL Interface
:交互层。接受命令和返回结果。Parser
:SQL语句解析器。Optimizer
:查询优化器。比如联合索引,等于常量时,顺序可变也一样。Caches & Buffers
:查询缓存。Management Serveices & Utilities
:管理层。备份、安全、复制、集群等等。。
-
引擎层:
Pluggable Storage Engines
:存储引擎,InnoDB、MyISAM等。 -
存储层:
File System
:数据落地到磁盘上,就是文件的存储。
-
MySQL存储引擎
最常用的就是InnoDB(默认)、MyISAM。
虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。如图:
https://blog.csdn.net/qq_27607965/article/details/79925288区别记住红色圈圈的话:InnoDB事务行锁和外键,真实数据也缓存。分析:支持事务,那就保证了数据安全。支持行锁,适合高并发。也缓存真实数据,所以对内存要求较高。相对而言,更适合写。
SHOW ENGINES
-
SQL性能下降和优化
原因:没使用索引,索引失效,sql差(多表连接、产生了临时表),内存不足,cpu性能不高等。
解决:开启慢查询,使用explain分析,使用Show Profile分析查询的生命周期,参数调优。
-
执行顺序
- select 、from 、join on 、 where 、group by 、having 、order by 、 limt offset
- from、on join、where、group by、 having、 select、 distinct、 order by、 limit offset
-
join语法
-
语法分页记录主键id,可以大于该id,然后使用limit分页
-
UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中
2、索引
-
索引一种数据结构,包括b-数和b+树,hash等。有一个索引文件生成,存在硬盘。
-
索引优点,用于快速查找(where)和排序(order by)。缺点,索引也占空间,写操作过多也会改索引文件。
额外的文件保存特殊的数据结构。
查询快,但是更新、删除、新增也会操作索引文件,所以也会慢点。
某字段查询的频繁可以创建索引,但是过多也不行,因为索引有文件,也会更新。
-
索引分类:
-
普通单值索引:一个索引只包含单个列,一个表可以有多个单列索引。
-
唯一索引:索引列的值必须唯一,但是允许空值。主键 ,不允许。
-
复合索引:一个索引包含多个字段。联合普通索引,联合唯一索引,联合主键索引
建议:一张表建的索引最好不要超过5个!
-
-
索引SQL
CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length)); DROP INDEX [indexName] ON tabName; SHOW INDEX FROM tabName -- 还可以使用ALTER改表的方式
-
适合建立索引的情况
适合 经常作为查询和排序(分组)条件的字段。 组合索引优于单值索引 不适合 频繁更新的字段 数值变化少比如性别 数据量少
-
索引失效的情况
记住一条,最左原则!!!
记住一条,最左原则!!!
记住一条,最左原则!!!
使用计算,类型转化,范围,like,等于不等于,排序等操作都有可能导致索引失效,具体情况具体分析。 最好的用法: 全值匹配;最左原则;覆盖索引;范围之后索引失效。extra内容等 具体如下: - 全值匹配我最爱。 - 最佳左前缀法则。 - 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。 - 索引中范围条件右边的字段会全部失效。 - 尽量使用覆盖索引(只访问索引的查询,索引列和查询列一致),减少`SELECT *`。 - MySQL在使用`!=`或者`<>`的时候无法使用索引会导致全表扫描。 - `is null`、`is not null`也无法使用索引。 - `like`以通配符开头`%abc`索引失效会变成全表扫描。 - 字符串不加单引号索引失效。 - 少用`or`,用它来连接时会索引失效。
3、explain
- explain
-
id执行顺序:id相同,由上到下,id不同,越大越先执行。
-
select_type:
SIMPLE 简单的select查询,查询中不包含子查询或者UNION PRIMARY 查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY SUBQUERY 在SELECT或WHERE列表中包含了子查询 DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中 UNION
-
table,指的就是当前执行的表
-
type:查询使用了哪种类型。一般要ref,最差all
system (一行数据) const(一次索引,主键,唯一索引) eq_ref (唯一性索引扫描,只有一个行,本表中和关联表) ref(非唯一性索引扫描,索引访问,可能有多行) range(where语句中出现between、< 、>、in等的查询) index (index类型只遍历索引树) all(遍历全表)
-
key、possible_keys 和key_len:真正用到的索引、可能用到的索引、使用的索引长度。
-
ref,显示索引的哪一(几)列被使用了。
-
rows 预计找到目标数据所需要读取的行数
-
Extra,额外信息,比如使用了,额外“文件排序“,创建了临时表,使用覆盖索引,比如使用了where条件。
文件排序:MySQL中无法利用索引完成的排序操作成为"文件内排序"
临时表:使用了临时表保存中间结果,MySQL在対查询结果排序时使用了临时表。常见于排序
order by
和分组查询group by
。临时表対系统性能损耗很大。覆盖索引:就是select的数据列只用从索引中就能够取得,不必从数据表中读取。
条件where:
Using where
:表明使用了WHERE
过滤。缓存:
Using join buffer
:使用了连接缓存。
-
索引应用:
记住一条,最左原则!!!,即带头大哥不能死,中间兄弟不能断
数据准备
CREATE TABLE `staffs`( `id` INT(10) PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` INT(10) NOT NULL DEFAULT 0 COMMENT '年龄', `pos` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位', `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间' )COMMENT '员工记录表'; INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('Ringo', 18, 'manager'); INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('张三', 20, 'dev'); INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('李四', 21, 'dev');
创建索引
CREATE INDEX idx_staffs_name_age_pos ON staffs(name,age,pos);
最左原则-- 用到了idx_staffs_name_age_pos索引中的name字段 SELECT * FROM `staffs` WHERE `name` = 'Ringo'; -- 用到了idx_staffs_name_age_pos索引中的name, age字段 SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18; -- 用到了idx_staffs_name_age_pos索引中的name,age,pos字段 这是属于全值匹配的情况!!! SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager'; -- 索引没用上,ALL全表扫描 SELECT * FROM `staffs` WHERE `age` = 18 AND `pos` = 'manager'; -- 索引没用上,ALL全表扫描 SELECT * FROM `staffs` WHERE `pos` = 'manager'; -- 用到了idx_staffs_name_age_pos索引中的name字段,pos字段索引失效 SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `pos` = 'manager';
索引列上不计算
-- 用到索引,直接使用 字段 = 值的方式来计算 SELECT * FROM `staffs` WHERE `name` = 'Ringo'; -- 没有用到,ALL全表扫描 SELECT * FROM `staffs` WHERE LEFT(`name`, 5) = 'Ringo';
范围之后全失效,不等于可能会失效
-- 用到了idx_staffs_name_age_pos索引中的name,age字段,pos字段索引失效 SELECT * FROM `staffs` WHERE `name` = '张三' AND `age` > 18 AND `pos` = 'dev'; -- 跟MySQL版本有关,有的全表,有的用到的索引范围查询 SELECT * FROM `staffs` WHERE `name` != 'Ringo';
覆盖索引
-- 没有覆盖索引 SELECT `name`, `age`, `pos`, add_time FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager'; -- 使用覆盖索引,查询列是索引的字段 SELECT `name`, `age`, `pos` FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';
like百分加右边
-- 索引失效 全表扫描 SELECT * FROM `staffs` WHERE `name` LIKE '%ing%'; -- 索引失效 全表扫描 SELECT * FROM `staffs` WHERE `name` LIKE '%ing'; -- 使用索引范围查询 SELECT * FROM `staffs` WHERE `name` LIKE 'Rin%'; -- 一定要使用%左边,可以使用覆盖索引
类型转换
-- 使用索引 SELECT `name` ,add_time FROM `staffs` WHERE `name` = 'Ringo'; -- ALL SELECT `name`,add_time FROM `staffs` WHERE `name` = 2000;
-
explain训练
假设index(a,b,c)
Where语句 索引是否被使用 where a = 3 Y,使用到a where a = 3 and b = 5 Y,使用到a,b where a = 3 and b = 5 Y,使用到a,b,c where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 N,没有用到a字段 where a = 3 and c = 5 使用到a,但是没有用到c,因为b断了 where a = 3 and b > 4 and c = 5 使用到a,b,但是没有用到c,因为c在范围之后 where a = 3 and b like ‘kk%’ and c = 4 Y,a,b,c都用到 where a = 3 and b like ‘%kk’ and c = 4 只用到a where a = 3 and b like ‘%kk%’ and c = 4 只用到a where a = 3 and b like ‘k%kk%’ and c = 4 Y,a,b,c都用到 假设index(c1,c2,c3,c4)
CREATE TABLE `test03`( `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT, `c1` CHAR(10), `c2` CHAR(10), `c3` CHAR(10), `c4` CHAR(10), `c5` CHAR(10) ); /* 插入数据 */ INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('a1','a2','a3','a4','a5'); INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('b1','b22','b3','b4','b5'); INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('c1','c2','c3','c4','c5'); INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('d1','d2','d3','d4','d5'); INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('e1','e2','e3','e4','e5'); /* 创建复合索引 */ CREATE INDEX idx_test03_c1234 ON `test03`(`c1`,`c2`,`c3`,`c4`); -- 1.全值匹配 用到索引c1 c2 c3 c4全字段 EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` = 'a3' AND `c4` = 'a4'; -- 2.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序 EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' AND `c3` = 'a3'; -- 3.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序 EXPLAIN SELECT * FROM `test03` WHERE `c4` = 'a4' AND `c3` = 'a3' AND `c2` = 'a2' AND `c1` = 'a1'; -- 4.用到索引c1 c2 c3字段,c4字段失效,范围之后全失效 EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` > 'a3' AND `c4` = 'a4'; -- 5.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序 EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` > 'a4' AND `c3` = 'a3'; -- 6.用到了索引c1 c2 c3三个字段, c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中,c4字段失效 EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' ORDER BY `c3`; -- 7.用到了索引c1 c2 c3三个字段,c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中 EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c3`; -- 8.用到了索引c1 c2两个字段,c4失效,c1和c2两个字段用于查找,c4字段排序产生了Using filesort说明排序没有用到c4字段 EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c4`; -- 9.用到了索引c1 c2 c3三个字段,c1用于查找,c2和c3用于排序 EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c2`, `c3`; -- 10.用到了c1一个字段,c1用于查找,c3和c2两个字段索引失效,产生了Using filesort EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`, `c2`; -- 11.用到了c1 c2 c3三个字段,c1 c2用于查找,c2 c3用于排序 EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY c2, c3; -- 12.用到了c1 c2 c3三个字段,c1 c2用于查找,c2 c3用于排序 EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c2, c3; -- 13.用到了c1 c2 c3三个字段,c1 c2用于查找,c2 c3用于排序 没有产生Using filesort 因为之前c2这个字段已经确定了是'a2'了,这是一个常量,再去ORDER BY c3,c2 这时候c2已经不用排序了!所以没有产生Using filesort 和(10)进行对比学习! EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c3, c2; -- GROUP BY 表面上是叫做分组,但是分组之前必定排序。 -- 14.用到c1 c2 c3三个字段,c1用于查找,c2 c3用于排序,c4失效 EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`,`c3`; -- 15.用到c1这一个字段,c4失效,c2和c3排序失效产生了Using temporary EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`,`c2`;
4、show profile
-
开启慢查询,设置阈值10s等
# 2.1、得到返回记录集最多的10个SQL mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log # 2.2、得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log # 2.3、得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log # 2.4、另外建议使用这些命令时结合|和more使用,否则出现爆屏的情况 mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more
-
Show Profile:MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。默认情况下,参数处于关闭状态,并保存最近15次的运行结果。
SHOW VARIABLES LIKE 'profiling'; SELECT SLEEP(4); SHOW PROFILES; SHOW PROFILE cpu,block io FOR QUERY 522;
-
show processlist,查看线程的执行情况
5、锁
-
MyIsam存储引擎,表锁。
开销小,加锁快,无死锁,锁定粒度大,发生锁冲突的概率最高,并发度最低。
# 对表加读锁 LOCK TABLE `mylock` READ; # 对表加写锁 LOCK TABLE `mylock` WRITE; # 查看数据库表锁的命令 UNLOCK TABLE ; # 查看数据库表锁的分析 SHOW STATUS LIKE 'table%';
2个会话
SESSION1给表加了读锁,那么 SESSION1:可读,不可写,不可读其他,不可写其他。 SESSION2:可读,不可写,可以读其他,可以写其他。 SESSION1给表加了写锁,那么 SESSION1:可读,可写,不可读其他,不可写其他。 SESSION2:不可读,不可写,可以读其他,可以写其他。 这的不可写,可能是严格的意义上代表报错不可写,或者是等待SESSION1释放锁之后才能继续执行。 总结:加锁的,只负责自己的权限,限制的死死地。读共享,写独占。
-
InnoDB存储引擎,行锁。
2个会话,开启MySQL数据库的手动提交
# 关闭自动提交 SET autocommit=0; # 行锁分析 SHOW STATUS LIKE 'innodb_row_lock%';
读几知所写
SESSION1对表的某行先做写操作,但是没有commit。那么 SESSION1能读到最新的数据,能读写。 SESSION2不能读到最新的数据,也不能对该行进行写操作。
索引失效行锁变表锁
# SESSION1 执行SQL语句,没有执行commit。 # 由于`b`字段是字符串,但是没有加单引号导致索引失效 mysql> UPDATE `test_innodb_lock` SET `a` = 888 WHERE `b` = 8000; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 # SESSION2 和SESSION1操作的并不是同一行,但是也被阻塞了??? # 由于SESSION1执行的SQL索引失效,导致行锁升级为表锁。 mysql> UPDATE `test_innodb_lock` SET `b` = '1314' WHERE `a` = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
间隙锁
范围条件,已有数据记录的索引项加锁。比如id有1/3/4/5,那么查询条件是大于0,小于6。此时插入id为2的数据,是插入不成功的。
共享锁、排他锁
共享锁:
自己可读可写,别人可读不可写,可加共享锁。
共享锁,事务都能加,都能读。修改是惟一的,必须等待前一个事务 commit,才可排他锁:
自己可读可写,别人可读不可写,不可加任何锁。
不允许其它事务增加共享或排他锁读取。修改是惟一的,必须等待前一个事务 commit,才可SELECT … LOCK IN SHARE MODE :共享锁(S锁, share locks)。 其他事务可以读取数据,但不能对该数据进行修改,直到所有的共享锁被释放。 如果事务对某行数据加上共享锁之后,可进行读写操作;其他事务可以对该数据加共享锁,但不能加排他锁,且只能读数据,不能修改数据。 SELECT … FOR UPDATE:排他锁(X锁, exclusive locks)。 如果事务对数据加上排他锁之后,则其他事务不能对该数据加任何的锁。获取排他锁的事务既能读取数据,也能修改数据。 在锁定某一行后,其他写操作会被阻塞,直到锁定的行被COMMIT。普通读没事。
5、主从复制
MySQL复制过程分为三步:
- Master将改变记录到二进制日志(Binary Log)。这些记录过程叫做二进制日志事件,
Binary Log Events
; - Slave将Master的
Binary Log Events
拷贝到它的中继日志(Replay Log); - Slave重做中继日志中的事件,将改变应用到自己的数据库中。MySQL复制是异步且串行化的。