索引是什么
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。
可以简单理解为“排好序的快速查询数据结构”
在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。
为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一 定的复杂度内获取到相应数据,从而快速的检索出符合条件的记录。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上
查看磁盘占用情况:df -lh ,Used:已经使用的空间;Avail:可以使用的空间;Mounted on:挂载的目录。
如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+数索引,统称使用。当然,除了B+树这种类型的索引之外,还有哈希索引(hash index)等。
优势
提高数据检索的效率,降低数据库的IO成本。通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
劣势
实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的。
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询。
mysql索引分类
单值索引
即一个索引只包含单个列,一个表可以有多个单列索引。
唯一索引
索引列的值必须唯一,但允许有空值。
复合索引
即一个索引包含多个列。
基本索引
创建
CREATE [ UNIQUE ] INDEX indexName ON mytable(columnname(length));
删除
DROP INDEX [ indexName ] ON mytable;
查看
SHOW INDEX FROM table_name\G
使用ALTER命令
有四种方式来添加数据表的索引:
ALTER TABLE tbl _name ADD PRIMARY KEY (column_ lit); 该语句添加一个主键,这意味着索引值必须是唯-一的,且不能为NULL。
ALTER TABLE tbl _name ADD UNIQUE index_ name (column_ list); 这条语向创建索引的值必须是唯一一的(除了NULL外,NULL可能会出现多次)。
ALTER TABL E tbl_ _name ADD INDEX index_ _name (column_ list); 添加普通索引,索引值可出现多次。
ALTER TABLE tbl name ADD FULLTEXT index_ name (column lst); 该语句指定了索引为FULLTEXT,用于全文索引。
mysql索引结构
BTree索引
检索原理:
[初始化介绍]
一颗b+树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1. P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。
[查找过程]
如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间, 锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。
真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个
数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。
还包括Hash索引,full-text全文索引,R-Tree索引
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段,外键关系建立索引
- 频繁更新的字段不适合创建索引。因为每次更新不单单是更新了记录还会更新索引。
- Where条件里用不到的字段不创建索引
- 单键/组合索引的选择问题,who? (在高并发下倾向创建组合索引)
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
哪些情况不要创建索引
- 表记录太少
- 经常增删改的表
原因:Why:提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE 和DELETE。 因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
- 数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。
注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。比如:国籍,性别
举一个栗子
假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1,这个索引的效率就越高。