Mysql_2

mysq innoDb支持事务，和行级锁
mysql的架构

• connection pool：连接池
• Parser：解析器
• Cache&Buffer：缓存
• Optimizer：优化器
• Sql Interface：
• Pluggabe storage Engines：存储引擎，存储引擎负责对表中的数据的进行读取和写入，常用的存储引擎有InnoDB、MyISAM、Memory等，不同的存储引擎有自己的特性，数据在不同存储引擎中存放的格式也是不同的，比如Memory都不用磁盘来存储数据。在InnoDB中，数据会存储到磁盘上，在真正处理数据时需要先将数据加载到内存，表中读取某些记录时，InnoDB存储引擎不需要一条一条的把记录从磁盘上读出来，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB，也就是说，当需要从磁盘中读数据时每一次最少将从磁盘中读取16KB的内容到内存中，每一次最少也会把内存中的16KB内容写到磁盘中。（操作系统的一页是4kb）
• File System：文件系统操作系统一次取数据，会取一页，操作系统的一页是4kb。mysql概念中的页是16kb。单条记录最大限制是65535个字节。而单页最多能存储16kb即16384个字符，此时一页连一条记录都存储不了，此时页数据溢出。compact行格式的做法是：那么会在第一页存储部分数据，然后在数据去存放执行第二页的地址。 其他行格式如dynamic则是不会再第一页存数据，而是存储地址，然后后续的页存放数据。SHOW GLOBAL STATUS LIKE ‘Innodb_page_size’;

局部性原理：是指程序执行时呈现出的局部性规律，即在一段时间内，整个程序的执行会仅仅限于程序的某一部分，相应地，执行所访问的存储空间也局限于某一部分。

页结构：

Page Directory：记录页中每组的主键最小，以顺序存储的方式实现。一行记录可以以不同的格式存在InnoDB中，行格式分别是Compact、Redundant、Dynamic和Compressed行格式。

Compact行格式如下：

变长字段长度列表：
MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。

• CHAR是一种固定长度的类型，VARCHAR则是一种可变长度的类型。
• VARCHAR(M)，M代表最大能存多少个字符。(MySQL5.0.3以前是字节，以后就是字符)

为什么会有变长字段呢？比方说varchar类型指定长度如20指的是20个字符，但是20个字符在不同的编码下有不同的空间大小。

NUll 值列表
Compact行格式会把可以为NULL的列统一管理起来，存一个标记为在NULL值列表中，如果表中没有允许存储NULL 的列，则 NULL值列表也不存在了。
* 二进制位的值为1时，代表该列的值为NULL
* 二进制位的值为0时，代表该列的值不为NULL

记录头信息：
除了变长字段长度列表、NULL值列表之外，还有一个用于描述记录的记录头信息，它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思，如图：

next_record指向下一条数据。

记录真实的数据：
记录的真实数据除了我们自己定义的列的数据以外，还会有三个隐藏列：

• 实际上这几个列的真正名称其实是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。
• 一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。

行记录除了Blob类型的字段，所占空间最大为65535个字节（不包括隐藏列和记录头）。此时一行数据都需要多页来存储，此时叫做行溢出。compact和Reduntant行格式是会在上一页中记录真实数据处用20个字节存储指向存储下一页的地址(当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节)。

Dynamic和Compressed行格式处理行溢出：
这两种行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储一部分数据，而是把所有的数据都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。另外，Compressed行格式会采用压缩算法对页面进行压缩。

• InnoDb插入时默认会按主键排序, – 聚集索引的方式组织数
• MyISAM的顺序和插入时顺序一致。 --堆表的方式组织数据聚集索

—引可以理解为主键索引，之所以称为聚集索引，是因为叶子节点存储的是数据，数据和索引在一起；
—联合索引和二级索引：索引和数据不在一起，索引和主键在一起。根据索引查到主键，然后再利用主键去查找数据。

联合索引的第二个条件不一定能用到索引，例如：

create index index_bc on tableName(b, C）；
select * from table where b>2 and c> 5;     必须第一个索引确定下来后才能使用C字段 。 
select * from table where c=2 and b >5;     也不能用到索引。

索引为什么能提高查询效率？

• 排序，然后利用算法去查找，比方说二分法
• 在遍历时，遍历到一定条件就可以不用遍历了，例如

 select * from table where a >5; 排序后遍历当a>5后的第二条数据以及之后的数据都不用比较了。

• 当一页存储很多条记录时，会将多行记录归为一组，然后将每组找那个主键最小的那条记录的主键存在页目录中。那么多组的最小主键是用一个数组存储的，而且里面的元素还是递增的。B+
• 树当有多页时，会将每页中的最小主键和页号作为元素存储起来。此时查找时先定位到页号（指针），再在页号中定位到组，再定位到记录。

建表之初，创建第一页，当第一页存储空间不够了，首先会将第一页copy一份，然后创建第二页，最后将最起初的第一页修改为目录页。此时就有了目录页，第一页（备份来的），第二页。目录页是固定的，是不会变的，是查询的总入口。如果目录快满了，快要达到上限了。那么后面的数据页也可以变成子目录页，然后存储它自己的数据页。

主键索引：那主键建立索引，建立索引会根据对应的字段排序。InnoDb默认会建立主键索引。InnoDb优先使用用户自定义的主键作为索引。如果没有定义主键，则会选取一个unique键作为主键；如果表中没有定义unique键的话，InnoDb会为表默认添加一个叫做row_id的隐藏列作为主键（row_id隐藏列只有在没有自定义主键和unique键的情况下才会自动添加该列，而其他两列隐藏列默认都会为每条记录添加，不管有没有自定义主键和unique键）。

非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据，其中非主键索引也被称为二级索引，而主键索引也被称为聚簇索引。自定义主键时尽量选取所占空间小的数据

聚簇索引的特点：
1,按主键值的大小进行记录和页的排序：

• 数据页(叶子节点)里的记录是按照主键值从小到大排序的一个单向链表。
• 数据页(叶子节点)之间也是是按照主键值从小到大排序的一个双向链表。B+树中同一个层的页目录也是按照主键值从小到大排序的一个双向链表。

2, B+树的叶子节点存储的是完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建。InnoDB存储引擎会自动的为我们创建聚簇索引。在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

聚簇索引：聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。当我们想以别的列作为搜索条件时我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。
二级索引与聚簇索引有几处不同：

1. 按指定的索引列的值来进行排序
2. 叶子节点存储的不是完整的用户记录，而只是索引列+主键。
3. 目录项记录中不是主键+页号，变成了索引列+页号。
4. 在对二级索引进行查找数据时，需要根据主键值去聚簇索引中再查找一遍完整的用户记录，这个过程叫做回表
   

根据这两种结构我们来进行下查询，看看他们在查询上有什么区别。1、如果查询语句是 select * from table where ID = 100,即主键查询的方式，则只需要搜索 ID 这棵 B+树。
2、如果查询语句是 select * from table where k = 1，即非主键的查询方式，则先搜索k索引树，得到ID=100,再到ID索引树搜索一次，这个过程也被称为回表。

使用索引的意义在减少查询范围。原理在于比较。例如联合索引bcd，b=1 and c>1可以使用索引，而b>1 and c>1不能使用到索引。当根据二级索引查询成本占总成本（二级索引查询+回表成本）到一定比例时，还不如全表扫描。

使用order by语句时能不能利用索引，判断的就是order by能不能利用索引排序的结果。order by 后的字段的顺序和索引字段一致时才能利用到索引。那么group by呢？group by其实也会先排序，然后在根据排序后的分组。如果排序能利用到索引，那便能利用到索引

2. 为什么用 B+ 树做索引而不用哈希表做索引?
   1、哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据。
   2、如果我们要进行范围查找，例如查找ID为100 ~ 400的人，哈希表同样不支持，只能遍历全表。
   3、索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加。

B+树与完全二叉树的区别：B+树的每个节点存储多个数据，而且叶子里面的数据有指向下一条数据。B+树的非叶子节点在叶子节点都有冗余，叶子节点存储了所有的数据。

最左前缀原则：
由于在B+树结构的索引中，索引项是按照索引定义里面出现的字段顺序排序的如联合索引(bcd)，索引在查找的时候，可以根据条件b，c，d或者b（b作为联合索引的第一位，必须给，不然就无法使用索引快速查找）的值快速定位到数据，然后直接向右遍历所有符合条件的人，直到条件不满足为止。也就是说，我们找到第一个满足条件的数据之后，直接向右遍历就可以了，由于索引是有序的，所有满足条件的人都会聚集在一起。而这种定位到最左边，然后向右遍历寻找，就是我们所说的最左前缀原则。
如果只给出索引的非最左数据，则无法使用索引。

mysql中utf8默认是0_{3字节，而java是0}4字节。mysql中utf8mb4才是0~4字节。collation即比对方法，即排序规则，一般我们采用b>a

前缀索引不能在使用group by 和order by时提高查询速度