存储引擎
MySQL体系结构
存储引擎介绍
- 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式
- 建表时指定存储引擎
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
…
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ; - 查询当前数据库支持的存储引擎
show engines;
存储引擎特点
InnoDB
- 兼顾高可靠性和高性能的存储引擎,是默认的存储引擎
- DML操作遵循ACID模型,支持事务
- 行级锁,提高并发访问
- 支持外键FOREIGN KEY约束,保证数据的完整性和正确性
- xxx.ibd:每张表都会有这样一个表空间文件,存储该表的表结构、数据和索引
- 逻辑存储结构
MyISAM
- 早期默认的存储引擎
- 不支持事务,不支持外键
- 支持表锁,不支持行锁
- 访问速度快
- xxx.sdi存储表结构信息
- xxx.MYD存储数据
- xxx.MYI存储索引
Memory
- Memory引擎的表数据存储在内存中,由于收到硬件问题或断电问题的影响,只能将这些表作为临时表或缓存使用
- 内存存放
- hash索引
- xxx.sdi存储表结构信息,没有数据信息和索引信息
区别及特点
存储引擎选择
- InnoDB:默认存储引擎,支持事务、外键。对事务完整性要求较高、并发条件下要求数据一致性、数据操作除了插入和删除还有很多更新、删除操作
- MyISAM:以读和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,
- MEMORY:将所有的数据保存在内存中,访问速度快,通常用于临时表的及缓存,缺点是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。
索引
索引概述
- 索引是帮助MySQL高效获取数据的数据结构(有序)
- 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引
索引结构
概述
- MySQL的索引是在存储引擎层实现的,不同的存储引擎层有不同的索引结构
二叉树
- 如果主键是顺序插入,二叉树会退化成单向链表
- 大数据情况下,层级较深,检索速度慢
- 红黑数是自平衡二叉树,但也是一颗二叉树,大数据量情况下,层级较深
B-Tree
- B树是一种多叉路衡二叉树
- 5阶的B树,每一个节点最多存储4个key,对应5个指针
- 一旦节点存储的key数量到达5,就会裂变,中间元素向上分裂
- 在B树中,非叶子节点和叶子节点都会存储数据
B+Tree
所有的数据都会存储在叶子节点
叶子节点形成一个单链表
非叶子节点仅仅起到索引数据作用,具体的数据都是在叶子节点存放的
Hash
- 哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中
- hash索引只能用于对等比较,不支持范围查询
- 无法利用索引完成排序操作
- 查询效率高
- Memory引擎支持hash索引
为什么InnoDB引擎选择使用B+tree索引结构
- 相比于二叉树,层级更少,搜索效率更高
- 对于Btree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,同样保存大量数据,只能增加树的高度,导致性能降低
- 相对于Hash索引,B+tree支持范围匹配及排序操作
索引分类
根据索引的具体类型分
在InnoDB存储引擎中,根据索引形式,可以分为
聚集索引的选取规则
- 如果存在主键,主键索引就是聚集索引
- 如果不存在主键,将使用第一个唯一索引作为聚集索引
- 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引
回表查询:
- 先到二级索引中找到主键值,再到聚集索引中更据主键值,获取数据的方式,就是回表查询
InnoDB主键索引的B+tree高度计算
假设:一行数据大小为1k,一页中可以存储16行这样的数据,InnoDB的指针占用6个字节的空间,主键即使为bigint,占用字节数为8
高度为2:
n * 8 + (n + 1) * 6 = 16 * 1024 算出n约为1170
1171 * 16 = 18736
也就是说,如果树的高度为2,则可以存储18000多条数据
高度为3:
1171 * 1171 * 16 = 21939856
如果树的高度为3,则可以存储2200w左右的记录
索引语法
- 创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,… ) ; - 查看索引
SHOW INDEX FROM table_name ; - 删除索引
DROP INDEX index_name ON table_name ;
SQL性能分析
SQL执行频率
- 通过show [session|global] status 命令可以提供服务器状态信息
- show global status like ‘com_______’;(7个_)可以查看各类sql语句的访问频次
慢查询日志
- 慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位 秒,默认10秒)的所有sql语句的日志
- 慢查询日志默认是关闭的
profile详情
- show profile能够在做SQL优化时帮助我们了解时间都耗费到哪里去了
- select @@have_profiling;能够看到当前Mysql是否支持profile
- set profiling = 1;开启profiling;
- show profiles;查看每一条sql语句耗时的基本情况
- show profile for query query_id;查看指定query_id的sql语句各阶段的耗时情况
explain
索引使用规则
最左前缀法则
- 针对联合索引,查询从索引的最左列开始,并且不跳过索引中的列。如果跳过某一列,索引将会部分失效(后面的字段会失效)
- 查询时,联合索引的最左边的字段必须存在,但和条件编写时的顺序无关
范围查询
- 联合索引中,出现范围查询< > ,范围查询右侧的列索引失效
- 当范围查询使用>= 或 <= 时,走联合索引,此时会范围查询右侧的字段也会走联合索引
索引失效的情况
- 在索引列上进行函数运算等操作,索引将失效
- 字符串不加引号,varchar类型不加引号不会走索引
- 左模糊查询会导致索引失效
- or连接条件:用or分割开的条件,如果or前的条件中的列有索引、而后面的列没有索引、那么涉及的索引都不会被用到。
- 数据分布影响:如果Mysql评估使用索引比全表更慢,则不适用索引。即如果表中绝大部分数据都不满足查询条件,则不会走索引,直接走全表扫描
SQL提示
- 通过添加提示,提示mysql走规定的索引
覆盖索引
- 尽量使用覆盖索引,减少使用select *
- 覆盖索引指 查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到
- Using where;Using Index 查询使用了索引,且需要的数据在索引列中能找到,所以不需要回表查询数据
- Using Index condition 查询使用了索引,但是需要回表查询数据
前缀索引
- 当字段类型为字符串时,有时候需要索引很长的字符串,这会让索引变得很大,查询时浪费大量的磁盘IO,影响查询效率,此时可以只讲字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率
- create index idx_xxx on table_name(cloumn(n));
索引设计原则
- 针对数据量较大,且查询比较频繁的表建立索引
- 针对常作为查询条件、排序、分组操作的字段建立索引
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高
- 如果是字符串字段,字段的长度较长,可以考虑建立前缀索引
- 尽量适合联合索引,减少单列索引,查询时,联合索引可以覆盖索引,节省存储空间,避免回表,提高查询效率
- 要控制索引数量,索引越多维护索引结构的代价也就越大,会影响增删改效率
- 如果索引列不能存储NULL值,在创建表时使用NOT NULL约束它,当优化器知道每列是否包含NULL值时,可以更好的确定哪个索引最有效的用于查询。
SQL优化
insert优化
- 一次性插入多条数据时,可以从以下3个方面进行优化
- 批量插入 insert into table values (), ()
- 手动控制事务
- 主键顺序插入,性能要高于乱序插入
- 一次性需要插入大批量数据(几百万的记录),使用Insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入。
主键优化
- 数据组织方式
- 表数据都是根据主键顺序组织存放的
- 页分裂
- 主键乱序插入时,可能会发生页分裂,影响插入性能
- 页合并
- 当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页,看看是否可以将两个页和并以优化空间使用
- 主键索引设计原则
- 满足业务需求的情况下,尽量降低主键的长度
- 插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键
- 尽量不要使用UUID做主键或是其他自然主键,如身份证
- 业务操作时,避免对主键的修改
Order by优化
- Mysql的排序有两种方式
- Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫做FileSort排序
- Using index:通过有序索引顺序扫描直接返回有序数据,不需要额外的排序,操作效率高
- 可以根据排序顺序自定义索引排序顺序
Group by优化
- 如果没走索引,会使用临时表,走索引则不会使用临时表,性能更好
- 在分组操作时,可以通过索引来提高效率
- 分组操作时,索引的使用也是满足最左前缀法则
Limit优化
- 当数据量比较大时,如果进行limit分页查询,越往后,分页查询效率越低,因为limit本身自带排序,所以性能影响很大
- 优化思路:覆盖索引加子查询的形式进行优化
count优化
- MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高;但如果是带条件的count,也慢
- InnoDB,执行count(*)的时候,需要把数据一行一行的从引擎里面读出来,然后累计计数
- 优化思路:自己维护一个总数’
- count(*) 约等于 count(1) > count(主键id) > count(其他字段)
update优化
- InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会锁全表。
锁
概述
- 锁是计算机协调多个进程并发访问某一资源的机制
- 数据也是一种供多用户共享的资源,如何保证数据并发访问的一致性,有效性是所有数据库必须解决的一个问题
- 锁冲突也是影响数据库并发访问性能的一个重要因素
- 全局锁:锁定数据库中的所有表
- 表级锁:每次操作所著整张表
- 行级锁:每次操作所著对应的行数据
全局锁
- 对整个数据库实例加锁,加锁后整个实例处于只读状态,后续的DML的写语句,DDL语句,和更新操作的事务提交语句都将被阻塞
- 典型使用场景是做全局的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性
- 语法
- 加全局锁:flush tables with read lock;
- 数据备份:mysqldump -uroot -p123456 database > dabasase.sql
- 释放锁:unlock tables;
- 特点
- 如果是在主库上备份,备份期间都不能执行更新,业务基本上就得停摆
- 如果在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志,会导致主从延迟
表级锁
介绍
- 每次操作锁住整张表,锁粒度大,发生锁冲突的概率最高,并发度最低,在MyISAM,InnoDB,BDB等存储引擎中
表锁
- 表共享读锁(read lock)
- 表独占写锁(read lock)
- 语法:
- 加锁:lock tables 表名 … read / write
- 释放锁:unlock tables /客户端断开连接
元数据锁
-
meta data lock 元数据锁,MDL
-
系统自动控制,无需显示使用,在访问一张表时会自动加上,
-
主要作用是维护表元数据的数据一致性,在表上有活动事务时,不可以对元数据进行写入操作,避免DML 和DDL冲突,保证读写正确性
-
查看数据库中元数据锁的情况
select object_type,object_schema,object_name,lock_type,lock_duration from
performance_schema.metadata_locks ;
意向锁
- 为了避免DML在执行时,加的行锁和表锁冲突,在InnoDB中引入了意向锁,使用表锁不用检查每行数据是否加锁,使用意向锁来减少表锁的检查
- 意向共享锁(IS):由语句select … lock in share mode添加,与表锁共享锁兼容,与表锁排他锁互斥
- 意向排他锁(IX):由insert、update,delete,select … for update添加,与表锁共享锁和排他锁都互斥,意向锁之间不会互斥。一旦事务提交,意向共享锁、意向排他锁都会自动释放
- 查看意向锁及行锁加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;
行级锁
介绍
- 每次操作锁住对应的行数据,锁粒度最小,并发度最高
- 行锁是对索引上的索引项加锁来实现的,而不是对记录加锁
行锁
- 共享锁(S):允许一个事务读一行,阻止其他事务获得相同数据集的排他锁
- 排他锁(X):允许获取排他锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排他锁
加锁规则
- InnoDB使用next-key锁进行搜索和索引扫描
- 针对唯一索引进行检索,对已存在的记录进行等值匹配时,将会自动优化为行锁
- 不通过索引条件检索数据,那么会对表中所有记录加锁,此时会升级成表锁
间隙锁& 临建锁
- 索引上的等值查询(唯一索引)给不存在的记录加锁时,优化为间隙锁
- 索引上的等值查询(非唯一普通索引),向右遍历时最后一个值不满足查询需求时,next-key退化成间隙锁
- 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。
InnoDB引擎
逻辑存储结构
表空间
- InnoDB存储引擎逻辑结构的最高层,如果用户启用了参数 innodb_file_per_table,则在每张表都会有一个表空间(xxx.ibd)一个mysql实例可以对应多个表空间,用于存储记录、索引等数据
段
- 段分为数据段、索引段、回滚段
- InnoDB是索引组织表,数据段就是B+树的叶子节点,索引段就是B+树的非叶子节点,段用来管理多个Extent(区)
区
- 表空间的单元结构,每个区的大小为1M,默认情况下,页大小为16k,一个区中一共有64个连续的页
页
- InnoDB存储引擎磁盘管理的最小单元,每个页默认大小为16KB, 为了保证页的连续性,InnoDB存储引擎每次从磁盘申请4-5个区
行
- 数据是按行进行存放的
- 在行中,默认有两个隐藏字段
- Trx_id:每次对某条记录进行改动时,都会把对应的事务id赋值给trx_id隐藏列
- Roll_pointer:每次对某条记录进行改动时,都会把旧的版本写入到undo日志,然后隐藏列就相当于一个指针,可以通过它来找到该记录修改前的信息
架构
内存结构
Buffer Pool
- InnoDB存储引擎基于磁盘文件存储,访问物理硬盘和在内存中进行访问,速度相差很大,为了尽可能弥补这两者之间的IO效率的差值,就需要把经常访问的数据加载到缓存池中,避免每次访问都进行磁盘IO
- 在InnoDB的缓冲池中,不仅缓存了索引页和数据页,还包含了undo页,插入缓存,自适应哈希索引以及InnoDB的锁信息等
- 缓冲池是主内存的一个区域,里面可以缓存磁盘上经常操作的真实数据,在执行增删改查操作时,先操作缓冲池中的数据,然后再以一定频率刷新到磁盘,从而减少磁盘IO,加快处理速度
- 缓冲池以Page页为单位,底层采用链表数据结构管理page。
- 根据状态,将Page分为三种类型:free page空闲页,未被使用;clean page:被使用页,数据没有修改过;dirty page脏页,被使用的页,数据被修改过,页中数据和磁盘数据产生了不一致
- show variables like ‘innodb_buffer_pool_size’;查看buffer pool 大小
Change Buffer
- 更改缓冲区(针对非唯一 二级索引),在执行DML语句时,如果这些数据Page没有在Buffer Pool中,不会直接操作磁盘,而会将数据变更存在更改缓冲区change buffer中,在未来数据被读取时,再将数据合并恢复到Buffer pool中,再将合并后的数据刷新到磁盘中。
Adaptive Hash Index
- 自适应hash索引,用于优化对Buffer pool数据的查询
- InnoDB存储引擎会监控对表上各索引页的查询,如果观察到在特定的条件下hash索引可以提升速度,则建立hash索引,称之为自适应hash索引
Log Buffer
- 日志缓冲区,用来保存要写入磁盘的log日志数据(redo log, undo log)
- 默认大小为 16MB,日志缓冲区会定期刷新到磁盘中,如果需要更新、插入或删除许多行的事务,增加日志缓冲区的大小可以节省磁盘IO
磁盘结构
System Tablespace
- 更改缓冲区的存储区域,如果表在系统表空间而不是每个表文件或通用表空间中创建的,它也可能包含表和索引数据。
File-per-Table Tablespaces
- 如果开启了innodb_file_per_table开关,则每个表的文件表空间包含单个InnoDB表的数据和索引,并存储在文件系统上的单个数据文件中
General Tablespaces
- 需用通过 CREATE TABLESPACE 语法来创建通用表空间,在创建时,可以指定该表空间
undo TbbleSpaces
- 撤销表空间,MySql实例在初始化时会自动创建两个默认的undo表空间,用于存储undo log日志
Temporary Tablespaces
- InnoDB 使用会话临时表空间和全局临时表空间,存储用户创建的临时表等数据
Doublewrite Buffer Files
- 双写缓冲区,innoDB引擎将数据页从Buffer Pool刷新到磁盘前,向将数据写写入到双写缓冲区,便于系统异常时恢复数据
Redo Log
- 重做日志,用来实现事务的持久性,该日志文件由两部分组成,重做日志缓冲(redo log buffer)以及重做日志文件(redo log)前者是在内存中,后者在磁盘中
- 当事务提交之后会把所有修改信息都会存储到该日志,用于在刷新脏页数据时,发生错误进行数据恢复使用
内存中的更新的数据,如何到磁盘中的?就涉及到一组后台线程
后台线程
Master Thread
- 核心后台线程,负责调度其他线程,还负责将缓冲池中的数据异步刷新到磁盘中,保持数据的一致性,还包括脏页的刷新、合并插入缓存、undo页的回收
IO Thread
-使用了大量的AIO来处理IO请求,这样可以极大的提高数据库的性能,而IO Thread主要负责这些IO请求的回调
Purge Thread
- 主要用于回收事务已经提交的undo log,在事务提交后,undo log可能不用了,就用它来回收
Page Cleaner Thread
- 协助Master Thread 刷新脏页到磁盘的线程,可以减轻Master Thread的工作压力,减少阻塞。
事务原理
事务基础
- 事务 是一组操作的集合,是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
- 事务原理,就是MySQL的InnoDB引擎是如何保证事务的这四大特性的
- 原子性、一致性、持久化是由InnoDB中的两份日志来保证的,一份是redo log,一份是undo log,而隔离性是通过数据的库的锁加上MVCC来保证的
-
redo log
- 重做日志,记录的是事务提交时数据页的物理修改,是用来实现事务的持久性
- 该日志由两部分组成,重做日志缓冲和重做日志文件,前者是在内存中,后者在磁盘中,当事务提交后会把所有修改的信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用
- 当对缓冲区的数据进行增删改之后,会首先将操作的数据页的变化,记录在redo log buffer中,在事务提交时, 会将redo log buffer 中的数据刷到redo log 磁盘文件中。在一段时间之后,如果刷新缓冲区的脏页到磁盘,发生错误,可以借助redo log进行数据恢复,这样就保证了事务的持久性。
- 如果脏页成功刷新到磁盘或者涉及到的数据已经落盘,此时redolog就没有作用了,就可以删除了,所以存在的两个redolog文件是循环写的
undo log
- 回滚日志,用于记录数据被修改前的信息,作用包含两个:提供回滚(保证事务的原子性)和MVCC(多版本并发控制)
- undo log和redo log记录物理日志不一样,它是逻辑日志,可以认为当delete一条记录时,undolog中会记录一条对应的insert记录。当执行rollback时,就可以从undo log中的逻辑记录读取到响应的内容进行回滚
- undo log在事务执行时产生,事务提交时并不会立即删除,因为这些日志还可能用于MVCC
- undo log采用段的方式进行管理和记录,存放在rollback segment回滚字段中,内部包含1024个 undo log segment
MVCC
概念
- 当前读:读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对记录进行加锁。如 select … lock in share mode(共享锁),select … for update, update insert delete 都是一种当前读
- 快照读:简单的select就是快照读,读取的是记录数据的可见版本,有可能是历史数据,不加锁,是非阻塞读
- Read Commited:每次select 都生成一个快照读
- Repeatable Read :开启事务后第一个select语句才是生成快照的地方
- Serializable:快照读会退化成当前读
- MVCC:全称Multi-Version Concurrency Control,多版本并发控制,维护一个数据的多个版本,使得读写操作没有冲突,快照读MySQL实现了一个非阻塞读功能,MVCC的实现依赖于数据库记录的三个隐式字段、uodo log日志、readview
隐藏字段
undolog
回滚日志,在insert、update、delete的时候产生的便于数据回滚的日志
版本链
DB_TRX_ID:记录修改当前记录的事务id,
DB_ROLL_PTR:指向上一个版本的指针
readview
MySQL管理
系统数据库
常用工具
mysql
- mysql的客户端工具
mysqladmin
- 一个执行管理操作的客户端程序,可以用它来检查服务器的配置和当前状态,创建并删除数据库等
mysqlbinlog
- 由于服务器生成的二进制日志文件以二进制格式保存,所以如果想要检查这些文本的文本格式,就会使用到mysqlbinlog日志管理工具
mysqlshow
- mysql客户端对象查找工具,用来很快的查找存在哪些数据库、数据库中的表、表中的列或者索引
mysqldump
- 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包括创建表,及插入表的sql语句
mysqlimport/source
-
客户端数据导入工具,用来导入mySqldump加 -t参数后导出的文本文件