MySQL 索引优化

2018-10-20 数据库阅读量

想要做MySQL索引优化首先得了解MySQL索引!

索引的作用

当一个数据表只有很少量的数据时,索引体现不了他的价值。但若是一个表有10w多的数据时,查找数据就要全表查找,
最差要查找10w个数据,太慢了,若是用索引的话,就会大大减少时间,
例如对 score字段做一个索引,索引会生成一个文件,查找数据就到索引文件中查找,又因为索引出来的数据是已经排好序的,
所以查找时,数据库会用类似二分法的查找,这样会比不用索引快很多倍。

MySQL索引种类

索引是帮助mysql获取数据的数据结构。最常见的索引是Btree索引和Hash索引。

不同的引擎对于索引有不同的支持：**Innodb和MyISAM默认的索引是Btree索引;而Mermory默认的索引是Hash索引**。

FULLTEXT

即为全文索引,目前只有MyISAM引擎支持。
不过目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHERE name LIKE “%word%”这类针对文本的模糊查询效率较低的问题。

HASH

由于hash索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端,
主要有以下这些:

Hash 索引仅仅能满足”=”,”IN”和”<=>”查询,不能使用范围查询。
Hash 索引无法被用来避免数据的排序操作。
Hash 索引不能利用部分索引键查询。
Hash 索引在任何时候都不能避免表扫描。
Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

BTREE

B+树索引是B+树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引。
B+树中的B代表平衡（balance），而不是二叉（binary），因为B+树是从最早的平衡二叉树演化而来的。

BTREE索引就是一种将索引值按一定的算法,存入一个树形的数据结构中,
如二叉树一样,每次查询都是从树的入口root开始,依次遍历node,获取子节点上的数据。

BTREE索引分两类,一个是单列索引 另一个是多列索引

单列索引

单列索引是给单个字段做索引,如给math字段。

建立索引

1 2	//给 test表中的math字段加一个索引,索引名叫 math_index alter table test add index math_index(math);

用命令 show index from test;可以查看是否建立了索引：

建立索引后,我们就要使用索引,索引一般是用于查找信息（用select语句）

注意：
使用索引时,不能 select * ,这样的话,索引是无法使用的,select后面跟的应该包含索引所在的字段,
例如上面我设置了 math 字段的索引为 math_index
则就要写成 select math from test;（select math,english from test; 也可以,只要有math字段就行）
因为索引生成索引文件,里面放的是索引对应字段的数据。

使用 select math from test; 得出的结果：可以看出,数据是应该排好序的,因为只要是用到索引,被查找后的字段都会被排好序的。

删除索引

1 2	//删除test表中的名为 `math_index` 的索引 alter table test drop index math_index;

多列索引

多列索引是一个索引对应多个字段的数据。
例如我们想查找数学成绩>60 且英语成绩 >60 的学生。可以用多列索引,当然你说用2个单列索引,分别是数学和英语的索引行不行？
其实也是可以的,但也有区别,下面会讲解：

创建一个多列索引

// 在test表中创建一个 为名 indexes 的多列索引,包含 math,english字段
alter table test add indexs(math, english);

// 用 show index from test; 查看是否有创建。

然后对test表：
//在test表中找出数学和英语成绩都高于60的数据

1	select math, english from test where math>60 and english>60;

可以看出，数据也是排好序的。

删除多列索引跟删除单列索引一样。

最左前缀：

最左前缀的意思是，例如我创建的多列索引是（math，english）这样表示的，则：


select  math  from test;
select  math, english  from test;
//上面的写法都可以使用多列索引，第一行的写法相当于math的单列索引
// 而
selec english from test;
// 则会使索引失效, 因为他不是math开头

RTREE

RTREE在mysql很少使用,仅支持geometry数据类型,相对于BTREE,RTREE的优势在于范围查找。

BTREE和HASH的区别?

B-Tree

B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型，除了 Archive 存储引擎之外的其他所有的存储引擎都支持 B-Tree 索引。
不仅仅在 MySQL 中是如此，实际上在其他的很多数据库管理系统中B-Tree 索引也同样是作为最主要的索引类型，
这主要是因为B-Tree 索引的存储结构在数据库的数据检索中有非常优异的表现。

HASH

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，
所以 Hash 索引的查询效率要远高于 B-Tree 索引。

B-Tree索引中的最左匹配原则

Btree是按照从左到右的顺序来建立搜索树的。
比如索引是(name,age,sex),会先检查name字段,如果name字段相同再去检查后两个字段。

所以当传进来的是后两个字段的数据（age,sex）,因为建立搜索树的时候是按照第一个字段建立的,所以必须根据name字段才能知道下一个字段去哪里查询。

所以传进来的是（name,sex）时,首先会根据name指定搜索方向,但是第二个字段缺失,所以将name字段正确的都找到后,然后才会去匹配sex的数据。

建立索引的规则：

利用最左前缀：Mysql会一直向右查找直到遇到范围操作（>,<,like、between）就停止匹配。比如a=1 and b=2 and c>3 and d=6;
此时如果建立了（a,b,c,d）索引,那么后面的d索引是完全没有用到,当换成了（a,b,d,c）就可以用到。
不能过度索引：在修改表内容的时候,索引必须更新或者重构,所以索引过多时,会消耗更多的时间。
尽量扩展索引而不要新建索引
最适合的索引的列是出现在where子句中的列或连接子句中指定的列。
不同值较少的列不必要建立索引（性别）。