1.索引概述
MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。
2.索引的优势劣势
2.1 索引的优势
1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
2） 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
2.2 索引的劣势
1） 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
2） 虽然索引大大提高了查询效率，同时却也降低更新表的速度。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。可理解为书本中增加内容,目录也会变化.
3.索引结构
索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：
- BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
- HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
- R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
- Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。
我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。
3.1 BTREE 结构
BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：
- 树中每个节点最多包含m个孩子。
- 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。
- 若根节点不是叶子节点，则至少有两个孩子。
- 所有的叶子节点都在同一层。
- 每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1
BTREE树 和 二叉树 相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。
例如:现在插入数据1,2,3,4,5,6,7,8的整个过程.5叉树.
3.2 B+TREE 结构
B+Tree为BTree的变种，B+Tree与BTree的区别为：
1). n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
2). B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。
3). 所有的非叶子节点都可以看作是key的索引部分。
由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。
3.3 MySQL中的B+Tree
MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
4 索引分类
1） 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引
2） 唯一索引 ：索引列的值必须唯一，但允许有空值
3） 复合索引 ：即一个索引包含多个列
5 索引语法
索引在创建表的时候，可以同时创建， 也可以随时增加新的索引。
因为对于索引来说,少量的数据没有任何意义,我们这里有一张百万数据的用户表userinfo(user_test),百万数据如何手动制造请点击这个链接学习如何手动撸出百万数据
5.1 创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,...)
示例 ： 为userinfo表中的user_name字段创建索引 ；
create index index_username on userinfo(user_name);
好家伙!这里我们有大约93.6w条数据,创建这么一个索引用的时间是
时间: 136.552s
5.2 查看索引
等待了两分多钟之后建好了索引,查看一下.
查看索引的语法是show index from table_name;
show index from userinfo;
5.3 删除索引
删除索引的语法是
DROP INDEX index_name ON tbl_name;
因为我这里数据量巨大,而且后续还会用到,我就不手动删除了.免得以后再建.浪费宝贵的两分钟时间.
5.4 ALTER命令
顺带一提Alter命令
1). alter table tb_name add primary key(column_list);
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
2). alter table tb_name add unique index_name(column_list);
这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
3). alter table tb_name add index index_name(column_list);
添加普通索引， 索引值可以出现多次。
4). alter table tb_name add fulltext index_name(column_list);
该语句指定了索引为FULLTEXT， 用于全文索引
6.索引设计原则
索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。
-
对查询频次较高，且数据量比较大的表建立索引。
-
索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
-
使用唯一索引，区分度越高，使用索引的效率越高。
-
索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
-
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
-
利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。
顺带一提的是面试中经常问的东西:
创建复合索引:
CREATE INDEX idx_xx_yy_zz ON tb_tt(xx,yy,zz);
就相当于
对xx创建索引 ;
对xx, yy创建了索引 ;
对xx, yy, zz创建了索引 ;
7 索引的使用
开发中,索引通常可以帮助用户解决大多数的MySQL的性能优化问题.
上面我们给user_name建立了索引,我们来分析一波sql语句
EXPLAIN SELECT * from userinfo where user_name = '724db6540bfd799bc6fc92650d354cca'
它涉及到的rows(行数)只有2行.速度非常的快
而我们的
EXPLAIN SELECT * from userinfo
它涉及到的rows(行数)有900000多行.速度非常的慢.
由此可见索引给我们的查询速度带来的优化很大.
7.1 模糊查询索引失效?
当我们使用模糊查询的时候回发现一个很有趣的问题
EXPLAIN SELECT * from userinfo where user_name like '724db6540bfd799bc6fc92650d354cca%'
这个语句涉及的行数只有2行
EXPLAIN SELECT * from userinfo where user_name like '%724db6540bfd799bc6fc92650d354cca'
而这个涉及的行数有900000多行,一个顺序,就导致索引失效而全表扫描.如果一定要左模糊或者是’%xx%’,可以先加一个反转字段,然后使用右模糊,例如,加一个REVERSE_USER_NAME字段,也可以,但是我会去探索用索引函数解决这个问题.可能会在函数章节开一个小节,具体的去讨论解决方案.
7.2 其他的失效情况及解决方案
1.OR.将or条件中的每个列都加上索引
2.违背最左原则
3.字符串没有引号.加上引号即可.
END
本人才疏学浅,如有遗漏请大神指出,技术交流可私信,大家每天进步一点点.
这里给大家拜个早年了! @w@
扫一扫
274
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
