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复制是异步且串行化的。
2166
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
