DBA课程-day07-存储引擎

聚集索引与辅助索引区别

1. 聚集索引 只能有一个，辅助索引可以有多个
2. 聚集索引叶子节点是整个数据行，辅助索引只是某个列的值

字符长度计算：

desc 
         字符长度       key_len（有设置not null）         key_len（没设置设置not null）
latin1     1                char(10)*1 + not null    =10         char(10)*1  =10+1
utf8       3                char(10)*3 + not null    =30       char(10)*3  =30+1
gbk        2        char(10)*2 + not null    =20
utf8mb4    4        char(10)*4 + not null    =40

filesort
扩展：

key_len 到底长好  还是短好？
维度一： 索引列的列值长度来看
越短越好，一般针对前缀索引
维度二： 从联合索引覆盖长度来看
覆盖长度越长约好

=========================================================

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

8.2.1 没有查询条件，或者查询条件没有建立索引

select * from tab;       全表扫描。
select  * from tab where 1=1;
在业务数据库中，特别是数据量比较大的表。
是没有全表扫描这种需求。
1、对用户查看是非常痛苦的。
2、对服务器来讲毁灭性的。
（1）
select * from tab;
SQL改写成以下语句：
select  * from  tab  order by  price  limit 10 ;    需要在price列上建立索引
（2）
select  * from  tab where name='zhangsan'          name列没有索引
改：
1、换成有索引的列作为查询条件
2、将name列建立索引

8.2.2 查询结果集是原表中的大部分数据，应该是25％以上。

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
假如：tab表 id，name    id:1-100w  ，id列有(辅助)索引
select * from tab  where id>500000;
如果业务允许，可以使用limit控制。
怎么改写 ？
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

8.2.3 索引本身失效，统计数据不真实

索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建。

现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select?  --->索引失效,，统计数据不真实
DML ?   --->锁冲突

8.2.4 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;

算术运算
函数运算
子查询

8.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

这样会导致索引失效. 错误的例子：
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id    | int(11)    | YES  |    | NULL    |      |
| name  | varchar(20) | YES  |    | NULL    |      |
| telnum | varchar(20) | YES  | MUL | NULL    |      |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

|  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql>

8.2.6 <> ，not in 不走索引（辅助索引）

EXPLAIN  SELECT * FROM teltab WHERE telnum  <> '110';
EXPLAIN  SELECT * FROM teltab WHERE telnum  NOT IN ('110','119');

mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum <> '1555555';

单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in  尽量改成union
EXPLAIN  SELECT * FROM teltab WHERE telnum  IN ('110','119');
改写成：
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

8.2.7 like "%_" 百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'  走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  不走索引

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

===========================================================

存储引擎

简历：熟悉InnoDB核心原理：ACID MVCC,事务,行级锁等
1. 介绍
类似于Linux系统中文件系统
2. 功能
数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持
等.
3. 种类
3.1 Oracle的MySQL的引擎
InnoDB
MyISAM
CSV
MEMORY
ARCHIVE

mysql> show engines;
mysql> select table_schema,table_name ,engine 
from information_schema.tables 
where engine='innodb';
说明：
存储引擎是作用在表上的，也就意味着，不同的表可以有不同的存储引擎类型。
3.2 其他MySQL的引擎
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB

其他的存储引擎支持:
TokuDB    
RocksDB
MyRocks
XtraDB
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高
现在很多的NewSQL,使用比较多的功能特性.

一、项目案例——监控系统架构整改

环境: 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.参数调整....

优化结果:
监控状态良好

为什么?

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

二、项目案例——业务压力大的时候,非常卡。

环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.

问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
职责
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM
解决方案:
1.升级MySQL 5.6.10版本
2. 迁移所有表到新环境
3. 开启双1安全参数
4. 重新主从

4.InnoDB 核心特性

1、事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、复制Replication: Group Commit , GTID (Global Transaction ID) ,多线程(MTS,Multi-Threads-SQL )

5.1 存储引擎操作类命令，使用 SELECT 确认会话存储引擎

SELECT @@default_storage_engine;
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.

扩展： 
在线修改MySQL参数：
会话级别，例如：
set default_storage_engine=myisam;
功能：只会影响到当前会话
全局级别，例如：
set global default_storage_engine=myisam;
功能: 不影响当前和历史会话，只影响到新开的会话
以上两种方法，在重启之后会失效，除非参数添加至my.cnf

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;

5.5 修改一个表的存储引擎

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

生产需求： 
将oldboy数据库下的所有1000表，存储引擎从MyISAM替换为innodb 

select concat("alter table ",table_name," engine innodb;")
from information_schema.tables 
where table_schema='oldboy'
into outfile '/tmp/alter.sql';

5.6 平常处理过的MySQL问题--碎片处理

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

定期执行：
alter table t1 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.0 InnoDB存储引擎物理存储结构，最直观的存储方式(/data/mysql/data)

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

6.1 表空间(Tablespace)

6.1.1、共享表空间

需要将所有数据存储到同一个表空间中 ，管理比较混乱。

5.5版本出现的管理模式，也是默认的管理模式。（数据字典，undo，临时表，索引，表数据）
5.6版本以，共享表空间保留，只用来存储:数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了
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/5.8/en/innodb-architecture.html

6.1.2 共享表空间设置

共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)
[(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 |
+-------------------------+
//下面两条命令是mysql的innodb引擎表空间迁移用的        *****
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 -ppassword -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）

8、事务的ACID特性

8.1 作用是什么？

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

1.Atomic（原子性）
所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。
2.Consistent（一致性）
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
3.Isolated（隔离性）
事务之间不相互影响。
4.Durable（持久性）
事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

9、事务的生命周期（事务控制语句）

9.1 事务的开始

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

9.2 事务的结束

commit：提交事务
完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。
rollback ：回滚事务
将内存中，已执行过的操作，回滚回去

9.3 自动提交策略（autocommit）

db01 [(none)]>select @@autocommit;
db01 [(none)]>set autocommit=0;
db01 [(none)]>set global autocommit=0;

注：
自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能
(1)临时设置
set autocommit=0;   
set global autocommit=0;
(2)永久设置
vim /etc/my.cnf
autocommit=0

9.4 事务的隐式控制

用于隐式提交的SQL语句
begin 
a
b

1.导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）

2.导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

9.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;

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

10.0 一些概念

redo log ---> 重做日志 ib_logfile0~1  50M ,轮询使用
redo log buffer ---> redo内存区域
t1.ibd     ----> 存储 数据行和索引 
buffer pool --->数据缓冲区池,数据和索引的缓冲
LSN : 日志序列号 
磁盘数据页,redo文件,buffer pool,redo buffer
MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动
WAL : write ahead log 日志优先写的方式实现持久化
脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.
CKPT:Checkpoint,检查点,就是将脏页刷写到磁盘的动作
TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务.

10.1 redo log

10.1.1 Redo是什么？

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

10.1.2 作用是什么？

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

10.1.3 redo日志位置

redo的日志文件：iblogfile0 iblogfile1

10.1.4 redo buffer

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

10.1.5 redo的刷新策略

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

10.1.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的"前滚操作"

11.2 undo 回滚日志

11.2.1 undo是什么？

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

11.2.2 作用是什么？

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

11.3 概念性的东西:

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

11.4 锁

“锁”顾名思义就是锁定的意思。
“锁”的作用是什么？
在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与).
悲观锁:行级锁定(行锁)
谁先操作某个数据行,就会持有<这行>的(X)锁.
乐观锁: 没有锁

11.5 隔离级别

影响到数据的读取,默认的级别是 RR模式.
transaction_isolation   隔离级别(参数)
负责的是,MVCC,读一致性问题
RU  : 读未提交,可脏读,一般部议叙出现
RC  : 读已提交,可能出现幻读,可以防止脏读.
RR  : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
SR   : 可串行化,可以防止死锁,但是并发事务性能较差
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.
例如:
[world]>select * from city where id=999 for update;
[world]>commit;

11.6 架构改造项目

项目背景:
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.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.4. innodb_flush_log_at_trx_commit (双一标准之一)

12.4.1 作用

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。

12.4.2 查询

select @@innodb_flush_log_at_trx_commit;

12.4.3 参数说明:

1，每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；flush  到操作系统的文件系统缓存  fsync到物理磁盘.
0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入文件系统缓存并且秒fsync磁盘一次；
2，每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作。
--------
The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
-------

12.5. Innodb_flush_method=(O_DIRECT, fdatasync)

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html #sysvar_innodb_flush_method

12.5.1 作用

控制的是,log buffer 和data buffer,刷写磁盘的时候是否经过文件系统缓存

12.5.2 查看

show variables like '%innodb_flush%';

12.5.3 参数值说明

O_DIRECT  :数据缓冲区写磁盘,不走OS buffer
fsync :日志和数据缓冲区写磁盘,都走OS buffer
O_DSYNC  :日志缓冲区写磁盘,不走 OS buffer

12.5.4 使用建议

最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT
最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync

12.6. redo日志有关的参数

innodb_log_buffer_size=16777216
innodb_log_file_size=50331648
innodb_log_files_in_group = 3

13.扩展(自己扩展，建议是官方文档。)

RR模式(对索引进行删除时):
GAP:          间隙锁
next-lock:    下一键锁定

例子:
id（有索引）
1 2 3 4 5 6 
GAP：
在对3这个值做变更时，会产生两种锁，一种是本行的行级锁，另一种会在2和4索引键上进行枷锁
next-lock：
对第六行变更时，一种是本行的行级锁，在索引末尾键进行加锁，6以后的值在这时是不能被插入的。
总之：
GAP、next lock都是为了保证RR模式下，不会出现幻读，降低隔离级别或取消索引，这两种锁都不会产生。
IX IS X S是什么?

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 158,736评论 4赞 362
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,167评论 1赞 291
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 108,442评论 0赞 243
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 43,902评论 0赞 204
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,302评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,573评论 1赞 216
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,847评论 2赞 312
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,562评论 0赞 197
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,260评论 1赞 241
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,531评论 2赞 245
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,021评论 1赞 258
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,367评论 2赞 253
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,016评论 3赞 235
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,068评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,827评论 0赞 194
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,610评论 2赞 274
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,514评论 2赞 269

DBA课程-day07-存储引擎

聚集索引 与 辅助索引 区别

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

8.2.1 没有查询条件，或者查询条件没有建立索引

8.2.2 查询结果集是原表中的大部分数据，应该是25％以上。

8.2.3 索引本身失效，统计数据不真实

8.2.4 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

8.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

8.2.6 <> ，not in 不走索引（辅助索引）

8.2.7 like "%_" 百分号在最前面不走

存储引擎

4.InnoDB 核心特性

5.1 存储引擎操作类命令，使用 SELECT 确认会话存储引擎

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

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

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

5.5 修改一个表的存储引擎

5.6 平常处理过的MySQL问题--碎片处理

5.6 扩展:如何批量修改

6.0 InnoDB存储引擎物理存储结构，最直观的存储方式(/data/mysql/data)

6.1 表空间(Tablespace)

6.1.1、共享表空间

6.1.2 共享表空间设置

6.1.3 独立表空间

6.1.4 独立表空间设置问题

6.1.5 真实的学生案例

8、事务的ACID特性

8.1 作用是什么？

9、事务的生命周期（事务控制语句）

9.1 事务的开始

9.2 事务的结束

9.3 自动提交策略（autocommit）

9.4 事务的隐式控制

9.5 开始事务流程：

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

10.0 一些概念

10.1 redo log

10.1.1 Redo是什么？

10.1.2 作用是什么？

10.1.3 redo日志位置

10.1.4 redo buffer

10.1.5 redo的刷新策略

10.1.6 MySQL CSR——前滚

11.2 undo 回滚日志

11.2.1 undo是什么？

11.2.2 作用是什么？

11.3 概念性的东西:

11.4 锁

11.5 隔离级别

11.6 架构改造项目

12 InnoDB存储引擎核心特性-参数补充（存储引擎相关）

12.1.1 查看

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

12.2. 表空间

12.2.1 共享表空间

12.2.2 独立表空间

12.3. 缓冲区池

12.3.1 查询

12.4. innodb_flush_log_at_trx_commit (双一标准之一)

12.4.1 作用

12.4.2 查询

12.4.3 参数说明:

12.5. Innodb_flush_method=(O_DIRECT, fdatasync)

12.5.1 作用

12.5.2 查看

12.5.3 参数值说明

12.5.4 使用建议

12.6. redo日志有关的参数

13.扩展(自己扩展，建议是官方文档。)

推荐阅读更多精彩内容

聚集索引与辅助索引区别