月度归档： 2017年6月

主键的特点

• 一个表中只能有一个主键，如果在其他字段上建立主键，则原来的主键就会取消

• 主键的值不可重复，也不能为空（NULL）

在InnoDB中，clustered index选择原则：

• 显示声明的主键

• 第一个不包含null列的唯一索引

• 内置的rowid

InnoDB中，主键特点：

• 如果索引最后没有主键，则会在索引内部存储时加上主键（只有主键key值，而不是整个row data）

• 如果索引最后已有主键，则会显式加上主键

• 因此只要用户定义的索引字段中包含了主键中的字段，那么这个字段就不会再被InnoDB自动加到索引中了，如果用户的索引字段中没有完全包含主键字段，InnoDB就会把剩下的主键字段加到索引末尾

唯一索引和主键索引的区别

相同点：他们都属于实体完整性约束

不同点：

1. 唯一性约束所在的列允许空值，但是主键约束所在的列不允空值

2. 可以把唯一性约束放在一个或多个列上，这些列或列的组合必须是唯一的，但是，唯一性约束所在的列并不是表的主键列

3. 唯一性约束强制在指定的列上创建一个唯一性索引，在默认情况下，创建唯一性的非聚簇索引，但是也可以指定所创建的索引是聚簇索引

4. 建立主键的目的是让外键来引用

5. 一个表最多只有一个主键，但可以有很多唯一键

深入理解索引

1. 索引基础理论知识

2. B+树索引

3. 哈希索引

4. 理解B+树，哈希索引结构及区别

5. 理解常见索引的基本概念：主键索引，唯一索引，普通索引，联合索引之间的区别

6. 理解MyISAM和InnoDB的索引结构区别

7. 理解如何通过索引提高SQL效率

binary search， 二分查找法，也叫折半查找（折半搜索，二分查找算法，二分搜索），是一种再有序数组中查找某一特定元素的搜索算法。

这种搜索算法每一次比较都使搜索范围减小一般，可在最坏的情况下用0（log n）完成搜索任务。

二分查找法的优点是比较次数少，查找速度块，平均性能好

缺点是要求待查表为有序表，且插入删除困难。

因此，折半查找方法适用于不经常变动而查找频繁的有序列表

binary tree， 二叉树

每个节点至多只有两颗子树（不存在度大于2的节点），二叉树的子树有左右有序之分，次序不能颠倒

平衡二叉树

1. 左右两个字数的高度差的绝对值不会大过1（<=1），且左右两个子树也是平衡二叉树

2. 不平衡树会通过自旋，变成平衡树

3. 平衡树和二叉查找的最大区别：前者是平衡的，后者未必

旋转后

用下面这些值来构造一个B树

ACGHEKQMFWLTZDPRXYS

1.构造一个4阶的B树，非根节点关键字数，小于2个就合并，大于4个就分裂

2.插入H

3.插入E,K,Q时不需要任何分裂操作,变成：

4.插入M之后就需要分裂了，M恰好时中间关键字元素，可以放在根节点

5.插入F,W,L,T不需要任何分裂操作

6.插入Z时，最右边的叶子节点空间满了，需要进行分裂操作，中间元素T上移到父节点中

7.插入D时，导致最左边的叶子节点被分裂，D是中间元素，上移到父节点

8.最后，当插入S时，第三个子节点需要分裂，但根节点已经满了，需要把根节点再次分裂，形成3层的B树

再来看下元素的删除

1.删除H，节点无需进行合并或分裂

2.删除T之后，最右边的子节点就没有父节点了，需要再找个新的元素放在其父节点

3.删除R

4.删除E之后，第一步变成了

可以把G,M,Q,X这4个元素提升到根节点，变成了

B+树是B树（B Tree）的变体，也是一种多路搜索树

1. 非叶子节点的子树指针与关键字个数相同

2. 为所有叶子节点增加一个链指针

3. 所有关键字都在叶子节点出现

二叉树，B树，B+树的小结

Binary树：二叉树，每个节点之存储一个关键字，等于则命中，小于则走左节点，大于走右节点

B树：多路搜索树，没个节点存储M/2到M个关键字，非叶子节点存储指向关键字范围的子节点

所有关键字在整颗树中出现，且只出现一次，非叶子节点可以命中

B+树：在B-树基础上，为叶子节点增加链表指针，所有关键字都在叶子节点中出现，非叶子节点左右叶子节点的索引，B+树总是到叶子节点才命中

经典B+树结构

==========================================

B+树插入新元素时，可能会遇到3种情况

1. 插入元素28（直接写入 叶子节点没有满）

2. 插入元素70

   

3.插入元素95

B+树删除元素时，也可能遇到3中情况

1. 从这个B+树中删除元素70，变成了

2.再删除元素25，变成了：

3.再删除元素60，变成了：

用Innodb的时候一定要注意

1. 要用顺序自增的主键

2. 主键可以删除（也不建议）但是一定不要更新它，更新后可能会导致很多碎片产生，建议用字段表示，而不是直接删除，这样高并发下，新增数据性能以及删除数据性能都会比较高，避免频繁合并和分裂

哈希索引

在mysql中，只有memory存储引擎支持显示的哈希索引

关键字key，经过哈希算法hash（key）后，产生一个精确的结果值

如果有多个关键字相同 产生的hash值也是相同的 有多个重复相同key 会把值放在同一个链表里面，在扫描到这个key的时候就会把链表里的值全部都去取出来

哈希索引和B+树索引的区别：

1. 先看下逻辑区别

   

索引优点

1.加快数据检索效率

2.可以创建唯一性约束索引，保证数据库中每一行数据的唯一性

3.加速表和表连接效率

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

索引缺点

1.索引需要占用更多物理存储空间

2.当对表中的数据进行增加，删除和修改的时候，索引野要更新维护，降低了数据维护效率

哪些情况下建议创建索引

1. 经常需要搜索的列

2. 作为主键的列，有唯一约束索引

3. 经常用在表连接的列

4. 经常需要排序的列

5. 经常使用再where字句中的列

6. 经常需要排序/分组的列

哪些情况下不建议创建索引

很不经常被搜索的列

基数值很低的列

长text、blob类型列

对比参考ORACLE索引类型

对比一下MySQL的索引基本知识都有哪些

MySQL 聚集索引建议

PRIMARY KEY， 主键索引

唯一索引（约束）（unique key, UNIQUE Index Constraints）

联合索引 Combined Indexes，Multiple-Column Indexes

覆盖索引 covering indexes

最左索引/部分索引 （prefix indexex）

外键/约束（FIREUGN KEY Constraints）

MyISAM主键索引

InnoDB的主键索引和辅助索引

[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键

[MySQL优化案例]系列 — InnoDB主键设计

Infobright是开源的MySQL数据仓库解决方案，引入了列存储方案，高强度的数据压缩优化的统计计算（类似sum/avg/group by之类），列式存储 对单列处理效率非常高 如果涉及到多列相对较慢。

Infobright的默认存储引擎是brighthouse，但是Infobright还可以支持其他的存储引擎，比如MyISAM，MRG_MyISAM， Memory，CSV

企业版增加了DML支持，同时有一些额外的工具集

社区版不支持DML，没有在线热备工具

Infobright优点

1. 高压缩比率，平均压缩比可达10:1，甚至可以达到40:1。

2. 列存储，及时数据量十分巨大，查询速度也很快。用于数据仓库，处理海量数据没一套可不行。

3. 不需要建索引，就避免了维护索引及索引随着数据膨胀的问题。把每列数据分块压缩存放，每块有知识网格节点记录块内的统计信息，代替索引，加速搜索。

4. 单一台服务器可以高效的读写30T数据。具有可扩展性，这里是指对于同样的查询，当数据量是10T时，它耗费的时间不应该比1T数据时慢太多，基本是一个数量级内。

缺点：

1. 不支持DML

2. 不支持多核

3. 不支持分布式

loader与unloader是infobright的数据导入导出模块，也即处理SQL语句里LOAD DATA INFILE ··· 与 SELECT “` INTO FILE任务，由于infobright面向的是海量数据环境

所以这个数据导入导出模块是一个独立的服务，并非直接使用muysql的模块

逻辑层的infobright优化器包再mysql查询优化器的外面，因为它的存储层有一些特殊结构，所以查询优化方式也跟mysql有很大差异

存储层最底层是一个个的Data Pack（数据块）。每一个pack装着某一列的64K个元素，所有数据按照这样的形式打包存储，每一个数据块进行类型相关的压缩（即根据不同类型采用不同的压缩算法），压缩比很高。它上层的压缩器与解压缩器就做了这个事情。

Knowledge Grid（知识网格）中包含两类节点：

• 每个Data Pack Node(数据包节点)对应一个Data Pack，存储改Data Pack的一些统计信息，如min，max，avg，null个数，单元总数count，sum总数等，甚至不同值的量等等；

• KN（Knowledge Node,知识节点）则存储了一些更高级的统计信息以及与其他表的连接信息，这里面的信息有些事是数据载入时已经算好的，有些事随着查询进行而计算的，所以说是具备一定的“智能”的

KN里面存储着指向DP之间或者列之间关系的一些元数据集合，比如值发生的范围（Min_Max），列数据之间的关联。大部分的KN数据是装载数据的时候产生的，另外一些是查询的时候产生。

Histogram用来提高数字类型（比如data，time，decimal）的查询性能。Histogram是装载数据的时候就产生的。

DPN中有mix，max，Histogram中把Min-Max分成1024段，如果Min_Max范围小于1024的话，每一段就是一个单独的值。

这个时候KN就是一个数值是否在当前段的二进制表示。

CMAP是针对于文本类型的查询，也是装载数据的时候就产生的。CMAP是统计当前DP内，ASCII在1-64位置出现的情况。如下图所示

比如说上面的图说明了A再文本的第二个，第三个，第四个位置从来没有出现过。0表示没有出现，1表示出现过。

查询中文本的比较归根究底还是按照字节进行比较，所以根据CMAP能狗很好的提高文本查询的性能。

Pack-To-Pack（P-2-P）是join操作的时候产生的，它是表示join的两个DP中操作的两个列之间关系的位图，也就是二进制表示的矩阵。

这个sql在innodb里跑了2.96秒

infobright vs innodb

0.29s vs 2.96s

• innodb_buffer_pool

• innodb_additional_mem_pool_size

• innodb_log_buffer_size

这3个内存参数是可见的，可控的

还有一些是看不见，不可控的靠mysql内部管理的：

• adaptive index hash

• system dictionary hash

• locking system

• sync_array

• os_events

Innodb内存结构

innodb在5.6以前undo 是放在共享表空间里面的

在高并发的时候，可能会导致ibdata1文件持续增长

ibdata1这个共享表空间又是不能自动释放的 

如果在高并发一直没有提交有大量事务等待回滚的时候会把大量脏数据放在undo里面

所以会导致ibdata1特别庞大 特别是32位的系统下

需要我们及时提交事务 不要把大量事务堆积在innodb redo

或者在初始化的时候把ibdata1初始化大一些 最好在1G以上

5.6可以有独立undo 但是很麻烦 不建议使用

5.7可以设置undo大小和路径、

1.innodb_buffer_pool

• Innodb高速缓冲（简称IBP），是Innodb最重要的组成部分

• InnoDB不依赖OS,而自己缓存了所有数据，把索引和数据（包括索引数据，行数据，等等）放在内存里面。这点跟MyISAM有差别MyISAM只是把索引放在key buffer里面，数据还是用OS的page cache进行缓存

• 应该把innodb_buffer_pool_size设置得大一些，建议设置为可用RAM的50%~80%

• 如果前端使用连接池 那么设置能70%会相对安全一点，因为前端要有连接池 就不会有特别大的连接连接进来，PGA分配的内存相对就没那么大，所以我们可以留更多的内存给SGA

• 但是前端如果有大量连接并发的时候，我们建议innodb_buffer_pool_size设置小一点 50%

• 查询或更新的时候会对IBP加锁，影响并发，innodb会把内存分成多分，来缓解并发带来的影响

• IBP有一块buffer用于插入缓冲，在插入的时候，先写入内存，之后再合并后顺序写入磁盘。在合并到磁盘上的时候，会引发较大的IO操作，对实时操作造成影响（看上去是抖动，tps变低）

• show global status like 'innodb_buffer_pool_%' 查看IBP状态们单位是page(16kb)

• Innodb_buffer_pool_wait_free 如果较大，需要加大IBP设置

2. innodb_buffer_pool_instances 

        如果内存不超过8G，就没有必要分多个instance

        分多个instance时，最好每个instance至少2G以上8G以下

        innodb_buffer_pool_instances也不要太多，一般不超过8

innodb_buffer_pool 现在默认值是128M（老版本默认8M，坑很多）

[MySQL FAQ]系列 — 数据不算大，备份却非常慢

http://imysql.com/2009/09/18/mysql-faq-why-backup-is-so-slow.html

3.Adaptive Hash Index

自适应哈希索引，用来管理buffer pool的哈希索引

• adaotive index hash, size= innodb_buffer_pool / 64, 随着buffer的频繁更新，会随之上升

• system dictionary hash, size=innodb_buffer_size / 256，基本固定

• memory for sync_array, which is used for syncronization primitives, size=OS_THREADS（当前线程数） * 152

• memory for os_events, which are also used for syncronization primitives, size=OS_THREADS * 216

• memory for locking system, size = 5*4*NBLOCKS(NBLOCK, innodb buffer pool的block数量)，随着并发、行锁增加，会随之上升

概念：

• OS_THREADS=如果innodb_buffer_pool_size >= 1000Mb, 则为:50000, 否则如果innodb_buffer_pool_size >= 8Mb,则为:10000, 否则为:1000（*nixes平台下通用）

• NBLOCKS = innodb_buffer_pool_size/8192

如果innodb_buffer_pool > 1000MB, OS_THREADS*368=17.5MB

否则如果innodb_buffer_pool > 8MB , 3,5m

我们只能设置innodb_buffer_pool_size为一个合适值 因为其他几个我们没有办法控制

4.innodb_additional_mem_pool_size

数据字典以及内部数据结构缓存，表数据量越多，相应的内容需要越大。

默认8M，通常设置为8~32M足够，一般建议设置为16M，如果确实不够，那么会从系统中请求增加分配内存，并且错误日志中会提醒，目前至少还未发生过。

5.innodb_log_buffer_size

show global status查看Innodb_log_waits是否大于0， 是的话，就需要提高innodb_buffer_pool_size，否则维持原样。

show global status查看30~60秒钟Innodb_os_log_written 的间隔差异值，即可计算出innodb_buffer_pool_size设置多大合适。

每个客户端连接时，用以维持连接缓冲以及读取结果缓冲。

初试分配预设值，在有需要时，则会自动扩大到max_allowed_packet大小，然后再回收到预设的net_buffer_length大小。

最小1K， 最大1M，默认是16K。

mysqldump时，没个extended insert 的行长度，不能超过net_buffer_length的值

专用于MyISAM引擎

用一个特别的类似树型结构体缓存

用于提高INSERT…SELECT, INSERT…VALUES(…),(…)以及LOAD DATA写数据到非空表的情景

每个线程分配，默认8M，可以设置为0来关闭这个buffer

从MySQL5.1开始，数据表文件描述符呗分开为数据文件及数据表定义文件两部分。

表定义文件缓存可以放在专属的table_definition_cache中。

表定义文件缓存比表文件描述符缓存消耗的内存更小，其默认值是400。

状态值：

Open_table_definition:表定义文件.frm被缓存的数量

Opened_table_definition:历史上总共被缓存过的.frm文件数量。

.frm文件只能同时打开一次

.MYD .MYI文件，则是没个线程都打开一次

innodb也是一样

table_cache就是缓存文件打开的描述符 使得我们打开数据文件更快

几个关于table_cache的状态值

Open_tables:当前打开的表的数量

Opened_tables:历史上全部已经打开的表总数，如果Open_tables较大，table_open_cache值可能需要加大