Day07-SQL存储引擎

作者: 驮着集装箱的鲸鱼 | 来源:发表于2019-08-26 08:49 被阅读0次

上节回顾

1. 聚集索引与辅助索引的区别？（面试题）

聚集索引构建B树过程（面试题）

辅助索引构建B树过程（面试题）

1. 在一张表中，聚集索引只能有一个，辅助索引可以有多个，而且聚集索引所在的列最好是自增列。
2. 聚集索引的叶子节点存储的是整个数据行，辅助索引存储的只是某个列的值。

2. key_len到底长好还是短好

（1）从索引列的列值长度来看
越短越好，一般针对前缀索引
（2）从联合索引覆盖长度来看。
覆盖长度越长越好

3. 不走索引的情况（开发规范）

（1）没有查询条件，或者查询条件没有建立索引。
（2）当查询的结果集，超过了总数行数25%，优化器会觉得就没有必要走索引了，自动转换为全表扫描。
（3）索引本身失效或统计数据不真实。
（4）查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)。
（5）隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误。
（6） <> ，not in 不走索引（辅助索引）。
（7）单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit、or或in  尽量改成union。
（8）like "%_" 百分号在最前面的不走。

%linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品
=================

注意：

在业务数据库中，特别是需求量比较大的表，是没有全表扫描这种需求的。
原因如下：
1、对用户查看是非常痛苦的
2、对服务器来讲是毁灭性的

存储引擎

1. 存储引擎介绍

（1）存储引擎类似于Linux系统中的文件系统，但是比文件系统强大。
（2）存储引擎是MySQL中的一个组件，用于处理不同表类型的SQL操作。

使用show engines可以查看MySQL中所有的引擎类型以及默认支持的存储引擎。
使用show engines\G可以查看所有存储引擎详细信息。

2. 存储引擎的功能

（1）提供数据的读写。
（2）保证数据安全和一致性。
（3）提高性能
（4）提供备份机制（热备）
（5）提供自动故障恢复机制
（6）高可用方面支持
等.

3. 存储引擎的种类

3.1 查看当前MySQL官方版所支持的存储引擎（show engines）

（1）CSV
（2）MRG_MYISAM
（3）MyISAM
（4）BLACKHOLE
（5）PERFORMANCE_SCHEMA
（6）MEMORY
（7）ARCHIVE
（8）InnoDB
（9）FEDERATED  
注意：MySQL中的引擎是支持可插拔的。

3.2 查看所有支持InnoDB引擎的表（笔试题）

3306 [(none)]>select table_schema,table_name,engine from information_schema.tables where engine='innodb';
+--------------------+---------------------------+--------+
| table_schema       | table_name                | engine |
+--------------------+---------------------------+--------+
| information_schema | COLUMNS                   | InnoDB |
| information_schema | EVENTS                    | InnoDB |
| information_schema | OPTIMIZER_TRACE           | InnoDB |
| information_schema | PARAMETERS                | InnoDB |
| information_schema | PARTITIONS                | InnoDB |
| information_schema | PLUGINS                   | InnoDB |
| information_schema | PROCESSLIST               | InnoDB |
| information_schema | ROUTINES                  | InnoDB |
| information_schema | TRIGGERS                  | InnoDB |
| information_schema | VIEWS                     | InnoDB |
| mysql              | engine_cost               | InnoDB |
| mysql              | gtid_executed             | InnoDB |
| mysql              | help_category             | InnoDB |
| mysql              | help_keyword              | InnoDB |
| mysql              | help_relation             | InnoDB |
| mysql              | help_topic                | InnoDB |
| mysql              | innodb_index_stats        | InnoDB |
| mysql              | innodb_table_stats        | InnoDB |
| mysql              | plugin                    | InnoDB |
| mysql              | server_cost               | InnoDB |
| mysql              | servers                   | InnoDB |
| mysql              | slave_master_info         | InnoDB |
| mysql              | slave_relay_log_info      | InnoDB |
| mysql              | slave_worker_info         | InnoDB |
| mysql              | time_zone                 | InnoDB |
| mysql              | time_zone_leap_second     | InnoDB |
| mysql              | time_zone_name            | InnoDB |
| mysql              | time_zone_transition      | InnoDB |
| mysql              | time_zone_transition_type | InnoDB |
| sys                | sys_config                | InnoDB |
+--------------------+---------------------------+--------+

说明：
存储引擎是作用在表上的，而且还可以把表的存储引擎替换成别的，也就意味着，不同的表可以有不同的存储引擎类型。

3.3 非官方版MySQL的存储引擎种类

PerconaDB:默认存储引擎是XtraDB
MariaDB:默认存储引擎是InnoDB

以上两种数据库还支持以下几种存储引擎：
TokuDB（该引擎是mariabd的存储引擎，适合结合zabbix用）
RocksDB
MyRocks
以上三种存储引擎的共同点：压缩比较高，数据插入性能极高（insert快）。

数据库项目案例---监控系统架构整改

环境: zabbix 3.2    mariaDB 5.5  centos 7.3
现象 : zabbix卡的要死 ,  每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.

问题 :
1. zabbix 版本 
2. 数据库版本
3. zabbix数据库500G,存在一个文件里

优化建议:
1.数据库版本升级到10.0版本,zabbix升级更高版本
2.存储引擎改为tokudb
3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
4.关闭binlog和双1
5.参数调整....

优化结果:
监控状态良好

为什么要选择MariaDB，而不选择官方版的MySQL?
1. MariaDB原生态支持TokuDB,另外经过测试环境,10.0要比5.5 版本性能 高  2-3倍
2. TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高
3.监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
4.关闭binlog ----->减少无关日志的记录.zabbix不需要特别注重安全，注重性能
5.参数调整...----->安全性参数关闭,提高性能.

3.4 InnoDB和MyISAM存储引擎的替换

环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
职责
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM
解决方案:
1.升级MySQL 5.6.10版本
2. 迁移所有表到新环境
3. 开启双1安全参数

面试题：InnoDB与MyISMA之间的区别

4、InnoDB存储引擎介绍

InnoDB.png

4.1 InnoDB核心特性（笔试题）

事务（Transaction）（重点）
行级锁（Row-level Lock）（重点）
MVCC（Multi-Version Concurrency Control）（重点）
外键（了解）
支持热备份（Hot Backup）（重点） MyISAM支持温备
ACSR（Auto Crash Safey Recovery）（重点）
复制Replicaion：Group Commit，GITD（Global Transaction ID）,多线程(Multi-Threads-SQL )

MyISAM存储引擎介绍

请你列举MySQL InnoDB存储优点？
请你列举 InooDB和MyIsam的区别？

5. 存储引擎查看

5.1 使用 SELECT 确认当前会话使用的存储引擎

SELECT @@default_storage_engine;  当前引擎，5.5以上默认都是这条命令 
mysql> show variables like '%engine%'; 查看引擎设置参数

5.2 默认存储引擎设置(不代表生产操作)

会话级别:
set default_storage_engine=myisam;

全局级别(仅影响新会话):
set global default_storage_engine=myisam;
重启之后,所有参数均失效.
如果要永久生效:
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
注意：
存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.

5.3 SHOW 确认每个表的存储引擎：

SHOW CREATE TABLE City\G;
SHOW TABLE STATUS LIKE 'CountryLanguage'\G

5.4 INFORMATION_SCHEMA 确认每个表的存储引擎

[world]>select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
Master [world]>show table status;
Master [world]>show create table city;

扩展项：

在线修改MySQL参数
修改会话级别，例如：
在线修改存储引擎

3306 [(none)]>set default_storage_engine=myisam;
Query OK, 0 rows affected (0.00 sec)
功能：当前会话生效

全局级别修改，例如：
set global default_storage_engine=myisam;
功能，不影响当前和已开的其他会话，只影响到新开的会话

以上两种方法，重启后失效，除非参数添加至my.cnf

5.5 修改一个表的存储引擎

db01 [oldboy]>alter table t1 engine innodb;
注意：此命令我们经常使用他，进行innodb表的碎片整理

模拟生产需求-单库批量替换存储引擎：

将olboy数据库下的所有表的存储引擎，从MyISAM替换成InnoDB

SELECT
    concat(
        "alter table ",
        table_name,
        " engine=innodb;"
    )
FROM
    information_schema.`TABLES`
WHERE
    table_schema = 'oldboy' INTO OUTFILE '/tmp/a.sql';

5.6 平常处理过的MySQL问题--碎片处理（面试）

环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工drop表,然后导入进去
现在:
对表进行按月进行分表(partition,中间件)
业务替换为truncate方式

修改引擎与碎片整理命令：
alter table tb1 engine='innodb';  #避开业务繁忙期！！！

5.6 扩展:批量修改存储引擎

需求:将zabbix库中所有表的存储引擎，innodb替换为tokudb
select concat("alter table zabbix.",table_name," engine tokudb;") from
information_schema.tables where table_schema='zabbix' into outfile '/tmp/tokudb.sql';

6、InnoDB存储引擎物理存储结构

6.0 最直观的存储方式(/data/mysql/data/*)

与InnoDB相关的核心文件：
ibdata1：系统数据字典信息(全局统计信息)，UNDO（回滚）表空间等数据，整个数据库的信息
ib_logfile0 ~ ib_logfile1: REDO（重做\前滚日志）日志文件，事务日志文件。
ibtmp1： 临时表空间磁盘位置，存储临时表
frm：存储表的列信息
ibd：表的数据行和索引

6.1 表空间(Tablespace)

6.1.0 什么是表空间

表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。
所有的数据库对象都存放在指定的表空间中。但主要存放的是表， 所以称作表空间。

6.1.1 共享表空间（ibdata1,共享表空间的数据文件）

需要将所有数据存储到同一个表空间中 ，管理比较混乱
5.5版本出现的管理模式，也是默认的管理模式。存储的数据有“数据字典、undo、临时表、索引、表数据”。
5.6版本以，共享表空间保留，只用来存储:数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了，只用来存储:数据字典信息,undo。
8.0版本,undo也被独立出去了，只用来存储:数据字典信息。

具体变化参考官方文档:

https://dev.mysql.com/doc/refman/5.6/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

6.1.2 共享表空间设置

共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)
[(none)]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |  这个“1”表示当前使用的表空间模式为“独立表空间”模式，每个表一个ibd文件，5.7中默认就是1。如果设置为“0”，表示为“共享表空间”模式。
+-------------------------+
[(none)]>select @@innodb_data_file_path;
[(none)]>show variables like '%extend%';

生产环境推荐设置参数：
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend 初始化之前设置。
innodb_autoextend_increment=64 自动增长的值

6.1.3 独立表空间

从5.6版本开始，默认表空间不再使用共享表空间，替换为独立表空间。
主要存储的是用户数据
存储特点为：一个表一个ibd文件，存储数据行和索引信息
基本表结构元数据存储：
xxx.frm
最终结论：
      元数据            数据行+索引
mysql表数据    =（ibdataX+frm）+ibd(段、区、页)
        DDL             DML+DQL

MySQL的存储引擎日志：
Redo Log: ib_logfile0  ib_logfile1，重做日志
Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志
临时表:ibtmp1，在做join union操作产生临时数据，用完就自动

6.1.4 独立表空间设置

db01 [(none)]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                      1 |
+-------------------------+

alter table city dicard tablespace; #删除表空间
alter table city import tablespace; #恢复表空间

6.1.5 真实的生产案例

案例背景:

硬件及软件环境:
联想服务器（IBM） 
磁盘500G 没有raid
centos 6.8
mysql 5.6.33  innodb引擎  独立表空间
备份没有，日志也没开

开发用户专用库:
jira(bug追踪) 、 confluence(内部知识库)    ------>LNMT

故障描述:

断电了，启动完成后“/” 只读
fsck  重启,系统成功启动,mysql启动不了。
结果：confulence库在  ， jira库不见了

学员求助内容:

求助：
这种情况怎么恢复？
我问：
有备份没
求助：
连二进制日志都没有，没有备份，没有主从
我说：
没招了，jira需要硬盘恢复了。
求助：
1、jira问题拉倒中关村了
2、能不能暂时把confulence库先打开用着
将生产库confulence，拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的

问：有没有工具能直接读取ibd
我说：我查查，最后发现没有

想出一个办法来：

表空间迁移:
create table xxx
alter table  confulence.t1 discard tablespace;
alter table confulence.t1 import tablespace;
虚拟机测试可行。

处理问题思路:

confulence库中一共有107张表。
1、创建107和和原来一模一样的表。
他有2016年的历史库，我让他去他同时电脑上 mysqldump备份confulence库
mysqldump -uroot -ppassw0rd -B  confulence --no-data >test.sql
拿到你的测试库，进行恢复
到这步为止，表结构有了。

2、表空间删除。
select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
source /tmp/discard.sql
执行过程中发现，有20-30个表无法成功。主外键关系
很绝望，一个表一个表分析表结构，很痛苦。
set foreign_key_checks=0 跳过外键检查。
把有问题的表表空间也删掉了。

3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中
select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';

4、验证数据
表都可以访问了，数据挽回到了出现问题时刻的状态（2-8）

7. 事务的ACID特性（事务主要针对DML语句）

7.1 作用是什么

影响了DML语句（insert update delete 一部分select）

Atomic（原子性）

所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。

Consistent（一致性）

如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。

Isolated（隔离性）

事务之间不相互影响。

Durable（持久性）

事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

8. 事务的生命周期（事务控制语句）

8.1 事务的开始

begin
说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。

8.2 事务的结束

commit：提交事务
完成一个事务，也就是把内存中操作的结果，提交到磁盘，一旦事务提交成功 ，就说明具备ACID特性了。
rollback ：回滚事务，但是已经提交的事务，不能回滚。
将内存中，已执行过的操作，回滚回去

8.3 自动提交策略（autocommit）

注意：
自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能

（1）查看自动提交策略状态，1为开启，0为关闭。
3306 [(none)]>select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
3306 [(none)]>show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

(2) 临时修改自动提交策略
set autocommit=0;   “当前会话临时生效！！！”
set global autocommit=0; “全局会话临时生效！！！”

(3) 永久修改自动提交策略
vim /etc/my.cnf
autocommit=0

8.4 隐式提交语句

隐式提交语句就是在执行DML语句的时候，系统自动在语句前后加上begin和commit。

触发隐式提交的 SQL 语句：即使不敲commit，系统也会自动帮我们提交
示例1：二次begin自动提交
begin “第一次事务开始”
a “DML语句”
b “DML语句”
begin “第二次事务开始，此时，上一个事务已经自动被系统加上commit提交了。”

示例2：set自动提交
begin “第一次事务开始”
a “DML语句”
b “DML语句”
SET AUTOCOMMIT = 1 “在事务中执行的set类操作，上一个事务会被系统自动提交。”

导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

以上都是在同一个会话中才能触发的。

8.5 开始事务流程：

1、检查autocommit是否为关闭状态
select @@autocommit;
或者：
show variables like 'autocommit';
2、开启事务,并结束事务
begin “事务开始”
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
rollback; “回滚事务”

begin “事务开始”
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
commit; “提交事务”

9. InnoDB 事务的ACID如何保证?

9.1 首先了解概念与名词

（1）redo log：重做日志，也就是数据目录下的ib_logfile0和1，默认大小50M, 轮询使用，也就是轮询记录日志。
（2）redo log buffer：redo内存区域，专门用来加载redo的。
（3）*.ibd：存储数据行和索引 
（4）buffer pool --->数据缓冲区池,数据和索引的缓冲
（5）LSN : 日志序列号 
（6）出现日志序列号位置
磁盘数据页、redo文件、buffer pool、redo buffer

（7）MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动

（8）WAL : write ahead log 日志优先写的方式实现持久化

（9）脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.

（10）CKPT:Checkpoint,检查点,就是将脏页刷写到磁盘的动作
 
（11）TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务.

image

9.2 redo log

9.2.1 Redo是什么？

redo,顾名思义“重做日志”，是事务日志的一种。

9.2.2 作用是什么？

在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用

9.2.3 redo日志位置

redo的日志文件：iblogfile0 iblogfile1

9.2.4 redo buffer

redo的buffer：记录数据页的变化信息+数据页当时的LSN号
LSN：日志序列号  磁盘数据页、内存数据页、redo buffer、redolog等都有日志序列号

9.2.5 redo的刷新策略

commit;
刷新当前事务的redo buffer到磁盘
还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘

9.2.6 MySQL CSR——前滚

MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致
情况一:
我们做了一个事务,begin;update;commit.
1.在begin ,会立即分配一个TXID=tx_01.

2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中

3.DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102

4.LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer

5. 执行commit时,LGWR日志写线程会将redobuffer信息写入redolog日志文件中,基于WAL原则,
在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)

6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失

7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102
MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动

以上的工作过程,我们把它称之为基于REDO的"前滚操作"

9.3 undo 回滚日志

9.3.1 undo是什么？

undo,顾名思义“回滚日志”

9.3.2 作用是什么？

在事务ACID过程中，实现的是“A” 原子性的作用
另外CI也依赖于Undo
在rolback时,将数据恢复到修改之前的状态
在CSR实现的是,将redo当中记录的未提交的时候进行回滚.
undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备

10. 概念性的东西:

redo怎么应用的
undo怎么应用的
CSR(自动故障恢复)过程
LSN :日志序列号
TXID:事务ID
CKPT(Checkpoint)

11. 架构改造项目

项目背景:
2台  IBM X3650   32G  ,原来主从关系,2年多没有主从了,"小问题"不断(锁,宕机后的安全)
MySQL 5.1.77   默认存储引擎 MyISAM  
数据量: 60G左右 ,每周全备,没有开二进制日志
架构方案:
    1. 升级数据库版本到5.7.20 
    2. 更新所有业务表的存储引擎为InnoDB
    3. 重新设计备份策略为热备份,每天全备,并备份日志
    4. 重新构建主从
结果:
    1.性能
    2.安全方面
    3.快速故障处理

12. InnoDB存储引擎核心特性-参数补充

12.1 存储引擎相关

12.1.1 查看

show engines;
show variables like 'default_storage_engine';
select @@default_storage_engine;

12.1.2 如何指定和修改存储引擎

(1) 通过参数设置默认引擎
(2) 建表的时候进行设置
(3) alter table t1 engine=innodb;

12.2. 表空间

12.2.1 共享表空间

innodb_data_file_path
一般是在初始化数据之前就设置好
例子:
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

12.2.2 独立表空间

show variables like 'innodb_file_per_table';

12.3. 缓冲区池

12.3.1 查询

select @@innodb_buffer_pool_size;
show engine innodb status\G
innodb_buffer_pool_size 
一般建议最多是物理内存的 75-80%

12.3.2 Innodb_flush_method=(O_DIRECT, fdatasync)