mysql索引

1.什么是索引

  数据是帮助mysql高效获取数据的一种数据结构

  索引的目的是提高查询效率，类似于字典

  可以理解为排好序的快速查找数据结构

    ①.在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据,这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

  一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘中

  我们平常所说的索引，一般都是值B数(多路搜索树，并不一定是二叉的)结构组织的索引；其中聚集索引、次要索引、覆盖索引、复合索引唯一索引默认都是使用B+树索引。除了B+树，还有hash索引

 

2.索引的优势

  提高数据检索效率，降低数据库的IO成本

  通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

 

3.劣势

  索引也是一张表，该表保存了主键于索引字段，并指向实体表的记录，所以索引也会占用空间

  更新表时，mysql不仅要更新真实的数据，还要保存一下索引文件每次更新添加了索引的字段。

  索引只是提高效率的一个因素，如果mysql有大量的表，要考虑建最优秀的索引或者优化查询

 

4.mysql索引分类 (一个表最好不要超过5个索引)

  单列索引:即一个索引只包含单列

  唯一索引:索引列的值必须唯一，但是允许有空值

  复合索引:一个索引包含多个列

  基本语法:

   创建: create index indexName on tableName(columnName(这里写多个列，就是复合索引))

         alter tableName add index indexName on (columnName)

   删除: drop index indexName on tableName

   查看: show index from tableName\G

 

5.哪些情况需要建索引，哪些不需要建立索引

 

 哪些需要建立索引:

   主键自动建立唯一索引

   频繁作为查询条件的字段创建索引

   查询中与其他表关联的字段，外键关系建立索引

   频繁更新的字段不适合建立索引，因为更新的时候 不仅要更新数据本身，还要更新索引

   where条件里面用不到的字段不创建索引

   单键/组合索引的选择问题，一般选择组合索引

   查询中排序的字段。排序字段若通过索引去访问，将大大提高排序速度

   查询中统计或者分组字段

 

 哪些不需要建立索引

   表记录太少

   经常增删改的表

   数据重复且分布平均的表字段

 

索引选择性:是指索引列中不同值的数目与表中记录数的比，比如:表中有2000天记录，表索引列有1920个不同的值，那么这个索引的选择性就是 1980/2000 = 0.99

                   索引的选择性越接近1 ，效率越高

 

为经常查询和经常排序的数据列建立索引；如果数据列包含重复 内容较多，建立索引没有太大效果

 

6.索引优化:

  全值匹配最好

  最佳左前缀原则:指的是查询从索引的最左前列开始并且不跳过索引中的列

  不在索引列上做任何操作(计算、函数、类型转换)，否则会导致索引失败，走全表扫描

  存储引擎不能使用索引中范围右边的列

  尽量使用覆盖索引(只访问索引的列)，不允许select *

  mysql在使用不等于(!= 或者 <>)、is null、is not null、like以%开头 都会导致索引失效，走全表扫描

  字符串不加单引号导致索引失效(如果查询条件的字段是字符串，但是不加单引号，会进行转换)

  少用or，用or来连接时会导致索引失效

 

  not in 和<>会导致无法使用索引

  如果查询中有某个列的查询范围，那右边的列都无法使用索引

  使用索引时，不能跳过索引中的列