合理使用索引

理解MySQL的索引

1,索引的原理:把无序的数据变成有序的查询;

索引:
1,索引的物理结构:
1,数据库文件存储的位置:my.ini配置文件中dataDir对应的数据目录中;
2,每一个数据库一个文件夹;

   1,MYISAM引擎:每一个表(table_name)-->
         table_name.MYI:存放的是数据表对应的索引信息和索引内容;
         table_name.FRM:存放的是数据表的结构信息;
         table_name.MYD:存放的是数据表的内容;
   2,InnoDB引擎:每一个表(table_name)-->
         table_name.frm:存放的是数据表的结构信息;
         数据文件和索引文件都是统一存放在ibdata文件中;
   3,索引文件都是额外存在的,对索引的查询和维护都是需要消耗IO的;

2,索引的结构:

1,默认情况下,一旦创建了一个表,这个表设置了主键,那么MYSQL会自动的为这个主键创建一个unique的索引;
2,索引类型:
    1,Normal:普通的索引;允许一个索引值后面关联多个行值;
    2,UNIQUE:唯一索引;允许一个索引值后面只能有一个行值;之前对列添加唯一约束其实就是为这列添加了一个unique索引;当我们为一个表添加一个主键的时候,其实就是为这个表主键列(设置了非空约束),并为主键列添加了一个唯一索引;
    3,Fulltext:全文检索,mysql的全文检索只能用myisam引擎,并且性能较低,不建议使用;
3,索引的方法(规定索引的存储结构):        (数据结构,算法基础)
     1,b-tree:是一颗树(二叉树,平衡二叉树,平衡树(B-TREE))
          使用平衡树实现索引,是mysql中使用最多的索引类型;在innodb中,存在两种索引类型,第一种是主键索引(primary key),在索引内容中直接保存数据的地址;第二种是其他索引,在索引内容中保存的是指向主键索引的引用;所以在使用innodb的时候,要尽量的使用主键索引,速度非常快;
           b-tree中保存的数据都是按照一定顺序保存的数据,是可以允许在范围之内进行查询;
           select * from accountflow where account_id <100;
     2,hash:把索引的值做hash运算,并存放到hash表中,使用较少,一般是memory引擎使用;优点:因为使用hash表存储,按照常理,hash的性能比B-TREE效率高很多。               
        hash索引的缺点:
1,hash索引只能适用于精确的值比较,=,in,或者<>;无法使用范围查询;
2,无法使用索引排序;
3,组合hash索引无法使用部分索引;
4,如果大量索引hash值相同,性能较低;

索引的利弊

1,索引的好处:

1,提高表数据的检索效率;
2,如果排序的列是索引列(如果查询的列==排序的列[并且在这列上做了索引]),大大降低排序成本;
3,在分组操作中如果分组条件是索引列,也会提高效率;

2,索引的问题:

  索引需要额外的维护成本;因为索引文件是单独存在的文件,对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率;

如何创建索引

1,较频繁的作为查询条件的字段应该创建索引;
2,唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件;

   作为索引的列,如果不能有效的区分数据,那么这个列就不适合作为索引列;比如(性别,状态不多的状态列)  
   举例:SELECT sum(amount) FROM accountflow WHERE accountType = 0;
   假如把accountType作为索引列,因为accountType只有14种,所以,如果根据accountType来创建索引,最多只能按照1/14的比例过滤掉数据;但是,如果可能出现,只按照该条件查询,那我们就要考虑到其他的提升性能的方式了;
    第一种方案:单独创建一个系统摘要表;在这个表里面有一个列叫做系统总充值金额;每次充值成功,增加这个列的值;以后要查询系统总充值金额,只需要从这个系统摘要表中查询;(缺陷:如果充值频率过快,会导致表的锁定问题;)
    第二种方案:流水一旦发生了,是不会随着时间改变的;针对这种信息,我们就可以使用增量查询(结算+增量查询);
        1,创建一张日充值表;记录每一天的充值总金额(beginDate,endDate,totalAmount),每天使用定时器对当前的充值记录进行结算;日充值报表里面记录只能记录截止昨天的数据;
        2,创建一张月充值表;记录每一个月的充值总金额(beginDate,endDate,totalAmount),每月最后一天使用定时器对当月的充值记录进行结算(数据源从日充值报表来);
        3,要查询系统总充值,从月报表中汇总(当前月之前的总充值金额),再从日充值报表中查询当天之前的日报表数据汇总;再从流水中查询当前截止查询时间的流水;使用另外一张当天流水表记录当天的流水;再把三个数据累加;         

3,更新非常频繁的字段不适合创建索引;原因,索引有维护成本;
4,不会出现在WHERE 子句中的字段不该创建索引;
5, 索引不是越多越好;(只为必要的列创建索引)

   1,不管你有多少个索引,一次查询至多采用一个索引;(索引和索引之间是独立的)
   2,因为索引和索引之间是独立的,所以说每一个索引都应该是单独维护的;数据的增/改/删,会导致所有的索引都要单独维护;

单值索引和组合索引

1,单值索引即一列作为索引;
2,组合索引即多列创建为一个索引;

1,因为一个查询一次至多只能使用一个索引,所以,如果都使用单值索引(一个列一个索引),在数据量较大的情况下,不能很好的区分数据;
2,所以,MYSQL引入了多值索引(复合索引);
复合索引就是由多列的值组成的索引;并且(注意),多列的索引是有顺序的!!!!
3,复合索引的原理:就是类似orderby(orderby后面可以跟多个排序条件order by hire_date,username desc);

就是在排序和分组(创建倒排表的时候),按照多个列进行排序和合并;
SELECT * FROM accountflow WHERE actionTime < 'xxxxx' AND account_id = 5 可以使用actionTime+account_id的复合索引;
SELECT * FROM accountflow WHERE actionTime < 'xxxxx' 可以使用actionTime+account_id的复合索引;
SELECT * FROM accountflow WHERE account_id = 5 不可以使用actionTime+account_id的复合索引;
SELECT * FROM accountflow WHERE account_id = 5 AND actionTime < 'xxxxx' 不可以使用actionTime+account_id的复合索引;

4,复合索引,在查询的时候,遵守向左原则;只要在查询的时候,是按照复合索引从左到右的顺序依次查询,不管查询条件是否完全满足所有的符合索引的列,都可以使用部分的符合索引;
5,在实际应用中,基本上都使用复合索引;

MySQL中索引使用限制

BLOB 和TEXT 类型的列只能创建前缀索引
MySQL 目前不支持函数索引
使用不等于(!= 或者<>)的时候MySQL 无法使用索引
过滤字段使用了函数运算后(如abs(column)),MySQL 无法使用索引
Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引
使用LIKE 操作的时候如果条件以通配符开始( '%abc...')MySQL 无法使用索引
使用非等值查询的时候MySQL 无法使用Hash 索引

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注