索引原理与慢查询优化
1、为何要用索引？
创建索引的目的就是为了优化查询速度
注意一张表一旦创建了索引，就会降低写速度
2、什么是索引？
索引是mysql数据库的一种数据结构，在mysql里称之为key
你是否对索引存在误解？
索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引----我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面，比如编写SQL语句、存储过程之类，他们甚至可能不知道索引的存在，或认为事后让相关DBA加上即可。DBA往往不够了解业务的数据流，而添加索引需要通过监控大量的SQL语句进而从中找到问题，这个步骤所需的时间肯定是远大于初始添加索引所需的时间，并且可能会遗漏一部分的索引。当然索引也并不是越多越好，我曾经遇到过这样一个问题：某台MySQL服务器iostat显示磁盘使用率一直处于100%，经过分析后发现是由于开发人员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率马上下降为20%。可见索引的添加也是非常有技术含量的。
二、索引原理：
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。
二 磁盘IO与预读
前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：
考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
三、索引的数据结构
前面讲了索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生（B+树是通过二叉查找树，再由平衡二叉树，B树演化而来）。
如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
###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，显然成本非常非常高。
###b+树性质
1.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2.索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
四、聚集索引与辅助索引
1、聚集索引
1 InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。 2 3 如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。 4 5 如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。 6 7 由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。
2、辅助索引
除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。
叶子节点除了包含键值以外（还有指针且指向聚集索引），每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。
五、Mysql索引管理
1 #1. 索引的功能就是加速查找 2 #2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能
1、Mysql常用索引
1 普通索引index：加速查找 2 3 唯一索引： 4 -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复） 5 -唯一索引UNIQUE:加速查找+约束（不能重复） 6 7 联合索引： 8 -PRIMARY KEY(id,name):联合主键索引 9 -UNIQUE(id,name):联合唯一索引 10 -INDEX(id,name):联合普通索引
六、索引的两大类型'hash'与'btree'
1 #我们可以在创建上述索引的时候，为其指定索引类型，分两类 2 hash类型的索引：查询单条快，范围查询慢 3 btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
七、创建和删除索引的语法
1 # 方式一： 2 create table t5( 3 id int primary key auto_increment, 4 name varchar(4), 5 email varchar(10), 6 unique key uni_name(name), 7 index xxx(email) 8 ); 9 10 # 方式二： 11 alter table t5 add index qqq(email); 12 13 # 方式三： 14 create index yyy on t5(name); 15 16 17 18 alter table t5 drop primary key; 19 alter table t5 drop index qqq;
八、测试索引
1、在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢
2、在索引建立完毕后，以该字段为查询条件时，查询速度提升明显
九、覆盖索引
InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。
使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作
十、查询优化（explain）的基本步骤
1 0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE 2 1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高 3 2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询） 4 3.order by limit 形式的sql语句让排序的表优先查 5 4.了解业务方使用场景 6 5.加索引时参照建索引的几大原则 7 6.观察结果，不符合预期继续从0分析
十一、慢日志管理
1 MySQL日志管理 2 ======================================================== 3 错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息 4 二进制日志: 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作 5 查询日志: 记录查询的信息 6 慢查询日志: 记录执行时间超过指定时间的操作 7 中继日志： 备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放 8 通用日志： 审计哪个账号、在哪个时段、做了哪些事件 9 事务日志或称redo日志： 记录Innodb事务相关的如事务执行时间、检查点等 10 ======================================================== 11 一、bin-log 12 1. 启用 13 # vim /etc/my.cnf 14 [mysqld] 15 log-bin[=dir\[filename]] 16 # service mysqld restart 17 2. 暂停 18 //仅当前会话 19 SET SQL_LOG_BIN=0; 20 SET SQL_LOG_BIN=1; 21 3. 查看 22 查看全部： 23 # mysqlbinlog mysql.000002 24 按时间： 25 # mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" 26 # mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54" 27 # mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54" 28 29 按字节数： 30 # mysqlbinlog mysql.000002 --start-position=260 31 # mysqlbinlog mysql.000002 --stop-position=260 32 # mysqlbinlog mysql.000002 --start-position=260 --stop-position=930 33 4. 截断bin-log（产生新的bin-log文件） 34 a. 重启mysql服务器 35 b. # mysql -uroot -p123 -e 'flush logs' 36 5. 删除bin-log文件 37 # mysql -uroot -p123 -e 'reset master' 38 39 40 二、查询日志 41 启用通用查询日志 42 # vim /etc/my.cnf 43 [mysqld] 44 log[=dir\[filename]] 45 # service mysqld restart 46 47 三、慢查询日志 48 启用慢查询日志 49 # vim /etc/my.cnf 50 [mysqld] 51 log-slow-queries[=dir\[filename]] 52 long_query_time=n 53 # service mysqld restart 54 MySQL 5.6: 55 slow-query-log=1 56 slow-query-log-file=slow.log 57 long_query_time=3 58 查看慢查询日志 59 测试:BENCHMARK(count,expr) 60 SELECT BENCHMARK(50000000,2*3);
十二、测试查询速度
一个表里面有ID name gender email记录
插入两百万多条占硬盘 160多兆
