Mysql-学习笔记汇总_drzeqz

一直认为 count(1) 比 count(*) 效果高，被同事鄙视了。：https://mp.weixin.qq.com/s/52KKVvfyJ6yMPoOFjNPa6g

SQL判断是否"存在"，还在用 count 操作？很耗时的！：https://mp.weixin.qq.com/s/z2eWwtVsikh6poMYXeJS0Q

 

Mysql优化提高笔记整理，来自于一位鹅厂大佬的笔记

B树、B-树、B+树、B*树图文详解

万字长文，最硬核的mysql知识总结

为了把mysql的索引底层原理讲清楚，我把计算机翻了个底朝天

面试官：听说你sql写的挺溜的，你说一说查询sql的执行过程

当执行一条查询的SQl的时候大概发生了一下的步骤：

• 客户端发送查询语句给服务器。
• 服务器首先检查缓存中是否存在该查询，若存在，返回缓存中存在的结果。若是不存在就进行下一步。
• 服务器进行SQl的解析、语法检测和预处理，再由优化器生成对应的执行计划。
• Mysql的执行器根据优化器生成的执行计划执行，调用存储引擎的接口进行查询。
• 服务器将查询的结果返回客户端。

执行连接器：用户权限校验
执行检索内存
执行分析器：词法分析和语法分析
执行优化器
执行执行器

面试官：你知道select语句和update语句分别是怎么执行的吗？：https://mp.weixin.qq.com/s/0FILLtmKUtaeYDmZcmqmxg

 

 

大厂面试官必问的Mysql锁机制

我以为我对Mysql事务很熟，直到我遇到了阿里面试官。

 

我以为我对 MySQL 索引很了解，直到我遇到了阿里的面试官：https://mp.weixin.qq.com/s/zoETascO4CG_YgHUF983lQ

 

深入mysql的innodb存储引擎之文件结构

 

助你进大厂，这些MySQL索引底层知识你是必须知道的：https://mp.weixin.qq.com/s/DRDSz8A1MGqcluzzHJCrPA

10张图，搞懂索引为什么会失效？：https://mp.weixin.qq.com/s/EQypRCK31XA7mb0eq3dFqA

 

 

SQL 语法速成手册：https://mp.weixin.qq.com/s/V5L4npvnX10BW0VuZSw9HA

SQL 支持三种注释

## 注释1
-- 注释2
/* 注释3 */

DDL 的核心指令是 CREATE、ALTER、DROP。
DML 的核心指令是 INSERT、UPDATE、DELETE、SELECT。这四个指令合称 CRUD(Create, Read, Update, Delete)，即增删改查。
TCL 的核心指令是 COMMIT、ROLLBACK。
DCL 的核心指令是 GRANT、REVOKE。
    DCL 以控制用户的访问权限为主，可利用 DCL 控制的权限有：CONNECT、SELECT、INSERT、UPDATE、DELETE、EXECUTE、USAGE、REFERENCES。

DELETE
TRUNCATE TABLE 可以清空表

事务处理
MySQL 默认是隐式提交
通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。

• START TRANSACTION - 指令用于标记事务的起始点。
• SAVEPOINT - 指令用于创建保留点。
• ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 START TRANSACTION 语句处。
• COMMIT - 提交事务。

GRANT 和 REVOKE 可在几个层次上控制访问权限：

• 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
• 整个数据库，使用 ON database.*；
• 特定的表，使用 ON database.table；
• 特定的列；
• 特定的存储过程。
• 新创建的账户没有任何权限。

账户用 username@host 的形式定义，username@% 使用的是默认主机名。
MySQL 的账户信息保存在 mysql 这个数据库中。
USE mysql;
SELECT user FROM user;

• 创建账户：CREATE USER myuser IDENTIFIED BY 'mypassword';
• 修改账户名：UPDATE user SET user='newuser' WHERE user='myuser';
•                       FLUSH PRIVILEGES;
• 删除账户：DROP USER myuser;
• 查看权限：SHOW GRANTS FOR myuser;
• 授予权限：GRANT SELECT, INSERT ON *.* TO myuser;
• 删除权限：REVOKE SELECT, INSERT ON *.* FROM myuser;
• 更改密码：SET PASSWORD FOR myuser = 'mypass';

 

15000 字的 SQL 语句大全：https://mp.weixin.qq.com/s/kbu5AHXpbLHiUYFw0uMrTA

 

 

explain都不会用，你还好意思说精通Mysql查询优化？：https://mp.weixin.qq.com/s/sYBTars65LGLDB-RYJWOPg

1、id表示执行select查询语句的序号，它是sql执行的顺序的标识，sql按照id从大到小执行，id相同的为一组，从上到下执行。
2、select_type表示查询的类型，也就是对应的是简单查询还是复杂查询，若是复杂查询又包含：「简单的子查询、from子句的子查询、union查询」。

• simple：表示简单查询，不含有任何的复杂查询。
• PRIMARY：复杂查询中「最外层的select语句的查询类型就是PRIMARY」
• SUBQUERY：在「select或者where中包含的子查询」会被表示为SUBQUERY类型。    DERIVED：「DERIVED表示的是派生表或者衍生表的意思，在from包含的子查询中会被表示为DERIVED类型」，Mysql会递归执行这些子查询，并且把结果放在临时表中。在Mysql 5.7以上的版本中对其做了优化，新增了derived_merge(派生合并)，可以加快查询效率。
• UNION：在出现「UNION查询语句中，第二个select的查询语句就会被表示为UNION」
• UNION RESULT：「UNION查询语句的结果被标记为UNION RESULT」
• 其他：
• DEPENDENT UNION：也表示UNION查询语句中第二个或者后面的语句，但是取决于外面的查询。
• DEPENDENT SUBQUERY：子查询中的第一个select语句，也是依赖于外部的查询。
• UNCACHEABLE SUBQUERY：子查询的结果不能被缓存，必须重新评估外连接的第一行。

3、table：表示的是查询的是哪个表，一个是已经存在的表也可以表示衍生表
4、type字段：表示的sql关联的类型或者说是访问的类型
    确定这条sql查找数据库表的时候，查找记录的大概范围是怎么样的，直接就能体现sql的效率问题
    system > const > eq_ref > ref > range > index > ALL
    system：是const的特例，「表示表中只有一行记录」，这个几乎不会出现
    const：表示通过索引一次就查找到了数据，一般const出现在「唯一索引或者主键索引中使用等值查询」，因为表中只有一条数据匹配，所以查找的速度很快。
    eq_ref：表示使用唯一索引或者主键索引扫描作为表链接匹配条件，对于每一个索引键，表中只有一条记录与之匹配    ref：ref性能比eq_ref差，也表示表的链接匹配条件，也就是使用哪些表字段作为查询索引列上的值，ref与eq_ref的区别就是eq_ref使用的是唯一索引或者主键索引。ref扫描后的结果可能会找到多条符合条件的行数据，本质上是一种索引访问，返回匹配的行。
    range：range使用索引来检索给定范围的行数据，一般是在where后面使用between、<>、in等查询语句就会出现range」    ALL：「ALL与index的区别就是ALL是从硬盘中读取，而index是从索引文件中读取」，ALL全表扫描意味着Mysql会从表的头到尾进行扫描，这时候表示通常需要增加索引来进行优化了，或者说是查询中并没有使用索引作为条件进行查询
5、possible_keys字段：possible_keys表示这一列查询语句可能使用到的索引，仅仅只是可能，列出来的索引并不一定真正的使用到。
    当没有使用索引为NULL时，说明需要增加索引来优化查询了，若是表的数据比较少的话，数据库觉得全表扫描更快，也可能为NULL。
6、key字段：key字段与possible_keys的区别就是，表示的真正使用到的索引，即possible_keys中包含key的值。
    若是想Mysql使用或者忽视possible_keys中的索引，可以使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
7、key_len字段：表示sql查询语句中索引使用到的字节数，这个字节数并不是实际的长度，而是通过计算查询中使用到的索引中的长度得出来的，显示的是索引字段最大的可能长度。
    一般来说在不损失精度的前提下，key_len是越小越好，比如上面的测试表的id为int类型，int类型由4个字节组成
8、ref字段：ref表示列与索引的比较，表连接的匹配条件ÿ