今日内容:
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,