mysql 索引

一、定义

：mysql官方定义：索引时帮助mysql高效的获取数据的数据结构。可以得到索引的本质：数据结构
是一种排好序的快速查找的数据结构  （ 查找+排序 两大功能）

也就是说：索引会影响到查找where后面的过滤，和 order by 中的排序

之所以查的快，是因为给你排好序了。

创建索引后，只对创建了索引的列有效。

执行流程：在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都会有一个硬盘的物理存储编号。通过索引拿到物理存储编号，定位记录。

二、索引的优劣势

优势：

1.缩小扫描范围，拿到物理存储编号，定位这条记录，避免全表扫描。
2.提高了数据检索的效率，降低数据库的I/O成本。
3.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

索引的代价
1.索引本身也会占用磁盘空间
2.索引虽然大大提高了查询速度，但是对dml（update delete insert）语句的效率有影响，  mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。比如说 delete 时，会导致二叉树会重新构造。

3.另外索引的建立也是需要花时间研究建立性能好的索引，不断的重建测试进行优化

三、索引的拓展知识

1.当没有创建索引时，会进行全表扫描，依次比较。索引在mysql中，是一个单独的对象
创建索引，会形成二叉排序树，B树、B+树都有可能有。不同的存储引擎以不同的形式存在，再myisal存储引擎中，索引存储在一个 .myi文件中 。在innodb存储引擎中，索引存储在一个逻辑名称叫做tablespace文件当中。在memory存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在 （自平衡二叉树：B-Tree）

2.索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。我们常说的索引，如果没有特别指明，都是B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引等。

四、索引的类型

1.主键索引，主键自动的为主索引 （类型 primary key） ，主键也是一种唯一索引？
2.唯一索引 （unique）约束也会自动创建索引对象
3.普通索引 （index）
4.全文索引 （fulltext）【使用于mylsam】
mysql自带的全文索引不好用，在开发中考虑使用：全文搜索 solr 和 elasticsearch （ES）

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，单允许有null值。   
复合索引：
总结：复合索引一定优于单值索引 。 （一张表最好只建5个左右的索引）

mysql索引结构：Btree索引、Hash索引、full-text全文索引、R-Tree索引

另外，一个列只能添加一个索引

五、索引的基本语法

1.查询索引

show index/indexs/keys from 表名

2.创建索引：
方式1：create 【unique / fulltext】 index 索引名 on 表名（列名 / 复合索引）； 
方式2：alter table 表名 add 【unique / fulltext】 index 索引名 on （列名 / 复合索引）；

3.删除索引
drop index 索引名 on 表名
 

4.添加主键索引 / 删除主键索引

alter table 表名 add primary key（列）；

alter table 表名 drop primary key     （不指定列，一个表最多一个主键）

删除的时候注意： 外键  和  auto_increment  。

5.如果要修改索引：先删除，在添加。

六、哪些情况适合建索引  /  不适合建索引

1.较频繁的作为查询条件字段应该创建索引 （出现在where后面）
2.查询中与其他表关联的字段，外键关系建立索引
3.更新非常频繁的字段不适合创建索引
4.单键/组合索引的选择问题。
5.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
6.查询中统计或者分组的字段。

1.表的记录太少
2.经常需要增删改的字段
3.不会出现在where字句中字段不该创建索引
4.唯一性太差（数据重复且分布平均）的字段不适合单独创建索引，即使频繁作为查询条件  （因为可区分性太差，比如‘男’，‘女’）

 七、索引失效的情况

1.select * from emp where ename like ‘%T’；
ename上即使添加了索引，也不会走索引。尽量避免模糊查询的时候以%开头

2.使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引。

3.索引列上做任何操作（计算、函数、（自动or手动,显示隐式）类型转换），会导致索引失  效而转向全表扫描
如：select * from emp where sal+5=800;

       select * from emp where lower(ename)='smith';

如：字符串不加单引号索引失效。

        select * from staffs where name=2000;

        name 是varchar类型。

        这个给的是整型2000，但是mysql 在底层隐式自动类型转换了，也能查到这条记录。

4. is null 、is not null 也无法使用索引

5.使用复合索引的时候，没有使用左侧的列查找，即最佳左前缀原则。

6.复合索引，范围之后全失效。

复合索引 ：（id，age，name）

select * from where id = ？ and  age>? and name =‘？’；

用到了 id 列 索引、age 列 索引 、但是name 列索引失效 。

7.对全表扫描速度比索引速度快的时候不会使用索引。

八、待补充

1.回表

2.聚集索引和非聚集索引

3.覆盖索引

4.索引存储数据的具体细节