mysql高级---索引

索引

• 索引是帮助mysql高效获取数据的数据结构。索引本质是一种数据结构。
• 数据本身之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
• 目的在于提高查询效率，可以类比字典。影响到查找和orderby。建立索引后比不建立索引查的快，因为在底层会进行一个排序。
• 索引本身很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
• 一般所说的索引默认指B树（多路搜索树，不一定是二叉树），除此以外还有哈希索引。

优点

• 类似大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

缺点

• 实际索引也是一张表，这张表保存了主键和索引字段，并指向实体表的记录，因此索引列也会占用空间。
• 虽然索引大大提高了查询速度，同时却会降低更新表是速度，如增insert改update删delete
• 更新表时，mysql不仅要保存数据，还要保存索引文件每次更新后添加了索引列的字段。
• 都会调整因为更新所带来的键值变化后的索引信息。
• 索引只是提高效率的一个因素，如果数据库中有大数据量的表，就需要花时间建立最优秀的索引，或优化查询。

索引的分类

• 单值索引

  一个索引只包含单个列，一个表可以有多个单列索引。

• 唯一索引

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

• 复合索引

  一个索引包含多个列

索引的结构

• BTree索引

  检索原理
  
  

• Hash索引

• Fulltext索引

• R-tree索引

创建索引

方法一 create

Create [unique] index 索引名 on 表名(字段名(长度));

Create index idx_user_name on user(name);
Select *from user where name =’’;

方法二 alter add

Alter 表名 add [unique] index 索引名(字段名(长度));

Alter user add index idx_user_name(name);

修改索引

为字段添加主键

索引值必须是唯一的，且不能为空
Alter table 表名 add primary key (列名);

修改索引为唯一索引

创建索引的值必须是唯一的，除null以外
Alter table 表名 add unique 索引名(字段名);

添加普通索引

Alter table 表名 add index 索引名(字段名);

指定索引为fulltext

用于全文索引
Alter table 表名 add fulltext 索引名(字段名);

删除索引

语法
Drop index 索引名 on 表名;

Drop index idx_user_name on user;

查看索引

语法
Show index from 表名;

Show index from user;

创建索引的情况

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他表关联的字段，外键关系建立索引
• 单值/组合索引的选择问题（高并发情况倾向创建组合索引）
• 查询中排序的字段orderby，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或分组字段groupby

不适合创建索引的情况

• 表数量太少。
• 经常增删改的表。
• 数据重复且分布平均的表字段。
• 某个数据列包含许多重复的内容，建立索引没有太大的效果。索引的选择性指索引列中不同值的数目和表中记录数的比。选择性越接近1，这个索引的效率越高。
• 频繁更新的字段，每次创建不单单是更新了记录还会更新索引。
• Where 条件里用不到的字段。