这篇文章主要涉及到MySQL的知识点：

• 索引（包括分类及优化方式，失效条件，底层结构）

• sql语法（join，union，子查询，having，group by）

• 引擎对比（InnoDB，MyISAM）

• 数据库的锁（行锁，表锁，页级锁，意向锁，读锁，写锁，悲观锁，乐观锁，以及加锁的select sql方式）

• 隔离级别，依次解决的问题（脏读、不可重复读、幻读）

• 事务的ACID

• B树、B+树

• 优化（explain，慢查询，show profile）

• 数据库的范式。

• 分库分表，主从复制，读写分离。

• Nosql相关（redis和memcached区别之类的，如果你熟悉redis，redis还有一堆要问的)

• 索引的分类。

• 主键索引和普通索引的区别，组合索引怎么用会失效。

• 索引的前缀匹配的原理，从B树的结构上具体分析一下。

• 聚集索引在底层怎么实现的，数据和关键字是怎么存的。

• 组合索引和唯一性索引在底层实现上的区别（这个是整个一面感觉答得不好的一个问题，不太明白面试官想问啥）

• sql的优化策略，慢查询日志怎么操作，参数含义。

• explain 每个列代表什么含义（关于优化级别 ref 和 all，什么时候应该用到index却没用到，关于extra列出现了usetempory 和 filesort分别的原因和如何着手优化等）

• show profile 怎么使用。

总结：

MySQL遵循GPL协议，意思是：可以免费试用，但若是进行修改，修改版本也需要开源。
分支版本：MariaDB，Percona ， Drizzle， MySQL
应用于OLTP业务：在线事务处理，并发量大，几百个并发线程，快速执行一些查询，每个事务的时间都很短
OLAP(A:Analytical)：面向交易的处理系统，特点：顾客的原始数据可以立刻传输到计算中心处理，并在很短的时间内给出处理结果。

MySQL分为社区版和企业版，额外的特性通过插件的方式提供给用户：如线程池，审计，SQL安全插件

下载和安装：通用二进制安装包版本，源码版本主要用于内核调试

安装步骤：下载，解压，添加mysql组合用户，设置权限，初始化数据库，将MySQL添加到启动项

配置文件：my.cnf 可以有多个，遵循参数替换原则，配置起作用的顺序：
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf

配置参数：
session参数 global参数
可修改参数 只读参数 用户可以在线修改非只读参数
只读参数只能通过配置文件修改，并重启数据库
Show global|session variables like xxx;
set global | session variables like xxx;
不指定的话 默认是修改session

用户权限管理：
检查权限：
1.检查用户名和ip
2.查看mysql.user 所有库的权限
3.查看mysql.db 指定库的权限
4.查看mysql.table_priv 指定表
5.查看mysql.column_priv 查看指定列

提示没有权限

有什么权限：
SQL语句：SELECT 、INSERT、UPDATE、DELETE、INDEX
存储过程：CREATE ROUTINE、ALTER ROUTINE、EXECUTE、TRIGGER
管理权限：SHOW DATABASE、GRANT OPTION

连接MySQL实例：
1.通过本地socket进行连接 -S
2.通过TCP/IP协议远程连接 -h -P
3.通过配置my.cnf免密码输入

多实例安装：一个MySQL服务器安装多个MySQL实例，通过mysqld_multi即可

MySQL数据类型：
整型:tinyint, smallint, mediumint, int, bigint 属性：unsigned(范围并没有多大改变，若是用了unsigned, 那计算1–2就得不到正确结果，发生了溢出现象，如果想解决：my_cnf 里加sql_mode= NO_UNSIGNED_SUBTRACTION),zerofill，auto_increment(自增，每张表一个，必须是索引的一部分),自增int类型主键推荐使用bigint
浮点型：float,double:精度丢失的问题M*G/G != M decimal(财务系统，钱用这个类型)

字符型：char varchar, 存储本质上一样，不存在char比varchar快，使用varchar即可
char varchar都是字符长度，一个gbk字符是占2字节的，utf8是占3个字节的

Binary 是存长文本用的，保存二进制字符串，保存的是字节而不是字符，没有字符集的限制
字符不区分大小写，binary区分大小写，保存8个字符，每个字符占一个字节，共占8个字节

BLOB,TEXT 最大长度16k
，无默认值。如何在这上面建索引，必须指定索引前缀的长度。(对索引的前一部分建立索引). 减少索引文件的容量，减少IO代价。缺点，不能用order by 和group by,也不能用于covering index.

Enum& set,当sql_mode 为strict_trans_tables时,插入非声明值，会报错，这个参数要加上，否则会出现很多奇怪的问题，sql_mode为 ""时，能插入非声明值，但是值是""（空字符串），不是空值。可以用length( )来看变量的长度。枚举值得成员有一个索引值，从1开始编号，而0代表空字符串错误值，意味着，可以用select * from tb_name where enum_col = 0;在enum存储数字是不明智的，会打乱思维。

字符集： utf8, utf8mb4, gbk 库，表，列的字符集都可以设置
Collection排序规则，ci不区分大小写，bin区分大小写。

日期类型：datetime, timestamp，
timestamp有一个时区的概念(@@time_zone)，timestamp的值随着时区变化
Set @@time_zone = “+06:00”;

日期函数：NOW返回SQL执行时的时间，sysdate返回执行函数时(执行后)的时间。
检验方法：select now(), sysdate(), sleep(4), now(), sysdate();

5.7支持json类型。

表：
表 是 关系型数据库的核心， 表=关系
外键约束：为了保证数据一致性。有多个动作：restrict/cascade/set null/no action
Restrict，no action子表有这个外键选项，主表不允许更新和删除。

Alter table, 有个on line操作，5.5版本的时候，当add index时，会对表加只读(s)锁,表现在只能读，如果要进行其他操作，只能等待。而5.6有了on line 操作，就不会阻塞其他线程的操作了，比如 增删改查。 可以查看哪些操作支持on line。

表的分区：
将一个表 或者 索引分成多个 更小，更好管理的部分。
Mysql目前只支持水平分区。

每个分区表 分别保存自己的索引和数据， 分区的列 必须是唯一索引的一个组成部分。

分区类型：range list hash key columns
Range list key hash分区字段必须是int类型

Create table tb_range(
id int primary key
)engine = innodb
Partition by range(id)(
Partition p0 values less than(10),
Partition p1 values less than(20) );

Create  table tb_list(
A int
B int)engine = innodb
Partition by list(b)(
Partition p0 values in(1,3,5,7,9),
Partition p1 values in(0,2,4,6,8));

Create table t_hash(
A int 
b datetiem
)engine = innodb
Partition by hash(year(b))
partition 4;

Create table t_key(
A int 
A datetime )engine=innodb
Partition by key(b)
Partitions 4;

COLUMNS分区支持以下的数据类型：
所有的整形都支持：int/smallint/tinyint/bigint/bigint . float和decimal不支持
日期类型：支持date和datetime. 其余的日期类型不支持
字符串类型：如char, varchar, binary 和varbinary
子分区，在分区的基础上再分区（允许在range和list的分区上再进行hash和key的子分区）

分区表的使用：查询时，如果使用分区字段作为条件，那么是可以提高性能的。因为少查了数据。
可以使用explain 加上partition查看执行计算：

partitions字段显示了查询的分区表。 查询条件是分区列，而且查询值还得靠前点，这时这个分区才有用。

如果查询条件不是分区字段，那这么做分区就是没有意义的，反而性能会变差(查询所有分区，原来只查一张表，现在要查19个分区，19个表)。

在哪里查看分区表信息：information_schema中，有partitions这个表，里面存储了分区信息。

sql join 语法：

Select xxx from a, b where a.x = b.x ;
Select xxx from a inner join b on a.x = b.x;
Select xxx from a joinn b on a.x = b.x;

SQL join 算法：

Nested_loop join 
    simple nested_loop join
    index nested_loop join
    block nested_loop join
Classic hash join
    only support in MariaDB
Batched key access join 
    from MySQL 5.6

Simple nested-loop join：
    for each row r in R do:     for each row s in S do:
            if s and r satisfy the join condition 
                then output the tuple <r, s>

扫描成本 O(Rn*Sn) (所谓的笛卡尔积， 性能太差，成本太高，mysql不用)

表上有索引的查询方法：

Index nested-loop join:
    for each row r in R do:
        lookup r in S index
            if found s == r:
                then output the tuple<r,s>

扫描成本: O(Rn，驱动表行数得到的，索引查询成本是固定的)，外表是驱动表，内表有index，>=1个表有index，如果两个表都有，那就用小表做驱动表，大表做内表 优化器倾向于用小表做驱动表。

表上没有索引时的查询算法： 好处：优化simple nested-loop join,减少了内部表的扫描次数

Block nested-loop join:
    for each tuple r in R do
        store used columns as p for R in join buffer
            for each tuple s in S do
                if p and s satisfy the join condition
                    then output the tuple <p, s>

innodb_buffer_size决定了join buffer的大小，一般设置成内存的75%-80%, join_buffer中的所有记录和 内表一一进行比较
读取外表记录到join_buffer中，如果join_buffer满了，则join_buffer中的所有记录和内表一一进行比较。
其中join_buffer存的是 join 的相关的字段和要查询的列
如果join_buffer足够大，能够存下驱动表，那么扫描次数(R+S),但是比较次数还是(R*S)。

join buffer可被用于连接，是all index range的类型

Classic hash join:
    for each tuple r in R do:
        store used columns as p from R in join buffer
        build hash table according join buffer
        for each tuple s in S do
            probe hash table :
            if find 
                then output the tuple <p,s>

根据join_buffer中的列，建立hash table，对于内表中的列，进行hash搜索
hash join另一边的hash 索引是根据查询语句临时建的，在内存建的，比较占内存

batched key access join: 待续

触发器：一触即发 当表上出现特定的事件时， 出发该程序执行update/delete/insert

触发器对性能有损耗，应该谨慎使用
对于事务表，触发器执行失败则整个语句回滚
Row格式的主从复制，触发器不会再从库上执行

存储过程：
存储在数据库端的一组SQL语句集，用户可以通过存储过程名和传参多次调用的程序模块
特点： 使用灵活，可以完成复杂的业务逻辑
提高数据安全性，屏蔽应用对表的操作，易于审计
减少网络传输
缺点：提高了代码维护的复杂度

自定义函数：
与存储过程类似，但是必须带有返回值

Explain命令：
显示SQL语句的执行计划
5.6支持DML语句

Id 执行计划的标志
select_type SELECT的类型
Table 输出记录的表 derived表，用id=N语句产生的表；unionM,N 由union得到的结果表；subqueryN 由子查询产生的表
partition 符合的分区
Type join的类型 all 全表扫描 const 表最多有一个匹配行(用于比较primary key或者unique索引，因为只匹配到一行数据，所以一定用到了primary或unique，一定用到了primary或unique，而且只用这两种匹配时，餐还是type主键，const是最优解) ref引用，用哪些列进行连接
possible_key 优化器可能用到的索引
Key 优化器实际用的索引
key_len 使用索引的字节长度
Ref 进行比较的索引列
Rows 优化器预估的记录数量
Extra 额外的显示选项
Filtered 根据条件过滤得到的记录的百分比

数据库体系结构：
数据库与数据库实例： 数据库 是很多文件的集合
数据库实例： 数据库后台进程/线程以及一个共享内存区组成
共享内存可以被运行的后台进程/线程所共享
数据库实例操作数据库文件

MySQL体系结构：单进程多线程架构，插件式存储引擎

MySQL：SQL解析器，分析器，优化器，cache&buffer，engine，file system，file&logs

一个mysql可以有多个实例，一个实例对应一个scheme,一个scheme可以有多个表，一个表可以有多个视图。

MySQL物理存储结构：数据文件存储参数：datadir mysql实例下所有数据库文件都存在这个目录下。一个数据库对应一个文件夹，每张表对应一组文件(如：myisam 引擎 一个表对应三个文件)。

注意：
mysqld :是一个程序， 叫数据库实例，格式是二进制文件
mysqld_safe：是shell脚本 功能：测试是否有mysql进程，如果没有就启动

mysql的主要文件：
数据库配置文件：my.cnf
表结构定义文件：每个表对应一个表结构文件
表结构文件以.frm结尾
表结构文件是二进制文件(一堆 010101)
一个叫utilities的工具可以恢复.frm文件
错误日志：参数: log_error 默认名：机器名.err
可以这样配置：
[mysqld]
log_error = mysql.err
[mysqld_safe] 可以将错误日志移到系统日志文件/var/log/messages中
syslog
syslog_tag = stock #用这个参数时，前面的log_error会默认注释掉
慢查询日志：
用于查看运行慢的SQL语句
将运行超过某个时间的sql记录到文件中
名字：机器名-slow.log

            相关参数：
            slow_query_log:是否打开慢查询日志
            slow_query_log_file:给慢查询日志命名
            long_query_time:设置sql记录时间，支持毫秒

            相关命令：
            mysqldumpslow: 对慢查询日志进行格式化(统计输出)
            使用： mysqldumpslow ebs-46137-slow.log
通用日志：
            可以记录数据库所有的相关操作
            相关参数： general_log = 1
            默认文件名：机器名.log

            他会把所有类型的语句都进行记录，所以开启之后性能会明显下降。

MySQL存储引擎：
用来处理数据库相关的CRUD操作
transactional：事务 是否支持事务

Mysql分为服务层和存储引擎层：
官方引擎：innodb，myisam,memory,csv。。。

任何情况下 innodb都是首选，现在官方其他的存储引擎，已经不再开发和维护了

mysql> show global status like '%tmp%tables';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 10 |
| Created_tmp_tables | 66 |
+-------------------------+-------+
2 rows in set (0.01 sec)
MyISAM：堆表数据结构，表锁涉及，支持数据静态压缩，不支持事务，优点，数据文件可以直接拷贝到另一台服务器上使用(MYI, MYD, frm拷贝)

Memory: 全内存存储的引擎，支持hash索引，不支持事务
MySQL内部 排序的临时表 用的就是这个engine，参数max_heap_table_size决定临时表的大小。默认16M，放不下时，转化为MyISAM表。
memory引擎中,varchar是用char的方式存的，极其浪费内存，且他的hash index只支持等值查询

命令：show global status like '%tmp%tables'; 查看创建的基于磁盘表的次数,用到排序就会创造一张内存表，内存表会占用内存，小心。

+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 10 | #基于myisam
| Created_tmp_tables | 66 | #基于临时表的次数
+-------------------------+-------+

磁盘：
分为机械磁盘和SSD
机械磁盘顺序访问快，吞吐率好，而随机访问不行
吞吐率：单位时间内某节点成功交付数据的平均数据，单位是bps:bit per seconds。
IOPS： 每秒随机访问IO的能力，计算方法： 磁盘每分钟转速 / 60s

SSD：
纯电设备，由闪存组成，没有读写磁头，IOPS高
但是 读写不对称， 因为写入的时候 需要先擦除一个区(128个块) 在写入 ，把128个块放到内存中，物理先擦除一遍，再写入块中。

磁盘调度算法设置成：deadline 或者noop

innodb参数：innodb_flush_neighbors = 0 (#随机转顺序关闭)
innodb_log_file_size = 4G (#重做日志文件大小，至少4g或者8g,默认是48M)

数据库所在的机器中，磁盘调度算法设置成deadline.
文件系统设置成：xfs/ext4

数据库性能衡量标准：
QPS query per second
TPS transaction per second

测试工具：sysbench
tpcc：专门针对 OLTP系统的

Innodb:
Mysql和innodb可以划等号。支持多版本控制，ACID，高可用，支持数据压缩，行锁

InnoDB 物理存储：
页(page)：最小的IO单位，默认是16k，可以通过innodb_page_size进行更改，和block可以进行转换，块是物理存储上的单位，把块加载到内存中就是页
区(extent)：空间管理单位，如果表要扩大，就以区为单位去申请空间(区的单位是1M)
段(segment): 对象，每个表是一个表对象，表的索引里有 叶子节点段 和 非叶节点段，加一个索引会产生两个段对象。
表空间：innoDB所有表的对象，称为表空间

表压缩：
对于SSD，进行全库压缩，页支持压缩功能，5.6支持设置页大小(SSD下 4k,8k的页会更好相关配置参数：innodb_page_size)
页一定能进行压缩吗？ 如果16k的页能压缩到8k的话，那就按照8k的页进行存放，如果16k的页压缩不到8k，那就把16k的页存到两个8k的页里面，然后再进行压缩，所以 数据量不一定会减少。
压缩命令：alter table tb_name row_format=compressed,key_block_size=0;
查看压缩表的数量：select * from information_schema.innodb_cmp; (compress_ops:压缩数量，compress_ops_ok:压缩成功数量)

建议：调大内存，开压缩。好处：IO压力变小了，本来一次请求是16k，现在变成8k了，刷盘的时候也是按照8k来刷的，对IO的压力就变小了。但是需要CPU来牺牲性能来进行解压。CPU解压后，内存中存在两份页，一份是压缩的，一份是非压缩的，所以，需要把内存调大。存在两种页的话，每次更新时，更新两种页：压缩前的和压缩后的，最后刷盘的时候只刷新4k的，16K的页会被LRU算法替代出去，释放出内存。有未压缩的页是为了加快速度，磁盘读到内存中时，只需一次解压，然后操作的是未压缩的页。

1.数据都从缓存中读取
2.数据库的数据不会全部读到内存中，因为服务器内存不够
3.mysql管理内存会定时把脏页写入到磁盘中，但这部分脏页还是会保存到内存中。
4.mysql会定时老化一些使用权重少的数据缓存，这部分缓存会被释放，用来分配给新的内存。

页中存放的数据：
Page header, page trailer, row, row offset array(16k，row的偏移量，通过这个来找记录。其实通过索引来找记录的说法是错误的，索引只能找到row所在的页，之后是通过row offset array来找记录的,row offset array是通过row_id排序的数组，通过二分查找法，就能快速找到row_Id为特定值得row，因为二分查找法很快，快到相比较通过索引查找磁盘的开销，成本可以忽略不计。)
File trailer 和File header 这俩里有个参数叫checksum和file_page_space_or_checksum，页首和页尾都有，两个地方的参数一进行比较，就可以知道页是否损坏了，若发生损坏，两个地方的值就不对应了。Infimun + Supremum Records 两个伪记录 是在加锁和分列的时候用的。

区：空间管理的最小单位，一个区的大小为1M，如果page为16k，那就是64个页。
类型：碎片区，空闲区，段对象区
分别介绍：
段对象区：创建了一个索引，就会有一个段对象，对象会分配给 区， 这个区就是段对象
空闲区：可以被使用但是没有被 任何对象引用
碎片区：存放临时数据的区，这个区的页可以分配给很多对象，创建表的时候，可以不按照区的大小来分配，先分配给你碎片页，等满了之后再按照区进行分配。

段：每个对象都有一个段，对于每张以innodb为engine的表，innodb是索引组织表，创建表后有一个主键，主键就是索引，这个索引对应两个段：叶子节点段和非叶节点段，目的是叶子节点段都存放到一个区里面，使存储更有效率。

段由区和碎片页组成，一个段对象首先分配23个碎片页，等碎片页满了之后，才按照区的大小去申请，碎片页是一个一个分配的。

表空间：
包括系统表空间和独立表空间。 系统表空间包括：数据字典，change buffer, doublewrite， undolog等等

————————————
独立表空间包括：.ibd文件。由innodb_file_per_table控制，如果这个变量是1，就是生成独立表空间。

innodb row format:
redundant: 最古老的行格式，现在不用了，有是为了兼容mysql5.0之前的版本。
compact: 默认的行格式
compressed: 压缩格式，要压缩的话设置成这种格式。
dynamic:和compact类似，在text，blob大数据处理上有点不一样。
compact和dynamic的区别：compact存blob,text类型的大数据的话，先存text类型的前768个字节，然后存一个指针，指针指向溢出页(存剩下的字节)
dynamic存blob时， 只存20个字节的指针，不存前768个字节。(对于compact来说，存了前768个字节，页中能存其他的数据就少了，数据库优化准则是一个页能存的记录越多，性能越好。对于博客这种数据量大的，设置成dynamic。)
要支持dynamic，compressed还要配置三个参数：
innodb_strict_mode=1
innodb_file_format = Barracuda
innodb_file_format_max = Barracude

Innodb存储引擎必须包括以下三个列： rowid:主键 如果没有就会创建一个6字节的列，innodb是以B+tree组织的，必须有个key值作为默认额主键。（首先选择定义的主键，如果没有那使用not null 的unique key，如果还没有 就会创建6个字节的自增列，但是对用户不可见，可以通过_rowid 查看隐藏列。）
xid:事务id，默认是6个字节，通过xid能知道当前记录对另一个事务 是否 可以可以使用, 如果当前记录对应的xid是活跃的，代表着有事务对这条记录进行操作，但是还没有提交，有事务访问这条记录的话，那其他事务对这条记录是不可见的。但是innodb支持多版本控制，事务对它还是可以读的，但是读到的是前一个版本，，如何读的呢，通过下一个列：回滚指针。
roll_pointer：回滚指针。

innodb 后台线程：
查看命令： show engine innodb status\G
Purge thread:清理已经删除的线程
Latch monitor thread: 监控的锁信息
IO thread:
file I/O可以看到insert buffer, log, write, read 的线程数
mysql> show variables like 'innodb%threads%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_purge_threads | 4 |
| innodb_read_io_threads | 4 |
| innodb_write_io_threads | 4 |
+-------------------------+-------+
3 rows in set (0.01 sec)

可以在配置文件中对 read ,write线程进行修改。
所有innodb的写都是异步IO，读是同步IO ，建议 read thread :4, write thread 8-12(ssd)
在background thread中最重要的线程是master thread，他做的事：
分为1秒做的事 和 10 秒做的事：
每一秒做的事： 刷新重做日志到磁盘、
如果上一秒的IO< 5% innodb_io_capacity,执行5%的insert_buffer合并。
如果上一秒的脏页比例 大于 innodb_max_dirty_pages_pct, 就去刷新100%的脏页(脏页数量根据innodb_io_capacity设定来的
否则 开启 自适应刷新（adaptive_flush：每秒刷新10%的脏页，通过每秒产生额重做日志来判断 刷新多少脏页。）
如果没有用户活动，跳入后台进程循环中。
do log buffer flush to disk. 刷新重做日志到磁盘： 不管事务大小，刷新速度都很快。因为：begin transaction后，不管事务运行什么，commit的速度都很快，因为如果十五中产生日志，这个线程会把每一秒的日志刷新到磁盘中，事务的提交速度很平均。而commit的时候，会释放事务占用的锁，以确保日志写到了磁盘中。
innodb_io_capacity是一个很关键的参数，默认值是200，代表着IOPS至少是200，每秒刷新的脏页数是200. 配置建议：HDD配置成800-1200，SSD配置成10000+ 如果这个参数设置小了，刷新或合并的脏页数就少了， 少了性能就会变差，因为如果系统有太多脏页来不及刷，没有可用的页进行替换，性能就会变差。IO_capacity是用来IE的，数据库还有读的工作，所以设置的值比IOPS小点，但是基本是IOPS的值。
innodb_io_capacity:决定脏页刷新吞吐量。
innodb_max_dirty_pages_pct: 设置成75-80%.
每10秒做的事：do log buffer flush to disk, 进行5%的insert buffer合并，并做回收(full purge)。
if 这10s 的 io操作小于 io_capacity, 全量刷新脏页。
if 脏页比例大于70%，就刷新 100% 的innodb_io_capacity.
如果不大于，刷新10%的脏页。
如果没有用户活动，就返回后台进程循环中。

总之：innodb每一秒或者每十秒都会进行脏页的刷新(更新数据)，刷新的比例是按照脏页的百分比和之前的IO负载(负载低就多刷新，负载高就少刷新)来决定的。

Innodb 缓冲池(buffer pool)：
数据库实例中，先把数据库放到内存中，然后再进行操作，意味着所有的操作都是在缓冲池中进行的。
把数据放到缓冲池中进行操作，操作页的时候，所有操作都会写日志，即使发生宕机，也可以根据日志进行恢复。
相关参数：innodb_buffer_pool_size 缓冲池大小，越大越好，和物理内存的比例是 90%左右，mysql使用的内存都是buffer_pool的内存。
5.7版本可以在线更新，调大调小都可以（set global innodb_buffer_pool_size=25610241024）默认值是128M.
缓冲池存放内容：数据页，索引页， change buffer ,自适应哈希， 锁(5.5之前) undo页。
buffer pool里面有三种列表(里面存的都是页信息)：
free list(空闲列表)，
LRU list(包括LRU(普通页的LRU) unzip_LRU(未压缩页的LRU) 这个list包括 flush list)，
flush list(脏页列表，根据oldest_lsn进行排序: 页的最早修改时间， 谁先更新，先刷新谁。)
如何查看：
Show engine innodb status\G

BUFFER POOL AND MEMORY

buffer pool size 缓冲池一共有多少个页
free buffers 空闲页
database pages 使用的页(LRU page 的数量) #他和free buffers 之和不是buffer pool size 原因是LRU包括压缩页和未压缩页
LRU len 没有压缩的页 unzip_LRU len 压缩的页
Modified db pages (脏页数量)

管理的时候 是一个页一个页进行管理的

要读取的时候，先去free list 看有没有空闲的页，如果有，把这块内存拿出来，把数据放到这个页里面，然后把这个页放入LRU列表，如果这个页被更新了，他就在LRU列表中，直接在LRU列表里更新他，更新后变为脏页，出现在flush list中。这个页的引用，有一个指针指向他，表示最近更新的这个页在这个块中，所以flush list是 LRU list的一个引用而已。

LRU list 包括脏页和干净页。使用了Least Recentl Used算法(近期最少使用)：
这是内存管理的一种页面置换算法， 对于在内存中但是又不用的块(页)叫做LRU，系统会根据数据是否使用LRU 而将其溢出内存以腾出空间以加载其他的数据块。

innodb的LRU算法不是最朴素的LRU算法，最朴素的LRU对于新进的数据是放在最前面的，然后旧的往后面退，innodb使用的是midpoint LRU，数据分为old和new，old占3/8,new占5/8。为何呢》
如果  进来一组数据赌了一次后就不再读了，放在最前面的效率就不高了，先放到midpoint,如果下次在读，就放到前面,这样效率高。

用select * from innodb_buffer_page limit 1；有个is_old选项，可以看是不是old

但是有一种情况没法避免，对表进行扫描，扫描的话会对这个页读很多次，非热点数据一般在一定时间内读了就不会再读，但是扫描表的话，还是会放到最前面，为了避免这种情况，有一个参数 innodb_old_blocks_time 设置时间为毫秒，意义是 在midpoint保留xx毫秒后，才把数据往前送。
Innodb_old_blocks_pct = { },可以设置new和old的百分比。

总的来说，LRU midpoint只能保证一个page 被读一个后不会放到new里面，但是在扫描的情况下，还是会放到前面，这是对LRU list的污染，设置时间后，就避免了污染。

LRU list里面的页有两个修改时间：oldest_modification(被更新时更新)，newest_modification(第二次被更新时更新), 但是在LRU列表中， 是根据oldest_modification进行排序的。

因为： 一个页会被更新更多次，一条记录被更新了。那modification就被更新了，但是刷新的时候要按照最前面的时间点去刷新，这样才能保证日志 能对一个页进行恢复，如果根据newest_modification进行排序的话，可能这个页会被移到前面去，如果移到前面去没能刷新成功，可能我的日志几句没有了，这个页就不能恢复了，所以要根据oldest_modification进行恢复，在日志中根据old_modification进行记录的，所以要有两个modification的值。oldest和newest记录的是lsn(Log sequence number),没有更新就是0，更新的话，就记录被更新lsn的值。 第二次更新的话，就是newest发生变化。

LRU list是根据日志的lsn进行排序的，这样恢复才没有问题，如果根据newest排序，那可能遇到这个页没有刷新，但是这个页页找不到的情况。所以是根据old进行排序的，所需要两个值，oldest表示页第一次被修改的lsn,针对的是一个一个的页.

Mysql从5.6版本推出源数据表，可以用来查看每个列具体是哪个类型的，对应的是哪张表。
Innodb_buffer_page_lru:可以查看LRU里面的页
innodb_buffer_pool_status:查看状态信息
innodb_buffer_page:整个buffer pool里面的信息

缓冲池预热(针对测试)：
数据库实例快速恢复到运行状态。重启，宕机的时候需要，因为应用的数据在磁盘上，如果现在打开应用，会对磁盘造成比较大的压力。
为什么要预热：如果数据库刚刚重启，那buffer pool是空的，这样的话 一开始用sysbench做测试的话，性能会比较差，因为大部分时间用来等待从磁盘中读取数据。 所以需要数据库预热，在做测试的时候，可以指定一段时间为预热时间，后面开始计算测试结果。
为啥读取速度慢呢？ 如果有64G的buffer pool，如果不做预热， 直接去读，可能速度只有10M/s，那么把buffer pool读满需要100分钟，在这一个半小时内，数据库和磁盘的负载都很高，关于10M/s，因为读取时随机的，可能连10M/s都打不到，应用读取数据是随机的。

预热方法：
5.6版本预热方法：将LRU列表dump出来，但是dump出来的不是原先在内存中的列，因为如果原先有300G的数据，现在要dump出来，还是会很慢，他dump出来的是space和page number。

现在只知道页里面存的信息是什么，页的编号。 加载的时候，还要做一个优化，先把space和page number读进来，然后根据这两个进行排序。排完后对整个数据进行读取，这时候读的就比较快 了，因为是已经根据space和page number进行排序了，现在读取，就不是随机的了， 是顺序的。

所以，现在预热就快了，可以达到50-200M/s。

相关参数：
? innodb_buffer_pool_dump_now={ON|OFF} #现在就dump 生成一个叫 ib_buffer_pool 的文件(这个文件不大) 下面有space and page number
? innodb_buffer_pool_load_now={ON|OFF} #现在往内存中load数据
? innodb_buffer_pool_dump_at_shutdown={ON|OFF} # 关数据库的时候dump一下 5.6默认关闭 5.7默认开启 下一同
? innodb_buffer_pool_load_at_startup={ON|OFF} #启动数据库的时候 就load过了 要把这两个参数设置为1 不会变慢，dump出来的只是page number and space
? innodb_buffer_pool_filename={ib_buffer_pool}#dump出来的数据放到这个文件中
? innodb_buffer_pool_load_abort={ON|OFF}
? innodb_buffer_pool_dump_pct #5.7新增百分比参数，可以只dump最热的25%的数据，而不是100%，这样可以保证dump出的是最热数据

[root@ebs-46137 data]# vi ib_buffer_pool
0,2
0,4
0,11
0,5
0,6
0,424

这个ib_buffer_pool文件是正常开启，关闭的时候生成的，如果宕机，就没有这个文件了，所以最好有一个脚本，进行定期dump，如 每30min dump一次，
这样的话，即使宕机，我也可以快速预热，用dump_now 设置定时脚本，细致一点的话，还可以传到slave服务器，这样，如果发生主从切换，slave也可以
快速预热，否则，虽然slave一直开着，但是效果不好。因为它只写不读，但是应用有很多读操作，所以这个预热可以很细致，根据需要进行设置吧。

Redo log:
checkPoint: 是刷新脏页进而缩短数据库恢复时间的，也就是说：缓冲池不够用时，将脏页刷新到磁盘。当然，如果数据库可以 不刷新脏页，如果日志可以一直保存，那就可以不刷新脏页了，但是，不刷新脏页的话，恢复时间会变得很长，也就是说， 如果服务器运行一年宕机了， 那重做日志就得做一年的脏页刷新了，这显然是不能接受的。有了checkpoint，可以缩短数据库恢复时间，把脏页刷新了，回复时，恢复没刷新到磁盘的这些页就够了。

在innodb中，脏页是通过lsn表示的。（lsn是一个字节数：如：我对一个页进行了操作，写入了14字节的日志，那lsn就会增加14，lsn是一个字节数：表示写入了多少字节）

有三种lsn:
log sequence number: 92561351052 表示内存中lsn写到的数字的位置
Log flushed up to 92561351052 表示在重做日志中lsn写到数字的位置 只要日志写入了，那日志对应的页面一定可以恢复
Log checkpoint at 92561351052 表示页已经刷新到的lsn写入到数字的位置

数据页启动做数据库恢复的时候，恢复的就是 checkpoint到 flushed up两点之间lsn代表的页

checkpoint分类：
Sharp checkpoint: 将所有脏页刷新回磁盘。数据库关闭时会使用，因为如果运行时所有脏页都刷回磁盘，系统就会被hang住，因为其他线程不能修改页了，只能读取，如果有100g内存，做了sharp，那100g就被hang住，系统就不能使用,shrp不能在线上用，只在关闭时用。

Fuzzy checkpoint(模糊检查点)：一段时间内刷新部分脏页，在这一段时间内，只对这些脏页有影响，对其他脏页影响较小。而Fuzzy checkpoint又可分为：
master thread checkpoint 从flush_list中进行刷新(LRU需要差不多100个可替换页，每读取一个页，还会检查LRU list, 如 innodb读取了一个页，会检查LRU list，看看LRU list里面是有有100个可替换的页(可替换的页指 马上就能被替换，所谓能被替换是这个页不是脏的，如果是脏页，就需要进行一次刷新， 不是脏页，也需要进行刷新。 替换后，这块内存就可以使用，如果是脏页，而且在最尾端，那这块内存使用的时候就需要先刷新到磁盘，然后再使用。 ))
Sync/Async Flush checkpoint: 基于重做日志的刷新
Dirty page too much checkpoint: 相关参数 innodb_max_dirty_pages_pct 根据最大脏页比例进行刷新。
脏页的刷新并不是只会从flush list里面刷新，也可能在LRU list进行刷新，因为LRU list也有脏页，从LRU里面读取，是为了保证LRU list尾端有100个可替换的页，保证下次读取的时候，有100个马上可以替换的页，否则就要等待进行刷新才能使用，读取之后进行检查，并刷新。

总结：checkpoint 是用来刷新脏页的，而且希望对系统的影响较小，如果已经刷新了脏页，而且数据库发生了故障，需要恢复的时候，只要恢复到检查点(已经刷新到lsn的位置 到 重做日志写到lsn的位置)就可以了。

innoDB存储引擎特性：

Double_write: 目的是确保数据写入的可靠性，解决部分写的问题(partial write)

数据库默认一个页是16k，但是操作系统层面每个页都是4k的，所以说，16k在操作系统层面写4个4k的页，如果写了8k，还有8k没有写，这个时候如果发生宕机，就叫partial write，那这个页就变得不一致了，因为它处于一个一半的状态，处在一个中间状态，不能通过redo Log进行恢复，日志能恢复的是完整，干净的页，而发生部分写时页不完整，会导致恢复失败。
解决办法：引入double write对象，这个对象大小是2M，数据库刷新到磁盘的时候，先刷新到double write，之后再刷新到磁盘，如果在写入磁盘时发生故障，因为之前已经写入到double write
里面了，可以通过double write中完整的页去恢复到磁盘，恢复数据文件里面的页； 如果写入 double write时发生部分写，也没关系，因为数据文件上页是完整的，所以，数据还是完整的，这时候可以通过重做日志进行恢复备份。 Double write对一个页写入变成两次，性能会有所下降，但是double write是顺序写入的，128个页顺序写入double write,相当于一份拷贝，因为是顺序写入的，所以速度比较快，之后写入磁盘的这个页，是根据位置进行写的，是随机的，速度可能就慢了，double write开销比较小。

如果发生了部分写，innodb重启时会判断时候有坏页，有 则通过double write进行恢复，这个时候数据库启动的时候回自动检测，不需要人工干预。

对于一些SSD 不会有部分写的问题，如宝存，Fusion_IO。

Insert buffer:提高辅助索引的插入性能。
CREATE TABLE t (
a INT AUTO_INCREMENT,
b VARCHAR(30),
PRIMARY KEY(a),
key(b)
);
对于这种表，因为有b索引，插入时，性能会急剧下降，因为主键列的插入是顺序的，但是b是一个varchar类型，是个辅助索引，没有唯一约束，如果b存的是姓名，那插入可能是随机的，随机 会 让性能变差，此时 对这种表进行插入，性能会变差，所以，建表的时候一般先不要建索引，只建一个主键，然后导数据，导入完后再创建索引，这样速度就快了。
insert buffer 是对缓存做了操作，使得插入性能变好。但是有些索引，虽然是辅助索引，但是他的插入也是比较顺序的。比如时间类型，通过在time行加索引，来插入数据。 对于时间插入的话，是比较顺序的，不像姓名完全是随机的，只要辅助索引是顺序的，那insert 的速度就不会慢， 慢是由于随机插入，随机 会有分列， 锁的竞争。 这些会导致性能下降。 对于顺序的插入，永远是在进行追加，所以性能不会变差。

insert buffer的工作原理： 先判断插入 的非聚集索引是否在缓冲池中，如果存在，那么直接插入，插入之前，肯定要先读这个页，如果这个页不在内存中，要先把页读到缓冲池中，  但是如果有insert buffer，那先把这个页放到insert buffer中，先把这个页cache到insert buffer中，之后如果读到辅助索引这个页(这个页已经读到内存中了)，那就将这个与insertt buffer已经cache的页合并到这个页中，或者 当一个页记录cache达到2k， 主动进行合并，这是主线程的合并操作，合并的是Insert的一些对象，把这些对象cache起来，当被读取到缓冲池时，或者说当被触发合并时，才会去放到buffer pool中。
insert buffer的工作原理 是  通过增加孔家来提高性能，增加了insert buffer这样的缓冲，从而提高insert的效率。因为避免了多次离散读， 本来读磁盘的时候 是插入一条记录 读取一个页，如果这个页不在内存中，那就是随机读的了，这样会有随机IO的问题，insert Buffer是 读一个页的时候，先cache起来，如果再次读到，把多条记录依次合并，这样性能才会提升。
Insert buffer占用缓冲池的内存。5.6可以通过Innodb-change-buffer-max-size={25},表示可以使用的最大缓冲池的比例。

Show engine innodb status\G--------------------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1 #使用的页是1, free list len 0 #空闲的页是0, seg size 2, # 已经使用的有2个内存，
merged operations: 0 merges # merges 的效率 一次merge达到多少条记录
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 553253, node heap has 0 buffer(s)
0.00 hash searches/s, 30.81 non-hash searches/s

解读：51897 merged recs, 14300 merges #merge了5w多条记录，但是次数只有1.4w次，本来插入5w条非聚集索引需要5w次，但是因为先cache起来了，然后进行merge 所以效率提升了。
并不是每次insert 都会用到Insert buffer，前提条件是这个页不在缓冲池中，这样才会用到insert buffer， 在缓冲池中的页，直接插入就可以啦。

条件为什么是没有唯一约束的二级索引呢？ (没有唯一约束的二级索引 是创建的一个key,不是 unique key，对unique key是没用的)， 因为记录一开始是被cache起来的，没读本来位置的那个页，也就是说，并没有被插入到一个实际的位置，所以， 不知道插入的这条记录是否是唯一的，如果想知道，那就得去读这个页了，但是读了这个页就直接插入了，不需要cache了。 那这样局没意义了，所以，前提条件是非唯一的二级索引。

当insert buffer进入合并阶段时，性能是有所下降的。写入到insert buffer是通过日志的方式进行的，出现突发情况时，是可以通过日志进行恢复的。

5.5之后就不仅仅是insert buffer了，变为了change buffer，可以对purge, delete_marking,insert 等进行缓存，具体是根据一个参数进行配置的。
--innodb_change_buffering
All
None
Inserts
Deletes
Changes=(insert & delete-marking)
Purges

Adaptive Hash Index
innodb 通常使用B+树使用索引，B+树的时间复杂度是树的高度，但 Adaptive Hash Index效率很高，通常只需要查一次就能找到结果。innodb的Adaptive Hash Index是全部放在内存中的，数据库重启之后，这部分数据就没有了，而B+树索引是持久化的，服务器宕机，发生故障都能回复成功，B+树索引的对象是页，Adaptive Hash Index索引对象是：热点所在的记录。

为什么innodb的Hash Index是Adapt的呢？ innodb可以判断哪些页的访问时比较热的， 然后对这个页的所有记录进行hash，所以是全内存的，且只对热点页所在的记录进行hash，效率较高，内存使用
使用量较小。

Adaptive Hash Index：根据B+树访问模式构建hash的，基本没有开销，因为如果本身没有Adaptive Hash Index,也需要有B+树进行访问，不过是保存每次访问的模式，另外，仅仅对热点页的记录创建hash index，Adaptive Hash Index是非持久化的，Adaptive Hash Index的特定决定了只能进行等值查询。range select 是不支持的。

Adaptive Hash Index的创建过程: 先判断这个索引是否被访问了17次，另外，索引中的某个页(针对某个热点页进行hash，热点页中的记录进行hash ) 判断某个页是否是热点页，
判断的方式是这个页至少被访问100次，如果这个索引被访问了17次，还有一个标准，那就是访问模式是否相同。 访问模式:对于一个a,b组合索引，访问模式可以是a=xxx,或者是
a=xxx and b=xxx,如果一直是a=xxx，那就能创建Adaptive Hash Index，如果一会是a=xxx ，一会是a=xxx and b=xxx,那就不能创建a=xxx and b=xxx,了

可以看到，Adaptive Hash Index的创建标准是很苛刻的，所以Adaptive Hash Index在内存中占的比例是比较小的。

hash index默认开启:
innodb_adaptive_hash_index 这个参数默认是开启的，但是开启关闭的意义不大，因为cpu运行很快，所以Adaptive Hash Index对数据库帮助不是很大，而且Adaptive Hash Index
比较占cpu，官方文档建议关闭。

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 553253, node heap has 0 buffer(s) #buffer 代表我使了多少buffer pool的page
0.00 hash searches/s #通过hash index进行了多少查询/s, 0.00 non-hash searches/s #通过B+树索引 每秒搜索多少次 #就能知道hash index的效率

(连接页的刷新)
Flush Neighbor Page: SSD设置的话，建议把这个参数关闭掉。
innodb 是以区来进行管理的，对于一个区，要进行这个区里一个脏页的刷新，那么刷新的时候innodb会检查区里是否还有别的脏页，
如果有，会一起进行刷新，(刷新一个脏页，会检测脏页所在区是否有其他脏页，把这些脏页都刷新) 为什么要做这样的机制呢?

 本来要随机的刷新数据，现在一区里，可以顺序刷新(1M里面的页是顺序的) ，而且可以减少IO。比如一个页周围的页都是脏的，那刷的时候会把这部分
脏页变成一次IO，操作系统底层会有IO的合并，这样的效率就比较高了。     这就是连接页的刷新  ，这样的缺点呢? 为啥要关闭掉呢。

     缺点:刷新的太频繁，可能脏页刚刚变脏就刷新了，过一会又变脏了，又要刷新一次，刷新的总量就会变大， 在HDD里面，随机性能很差，随机转顺序是
            有意义的，但是在SSD中，刷新一个脏页或者刷新若干的脏页不是问题，本身的吞吐率是很高的，所以在SSD就没必要启用这个参数了。
       --innodb_flush_neighbors={1|0}  5.6提供，配到配置文件中去

innodb

innodb_strict_mode =1
innodb_file_format=Barracuda
innodb_file_format_max=Barracuda
innodb_write_io_threads=8 # 8~12
innodb_io_capacity=200 #HDD:800~1200 SSD:10000+
innodb_adaptive_flushing = 1 #SSD：0 不需要自适应刷新
sync_binlog =1 #机器即使有任何宕机 也不会有数据丢失
innodb_flush_log_at_trx_commit =1 #机器即使有任何宕机 也不会有数据丢失
innodb_max_dirty_pages_pct=75

Btree:
Binary search(二叉树查找)： 使用前提:数组是要排序的. 没有排序就不能用二叉查找，因为二叉树原理就是排序之后找中间值。在innoDB中找记录，其实就是找到页之后，利用row offset array ，用二叉树进行定位。二叉树效率很高，数据量越大越好。

Binary Search Tree(BST):
Feature：
The left subtree of a node contains only nodes with keys less than the node'skey.
The right subtree of a node contains only nodes with keys greater than the node's key.
Both the left and right subtrees must also be binary search trees.

Balanced Binary Tree
Definition
a binary tree
the height of the two sub-trees of every node never differ by more than 1 (要构建出平衡的二叉树，左右节点高度最多差1)

B+Tree：
B+树用来存储数据，来取得面向块设备的存储。特别是文件系统，和B Tree比较，B+树所有记录都存放在叶子节点，键值都存放在非叶节点。
B tree是各个层级都会存放记录的。
B+ tree有很高的扇出，有很好的IO性能。(扇出是指针)
当叶子节点中的记录满了时，就要进行分列，把中间节点提取出来，放到上一层级，然后会这个页进行分裂，一个页就变成了两个页。
B+tree每个叶子节点之间有指针进行串联，这样，如果进行扫描，直接从一个页扫到另一个页就可以了。 叶子节点满了就分列。

B+tree index in mysql:
B+ Tree Index
InnoDB Storage Engine
MyISAM Storage Engine

B+tree index:
分为 Clustered index(聚集索引) Secondary index(Non clustered index非聚集索引)
Clustered index:叶子节点存放的是整条记录。(主键id就是聚集索引) ！
Secondary index:叶子节点存放的是行标示符(row identifier)！
B+tree Height 决定了IO，从而决定了性能，磁盘的random read,sequential read

Clustered VS Secondary:
 (根节点存的是键值和point)
Clustered index key = 4 bytes
Secondary index key = 4 bytes
Key pointer = 6 bytes
Average row length = 300 bytes
Page size = 16K = 16384 bytes
Average node occupancy = 70% ( both for leaf and index page )(节点填充率是70%)
Fan-out for clustered index = 16384 * 70% / ( 4+6 ) = 1000
Fan-out for secondary index = 16384 * 70% / ( 4+ 6 ) = 1000
Average row per page for clustered index = 16384 * 70% / 300 = 35
Average row per page for clustered index = 16384 * 70% / ( 4 + 6 ) = 1000

H Clustered Index Secondary Index
2 100035 = 35,000 1000 * 1000 = 1,000,000
3 (1000)235 = 35,000,000 (1000)21000 = 1,000,000,000
4 (1000)335 = 35,000,000,000 (1000)3*1000 = 1,000,000,000,000

B+ Tree Index in InnoDB：(查找为书签查找)
IOT(索引组织表):数据都是通过索引的方式进行组织的，逻辑上有序，页与页之间也是逻辑有序的。高扇出特性。书签查找特性。(自增的话是物理有序的)。

 Bookmark lookup:所有的二级索引(里面存的是键值和主键值) 通过辅助索引查找记录的话，需要查两次记录。
 先根据二级索引查到叶子节点，找到主键值，然后根据主键值索引查到主键值对应的完整的记录。

如何理解Index呢?把index理解成表，表就是Index，index就是表。
先找到id，再根据id找到字段，查了两个表，理解为查了两个index。

比如这个表：
CREATE TABLE UserInfo (
userid INT NOT NULL AUTO_INCREMENT,
username VARCHAR(30),
registdate DATETIME,
email VARCHAR(50),
PRIMARY KEY (userid),
UNIQUE KEY idx_username (username),
KEY idex_registdate (registdate)
)；
这个表 理解为有3个表:
CREATE TABLE UserInfo (
userid INT NOT NULL AUTO_INCREMENT,
username VARCHAR(30),
registdate DATETIME,
email VARCHAR(50),
PRIMARY KEY (userid)
);
CREATE TABLE idx_username (
userid INT NOT NULL,
username VARCHAR(30),
PRIMARY KEY (username,userid)
);
CREATE TABLE idx_registdate (
userid INT NOT NULL,
registdate DATETIME),
PRIMARY KEY (registdate,userid)
);

  比如要select email where key(registdate=xx),就理解为先查idx_registdate这个表，找到对应主键值后，再去Userinfo表中 根据主键值找对应的记录。

     对一张表插入一条记录，其实就是对3个表insert 记录，但是是以事务的形式插入的，操作一个index，一条row插入了，其实是多个row插入了，而且他是一个事务，要么所有index插入这条记录，要不就都rollback。

START TRANSACTION;
INSERT INTO UserInfo values (aaa,bbb,ccc);
INSERT INTO idx_username_constraint (bbb);
INSERT INTO idx_username(bbb,aaa);
INSERT INTO idx_registdate(ccc,aaa);
COMMIT;

B+ Tree Index in MyISAM：heap table(堆表)，索引是如何组织的呢?
所有索引都是二级索引 主键和普通index的区别仅仅为是否允许为null 和 是否unique
叶子节点存的是物理地址 (MYI index) (MYD data)
叶子节点存的是 键值 和 row identifier(保存的是物理地址指针 指向data)
在myisam中，主键和普通索引的访问成本一样。 这个的好处就是 快。
( innodb的二级索引比聚集索引性能来的差)
缺点:row identifier 存的是物理位置，如果row位置发生改变了，就需要新的物理位置存放记录，这时，所有index地址都需要改变。(所有point都需要更新)

B+ Tree Index in MyISAM：
heap table(堆表)，索引是如何组织的呢?
所有索引都是二级索引 主键和普通index的区别仅仅为是否允许为null 和 是否unique
叶子节点存的是物理地址 (MYI index) (MYD data)
叶子节点存的是 键值 和 row identifier(保存的是物理地址指针 指向data)
在myisam中，主键和普通索引的访问成本一样。 这个的好处就是 快。
( innodb的二级索引比聚集索引性能来的差)
！缺点:row identifier 存的是物理位置，如果row位置发生改变了，就需要新的物理位置存放记录，这时，所有index地址都需要改变。(所有point都需要更新)

如何使用B+树索引：
建立原则：
cardinality(基数):这个索引中非唯一记录的数量。(普通索引是允许重复的) 是否是高选择性的(重复的),决定着建立索引是否有意义。如果对一个列建立索引的话，这个列必须是高选择性的。否则没有意义。比如:
对性别建立索引没有必要，因为性别是低选择性的，只有俩值
对姓名建立索引有必要，因为是高选择性的，非唯一记录数量较多。

Not use B+ Tree index situation:
Secondary Index 是二级索引
Need lots of random read 需要大量的随机读
Access more rows ( 20%+ ) 访问记录数量要超过20%
Optimizer choose sequential read instead 优化器倾向于使用顺序扫描替换随机读

  B+ Tree index需要回表，而且是每找一条记录就回一次表(回表是随机的)，回表就是回主键，不能保证id和orderid是同一个顺序，回表变成随机查询，性能会变差。

PK是聚集索引，二级索引是非聚集索引。 主键查找的话，需要全表遍历一次，扫描是顺序的，二级索引不必了。
查看cardinality的数量的命令:

show index from sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 85722 #这个索引中非唯一记录的数量
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:

Compound Index:和B+tree index一样，只不过由复合column组成: 对于(a,b) a排序，(a,b)排序，b其实是不用排序的。
那么 建立复合索引之后，哪些能用到，哪些用不到呢?(用指的是:用得到index)
Can be used
SELECT * FROM t WHERE a = ?
SELECT * FROM t WHERE a = ? AND b = ?
Can not be used
SELECT * FROM t WHERE b = ?

    SELECT * FROM t where a=? ORDER BY b   
    对于这样的，一定要建立(a,b)索引，因为对于a，就是根据b进行排序的，如果只建立a索引，意味着索引取出数据后，还要在数据库层做一次额外的排序，这样会变得比较慢，  所以对于 where a=? ORDER BY b，  要建立复合索引(a,b)。

Covering Index(索引覆盖):Get data through secondary index,no bookmark lookup needed.Using index hint. innodb是索引组织表，二级索引中包含主键值，对于下列查询 ，都不需要回表，不用回表，就叫做索引覆盖。

以下查询,都不需要回表。(根据一个键值查另一个键 或 主键)
（ primary key1， primary key2， ⋯， key1， key2， ⋯）
SELECT key2 FROM table WHERE key1=xxx；
SELECT primary key2,key2 FROM table WHERE key1=xxx
SELECT primary key1,key2 FROM table WHERE key1=xxx；
SELECT primary key1,primary key2， key2 FROM table WHERE key1=xxx；

SELECT COUNT(1) FROM buy_log WHERE buy_date>='2011-01-01' AND buy_date<'2011-02-01； 如果这个查询能被索引覆盖的话，就能使用到 ( userid,buy_date ) compound index
查count(1) 用索引覆盖的话也是能查到的。
( userid,buy_date ) compound index 使用了b索引，全索引扫描，扫描整个index，二级索引比较小，会使用二级索引， 不需要回表。

Multi-Range Read（ MRR）：
不是从索引中取一条记录就去匹配主键， 然后回表，而是把pk值存起来放在内存中，放满以后，进行排序，排好以后进行回表。
Cache secondary index key to the buffer
Sort key with rowid
Access row with rowid
More sequential

batched key access join:
如果有两张表做join，join的索引是二级索引，而且select需要回表(查聚集索引index的列)，这时涉及到回表，是随机的，性能就会很差，虽说有index，但是有回表，
如果结合MRR，回表时候先有一个interface，回表的键值用来排序，然后再去回表，极大提高了join的性能。

前提：join的列是二级索引，通过二级索引时还需要回表。 (通过索引只需要扫部分记录)

select @@optimizer_switch\G #优化器开关 MRR默认是On 但是batched key access join 是关闭的，开启命令：
SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'; #mysql没有hash join

事务：原子 一致性 隔离性 持久性
A Atomicity redo
C Consistency Undo
I isolation lock
D durable redo & undo

Redo log：
事务要符合ACID的原则，其中持久性是由redo log保证的，
当事务提交commit时，必须先将事务的所有日志写入到重做日志文件中进行持久化，待事务commit操作完成才算完成，这里的日志就是redo log
redo组成：
redo log buffer
—innodb_log_buffer (重做日志缓冲) 一般8M已经足够使用
redo log file
—innodb_log_file_size redo log 文件大小 默认是128M，要尽可能大 HDD 1-2g，SSD 4-8g
—innodb_files_in_group 一共有多少重做日志文件(默认是2) 如果文件大小是4g，那两个重做日志大小就是8g了
—innodb_log_group_home_dir 重做日志保存路径
做数据库规划时，一般是data文件放到一块盘下，顺序文件放到一块盘下(顺序文件, 如redo log，二进制文件 ) 以此来提高性能。
redo log buffer
redo log buffer 由log block组成，每个log block为512字节，
它是放在内存中的,那么，什么时候刷回到磁盘中呢？
条件：① master thread每秒进行刷新
② redo log buffer使用大于1/2进行刷新 如果在1秒之内redo log buffer 使用大于1/2的容量了 ，那就进行刷新。
③ 事务提交时进行刷新 innodb_flush_log_at_trx_commit={0|1|2},只有刷新到disk了，事务才算提交完成，因为一旦故障，有redo log，可以对文件进行恢复，这个参数默认是1，如果调节到0，事务提交时不刷新redo log到磁盘，这个主要由master thread的每秒刷新进行控制。调节为2，表示事务提交时把日志写入操作系统缓存，不确保一定写入磁盘。如果这样做，当MySQL发生宕机时，数据没事，但是当服务器发生宕机时，数据会丢失。
redo log file(重做日志文件)

组提交：
一次同步(fsync)刷新多个事务(一次同步fsync很多事务的redo log，从而提高性能)。
5.5版本是没有组提交的，可以用sysbench对5.5和5.6版本分别进行测试，性能会提高10~100倍。

组提交：为了确保数据写入到disk，会在操作系统做同步fsync操作，即 先write()操作，写到操作系统缓存，同步fsync等待操作系统缓存都写入到磁盘中，才能保证数据落盘了，这时，就算发生宕机，也能通过redo Log进行恢复 fsync性能决定了数据库性能，因为每次事务进行commit时，都会触发同步fsync操作，这代表着TPS(每秒事务能力)。
fsync同步 指的是之前说的IOPS，默认是100-200，决定了IOPS只有100-200.
Redo日志的分类：
物理日志：记录整个页的变化(diff)
逻辑日志：like SQL 语句
物理逻辑日志：根据页进行记录，内容逻辑

undo：
undo日志用来回滚操作和进行mvcc版本管理，日志里存的是记录的前项和后项(修改的前项和后项)，是逻辑记录(记录的是具体的值，而不是页里的偏移量)。
在innodb中，Undo日志有两种：insert，update , 分别由Insert 和 update操作产生.

show engine innodb status\G

TRANSACTIONS

History list length 467 #表示当前undo段 的容量 有467个undo页 undo页可以被重用，用过之后还能用

MySQL 5.5之前只有一个rollback segment， 而一个rollback segment里面有1024个undo段，导致5.5之前如果并发执行1024个事务而且这些事务都是同一个操作，就会申请undo段，如果有1025个thread接入的话，再有事务发生就会报错。 也就是说5.5之前DML操作最大并发数是1024，超过的话就会有问题，在5.5版本时进行了解决，5.5版本允许有128个rollback segment，支持12w并发。

5.5之前，所有undo段存放在系统表空间中(ibdata1)，5.6引入3个新的参数：    innodb_undo_directory :     设置Undo日志路径
innodb_undo_logs:       设置有多少个undo日志
innodb_undo_tablespaces: 设置有多少表空间

purge：
purge是真正删除记录，而且删除undo log.
从数据中删除一条记录时，其实并不是真正删除一条记录，而是数据库中操作 是有一个行标识符的，把它标识为已经删除了，真正的删除是通过purge操作进行的 (删除并不是直接删除，运行了一个删除操作，会产生Undo,但是事务提交完成后，这个Undo并不是马上被删除的，而是在purge中删除的)，commit时 把记录标识为已删除，实际上并没有删除，记录还是占用空间的，空间没有被释放。另外，这个操作对应的undo空间也没有被释放。
为什么不马上删除记录呢？
为了一致性。如果一条记录被更新了，这条记录更新前一个版本可能还被其他事务使用(可重复读，读的是之前版本的记录)。所以，不能马上把这条记录删除，因为还有其他事务正引用上个版本的记录。
Purge是一个后台线程，每10秒做一个Purge操作，回收真正的undo空间，删除undo对应的记录。5.5版本之前，所有的purge操作都是在master thread中完成的，5.5时，有了innodb_purge_thread，把purge操作放到独立线程中去，而5.6可以放到多个线程中进行并发回收，性能会更好。相关参数是innodb_purge_thread=4, undo段不够用时会自己扩充。
purge需要大量的离散读，5.6时每次purge是Purge 300 个page,(5.5是20个)，IO性能高的话可以适当调大(控制参数:innodb_purge_size), innodb_max_purge_lag(默认是0，如果由于某些原因，history list增长非常快(可能是没加索引)，查的比较慢，那Undo就不能进行回收，对其进行delay(延期)，每取一条记录都会delay一下，在innodb内部进行sleep,增长速度就降下来了。
为何purge效率不高，会导致系统表空间不断增大呢？
因为不能释放Undo，就会有新的空间进行申请(存在长事务，事务很长很大，用到Undo很多，不能释放的最大原因就是没有加索引，每次操作实行时间会很长，意味着其他事务还在引用这个undo,不能被purge)