首页  思维导图  详情

MySQL

2023-01-09 01:57:02   11  举报





仅支持查看

AI智能生成

学习思维导图

学习笔记

作者其他创作

大纲/内容

索引

使用索引的目的

需要在磁盘中快速找到定位查找的记录，所以需要维护一组数据结构来进行快速定位到查询的记录

查找相关算法

有序

二分查找

排序二叉树

红黑树(平衡排序树)

B树

B+树

无序

暴力查找——ONE BY ONE

Hash结构

数组

数组 + 链表

索引的概念

为了高效查询（排好序）的数据结构

常见索引的结构

二叉树

存在单边增长的数据导致查询效率降低

红黑树

通过平衡二叉树的特点解决了单边增长的问题

但是当数据量较大时查询效率还是较低

B树

每个节点页可以存储多个数据，从而减小了树的高度

节点页大小16KB

SHOW GLOBAL STATUS LIKE 'Innodb_page_size'

主键的一般使用bigInt类型占 8byte

节点页的指针大小为 6 btye

叶子节点中数据大小约为 1kb

B+树

数据挂载在叶子节点

非叶子节点只存储索引来提升节点页存放索引个数

叶子节点通过双向链表连接，提高空间访问效率（方便进行范围查找）

根节点可能长期保存在内存中，提高查询效率

Hash表

仅通过一次hash运算就可定位到存储位置（很多时候效率会高于B+树查询效率）

存在Hash冲突

仅支持等值相关的运算，不支持范围查询

索引的分类

聚集索引

索引结构和数据存放在一起的索引

InnoDB引擎采用的就是聚集索引

.frm/.idb

非聚集索引

索引结构和数据分开存放的索引

MyISAM引擎采用的就是非聚集索引

.frm/.MYD/.MYI

稠密索引

叶子节点保存的不只是键值，还保存了位于同一行记录里的其他列的信息

InnDB中的主键索引

稀疏索引

叶子节点仅保存了键位信息以及该行数据的地址，有的稀疏索引只保存了键位信息机器主键

MyISAM的所有的索引类型

InnoDB的非主键索引外的其他类型索引

包括

唯一索引

普通索引

叶子节点存储的是主键的值

为什么非主键索引不保存所有数据

节省存储空间

减小数据一致性维护的成本

查询是通过主键到主键索引的B+树进行数据的查找

字段个数

单值索引

尽可能避免单值索引的创建

联合索引

特点

节点内按索引字段顺序进行排列

满足最左前缀匹配

根据业务场景进行分析创建

面试题（key point）

InnoDB使用B+树做索引的优势？(和B树相比)

降低了树的深度，提高了数据查询效率

B+树维护了叶子节点的双向指针，提交空间访问效率

为什么Innodb必须包含主键，并且推荐主键整型自增？

1、为什么要有主键

通过主键来构建一棵包括所有数据B+树，B+树的叶子节点包括所有的数据

如果没有主键，优先选择唯一的字段组织构建索引；如果不存在这样的字段则默认生成一个默认列（虚拟列—行号）来组织构建索引

2、为什么整型自增

整型

方便比较大小

存储空间使用的较小

自增

方便节点的拓展，避免因为插入已有序结构后的节点分裂和树的节点平衡

为什么满足最左前缀匹配才能使用联合索引？

因为InnoDB的索引是一个排好序的数据结构

联合索引是按照定义顺序进行排的结构，所以要按照最左前缀原则来进行使用

Explain详解

explain关键字的使用

5.7版本后

使用explain关键字即可

使用 show warnings 语句查看优化有执行计划的大概的sql语句

5.7版本前

explain extended

filtered列

row*filtered/100可以估算出将要和explain中前一个表进行连接的行数

explain patitons

partitions列

基于分区表的查询

执行计划表字段

select 语据对应的编号

大小与执行顺序

id越大执行的优先级越高

id相同则按照从上到下进行执行

id为NULl最后执行

select_type

select语句的类型

分类

simple

不好含union和子查询的简单语句

primary

复杂查询中的最外层不是from后跟的select语句

subquery

select语句中非from子句中的子查询

derived

跟在from语句后的子查询

from子句中的子查询结果会生成一个临时表（派生表）

union

在union中的第二个以后的所有select语句

table

这一列表示explain的一行正在访问哪个表

当from子句中有子查询时，table列是<derivenN>格式

表示当前查询依赖id=N的查询，于是先执行id=N的查询

当有union时，UNIONRESULT的table列的值为<union1,2>

1和2表示参与union的select行id

type

表示关联类型或访问类型，即如何进行数据的查询

分类

NULL

在优化阶段分解语句，在执行阶段无需使用访问表或索引的时候

比如对索引字段进行一些聚合操作，仅针对索引字段聚合就可以得到结果

const(system)

const

常量级别

语句优化后某些部分可以优化成为常量

一般是针对主键索引和唯一索引的等值比较时

表中只能找到一条记录，读取一次，速度较快

system

const的一种特例

如果数据表中只有一条记录时查询的级别就会是system

eq_ref

使用主键索引或唯一索引进行连接查询时所有部分被连接使用（即查询结果集中只会返回一条符合条件的记录）

所有部分指的是使用到了索引中的所有字段

不会出现在简单的查询中

ref

使用普通索引，或者联合的唯一性的索引部分前缀进行查询

即在进行比较后查询出包含多条数据的结果集

range

通常出现在in()、between、> 、<、>=、<=等范围查询的语句中

index

通过扫描全索引来进行数据查询

eq_ref、ref和range都是通过根节点进行树的查找方式来进行查询

但是index其实是直接从叶子节点中的第一个记录开始进行数据的查找，有点类似与链表的数据查找

一般使用的是二级索引

二级索引的创建只使用到的记录中的部分数据（创建索引的字段和主键），所以二级索引较小，加载到内存中进行查找效率更高

一般是使用二级索引找到主键，在回表到主键构建的主键索引的B+树中进行数据查找

all

全表扫描

扫描聚簇索引中的所有叶子节点

一般这种需要使用查询字段添加索引来优化到index级别

但是当数据量较小时可能使用二级索引的效率不如全表扫描的效率高

二级索引涉及到回表操作

回表可能就涉及到频繁IO操作

system>const>eq_ref>ref>range>index>ALL

最好保证到range级别

最好达到ref以上的级别

possible_key

查询时可能会使用到的索引

但是具体最后是否会使用索引，要看MySQL底层优化器的选择，是否使用了索引以key的内容为主

只是说明当前sql语句的执行时可以使用索引的

key

mysql进行成本计算和优化后实际会使用到的索引

如果查询条件中字段有索引但是执行计划中key是NULL表示不用索引的话
（不建议使用强制或者忽略索引语句）

使用force index 强制使用索引

使用ignore index 忽略索引

key_len

索引使用的字节数，可以算出联合索引中到底使用那些字段

计算原则

字符串

utf8中字符数字和字母占一个字节，汉字占3个字节

char(n)

n字节

3n字节

varchar(n)

n+2字节

3n+2字节

两个字节来存储字符串长度

数值类型

tinyint

1字节

smallint

2字节

int

4字节

bigint

8字节

时间类型

date

3字节

timestamp

4字节

datetime

8字节

NULL

1字节

如果字段允许为空，需要一个字节的大小来存储是否为NULL

索引最大长度为768字节，如果字符串过长则会进行一个类似最左前缀的匹配来进行检索

ref

使用索引查询时使用的到列字段名或者常量const(等值查询时)

rows

估计要读取并检索的行数

Extra

额外信息

常用的值

Using index

使用索引

表示查询时使用的时索引

既包括根节点查询的过程

也包括叶子节点遍历查询的过程

【覆盖索引】

select中查询的字段在索引中都可以获得，这样的时候我们说查询使用到了覆盖索引

一般时针对于二级索引，不需要通过回表来进行查询相应数据

Using where

使用where语句来处理，而且查询的字段未被索引覆盖

一般type都会是ALL

Using index condition

查询的列不完全被覆盖，where条件是联合索引中的一部分

Using temporary

查询过程中是需要从临时表中进行查找

比如使用distinct进行去重操作

这样需要通过给相对应的字段新增索引来进行优化

Using filesort

在排序中使用外部进行排序，则将数据全部找出来在进行排序

这种情况要考虑到添加索引来优化

索引本身就是一个排好序的结构

Select tables optimized away

使用某些聚集函数来放访问存在索引中的某个字段

索引使用实践

联合索引的使用要满足最左前缀原则

尽可能写sql语句时就按顺序书写条件，避免Mysql的优化阶段的工作量

设计联合索引时按照业务实际来设计

字段

字段的顺序

联合索引中范围条件右侧列的字段不会使用

索引字段上做任何操作导致索引失效，使用全表进行扫描

操作类型

计算

聚合函数

类型转化

优化原则

使用索引字段对应的类型进行匹配

将函数表示的含义通过业务含义来进行规避

尽量使用覆盖索引，避免回表操作

业务需要哪些字段查询哪些字段

减少select * 的使用

!=、not in、not exists有的时候无法使用索引导致全表扫描

但数据量不大时可能范围查询也不会使用索引，因为可能进行全表扫描效率更高

优化范围查询没有使用索引时可以通过缩小查询范围的方式来使其走索引

like使用通配符开头时会导致索引失效

可以使用覆盖索引来进行优化，如果查询的数据字段都在索引树中则可以通过直接遍历字节节点来进行查找

底层执行原理

MySQL的执行原理

客户端（client）

服务层（service）

连接器

功能

和客户端进行连接的建立

获取权限

维持和管理连接

过程

连接成功后会维护一个用户连接的session，session中维护这个用户的相关权限信息

在连接使用的过程中执行语句的鉴权都是通过维护的session进行比较

所以连接创建后root用户修改权限后不会对当前连接造成影响，除非重新创建连接

相关设置查看

当前连接的状态查看

show processlis

用户权限查看

show grants for root@"%";

默认连接时长查看

show global variables like "wait_timeout";

set global wait_timeout=28800; 设置全局服务器关闭非交互连接之前等待活动的秒数

连接类型

短链接

连接时间短

断开后再次查询时会再创建一个连接

长连接

默认连接时长为8小时

生产开发过程中通常使用的是长连接

长连接通常放在Pool中进行管理，但是长连接会导致Mysql的内存增长较快导致内存使用较快

定期断开长连接

当连接占用内存大后，要断开后，下次使用再重连

Mysql5.7版本以上，再执行一个较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源

这个过程不需要重连和重新做权限验证

即恢复到刚刚创建完的状态

查询相关部分

查询缓存

缓存形式

类似与 key-value的格式来存储

key

对应的sql语句

value

结果集

缓存的使用

5.8版本后取消了service层的缓存

5.7和以前版本

需要在my.cnf配置文件中进行参数配置

query_cache_type

关闭缓存

开启缓存

使用 SQL_CACHE 来进行缓存的记录

set global query_cache_size=0;
set global query_cache_type=0;

查看当前Mysql实例是否开启缓存机制

show global variables like "%query_cache_type%";

运用范围

当数据更新后，缓存就会被清空，所以对于频繁更新数据的表来说，缓存时没有太大的存在意义的

所以，通常不会频繁跟新的表（系统配置表、字典表）是可以开启缓存的

show global variables like "%query_cache_type%";

监控缓存相关的参数

Qcache_free_blocks

表示查询缓存中目前还有多少剩余的blocks

Qcache_free_memory

查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用

Qcache_hits

表示有多少次命中缓存

Qcache_inserts

表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中

Qcache_lowmem_prunes

该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。

Qcache_not_cached

表示因为query_cache_type的设置而没有被缓存的查询数量

Qcache_queries_in_cache

当前缓存中缓存的查询数量

Qcache_total_blocks

当前缓存的block数量

语句查询

词法分析器

作用

校验sql语句是否合法，是否有语法格式错误

步骤

词法分析

语法分析

语义分析

构造执行树

生成执行计划

计划的执行

执行树的作用

提取分析构造出的树中的关键数据

作用

分库分表中的路由键的获取用于路由策略中使用

分布式事务中子事务执行失败后补偿机制的相关数据的提取

执行过程

优化器

作用

可使用多索引时，决定使用哪个索引

关联查询时，决定各表的连接顺序（小表驱动大表）

执行器

过程

权限的查看

使用对应引擎的的执行接口

引擎层（store）

负责数据的存储和提前

使用的是插拔式设计，支撑多种存储引擎

MySQL5.5.5版本默认使用InnoDB引擎

流程分析

binlog归档

作用

记录数据表的变动相关操作

用户进行数据的恢复

binlog日志的开启

进行my.cnf文件的配置

配置参数

log-bin=/usr/local/mysql/data/binlog/mysql-bin

日志的开启和存放位置的设置

server-id=123454

自定义,保证唯一性

注意5.7以及更高版本需要配置本项

binlog-format=ROW

含义：记录的格式

分类

statement

记录当前语句

优劣

占用资源小，效率较高

但是进行主从同步时可能会有数据不一致问题的发生

主从库可能使用不同的索引导致数据更新不一致

row

记录当前语句的执行结果

优劣

使用的资源较多

但是可以保证主从库的数据同步安全，数据一致

mixed

statement + row混合模式

当使用statement时不会出现数据不同步问题时使用statement

反之使用 row

binlog相关命令

show variables like '%log_bin%'; 查看bin-log是否开启

flush logs; 会多一个最新的bin-log日志

show master status; 查看最后一个bin-log日志的相关信息

reset master; 清空所有的bin-log日志

/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 ;查看binlog内容

通过binlog进行数据恢复

恢复全部数据

/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库名)

恢复指定位置数据

/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position="408" --stop-position="731" /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库)

恢复指定时间段数据

/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 --stop-date= "2018-03-02 12:00:00" --start-date= "2019-03-02 11:55:00"|mysql -uroot -p test(数据库)

索引优化

查询的一些特例解析

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

可能使用索引的范围找到的结果集较大，还涉及到回表操作导致了效率的降低

强制使用索引

force index

一般不建议使用

但是可以通过使用覆盖索引来进行优化

in 和 or 在不同数据量下执行计划不同

数据量较大

使用索引

因为在大数据的基础上做全表的操作来说，将语句拆分成等值的索引操作在回表的效率可能回提升

数据量较小

使用索引时的回表效率较低导致不如全表的效率

LIKE KK% 一般会使用索引

使用到了索引下推（Index Condition PushDown）

原理

当联合索引中查询字段有使用后模糊like查询时，会使用like中确定部分进行匹配，并且也会使用后续的字段进行查找，找到所有满足条件的id,在进行回表操作

使用like匹配字段以及其顺序后的字段进行筛选，可以一定程度上的减少查询到的id个数，从而减少回表次数

MySQL5.6以前不会进行索引下推，找到所有使用到Like条件前的所有结果id，回表找找到所有的记录，再进行like字段后的字段条件匹配

索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引

【总结】具体是否使用索引还是全表要注意几点

数据量

回表操作的次数

查询字段是否可以被索引进行覆盖

是否满足索引树构造的结构

trace工具的使用

使用

set session optimizer_trace="enabled=on",end_markers_in_json=on;

开启trace工具

执行sql语句

SELECT * FROM information_schema.OPTIMIZER_TRACE;

查询trace字段

set session optimizer_trace="enabled=off";

关闭trace

关键内容

join_preparation

第一阶段：SQL准备阶段，格式化sql

join_optimization

--第二阶段：SQL优化阶段

具体

condition_processing

条件处理

table_dependencies

表依赖详情

rows_estimation

预估表的访问成本

具体

range_analysis

table_scan
全表扫描情况

rows

扫描行数

cost

查询成本

potential_range_indexes
可能使用的索引

analyzing_range_alternatives
分析各个索引使用成本

ranges

索引范围

rowid_ordered

使用该索引获取的记录是否按照主键排序

index_only

是否使用覆盖索引

rows

索引扫描行数

cost

索引使用成本

chosen

是否使用索引

considered_execution_plans

最终执行计划

具体

table

数据表

considered_access_paths

最终选择路径

rows_to_scan

扫描行数

access_type

访问类型

resulting_rows

结果行数

cost

成本

chosen

确定选择

use_tmp_table

是否使用临时表

join_execution

第三阶段：SQL执行阶段

常见深入优化

Order By 和 Group by的优化

排序的方式

using index（效率较高）

使用MySQL扫描的索引本身

using filesort（效率较低）

使用文件排序，即将数据load到内存中进行文件排序

文件排序

分类

单路排序

是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；

排序后的结果就我我们想要的结果集

trace工具的标识为【< sort_key, additional_fields >】/【< sort_key, packed_additional_fields >】

多路排序
（回表排序）

是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段

sort buffer排序后的id结果集需要在进行回表才能得到我们想要的结果集

trace工具的标识为【< sort_key, rowid >】

判断依据

MySQL 通过比较系统变量 max_length_for_sort_data进行判断

不建议进行调整

默认大小为：1024字节

大于

多路排序

小于等于

单例排序

当需要加载到sort buffer记录字段较多时，则使用多路排序，只将唯一标识id和排序字段加入到sort buffer中进行比较

不会使用索引的情况

非wher条件中等值排序字段不能跳跃

非where条件中等值排序字段不能颠倒顺序

where条件使用 in

不使用覆盖索引的where条件使用了范围查询

优化方式

尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则

order by语句使用索引最左前列

使用where子句与order by子句条件列组合满足索引最左前列

能用覆盖索引尽量用覆盖索引

分组group是基于排序的，实质就是先排序后分组

遵照索引创建顺序的最左前缀法则

group by的优化如果不需要排序的可以加上order by null禁止排序

避免使用having，将条件在where条件中进行指定

分页查询优化

自增连续主键排序的分页

如果sql语句中没有指定排序字段，则默认使用主键进行排序

使用自增连续主键排序的使用可以将limit start rownumbers 的格式优化成捅过主键范围后的limt rownumbers

select * from table limit start rownumber

优化到

select * from table where id > start limit rownumber

原写法全表进行查找

优化后使用主键索引

如果数据记录有删除操作则不能使用该方法进行优化，会导致数据集不正确

非主键排序分页

优化原理

先使用索引进行排序后找到对应主键，在捅过主键在回表来进行结果集的查询

案例

employee表

字段

name

age

position

索引

name_age_posisiton

语句

select * from employee order by name limit 1000,5

执行计划使用ALL进行全表扫描

优化思想

子查询使用索引的排序特性查询到排序后对应分页的数据id

通过id关联表来进行表关联

通小表驱动大表来在全表中使用id主键索引

select * from employee e inner join (select id form employee order by name limit 1000,5 ) ed on e.id=ed.id

子查询使用二级覆盖索引查询到id

nam_age_posistion

两个表通过id字段进行关联

通过id使用主键索引查询数据

PRIMARY

Join关联字段优化

连接算法
【大表10000条数据】
【小表100条数据】

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

从驱动表中一次取出一行数据，找到这行数据的关联字段，使用字段在被驱动表中取出满足条件的数据，然后取出两张表的结果集合

使用NLJ算法，是执行计划的Extra中不会出现 Using join buffer

一般两张表中的关联字段都为索引字段时，会使用NLJ算法

连接表分类

inner join

优化器会将需要进行关联的小表作为小表（驱动表）

另一张表就会成为大表（被驱动表）

所以inner join 中排在前面的表不一定为驱动表

left join

左表为驱动表

右表为被驱动表

优化器不会进行自己优化，所以使用是要注意表的大小和位置

right join

左表为被驱动表

右表为驱动表

优化器不会进行自己优化，所以使用是要注意表的大小和位置

大概过程

1、从驱动表(经过where条件筛选)数据中取出一行数据

2、从第一步中的数据，取出关联字段，到被驱动表中进行查找

3、取出被驱动表中找到对应的数据，取出的数据和驱动表中的数据进行合并，返回给客户端

4、重复前三步的操作

算法效率

扫描行数

10000 + 100

小表逐行扫描

大表通过索引进行扫描

判断次数

10000

通过索引在大表中进行判断匹配

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

把驱动表的数据读取到join_buffer中，让后扫描被驱动表，把驱动表中的每行读取出来和join_buffer数据进行标胶

使用BLJ算法的执行计划中的Extra中会有 Using join buffer(Block Nested Loop)

通常情况是关联的字段不是索引字段会使用过这种算法进行关联

大概过程

1、驱动表将数据放在join_bufferz中（以buffer大小进行分批）

join_buffer的默认大小时256K，不建议自己进行修改

2、把被驱动表的每行数据取出，和join_buffer中是数据进行比较

3、返回满足join条件的数据

4、驱动表较大时分批重复上述三个步骤

算法效率

扫描行数

100 + n*10000

驱动表行数 + 分批每次都会扫描一次被驱动表

判断次数

100 * 10000

表中每行都要进行两两比较

为什么使用BLJ时不使用NLJ算法

如果关联字段没有索引，则都需要扫描全表进行比较，所以扫描行数的就是 100 * 10000

关联查询的优化

关联字段添加索引，让大表走索引

小表驱动大表
【经过条件过滤后的数据量】

小表作为驱动表

explain中先执行的表

大表作为被驱动表

explain中后执行的表

explain 中小表先执行，查出对应连接的数据，再通过索引在大表中查询

如果执行计划有问题，可以自己指定驱动表

straight_join

使用左表驱动右表

只针对inner join有效

尽可能减少关联

in 和 exist 的优化

优化原则：使用小表来驱动大表

select * from A where id in (select id from B)

#等价于：
for(select id from B){
select * from A where A.id = B.id
}

B表小于A表时，in优于exist

exsist

select * from A where exists (select 1 from B where B.id = A.id)

#等价于:
for(select * from A){
select * from B where B.id = A.id
}

A表和B表关联字段要添加索引

当A表的数据集小于B表的数据集时，exists优于in

使用方法

EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别

EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比

EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

count查询优化

日常使用方式

count(1)

找到对应的数据后直接对结果做累加

count(id)

5.7版本做了优化，如果count的字段有索引，就是使用的count(id)也会优化成为使用二级索引来进行计算

count(字段)

该方式统计时不会统计null的数据行

通过索引找到数据后会加载到内存中，在进行计数操作，所以效率低于count(1)

count(*)

MySQL底层做了优化按行进行累加，不会取值，效率较高

效率

字段有索引

count(*)≈count(1)>count(字段)>count(主键 id)

count(字段)会走二级索引，所以效率比使用主键效率高

字段没有索引

count(*)≈count(1)>count(主键 id)>count(字段)

count(字段)无法走索引，所以效率就低于使用主键索引的count(id)

常见优化方法

查询mysql自己维护的数据

myisam会维护数据，无需计算

innoDB由于MVCC机制，count需要实时计算

show table status 获取大致的数据行数

将数据存储到redis中

使用redis中的incr或decr命令计数

但是这种方式不准，很难保证表操作和redis操作事务一致性

双写一致维护成本高

使用数据额外表进行计数，使用数据库事务进行维护

MySQL底层对查询SQL进行的优化

join中的驱动表和被驱动表的选择

联合索引字段顺序的优化

联合索引查询时的索引下推

二级索引查询优化——自适应哈希索引

二级索引回表前的排序MRR

索引设计的原则

代码先行，索引后上

索引的设计要贴合业务的需求来进行设计

避免盲目的创建过多冗余的索引，从而提升索引维护的成本

联合索引尽量的覆盖条件

尽量减少单值索引的创建

即要考虑到where条件中的字段还用注意到order group 的字段

当索引字段where和order/group冲突是优先考虑where

避免在小基数字段创建索引

字段的数据的离散度较低会大大降低索引的效率

时长针对慢sql做索引的优化

业务系统使用初期和使用一段时间后的数据量是有较大改变的，索引的执行可能也会发生变化

所以要时长监控慢sql，针对慢sql来进行索引的优化

MySQL慢查询

概念：用来记录超过响应阈值的SQL语句

条件：通过 long_query_time 值来判断是否会被记录

默认long_query_time是10秒

参数配置

slow_query_log

开启

关闭

log-slow-queries

旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。

slow_query_log_file

新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。

long_query_time

慢查询阈值，当查询时间多于设定的阈值时，记录日志。（大于时记录，不包括等于）

log_queries_not_using_indexes

未使用索引的查询也被记录到慢查询日志中（可选项）。

log_output

日志存储方式

方式

'FILE'表示将日志存入文件

默认

推荐优先使用

'TABLE'表示将日志存入数据库。

写入到mysql.slow_log表中

log_out='FILE,TABLE'

混合使用

配置

show variables like '%slow_query_log%';

查看慢日志相关信息

slow_query_log

slow_query_log_file

set global slow_query_log=1

设置当前数据库有效，重启后失效

永久失效需要在my.cnf中进行配置

show variables like 'long_query_time%';

查看响应阈值

set global long_query_time=4;

设置阈值，但是重新开启新的会话才会生效

show variables like 'log_queries_not_using_indexes';

查看是否开启了非索引查询的记录

set global log_queries_not_using_indexes=1;

使用full index scan的SQL也会被记录到慢查询日志。

未使用索引的查询也被记录到慢查询日志中

show global status like '%Slow_queries%';

查询慢sql的条数

长字符串使用前缀进行索引的创建

长字符串做索引会耗费大部分磁盘空间

使用前缀可以减少磁盘空间的占用

但是只使用前缀会导致order 和 group 没法使用索引

阿里MySQL规范

设计规约

单行数据超过500万行或者容量大于2G时建议进行分库分表

业务的唯一字段，即使时组合字段也创建唯一索引

禁止超过3张表以上的关联查询，多表设计和优化索引起来比较复杂

这种多表联合建议在Java层级进行业务的拆分，使用Java层级的业务拆分如果出现了并发瓶颈问题可以通过扩容和并发来解决，但是MySQL处理难度较大

varchar字段创建索引要指定长度，根据业务进行长度设置

要避免左模糊和全模糊查询

避免使用in关键字，使用的话in集合大小在1000以内

字段类型选择

原则

确定合适的大类型

数字、字符串、时间、二进制

具体类型

有无符号、取值范围、定变长

类型注意事项

数值类型

确定有无符号可以增加可表示范围

确定取值范围，尽可能选择就满足条件的小类型

括号后的参数只显示长度

配合ZEROFILL使用

使用0进行填充

日期和时间

DATETIME

8个字节

范围大

TIMESTAMP

4个字节

范围到2038年

字符串

字符串的长度相差较大用VARCHAR；

字符串短，且所有值都接近一个长度用CHAR

尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联

事务隔离级别和锁机制

事务隔离级别

事务

概念

由一组SQL语句组成的逻辑处理单元

特性【ACID】

原子性
Atomicity

操作层面来说，一组操作同时成功或者失败

一致性
Consistent

数据层面来说，一组SQL影响的数据的状态要保持一致，以保持数据的完整性

隔离性
Isolation

数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。

一个事务的操作不影响其他事务中的数据

持久性
Durable

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

并发处理事务带来的问题

脏写（更新丢失）

后面执行的更新操作导致了覆盖了其他事务进行的更新操作

解决

不在业务层级完成与原数据有关的更新操作，将这些操作放在数据库的层级来做

使用乐观锁的思想，在更新前进行版本号的比对

脏读

事务A读取到了事务B已经修改但是没有提交的数据

如果A使用了没有提交的数据做了操作进行了数据更新

B事务发生了回滚，那么其实A使用的数据就是一个脏数据，使用脏数据做的运算也就没有意义了

不可重复读

事务A内部的相同查询语句在不同时刻读取的结果不一致，不符合隔离性

针对与update操作

幻读

事务A读取到了事务B提交的新增数据，不符合隔离性

针对于insert和delete操作

事务的隔离级别

读未提交、读已提交、可重复度、可串行化

show variables like 'tx_isolation';

查看使用隔离级别

设置隔离级别

set tx_isolation='read-uncommitted';

set tx_isolation='read-committed';

set tx_isolation='repeatable-read';

set tx_isolation='serializable';

MySQL默认的隔离级别是可重复度（REPEATABLE-READ）

为了解决并发的事务问题才出现了事务的隔离级别这么一说，不同的事务隔离级别可以解决相应的问题

事务优化

将查询等数据准备操作放在事务之外

事务中避免远程调用，远程调用要设置超时时间，防止造成大事务的情况

事务中避免一次性处理过多的数据量，将数据进行拆分多事务进行

更新等涉及加锁的操作尽可能放在事务靠后的位置

能异步处理的尽量异步处理，异步处理也要做超时日志

通过应用侧（业务代码）保证数据一致性，可非事务执行

在业务代码中的try...catach进行业务补充

业务逻辑复杂不推荐使用

锁机制

概念

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

数据库的锁机制就可以保证不同的隔离级别，换句话说事务的隔离级别就是通过锁机制来实现的

锁的分类

性能

乐观锁

使用版本控制来实现

比如在数据表中增加版本字段实现

MVCC

悲观锁

通过排他性来保证

粒度

表锁

每次操作锁住整张表

开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

一般用在整表数据迁移的场景

操作

lock table 表名称 read(write),表名称2 read(write);

手动增加表锁

show open tables;

查看表上加过的锁

unlock tables;

删除表锁

存在于MyISAM、InnoDB引擎中；而且MyISAM只有这种锁,不支持事务

行锁

每次操作锁住一行数据

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高

行锁是加在索引之上的，如果对非索引行做更新时，行锁会升级为表锁

页锁

每次锁定相邻的一组记录

锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般

应用在BDB 存储引擎中

BDB引擎

操作类型
（都属于悲观锁）

读锁（共享锁，S锁）

针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁（排他锁，X锁）

当前写操作没有完成前，它会阻断其他写锁和读锁

数据修改操作都会加写锁

意向锁（I锁，针对表锁）

主要是为了提高加表锁的效率

当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了

其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁

分类

意向共享锁，IS锁

对整个表加共享锁之前，需要先获取到意向共享锁

意向排他锁，IX锁

对整个表加排他锁之前，需要先获取到意向排他锁

引擎中的锁

MyISAM

执行查询语句SELECT前，会自动给涉及的所有表加读锁

在执行update、insert、delete操作会自动给涉及的表加写锁

InnoDB

在执行查询语句SELECT时(非串行隔离级别)，不会加锁

在执行update、insert、delete操作会加行锁。

事务隔离级别的实现
（针对InnoDB引擎）

读已提交和可重复读

通过MVCC机制保证的，MVCC实际的设计思路就是一种乐观锁的设计

可以有效的解决脏读、不可重复读

通过间隙锁可以部分解决幻读的问题

间隙锁
Gap Lock

根据id字段当前已有的值做出相应等多个范围

查询的id落在对应的区间，则对应的区间范围的id行都会都会进行加锁，不能进行写操作

间隙锁+区间右行的行锁成为临键锁

串行化

通过读写锁以及偏向锁来（即互斥锁）实现了脏读、不可重复读和幻读的问题

这些悲观锁就导致了并发效率较低，所以一般不会使用这种隔离级别

优化建议

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

无索引行锁会升级为表锁

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁

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

尽量控制事务大小

减少锁定资源量和时间长度

涉及事务加锁的sql尽量放在事务最后执行

涉及事务相关的业务SQL尽可能使用数据库中数据进行运算

读已提交虽然读出单数据是保持一致的

但是做写操作时会使用数据库中最新的数据进行运算

尽可能低级别事务隔离

事务分析和运维

行锁的分析

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;

等待锁查看

select * from INFORMATION_SCHEMA.INNODB_LOCKS;

锁查看

select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

锁等待详细信息

show engine innodb status\G;

释放锁

kill trx_mysql_thread_id

运维

查看锁等待详细信息中发现了死锁deadlock相关的信息

在INFORMATION_SCHEMA.INNODB_TRX表找到事务对应的线程ID trx_mysql_thread_id

通过kill 线程id的方式解决死锁

一般简单的死锁出现MySQL自己就会处理，将相互依赖的事务中的锁进行提交，解决死锁问题

MVCC和BufferPool缓存机制

MVCC

Multi-version Concurrency Control：多版本并发控制机制

其中相关概念

undo日志

为了事务操作失败而需要进行回滚而进行数据记录成为undo日志

undo日志版本链

指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志

通过两个隐藏字trx_id和roll_pointer进行undo日志串联起来形成版本链

trx_id

更新所处的事务id

roll_pointer

指向上一个版本的undo日志

结构

read-view（查询）一致性视图

生成时间

可重复读

事务执行了第一条查询语句就会生成

读已提交

每次进行查询之前

组成

当前未提交事务id的数组

数组中最小事务id
（即未提交事务的最小id）

min_trx_id

当前最大的事务id

max_trx_id

隔离级别区别

可重复读

执行任何sql生成的一致性视图，在该事务结束之前不会改变

读已提交

执行完成一条sql前会生成最新的read-view视图

事务的起点
(生成事务id)

一个事务的起点不是从begin/start transaction开始的，当事务中执行第一个写语句，事务才真正的开始，才会像mysql来申请事务id

mysql内部是严格按照事务的启动顺序来分配事务id的

实现过程

开启事务

第一次执行查询sql时生成本次是事务对应的read-view视图

具体操作

读操作

根据undo日志版本链和当前事务的read-view视图进行数据查找

比较原则

从日志版本链的开头进行比较匹配

当前undo日志维护的trx_id

trx_id<min_trx_id

说明该undo日志对应的版本已提交

数据可见，直接使用数据即可

trx_id>max_trx_id

该版本对应的trx_id是未来的，当前一定没有进行事务提交

数据不可见，通过roll_pointer找到下一个undo日志

min_trx_id <= trx_id <= max_trx_id

当前undo日志维护的trx_id没在未提交事务的事务id集合中则读取相应记录值

当前undo日志维护的trx_id在未提交事务的事务id集合中则通过roll_pointer找到上个版本的undo日志进行重复比较

写操作

对undo日志进行追加

事务提交

总结

MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

所有事务公用一个undo日志，但是每个事务根据不同的隔离级别会有自己独有的read-view视图

BufferPool机制

存储流程

重点梳理

Buffer Pool中是以Page为单位进行数据读取

三个文件

undo日志文件

InnoDB引擎特有的

用户事务回滚恢复Buffer Pool中的数据

InnoDB引擎特有的

是进行顺序操作的

开辟一块连续的空间

操作效率比随机操作效率高得多

redo日志文件

InnoDB引擎特有的

用于恢复Buffer Pool中的丢失数据的

同样也是顺操作的

binlog日志文件

由server层所控制

用户恢复数据库的数据使用

binlog日志文件存储磁盘完成后会给rido日志设置一个标识，标识redo和binlog日志数据的一致

同步标识后进行事务提交

Buffer Pool中更改的数据是由一个专门进行刷盘操作的IO线程执行的，所以具体写回磁盘的时间是不定的

Buffer Pool中的数据不是一成不变的，根据LRU算法进行数据的淘汰

InnoDB引擎底层执行原理

InnoDB引擎底层存储和缓存原理

InnoDB引擎的三大特性

AdaptiveHashIndex

自适应哈希索引

为了提高索引更好的查询数据的效率，而进行对二级索引的优化

查看自适应索引是否打开

show variables like '%ap%hash_index';

说明

经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来

特点

无序，没有树高

降低对二级索引树的频繁访问

缺点

占用InnoDB Buffer Pool 的空间

自适应hash索引只适合搜索等值的查询

极端情况下，自适应hash索引才有比较大的意义，可以降低逻辑读

查询原理

Buffer Pool

缓存池

下面详细说明

Doublewrite Buffer

双写缓冲区

下面详细说明

InnoDB的内存和磁盘结构

内存

Buffer Pool

Log Buffer

AdaptiveHashIndex

磁盘

System Tablespace

InnoBD Data Dictionary

Doublewrite Buffer

Change Buffer

Undo Logs

Undo Tablespaces

Redo Log

File-per-Table Tablespaces

General Tablespaces

Temporary Tablesapces

InnoDB的体系结构
【以页位单位的I/O操作】

表空间

数据存放和空间的管理

记录

每条数据记录

页

B+树中数据存储的基本单元

大小16KB

页号是又4个字节来记录

区

对于16KB的页，连续的64个页组成了一个区

区的默认占用1MB

设置区的目的

更方便的管理页

以区为单位进行磁盘空间的申请

尽可能将随机IO转化为顺序IO

一个区中的页物理地址是连续的

组

256个区组成了一个组

各组最开始的2个页面的类型是固定的，用来登记本组256个区的属性

段

为了更高效的进行分为查询，按是否为叶子节点分为了两个段

段只是一个逻辑上的概念，不一定是一段连续的物理空间

页节点段

存放B+树的叶子节点

非页节点段

存放B+树的非叶子节点

表空间

表空间是一个抽象的概念

每个表空间对应了一个或多个文件