面试问题简易回答：

1.一张表只能有一个聚集索引，最好是选择自增的数字列
2.聚集索引叶子节点有序存储的整行数据
3.辅助索引一个表可以有多个
4.辅助索引叶子节点存储的是索引列的有序值+此列值的主键
想要详细回答，整个两个索引构建的过程和配合使用，最好是画图说明

辅助索引细分

单列辅助索引
多列联合索引*****
        ---select xxx from where a b c
        当都是等值查询的时候，建索引有什么建议
     1.考虑联合索引
      2.排列问题？唯一值多的放左边
alter table t1 add index idx(c,b,a)
select count(distinct a ) from t1
为什么？
优化器会自动按照索引建立的顺序，自动排列where条件的顺序，前提都是等值的或者in的
如果出现其他方式条件，比如
出现了<>或者group by order by

怎么判断联合索引将来的优化效果？
1.看执行计划
2.ken_len

索引树高度问题

影响树高的因素：
1. 数据类型： char  varchar   enum 
2. 数据量级问题： 分库  分表  分布式
3. 索引列值长度： 前缀索引 

explain(desc)重点回顾

你在你们公司做过什么优化？

1.我在公司主要是配合开发和业务人员，进行SQL优化及索引这块工作
2.主要是针对索引
3.一般都是配合两个数据库工具进行配置优化
4.第一个就是slowlog慢日志，第二个是explain/desc
5.我通过之前的案例来说明一下我的思路
6.explain(desc)使用场景
题目意思：
我们公司业务慢，请你从数据库角度进行分析：
1.如果是平时没问题，突然变慢，我们需要
show processlist获取语句
然后我们desc/explain获取语句执行计划，看有没有走索引，索引类型，额外工作等
在执行计划看type 和extra 两列，针对其进行优化
然后改索引，改语句
2.如果一直慢，则看系统的slowlog(慢日志)，这个日志他主要记录了导致
数据库变慢的SQL语句，我们可以将语句提出来然后看执行计划，按刚才的思路进行优化即可

7. 另外，我还做一部分存储引擎方面的优化。

由于我们业务是插入类较多，做了一个存储引擎方面的优化
将默认的InnoDB引擎换成了TokuDB
为什么要使用TokuDB
TokuDB优点
1 高压缩比，默认使用zlib进行压缩，尤其是对字符串(varchar,text等)类型有非常高的压缩比，比较适合存储日志、
原始数据等。一般有5-10倍压缩比。
2 支持在线字段增加、删除、扩展、重命名操作。
3 支持完整的ACID特性和事务机制
4 支持快速的写入场景，Fractal-tree Index 简称（FTI）在事务实现上有优势,无undo log
TokuDB缺点
1，响应时间相对较长
2，online ddl 对text,blob等类型的字段不适用
3，没有完善的热备工具，目前有阿里云数据库团队推荐的备份方案和逻辑备份
建议适用场景：
1 访问频率不高的数据或历史数据归档
2 表非常大并且时不时还需要进行ddl操作
TokuDB的压缩算法如何选择
tokudb_zlib:表启用zlib压缩，压缩效果偏中，CPU消耗偏中，建议使用(默认)；
tokudb_quicklz:表启用quicklz压缩，压缩效果差，CPU消耗低；
tokudb_lzma:表启用lzma压缩，压缩效果好，CPU消耗高。
TokuDB默认压缩算法为zlib，建议您不要做修改，因为zlib压缩的性价比非常高。

1.存储

1.1特性

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) ,多线程(Multi-Threads-SQL )

1.2存储引擎

查看默认的存储引擎
SELECT @@default_storage_engine;
修改
    会话级别set default_storage_engine=引擎；
    全局 set global default_storage_engine=myisam;

功能&种类

功能
    数据读写
    数据安全和一致性
    提高性能
    热备份
    自动故障恢复
    高可用
种类
    innodb
    MyISAM
    CSV
    MEMORY
其他存储引擎支持
    TokuDB
        RocksDB
        MyRocks
        压缩比较高 数据插入快

InnoDB存储引擎核心特性

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) ,多线程(Multi-Threads-SQL )

案例

1、备份
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';
    into  outfile 配置文件secure-file-priv=/tmp
3、执行 source /tmp/discard.sql
    解决外键问题  set foreign_key_checks=0 
也可以在配置文件中设置
4、拷贝ibd文件
select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
执行

表空间管理

alter table t1 discard tablespace;
later table t1 import tablespace;

事务特性

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

事务的生命周期

事务的生命周期
    开始 begin
    结束 commit：提交事务
    自动提交
        查看
            select @@autocommit;
        会话修改
            set autocommit=0;
        全局修改
            set global autocommit=0;
        永久修改
            vim /etc/my.cnf
autocommit=0
    隐式提交
        begin 
a
b
begin
        非事务语句
            DDL语句： （ALTER、CREATE 和 DROP）
            DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
            锁定语句：（LOCK TABLES 和 UNLOCK TABLES）

redo在ACID中的作用

重做日志，前滚日志，主要完成ACID中的D功能，对AC也齐了一定的作用
存储了内存数据页变化的过程和INS，和TXID

redo

Redo重做日志
    作用
        在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用
    位置
        redo的日志文件：iblogfile0 iblogfile1
    redo buffer
        redo的buffer:数据页的变化信息+数据页当时的LSN号
        LSN：日志序列号  磁盘数据页、内存数据页、redo buffer、redolog
    redo的刷新策略
        commit;
        刷新当前事务的redo buffer到磁盘
        还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘

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

undo

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

2.锁

2.1锁介绍

锁定的意思，提供的是ACID中I方面的隔离方面的功能，需要配合Undo+隔离级别一起来实现
在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与)

2.2InnoDB锁级别

行级锁
Next lock下键锁
GAP lock 间隙锁
悲观锁
乐观锁

行级锁，谁先操作某个数据行，就会持有<这行>的<x>锁

2.3扩展内容

Next lock下键锁
GAP Lock间隙锁
SELECT
    course.cname,
    concat(
        '及格率：',
        CONVERT (
            sum(score.score >= 60) / COUNT(score.cno) * 100,
            DECIMAL
        ),
        '%'
    )
FROM
    course
JOIN score ON course.cno = score.cno
JOIN student ON score.sno = student.sno
GROUP BY
    course.cname

3.事务的隔离级别

查看
    select @@transaction_isolation;
    select @@tx_isolation;
    show variables like '%iso%';
影响到数据的读取,默认的级别是 RR模式.
负责的是,MVCC,读一致性问题
RU Read-uncommitted : 读未提交,可脏读,一般部议叙出现
RC   read-committed: 读已提交,可能出现幻读,可以防止脏读.
RR  REPEATABLE-READ : 可重复读,功能是防止"幻读"现象 ,
利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
    解决了 不可重复读问题和幻读的现象
    不可重复读的问题是由 undo的快照技术
    幻读现象是由 ：MVCC+GAP+Next LOCK
SR SERIALIZABLE  : 可串行化,可以防止死锁,但是并发事务性能较差
一个事务必须等待另一个事务执行完成再可以执行
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,
一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.

4.InnoDB核心参数

双1标准************：

innodb_flush_log_at_trx_commit=1
查看
      select @@innodb_flush_log_at_trx_commit;
作用：主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，
取值分别为0、1、2三个，是一个安全参数，是在5.6以上的默认的参数
1:每次事务提交，都会立即刷写redo到磁盘
（redo buffer ---每事务----os buffer---每事务---磁盘）
0:表示当事务提交时，不立即做日志写入操作
（redo buffer ---每秒----os buffer---每秒---磁盘）
2:每次事务提交引起写入文件系统缓存
（redo buffer ---每事务----os buffer---每秒---磁盘）
适用情况:对于业务安全要求性高则1 

刷写策略

InnoDB__flush_method=(O__DIRECT,fsync)
查看：
        select @@innodb_flush_method;
        show variables like '%innodb_flush%'
作用：控制了redo buffer和data buffer刷写磁盘的方式
O_DIRECT  :数据缓冲区写磁盘,不走OS buffer 日志走OS buffer 以后的工作标准
fsync :日志和数据缓冲区写磁盘,都走OS buffer
O_DSYNC  :日志缓冲区写磁盘,不走 OS buffer 数据缓冲区走OS buffer

使用建议：
最大安全模式
innodb_flush_log_at_trx_commit=1
innoDB_flush_method=O_DIRECT（必须大写）

最大性能模式：
innodb_flush_log_at_trx_commit=0
innoDB_flush_method=fsync

4.3关于redo设置

innodb_log_buffer_size=128M起，和业务系统CPU压力有关
innodb_log_file_size=256M      一般是buffer_size的1-2倍
innodb_log_files_in_group=3   3-4组

4.4innodb_buffer_pool

前提只有一个mysql实例
一般调整为物理内存的50%~80%，70以内就行

5.课后拓展

Next lock
GAP lock