最近一直在忙，好不容易项目差不多了同时忙的时候面试过一些求职者，发现数据库优化相关的知识还是很有必要整理一下，方便自己的同时也许也能帮助大家少走一点儿弯路。
为什么要进行数据库性能优化？
众所周知，数据库的读写性能直接影响到了用户在网站上操作的体验，进入一个页面如果让你等个一两分钟，你也就只能呵呵了，而一个页面从访问到全部展示到用户面前，需要页面渲染、服务器拿数据，浏览器dom结构渲染的时间可以通过交互忽略不计，但是如果服务器接收到请求之后出时间不突出数据，请求一直挂起那就很尴尬了。服务器吐数据的时间主要有数据库性能来决定（忽略掉很烂的脚本造成的可能）；
服务器接收到请求之后要做什么操作之后才能吐出数据？
服务器接收到请求之后http服务器（比如apache）会解析请求然后产生一个线程根据请求路径去执行服务器脚本（比如php），脚本执行之后和数据库（比如mysql）产生交互拿数据，拿数据，拿到数据之后脚本处理成页面需要的数据格式，然后根据http握手协议resopnse到客户端接口，客户端拿到数据渲染到页面中，然后用户需要的页面就呈现出来了。
在这个工程中最耗时的就是mysql拿数据的过程，apache和mysql的连接数并不是无限的，如果mysql读写性能差那么这次链接时间就长，那么这个mysql链接数就会一直被占用着，如果类似慢查询比较多的话mysql连接数都被占用，之后的请求就要一直被挂起等待连接数被释放才能进行数据库交互，那么就是灾难的开始了。
mysql数据的存储是以文件的方式进行存储，说到存储就要提到访问磁盘了，接下来就说一下硬盘性能对数据查询的影响。
现在主流的硬盘不外乎就是机械硬盘和固态硬盘了。
机械硬盘读数据的原理
前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：
考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO。
固态硬盘读取数据的原理
固态硬盘的存储是通过虚拟颗粒存储的，固态硬盘会分出一定的容量来存储存储映射关系，从固态硬盘读数据是先访问存储映射关系，然后根据存储映射关系来读取数据，减少了机械硬盘的转盘和寻到的时间，但是读数据需要现在映射关系中确定存储路径，如果数据量大的话还是需要一定的时间，因此ssd的IO时间也是不能忽略的。
通过对这两种主流硬盘的存储介绍可以发现硬盘读取每一次IO都是需要一定的时间的，但是如果尽可能的减少IO的次数，那么数据读取的总时间肯定会下降，那么数据读取的效率就会有很大的提升，那么怎么才能减少IO的次数呢，这里就要引入mysql的索引了
什么是索引？
索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。说白了就是把数据分成若干大的类目，大的类目下又有细分的小类目，一层一层分下来之后就构造出了数据树，也就是常说的B+树。
前面介绍的也差不多了接下来我们说一下mysql的性能优化
一：缓存；
二：索引；
三：读写分离；
缓存
什么是缓存？说白了就是把数据键值对存储，能够直接命中目标数据，比如redis，命中之后拿到数据直接处理而不是去数据库中查询，这样就大大的减少了IO次数，但是有一个弊端就是缓存吃性能，也就是说需要花钱。
索引
相对来说索引是很廉价的方式，同时也是很有效的数据库性能优化方式。
索引的数据结构
前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。当然除了b+树还有b树、b-树、b*树等，这里就不做详细介绍了，有兴趣的可以移步这里查看http://www.cnblogs.com/oldhorse/archive/2009/11/16/1604009.html
知道了索引的数据结构那么mysql是怎么通过索引来查找数据呢？
先来张b+树的存储结构图
如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
索引对于mysql性能优化作用很大，但是没有好好维护的索引也会成为性能的瓶颈，创建索引之后mysql就会存储索引产生的b+树数据结构，一张表索引越多那么b+树也就越多，这些是直接存储在你的硬盘上边的，试想一下如果你每个字段都创建索引那么多少个字段就会产生多少个b+树，存储量也是很客观的。
所以索引的创建也是有一系列的原则的
1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
索引大概介绍的也差不多了接下来我们说一下可能被忽视的知识点
现在有一个表，表结构如下
表上边有两个索引如下
如果我只是根据status作为条件进行查询的话那么按照最左匹配原则就不应该匹配到idcodeid_status这个索引，但是请看下图
很明显是匹配到了这个索引，这个不是和最左匹配规则冲突了么？显然不是的，这里边就涉及到了mysql查询机制了，mysql查询时先匹配索引，匹配到索引之后会找到索引存储的目标节点，在目标节点上边有对应数据的主键id还有组合索引的其他字段的值，如果你查询的数据就是组合索引的字段的话就会直接返回数据，否则就会根据id再去表中查询数据，这也就是反表查询机制，接下来下边这个图会说明一切
同时索引的匹配也会受字段类型的限制，下边这张图片就能说明问题
从上边的表结构可以看出来，field_value是varchar类型，第一条sql和第二条sql都能匹配到索引，但是实际查询的效率可以说是天差地别，主要原因就有涉及到了索引优化的目的了，之前有说过索引的目的就是减少IO次数，explain语句中有一个rows，rows越大那么匹配到的数据条数就越多，那么IO次数就越大，所以索引优化的目的就是为了减少rows的匹配个数，从sql可以体现出来第一条sql条件语句中类型和字段类型不一致，这样虽然匹配上索引了，但是对mysql性能还是有很大的影响的。
现在有一个表，表结构如下
现在有一个表，表结构如下
现在有一个表，表结构如下
现在有一个表，表结构如下