MySQL索引
什么是索引?
描述 : 为表设置索引,会按照设置的索引生成索引文件,查询时通过索引文件来查询,从而提高查询速度
作用 : 可以大大提高查询速度
副作用 : 会降低更新表(insert,update,delete)的速度,因为更新表时,mysql不仅要保存数据,还要保存索引文件
索引分类 : 单列索引(多个单列叫多列索引)和组合索引, 单列索引:一个索引只包含单个列, 组合索引:一个索引内有多个列
怎么使用 : 在WHERE后边使用有索引的字段作为条件语句
索引类型
1.普通索引(INDEX)
特性 : 没有任何限制,可以重复
创建索引 : CREATE INDEX 索引名 ON 表名 (字段名(length))
修改表结构添加索引 : ALTER TABLE 表名 ADD INDEX 索引名(字段名(length) )
创建表的时候指定索引 : CREATE TABLE testtable(id int primary key, ... , INDEX [索引名] (字段名(length)) );
删除索引 : DROP INDEX 索引名 ON 表名
创建组合索引 : ALTER TABLE 表名 ADD INDEX 索引名(字段1(length), 字段2(length), ... )
2.唯一索引(UNIQUE INDEX)
特性 : 索引的值必须唯一,如果是组合索引,则组合索引必须唯一,单个列的值不必唯一.
创建索引 : 同上普通索引, 把INDEX改成UNIQUE INDEX即可,修改表结构添加索引,建表时添加索引时可以只用UNIQUE
3.全文索引(FULLTEXT INDEX)
省略
4.多列索引
MYSQL在查询时,将选择一个限制最为严格的索引使用
5.组合索引(最左前缀)
最左前缀 : 查询条件要按照索引的从左到右的顺序使用,否则不会使用索引
比如创建了组合索引 : ALTER TABLE mytable ADD INDEX index_name_sex (name(24), sex(1) )
实际上在查询时只有两种组合会用到索引,分别是:
name, sex
name
其他的排列顺序就不会使用索引,比如sex, name 或者 sex
不使用索引的例句 : SELECT * FROM mytable WHERE sex=1 AND name='zhangsan';
聚集索引和非聚集索引
一.聚集索引和非聚集索引的定义
MySQL, InnodDB存储引擎下:
默认主键为聚集索引,如果没有设定主键,则第一个唯一索引作为聚集索引.如果也没有唯一索引,innoDB会隐式定义一个6字节的rowid主键来作为聚集索引
除聚集索引外的索引,是非聚集索引
二.聚集索引
聚集索引就像是字典里的拼音目录
一张表只有一个聚集索引,(默认就是主键)
数据在磁盘中的物理地址排列方式,与聚集索引的顺序相同,如果我们查询id靠后的数据,那么这个数据的物理地址也靠后
如果在数据中间部分插入一个新的数据,那么后边的数据物理位置全都要改变!
三.非聚集索引
非聚集索引就像是字典里的偏旁索引,索引的顺序未必就是数据的排列顺序.
BTREE索引和HASH索引
不同的引擎对于索引有不同的支持：InnoDB和MyISAM默认的索引是Btree索引；而Mermory默认的索引是Hash索引
BTREE索引
Btree索引是以B+树为存储结构实现的,Innodb和MyISAM默认的索引是Btree索引
但InnoDB和MyISAM所使用的Btree存储结构有很大不同
一.MyISAM
1).MyISAM的主索引
上图是MyISAM主索引的结构图, 以Col1作为主键,我们看这棵树的叶节点保存的是数据的物理地址
在MyISAM中,主索引和辅助索引在结构上没有任何区别,唯一的区别是主索引要求key唯一,而辅助索引的key可以重复
2).MyISAM的辅助索引(在Col2上建立的)
上图可见,MyISAM下,主索引和辅助索引的索引结构没有不同.都是在叶节点保存数据的物理地址
所以,MyISAM中检索索引的方法是
1.按照Btree搜索算法搜索索引,如果key存在,则取出起data域的值
2.以data域的值为地址,读取对应的数据.
MyISAM的索引文件和数据文件(表)是分离的
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分
二.innoDB
innoDB存储引擎下,数据文件本身就是索引文件,表本身就是主索引,辅助索引文件其实也是一张表
1).innoDB下的主索引
上图可见,innoDB的主索引(同时也是数据文件)的叶节点包含了完整的数据,这种索引叫聚集索引(以主键作为索引),因为innoDB的数据文件本身要按照主键聚集,所以innoDB要求表必须有主键.而innoDB的辅助索引和主索引不同.
2).innoDB的辅助索引
上图可见,innoDB的辅助索引保存的是主键的值,使用这种索引需要检索两遍索引
1.首先检索辅助索引获得主键.
2.然后用得到的主键在主索引中获取数据.
BTREE索引总结
1.innoDB主索引是聚集索引,因为数据要按照主键聚集. 而innoDB的辅助索引和MyISAM的主索引和辅助索引则是非聚集索引.
2.innoDB的主索引的索引文件就是表本身,保存完整数据,辅助索引保存的是主键id,MyISAM的主索引和辅助索引都保存的是数据的物理地址,唯一的区别是主索引的key必须唯一
3.使用innoDB存储引擎,表必须要有主键, 而使用MySIAM存储引擎,表没有主键也不影响.
4.使用索可以引消除排序,order by按照索引来写即可.(可以提高速度)
Hash索引
先不写,以后有需要了再来写
参考文章:
https://www.cnblogs.com/shijingxiang/articles/4743324.html
https://www.jianshu.com/p/bf30c2b9a0cf
f
