MySQL知识笔记——一看就会

MySQL

下面记录了MYSQL学习过程中的一些理论知识笔记。


1、基础
  1. MySQL的逻辑架构
  • 连接层:Connection Pool:管理用户连接,线程请求。

  • 服务层:

    • SQL Interface:交互层。接受命令和返回结果。
    • Parser:SQL语句解析器。
    • Optimizer:查询优化器。比如联合索引,等于常量时,顺序可变也一样。
    • Caches & Buffers:查询缓存。
    • Management Serveices & Utilities:管理层。备份、安全、复制、集群等等。。
  • 引擎层:Pluggable Storage Engines:存储引擎,InnoDB、MyISAM等。

  • 存储层:File System:数据落地到磁盘上,就是文件的存储。

  1. 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
    
  1. SQL性能下降和优化

    原因:没使用索引,索引失效,sql差(多表连接、产生了临时表),内存不足,cpu性能不高等。

    解决:开启慢查询,使用explain分析,使用Show Profile分析查询的生命周期,参数调优。

  2. 执行顺序

    • 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
  3. join语法

  1. 语法分页记录主键id,可以大于该id,然后使用limit分页

  2. UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中

2、索引
  1. 索引一种数据结构,包括b-数和b+树,hash等。有一个索引文件生成,存在硬盘。

  2. 索引优点,用于快速查找(where)和排序(order by)。缺点,索引也占空间,写操作过多也会改索引文件。

    额外的文件保存特殊的数据结构。

    查询快,但是更新、删除、新增也会操作索引文件,所以也会慢点。

    某字段查询的频繁可以创建索引,但是过多也不行,因为索引有文件,也会更新。

  3. 索引分类:

    • 普通单值索引:一个索引只包含单个列,一个表可以有多个单列索引。

    • 唯一索引:索引列的值必须唯一,但是允许空值。主键 ,不允许。

    • 复合索引:一个索引包含多个字段。联合普通索引,联合唯一索引,联合主键索引

      建议:一张表建的索引最好不要超过5个!

  4. 索引SQL

    CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length));
    DROP INDEX [indexName] ON tabName;
    SHOW INDEX FROM tabName
    -- 还可以使用ALTER改表的方式
    
  5. 适合建立索引的情况

    	适合
       	经常作为查询和排序(分组)条件的字段。
       	组合索引优于单值索引
       	
        不适合
       	频繁更新的字段
       	数值变化少比如性别
       	数据量少
    
  6. 索引失效的情况

    记住一条,最左原则!!!

    记住一条,最左原则!!!

    记住一条,最左原则!!!

    使用计算,类型转化,范围,like,等于不等于,排序等操作都有可能导致索引失效,具体情况具体分析。
    最好的用法:
    全值匹配;最左原则;覆盖索引;范围之后索引失效。extra内容等
    
    
    具体如下:
    - 全值匹配我最爱。
    - 最佳左前缀法则。
    - 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。
    - 索引中范围条件右边的字段会全部失效。
    - 尽量使用覆盖索引(只访问索引的查询,索引列和查询列一致),减少`SELECT *`。
    - MySQL在使用`!=`或者`<>`的时候无法使用索引会导致全表扫描。
    - `is null`、`is not null`也无法使用索引。
    - `like`以通配符开头`%abc`索引失效会变成全表扫描。
    - 字符串不加单引号索引失效。
    - 少用`or`,用它来连接时会索引失效。
    

3、explain
  1. 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:使用了连接缓存。

    在这里插入图片描述
    在这里插入图片描述

  1. 索引应用:

    记住一条,最左原则!!!,即带头大哥不能死,中间兄弟不能断

    数据准备

    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;
    
  2. explain训练

    假设index(a,b,c)

    Where语句索引是否被使用
    where a = 3Y,使用到a
    where a = 3 and b = 5Y,使用到a,b
    where a = 3 and b = 5Y,使用到a,b,c
    where b = 3 或者 where b = 3 and c = 4 或者 where c = 4N,没有用到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 = 4Y,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 = 4Y,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
  1. 开启慢查询,设置阈值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
    
  2. Show Profile:MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。默认情况下,参数处于关闭状态,并保存最近15次的运行结果。

    SHOW VARIABLES LIKE 'profiling';
    SELECT SLEEP(4);
    SHOW PROFILES;
    SHOW PROFILE cpu,block io FOR QUERY 522;
    

    在这里插入图片描述
    在这里插入图片描述

  3. show processlist,查看线程的执行情况

5、锁
  1. MyIsam存储引擎,表锁。

    开销小,加锁快,无死锁,锁定粒度大,发生锁冲突的概率最高,并发度最低。

    # 对表加读锁
    LOCK TABLE `mylock` READ;
    # 对表加写锁
    LOCK TABLE `mylock` WRITE;
    # 查看数据库表锁的命令
    UNLOCK TABLE ;
    # 查看数据库表锁的分析
    SHOW STATUS LIKE 'table%';
    

    2个会话

    SESSION1给表加了读锁,那么
    SESSION1:可读,不可写,不可读其他,不可写其他。
    SESSION2:可读,不可写,可以读其他,可以写其他。
    
    SESSION1给表加了写锁,那么
    SESSION1:可读,可写,不可读其他,不可写其他。
    SESSION2:不可读,不可写,可以读其他,可以写其他。
    
    这的不可写,可能是严格的意义上代表报错不可写,或者是等待SESSION1释放锁之后才能继续执行。
    
    总结:加锁的,只负责自己的权限,限制的死死地。读共享,写独占。
    
  2. 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复制是异步且串行化的。
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值