Mysql高级笔记

最新推荐文章于 2024-11-16 18:02:50 发布

原创最新推荐文章于 2024-11-16 18:02:50 发布 · 201 阅读

0 ·

CC 4.0 BY-SA版权

MYSQL 专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍了MySQL的安装、字符集配置、权限管理以及逻辑架构。重点讲解了索引的类型、优缺点、使用规范及优化技巧，包括B+Tree索引、覆盖索引和避免全表扫描。还探讨了SQL查询流程、连接查询、索引在连接优化中的应用，并提供了索引面试题分析和慢查询日志的管理和分析。同时，分享了存储过程的使用和SQL优化策略，如小表驱动大表原则和排序优化。

参考尚硅谷msyql高级课程的笔记，仅供学习交流使用

1、简介

在这里插入图片描述

安装mysql liunx版
在这里插入图片描述

mysql服务
在这里插入图片描述

mysql字符集
在这里插入图片描述

设置大小写不敏感

mysql权限管理
在这里插入图片描述

第三章msyql逻辑架构简介

在这里插入图片描述

1、连接层
在这里插入图片描述

2、服务层
在这里插入图片描述
3、引擎层

4、存储层
在这里插入图片描述

MyiSAM和InnoDB
在这里插入图片描述

AliSql和AliRedis

查看sql执行周期
在这里插入图片描述

sql查询流程
在这里插入图片描述

sql执行顺序
在这里插入图片描述

在这里插入图片描述

常见join查询（其中）
在这里插入图片描述

七种连接join实例，第六个是全连接 union 是并集去重，第七个是a和b的独有并集
在这里插入图片描述

索引优化
索引简介
索引就是排好序的快速查找数据结构
在这里插入图片描述
索引优缺点

在这里插入图片描述

索引分类
单值索引
在这里插入图片描述

唯一索引
在这里插入图片描述

主键索引
在这里插入图片描述

复合索引
索引使用基本语法

在这里插入图片描述

索引结构

1、btree索引（重点）
2、hash索引
3、full-text全文索引
3、R-tree索引

btree索引
在这里插入图片描述

B+tree索引
在这里插入图片描述
b树和b+树区别

在这里插入图片描述

聚簇索引和非聚簇索引（聚集索引 innodb有）

在这里插入图片描述

索引使用规范
在这里插入图片描述

ps:where 条件中用不到的字段不创建索引
数据重复且分布平均的表字段，也不应该建立索引
索引效率，索引字段不重复的值除以表的总记录数
这个数值越接近于1表示效率越高，如果接近与50%，这种低效率索引不会提高查询速度。

性能分析Explain性能分析
在这里插入图片描述
返回的信息

表的读取顺序（id）
数据读取操作的操作类型（select_type）
那些索引可以使用
那些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

id
在这里插入图片描述

在这里插入图片描述

select_type

在这里插入图片描述

type查询的访问类型关键字段
一般而言，大数据量的查询，级别应该达到range级别以上，
最好到ref
system>const>eq_ref>ref>range>index>ALL
在这里插入图片描述

在这里插入图片描述

const和eq_ref的区别

range

possible_keys

key可以判断索引是否失效
在这里插入图片描述

查询中如果使用了覆盖索引，该索引仅出现在key列表中，而不在possible_key中

mysql覆盖索引与回表

在这里插入图片描述

key_len
在这里插入图片描述

explain之ref
被用于与索引比较查找结果的值，如果比较没有用到索引，为null

extra
在这里插入图片描述

出现using filesort
在这里插入图片描述

解决方法：通过索引字段去排序

using temporary
在这里插入图片描述

优化前
在这里插入图片描述

优化后
在这里插入图片描述

**using index **
在这里插入图片描述

优化调大连接缓存
在这里插入图片描述

在这里插入图片描述

存储过程

在这里插入图片描述

优化小案例

一、单表优化案例

在这里插入图片描述

在这里插入图片描述
优化方案一
三字段复合索引
创建索引 index idx_article_ccv on article(cattegory_id,commnents,views)

在这里插入图片描述
方案一索引部分使用，有部分索引失效，因为范围后的索引会失效

优化方案二、
create index idx_article_cv on article(category_id,views);

在这里插入图片描述

多表索引优化案例
左连接（右连接原理相同）
在这里插入图片描述

优化前
在这里插入图片描述
在哪个表上建立card字段索引？

在book表，右表上建立索引
在这里插入图片描述
在左表建立索引

原理：因为左连接需要加载左表全部数据，建立索引也需要访问所有数据
因此索引建立在右表，有时候我们需要根据已经建立的索引来决定使用左连接还是右连接。

三表索引优化案例
在这里插入图片描述
其它两个表，用上面的book（bookid card ）class(classid card)

优化思路同双表，在book和phone的card建立索引

索引优化

建表语句
在这里插入图片描述

口诀
在这里插入图片描述

1、全值匹配我最爱（覆盖索引）查询字段中被索引字段覆盖
在这里插入图片描述
最佳左前缀法则

在这里插入图片描述

4索引列上不能有范围查询
在这里插入图片描述

在这里插入图片描述

前缀不能出现模糊匹配
在这里插入图片描述
解决模糊查询两边加百分号导致所以失效的问题

创建索引
在这里插入图片描述
创建覆盖索引后查询

在这里插入图片描述

索引面试题分析
在这里插入图片描述

在这里插入图片描述

在这里插入图片描述
上面使用了覆盖索引

为什么like不会像范围查询那样使索引失效

在这里插入图片描述

sql优化流程
在这里插入图片描述

in和exist的区别

**小表驱动大表原则，让小数据表先执行查询 **
在这里插入图片描述

排序优化

在这里插入图片描述

在这里插入图片描述
4.1版本后才有单路排序
单路排序和双路排序的详解

在这里插入图片描述

使用索引排序的情况和不使用索引排序（文件排序）的情况
在这里插入图片描述

慢查询日志

在这里插入图片描述

查看是否开启慢查询日志和开启
在这里插入图片描述

开启慢查询日志（一次性，作用范围是当前数据库）
在这里插入图片描述
永久开启慢查询日志

查看和设置慢查询阈值
修改阈值后需要重新连接或者新开会话才可以看到修改生效
在这里插入图片描述
设置开启慢查询日志后，可以通过查看指定路径下的路径查看运行过慢的sql语句

mysql日志分析工具 msyqldumpslow
在这里插入图片描述

存储过程（批量插入数据）

在这里插入图片描述

设置函数参数

创建函数
在这里插入图片描述

在这里插入图片描述
函数必须有返回值
返回一个长度为n的随机字符串

随机产生部门编号
在这里插入图片描述
存储过程可以没有返回值，并且要把自动提交关闭，一次性提交所有数据

emp表的存储过程
在这里插入图片描述

dept表的存储过程
在这里插入图片描述

调用存储过程
call + 存储过程名（参数）

在这里插入图片描述

利用show profile进行sql分析
在这里插入图片描述