什么是索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。类似字典上面的目录,用物理空间换区搜索速度。
索引的使用
//创建
Alter table t2wkk add index `name`(`name`);
create index `name` on t2wkk(`name`);
alter table t2wkk add INDEX `age`(`age`);
//删除
Drop index `name` on t2wkk;
索引的优点。
1.可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
2.通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
如果说没用索引的查询是自行车,加上合适的索引就会是飞机。
索引什么时候用
1,主键自动建立唯一索引
2,频繁作为查询条件的字段应该创建索引
3,查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找
4,查询中统计或者分组的字段;
5,在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;
什么时候不需要创建索引
1,频繁更新的字段不适合创建索引,因为每次更新不单单是更新记录,还会更新索引,保存索引文件
2,where条件里用不到的字段,不创建索引;
3,表记录太少,不需要创建索引;
4,经常增删改的表;
5,数据重复且分布平均的字段,因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据,因此他建立索引就没有太大的效果,例如性别字段,只有男女,不适合建立索引。
索引会失效吗?为什么?
会
索引失效的原因
1,表里面列少的情况,譬如只有两列,因为这个时候mysql认为全表扫面要比使用索引快,则不使用索引。
2,对于使用 like 查询, 查询如果是 ‘%aaa’ 不会使用索引,而 ‘aaa%’ 会使用到索引。
3,索引上面进行运算,或者使用函数。
4,如果列类型是字符串,那么一定要在条件中使用引号引用起来,否则不使用索引。
5,建立索引时,给每一个索引列建立一个条目,如果查询条件为等值或范围查询时,索引可以根据查询条件去找对应的条目。反过来当查询条件为非时,索引定位就困难了,执行计划此时可能更倾向于全表扫描,这类的查询条件有:<>、NOT、in;
6,当查询条件存在隐式转换时,索引会失效。比如在数据库里id存的number类型,但是在查询时,却用了下面的形式:
select * from sunyang where id=‘123’;
7,还有就是我们去查数据库里面没有的数据
譬如,数据库里面有1000条数据,我们查第1500条,这个时候也会失效。
8,避免在where子句中使用or来连接条件,因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描
9,where 里面有 in 也不会走索引,key 是 primary 不是自己定义的id。
10,多列索引设置时请考虑好使用场景,如果有只查其中部分字段的情况,该索引可能不会生效;
譬如建立联合索引,a,b,c,如果只用a ,可以走索引,如果用a ,b 也可以,用 a,b, c也可以,但是用 b,c,就不可以,要遵循最左原则;
索引底层是什么?
MySQL索引使用的数据结构主要有BTree索引 和 哈希索引 。
对于哈希索引来说,底层的数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快;
其余大部分场景,建议选择BTree索引。
MySQL的BTree索引使用的是B树中的B+Tree,但对于主要的两种存储引擎的实现方式是不同的。
MyISAM 和 innoDB索引的不同
MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候,首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其 data 域的值,然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
InnoDB: 其数据文件本身就是索引文件。相比MyISAM,索引文件和数据文件是分离的,其表数据文件本身就是按B+Tree组织的一个索引结构,树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引(或聚集索引)”。而其余的索引都作为辅助索引,辅助索引的data域存储相应记录主键的值而不是地址,这也是和MyISAM不同的地方。在根据主索引搜索时,直接找到key所在的节点即可取出数据;在根据辅助索引查找时,则需要先取出主键的值,再走一遍主索引。 因此,在设计表的时候,不建议使用过长的字段作为主键,也不建议使用非单调的字段作为主键,这样会造成主索引频繁分裂。
什么是b+树
B+树
只有叶子节点存储data,叶子节点包含了这棵树的所有键值,叶子节点不存储指针,后来在B+树上增加了顺序访问指针,也就是每个叶子节点增加一个指向相邻叶子节点的指针
B+树的特征:(常见于关系型数据库,Mysql。)
有k个子树的中间节点包含有k个元素(B树中是k-1个元素),每个元素不保存数据,只用来索引,所有数据都保存在叶子节点。
所有的叶子结点中包含了全部元素的信息,及指向含这些元素记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。
所有的中间节点元素都同时存在于子节点,在子节点元素中是最大(或最小)元素。
B+树的优势:
单一节点存储更多的元素,使得查询的IO次数更少。
所有查询都要查找到叶子节点,查询性能稳定。
所有叶子节点形成有序链表,便于范围查询。
看到这个文章不错,一看就懂。b+树图文详解.
总结一下
其实这个文章就是把索引的大概给介绍了一下,近期一直在搞sql优化,搞的差不多了,所以这几天会慢慢的总结一下收获。这个早就总结在笔记上了,忘了上传博客了,突然看到就上传一下。