MySQL复习知识点总结(针对校招|持续更新中)

这篇文章主要涉及到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),zerofillauto_increment(自增,每张表一个,必须是索引的一部分),自增int类型主键推荐使用bigint
浮点型:float,double:精度丢失的问题M*G/G != M decimal(财务系统,钱用这个类型)

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

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

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

Enum& set,当sql_modestrict_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 . floatdecimal不支持
日期类型:支持datedatetime. 其余的日期类型不支持
字符串类型:如char, varchar, binaryvarbinary
子分区,在分区的基础上再分区(允许在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)2
35 = 35,000,000 (1000)21000 = 1,000,000,000
4 (1000)3
35 = 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)

推荐阅读更多精彩内容