day039 数据库索引

今日内容:

1.为什么要有索引

简而言之,索引出现的意义是为了更方便,更快速的查询数据.

什么是索引

　　索引在mysql中也叫''键''或'key'(primary key unique key,index key),是存储引擎用于快速找到记录的一种数据结构.索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响越发重要,减少is次数,加速查询.(其中primary key和unique key,除了有加速查询的效果之外,还有约束的效果,primary key 不为空且唯一,unique key唯一,而index key 只有加速查询的效果,没有约束的效果).

　　索引优化应该是对查询性能优化最有效的手段了.索引能够轻松将查询性能提高好几个数量级.

　　强调: 一旦为表创建了索引,以后的查询最好先查询索引,再根据索引定位的结果去找数据.

2.索引的原理

　　索引的目的就在于提高查询效率,就好比查字典的目录一样,先定位到某一个字母,以此类推.直到找到这个字.

索引的影响: 

　　1.在表中有大量数据的前提下,创建索引速度会很慢.

　　2.在索引创建完毕后,对表的查询性能会大幅度提升,但是写性能会降低.

本质是 : 通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是用同一种查找方式在锁定数据.

3.聚集索引与辅助索引

在数据库中,B+树的高度一般都在2-4层,这也就是说,查找某一个键值的行记录时最多只需要2到4次IO,这倒不错,因为当前一般的机械硬盘至少可以做100次IO,2-4次的IO以为这查询时间只需要0.02-0.04秒.

数据库中的B+树索引可以分为聚集索引和辅助索引.

聚集索引与辅助索引相同的是,不管是聚集索引还是辅助索引,其内部都是B+树的形式,即高度是平衡的,椰子阶段存放着所有的数据.

不同的是 叶子节点存放的数据是否是一整行的信息.

聚集索引

#InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是
按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称
为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据
页都通过一个双向链表来进行链接。
    
#如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使
用它作为聚簇索引。
#如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。
#由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化
器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻
辑顺序，聚集索引能够特别快地访问针对范围值得查询。

　　聚集索引的好处之一: 他对主键的排序查找和范围查找速度非常快,叶子结点的数据就是用户所要查询的数据.如果用户需要查找一张表,查询最后的10位用户信息,由于B+树索引是双向链表,所以用户可以快速找到最后一个数据页,并取出10条记录.

　　聚集索引的好处之二: 范围查询 ,即如果要查找主键某一范围内的数据,通过叶子节点的上层中间阶段就可以得到页的范围,之后直接读取数据页即可.

 

辅助索引

　　我们在查询的时候,where后面需要些id之外的其他字段名字来进行查询,比如说where name=xx,没法用到主键索引的效率,这是就需要我们添加辅助索引了,给name添加一个辅助索引.

表中除了聚焦索引外其他索引都是辅助索引,与聚焦索引的区别: 辅助索引的叶子结点不包括行记录的全部数据.

mysql索引管理

1.索引的功能就是加速查找

2.mysql中的primary key ,unique ,联合唯一也都是索引,这些索引出了加速查找以外,还有约束的功能.

mysql常用的索引 

　　1.普通索引: index 加速查找

　　2.唯一索引: 

　　　　主键索引primary key :加速查找+约束(不能为空,不能重复)

　　　　唯一索引 unique : 加速查找+约束(不能重复)

　　3.联合索引:

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

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

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

 

索引的两大类型hash 与btree

我们可以在创建上述索引的时候,为其指定索引类型,分两类
hash类型索引: 查询单条快,范围查询慢
btree类型的索引:b+树,层数越多,数量指数级增长(我们就用它,因为innodb默认支持他)

#不用的储存索引引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

创建/删除索引的语法

删除索引 : 

　　语法: drop index 索引名 on 表名;

方法1和方法2 是在创建表的时候插入索引,方法3是创建表之后插入索引

方法一
    create table t1(
    id int,
    name char,
    age int,
    sex enum('male','female'),
    unique key un_id(id),
    index ix_name(name) # index 没有key
    );

方法二
    create index ix_age(age) on t1(age);

方法三
    alter table t1 add index ix_sex(sex);

查看
mysql> show create table t1;
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  UNIQUE KEY `uni_id` (`id`),
  KEY `ix_name` (`name`),
  KEY `ix_age` (`age`),
  KEY `ix_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

　　尽量选择区分度高的列作为索引,区分度公式是count() .表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态,性别字段可能在大数据面前区分度是0,