mysql底层知识

##基础知识

文章来源:https://www.jianshu.com/p/47664afa249e

Mysql的常用引擎

1.Innodb(更适合高并发场景)
	Innodb的存储文件有两个,后缀名分别为.frm和.idb,其中.frm是表的定义文件,而.idb是数据文件
	Innodb中存在表锁和行锁,不过行锁是在命中索引的情况下才会起作用
	Innodb支持事务,且支持四种隔离级别(读未提交,读已提交,可重复读,序列化),默认为可重读读;而在oracle数据库中,只支持序列化和读已提交这两种级别,默认为读已提交
2.Myisam
	Myisam的存储文件有三个,后缀名分别为.frm,.MYD,.MYI.其中.frm是表的定义文件,MYD是表的数据文件,MYI是索引文件.
	Myisam只支持表锁,且不支持事务.Mysiam由于有单独的索引文件,在读取数据方面性能更高
3.存储结构
	Innodb和Myisam都是用B+Tree来存储数据的

Mysql的数据、索引存储结构

1.数据存储的原理(硬盘)
	信息存储在硬盘里,硬盘是有很多的盘片组成,通过盘片表面的磁性物质来存储数据.盘片表面是凹凸不平的,凸起的地方磁化,代表数字1,凹的地方被磁化,代表数字0,因此硬盘是通过二进制来存储数字,文字等信息的
2.数据读写的原理
	硬盘在逻辑上被分为磁道,柱面以及扇区.
	磁头靠近主轴接触的表面,即线速度最小的地方,是一个特殊的区域,它不存放任何数据,称为启停区或者着陆区,启停区外就是数据区
	在最外圈,离主轴最远的地方就是0磁道,硬盘数据的存放就是从最外圈开始的
	在硬盘中还有一个叫0磁道检测器的构件,用来完成磁盘的初始定位
3.磁盘的读写原理
	系统将文件存储到磁盘上时,按柱面、磁头和扇区的方式进行，即最先是第一磁道的第一磁头下的所有扇区，然后是同一柱面的下一个磁头
	一个柱面存储满后就推进到下一个柱面，直到把文件内容全部写入磁盘
	系统也以相同的顺序读出数据，读出数据时通过告诉磁盘控制器要读出所在扇区所在柱面、磁头号和扇区号进行
4.减少i/o的预读原理
	由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动消耗的时间。磁盘的存取速度往往是主存的几百分之一，因此为了提高效率尽量减少磁盘的io。
	磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。
5.mysql的索引
	索引是一种用来实现Mysql高效获取数据的数据结构
	我们通常所说的在某个字段上建索引，意思就是让mysql对该字段以索引这种数据结构来存储，然后查找时有对应的查找算法。
	建立索引的目的是为了查找的优化，一般的查找算法有顺序查找，折半查找，快速查找。每种查找算法都用于特定的数据结构。如顺序查找以来于顺序结构，折半查找通过二叉查找树黑着红黑树实现二分搜索。因此在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这种数据结构以某种方式引用数据，这样就可以在这些数据结果上实现高级查找算法，这种数据结构就是索引。

Mysql中的B+Tree

目前大多数的数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构
B+树索引是B+树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引，B表示平衡而非二叉。
B+Tree是由平衡二叉树演变而来,B+Tree是由二叉查找树(左子树的键值小于根节点键值,右子树的键值大于根的键值),平衡二叉树(在二叉查找树的条件下,还满足任何节点的两个子树高度最大差为1),平衡多路查找树(为磁盘外存储设备设计的一种平衡查找树)逐步优化过来的
系统从磁盘读取数据到内存时是以磁盘块为基本单位的,位于同一磁盘块中的数据会被一次性取出来,而不是按需读取
Innodb使用页作为数据读取单位,页是其磁盘管理的最小单位,默认page大小是16k
系统中一个磁盘的大小往往没有那么大,因此Innodb每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16kb
Innodb在把磁盘数据读入到磁盘时会以页作为基本单位,在查询数据时如果一个页中的每条数据都能助于定位数据记录的位置,这将会减少磁盘I/O的次数,提高查询效率
B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块
为了描述B-Tree,首先定义一条数据记录为一个二元组[key,data],key为记录的键值,对于不同数据记录,可以是互不相同的,data为数据记录除key外的数据
B-Tree的每个节点根据实际情况可以包含大量的关键字信息和分支
每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的关键字个三个只想子树根节点的指针,指针存储的是子节点所在磁盘块的地址.
两个关键词划分成的三个范围域对应三个指针只想的子树的数据的范围域
以根节点为例,关键字为17和35,p1指针指向的子树的数据范围为小于17,p2指针指向的子树的数据范围为17-35,p3指针指向的为大于35

####B-Tree

查找关键字29的过程:

​ 1.根据根节点找到磁盘1,读入内存(磁盘i/o操作一次)

​ 2.根据关键字29在区间(17,35),找到磁盘块1的指针p2

​ 3.根据p2指针找到磁盘块3,读入内存,磁盘i/o操作第二次

​ 4.比较关键字29在区间(26,30),找到磁盘块3的指针p2

​ 5.根据p2指针找到磁盘块8,读入内存,磁盘i/o操作第三次

​ 6.在磁盘块8的关键字列表中找到关键字29

Mysql的Innodb存储引擎在设计时是将根节点常驻内存的,因此力求达到树的深度不超过3,也就是I/O不需要超过3次

根据上面的过程可以发现需要3次磁盘I/O操作,和3次内存查找工作.由于内存中的关键字是一个有序表结构,可以利用二分法查找提高效率,因而3次磁盘的I/O操作是影响整个B-tree查找效率的决定性因素

B-Tree 相对于平衡二叉树缩减了节点个数,使每次I/O取到内存的数据都发挥了作用,从而提高了查询效率

B+Tree是在B-Tree的基础上的一种优化,使其更适合实现外存储索引结构,Innodb存储引擎就是用B+Tree实现其索引结构

在B-Tree中,每个节点中有key,也有data,而每一个页的存储空间有限,如果data数据较大时会导致每个节点(即一个页)能存储的的key数量很小

在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子结点上,

而非叶子结点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度

B+Tree和B-Tree的区别

​ 1.数据是存在叶子节点上中的

​ 2.数据节点间是有指针指向的

Myisam中的B+Tree

Myisam引擎也是采用的B+Tree结构作为索引结构

由于Myisam中的索引和数据存放在不同的文件,所以在索引树中的叶子结点中存的数据是该索引对应的数据记录的地址,由于数据和索引不在一起,所以Myisam是非聚簇索引

(叶子结点存放对应数据的物理地址)

#####Innoodb中的B+Tree

Innodb是以ID为索引的数据存储

采用Innodb引擎的数据存储文件有两个,一个定义文件,一个数据文件

Innodb通过B+Tree结构对ID建索引,然后通过叶子结点中存储记录

若建索引的字段不是主键ID,则对该字段建索引,然后在叶子节点中存存的是该记录的主键,然后通过主键索引找到对应记录

#####Mysql的相关优化

1.Mysql性能优化:组成,表的设计

​ ①开启查询缓存.避免某些sql函数直接在sql语句中使用,从而导致Mysql缓存失效

​ ②目的是什么就取什么,能查一条判断的就不要全取

​ ③建合适的索引,所以要建在合适的地方,合适的对象上,经常操作/比较/判断的字段应该建索引

​ ④字段大小要适宜.字段的取值是有限而且固定的,这种情况下可以使用enum,ip字段介意用unsigned int来存储

​ ⑤表的设计.垂直分割表,使得固定表与边长表分割,从而降低表的复杂度和字段的数目

2.SQL语句优化:避免全表扫描

​ 1.建索引 一般在where及order by中涉及到的列上建索引,尽量不要对可以重复的字段建索引.

​ 2.尽量避免在where中使用!或or也不要进行null值判断

​ 3.尽量避免在where中对字段进行函数操作、表达式操作

​ 4.尽量避免使用llike %，在这种情况下可以进行全文检索

Mysql基准测试

原因:基准测试可以观察系统在不同压力下的行为,评估系统的容量,掌握哪些是重要的变化,或者观察系统如何处理不同的数据

######基准测试的策略:

​ 针对整个系统的测试(集成式full-stack)

​ 单独测试Mysql(单组件式single-component)

测试的指标

​ 1.吞吐量,指单位时间内的事务处理数,常用的测试单位是每秒事务级(TPS),或每分钟事务数(TPM)

​ 2.响应时间或者延迟,用于测试任务所需的整体时间,根据具体的应用,测试的时间单位可能是微秒毫秒秒或者分钟.通常使用百分比响应时间来代替最大响应时间

​ 3.并发性,需要关注的是正在工作中的并发操作,或者是同时工作中的线程数或者连接数.在测试期间记录Mysql数据库的Threads_running状态值

​ 4.可扩展性,给系统增加一倍的工作,在理想状态下就能获得两倍的效果(即吞吐量增加一倍),对于容量规范非常有用,可以提供其他测试无法提供的信息

基准测试方法

1.需要避免的一些常见错误:

​ 使用真实数据的子集而不是全集

​ 使用错误的数据分布

​ 使用不真实的分布参数

​ 在多用户的场景中,只做单用户测试

​ 在单服务器上测试分布式应用

​ 与真实用户行为不匹配

​ 反复执行同一个查询

​ 没有检查错误

​ 忽略了系统预热(warm up)过程

​ 使用默认的服务器配置

​ 测试时间太短

2,应该建立将参数和结果文档化的规范,每一轮测试都必须进行详细记录

3.基准测试应该运行足够长的时间,需要在稳定状态下测试并观察

4.在执行基准测试时.需要尽可能多地收集被测试系统的信息

5.自动化基准测试可以防止测试人员偶尔遗漏某些步骤,或者误操作,宁外也有助于归档整个测试过程

剖析mysql查询

1.剖析服务器负载
	·慢查询日志  long_query_time为0可以捕获所有的查询,查询的响应时间单位可以做到微秒级
	·生成剖析报告  pt-query-digest
2.剖析单条查询
	show profiles
	show global status

诊断间歇性问题

1.尽量不要用试错的方式来解决问题,如果一时无法定位,可能是测量的方式不准确,或者测量的点选择有误,或者使用的工具不合适
2.确定单条查询问题还是服务器问题
	show global  status
	show processlist
	使用查询日志
	理解发现的问题,使用gnuplot或R,或其他绘图工具将结果绘制成图形
3.捕获诊断数据
	诊断触发器:在问题出现时能够捕获数据的基础,有两个常见问题可能导致无法达到预期的结果:误报或者漏检
	收集数据:尽可能收集所有能收集的数据,但只在需要的时间段内收集,oprofile,strace,tcpdump,pt-collect,pt-stalk
	解释结果数据:pt-mysql-summary输出结果打包....

Schema与数据类型优化

schema就是数据库对象的集合，所谓的数据库对象也就是常说的表，索引，视图，存储过程等。
在schema之上的，就是数据库的实例，也就是通常create databases获得的东西
选择优化的数据类型
	1.数据类型的选择原则
		更小的通常更好
		简单就好
		尽量避免null
	2.应该尽量只在对小数进行精确计算时才使用decimal,使用int类型通过程序控制单位效果更好
	3.使用varchar合适的情况,字符串列的最大长度比平均长度大很多;列的更新很少
	4.char适合存储很短的字符串,或者所有值都接近同一个长度,不容易产生碎片,在存储空间上更有效率
	5.通常应该尽量使用timestamp,它比datetime空间效率更高

加快alter table操作的速度

1.在一台不提供服务的机器上执行alter  table操作,然后和提供服务的主库进行切换
2.通过影子拷贝,创建一张新表,然后通过重命名和删表操作交换两张表及里面的数据

快速创建Myisam索引,先禁用索引,导入数据,然后重新启用索引

创建高性能的索引

一.索引基础
	1.索引可以包含一个或多个列的值,如果索引包含多个列,那么列的顺序也十分重要,因为mysql只能高效地使用索引的最左前缀列
	2.orm工具能够产生符合逻辑,合法的查询,除非只是生成非常基本的语句,否则很难生成适合索引的查询
	3.在mysql中,索引是在存储引擎层而不是服务器层实现的,所以,并没有统一的索引标准;不同存储引擎的索引的工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引
	4.B-Tree意味着所有的值都是按顺序存储的,并且每一个叶子页到根的距离相同,能够加快访问数据的速度,从索引的根节点开始搜索,适用于全键值\键值范围或键前缀查找
	5.B-Tree索引的限制:
		如果不是按照索引的最左列开始查找,则无法使用索引
		不能跳过索引中的列
		如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找
	6.哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才会有效,只有memory引擎支持哈希索引
	7.哈希索引的限制:
		哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行
		哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序
		哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的
		只支持等值比较查询,不支持任何范围查询
		访问哈希索引的数据非常快,除非有很多哈希冲突
		如果哈希冲突很多的话,一般索引维护的代价也会很高
	8.空间数据索引(r-Tree),myisam支持空间索引,可以使用地理数据存储
	9.全文索引,试用与Match against操作,而不是普通的where条件操作
二.索引的优点
	1.三个优点
		①索引大大减少了服务器需要扫描的数据量
		②索引可以帮助服务器避免排序和临时表
		③索引可以将随机I/O变成顺序I/O
	2.索引三星系统:
		①索引将相关的记录放到一起则获得一星
		②如果索引中的数据顺序和查找中的排序一致则获得二星
		③如果索引中的列包含了查询中需要的全部列则获得三星
三.高性能的索引策略
	1.独立的列:如果查询中的列不是独立的,则MYSQL不会使用索引.独立的列是指索引列,不能是表达式的一部分,也不能是函数的参数
	2前缀索引和索引选择性
		①通常可以索引开始的部分字符,可以大大节约索引空间,但也会降低索引的选择性
		②索引的选择性是指,不重复的索引值和数据表的记录总数的比值.选择性越高则查询效率越高,因为选择性高的索引可以让mysql在查找到时候过滤掉很多的行
		③mysql无法使用最左前缀索引做order by 和group by,也无法做覆盖扫描
	3.选择合适的索引列顺序
		①正确的索引列顺序依赖于使用该索引的查询,并且同时需要考虑如何更好地满足排序和分组的需要
		②在一个多列B-Tree索引中,索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列
		③将选择性最高的列放到索引最前列
	4.聚簇索引:并不是一种单独的索引类型,而是一种数据存储方式
		(最好避免随机的聚簇索引,特别是对于I/O密集型的应用)
	5.覆盖索引:如果一个索引包含所有要查询的字段的值,称为覆盖索引
		覆盖索引必须要存储索引列的值
	6.压缩(前缀)索引,默认值压缩字符串,减少索引的大小,对于cpu密集型应用没因为扫描需要随机查找,压缩索引在myisam上要慢好几倍
	7.重复索引是指在相同的列上按照相同的熟悉顺序创建的相同类型的索引,应该尽量避免这样重复的创建索引
	8.索引可以让查询锁定更少的行
四.维护索引和表
	1.check table 表名 检查表是否损坏
	2.altet table 表名 engine = Innodb  修复表
	3.show index from 表名  查看索引的基数
	4.b-tree可能会碎片化,降低查询的效率

五.查询性能优化

1.为什么查询速度会变慢
	①如果要优化查询,实际上要优化其子任务,要么消除其中一些子任务,要么减少子任务的执行次数,要么让子任务运行的更快
    ②查询的生命周期大致可以按照顺序来看:从客户端,到服务器,然后在服务器上进行解析,生成执行计划,执行,并将结果返回给客户端
2.慢查询基础:优化数据访问
	1.两个分析步骤:
    	①确认应用程序是否检索大量超过需要的数据
		②确认Mysql服务器层是否在分析大量超过需要的数据行
	2.是否向数据库请求了不需要的数据
		①查询了不需要的数据
		②多表关联并返回全部列
		③总是取出全部列
		④重复查询相同的数据
	3.mysql是否在扫描额外的记录
		1.查询开销三个指标:响应时间,扫描行数,返回的行数
             ②响应时间:服务时间和排队时间之和
             ③扫描的行数:较短的行的访问速度更快,内存中的行也比磁盘中的行的访问速度要快的多
             ④访问的类型:explain中的type列反应了访问类型,通过增加合适的索引.
		2.三种方式应用where条件,在索引中使用where条件来过滤不匹配的记录;使用索引覆盖扫描来返回记录,直接从索引中过滤不需要的记录并返回命中结果;从数据表中返回数据,然后过滤不满足条件的记录
		3.需要扫描大量数据但只返回少数行的优化技巧:使用索引覆盖扫描,改变库表结构,重写复杂的查询
3.重构查询的方式
	①Mysql从设计上让连接和断开连接都很轻量级,再返回一个小的查询结果方面很高效
	②切分查询,将大查询切分成小查询.每个查询功能完全一样,只完成一小部分,每次只返回一小部分查询结果,可以避免锁住很多数据,占满事务日志,耗尽系统资源,阻塞很多小的但重要的查询
	③分解关联查询优势:
		1.让缓存的效率更高
		2.将查询分解后,执行单个查询可以减小锁的竞争
		3.在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展
		4.查询效率本身效率也可能会有所提升
		5.可以减少冗余记录的查询
		6.相当于在应用中实现了哈希关联,而不是使用Mysql的嵌套循环关联
	④分解关联查询的场景	
		1.当应用能够方便的缓存单个查询的结果的时候
		2.当可以将数据分布到不同的MYSQL服务器上的时候
		3.当能够使用IN()的方式代替关联查询的时候
		4.当查询中使用同一个数据表的时候
4.查询执行的基础
	①查询执行的路径
		客户端发送一条查询给服务器
		服务器先检查查询缓存,如果命中则立刻返回,否则进入下一阶段
		服务器端进行sql解析,预处理,再由优化器生成对应的执行计划
		mysql根据优化器生成的执行计划,调用存储引擎的api来执行查询
		将结果返回给客户端
	②Mysql客户端和服务器之间的通信协议是半双工的,无法将一个消息切成小块独立来发送,没法进行流量控制,一旦一端开始发生消息,宁一端要接受完整个消息才能响应它
	③mysql通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源,所以接收全部结果并缓存通常可以减少服务器的压力
	④语法解析器和预处理,通过关键字将sql语句进行解析,并生成一颗对应解析树,解析器将使用MYSQL语法规则验证和解析查询,预处理器则根据一些mysql规则进一步检查树是否合法
	⑤查询优化器,找到最好的执行计划,使用基本成本的优化器,将尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个,使用show status like 'last_query_cost';查看需要多少个数据页的随机查找
	⑥导致查询优化器选择错误的原因
		1.统计信息不准确,Innodb不能维护一个数据表的行数的精准统计信息
		2.执行计划中的成本估算不等同于实际执行的成本
		3.Mysql的最优跟你想的最优可能不太一样
		4.mysql从不考虑其他并发执行的语句
		5.MYSQL也并不任何时候都是基于成本的优化
		6.Mysql不会考虑不受控制的操作的成本
		7.优化器有时候无法去估算所有可能的执行计划
	⑦mysql能处理的优化类型
		1.重新定义关联表的顺序
		2.将外链接转换为内链接
		3.使用等价变换规则
		4.优化count(),min(),max(),在explain可以看到'select tables optimized away'
		5.预估并转化为常数表达式,当检测到一个表达式可以转换为常数的时候,就会一直把该表达式作为常数进行优化处理
		6.覆盖索引操作,当索引中的列包含所有查询中需要使用的列的时候,就可以使用索引返回需要的数据,而无需查询对应的数据行
		7.子查询优化
		8.提前终止查询,在发现已经满足查询需求的时候,在发现已经满足查询需求的时候,Mysql总是能够立刻终止查询
		9.等值传播,如果两个列的值通过等式关联,那么mysql能够把其中一个列的where条件传递到宁一个列上
		10.列表in()的比较,mysql将in()列表中的数据先进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件
	⑧对于union查询,mysql先将一系列的单个查询结果放到一个临时表中,然后再重新读出临时表数据来完成union查询
	⑨无论如何排序都是一个成本很高的操作,所以从性能角度考虑,应尽可能避免排序或者尽可能避免对大量数据进行排序
	⑩当不能使用索引生成排序结果的时候,MYSQL需要自己进行排序,如果数据量小则在内存中进行,如果数据量大则需要使用磁盘,Mysql将这个过程称为文件排序,及时完全是内部排序不需要任何磁盘文件也是如此.

查询优化器

一.Mysql查询优化器的局限性
	1.关联子查询:Mysql的子查询实现起来非常糟糕,最糟糕的一类查询是where条件中包含in()的子查询语句.使用group_concat()在in()中构造一个由逗号分隔的列表,或者使用exists()来改写
     2.union的限制,有时,Mysql无法将限制条件从外层下推到内层,这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上
    3.Mysql不支持哈希关联,mariadb已经实现了哈希关联
    4.MYSQl无法利用多核来进行并行查询
    5.Mysql不支持松散索引扫描,5.0后的版本在分组查询中需要找到分组的最大值和最小值时可以使用松散索引扫描
    6.对于min和max.mysql优化的并不好
二.优化特定类型的查询
	1.优化count()查询
    ·count()是一个特殊的函数,有两种非常不同的作用:可以统计某个列值的数量,也可以统计行数,在统计列值时要求列值是非空的(不统计null)
   	·count(*)并不是会像我们猜想的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计所有的行数,当mysql确认括号内的表达式不可能为空时,实际上就是在统计行数
    Myisam的count函数只有没有任何where条件下的count(*)才非常快
    使用近似值,如explain出来的优化器估算行数
    使用索引覆盖
    使用汇总表
    使用外部缓存系统
	2.优化关联查询
	确保on或者using子句的列上有索引
    确保任何的groupby和order by中的表达式只涉及到一个表中的实例
    当升级mysql的时候需要注意:关联语法,运算符优先级等其他可能会发生变化的地方
    3.优化子查询,尽可能使用关联查询代替
4.优化GROUP BY和DISTINCT
使用索引优化
当无法使用索引时，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组
尽可能的将WITH ROLLUP（超级聚合）功能移动应用程序中处理
5.优化LIMIT分页
最简单的办法是尽可能地使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列，select id,name,…… from table innert join (select id from table order by xxx limit 5000,5) as table1 USING(id);
offset会导致MySQL扫描大量不需要的行然后再抛弃掉，如果可以记录上次取数据的位置，下次就可以直接从该记录的位置开始扫描，可以避免使用offset
使用预先计算的汇总表，或者关联到一个冗余表
6.优化UNION查询
    通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好地使用，经常需要手工地将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中
    除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL

######Mysql高级特性

一.分区表
	1.对用户来说分区表是一个独立的逻辑表,但是底层由多个物理子表组成,实际上是对一组底层表的句柄对象的的封装
	2.使用场景:
		·表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他均是历史数据
		·分区表的数据更容易维护
		·分区表的数据可以分布在不同的物理设备上,从而高效的利用多个硬件设备
		·可以使用分区表来避免某些特殊的瓶颈
		·如果需要,还可以备份和恢复独立的区
	3.使用限制
		·一个表最多只能有1024个区
		·在Mysql5.1中,分区表达式必须是整数,或者返回证书的表达式.在mysql5.5中,某些场景中可以直接使用列来进行分区
		·如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来
		·分区表无法使用外键约束
	4.使用分区表
		当数据量超大的时候,B-Tree索引就无法起作用了,除非是覆盖索引查询,否则数据库服务器需要根据索引扫描返回的结果,查询所有符合条件的记录,如果数据量巨大,将产生大量随机I/O
	5.保证大数据量的可扩展性的策略
		·命题扫描数据,无需使用任何索引
		·索引数据并分离热点
	6.分区策略的问题
		·NULL值会使分区过滤无效
		·分区列和索引列不匹配
		·选择分区的成本可能会很高
		·打开并锁住所有底层表的成本可能很高
		·维护分区的成本可能很高
		·所有分区都必须使用相同的引擎存储
		·分区函数中可以使用的函数和表达式也有一些限制
		·某些引擎不支持分区
		·对于Myisam的分区表,不能再使用load  index  into cache操作
		·对于Myisam表,使用分区表时需要打开更多的文件描述符
	7.查询优化
		很重要的一点是要在where条件中带入分区列
		只能在使用分区函数的列本身进行比较时才考虑分区,而不能根据表达式的值去过滤分区,及时这个表达式是分区函数也不行
		
二.视图
	1.视图本身是一个虚拟表,不存放任何数据,返回的数据是从mysql从其他表生成的
	2.Mysql使用两种算法:合并算法和临时表算法,会尽可能的使用合并算法
	3.如果视图中包含groupby\distinct\任何聚合函数\union\子查询等.只要无法在原表记录和试图记录中建立一一映射的场景中,Mysql都将使用临时表算法来实现视图
	4.可更新视图是指可以任何通过更新这个视图来更新视图涉及的相关表,check option表示任何通过视图更新的行,都必须符合视图本身的where条件定义
	5.在重构schema的时候可以使用视图,使得在修改底层表结构的时候,应用代码还可能继续不报错的运行
	6.mysql中不支持物化视图(指将视图结果数据存放在一个可以查看的表中,并定期从原始表中刷新到这个表中)
	7.不会保存视图定义的原始SQL语句
	
三.外键约束
	1.使用外键是有成本的,通常要求每次在修改数据时都要在宁外一张表中多执行一次查找操作
	2.如果想确保两个关键表始终有一致的数据,那么使用外键比在应用程序中检查一致性的性能要高得多,在相关数据的删除和更新上,比在应用中维护更高效
	3.外键会带来很大的额外消耗

四.在Mysql内部存储代码
	1.mysql允许通过触发器,存储过程,函数的形式来存储代码,从5.1开始还可以在定时任务中存放代码,这个定时任务称为时间.存储过程和存储函数都被统称为存储程序
	2.存储代码的优点:
		·他在服务器内部执行,离数据最近,宁外在服务器上执行还可以节省带宽和网络延迟
		·它是一种代码复用,可以方便统一业务规则,保证某些行为总是一致的,所以也可以为应用提供一定的安全性
		·它可以简化代码的维护和版本更新
		·可以帮助提升安全,比如提供细粒度的权限控制
		·服务器端可以缓存存储过程的执行计划,这对于需要反复调用的过程,会大大降低消耗
		·因为是在服务器端部署的,所以可以备份,维护都可以在服务器端完成
		·可以在应用开发和数据库开发人员之间更好地分工
	3.存储代码的缺点
		·Mysql本身没有提供好用的开发和调试工具
		·较之应用程序的代码,存储代码效率稍微差点
		·存储代码可能会给应用程序代码的部署带来额外的复杂性
		因为存储程序都部署在服务器内,所以可能有安全隐患
		存储过程会给数据库服务器增加额外的压力,而数据库服务器的扩展性相比应用服务器要差很多
		mysql并没有选项可以控制存储程序的资源消耗,所以在存储过程的一个小错误,可能会直接把服务器拖死
		存储代码在mysql中的实现也有很多的限制--执行计划缓存是连接级别的,游标的物化和临时表相同,异常处理也非常困难
		调试mysql的存储过程是一件非常困难的事情
		他和基于语句的二进投影日志复制合作的并不好
	4.存储过程好而寒暑限制
		·优化器无法使用关键字DETERMINISTIC来优化单个查询中多次调用存储函数的情况
		·优化器无法评估存储函数的执行成本
		·每个连接都有独立的存储过程的执行计划缓存
		·存储过程和复制是一种诡异的组合
	5.触发器:可以让你在执行insert\update\delete的时候,执行一些特定的操作,可以在mysql中指定是在授权率语句执行前触发还是执行后触发,可以使用触发器实现一些强制限制,或者某些业务逻辑,否则就需要在应用程序中实现逻辑
	6.触发器的注意和限制:
		·对于每一个表的每一个事件,最多只能定义一个触发器
		·只支持基于行的触发,也就是说,触发器是针对一条记录的,而不是针对整个授权率语句的,如果变更的数据集非常大,效率会很低
		·触发器可以掩盖服务器背后的工作
		·触发器可以掩盖服务器背后的工作,一个简单的sql语句背后可能包含了许多看不见的工作
		·触发器的问题很难排查,如果某个性能问题和触发器相关,会很难分析和定位
		·触发器可能会导致死锁和锁等待
		·触发器并不能保证更新的原子性
	7.触发器的用处:
		·实现一些约束,系统的维护任务,以及更新反范式化数据的时候
		·记录数据变更日志
	8.事件:类似于linux的定时任务,指定mysql在某个时候执行一段sql代码,或者每隔一个时间间隔执行一段sql代码

五.全文索引
	1.myisam的全文索引作用对象是一个全文集合,这可能是某个数据列的一列,也肯能是多个列
	2.可以根据where子句中的match against来区分查询是否使用全文索引
	3.在使用全文索引进行排序的时候,Mysql无法在使用索引排序,如果不想使用文件排序的话,就不要在查询中使用order by子句
	4.在布尔搜索中,用户可以在查询中自定义某个被搜索的词语的相关性,可以通过一些前缀修饰符来定制搜索
	5.全文索引在insert \update\delete 中的操作代价很大
	6.全文索引会影响索引选择\where 子句\order by 等

六.查询缓存
	1.MYSQL查询缓存保存查询返回的完整结果,当查询命中该缓存,mysql会立即返回结果,跳过了解析\优化和执行阶段
	2.MYSQL判断缓存命中的方法很简单,缓存放在一个引用表中,通过一个哈希值引用,这个哈希值包括了如下因素,即查询本身,当前要查询的数据库,客户端协议的版本等一些其他可能会影响返回结果的信息
	3.当判断缓存是否命中时,mysql不会解析\正规化\或者参数化查询语句,而是直接使用sql语句和客户端发送过来的其他原始信息,任何字符上的不同,例如空格,注释--都会导致缓存的不命中
	4.当查询语句中有一些不确定的数据时,则不会被缓存,如包含函数now()等
	5.打开查询缓存对读和写都会带来额外的消耗
		·读查询在开始之前必须检查是否命中缓存
		·如果这个读查询可以被缓存,那么当执行完后,MYSQL若发现查询缓存中没有这个查询,会将其结果存入查询缓存,会带来额外的系统消耗
		·当想某个表写入数据的时候,mysql必须将对应表的所有缓存都设置失效,如果查询缓存非常大,或者碎片非常多,这个操作就会带来很大系统消耗
	6.对于需要消耗大量资源的查询通常是非常适合缓存的
	7.缓存未命中
		·查询语句无法被缓存
		·MYSQL从未处理这个查询
		·查询缓存的内存用完了
		·查询缓存还没有完成预热
		·查询语句之前从未执行过
		·缓存失效操作太多了
	8.缓存参数配置
		query_cache_type,是否打开查询缓存
		query_cache_size  查询缓存使用的总内存空间
		query_cache_min_res_unit  在查询缓存中分配内存块时的最小单位,可以帮助减少由碎片导致的内存空间浪费
		query_cache_limit  mysql能够缓存的最大查询结果
		query_cache_wlock_invalidate 如果某个数据表被其他的连接锁住,是否仍然从查询缓存中返回结果

优化服务器设置

一.mysql配置的工作原理
	1.任何打算长期使用的设置都应该写到全局配置文件,而不是在命令行特别指定
	2.常用变量和动态修改它们的效果:
		key_buffer_size:可以一次性为键缓冲区分配所指定的空间
		table_cache_size:不会立即生效--将在下次有线程打开表才有效果,如果值大于缓存中表的数量,线程可以把最新打开的表放入缓存,如果比缓存中的表小,京葱缓存中删除不长使用的表
		thread_cache_size,不会立即生效,将在下次有连接被关闭时产生效果,检查缓存中是否还有空间在缓存线程,如果有空间,则缓存该线程以备下次连接征用,如果没空间,将销毁该线程而非缓存
		query_cache_size:一次性分配并初始化这块内存
		read_buffer_size:只有在查询需要使用时才会为该缓存分配内存
		read_rnd_buffer_size:只有在查询需要使用到时,才会为该查询分配内存,并且只分配需要的内存大小而不是全部指定的大小
		sort_buffer_size:只会在也有查询需要排序的时候才会为该缓存分配内存
	3.对应连接级别的设置,不要轻易的在全局级别增加他们的值,除非确认这样做是对的
	4.设置变量时请小心,并不是值越大越好,而且如果设置的值太高,可能更容易导致问题:可能会由于内存不足导致服务器内存交换,或者超过地址空间
二.什么不该做
	1.不要根据比率来调优,例如缓存命中率跟缓存是否过大或者过小无关
	2.不要使用调优脚本
	3.不要相信很流行的内存消耗公式
三.创建mysql配置文件
	1.mysql编译的默认设置并不都是靠谱的,其中大部分比较合适
	2.从一个比默认值大一点但不是大的很离谱的安全值开始是比较好的,MYSQL的内存利用率并不总是可以预测的,他可能依赖很多的因素,例如查询的复杂性和并发性
	3.配置服务器的首选途径,了解它内部做了什么,以及参数之间如何相互影响,然后再决定
	4.open_files_limit 在linux上尽可能设置的大一点,如果参数不够大,可能会报错,打开的文件太多
	5.每隔60s查看状态变量的增量变化:mysqladmin extended-status  ri60
四.配置内存使用
	1.配置mysql正确使用内存量对高性能至关重要,内存小号分为两类,可以控制的内存和不可以控制的内存
	2.配置内存
		确定可以使用的内存上限
		确定每隔mysql连接需要使用的内存
		确定操作系统需要多少内存才够用
		吧剩下的内存全部给mysql的缓存
	3.mysql保持一个连接只需要少量的内存,他还需要一个基本量的内存来执行任何给定的查询,需要为高峰时期执行的大量查询预留好足够的内存,否则,查询执行可能因为缺乏内存而导致执行效率不佳或执行失败
	4.跟查询一样,操作系统也需要保留足够的内存给他工作,如果没有虚拟内存正在交换到磁盘,就是表名操作系统内存足够的最佳迹象
	5.如果服务器只运行mysql.所有不需要为操作系统以及查询管理保留的内存都可以用作mysql缓存
	6.大部分情况下最重要的缓存:
		innodb缓冲池
		innodb日志文件和myisam数据的操作系统缓存
		myisam键缓存
		查询缓存
		无法手工配置的缓存,例如二进制日志和表定义文件的操作系统缓存
	7.innodb缓冲池并不仅仅缓存索引,他还会缓存行的数据,自适应哈希索引,插入缓冲,锁,以及其他内部数据结构,还使用缓冲池来帮助延迟写入,innodb严重依赖缓冲池
	8.如果事先知道什么时候需要关闭innodb,可以在运行时修改innodb_max_dirty_pages_pct变量,将值改小,等待刷新纯种清理缓冲池,然后在脏页数量较小时关闭,可以监控the innodb_buffer_pool_pages_dirty状态变量或者使用innotop来监控show innodb status来观察脏页的刷新量
	9.Myisam的键缓存也被称为键缓冲,默认只有一个键缓存,但也可以创建多个,MYISAM自身之缓存索引,不换存数据.最重要的配置项是key_buffer_size,不要超过索引的总大小,或者不超过操作系统的缓存保留总内存的25%-50%,以更小的为准
	10.了解MyISAM索引实际上占用多少磁盘空间，查询INFORMATION_SCHEMA表的INDEX_LENGTH字段，把它们的值相加，就可以得到索引存储占用空间
	11.thread_cache_size变量指定了MySQL可以保持在缓存中的线程数，一般不需要配置这个值，除非服务器会有很多连接请求
	12.可以关闭InnoDB的innodb_stats_on_metadata选项来避免耗时的表统计信息刷新
	13.如果可以，最好把innodb_open_files的值设置得足够大以使服务器可以保持所有的.ibd文件同时打开
五.配置mysql并发
	1.InnoDB并发配置
     	InnoDB有自己的“线程调度器”控制线程怎么进入内核访问数据，以及它们在内核中一次可以做哪些事，最基本的限制并发的方式是使用innodb_thread_concurrency变量，它会限制一次性可以有多少线程进入内核,并发值 = CPU数量 * 磁盘数量 * 2，在实践中使用更小的值会更好一点
	2.MyISAM并发配置
		尽管MyISAM是表级锁，它依然可以一边读取，一边并发追加新行，这种情况下只能读取到查询开始时的所有数据，新插入的数据是不可见的，这样可以避免不一致读
通过设置concurrent_insert这个变量，可以配置MyISAM打开并发插入
让INSERT、REPLACE、DELETE、UPDATE语句的优先级比SELECT语句更低，设置low_priority_updates选项就可以

六.基于工作负载的配置
	1.当服务器满载情况下运行时，请尝试记录所有的查询语句，因为这是最好的方式来查看哪种类型的查询语句占用资源最多，同时创建processlist快照，通过state或者command字段来聚合它们
	2.优化BLOB和TEXT场景
BLOB有几个限制使得服务器对它的处理跟其他类型不一样，不能在内存临时表中存储BLOB值，效率很低
	·通过SUBSTRING()函数把值转换为VARCHAR
	·让临时表更快一些：放在基于内存的文件系统
	·如果使用的是InnoDB，也可以调大InnoDB日志缓冲大小
	·大字段在InnoDB里可能浪费大量空间
	·扩展存储禁用了自适应哈希，因为需要完整地比较列的整个长度，才能发现是不是正确的数据
	·太长的值可能使得查询中作为WHERE条件不能使用索引
	·如果一张表里有很多大字段，最好是把它们组合起来单独存到一个列里面
	·有时候可以把大字段用COMPRESS()压缩后再存为BLOB，或者发送到MySQL前在应用程序中进行压缩
	3.优化排序（Filesorts）：当MySQL必须排序BLOG或TEXT字段时，它只会使用前缀，然后忽略剩下部分的值

七.完成基本配置
	1.tmp_table_size和max_heap_table_size，这两个设置控制使得Memory引擎的内存临时表能使用多大的内存
	2.max_connections，这个设置的作用就像一个紧急刹车，以保证服务器不会因应用程序激增的连接而不堪重负，设置得以容纳正常可能达到的负载，并且要足够安全，能保证允许你登录和管理服务器
	3.thread_cache_size，可以通过观察服务器一段时间的活动，来计算一个有理有据的值，250的上限是一个不错的估算值
	4.table_cache_size，应该被设置得足够大，以避免总是需要重新打开和重新解析表的定义，可能通过观察Open_tables的值及其在一段时间的变化来检查该变量
八.安全和稳定的设置
	1.expire_logs_days，如果启用了二进制日志，应该打开这个选项，可以让服务器在指定的天数之后清理旧的二进制日志
	2.max_allowed_packet，防止服务器发送太大的包，也会控制多大的包可以被接收
	3.max_connect_errors，如果知道服务器可以充分抵御蛮力攻击，可以把这个值设得非常大，以有效地禁用主机黑名单
	4.skip_name_resolve，禁用了另一个网络相关和鉴权谁相关的陷阱：DNS查找
	5.sql_mode，不建议修改
	6.sysdate_is_now，可能导致与应用预期向后不兼容的选项
	7.read_only，禁止没有特权的用户在备库做变更，只接受从主库传输过来的变更，不接受从应用来的变更，可以把备库设置为只读模式
	8.skip_slave_start，阻止MySQL试图自动启动复制
	9.slave_net_timeout，控制备库发现跟主库的连接已经失败并且需要重连之前等待的时间，设置为一分钟或更短
	10.sync_master_info、sync_relay_log、sync_relay_log_info，5.5以后版本可用，解决了复制中备库长期存在的问题：不把它们的状态文件同步到磁盘，所以服务器崩溃后可能需要人来猜测复制的位置实际上在主库是哪个位置，并且可能在中继日志（Relay Log）里有损坏

八.高级InnoDB设置
	1.innodb，如果设置为FORCE，只有在InnoDB可以启动时，服务器才会启动
	2.innodb_autoinc_lock_mode，控制InnoDB如何生成自增主键值
	3.innodb_buffer_pool_instances，在5.5以后，可以把缓冲池切分为多段，在高负载的多核机器上提升MySQL可扩展性的一个重要方式
	4.innodb_io_capacity，有时需要把这个设置得相当高，才能稳定地刷新脏页
	5.innodb_read_io_threads和innodb_write_io_threads，控制有多少后台线程可以被I/O操作使用
	6.innodb_strict_mode，让MySQL在某些条件下把警告改成抛错，尤其是无效的或者可能有风险的CREATE TABLE选项
	7.innodb_old_blocks_time，指定一个页面从LRU链表的“年轻”部分转移到“年老”部分之前必须经过的毫秒数，默认为0，设置为1000毫秒（1秒）非常有效

九、操作系统和硬件优化
	A.什么限制了MySQL的性能
		1.当数据可以放在内存中或者可以从磁盘中以足够快的速度读取时，CPU可能出现瓶颈，把大量的数据集完全放到大容量的内存中，以现在的硬件条件完全是可行的
		2.I/O瓶颈，一般发生在工作所需的数据远远超过有效内存容量的时候，如果应用程序是分布在网络上的，或者如果有大量的查询和低延迟的要求，瓶颈可能转移到网络上
	B.如何为MySQL选择CPU
		1.可以通过检查CPU利用率来判断是否是CPU密集型的工作负载，还需要看看CPU使用率和大多数重要的查询的I/O之间的平衡，并注意CPU负载是否分配均匀	
		2.当遇到CPU密集型的工作时，MySQL通常可以从更快的CPU中获益，但还依赖于负载情况和CPU数量
		3.MySQL复制也能在高速CPU下工作得非常好，而多CPU对复制的帮助却不大
		4.多CPU在联机事务处理（OLTP）系统的场景中非常有用，在这样的环境中，并发可能成为瓶颈	
	C.平衡内存和磁盘资源
		1.配置大量内存最终目的是避免磁盘I/O，最关键的是平衡磁盘的大小、速度、成本和其他因素，以便为工作负载提供高性能的表现
		2.设计良好的数据库缓存（如InnoDB缓冲池），其效率通常超过操作系统的缓存，因为操作系统缓存是为通用任务设计的
		3.数据库服务器同时使用顺序和随机I/O，随机I/O从缓存从受益最多
		4.每个应用程序都有一个数据的“工作集”——就是这个工作确实需要用到的数据
		5.工作集包括数据和索引，所以应该采用缓存单位来计数，一个缓存单位是存储引擎工作的数据最小单位
		6.找到一个良好的内存/磁盘比例最好的方式是通过试验和基准测试
		7.硬盘选择考虑因素：存储容量、传输速度、访问时间、主轴转速、物理尺寸
		8.MySQL如何扩展到多个磁盘上取决于存储引擎和工作负载，InnoDB能很好地扩展到多个硬盘驱动器，然而，MyISAM的表锁限制其写的可扩展性，因此写繁重的工作加在MyISAM上，可能无法从多个驱动器中收益
	D.固态存储
		1.高质量闪存设备具备：
			相比硬盘有更好的随机读写性能
			相比硬盘有更好的顺序读写性能
			相比硬盘能更好地支持并发
			提升随机I/O和并发性
		2.闪存的最重要特征是可以迅速完成多次小单位读取，但是写入更有挑战性。闪存不能在没有做擦除操作前改写一个单元（Cell），并且一次必须擦除一个大块。擦除周期是缓慢的，并且最终会磨损整个块
		3.垃圾收集对理解闪存很重要。为了保持一些块是干净的并且可以被写入，设备需要回收脏块。这需要设备上有一些空闲空间
		4.许多设备被填满后会开始变慢，速度下降是由于没有空闲块时必须等待擦写完成所造成的
		5.固态存储最适合使用在任何有着大量随机I/O工作负载的场景下，随机I/O通常是由于数据大于服务器的内存导致的，闪存设备可能大大缓解这种问题
		6.单线程工作负载也是另一个闪存的潜在应用场景
		7.闪存也可以为服务器整合提供巨大的帮助
		8.Flashcache，磁盘和内存技术的结合，适合以读为主的I/O密集型负载，并且工作集太大，用内存优化并不经济的情况
		9.优化固态存储上的MySQL
			增加InnoDB的I/O容量
			让InnoDB日志文件更大
			把一些文件从闪存转移到RAID
			禁用预读
			配置InnoDB刷新算法
			禁用双写缓冲的可能
			限制插入缓冲大小，插入缓冲设计来用于减少当更新行时不在内存中的非唯一索引引起的随机I/O
			InnoDB的页大小
			优化InnoDB页面校验（Checksum）的替代算法
	E.为备库选择硬件
		1.通常需要跟主库差不多的配置