首页  思维导图  详情

mysql

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





AI智能生成

mysql

MySQL总结

mysql数据库

MySQL执行流程

java

作者其他创作

大纲/内容

① 索引

结构：B+树

定义

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

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

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

索引容量

SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; 16kb

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

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

与B树区别

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

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

数据存储位置

innodb（聚集索引）

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

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

索引结构

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

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

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

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

一致性

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

节省存储空间

非主键索引，需要回表

MyISAM（非聚集索引）

索引结构

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

索引在MYI文件

Hash索引

联合索引

最左前缀原理

② Explain详解与索引最佳实践

Explain介绍

变种

explain extended

explain partitions

Explain分析

explain select * from actor;

Explain列介绍

select_type

simple

primary

subquery

derived

union

table

type

NULL

const, system

eq_ref

ref

range

index

ALL

possible_keys

key

key_len

key_len计算规则

ref

rows

Extra

Using index

Using where

Using index condition

Using temporary

Using filesort

Select tables optimized away

索引最佳实践

全值匹配

最左前缀法则

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

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

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

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

is null,is not null 一般情况下也无法使用索引

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

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

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

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

范围查询优化

总结

③ 索引优化

示例

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

强制走索引

覆盖索引优化

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

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

trace工具使用

set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace

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

SELECT * FROM information_schema.OPTIMIZER_TRACE;

Order by与Group by优化

示例

Using filesort文件排序原理

方式

单路排序

双路排序

示例

单路排序过程

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

多路排序过程

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

索引设计原理

代码先行，索引后上

联合索引尽量覆盖条件

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

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

where与order by冲突时优先where

基于慢sql查询做优化

索引设计实战

分页查询优化

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

select * from employees limit 90000,5;
改写成
select * from employees where id > 90000 limit 5;

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

select * from employees ORDER BY name limit 90000,5;
改写成
select * from employees e inner join (select id from employees order by name limit 90000,5) ed
on e.id = ed.id;

Join关联查询优化

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

表关联两种算法

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

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

如何优化

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

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

in和exsits优化

count(*)查询优化

查询mysql自己维护的总行数

show table status

增加数据库计数表

④ 事务隔离级别、锁机制

事务及其ACID属性

原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。

一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规
则都必须应用于事务的修改,以保持数据的完整性。

隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独
立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。

持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

并发事务处理带来的问题

更新丢失(Lost Update)或脏写

脏读（Dirty Reads）

不可重读（Non-Repeatable Reads）

幻读（Phantom Reads）

事务隔离级别

锁

分类

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

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

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

表锁

基本操作

建表语句

CREATE TABLE `mylock`( `id` INT (11) NOT NULL AUTO_INCREMENT, `name` VARCHAR (20) DEFAULT NULL, PRIMARY KEY (`id`) )ENGINE = MYISAM DEFAULT CHARSET=utf8; INSERT INTO mylock (id,NAME) VALUE (1,'a'); INSERT INTO mylock (id,NAME) VALUE (2,'b'); INSERT INTO mylock (id,NAME) VALUE (3,'c'); INSERT INTO mylock (id,NAME) VALUE (4,'d');

手动增加表锁

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；

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

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

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

这个时候，B提交事务。

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

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

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

幻读

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

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

间隙锁

示例

临键锁(Next-key Locks)

示例

行锁分析

SHOW STATUS LIKE 'innodb_row_lock%';

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

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

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

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

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

查看INFORMATION_SCHEMA系统库锁相关数据

SELECT * FROM information_schema.`INNODB_TRX`;

查看事务

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

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

死锁

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ；

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;

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

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

锁优化建议

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

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

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

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

尽可能低级别事务隔离

⑤ MVCC(Muti-Version Concurrency Control)

分库分表

基础

方式

垂直分片

水平分片（标准方案）

分片策略

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

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

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

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

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

缺点

事务一致性问题

跨节点关联查询问题

跨节点分页、排序函数

主键避重问题

公共表处理

运维工作量

什么时候需要分库分表

mysql单表记录达到500w

单表容量达到2GB

数据预估

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

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

组件

shardingsphere

mycat

DBLE

shardingsphere

核心概念

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

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

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

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

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

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

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

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

mysql主从

主从同步原理

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

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

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

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

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

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

必要条件

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

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

配置 my.cnf

主库主要配置

指定serverid：server-id=47

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

从库主要配置

指定serverid：server-id=48

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

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

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

需要登录后设置

主库

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

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

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

File

当前日志的binlog文件

Position

文件中的索引

Binlog_Do_DB

需要记录binlog文件的库

Binlog_Ignore_DB

不需要记录binlog文件的库

从库

设置同步主节点

CHANGE MASTER TO MASTER_HOST='192.168.232.128', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='root', MASTER_LOG_FILE='master-bin.000004', #这个需要和主库的File同步 MASTER_LOG_POS=156，# 这个需要和主库的Position同步 GET_MASTER_PUBLIC_KEY=1;

开启slave

start slave;

查看主从同步状态

show slave status;

常见错误

如果从库 Slave_SQL_Running=no

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

STOP SLAVE; CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000007',MASTER_LOG_POS=0; # pos一定写0 START SLAVE;

部分同步

主库配置my.cnf

#需要同步的二进制数据库名 binlog-do-db=masterdemo #只保留7天的二进制日志，以防磁盘被日志占满(可选) expire-logs-days = 7 #不备份的数据库 binlog-ignore-db=information_schema binlog-ignore-db=performation_schema binlog-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_dict replicate-wild-do-table=masterdemo01.t_num

读写分离配置

设置从服务只读

set global read_only=1;

基于GTID搭建

主服务配置my.cnf

gtid_mode=on enforce_gtid_consistency=on log_bin=on server_id=单独设置一个 binlog_format=row

从服务配置my.cnf

gtid_mode=on enforce_gtid_consistency=on log_slave_updates=1 server_id=单独设置一个

集群扩容

新加从服务

复制主节点历史数据到从节点

mysqldump -u root -p --all-databases > backup.sql