首页  思维导图  详情

Mysql高级

2021-07-07 21:43:08   8  举报





AI智能生成

mysql高级优化总结(偏向java优化)

mysql

MySQL主从复制原理

Mysql优化

数据分析

学习总结

作者其他创作

大纲/内容

mysql架构介绍

mysql简介

高手的 mysql 是怎样炼成的?

数据库内部结构和原理

数据库建模优化

数据库索引的建立

sql语句优化

mysql 服务器的安装配置

数据库的性能监控分析与系统优化

mysql服务器的优化

各种参数常量设定

查询语句优化

主从复制

分布式架构搭建,垂直切割和水平切割

软硬件升级

容灾备份与恢复

shell 或 python 等脚本语言开发

对开源数据库进行二次开发

sql编程(自定义函数,存储过程,触发器,定时任务)

mysql linux的安装(5.7)

检查当前系统是否安装过mysql

安装mysql服务端

安装mysql客户端

查看mysql安装时创建的mysql用户和mysql组

mysql服务的启动和停止

mysql服务启动后,开始连接

mysql服务状态

启动 mysql

systemctl start mysqld

重启 mysql

systemctl restart mysqld

停止 mysql

systemctl stop mysqld

查看mysql 状态

systemctl status mysqld

修改配置文件的位置

问题

linux 向表中插入一条中文字符报错误

修改全局配置文件(vim etc/my.cnf)

character-set-server=utf8

表修改类型字符集

alter table xs modify name varchar(30) character set utf8;

修改已有数据库的字符集

alter database 库名 character set 'utf8';

mysql的用户与权限管理(远程访问mysql)

mysql 用户管理

创建用户

create User zs identified by '123123'

授权

grant all privileges on *.* to root @'%' identified by '123123'

修改用户密码

set password = password('123456')

修改的是硬盘里面的数据,内存中的数据并没有修改,索引需要刷新内存中的数据使用 flush privileges;

如果navite 远程连接失败注意:防火墙要开放3306端口

查看防火墙状态

systemctl status firewalld

开启防火墙

service firewalld start

关闭防火墙

service firewalld stop

重启防火墙

service firewalld restart

如果开启防火墙则需要开放端口

开放3306

firewall-cmd --zone=public --add-port=3306/tcp --permanent

重启防火墙

firewall-cmd --reload

查看防火墙开放端口

firewall-cmd --list-ports

mysql 的一些杂项配置

sql_mode

创建表

问题

结论: group by 使用原则 select 后面只能放函数和 group by 后的字段

如何查看sql_mode

show variables like '%sql_mode%';

把公司服务器的sql_mode验证修改为本地的服务器的sql_mode

mysql 配置文件

二进制日志 log-bin

主从复制

错误日志 log-err

默认是关闭的,记录严重的警告和错误信息,每次启动和关闭的详细信息

查询日志 log

默认是关闭的,记录查询的sql语句,如果开启会降低mysql的整体性能,因为记录日志会增加服务器负担

数据文件

俩系统

windows

mysql安装路径/data

linux

使用命令查看全部库

frm文件

存放表结构

myd文件

存放表数据

myi文件

存放表索引

如何配置

windows配置文件

my.ini

linux配置文件

/etc/my.conf

mysql 逻辑架构介绍

总体概览

执行步骤为以下几步

1), mysql之外类型Java 程序访问 (Connectors)

2), 和连接池进行沟通 ( Connection Pool )

3), 缓存,缓冲查询 ( Caches )

4), SQL接口分析sql ( SQL Interface )

5), 解析器复杂sql 解析 ( Parser )

6), 优化器,不影响结果进行优化,生成执行计划 ( Optimizer )

7), 存储引擎按执行计划分类执行 ( Pluggable Storage Engines )

8), 存入缓存 ( Buffers )

利用 show profile 查看sql的执行计划

修改配置文件 /etc/my.cnf

query_cache_type=1

开启 profiling

查看 profiling 是否开启

show variables like '%profiling%';

开启这样设置 set profiling =1 ;

select * from 表名

show profiles; 查看执行计划

show profile cpu,block io for query ?

mysql 存储引擎

查看命令

innodb 和 myisam

行表锁谁会发生死锁?

行锁

什么情况下会用到 MyISAM存储引擎?

Mysql 自带的表都是用 MyISAM存储引擎,不会出现高并发,节省资源

各个存储引擎介绍

InnoDB

InnoDB 是mysql 的默认事务型引擎,它被用来处理大量短期事务,除非有非常特别的原因需要使用其它的存储引擎,否则应该优先考虑 InnoDB 引擎。

MyISAM

MyISAM 提供了大量的特性,包含全文索引,压缩,空间函数等,但 MyIsam 不支持事务和行级锁,有一个缺陷就是崩溃后无法安全恢复。

Archive

Archive 档案存储引擎只支持 insert 和 select 操作,在 mysql 5.1之前不支持索引。 Archive 表适合日志和数据采集类应用。

Blackhole

Blockhole 引擎没有实现任何的存储机制,它会丢弃所有插入的数据,不做任何保存。但服务器会记录 Blackhole 表的日志,所以可以用于复制数据库到备库,或者简单地记录到日志。但这种应用方式会碰到很多问题,因此不推荐使用

CSV

CSV 引擎可以将普通的csv文件作为mysql的表来处理,但不支持索引,CSV引擎可以作为一种数据交换机制,非常有用,CSV存储的数据直接可以在操作系统中,用文本编译器,或者 excel 读取

memory

如果需要快速地访问数据,并且这些数据不会被修改,重启以后丢失也没有关系,那么使用 memory 表是非常有用的 moemory 表至少比 myisam 表要快一个数量级别

federated

federated 引擎是访问其它服务器的一个代理,尽管该引擎看起来提供了很好的跨服务器的灵活性,但也经常带来问题,因此默认禁止

索引优化分析

性能下降 sql 慢了 (执行时间长,等待时间长)

查询语句写的烂

索引失效(前提是你建了索引,没有使用上索引)

单值索引

复合索引

关联查询有太多的join(设计缺陷或不得已的需求)

sql优化

服务器调优及各个参数设置(缓冲,线程数等)

调整 my.cnf

没有充分利用到索引

建立索引

数据过多

分库分表

常见通用的join查询

sql的执行顺序

手写

机读

总结

7中join图

建表sql语句

查询语句

实际工作中都是使用左外连接,很少使用右外连接

索引简介

索引是什么?

排好序的快速查找数据结构就是索引

详解(二叉树)

Mysql索引为什么没有使用二叉树???

二叉树有一个缺点,例如,把5的那条数据删了新增一个92,然后再把23删了,新增一个93,然后再把22删了,新增一个95,然后再把77删了在添加一个98,这时,就变为了链表的数据结构,最好的情况是平衡的一棵树,最坏的情况是一个链表

结论

一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上

我们平常说的索引,如果没有特别指明,都是指 B树(多路搜索树,并不一定是二叉树的)结构组织的索引。

优势

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

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

劣势

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

实际上索引大大提高了查询速度,但同时也降低了更新表的速度

索引只是提高效率的一个因素,如果你的mysql有大数据量的表,就需要花大量时间研究建立最优秀的索引,或者优化查询

Mysql 索引结构(平衡树)

什么是平衡树

例如把5 删了新增一个24,这时这个树不平衡了,平衡树会进行旋转,然后会把22挪到了左边23挪到了上边24挪到了右边,这时有变平衡了

BTree索引

原理图

红色小方块 ->指向数据指针, 数据 ,向下指针

B+Tree索引

原理图

数据,向下指针

B树和B+树的区别

B树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。

在B树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。

那么为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

B+树的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

时间复杂度(时间一去不复返)

同一问题可用不同算法解决,而一个算法的质量优劣将影响到算法乃至程序的效率,算法分析的目的在于选择合适算法和改进算法

我们希望随着问题规模的增长复杂度是趋于稳定地上升,但是上升的幅度不能太大

拓展: log n是什么意思? 2的3次幂结果是8 ,log n就是反向算 3

聚簇索引与非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。
如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致

除了自己建的主键之外,建的所有字段都是非聚簇索引

空间复杂度(可以用钱买)

空间复杂度全称就是渐进空间复杂度（asymptotic space complexity），表示算法的存储空间与数据规模之间的增长关系。

mysql索引分类

单值索引

即一个索引只包含某个单列,一个表中可以有多个单列索引

create index 索引名 on 表名(索引字段);

唯一索引

索引列的值必须唯一,但允许为null值

create unique index 索引名 on 表名(表字段)

主键索引

设定为主键后数据库会自动建立索引, innoDB 为聚簇索引

复合索引

即一个索引包含多个列

create index 索引名 on 表名(索引字段1,索引字段2...);

基本语法

索引

alter四种方式建立索引

不给你索引名根据表名,库名如何删除索引,只剩下主键索引

查询索引名(information_schema库的STATISTICS 表)

SELECT s.INDEX_NAME,s.COLUMN_NAME,s.INDEX_TYPE,s.CARDINALITY FROM information_schema.STATISTICS s WHERE s.TABLE_NAME = 't_emp' AND s.TABLE_SCHEMA = 'datelog' and index_name <> 'PRIMARY' and seq_in_index=1

怎么把字符串转变为sql

使用游标和预编译

使用存储过程进行删除

mysql索引结构

BTree索引

检索原理

hash索引

full-text全文索引

r-tree索引

那些情况下可以建立索引

主键自动建立唯一索引

频繁作为查询字段应该建立索引

查询中与其他表关联的字段,外键建立索引

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

where 条件用不到字段不需要创建索引

单键/组合索引选择问题 who? 在高并发的情况下使用组合索引

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

查询中统计或者分组字段

那些情况下不要建立索引

表记录太少

经常增删改的字段

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

过滤性不好的不适合建立索引

数据重复且分布均匀的表字段,因为应该只为最经常查询和最经常排序的数据列建立索引

性能分析

Mysql Query Optimizer

mysql常见性能瓶颈

CPU: CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候

IO: 磁盘I/O 瓶颈发生在装入数据远大于内存容量的时候

服务器硬件的性能瓶颈 : top, free , iostal 和 vmstat 来查看系统的性能状态

Explain

是什么(执行计划)

能干嘛

表的读取顺序(id)

数据读取操作的操作类型(selecttype)

那些索引可以使用(possiblekeys)

那些索引被实际使用(key)

表之间的引用(ref)

每张表有多少行被物理查询(rows)

怎么玩

Explain + sql语句

执行计划包含的信息

字段解释

select 查询的序列号,包含一组数字,表示查询中执行 select 子句或操作表的顺序

又分为三种顺序

id 相同,执行顺序从上到下

id不同,如果是子查询,id序号会递增,id值越大优先级越高,越先被执行

id相同又不同,同时存在先从大到小,然后再从上到下

id 号每个号码,表示一趟独立的查询。一个sql的查询趟数越少越好

select_type

有哪些类型

table

partitions

type

显示查询使用了何种类型 从最佳到最差依次是: system > const > eq_ref > ref > range > index > ALL

system

表只有一行数据(等于系统表),这是const类型的特例,平时不会出现,这个也可以忽略不计

const

表示通过索引一次就找到了,const 用于比较 primary key 或者 union 索引。因为只匹配一行数据,所以很快如将逐渐置于 where 列表中, mysql 就能将该查询转换为一个常量

eq_ref

唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描

ref

非唯一索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而它可能找会找打多个符合条件的行,所以它应该属于查找和扫描的混合体

range

只检索给定范围的行,使用一个索引来选择行,key列显示使用了那个索引,一般就是在你 where 语句中出现 between < , > , in等语句 ,这种扫描索引比全表扫描要好,因为它只需要开始与索引的某一点,而结束语另一点,不用全表扫描

all

full table scan 将遍历全表以找到匹配的行

index_merge

在查询过程中需要多个索引组合使用,通常出现有 or 的关键字sql中

ref_or_null

对于某个字段即需要关联条件,也需要null值的情况下,查询优化器会

index_subquery

利用索引来关联子查询,不在全表扫描

unique_subquery

该连接类型类似于 index_subquery 子查询中的唯一索引

备注 : 一般来说,将保证查询至少达到range 级别最好能达到 ref。

如果你的type类型为 all 并且你的数据在百万以上的请你优化你的sql语句

possiblekeys

显示可能应用在这张表中的索引,一个或多个。查询设计到的字段上若存在索引,则改索引将被列出,但不一定被查询实际使用

key

实际使用的索引,如果为null ,则没有使用索引

查询中使用了覆盖索引,则改索引仅出现在 key 列表中

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精确性的情况下长度越短越好,key_len 显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出来的(key_len 越大越好)

ref

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

rows

rows 列显示Mysql 认为它执行查询时必须检查的行数 (值越小越好)

filtered

filtered列表示将按表条件筛选的表行的估计百分比。最大值为100，这意味着没有对行进行筛选。值从100减小表示过滤量增加。rows显示检查的估计行数，rows×filtered显示将与下表联接的行数。例如，如果rows为1000，filtered为50.00（50%），则要与下表联接的行数为1000×50%=500。

Extra

包含不适合在其它列中显示但十分重要的额外信息

Using filesort(要你命三千)

如何出现问题?

如何解决这个问题呢?

Using temporary(要你命三万)

如何出现这个问题?

如何解决这个问题呢?( group by 包含一个排序和分组)

Using index

如果同时出现?

只有一个?

覆盖索引(Covering Index)

Using where

表名使用了 where 过滤

Using join buffer

使用了连接缓存

Impossible WHERE

如何出现这个问题?

WHERE子句总是false，不能用来获取任何元组

Select tables optimized away

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

Distinct

优化 distinct 操作,第一个匹配的行后，它将停止为当前行组合搜索更多行

热身case

索引优化

索引分析

单表优化

创表语句

问题及优化

问题?

解决思路1

解决思路2

俩表优化

建表语句

left join案例分析1

left join案例分析2

right join ,和left join 基本没变化,记住一句话: left join(左)把索引建立在 join后的那个表,right join(右连接)把索引建立在 right 前面那个表 (相反建立索引)

三表优化

建表语句

索引优化

Join优化总结

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

2),优先优化 NestedLoop的内层循环

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

4),当无法保证驱动表的Join条件字段被索引且内存资源充足的前提下,不要太吝啬JoinBuffer的设置

索引失效(应该避免)

建表sql

单表-索引失效的原因

1), 全值匹配我最爱

如果只用到覆合索引的其中一个或者多个又会如何呢?

只查覆合索引第一个

只查覆合索引前俩个

全值匹配

索引如何不会生效呢?(没有了火车头 ,索引就会失效)

创建的索引是 nameAgePos 这时我们只查Age 和 pos

创建的索引是 nameAgePos 这时我们只查 pos

口诀1 : 带头大哥不能死(我们建立的索引 nameAgePos 这里的带头大哥就是 name 相当于火车头,它不能丢)

我们创建的索引 nameAgePos 这时我们只查 name与Pos

2),最佳左前缀法则

如果索引多列,要遵循最左前缀法则,指的是查询从索引的最左侧前列开始并且 不跳过索引中间列

3),不在索引where列上做任何操作(计算,函数,(自动or手动)类型转换),会导致索引失效转向全表扫描

过程和结果同样重要

问题查询name为 july的用户信息

方式1

方式2

但是它们的执行过程一个天上一个地下

方式1

方式2

4),存储引擎不能使用索引中范围条件右边的列(这里说的是建索引的地方)

全值匹配

范围之后全失效

5),尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *

使用 *

不使用 *(返回索引字段)

不使用* 且查询包含 < 或者>

6),mysql在使用 不等于(!= 或者 <>) 的时候无法使用索引回导致全表扫描

等于 =

不等于 !=

不等于 <>

7),is null 走索引,is not null 也无法使用索引

is null

is not null

8), like 以通配符开头 ('%abc') mysql索引会失效会变为全表扫描

like查询%号写右边

方式1

方式2

方式3

问题?(解决like %xx% 时索引不被引用的方法?)

覆盖索引(你建的索引和你查的字段个数顺序上最好完全一致)

覆合索引 nameAgePos

生效

查询语句1

查询语句2

查询语句3

查询语句4

失效

语句1查询索引之外字段

语句2 select*查询全部字段

9),字符串不加单引号索引会失效

语句1(加单引号)

语句2(不加单引号)

10),少用 or 用它来连接时索引失效

使用or 关键字索引失效

单表-一般性建议

对于单键索引,尽量选择针对当前query 过滤性更好的索引

在选择组合索引的时候,当前Query中过滤性最好的字段顺序中,位置越靠前越好

在选择组合索引的时候,尽量选择可以能够包括当前query中 where 子句中更多字段的索引

在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序列的最后

书写sql语句时,尽量避免造成索引的失效情况

单表-口诀

关联表查询优化

建表语句

left /right join案例

explain select * from class left join book on class.card=book.card;

结论: type类型是all ,这时的迪达尔结果集为 400

为 book表的card 建立索引,如下 create index idx_book_card on book(card); 然后重新执行 explain语句

结论: 这是 book表使用了索引 type类型为 ref 笛卡尔结果集为 20 其中Extra 也没有了 Using join buffer

如果我们在给class 表的card 也建立索引是否能更快的查询到数据呢? create index idx_class_card on class(card); 再次执行 explain 语句

结论: 就算我们为俩个表的主外键都建立了索引它们的笛卡尔乘积都是20 性能也没有得到很大的提升 ,class表也是进行了全表的扫描,索引,当我们进行关联查询的时候,尽量给被驱动表建立索引,驱动表不必建立索引

inner join 案例

explain select * from class inner join book on class.card=book.card; (注意: class 表的card字段是有索引的,而book字段的card 是有索引的)

结论: 如果用的是inner join mysql自己选择驱动表和被驱动表

小表驱动大表 ,小表放在驱动表 ,大表放在被驱动表

关联查询-建议

保证被驱动表的join字段已经被索引

left join 时,选择小表作为驱动表,大表作为被驱动表

inner join时,mysql会自己帮你把小结果集选为驱动表

子查询尽量不要放在被驱动表,有可能使用不到索引

能够直接多表关联的尽量直接关联,不用子查询

子查询优化

尽量不要使用 not in 或者 not exists 用left join 表名 on xxx is null 来代替它

面试题讲解

建表语句

案例(我们创建的覆合索引 idx_test03_c1234 根据以下sql分析下索引的使用情况)

热身(都按照索引顺序来进行查询)

全值匹配我最爱

前俩个索引

只查最左侧索引

查左侧的三个索引

索引顺序和where查询的顺序不同(不包含< 或者 >)

explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';

explain select * from test03 where c4='a4' and c3='a3' and c2='a2' and c1='a1';

总结: mysql 底层会对sql语句进行一次优化,在 optimiter 中,建议where 后面的判断根据索引来写,因为这样会减少一次mysql底层的优化

索引顺序和where查询的顺序不同(包含< 或者 >)

explain select * from test03 where c1='a1' and c2='a2' and c3 >'a3' and c4='a4';

explain select * from test03 where c1='a1' and c2='a2' and c4 >'a4' and c3='c3';

索引中使用 order by(出现 Using filesort 就是没有用到索引)

顺序错,必排序

explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;

explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;

explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;

explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;

explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;

explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;

explain select * from test03 where c1='a1' and c2='a2' order by c4;

explain select * from test03 where c1='a1' and c2='a2'order by c3;

explain select * from test03 where c1='a1' and c2='a2'and c4='a4' order by c3;

无过滤条件,不使用索引

explain select SQL_NO_CACHE * from emp order by age,deptId;

创建索引 create index idx_emp_ageDeptid on emp(age,deptId);

再次执行explain 语句

我们需要添加过滤条件,如果实在没有过滤条件就是 limit 10

方向相反,必排序(Using filesort)

创建覆合索引 create index idx_emp_ageDeptidName on emp(age,deptId,name);

explain select SQL_NO_CACHE * from emp where age=1 order by deptId desc,name desc;

explain select SQL_NO_CACHE * from emp where age=1 order by deptId asc,name desc;

排序分组优化

索引的选择

例子,查询年龄为30岁的,且员工编号小于101000的用户,按用户名称排序 select SQL_NO_CACHE* from emp where age =30 and empno <101000 order by name;

建立索引

create index idx_emp_ageEmpno on emp(age,empno);

explain 执行sql语句( type 类型为 range rows 为 4000多行会出现手动排序的Using filesort)

create index idx_emp_ageName on emp(age,name);

explain 执行sql语句 (type 类型为 ref rows 物理扫描行数为 5w行)

总结: 如果上面俩个索引(idx_emp_ageEmpno 和 idx_emp_ageName ) 这时mysql 会选择 idx_emp_ageEmpno ,因为mysql 会自动给我们选择效率最高的索引

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

索引中使用 group by

explain select * from test03 where c1='a1' and c4 ='a4' group by c2,c3;

explain select * from test03 where c1='a1' and c4 ='a4' group by c3,c2;

总结: group by 表面交分组,分组之前必排序,group by 和 order by其排序法则和优化原则大都是一致的,只是group by 有having 这个筛选

like 模糊查询

explain select * from test03 where c1='a1' and c2 like 'a2%'and c3='a3';

explain select * from test03 where c1='a1' and c2 like '%a2'and c3='a3';

explain select * from test03 where c1='a1' and c2 like '%a2%'and c3='a3';

explain select * from test03 where c1='a1' and c2 like 'a%a2%'and c3='a3';

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

group by 基本上都需要进行排序,如果有错乱的产生,会有临时表产生

一般性建议

在对于单键索引,尽量避免选择针对当前 Query 过滤性更好的索引

在选择组合索引的时候,当前 Query 中过滤性最好的字段在索引字段顺序中,位置越靠左越好

在选择组合索引的时候,尽量选择可以能够包含当前 Query 中的 where 子句中更多字段的索引

尽可能通过分析统计信息和调整Query 的写法来达到选择合适索引的目的

索引优化口诀

全值匹配我最爱,最左前缀要遵守

带头大哥不能死,中间兄弟不能丢

索引列少计算,范围之后全失效

like 百分写最右,覆盖索引不写*

不等null还有or,索引失效要少用

查询截取分析

查询优化

永远小表驱动大表 (类似嵌套循环 Nested Loop)

注意: emp表和dept表应该建立索引

EXISTS

order by关键字优化

order by 尽量使用Index方式排序,避免使用 filesort 方式排序

建表语句

order by能不能产生filesort

不能产生filesort的sql

explain select * from tblA where age>20 order by age;

explain select * from tblA where age>20 order by age,birth;

explain select * from tblA where birth > '2020-09-23 14:26:15' order by age;

能产生filesort

explain select * from tblA where age>20 order by birth;

explain select * from tblA where age>20 order by birth,age;

explain select * from tblA where age>20 order by birth;

explain select * from tblA where birth > '2020-09-23 14:26:15' order by birth;

explain select * from tblA order by age ,birth desc;

Mysql支持二种方式的排序,FileSort和 Index , Index 效率高,它指 Mysql 扫描索引本身完成排序。 FileSort 方式效率低

Order by 满足俩种情况,会使用Index方式排序

order by语句使用最左前缀法则

使用Where 子句与 Order by 子句条件列组合满足索引最最左前缀

结论 : 尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀法则

如果排序的字段不在索引列上,filesort有俩种算法

单路排序

双路排序

结论及引申出来的问题?

优化策略

增大 sort_buffer_size的容量

增大 max_length_for_sort_data参数的设置

why?(提高order by 的速度)

1), order by 时 select * 是一个大忌只 Query 需要字段,这点非常重要。

2),尝试提高 sort_buffer_size

3),尝试提高 max_length_for_sort_data

总结和查询要点(为排序使用索引)

1),mysql 俩种排序方式: 文件排序或扫描有序索引排序

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

Case (创建索引 key_a_b_c)

order by使用最左前缀

order by a

order by a,b

order by a,b,c

order by a desc,b desc ,c desc

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

where a=const order by b,c

where a=const and b=const order by c

where a=const and b > const order by b,c

不能使用索引进行排序

order a desc , b asc ,c desc (排序不一致)

where g=const order by b,c (带头大哥a不能丢)

where a=const order by c (中间兄弟 b 不能丢)

where a=const order by a,d (d不是索引的一部分)

where a in (...) order by b,c (对于排序来说,多个相等条件也可是范围查询)

Group by关键字优化

group by 使用索引的原则几乎根 order by 一致,唯一区别就是 group by 即使没有过滤条件也能用到索引

当无法使用索引列,增大 max_length_for_sort_data 参数的设置 + 增大 sort_buffer_size参数的设置

where 高于 having,能写在 where 限定的条件就不要去 having限定了

最后使用索引的手段: 覆盖索引(简单来说就是 select 和到 from 之间查询的列 <= 使用的索引列 + 主键)

create index idx_emp_ageName on emp(age,name);

explain select * from emp where age <> 30;

结论: type 类型为全表扫描 rows 为50w行

explain select id,name,age from emp where age <> 30;

结论: type类型为 range 使用到了索引 rows为25w行

总结: 写sql不要写 * 写具体的字段

慢查询日志

是什么?

怎么玩?

说明

查看是否开启及如何开启

默认

show variables like '%slow_query_log%';

全局开启

set global slow_query_log=1;

永久开启慢查询日志如何做呢?

如果开启了慢查询日志,什么样的sql将被记录在慢查询日志里呢?

查看默认阈值

show variables like '%query_time%';

修改为阈值大于3的慢sql

set long_query_time=3;

Case分析

默认睡4秒的sql : select sleep(4);

然后去你的 slow_query_file 路径下面查看你的慢sql

如何查看当前系统存在多少条慢sql

show global status like '%Slow_queries%';

配置版本怎么玩呢?

日志分析工具 mysqldumpslow

s : 是表示按照何种方式排序

c : 访问次数

l : 锁定时间

r : 返回记录

t : 查询时间

al :平均锁定时间

ar : 平均返回记录数

at : 平均查询时间

t : 即返回前面多少条的数据

g : 后面搭配一个正则匹配模式,大小写不敏感的

mysqldumpslow 工作常用参考

得到返回记录集最多的10个sql集合

mysqldumpslow -s r -t 10 /var/lib/mysql/host_name-slow.log

得到返回次数最多的10个sql

mysqldumpslow -s c -t 10 /var/lib/mysql/host_name-slow.log

按照时间排序的前10条里面包含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/host_name-slow.log

建议 : 使用这些命令时结合 | more 使用,否则有可能出现爆屏的情况

批量数据脚本

建表语句

设置参数 log_bin_trust_function_creators

默认

show variables like '%log_bin_trust_function_creators%';

开启

set global log_bin_trust_function_creators=1;

创建函数,保证每条数据都不相同

随机产生字符串

随机产生部门编号

创建存储过程

创建emp表中插入数据的存储过程

创建dept表中插入数据的存储过程

插入数据

dept表插入

call insert_dept(100,10);

emp表插入

call insert_emp(10001,500000);

show Profile

是什么?

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

默认情况是关闭的

show variables like '%profiling%';

保存最近15次的运行结果

开启 profile

set profiling=1;

分析

运行sql

select * from emp group by id%20 order by 5;

select * from emp group by id%10 limit 150000;

查看结果

show profiles

诊断sql , show profile cpu,block io for query 上一步前面的问题sql数字号码

all : 显示所有的开销信息

block io : 显示块IO相关开销

context switches : 上下文切换相关开销

cpu : 显示cpu相关开销信息

ipc : 显示发送和接受相关开销的信息

memory : 显示内存相关开销信息

page faults : 显示页面错误相关开销信息

source : 显示和source_function , source_file , source_line 相关开销信息

swaps : 显示交换次数相关开销信息

日常开发中需要出现的问题

status状态栏出现下面几种情况

Creating tmp table 创建临时表

拷贝数据到临时表

用完再删除

converting HEAP to MyISAM 查询结果太大,内存都不够用了往磁盘上搬数据了

Copying to tmp table on disk 把内存中临时表的数据复制到磁盘,危险

locked

show processlist(展现进程列表)

可以使用 kill id

全局查询日志

永远不要在生产环境开启这个功能

配置启用

编码启用

1), set global general_log =1;

2), set global log_output='TABLE';

查看 select * from mysql.general_log;

工具和技巧拾遗

视图 View

什么是视图?

将一段查询的sql封装为一个虚拟的表

虚拟表会不会对sql有优化查询的作用? 不会,因为视图只是进行了封装

这个虚拟表只保存了sql逻辑,不会保存任何查询结果

作用

封装复杂sql语句,提高复用性

逻辑放在数据库上面,更新不需要发布程序,面对频繁的需求变更更灵活

适用场景

很多地方可以共用的一组查询结果

报表

创建/更新语法

创建

create view 视图名 as sql语句

使用

select * from view_test

更新

create or replace view view_test as
select d.deptName, if(avg(e.age)>50,'老鸟','菜鸟') 老鸟or菜鸟 from t_dept d
inner join t_emp e
on d.id=e.deptId
group by d.deptName,d.id

注意事项(使用 5.5)

mysql 的视图中不允许有form 后面的子查询,但 oracle可以

mysql 锁机制

概念

什么是锁?

生活购物

锁的分类

从对数据库操作的类型进行分类

读锁

写锁

从对数据库操作的粒度分类

表锁(偏读)

Case分析

手动增加表锁

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

查看锁的命令

show open tables;

加锁

lock table mylock read,book write;

解锁

unlock tables;

读锁/写锁对我们性能有什么影响?

读锁

写锁

结论

表锁分析

通过命令 show open tables 查看那个表被锁了

如何分析表锁定呢?

show status like '%table%';

MyISAM 的读锁写锁是以写优先,这也是 MyIsam 不适合做写为主表的引擎,因为写锁后,其它线程不能进行任何操作,大量的更新会使查询很难得到锁,从而造成永久阻塞

行锁(偏写)

事务及ACID属性

原子性

原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。

一致性

事务必须使数据库从一个一致性状态切换到另一个一致性状态。

隔离性

事务的隔离性是指事务的执行不能被其他事务干扰,即一个事物内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事物之间不能互相干扰。

持久性

持久性是指一个事物一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响。

并发事务处理带有的问题

更新丢失(Lost Update)

脏读 (Dirty Reads)

不可重复读(Non-Repeatable Reads)

幻读( Phantom Reads)

mysql数据库的隔离级别(常用到不经常用)

repeatable read (读未提交的数据)

出现幻读 ,不会出现脏读和不可重复读

read committed (读已提交的数据)

出现幻读和不可重复读,不会出现脏读

read uncommitted (可重复读)

出现脏读,幻读不可重复读

serializable (串行化)

都不会出现脏读,幻读和不可重复读

查看mysql隔离级别

show variables like '%tx_isolation%';

Case分析

建表语句

行表锁的基本演示

关闭数据库的自动提交

默认是开启的查询命令是: show variables like '%autocommit%';

关闭命令是 : set autocommit = 0;

分析 (连接客户端a,连接客户端b)

更新同一行

如果a 客户端更新表中x行的数据,但是不提交,自己查询x行这是更新后的行数据,这时b客户端查询x行获取则是更新之前的旧数据,只有在 a客户端 commit 提交之后,b客户端才会获取提交之后的x行的新数据

如果a客户端更新x行,b客户端也更新x行,同时操作,如果a客户端先进行更新操作,则b客户端则会阻塞(如果b客户端先进行更新操作,则a客户端则会阻塞)

更新不是同一行

如果a客户端更新表中x行,这时b客户端更新表中的y行,这时a客户端与b客户端不会发生阻塞的状态

无索引行锁升级为表锁

由于varchar使用单引号引起的行锁变为表锁

a客户端

b客户端就会阻塞

间隙锁危害

如何产生

data

a客户端使用范围进行updat批量修改数据

b客户端插入一个范围之内没有的值(发生阻塞)

面试题: 如何锁定某一行

锁定一行

总结

行锁分析

检查行锁争夺情况

show status like '%innodb_row_lock%'

字段说明

Innodb_row_lock_waits

系统启动后到现在锁定总共等待的次数

Innodb_row_lock_time_max

从系统启动到现在等待最长的一次花费的时间

Innodb_row_lock_time_avg

每次等待平均花的平均时间

Innodb_row_lock_time

从系统启动到现在锁定总时间长度

Innodb_row_lock_current_waits

当前系统正在等待锁定的数量

尤其是当等待次数很高,而且每次等待时长也不小时,我们需要分析系统中为什么会有这么多的等待,然后根据分析结果进行指定优化计划

优化建议

尽可能让所有检索都通过索引来完成,避免无索引行索升级为表锁

合理设计索引,尽量缩小锁的范围

尽可能较小检索索引,避免间隙锁

尽量控制事物大小,减少锁定资源量和事件长度

尽可能低级别事务隔离

页锁

开销和加锁时间介于表锁和行锁之间:会出现死锁,锁定的粒度介于表锁和行锁之间,并发度一般

主从复制

主从复制的基本原理

slave 会从mast读取binlog来进行数据同步

原理图

mysql复制过程分成三步

复制的基本原则

每个salve只有一个 master

每个salve只能有一个唯一的服务器ID

每个master 可以有多个salve

复制的最大问题

网络延时

一主一从的常见配置

前提条件

mysql 版本一致且后台以服务运行

双向网络是否ping通

主从复制都配置在 [mysqld]节点下面.都是小写

修改主机配置文件 (my.ini)文件

server-id=1

log-bin=mysql-bin

log-err=本地mysql路径/mysqlerr

basedir=mysql本地路径

tmpdir=mysql本地路径

datadir=mysql本地路径/data

read-obly=0

binlog-ignore-db=mysql

binlog-do-db=数据库名

binlog_format=STATEMEN(默认)

修改从机my.cnf文件

server-id=2

log-bin=mysql-bin

重启主机mysql服务和从机mysql服务,注意关闭 linux的防火墙

linux 查看防火墙状态

systemctl status firewalld

主机和从机设置

主机

grant replication slave on *.* to '账号'@'从机数据库ip' identified by '密码' ;

刷新权限

flush privileges;

查看主机的状态

show master status;

从机

change master to master_host='192.168.73.1',master_user='zhangsan',master_password='123456', master_log_file='mysql-bin.000004',master_log_pos=606;

启动slave

start slave

查看从机状态

show slave status \G;

如下执行成功

从机出现问题 ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.

上一次已经启动了一个主从复制,需要把上一个主从复制关闭,然后再次启动从机的语句

stop slave;

reset master;

测试内容

主机新建表新建库增加数据

从机测试数据

Mycat 8066端口

介绍

是什么?

数据库中间件

干什么的

读写分离

数据分片

垂直拆分

水平拆分

垂直+水平拆分

多数据源整合

原理

拦截

原理图

Mycat 的原理中最重要的一个动词就是拦截 , 它拦截了用户发送过来的sql语句。首先对sql语句做了一些特定的分析,: 如分片分析 , 路由分析, 读写分离分析 ,缓存分析等,然后将此sql发往后端的真实数据库,并返回的结果做适当的处理,最终返回给用户

安装

下载地址 https://github.com/MyCATApache/Mycat-download

把 mycat 文件的所有内容 copy 到/user/local/

cp -r mycat/ /usr/local/

修改配置文件

server.xml

为了让防止与mysql名字相同,修改mycat的用户名

user name="mycat" name="password" 123456 name="schemas">TESTDB

schema.xml

删除 <schema></schema> 里面的所有内容

显示行数

:set nu

删除6行,32行所有内容

: 6,32 d

添加 <schema dataNode="dn1"></schema>

修改 <dataNode dataHost="host1" database="自己数据库名"></dataNode>

修改 <dataHost name="host1"></dataHost>

修改完如下

rule.xml

验证数据库访问情况

(2个主机的mysql都需进行检验)mysql -uroot -proot -h 192.168.73.131 -P 3306

如果出现错误(ERROR 1045 (28000): Access denied for user 'root'@'192.168.73.131' (using password: YES))

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

flush privileges;

启动程序

控制台启动

在 mycat/bin目录下

mycat console

后台启动

在 mycat/bin目录下

mycat start

启动时可能出现报错

域名解析失败

修改 etc/hosts文件添加linux用户名

如何查看linux用户名

hostname

添加系统名etc/hosts文件中

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 localhost.localdomain

Unable to start JVM: No such file or directory (2)

修改 mycat中conf里面的wrapper.conf

wrapper.java.command=java安装目录/bin/java

Unable to start JVM: Permission denied (13)

修改 mycat中conf里面的wrapper.conf

wrapper.java.command=java安装目录/bin/java

后台管理窗口

mysql -urooot -proot -P9066 -h ip地址

数据窗口(默认启动)

mysql -urooot -proot -P8066 -h ip地址

读写分离(先实现主从复制然后才能实现读写分离)

schema.xml(balance=?)

0(默认)

不开启读写分离机制,所有的读操作都发送到当前可用的 writeHost 上

全部的 readHost 与 stand by writerHost 参与 select 语句的负载均衡,简单的说就是当双主双从模式( M1 -> S1, M2 -> S2,并且M1 与 M2互为主备)正常情况下 M2,S1,S2 都参与 select 语句的负载均衡,M2才能写入

所有读操作都随机的在 writerHost, readHost 上分发

所有读请求随机分发到readHost上执行,writerHost 不负担读压力

@@hostname 使俩个库插入不同的用户名来查看

分库

如何选择分库表,;栗子把客户表拆分出来一个独立的服务器,订单表和其它关联的表一个服务器

为啥要把客户相关的表拆分出来呢,因为客户表与订单表没有join连接的关系,可以独立拆分出来

schema.xml

配置完成如下

分表操作(订单表 -orders)

订单表根据customer_id 进行分表

schema.xml

增加一行

rule.xml

<tableRule name="mod_rule">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>

向orders 插入6行数据,得到如下内容

问题? orders 不能使用数据库默认的自增主键,应该在插入的时候指定主键,

跨库join

ER表(订单详情表-orders_detail)

添加orders的字表 orders_detail的 schema.xml

name="orders_detail" :字表订单详情表表名

primaryKey="id" id 是订单标详情的主键id

joinKey="order_id" order_id 是订单详情表的字段关联父类的字表字段

parentKey="id" id 是orders 表关联字表的字段

向订单详情表插入数据,并执行查询的sql语句

插入

insert into orders_detail (id,detail,order_id) values (1,'details',1);
insert into orders_detail (id,detail,order_id) values (2,'details',2);
insert into orders_detail (id,detail,order_id) values (3,'details',3);
insert into orders_detail (id,detail,order_id) values (4,'details',4);
insert into orders_detail (id,detail,order_id) values (5,'details',5);
insert into orders_detail (id,detail,order_id) values (6,'details',6);

关联查询sql

select o.*,d.* from orders o inner join orders_detail d on o.id = d.order_id;

全局表(dict_order_type)

schema.xml

向订单字典表插入数据

insert into dict_order_type (id,order_type) values (101,'type1');
insert into dict_order_type (id,order_type) values (102,'type2');

全局序列

0本地文件(不推荐,抗风险能力差,如果主机mycat挂了,从机不知道从那个地方开始,如果没挂前生成到1000 ,挂了之后如果从机从900开始生成主键就重复了)

1数据库方式

数据库序列方式原理

利用数据库生成一张表来进行技术累加,但是并不是每次生成序列都需要读写数据库,这样效率太低

mycat会预加载一部分号段到mycat内存中,这样大部分读写序列都是在内存中完成的如果内存中的号段已经用完,mycat会再向数据库要一次