索引

一、索引介绍

　　为何要有索引

　　一般的应用系统，读写比例在10:1左右(参看下面注释),如果数据量很大,需要查找或修改某些数据时,这个时间就会凸显的非常长,很浪费时间,所以需要一种提高系统读写效率的方案.

    　　#磁盘读取数据靠的时机械运动,每次读取数据花费的时间分为寻道时间,旋转时间,传输时间三个部分,寻道时间指的是磁臂移动到指定磁道所需要的时间,找到磁道了，
　　　　还需要找到你数据存在的那个点(该段时间称为寻点时间),机械硬盘主要是慢在这两个时间上，把数据拷贝到内存的时间是非常短暂的，传输时间是指从磁盘读出或将数据写入磁盘的时间，相对于前两个时间可以忽略不计

　　　　什么是索引

索引在MySQL中也叫做“键”或者"key"（primary key，unique key，index key），是存储引擎用于快速找到记录的一种数据结构。减少io次数，加速查询(索引就是对系统读写所需时间的优化).

　　　　（其中primary key和unique key，除了有加速查询的效果之外，还有约束的效果，primary key 不为空且唯一，unique key 唯一，而index key只有加速查询的效果，没有约束效果） 　　　　
　　　　　　　　索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级
 　　　　索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。 　　　　
强调：一旦为表创建了索引，以后的查询最好先查索引，再根据索引定位的结果去找数据
注意:
　　索引太多,应用程序的性能会受到影响,索引太少,对查询性能又会产生影响,所以最好在设计表时,把该表对应的实际应用场景了解清楚,否则就会出现太多索引或者太少索引的而影响效果的尴尬.

 

 二、索引原理

    与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等，书的目录占不占页数，这个页也要存到硬盘里面，也占用硬盘空间。
建立索引或者目录,肯定时在没有数据的时候建立的快,(如果已经有很多数据了,你再去根据这些数据建索引,需要将数据全部遍历一遍,然后根据数据建立索引,这个来回就是很长的时间)
但是索引会降低插入数据的速度(但凡你加入一些新得数据,都需要把索引或者书的目录重新做一个,所以会降低插入的速度)

总结
　　1、在表中有大量数据的前提下，创建索引速度会很慢

　　2、在索引创建完毕后，对表的查询性能会发幅度提升，但是写性能会降低

 

 　　　　磁盘IO与预读

    磁盘读取数据靠的时机械运动,每次读取数据花费的时间分为寻道时间,旋转时间,传输时间三个部分,寻道时间指的是磁臂移动到指定磁道所需要的时间,找到磁道了，
还需要找到你数据存在的那个点(该段时间称为寻点时间),机械硬盘主要是慢在这两个时间上，把数据拷贝到内存的时间是非常短暂的，传输时间是指从磁盘读出或将数据写入磁盘的时间，相对于前两个时间可以忽略不计

　　考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，
当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，
实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

 

三、B+树结构

    B+树介绍
      B+ 树是一种树数据结构，是一个n叉排序树，每个节点通常有多个孩子，一棵B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上孩子节点的节点。
    B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入。

    为什么b+树要求把真实的数据放到叶子节点而不是内层节点
    如果数据项占的空间越小，数据项的数量越多，树的高度越低。一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表,反而会影响数据查找和写入速度. 

 四、聚集索引与辅助索引

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index）
　　聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。
　　聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息

　　　　　　聚集索引介绍

MyISAM存储引擎在创建表的时候会在硬盘上生成.frm(表结构文件)\.MYD(数据文件)\.MYI(索引文件)结尾的三个文件，也就是说索引也是存在硬盘上的.

    InnoDB引擎创建一个表时，会在硬盘上会生成.frm(字段的类型的文件）\.idb(存放数据和索引)结尾的两个文件，InnoDB引擎的表，它的索引和数据都在同一个文件里面，
使用InnoDB存储引擎的时候，每建一个表，就需要给一个主键，就是说InnoDB存储引擎在存储数据的时候默认就按照索引的那种树形结构来帮你存。这种索引，我们就称为聚集索引，
也就是在聚集数据组织数据的时候，就用这种索引。InnoDB这么做就是为了加速查询效率，因为你经常会遇到基于主键来查询数据的情况，并且通常我们把id字段作为主键，
第一点是因为id占用的数据空间不大，第二点是你经常会用到id来查数据。
这种聚集索引的特点是它会以id字段作为依据，去建立树形结构，但是叶子节点存的是你表中的一条完整记录，一条完整的数据

   聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录　　
　　聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

　　　　　　辅助索引介绍

我们在查询的时候，where后面需要写id之外的其他字段名称来进行查询，比如说是where name=xx，没法用到主键索引的效率，就需要我们添加辅助索引了，给name添加一个辅助索引。
  　表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引）（unique key、index key），
与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

sql语句使用多条件查询时
 1.多条件查询(条件 and 条件),它会先找条件里面有索引的,先找1.主键索引(2.辅助索引,3.普通索引) 这个顺序来进行查找
 2.字段上与数值上做四则运算的查找速度,前者比后者慢很多.

 　　　　　　主键索引,唯一索引,普通索引的创建

创建原则:选择区别度高的字段进行设置
在创建表时
    主键、普通索引的创建
　　 create table ku1(id int primary key auto_increment,  #主键和自增
                        name char(10) not null default "匿名", 
                        age int,index in_age(age));  #创建普通索引
    唯一索引创建(2种写法)　　
    create table t1(id int unnique)
    create table t1(id int, unique key uni_name(id)  #如果没有给名字,要删除时,看show create table t1;

表创建完后
　　(MUL为普通索引的意思,show create table t2; 查看字段的约束条件等信息)
    主键的添加
    alter table 表名 add primary key(id);
    主键的删除
    alter table  表名 drop primary key;
    唯一索引的添加
    alter table t1 add unique key uni_name(name);
    唯一索引的删除
    alter table t1 drop index uni_name;
    drop index uni_name on t1;#uni_name 是自己指定的唯一索引的名字 
    普通索引的添加(2种方式)
    alter table t2 add index ind_name(name);  
    create index ind_name on s1(id);
    普通索引的删除
     alter table s1 drop index u_name;
     drop index 索引名 on 表名字;

 　　联合(主键、普通、唯一)索引的创建

联合索引:多个字段,组成一个索引
#设置联合索引时,把数据量小,区别度高的放前面,可以提高联合索引的查找速度
主键也可写成联合主键索引

    联合唯一索引
create table t1(id int,unique key index_name(id,name));
    联合普通索引
create table t1(id int, index ind_name(id,name));

最左匹配特性(↓  比如id是主键索引)  #查找时,一定要把联合索引设置时的第一位带上,索引的加速查找效果才会体现
where id=10 and name='xxx';

where name="xxx" and age='xxx'; #这样写,就是没有用到索引加速效果

五、正确使用索引

使用索引查找时

1.条件中使用模糊匹配(between,and,like,>,<...),应尽量缩小匹配的范围,范围太大也会很慢
2.尽量选择区分度高的列作为索引(区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例)
3.索引字段应保持“干净”,不参与计算.例如from_unixtime(create_time) = ’2014-05-29’,和
  create_time = unix_timestamp(’2014-05-29’)相比,前者就明显慢于后者,(等于号后面的数字，是在比较之前就计算出来了，不需要每次都计算一次每次都计算一次了，跟直接等于一个常数是一样的)

mysql (and)条件索引查找工作原理
    索引的本质原理就是先不断的把查找范围缩小下来，然后再进行处理，对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序

explain方法

　　#explain 预执行.返回的结果信息中可以看到,按照你给出的条件去找数据,需要扫描多少行(rows字段)..等信息.
explain select count(*) from s1 where id*10= 10000;

　　具体用法和字段含义,参考下列地址:
http://www.cnblogs.com/yycc/p/7338894.html

 此处省略一个慢日志管理,详情参看,慢日志段落

https://www.cnblogs.com/clschao/articles/10049133.html#_label7