Mysql全面总结

Mysql常见的存储引擎有什么,有什么区别

常见的存储引擎有MyISAM,InnoDB,MEMORY,MERGE.今天我们就分别介绍一下,

MyiSAM存储引擎特点

• 不支持事务，不支持外键
• 每个myisam在磁盘存储成3个文件文件名和表名相同，
  • .frm,存储表定义
  • .MYD,存储数据
  • .MYI,存储索引
• 数据文件和索引文件放到不同的目录，平均分布IO,获得更快地的速度
• 支持3种不同的存储格式
  • 静态表

  静态表的字段都是非变长字段,这样每个记录的长度是固定，这个方式的优点在于存储速度非常快,容易缓存,而且表发生损坏后也容易修复，缺点就是占空间.

  • 动态表

  包含变长字段，记录不是固定长度，优点就是占用空间少，但出错恢复起来很难修复

  • 压缩表

  由myisampack工具常见,占据非常小的磁盘空间,因为每个记录是被单独压缩的，所以只有非常小的访问开支

InnoDB存储引擎

• 支持具有提交，回滚和崩溃恢复能的事物安全
• 支持自动增长列
• 支持外键约束
• 数据的物理组织形式聚簇表，所有的数据按照主键来组织，数据和索引放在一起，都位于B+树的叶子节点上
• 存储表和索引也有两种方式
  • 使用共享表空间存储，表的表结构保存在.frm文件中,数据和索引保存在innnodb_data_home_dir和innodb_data_file_path定义的表空间，可以是多个文件
  • 使用多表空间存储,表结构放在.frm文件中，但是每个表的数据和索引单独放在.idb中，分区表的话，每个分区对应单独的idb文件，文件名是表名+分区名

Memory存储引擎

• 支持的数据类型有限制，比如不支持text和blob类型
• 每个memeory表只实际对应一个磁盘文件,格式.frm
• 访问速度快,因为数据都在内存中，默认使用HASH索引

Merge存储引擎

• 是一组MyiSAM表组合，这个鞋myisam表结构必须完全相同
• 本身没有数据，对他操作实际上是对内部Myisam表操作
• merge磁盘存储两个文件，一个是.frm存储表定义，一个是.mgr文件包含组合表的信息

一般都如何设计索引

• 搜索的索引列，如在where子句的列或连接子句指定的列
• 使用唯一索引，考虑某列的分布,索引的列基数越大索引的效果越好
• 使用短索引,如一个char(200)列,如果前10或20个字符内，多数值能唯一,那么就不要对整个列进行索引,且可以节约索引空间,索引高速缓存中容纳更所的键值，
• 利用最左前缀,建立n列索引如(c1,c2,c3),相当于建立(c1),(c1,c2)(c1,c2,c3),索引
• 不要过度使用索引，每个索引都会占用额外的磁盘空间,会降低写操作的性能,且每次修改表内容的时候,索引就会进行更改，也会增加执行计划正确的选择更优的索引复杂度
• 对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果指定了主键，按照主键顺序保存，如果没有之间但是有唯一索引，按照唯一索引排序，如果既没有主键也没有唯一索引，内部会生成一个内部列，按照这个列进行排序，主键和内部类进行访问是最快的，所以尽可能的指定主键,主键尽可能的短

Mysql有那些分区类型，分别有什么作用

主要有4中分区类型

• RANGE分区,基于一个给定连续区间范围，把数据分配到不同的分区

create tbale emp(
   id int not null,
   ename varchar(30),
   hired date not null default '1970-01-01',
   separated date not null default '1970-01-01',
   job varchar(30) not null,
   store_id int not null
)
partition by range(store_id)(
   partition p0 values less than(10),
   partition p1 values less than(20),
   partition p2 values less than(30),
);

RANGE分区适用场景

1. 当需要删除过去数据的时候，只需要简单的alter table emp drop partition p0来删除p0分区的数据，对于上百万数据说，删除分区要比运行一个delete语句有效的多
2. 经常执行包含分区键的查询，mysql可以很快的确定只有某一个或者某些分区需要扫描，因为其他分区是不可能符合where子句的任何记录，例如查询store_id大于25的记录数,只要扫描p2分区即可

• LIST分区，类似range分区，区别在于list分区是基于枚举的列表分区，而range分区是基于给定连续区间范围分区

create tbale expenses(
   expense_date date not null ,
   category int,
   amount decimal(10,3) not null
)
partition by list(category)(
   partition p0 values in(3,5),
   partition p1 values in(1,10),
   partition p2 values in(4,9),
);

LIST分区是建立离散的值列表告诉数据库特定的值属于那个分区，list分区是从属于一个枚举列表的值的集合

• HASH分区，基于给定的分区个数，把数据分配到不同的分区

create tbale emp(
   id int not null,
   ename varchar(30),
   hired date not null default '1970-01-01',
   separated date not null default '1970-01-01',
   job varchar(30) not null,
   store_id int not null
)
partition by hash(store_id) partition 4

hash分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能的平均分布，hash分区分为两种，常规hash分区和线性hash分区，常规分区是取模算法，让数据平均分布在每个分区，提高查询效率，但是如果增加一个新的分区，原来的分区的数据就要重新计算，线性hash分区使用的是一个线性的2的幂的运算法则。当分区个数2的N次幂时,线性hash分区结果和常规hash分区结果是一致的,在分区维护时，Mysql能够处理的更加迅速，同时对比常规分区的时候，线性分区数据分布不太平衡。

语法上线性分区使用partition by linear hash(store_id) partition 4,

• KEY分区，类似HASH分区

create tbale emp(
   id int not null,
   ename varchar(30),
   hired date not null default '1970-01-01',
   separated date not null default '1970-01-01',
   job varchar(30) not null,
   store_id int not null
)
partition by key (job) partition 4

类似hash分区但是hash分区允许使用用户自定义的表达式，而key分区允许使用用户自定义表达式，同时hash分区只支持整数分区，而ke分区支持使用除BLOB or text类型其他类型的列作为分区键，可以不用指定分区键，默认首先选择使用主键分区，如果没有主键的时候，会选择非空唯一键作为分区键(且唯一键是非空，否则也会报错)

一般是如何优化sql

通过show status了解各种sql的执行频率

• Com_select,执行select操作的次数，一次查询只累加1
• Com_update,执行update操作的次数

可以用下面语句，了解当前数据库是更新和插入为主，还是以查询操作为主，以及各种类型的sql大致执行比例多少

定位执行效率低的sql语句

• 通过慢查询日志定位那些执行效率较低的sql,mysqlid写一个包含所有执行时间超过long_query_time秒的sql语句的日志文件

通过explain分析低效率sql的执行计划

table，数据结果的表

ALL<Index<range<ref<eq_ref<const,system<null

通过show profile分析sql

 show profiles;
+----------+------------+-----------------------------+
| Query_ID | Duration   | Query                       |
+----------+------------+-----------------------------+
|        1 | 0.00168250 | select count(*) from depart |
+----------+------------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

 show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000605 |
| checking permissions | 0.000015 |
| Opening tables       | 0.000023 |
| init                 | 0.000020 |
| System lock          | 0.000477 |
| optimizing           | 0.000019 |
| executing            | 0.000196 |
| end                  | 0.000013 |
| query end            | 0.000006 |
| closing tables       | 0.000019 |
| freeing items        | 0.000264 |
| cleaning up          | 0.000028 |
+----------------------+----------+
12 rows in set, 1 warning (0.00 sec)

 show profile cpu for query 1;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000605 | 0.000000 |   0.000000 |
| checking permissions | 0.000015 | 0.000000 |   0.000000 |
| Opening tables       | 0.000023 | 0.000000 |   0.000000 |
| init                 | 0.000020 | 0.000000 |   0.000000 |
| System lock          | 0.000477 | 0.000000 |   0.000000 |
| optimizing           | 0.000019 | 0.000000 |   0.000000 |
| executing            | 0.000196 | 0.000000 |   0.000000 |
| end                  | 0.000013 | 0.000000 |   0.000000 |
| query end            | 0.000006 | 0.000000 |   0.000000 |
| closing tables       | 0.000019 | 0.000000 |   0.000000 |
| freeing items        | 0.000264 | 0.000000 |   0.000000 |
| cleaning up          | 0.000028 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+
12 rows in set, 1 warning (0.00 sec)

通过trace分析优化器如何选择执行计划

mysql支持目前支持那些索引

1. B+tree 和B-tree索引，最常见的索引类型，innoDB默认B+tree索引
2. Hash索引，只有memeory引擎支持，适用于key-value查询,使用场景简单，不支持范围查询
3. R-tree,空间索引，是myisam的一个特殊索引类型，主要用于地理空间数据类型
4. full-text,全文索引，是myisam的一个特殊索引类型，主要用于全文索引

聚簇索引和非聚簇索引

聚簇索引,将数据存储和索引放到一起，索引结构的叶子节点保存了行数据

非聚簇索引,将数据与索引分开存储，索引结构的叶子节点指向数据对应的位置

在innodb中聚簇索引上创建的索引叫辅助索引，非聚簇索引都是辅助索引,像复合索引,前缀索引,唯一索引，辅助索引的叶子节点不是行的物理位置，而是主键值，辅助索引访问总要二次访问查找

聚簇索引具有唯一性，由于聚簇索引是将数据和索引结构放在一起，因此一个表仅有一个聚簇索引。

表中行的物理谁许和索引中的行的物理顺序是相同的,在创建任何非聚簇索引之前创建创建聚簇索引，这是因为聚簇索引改变表中行的物理顺序，数据行，按照一定的顺序排列,并且自动排序

聚簇索引默认是主键,如果没有主键会选择一个唯一非空的字段，如果还是没有就会自动定义一个主键作为聚簇索引

Myisam使用的非聚簇索引，非聚簇索引的两颗B+树看上去没有区别，节点结构完全一致，值存储的内容不同而已，主键索引节点存储的是主键，辅助索引存储的是辅助键，表数据存储独立的地方，这两颗b+树的叶子节点都使用一个地方指向真正的表数据，由于索引树是独立的，通过辅助索引无需在访问抓紧索引树

看上去聚簇索引的效率明显低于非聚簇索引，不用回表查询，那聚簇索引的优势是啥

1. 由于行数据和索引存储在一起，同一页的会有多条数据，当访问同一个也的不同记录时候，已经把页的数据加载到了缓存，再次访问时候，会在内存中完成访问，不必访问磁盘
2. 辅助索引的叶子节点，存储主键，而不是物理地址，好处是当行数据发生变化的时候，索引树的节点也需要分裂变化，可以避免辅助索引的维护工作，另一个好处，因为辅助索引存放了主键值，减少辅助占用的存储空间小
3. 因为myisam主键不是聚簇索引，所以他的物理地址就是乱的，拿到物理地址按照合适的算法进行I/O读取，于是开始不停的寻道，而聚簇索引则只需一次
4. 但是如果涉及大数据量的排序,全表扫描，count之类的操作的话,Myisam还是占优势的，因为索引占空间小，这些操作在内存中完成即可。

为什么主键通常建议用自增id

聚簇索引的数据的物理存放顺序和索引顺序一直，只要索引相连的那么对应的数据一定也是相邻的，如果主键不是自增id,就会不断的调整物理地址,分页,如果是自增他只需要一页一页的写，索引结果紧促,磁盘碎片少,效率也高。

为什么B+树适合做索引

要知道这个问题的答案,首先我们必须先说一下二叉树查找树,平衡二叉树,红黑树,B树,以及最后的B+树各自的特点说明

二叉查找树:不平衡

也叫二叉排序树,任何节点的左子树所有的节点值都小于根节点，右子树所有的节点值都大于根节点，但是二叉查找树是不平衡

二叉树的查询时间取决于高度,平均时间复杂度是O(logN),但是有可能不断的添加至导致最终的不平衡，退化成链表如下

平衡二叉树(AVL):旋转耗时

由于二叉查找树的退化，引入了平衡二叉树，平衡二叉树左右子树高度差不能超过1，AVL查找，插入，删除在平均和最坏情况下都是O(logN)

但是由于平衡二叉树在插入和删除的时候,要破坏平衡二叉树，此时需要自旋重新平衡这个数,由于旋转的消耗，AVL树在删除数据的失效很低，在删除操作比较多的时候，维护平衡所需要的代价高于其他带来的好处，因此AVL实际使用并不高

红黑树，树太高

相比AVL树，红黑树并不是严格的平衡,只要确保从根到叶子的最长路径不多于最短可能路径的两倍长

由于红黑树的平衡性变差,所以查询的效率相对来说比较低，但是删除红黑树的删除效率大大提高,因为引入了颜色，当插入和删除的时候，只需要进行O(1)次数的宣战以及变色就能保证基本的平衡，不像AVL树进行O(longN)次旋转,总体来说性能高于平衡二叉树,但是对于数据在瓷片等辅助存储设备中的情况，红黑树并不擅长，因为红黑树长的还是太高了

B树，为磁盘而生

B树是为磁盘等辅助存储设备设计的多路平衡二叉树，而二叉树相比，B树每个非叶子节点可以有多个子树，因此当节点数量相同的时候，B树的高低比较的，是一个矮胖子，磁盘IO次数大大减少

B树除了树高度低,还有就是局部性原理,是指当一个数据被使用时候，其附件的数据有比较大的概率在短时间内被用到,B树是将临近的数据放到同一个节点，当访问某个数据的时候，会把整个节点放入缓存，以后直接可以从缓存中读取，无需进行磁盘I/O操作,命中率更高，

B+树

B+树叶是多路平衡查找树，但相比于B树的优点如下

• 更少的IO次数，B+树叶子节点只包含键，因此存储的数据更多，因此树的高度比较低，访问所需要的io次数更少，且节点记录数更多，对访问局部性原理利用更好，缓存命中率更高
• 更适合范围查询，B树中进行范围查询时,首先查找到下限,然后对B数进行中序遍历,知道找到上限，而B+树只要遍历叶子节点的链表即可
• 更稳定的查询效率,B树的时间复杂度是1到树的高度，而B+树更为稳定技术树高，因为所有数据都在叶节点

但是B+树会出现键重复，因此占用更多的空间,但是总体而言带来的性能还是可以结束的，因此B+树更加广泛再使用.

Mysql使用索引的典型场景

1. 匹配全值，即对索引中所在列指定具体值
2. 匹配值的范围查找，即索引值能够进行范围查找
3. 匹配最左前缀仅仅使用最左列进行查找，比如联合索引(col1+col2+col3),可以使用(col1),(col1+col2),(col1+col2+col3)而且使用(col1+col3)也可以使用索引
4. 仅仅对索引查询，当查询的列都在索引的字段中，查询效率会更高，不需要回表
5. 匹配列前缀，仅仅使用索引中的第一列,并且只包含索引第一列的开头一部分查找
6. 实现索引匹配部分精确而其他部分进行范围匹配
7. 如果列名是索引使用column_name is null使用索引

索引失效有哪些场景

1. 以%开头的like查询不能够利用B-tree索引
2. 数据类型出现隐式转换的时候不会使用索引
3. 复合索引情况下，假设查询条件不包含索引列最左边部分
4. 如果mysql认为使用索引比全表扫描更慢，则不是用索引
5. 用OR分割开的条件，如果or前的条件的列有索引,而后面列中没有索引，不会使用索引

还有那些优化手段

1. 使用下面语句优化

analyze tbale tbl_name:用于分析和存储表的关键字分布，分析的结果将可以使得
系统得到准确的统计信息,使得sql能够生成正确的执行计划
check table tbl_name:检查一个或多个表是够有错误
optimize table tbl_name:如果已经删除了表的一部分，或者如果对含有可变长度
行的表进行了很多更改，则使用这个命令进行空间碎片进行合并

2.大批量插入数据

如果存储引擎是MyISAM引擎,可以使用下面语句

alter table tbl_name disable keys
loading data infile '文件路径' into table tbl_name
alter table tbl_name enable keys

如果是InnoDB存储引擎，可以使用下面方式

• 因为InnoDB的表是按照主键顺序保存的，所以将导入的数据按照主键排序，进行导入
• 导入数据之前关闭唯一性校验set unique_checks=0
• 导入数据之前关闭自动提交,set autocommit=1

3.如果进行insert语句，可以使用下面方式

• insert into test values(1,2),(3,4),(1,4)...
• insert delayed into test values(1,2)
• 也可以将索引文件和数据文件分在不同的磁盘
• 如果进行批量插入可以增加bulk_insert_buffer_size变量
• 如果是一个文本文件装载一个表时候，可以使用load data infile,通常比insert快20倍

4.对于order by 优化

mysql有两种排序方式

1. 通过有序索引顺序扫描直接返回有序数据,explain分析语句之后显示Using idnex
2. 通过对返回的数据进行排序，也就是filesort排序,所有不是通过索引排序直接返回的都叫filesort

filesort是通过响应的排序算法,将取的数据在sort_buffer_size系统变量设置的内存排序区进行排序，如果内存装载不下，可以将磁盘的数据进行分块,在对各个数据块进行排序，然后将各个块的数据进行合并有序的结果集，

所以我们可以知道我应该减少额外的排序,通过索引直接返回有序数据。

5.FileSort的优化

通过合适的索引能够减少filesort的出现，但是某些情况,条件限制不能让filesort消失，我们就得看看如何优化filesort.

Mysql有两种filesort排序算法

• 两次扫描算法,首先根据条件取出字段和行指针信息，之后在sort buffer区进行排序,如果sort buffer不够,则在临时表中存储排序结果完成排序只有，在根据行信息查询记录，第一次获取排序字段和行信息，第二次根据行信息获取记录，但是第二次可能引起大量的IO操作，优点就是排序内存小
• 一次扫描，就是把满足的所有字段取出，然后在排序去sort buffer进行排序，此算法消耗内存开销大，但是排序效率比二次扫描性能好

如何选择算法，是根据max_lenght_for_sort_data的大小和query语句去取出的字段总大小判断,如果max_lenght_for_sort_data大,使用第二种优化算法，否则使用第一种

因此我可以尽量吊打max_lenght_for_sort_data的值,让mysql选择更优化的filesort排序算法，也可以适当加大sort_buffer_size排序区,尽量在内存中排序完成，而不是通过临时表放在文件进行，也不能无限加大，会导致服务器SWAP严重,

优化嵌套查询

子查询就是把select语句创建一个单例查询结果，然后把结果作为过滤条件放到另外一个查询，一次性查询可以避免事务或表锁死，但是有时间使用join更有效率，因为join不需要在内存中创建临时表完成这个逻辑上两个步骤的查询，

优化分页查询

我们使用limit 1000,20,此时是把前1020条数据查询出来仅仅返回1001到1020条数据，抛弃了前1000条数据，是在浪费，因此有以下优化思路

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

(select film_id from film order by title limit 50,10)

把limit转换成某个位置的查询

mysql锁问题

表级锁

有两种模式,表共享读锁，表独占写锁，对于MyISAM的读操作，不会阻塞其他用户对同一个表读，但是会阻塞同一个表的写请求，对于MyISAM的写操作,会阻塞对同一个表的读写操作，MyiSAM的读操作和写操作之间，和写操作之间是串行的

由于MyiSAM是一次性给表加所有需要的锁，因此他不会出现死锁，

我们知道MyISAM的读写操作是串行的，同时我们要知道如果一个进程是读表，一个进程是写锁，他们同时对一个表操作，是如何执行的呢，其实MyISAM是先进行写操作，在进行读操作，即使读操作先到锁的等待队列，写请求后到，也会把写请求放到读请求之前，这是因为Mysql认为写操作比读操作重要，因此MyISAM不适合大量更新操作的和查询的操作，当然我们也可以修改参数进行优化，但是还是不建议使用在大量读写操作的场景使用MyISAM。

InnoDB锁问题

innoDB存储引擎和MyISAM存储引擎最大的不同，就是支持事务和行锁,事务具有的属性

对于并发事务处理带来的问题

事物的隔离级别

数据库实现事物隔离的方式，基本可以分成两种

• 对数据操作之前进行加锁,阻止其他事物读数据进行操作
• 不用加任何锁,通过一定的机制生成一个数据请求时间点的执行性快照，并且这个快照提供一定解蔽的一致性读取，从用户角度看，就是同一个数据的多个版本，这种技术叫做多版本并发控制(MVCC)

InnoDB的行锁模式

• 共享锁,允许一个事物读一行，阻止其他事物获得相同数据集的排他锁
• 排他锁,允许获得排他锁的事物更新数据,阻止其他事物获取相同数据集的共享读锁或排他写锁

select *  from tableName where .... lock share mode(共享锁)
select *  from tableName where .... lock update(排他锁)

InnoDB行锁的实现方式

行锁是通过给索引加锁来实现的，如果没有索引，会在默认的聚簇索引上对记录加锁,分三种情况

1. Recond lock.对索引项加锁
2. Gap lock,对索引项之间的间隙加锁，第一条记录前的间隙或最后一行记录后的间隙加锁
3. Next-key lock,前两种的组合，对记录的前后的间隙加锁

注意的是如果不通过所以索引条件搜索数据，那么InnoDB将对表中的所有记录加锁，实际效果和表锁一样

实际应用中，要注意上面说的行锁的特点，否则导致大量的锁冲突

1. 在不通过索引条件查询中,InnoDB会锁定表中的所有记录
2. 由于Mysql的行锁是对索引项的,所以虽然使用同一个索引项对不同的记录操作,依然会出现锁冲突
3. 当表具有多个索引项的时候，不同的事务可以使用不同的索引锁定不同的行，不论是唯一索引还是主键索引，普通索引
4. 即便在条件中使用索引字段，但是使用索引来搜索数据是否用到索引，还是根据mysql的执行计划判断的，索引有可能使用的索引搜索，依然会全表加锁

Next-key LOCK

对范围条件而不是相等条件检索数据，并请求共享或排他锁时候.InnoDB会对符合条件的记录加锁，对于记录符合条件但不存在的记录叫做间隙,对这种加锁，叫做间隙锁，比如我有101条记录，1，2，3...101，使用下面语句范围查询

select *  from  table where id>100 for update

不但会对101加锁也会对大于101的记录加间隙锁，间隙锁是为了防止幻读，比如如果其他事物对大于101的数据插入操作，就会产生幻读，其次是为了恢复和复制的需要，因此我们在使用范围查询的时候，可能会导致长时间等待，所以尽量使用等值查询，同时我们要记住对于一个不存在的记录使用等值条件，也是会加锁的，

什么时候使用表锁

1. 事务需要更新大部分或全部数据，表有比较大,如果使用默认的行锁，不仅事物执行效率低,而且可能造成事物长时间等待和锁冲突，这种情况下可以使用表锁
2. 事物设计多个表，比较复杂，可能引起死锁,造成大量回滚，可以考虑一次性锁定事务设计的表,避免死锁,减少数据库事物回滚带来的开销

如何避免死锁

两个事物需要获取对方持有的排他锁才能继续完成事物，就是循环导致的死锁

如何避免死锁常用方式

1. 在应用中，如果不同的程序会并发存取多个表，应尽量约定相同的顺序访问表，
2. 批量处理顺序的时候，可以事先对数据进行排序，按照顺序进行处理记录
3. 在事物中，如果更新记录，应该直接申请足够级别的锁，排他锁，而不应该申请共享锁，更新的时候申请排他锁，因为当用户申请排他锁的时候，其他记录已经获取了同记录的共享锁，从而导致所冲突，设置死锁
4. 在可重读的隔离级别下,如果两个线程对相同记录用排他锁，在没有符合记录的情况下，都会加锁成功，就试图插入一条记录,就会产生死锁，这种情况下，把隔离级别改成读已提交
5. 但在读已提交隔离级别下如果两个线程同时要获取排他锁，判断有没有记录，如果没有则插入，此时只有一个能获取，另外一个线程等待，但是，等第一个线程提交之后,如果第二个线程插入同样的记录,会报主键冲突,但却获得了排他锁，而这个时候第三个线程申请了共享锁，因为第二线程锁定了该记录，所以第三个线程等待，而第二线程再次对记录进行更新操作就会导致死锁，对于这种情况，可以直接插入，然后在捕获异常，或者遇到主键错误时候回滚释放获得的排他锁。

内存优化的原则

• 尽可能多的内存分配给mysql做缓存，但是也要给操作系统预留足够的内存避免发生SWAP页交换,严重影响性能
• 对于Myisam存储引擎是因为操作系统自身的IO缓存，因此如果是myisam存储引擎，应尽可能预留更所内存给操作系统做IO缓存
• 排序去,链接区等缓存是分配给每个数据库会话专用的，其默认知识设置要根据最大链接数合理分配，如果设置大了，浪费资源而且在并发链接较高时会导致物理内存耗尽

MyISAM内存优化

1. MyISAM存储引擎使用key buffer缓存索引块，以加速索引的读取速度，可以设置key_buffer_size参数设置索引缓存区的大小
2. 虽然mysql通过各sesion共享key buffer提高对索引的读取，但是如果一个session如果对某个很大的索引进行扫描，就可能产生竞争key buffer,因此Mysql5.1版本引入了多索引缓存机制,从而不同表的索引缓存到不同的key buffer中。
3. 由于mysqL使用简单的LRU策略来选择要淘汰的索引数据块,但是有可能淘汰真正的热块,因此引入了中间插入策略，将LRU分成了两部分，一部分是warm子表和hot子表，当索引块读入内存的时候，先放到warm子表的尾部,即lru的中间,等达到一定的命中率之后就会进入hot子表的尾部,该数据块在hot子表流转,如果达到hot子表的头部并超过了一定时间,他将由hot头部降级到warn头部,当淘汰的时候可以首先淘汰warn子表的头部的索引块
4. 调整read_buffer_size和read_rnd_buffer_size(用于在sort查询之后,保证获取以顺序的方式获取到查询的数据,多个order by ,增加这个值可以优化性能)

InnoDB内存优化

1. innoDB用一块内存去做IO缓存池，该缓存池不仅用来缓存innoDB的索引块，而且用来缓存数据块。 innoDB使用的LRU算法和MyIsam算法很类似，Lru分为了young list,old sublist,数据从磁盘读取的时候，先放到old sublist的头部，经过一定时间转移到young list的头部，也就是整个Lru的头部，young list和old subList 中较少被访问的缓存块将从各个链表的头部转移到尾部，要淘汰的时候，优先淘汰链表尾部，这样设计也是为了方式淘汰访问频繁的热快数据。
2. 调整innodb_buffer_pool_size大小，此参数控制InnoDB的最大缓存区的大小,
3. 可以调整old subList大小使用参数 innodb_old_blocks_pct的值,
4. 调整innoDB_old_blocks_time的参数，控制缓存数据有old subList到young sublist的快慢
5. 调整缓存池数量，减少内部对缓存池数据结构的争用
6. 控制innoDB buffer 刷新，延长数据缓存时间，交换磁盘I/O
7. InnoDB doublewrite

由于mysq的数据页(一般是16KN)和操作系统的IO数据页大小(一般是4KB)不一致，无法保证InnoDB缓存也被完整,一致的刷新到磁盘，比如当发生部分断裂时候，就会出现无法恢复的问题

因此引入了doublewrite的机制，用系统表空间的一块连续的空间(大小是2MB)作为doublewrite buffer，当进行脏页刷新的时候，首先把脏页的副本刷新到doublewrite buffer中，然后调用fsync调用操作系统缓存,确保副本完整的写入到磁盘,最后InnoDB后台线程将脏页刷新到磁盘数据文件中。

Mysql binlog unlog redolog作用和区别

redolog产生的背景就是，如果每一次数据的操作都写入磁盘中,首先磁盘先要找到对应的记录然后更新整个过程，查询的成本很高，所以为了提高mysql的性能，引入了类似酒馆黑板的方式，下班更细你数据放到某个地方，待空闲的时候写入磁盘中

redolog日志是物理日志，记录的是修改了什么，记录事物操作的变化,属于InnoDB存储引擎的，不管事物是否提交，都会被记录下来，这种先写日志后写磁盘的技术称之为WAL技术，即当一条数据更新的的时候.先会写入redo log即黑板上，并更新内存，这样就更新操作完成了，同时引擎会在闲暇的时候讲redo log的记录写入磁盘

redolog日志的记录是数据页的变更，而这种记录没有必要持久化，因此他是固定大小，循环写入，如下图

其中write pos 就是此时写入的位置，之前的位置就是写入记录的地方，而checekpoint 就是把记录刷新到磁盘的位置，之前的数据已经被刷新到磁盘了，当这两个位置相遇的时候，就会停止更新操作，转而进行redo log日志同步到磁盘,

redo log日志，那么数据库进行异常重启的时候，可以根据redo log 日志进行恢复，达到了crash-safe

binlog日志

binlog是mysql数据库service层，是所有存储引擎共享的日志模块，当事物commit提交的时候，以二进制的形式保存于磁盘中，binlog是逻辑日志是已追加的形式写入的

可以设置sync_binglog控制写入磁盘的时机

1. 0:不去强求，有系统自行判断何时写入
2. 1:每次事物commit的时候都要将binlog 写入磁盘(建议参数设置为1)
3. N：每N个事物commit，才会binlog写入磁盘、

他有三种模式

1. statement模式,每一条修改数据的sql都会记录在binlog中，不需要记录每一行记录的数据变化，从而减少了binLog的日志量，节约IO存储资源，但是在某些情况下会导致主从数据不一致或不能正确复制的情况，如在master使用特殊函数，然后在slave中执行就会导致不一样的值，如last_insert_id(),sleep()
2. row模式，是记录每一行数据的被修改的形式，记录的是数据的拜年话，不会有sql多表的关联情况，row日志记录了每一行数据修改的细节，非常容易裂解，不会出现无法真确复制的问题，但是会有大量的日志存在。占据空间
3. mixed模式，是上面两种的结合，对每一条sql存储的形式不一样，在statement和mixed中任选一种,在新版本的mysql中也对row做了优化，并不是所有的修改都是以row记录，如表结构变更就会以statement模式记录，如果sql还是update或delete等修改数据的语句，那么还是按照row模式记录所有行的变更

binglog主要用再主从复制，和数据恢复场景中

undo log日志

undo log日志记录的是每条数据的所有版本，比如update语句，我会将该条记录的数据记录到undolog日志中，并且将最新版本你的roll_pointer指针指向上一个版本，这样就可以形成当前的所有版本，这就是mvcc机制，即用户读取一行记录时候，若该记录已经被其他事物占用，当前事务可以通过undo log读取之前的行版本信息，一实现非锁定读取。

undo log 记录的是逻辑日志，可以认为当delete一条记录的时候，undolog 记录的是insert记录，当update 语句的时候，记录的是一条相反的update记录

bingLog和redo log如何写入保证数据的一致性

update tb_name set c=c+1 where id =2

mysql使用了两阶段提交保证两份日志的逻辑一致，我们假设上面的update语句id=2,此时的c=0,再假设我们第一个日志成功，第二个日志失败，不使用两阶段提交,会发生什么情况，

1. 先写redo log后写binlog日志，当我们把redolog日志写完之后，在写binlog日志的时候宕机了，此时我们利用redolog可以这一行c的值恢复到1，但是binlog日志没有这条记录，当使用binLog进行数据恢复的时候，没有记录这条记录,所以binlog的c值还是0,此时就会出现数据不一致的情况
2. 先写binLog在写redo log ,当binlog日志写完之后，在写redolog的时候宕机,此时无法恢复，则c的值是0,而binlog记录了c=0改成了c=1的事务,进行恢复从库的时候,就会把c=1恢复出来，导致与源库不一致。

因此我们需要使用两阶段提交保证两个日志保持一致。

磁盘IO调度算法

传统的硬盘读取数据分为三个步骤

1. 将磁头移动到磁盘正确的位置，叫寻道时间
2. 等待磁盘旋转，需要的数据移到到磁头下面，花费时间取决于磁盘的转速，转速越高需要的时间越短
3. 磁盘继续旋转，磁盘等到所有数据都进过磁头

磁盘上面的动作的快慢取决于访问时间和传输速度，也叫延迟和吞吐量

IO的请求处理快慢取决于磁盘的寻到时间，因此操作系统并不是每次IO都去寻道，而是进行合并和排序，合并就是把相邻的和同一个的请求进行和并,对于相邻扇区通过合并但是对于非相邻的扇区就可以进行排序处理，因为IO请求按照扇区增长排序，一次旋转就可以访问更多的扇区,更够缩短所有请求的实际寻道时间

Lunux也提供了四种调度算法

1. NOOP算法 可以进行合并，但是不进行排序,按照先进先出的队列顺序提交IO请求，此算法主要场景就是随机访问设备，例如SSD,那是因为随机访问设备不存传统机械磁盘的机械臂移动造成的寻道时间，就没有必要做多余的事情了
2. 最后期限算法 除了维护拥有合并和排序的队列外，还单独维护了两个队列分别是读请求和写请求，且带有超时的队列。一个IO请求来的时候，会同时插入普通队列和读写队列中，正常处理普通队列的请求，但是如果发现读写请求队列有超时的时候，优先处理超时的请求，防止出现饥饿请求，但当系统存在大量顺序请求的时候，此算法可能导致请求无法被很好的排序，引发频繁寻道
3. 预期算法， 他和最后期限算法类似也是提供了三个队列，不同的是，处理完一个io请求之后并不会返回处理下一个请求，而是等待下一个请求，如果来的请求正好是相邻的扇区的请求,会直接处理，适合写入较多的场景
4. 完全公平队列 把每个Io请求放到进程对应的队列中，每提交一个IO请求的进程都会有自己的Io队列,以时间片算法为前提，转动队列,默认每次取出4个请求,然后处理下一个队列的4个请求，确保每个进程有资源。