MySQL技术高级篇-B站尚硅谷

B站尚硅谷MySQL技术高级篇

之前看了阳哥的Redis视频,感觉阳哥讲的挺好的

文章记录在gitee上

https://gitee.com/Freegxx/note/blob/master/MySQL/MySQL%E5%AD%A6%E4%B9%A0.md

我发现使用Git加Typora是真的爽,就是Typora文章有改动时Git拉取数据,本地文件会丢失还找不回来,丢失了好几篇文章了,气到炸,可能是我Git用的菜吧

学习地址

https://www.bilibili.com/video/BV1EC4y1s7a7

1.MySQL架构介绍

MYsql安装

查看mysql是否已经安装

rpm -qa|grep mysql

ps -ef |grep mysql

mysqladmin -version

mysql安装完成后会有密码与用户组记录

cat /etc/passwd|grep mysql

cat /etc/group|grep mysql

Mysql启动与暂停,开机启动

service mysql start

systemctl start mysql

service mysql stop//-- /etc/init.d/mysql 启停相关脚本

systemctl enable mysql

修改Mysql密码

/usr/bin/mysqladmin -u root password 123456 --改为了123456

修改mysql编码格式

查看mysql编码格式

show variables like ‘%char%’

Mysql配置文件

二进制日志log-bin

错误日志log-error

查询日志–默认关闭的–慢查询日志等

数据文件:

• frm文件–存放表结构
• myd文件–存放表数据
• myi文件–存放表索引

Mysql逻辑架构介绍

总体概览

1. 连接层:客户端与服务连接,包含本地socket通信和大多数基于客户端服务端工具实现于tcp/ip的通信,主要完成类似于连接处理,授权认证以及线管的安全方案,在改成引入了线程池的概念
2. 服务层:主要完成核心服务功能,如SQL接口,完成缓存的查询,SQL分析优化以及部分内置函数
3. 引擎层:真正负责数据的存储和提取,不同存储引擎的功能不同
4. 存储层:主要是将数据存储在裸设备的文件系统之上,完成与存储引擎的交互

查询说明

MYSQL存储引擎

查看所有引擎命令

show engines

查看当前引擎命令

show variables like ‘%engines%’

MyISAM与InnoDB

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	支持标所	行锁,操作时只锁某一行,适合高并发
缓存	仅缓存索引,不缓存真实数据	缓存索引与真实数据,对内存要求高
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

2.MySQL索引分析

性能下降SQL慢,执行时间长,等待时间长

• 查询语句写的烂
• 索引失效
• 关联查询太多join
• 服务器调优以及各个参数设置(缓冲,线程数)

create index idx_user_name on user(name)

索引名:idx_user_name

表名:user

列名:name

常见的Join查询

SQL执行顺序

SQL JOINS

• 

• 

• 

• 

• 

• 

上面的几张图中,MySQL不支持Full 语法

union合并加去重,union all合并不去重(这里总是记反)

索引简介

索引的定义

Mysql官方对索引的定义是:索引是帮助MySQL高效获取数据的数据结构

所以索引的本质是:数据结构

排好序的快速查找数据结构

为什么要建立索引?

索引的目的在于提高查询效率,类似于字典查询

在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构一模中方式引用指向数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构,就是索引–>B树索引

平常说的索引,没有特别指明,都是B树(多路搜索树,并不一定是二叉树)

其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是B+树索引,统称索引,当然除了B+树索引,还有哈希索引

索引的优势

类似图书馆建立书目 索引,提高检索效率,降低数据库IO成本

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

MySQL索引分类

• 单值索引
• 唯一索引
• 复合索引

MySQL索引结构

主要了解BTree

哪些情况需要建立索引

主键自动建立唯一索引

频繁作为条件的字段应该作为索引

频繁与其他表关联的字段，外键建立索引

哪些情况不要建立索引

表记录比较少，与经常增删该的表不要创建索引

频繁更新的字段不适合创建索引

where后面用不到的字段不用创建索引

列重复数据比较多的字段不用创建索引

索引的选择性是指索引列中不同值的数目与表中记录数的比，一个索引的选择项越接近于一，这个索引的效率就越高

性能分析

Mysql Query optimizer

Mysql中有专门负责优化Select语句的优化器模块

Mysql常见瓶颈

Explain

Explain + SQL语句

explain select * from table

能干吗?

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

执行计划包含的信息

• id(重要) : id相同,执行顺序从上往下,id越大越先执行

• select_type :

  ​ Simple:简单的select查询,查询中不包含子查询或者UNION

  ​ Primary:查询中若包含任何复杂的子查询,最外层查询则被标记为Primary

  ​ SubQuery:在select或where列表中包含的子查询

  ​ derived:衍生,在from列表中包含的子查询被标记为Derived衍生,MySQL会递归查询这些子查询,将结果放到临时表

  ​ union: union查询后面的结果

  ​ union result:所有的结果在一起

• table :这一行数据是关于哪一张表的
• type (重要):常用的8种: all,index,range,ref,eq_ref,const,system,null
  • 访问类型排列
  • 最好到最差 system>const>eq_ref>ref>range>index>all
  • system表只有一行记录,平常不会出现,这个可以忽略不计
  • const:通过索引一次(不需要回表)就找到了,其用于比较primary key或者unique索引,因为只有一条,所以很快
  • eq_ref:唯一性索引扫描(需要回表),表中只有一条记录与之匹配,常见于逐渐或者唯一索引
  • ref:非唯一性索引扫描,返回匹配某个单列值的所有行
  • range:只检索给定范围的行,between,in ><等
  • index:查询所有索引,值遍历索引树,因为索引文件比数据文件小
  • all 全表扫描
• possible_keys(重要):显示可能应用的索引,一个或多个,索引被列出,但是不一定被使用
• key(重要):实际使用的索引,如果为null,则没有使用索引
  • 查询中如果使用了覆盖索引,则索引出现在key中
• key_len:表示索引中使用的字节数,不损失精度的情况下长度越短越好
• ref:显示索引的那一列被使用了
• rows:每张表有多少行被优化器查询,即每张表查询出来了多少行数据
• extra(重要):

  • Using filesort:mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取

    mysql中无法利用索引完成的排序操作称为"文件排序"

  • Using temporary:产生了新建的内部临时表,常见于order by和分组group by
  • Using Index:相应的select中使用了覆盖索引,避免的访问表的数据行(回表),效率不错
  • 覆盖索引:select s1 from t where s1 = “”,查询的条件正好是索引s1对应的列(不需要回表)
  • 等等

索引分析

组合索引中,如果其中一个是条件范围,则会导致索引失效

例如组合索引是三个条件(c1,c2,c3),c2如果是范围判断则c3的索引会失效

两张表左右连接时添加索引:

左连接索引加到右表中,右连接索引加到左表中,相反加

JOIN语句的优化

尽可能减少Join语句中的NestedLoop的循环总次数;“永远用小结果集驱动大结果集”,小表驱动大表

优先优化NestedLoop(嵌套循环)的内层循环

保证Join语句中被驱动表上Join条件字段已经被索引

当无法保证被驱动表的Join条件字段被索引,且内层充足时,无需吝啬JoinBuffer

索引失效(怎样避免)

MySQL基本命令

使用哪个数据库

use database1

创建索引

单索引create index idx_table_id on table(id)

组合索引create index idx_table_id on table(id,name)

alter table “table” add index idx_table_id(id,name)

查看索引

show index from table

删除索引

drop index idx_table_id on table

案例索引失效

最好全值匹配

组合索引最好几个条件都写上,最左侧不可少,中间不可断

最佳左前缀法则

组合索引最左侧列不可缺少

不在索引列上做任何操作(计算,函数,类型转换(自动手动))

将运算放在逻辑运算符右边

例:where age > 3+1

范围后面全失效

where后面多个条件,中间一个是范围(例:>),则后面的查询都不会走索引

尽量使用覆盖索引

即指不需要回表,只从MYI文件中就能得到值

mysql在使用不等于时会全表扫描

is null ,is not null也无法使用索引

like以通配符开头(’%abc’)也会导致索引失效

解决like索引失效的问题，使用覆盖索引，

如果查询的项是*或者查询的项比（覆盖的与id）的要多，依然会失效

字符串不加单引号索引失效

自动或手动类型转换

少用or,用它来连接会导致索引失效

MySql优化器

视频P44

当查询时,MySQL会自动调整查询条件的顺序

MySQL定值，范围还是排序，一般order by是给个范围

排序没有用到索引，都会有using filesort

group by 基本上都需要进行排序，会有临时表产生

范围后面全失效，如果是like ‘aa%’,组合索引依然会用到,所以该并不是范围后面全失效

P45结束

SQL优化:

1.explain

1. 观察,至少跑1天,看看生产的慢SQL情况
2. 开启慢查询日志,设置阈值,比如超过5秒钟的就是慢SQL,将其抓取出来
3. explain + 慢SQL 分析
4. show profile
5. 最后是DBA进行SQL数据库服务器的参数调优

==总结

1. 慢查询的开启并捕获
2. explain+慢SQL分析
3. show profile查询SQL在mysql服务器的执行细节和声明周期情况
4. SQL数据库服务器参数的调优

3.MySQL查询截取分析

小表驱动大表 例子IN与Exist

下面两种循环,上面只需要与数据建立5次连接,下面则需要建立1000连接

具体SQL例子:

Order BY 关键字优化

order by 子句,尽量使用index方式排序,避免使用filesort方式排序

尽可能在索引列上完成排序操作,遵循索引建的最佳左前缀原则

如果不在索引列上,filesort有两种算法:mysql就要启动双路排序和单路排序

双路排序:MySQL4.1之前使用(已经过时),意思是两次扫描磁盘,先进行一次排列,在将拍好序的数据返回

单路排序:一次扫描磁盘,在buffer中进行排序(在内存中处理了)

单路总体好过双路,但是其有问题,如果一次性不能抓取完所有数据,则可能会比双路慢

MySQL配置文件中可以配置sort_buffer的大小,如果单路取出的需要的数据比sort_buffer的容量大,则会导致多次I/O,会更慢

优化策略

增大sort_buffer_size参数的设置

增大max_length_for_sort_data

提高order by的速度

1. order by的select *是一个大忌,只查询需要的字段

   1.1 当query的字段大小小于max_length_for_sort_data而且排序字段不是text\blob时候,用单路,否则用多路排序

   1.2 两种散发的数据都有可能超出sort_buffer的容量,会创建临时文件进行合并,会有多次IO,但是用单路排序风险会大一些

2. 尝试提高sort_buffer_size
3. 尝试提高max_length_for_sort_data

小总结

mysql两种排序方式:文件排序或扫描有序索引排序

mysql能为排序与查询使用相同的索引

order by能使用索引最左前缀

如果where使用索引的最左前缀定义为常量,则order by能使用索引

不能使用索引排序

Group BY基本类似order by

只有一点不一样:where 高于having,能使用where的就不要使用having来限定

慢查询日志

慢查询

用来记录SQL响应时间超过阈值的语句,具体指时间超过long_query_time的SQL

默认情况下,mysql并没有开启这个功能

如何查看和开启

开启只对当前数据库生效

如果要永久生效,需要改变配置文件my.cnf

日志分析工具

mysqldumpslow --help

批量数据脚本

定义返回随机字符串的方法

delimeter–用于定义结束符号,原先的是分号(😉

创建存储过程

执行存储过程,call 调用

show profile

是什么

是Mysql提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量

默认情况下:参数处于关闭状态,并保存最近15次的运行结果

命令:

show variables like ‘profiling’

查看结果:show profiles

show profiles cpu,block io for query 3(这个三是上面截图的query_id)

全局查询日志

仅可以在测试环境测试

配置启用

在MySQL的my.cnf中，设置如下：

#开启

general_log=1

#记录日志文件的路径

general_log_file = /path/logfile

#输出格式

log_output=FILE

命令:

set global general_log =1

set global log_output=‘table’

4.MySQL锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制

锁分类

粒度来分:表锁,行锁,页锁

操作类型来分:读写锁

读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响

写锁(排它锁):当前写操作没有完成前,它会阻断其他写锁和读锁

三锁

• 表锁(偏读)
• 行锁(偏写)
• 页锁

表锁:

偏向MYISAM存储引擎,开销小,加锁快;无死锁;锁定粒度大.锁冲突高,并发度小

手动增加表锁

lock table 表名字 read(write),表名字2 read(write),其他;

查看加锁的表

show open tables

释放表锁

unlock tables;

读锁:

Thread1对表table1加了读锁,多个线程都可读,但是都不可写,被加锁的表table1不可写,未被加锁的表(其他的表)也不可写,其他线程不可以对table1写操作(操作要等待,Thread1解锁之后才能执行)

写锁:

当前线程可读可写表,不能读其他表,其他线程不能读写被加锁的表(会阻塞)

简而言之:就是读锁阻塞写,但是不会阻塞读,而写锁则会把读和写都阻塞

行锁

加锁

CAP

强一致性C

高可用A

分区容错性P

间隙锁

行锁总结

分析行锁

5.主从复制

就是一些配置

最好MySQL要版本一致

主从都配置在 mysqlid 节点下

主机修改my.ini文件

从机需要修改的东西

到这里基本所有视频都看完了

大部分都只是看了,并没有去实践,所以之后要多实践

另外这里的主从配置只是简单截图,之后要多搜一下其他博客再看一下具体操作步骤以及相关的问题解决

ok,就到这里了