Mysql学习笔记（全）

 ✅作者简介：我是18shou，一名即将秋招的java实习生

🔥系列专栏：牛客刷题专栏

📃推荐一款模拟面试、刷题神器👉 [在线刷题面经模拟面试](在线面试刷题)

目录

MyISAM和InnoDB

性能下降SQL慢的原因：

Mysql执行顺序

SQLJoin

索引

索引的优劣

索引分类

mysql索引结构

那些情况建索引

哪些情况不要建索引

性能分析

Extra

索引优化

索引口诀

索引案例

Order By 索引优化

exsites

Mysql慢查询日志命令

函数操作批量插入数据

show Profile分析sql

全局查询日志

Mysql锁

锁命令

表锁：读锁

表锁：写锁

行锁

事务

行锁测试

间隙锁

行锁总结

---

MyISAM和InnoDB

对比	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，操作时即使操作一条记录也会锁住一整张表，不适合高并发的操作	行锁，操作时只锁住某一行，不会影响到其他行，适合高并发
缓存	只缓存索引，不缓存其他数据	缓存索引和真实数据，对内存要求较高，而且内存大小对性能有影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

性能下降SQL慢的原因：

1. 查询语句写的差

2. 索引失效

3. 关联查询太多join （设计缺陷或不得已的需求）

4. 服务器调优及各个参数设置(缓冲，线程参数)

Mysql执行顺序

1. 手写

2. 机读先从from开始

3. 

SQLJoin

a表

 mysql> select * from tbl_dept;
 +----+----------+--------+
 | id | deptName | locAdd |          
 +----+----------+--------+
 |  1 | RD       | 11     |
 |  2 | HR       | 12     |
 |  3 | MK       | 13     |
 |  4 | MIS      | 14     |
 |  5 | FD       | 15     |
 +----+----------+--------+
 5 rows in set (0.00 sec)

b表

 +----+------+--------+
 | id | name | deptId |
 +----+------+--------+
 |  1 | z3   |      1 |
 |  2 | z4   |      1 |
 |  3 | z5   |      1 |
 |  4 | w5   |      2 |
 |  5 | w6   |      2 |
 |  6 | s7   |      3 |
 |  7 | s8   |      4 |
 |  8 | s9   |     51 |
 +----+------+--------+

8 rows in set (0.00 sec)

mysql不支持全连接

使用以下方式可以实现全连接

 mysql> select * from tbl_dept a right join tbl_emp b on a.id=b.deptId
     -> union
     -> select * from tbl_dept a left join tbl_emp b on  a.id=b.deptId;
 +------+----------+--------+------+------+--------+
 | id   | deptName | locAdd | id   | name | deptId |
 +------+----------+--------+------+------+--------+
 |    1 | RD       | 11     |    1 | z3   |      1 |
 |    1 | RD       | 11     |    2 | z4   |      1 |
 |    1 | RD       | 11     |    3 | z5   |      1 |
 |    2 | HR       | 12     |    4 | w5   |      2 |
 |    2 | HR       | 12     |    5 | w6   |      2 |
 |    3 | MK       | 13     |    6 | s7   |      3 |
 |    4 | MIS      | 14     |    7 | s8   |      4 |
 | NULL | NULL     | NULL   |    8 | s9   |     51 |
 |    5 | FD       | 15     | NULL | NULL |   NULL |
 +------+----------+--------+------+------+--------+
 9 rows in set (0.00 sec)

a的独有和b的独有

 mysql> select * from tbl_dept a left join tbl_emp b on  a.id=b.deptId where b.id is null
     -> union
     -> select * from tbl_dept a right join tbl_emp b on  a.id=b.deptId where a.id is null;
 +------+----------+--------+------+------+--------+
 | id   | deptName | locAdd | id   | name | deptId |
 +------+----------+--------+------+------+--------+
 |    5 | FD       | 15     | NULL | NULL |   NULL |
 | NULL | NULL     | NULL   |    8 | s9   |     51 |
 +------+----------+--------+------+------+--------+
 2 rows in set (0.01 sec)

索引

索引的定义：

索引是帮助SQL高效获取数据的数据结构，索引的本质：数据结构

可以简单的理解为：排好序的快速查找数据结构

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式（引用）指向数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引，下图就是一种示例：

一般来说索引也很大，因此索引往往以索引文件的方式存储在磁盘上

我们平常所说的索引，如果没有特别指明，一般都是指B树（多路搜索树，不一定是二叉的）结构组织的索引，

其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引，当然除了B+树这种类型的索引之外，还有哈希索引。

目录

索引的优劣

索引分类

mysql索引结构

那些情况建索引

哪些情况不要建索引

性能分析

---

索引的优劣

1.优势

类似大学图书馆图书编号建索引，提高了数据检索的效率，降低数据库的IO成本

通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗

2.劣势

实际上索引也是一张表，该表保存了主键与存在索引的字段，并指向实体表的记录，所以索引列也是占用空间的

虽然索引大大提高了查询速度，但是会降低更新表的速度，比如 update，insert，delete操作，因为更新表时，MySQL不仅要数据也要保存索引文件每次更新添加了索引的字段，都会调整因为更新所带来的键值变化后的索引信息

索引只是提高效率的一个因素，在一个大数据量的表上，需要建立最为优秀的索引或者写优秀的查询语句，而不是加了索引就能提高效率

索引分类

1. 单值索引

2. 唯一索引

3. 复合索引

4. 基本语法:

   1. 创建

        create [unique] index indexName on mytable(cloumnname(length));

   2.  alter mytable add [unique] index [indexName] on (columnname(length));

   3. 删除

        drop index [indexName] on mytable

   4. 查看

       show index from table_name\G

有四种方式来添加数据表的索引

mysql索引结构

1. BTree索引

2. Hash索引

3. full-text全文索引

4. R-Tree

 

那些情况建索引

1. 主键自动建立唯一索引

2. 频繁作为查询条件的字段应该创建索引

3. 查询中与其他表相关联的字段，外键关系建立索引

4. 频繁更新的字段不适合创建索引，因为每次更新不单单更新了记录还更新了索引

5. where条件里用不到的字段不要创建索引

6. 单键/组合索引的选择问题 who？（高并发下建议组合索引）

7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

8. 查询中统计或分组字段

哪些情况不要建索引

1. 表记录少

2. 经常操作dml语句的表

3. 数据重复且平均分布的表字段，因此只为最经常查询和最经常排序的数据列建立索引，注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

性能分析

explian重点

能干什么

1. 表的读取顺序

2. 数据读取操作的操作类型

3. 哪些索引可以被使用

4. 哪些索引被实际使用

5. 表之间的引用

6. 每张表有多少行被优化器查询

id三种情况

1. id 相同，执行顺序由上至下

2. id不同，如果是子查询，id序号递增，id越大优先级越高

3. id相同不同 ，同时存在

select_type

1. SIMPLE 简单查询

2. PRIMARY 主查询 （最外层的查询）

3. SUBQUERY 子查询

4. DERIUED 某个查询的子查询的临时表

5. UNION 联合查询

6. UNION RESULT 联合查询结果

type：：

type显示的是访问类型排列，是较为重要的一个指标

从最好到最差依次是：

system > const > eq_ref> ref > range > index > ALL;

一般来说，得保证查询至少达到range级别，最好ref

----------------------------------------------type类型-------------------------------------------------------

1. system:表只有一行记录（等于系统表） 这是const类型的特列 一般不会出现，可忽略不计

2. const：表示通过索引一次就查询到了，const用来比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，Mysql就能将该查询转换为一个常量

3. eq_ref：唯一性索引扫描，表中只有一条记录与之匹配，常用于主键或唯一索引扫描（两个表是多对一或者一对一的关系，被连接的表是一的情况下，他的查询是eq_ref）

4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回匹配某个单独值的所有行，然而他可能会找到多个复合条件的行，属于查找和扫描的结合体

5. range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般where语句里出现了betweent，<,>,in等的查询，这种范围扫描索引比全表扫描好

6. index：index与ALL的区别，index只遍历索引树，索引文件通常比数据文件小

7. ALL：全表扫描

----------------------------------------------type类型-------------------------------------------------------

possible_keys：显示可能应用的的索引（理论上）

key：实际使用的索引，查询中若使用了覆盖索引，则该索引仅仅出现在key中

key_len：表示索引中使用的字节数，在不损失精度的情况下越短越好，kenlen显示的值为索引字段的最大可能长度，并非实际使用长度，kenlen是根据表定义计算而得，而不是通过表内检索出的

key_len长度：13是因为char(4)*utf8(3)+允许为null(1)=13

ref：显示索引的哪一列被使用了，如果可能的话是一个常数，哪些列或常量被用于查找索引列上的值

rows：根据表统计信息及索引选用情况，大致计算出找到所需的记录所需要读取的行数

没建立索引时查询t1 t2表 t1表对应t2表的id t2表 col1的值要为'ac'

对于Id这个字段t1表对t2表相当于 一对多

t1表的type为 eq_ref代表唯一性索引扫描，表中只有一条记录与之匹配，t2表对应t1的这个id对应的col值只有一个，根据t2表的主键id索引查询，t1表读取了一行，t2表读取了640行

建立索引后

t1读取一行，t2读取142行，ref非唯一性索引扫描，返回匹配某个单独值的所有行，返回t2对应id的col所有行，而t1对应id的col只有一行，所以type为eq_ref

Extra

包含不适合在其他列展现但十分重要的信息

\G ：竖直显示排序

1. Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成排序的操作称为文件排序未被方框框住的图建立了复合索引，但是直接使用col3进行排序导致空中楼阁，mysql不得已只能进行filesoft

2. 

    

3. Using temporary：使用了临时表保存中间中间结果，MySQL在对查询结果排序时使用临时表。常见于order by排序和group by分组上表中建立了复合索引 col1_col2 但是直接通过col2进行分组导致了mysql不得已只能进行filesoft和建立临时表

4. using index 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，如果同时出现using where 表示索引被用来执行索引键值的查找，没有usingwhere表示索引用来读取数据而非执行查找动作

5. using where 表示使用了 where过滤

6. using join buffer 私用了链接缓存

7. impossible buffer where子句的值总是false 不能用来获取任何元组

8. select tables optimized away 在没有group by子句的情况下，基于索引优化min/max操作，或者对myisam存储引擎执行count(*)操作，不必等到执行操作进行，查询执行计划生成的阶段即完成优化

9. distinct 优化distinct操作，在找到第一匹配的元组后立即停止查找同样值的操作

案例

索引优化

单表优化

 CREATE TABLE IF NOT EXISTS `article`(
 ​
 `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 `author_id` INT (10) UNSIGNED NOT NULL,
 `category_id` INT(10) UNSIGNED NOT NULL , 
 `views` INT(10) UNSIGNED NOT NULL , 
 `comments` INT(10) UNSIGNED NOT NULL,
 `title` VARBINARY(255) NOT NULL,
 `content` TEXT NOT NULL
 );
 INSERT INTO `article`(`author_id`,`category_id` ,`vi