通俗易懂讲索引

B平衡树的索引结构，三种索引的速度以及覆盖范围排序: 1覆盖索引>= 2聚集索引>3非聚集索引=组合索引。 1和2中大于的部分不是速度，而是适用范围，1覆盖索引能够根据业务自定义，而2基本都是主键，适用性不强，但是覆盖索引占用内存比较大，这个是一个限制条件。

 

索引总共分为三种，聚集索引，非聚集索引，覆盖索引

非聚集索引会先找到聚集索引的唯一主键，然后根据聚集索引查找值，例外的是覆盖索引（多列索引）：查询列在索引列中，不需要再到聚集索引中查找一遍。

没有主键（聚集索引）的表加数据堆，数据堆是通过使用索引分配图（IAM）页来维护的。当在数据堆上创建了非聚簇索引时，叶级中包含了指向数据页的行标识符。行标识符指定记录行的逻辑顺序，由文件ID、页号和行ID组成

left join 的时间开销类似于笛卡尔积，相当费时，如果关联字段是索引字段，可以减少时间复杂度，但是还是非常费时。

left 的优化：首先，mysql都是使用（Nested Loop ）循环套嵌的方式实现join，这里包括两个部分：驱动表结果集作为条件连接被驱动表X，被驱动表根据驱动表结果查询数据集Y。时间复杂度（X*Y），这里的第二部分是数据库内部的操作，涉及io，cpu等的操作很少，而且可以使用索引优化。第一部分是表的连接，这里是需要时间的，时间复杂度，CPU，io操作等都耗费比内部操作更多的时间。所以，以少的实际结果集（where筛选后）驱动大的结果集，大的结果集使用索引，这种方式能够最大层度的减少时间复杂度。也就是永远使用小的结果集驱动大的结果集。

这里规定join连接单次总用时为m1，被驱动表一次查询使用时间为m2;则总的查询时间为：

X*m1+Y*m2;这里的m1大于m2则 X相对于Y越小，结果越小;

 

索引的操作

创建索引

create index index_name on table_name(**);

alter table table_name add index index_name(**);

查看索引 show index from table_name;

删除索引 :drop index i_deptno_job on table_name;

alert table table_name drop index index_name;

 

 

索引失效的原因

1，数据类型不一致 当变量和列类型不一致，会导致索引失效！！！

错误的例子：select * from test where tu_mdn=13333333333; 

正确的例子：select * from test where tu_mdn='13333333333'; 

2，对索引列进行运算会导致索引失效 运算包括(+，-，*，/，! 等) 

错误的例子：select * from test where id-1=9; 

正确的例子：select * from test where id=10; 

3,运算是不能创建索引的，但是运算函数是可以创建索引的，在sql中，where条件中的函数可以创建索引

alter table table_name add index(round(cell_name)); round() 遵循四舍五入把原值转化为指定小数位数，

如：round(1.45,0) = 1;round(1.55,0)=2;round(1.55,1)=1.6;round(1.55,2)==1.55;

floor()向下舍入为指定小数位数 如：floor(1.45,0)= 1;floor(1.55,0) = 1

ceiling()向上舍入为指定小数位数 如：ceiling(1.45,0) = 2;ceiling(1.55,0)=2

4,or 的使用会导致索引失效。条件是两边只要一个字段创建了索引。

5，不使用组合索引的第一个索引，会导致索引失效。

6，like，当%在前缀的时候会导致失效，索引是从内存的起始位置匹配。%后缀有效

7，当全表扫描比索引快的时候，索引失效，一般是数据量比较小，当数据的值比较单一(总共有n种值)的时候，B树的机构是没有办法将值区分开的，貌似最多只能将复杂度下降到 all（全扫描）/n的程度，这对于大量的数据的表中无意义，杯水车薪，这里需要的是量级级别的降低。所以，无重复的字段，索引的效果是最好的。值枚举较少可以使用位图索引。

 

位图（BitMap）索引 位图索引的好处是维护的是二进制bit串，查找的时候可以使用位运算，这是相当快的，位运算获得数据所在行数，然后找到数据。这只适用于枚举值类型比较少的情况。否则存储维护二进制串的难度逐渐加大，位运算次数也增多，数据增删改对整个阵列串的影响也很大。索引平衡树子叶存的是值，位图存的是行数。这是内存方面的优化，不是数据结构方面的。