Mysql索引

1. 简介

索引是mysql提供用于高效的获取数据的数据结构

索引是通过额外的空间来保存你需要快速查询的一些字段，将这些字段按照一定的顺序存储并与原始数据进行映射

1. 原理：

1. 和查字典一样，通过不断的缩小数据的范围，来减少磁盘的IO次数，进而提高数据的读取速度   b树
2. mysql索引都是基于b+树来实现；非叶子节点只存储子节点数据的范围，数据只存在叶子节点中

1. 使用场景：

1.  where，order by，group by中频繁出现，且数据分布比较离散的列适合创建索引。
2. 比如学生表，sex,Name,age，对于单列索引来说，比较适合建在重读度低的列上，即name。
3. 频繁修改的列不适合创建索引，且不能过多。mysql索引基于B+树实现，修改时，需要修改对应的索引。

1. 语法以及分类：
   1. 单列索引

CREATE Index   index_Name ON t_user(userName);

ALTER TABLE T_name ADD index index_name (userName);

1. 1. 唯一索引

主键索引：primary key ：加速查找+约束（不为空且唯一）

ALTER TABLE table_name ADD primary  KEY ( column )

设置主键时，系统会自动创建主键的索引

唯一索引：unique：加速查找+约束 （唯一）

ALTER TABLE table_name ADD unique ( column )

Create unique index  index_Name ON t_user(userName);

 

1. 1. 联合索引

Create index index_Name ON t_user(userName,PASSWORD);

大学修课，有一个关系对应表，student_id 和 teacher_id，想要查询某个老师和某个学生是否存在师生关系；如果只为student_id建立索引的情况下，经过索引会选出50条记录，然后在内存中where一下，去除其余的老师。反之亦然，使用索引后范围仍然很大，最好使用联合索引。

1. 1. 全文索引

fulltext  key : mysql-InnoDB 5.6版本及以上才有；MyIsam有

1. 1. 删除索引

DROP Index  index_userName ON t_user;

1. 索引方法
   1. Btree

1. 最为常见；被索引的列是排过序的，每个叶节点到跟节点距离相等，但是需多次定位
   1. Hash
2. 仅支持"=","IN"和"<=>"精确查询                  
3. 检索效率高，可以一次定位
4. Hash索引中存放的是经过Hash计算之后的Hash值，不不支持范围查询以及排序

1. 使用原则
   1. 最左前缀匹配原则。 mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
   2. =和in可以乱序。比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
   3.  尽量选择区分度高的列作为索引。区分度的公式是count(distinct col)/count(*)
   4. 索引列不能参与计算，保持列“干净”。
   5. 尽量的扩展索引，不要新建索引。 比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可