MySQL数据库～～～～～索引

1. 索引

索引在MySQL中也叫"键"或者"key",是存储引擎于快速找到记录的一种数据结构.

索引的数据结构: B+树

B+树性质: 索引字段要尽量小; 索引的最左匹配特性

2. 索引的种类

1. 聚集索引(primary key)

   聚集索引就是按照每张表的主键构造一颗B+树,同时叶子结点存放的即为整张表的行记录数据,也将聚焦索引的叶子结点称为数据页.

   优点:

   1. 它对主键的排序查找和范围查找速度非常快,叶子节点的数据就是用户所要查询的数据.
   2. 范围查询,即如果要查找主键某一范围内的数据,通过叶子节点的上层中间节点就可以得到页的范围,之后直接读取数据即可.

2. 辅助索引(普通索引)

   unique key(唯一索引) , index key(普通索引)

   辅助索引的叶子节点不包含行记录的全部数据,存放的是对应的那条数据的字段的值,除了包含键值以外,每个叶子节点中的索引行中还包含一个书签,书签里存放主键的值,该书签用来告诉InnDB存储引擎去哪找到与索引相对应的行数据,如果我们要的数据就是这个键,我们直接就可以在辅助索引的叶子节点找到对应的值,这种称为覆盖索引.如果通过辅助索引的叶子节点不能直接拿到我们想要的数据,需要通过辅助索引的叶子节点中保存的主键的值再去通过聚焦索引来找到完整的一条记录,这种操作称为回表操作.

3. 联合索引

   primary key(id,name): 联合主键索引

   unique key(id,name): 联合唯一索引

   index(id,name): 联合普通索引

   如果只查id和查id and name key使用联合索引,如果跳过第一个查后面的,无法使用联合索引.

3. 索引的操作

1. 聚集索引

   添加: 添加索引的时候要注意,给字段里面数据大小比较小的字段添加,给字段里面的数据区分度高的字段添加.
   创建的时候添加:
   	create table t1(id int primary key);
   	create table t1(id int,primary key(id));
   表创建完之后添加:
   	alter table t1 add primary key(id);
   删除主键索引:
   	alter table t1 drop primayr key;
   

2. 唯一索引

   创建的时候添加:
   	create table t1(id int unique);
   	create table t1(id int,unique key uni_name(id));
   表创建完之后添加:
   	alter table t1 add unique key u_name(id);
   删除主键索引:
   	alter table t1 drop index u_name;
   

3. 普通索引

   创建的时候添加:
   	create table t1(id int,index index_name(id));
   表创建完之后添加:
   	alter table t1 add index index_name(id);
   	create index index_name on t1(id);
   删除主键索引:
   	alter table t1 drop index index_name;
   	drop index index_name on t1;
   

4. 索引的两大类型

1. hash类型的索引: 查询速度快,范围查询慢
2. btree类型的索引: b+数,层数越多,数据量指数级增长(InnoDB默认支持)

8.6.5 使用索引注意事项

1. 范围问题,或者条件不明确,条件中出现这些符号或关键字: > ,< ,>= , <= ,!= ,between...and... ,like

   使用like的时候，通配符写在最前面，也是需要全匹配一遍，然后在比较字符串的第二个字符，最左匹配的规则

2. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越来越少,唯一键的区分度是1,而一些状态,性别字段可能在大数据面前区分度就0,一般需要join的字段我们要求是0.1以上,即平均1条扫描10条记录.

3. = 和 in 可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,MySQL的查询优化器会帮你优化成索引可以识别的形式

4. 索引列不能参与计算,保持列"干净"

5. and / or

1. and 的工作原理
	条件:
		a = 10 and b = 'xxx' and c > 3 and d = 4
	索引:
		制作联合索引(d,a,b,c)
	对于连续多个and,mysql会按照联合索引,从左到右的顺序找一个区分度高的索引字段,加快查询,即d>a>b>c
2. or 的工作原理
	条件:
		a = 10 or b = 'xxx' or c > 3 or d = 4
	索引:
		制作联合索引(d,a,b,c)
	对于连续多个or,mysql会按照条件的顺序,从左到右依次判断,即a>b>c>d

6. 最左前缀匹配原则

   对于组合索引MySQL会一直向右匹配,直到遇到范围查询(> , < , between , like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。