首页  思维导图  详情

mysql

2022-02-24 11:35:51   0  举报





AI智能生成

为你推荐

查看更多



MySQL事务大纲

mysql

Mysql

MySQL

Mysql

MySQL高级调优及面试点详解

mysql

MySQL总结

mysql数据库

MySQL执行流程

java

作者其他创作

大纲/内容

非叶子节点不存储data，只存储索引，可以放更多的索引

叶子节点包含所有索引字段

叶子节点用指针连接，提高区间访问的性能

定义

SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; 16kb

非叶子节点大概能存储1170个索引数据

如果树的高度=3，能存储多少记录 1170*1170*16=2千万

索引容量

B树叶子节点没有指针连接，访问性能差。在查询范围的时候，效率差。

B树非叶子节点也能存储数据，相同高度下，存放的索引记录少

与B树区别

结构：B+树

表数据文件本身就是按B+Tree组织的一个索引结构文件

聚集索引-叶节点包含了完整的数据记录

索引结构

如果不是自增，插入索引的时候，会影响索引的结构，导致效率低

如果不建立主键，mysql会查找一个不重复数据的列作为索引列；如果没有这样的列，mysql会自己建立这个隐藏列作为索引列

建议Innodb表必须建主键，并且推荐使用整型的自增主键

如果存储的是记录的话，修改记录的时候就要修改两份，涉及一致性问题

一致性

节省存储空间

非主键索引结构叶子节点存储的是聚集索引的id

非主键索引，需要回表

innodb（聚集索引）

叶子节点只包含索引和地址，没有包含数据，数据保存在.MYD文件里面

索引在MYI文件

索引结构

MyISAM（非聚集索引）

Hash索引

数据存储位置

最左前缀原理

联合索引

① 索引

explain extended

explain partitions

变种

Explain介绍

explain select * from actor;

Explain分析

simple

primary

subquery

derived

union

select_type

table

NULL

eq_ref

ref

range

index

ALL

type

possible_keys

key

key_len计算规则

key_len

ref

rows

Using index

Using where

Using index condition

Using temporary

Using filesort

Select tables optimized away

Extra

Explain列介绍

mysql> set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合并优化 mysql> explain select (select 1 from actor where id = 1) from (select * from film whereid = 1) der;

全值匹配

最左前缀法则

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

存储引擎不能使用索引中范围条件右边的列

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

问题：解决like'%字符串%'索引不被使用的方法？

like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

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

少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，

范围查询优化

总结

索引最佳实践

② Explain详解与索引最佳实践

联合索引第一个字段用范围不会走索引

强制走索引

覆盖索引优化

in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

like KK% 一般情况都会走索引

示例

set session optimizer_trace=\"enabled=on\

select * from employees where name > 'a' order by position;

SELECT * FROM information_schema.OPTIMIZER_TRACE;

trace工具使用

示例

Order by与Group by优化

单路排序

双路排序

方式

1. 从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中3. 从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id4. 重复步骤 2、3 直到不满足 name = ‘zhuge’5. 对 sort_buffer 中的数据按照字段 position 进行排序6. 返回结果给客户端

单路排序过程

1. 从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id2. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中3. 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id4. 重复 3、4 直到不满足 name = ‘zhuge’5. 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序6. 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出所有字段的值返回给客户端

多路排序过程

Using filesort文件排序原理

代码先行，索引后上

联合索引尽量覆盖条件

不要在小基数字段上建立索引

长字符串我们可以采用前缀索引

where与order by冲突时优先where

基于慢sql查询做优化

索引设计原理

索引设计实战

根据自增且连续的主键排序的分页查询

根据非主键字段排序的分页查询

分页查询优化

主键自增且连续

结果是按照主键排序的

需要满足两个条件

让排序返回的字段尽量少，所以可以先查出主键

前提：t1有10000行记录表t2有100行记录

嵌套循环连接 Nested-Loop Join(NLJ) 算法

基于块的嵌套循环连接 Block Nested-Loop Join( BNL )算法

表关联两种算法

关联字段加索引，让mysql做join操作时尽量选择NLJ算法

小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

如何优化

Join关联查询优化

in和exsits优化

查询mysql自己维护的总行数

show table status

增加数据库计数表

count(*)查询优化

③ 索引优化

事务及其ACID属性

更新丢失(Lost Update)或脏写

脏读（Dirty Reads）

不可重读（Non-Repeatable Reads）

幻读（Phantom Reads）

并发事务处理带来的问题

事务隔离级别

从性能上分为乐观锁(用版本对比来实现)和悲观锁

从对数据库操作的类型分，分为读锁和写锁 (都属于悲观锁)读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分，分为表锁和行锁

分类

建表语句

LOCK TABLE mylock READ;

手动增加表锁

SHOW OPEN TABLES;

查看表上加过的锁

UNLOCK TABLES;

解锁

基本操作

案例

表锁

设置客户端A当前事务为未提交：SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

在客户端A开启一个事情，查询数据

在客户端B设置当前事务为读提交，然后开启一个事务，修改数据，但未提交

这时在客户端A的当前事务中能查询到客户端B修改的数据

一旦客户端B回滚，这个时候就产生了脏读

读未提交

设置客户端A当前事务为未提交：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

在客户端A开启一个事情，查询数据

在客户端B设置当前事务为读提交，然后开启一个事务，修改数据，但未提交

这时在客户端A的当前事务中能查询不到客户端B修改的数据，解决了脏读的问题

这个时候，B提交事务。

客户端A在当前事务下再次查询，会查询到B修改的数据。

出现了不可重复读的问题

读已提交

设置客户端A当前事务为未提交：SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ；

在客户端B设置当前事务为可重复读，然后开启一个事务，修改数据，但未提交

这时在客户端A的当前事务中能查询不到客户端B修改的数据，解决了脏读的问题

客户端A在当前事务下再次查询，不会查询到B修改且已提交的数据。解决了不可重复读问题。

这个时候在客户端A当前事务下，修改数据，会在客户端B修改的前提下修改数据。解决了数据不一致的问题

可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）。

可重复读

在上面的前提下，客户端B新增一条数据，并提交

客户端A不会查询到客户端B新增的数据，但是可以修改客户端新增的数据。再次查询，可以查询到新数据。出现了幻读。

幻读

行锁与事务隔离级别分析

行锁

间隙锁

临键锁(Next-key Locks)

Innodb_row_lock_current_waits: 当前正在等待锁定的数量

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

Innodb_row_lock_time_avg: 每次等待所花平均时间

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

Innodb_row_lock_waits:系统启动后到现在总共等待的次数

SHOW STATUS LIKE 'innodb_row_lock%';

行锁分析

查看事务

SELECT * FROM information_schema.`INNODB_TRX`;

‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到 kill trx_mysql_thread_id

‐‐ 查看锁等待详细信息show engine innodb status\\G;

查看INFORMATION_SCHEMA系统库锁相关数据

Session_1执行：select * from account where id=1 for update;Session_2执行：select * from account where id=2 for update;Session_1执行：select * from account where id=2 for update;Session_2执行：select * from account where id=1 for update;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ；

查看近期死锁日志信息：show engine innodb status\\G;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

死锁

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

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

尽可能减少检索条件范围，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

尽可能低级别事务隔离

锁优化建议

锁

④ 事务隔离级别、锁机制

⑤ MVCC(Muti-Version Concurrency Control)

垂直分片

取余\\取模：优点均匀存放数据，缺点扩容非常麻烦

按照范围分片：比较好扩容，数据分布不够均匀

按照时间分片：比较容易将热点数据区分出来。

按照枚举值分片：例如按地区分片

按照目标字段前缀指定进行分区：自定义业务规则分片

分片策略

水平分片（标准方案）

事务一致性问题

跨节点关联查询问题

跨节点分页、排序函数

主键避重问题

公共表处理

运维工作量

缺点

mysql单表记录达到500w

单表容量达到2GB

什么时候需要分库分表

对于用户数据，后期增长缓慢。预估三年

对于业务数据，增长快。需要按照预估量的2倍进行处理

数据预估

shardingsphere

mycat

DBLE

组件

基础

逻辑表：水平拆分的数据库的相同逻辑和数据结构表的总称

真实表：在分片的数据库中真实存在的物理表。

数据节点：数据分片的最小单元。由数据源名称和数据表组成

绑定表：分片规则一致的主表和子表。

广播表：也叫公共表，指素有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中都完全一致。例如字典表。

分片键：用于分片的数据库字段，是将数据库(表)进行水平拆分的关键字段。SQL中若没有分片字段，将会执行全路由，性能会很差。

分片算法：通过分片算法将数据进行分片，支持通过=、BETWEEN和IN分片。分片算法需要由应用开发者自行实现，可实现的灵活度非常高。

分片策略：真正用于进行分片操作的是分片键+分片算法，也就是分片策略。在ShardingJDBC中一般采用基于Groovy表达式的inline分片策略，通过一个包含分片键的算法表达式来制定分片策略，如t_user_$->{u_id%8}标识根据u_id模8，分成8张表，表名称为t_user_0到t_user_7。

核心概念

分库分表

主服务上打开binlog记录每一步的数据库操作

从服务上会有一个IO线程，负责跟主服务建立一个TCP连接，请求主服务将binlog传输过来

主库上会有一个IOdump线程，负责通过这个TCP连接把Binlog日志传输给从库的IO线程

从服务的IO线程会把读取到的binlog日志数据写入自己的relay日志文件中

从服务上另外一个SQL线程会读取relay日志里的内容，进行操作重演，达到还原数据的目的

备注：mysql的binlog日志还可以用于缓存，例如阿里巴巴的Canal

主从同步原理

双方MySQL必须版本一致。至少需要主服务的版本低于从服务

两节点间的时间需要同步。

必要条件

指定serverid：server-id=47

开启binlog：log_bin=master-binlog_bin-index=master-bin.index

主库主要配置

指定serverid：server-id=48

打开mysql中继日志：relay-log-index=slave-relay-bin.indexrelay-log=slave-relay-bin

打开从服务二进制日志log-bin=mysql-bin

#使得更新的数据写进二进制日志中log-slave-updates=1

从库主要配置

配置 my.cnf

GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';flush privileges;

当前日志的binlog文件

File

文件中的索引

Position

需要记录binlog文件的库

Binlog_Do_DB

不需要记录binlog文件的库

Binlog_Ignore_DB

#查看主节点同步状态：show master status;

root用户分配一个replication slave的权限

主库

设置同步主节点

start slave;

开启slave

show slave status;

查看主从同步状态

从库

需要登录后设置

stop slave ;set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;start slave ;

如果从库 Slave_SQL_Running=no

常见错误

#需要同步的二进制数据库名binlog-do-db=masterdemo#只保留7天的二进制日志，以防磁盘被日志占满(可选)expire-logs-days = 7#不备份的数据库binlog-ignore-db=information_schemabinlog-ignore-db=performation_schemabinlog-ignore-db=sys

主库配置my.cnf

#如果salve库名称与master库名相同，使用本配置replicate-do-db = masterdemo#如果master库名[mastdemo]与salve库名[mastdemo01]不同，使用以下配置[需要做映射]replicate-rewrite-db = masterdemo -> masterdemo01#如果不是要全部同步[默认全部同步]，则指定需要同步的表replicate-wild-do-table=masterdemo01.t_dictreplicate-wild-do-table=masterdemo01.t_num

从库配置my.cnf

部分同步

配置完成了之后，在show master status指令中，就可以看到Binlog_Do_DB和Binlog_Ignore_DB两个参数的作用了。

set global read_only=1;

设置从服务只读

读写分离配置

gtid_mode=onenforce_gtid_consistency=onlog_bin=onserver_id=单独设置一个binlog_format=row

主服务配置my.cnf

gtid_mode=onenforce_gtid_consistency=onlog_slave_updates=1server_id=单独设置一个

从服务配置my.cnf

基于GTID搭建

分别重启主服务和从服务，就可以开启GTID同步复制方式