-
索引的分类
根据创建分类
1.主键索引(聚集索引 & 特殊索引)
主键索引也称为聚簇索引,其实我们在创建表后(比如以自增id为主键)时,主键索引已经建立好了;一张表中只能有一个主键索引,通常我们选取的主键索引(比如id)都是自增的,它的记录顺序与磁盘中的物理地址顺序相同的
2.二级索引(非聚集索引)
我们平时为了优化查询速度所添加的索引都可以称为二级索引,二级索引都可以看成是建立索引字段的与主键相关联的联合索引
根据使用分类
1.覆盖索引
覆盖索引简单来说,sql语句中的字段都被包含在索引中,可以理解为可以直接从索引上获取想要的数据,不需要回表进行查询,所表现出来的状态为用 EXPLAIN 解析出来的Extra 为 using index
2.辅助索引
与覆盖索引不同,所查询的数据并不是都能够在索引中找到,这个时候会进行回表查询,通常情况下如果回表率达到30%的情况下,Mysql可能会选择不适用索引,进行全表扫描,索引我们不会在比如性别上建立索引
根据字段数量分类
1.单索引
唯一索引 – 几乎为唯一值的字段 90%以上是惟一的
2.联合索引
通常我们优化查询语句时应用最多的索引 -
EXPLAIN* 会对我们使用的sql语句进行解析,下面的内容就是根据解析出的结果,得出我们将要优化sql语句的方向
type:告诉我们对标使用的访问方式,主要包含如下几种类型。
ALL, index, range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好)
const、system、NULL指查询优化到常量级别, 甚至不需要查找时间.
. all: 全表扫描,MySQL将遍历全表以找到匹配的行 ,逐行做全表扫描.,运气不好扫描到最后一行。
. const: 读常量,最多只会有一条记录匹配,由于是常量,实际上只需要读一次。
. eq_ref:最多只会有一条匹配结果,一般是通过主键或唯一键索引来访问
. fulltext:进行全文索引检索
. index:全索引扫描
. index_merge:查询中同时使用两个(或更多)索引,然后对索引结果进行和并,在读取表数据。
. index_subquery:子查询中的返回结果字段组合是一个索引(或索引组合),但不是一个主键或唯一索引。
. rang:索引范围扫描,只检索给定范围的行,使用一个索引来选择行,能根据索引做范围的扫描。
. ref:join语句中被驱动表索引引用的查询,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值,通过索引列,可以直接引用到某些数据行。
. ref_or_null:与ref的唯一区别就是使用索引引用的查询之外再增加一个空值的查询。
. system:系统表,表中只有一行数据:
. unique_subqery:子查询中的返回结果字段组合式主键或唯一约束。
Possible_keys:该查询可以利用的索引。如果没有任何索引可以使用,就会显示程null,这项内容对优化索引时的调整非常重要。
key:优化器从possible_keys中选择使用的索引。
key_len:被选中使用索引的索引建长度。
ref:列出是通过常量,还是某个字段的某个字段来过滤的
rows:优化其通过系统手机的统计信息估算出来的结果集记录条数
extra:查询中每一步实现的额外细节信息。
. using index : 出现这个说明mysql使用了覆盖索引,避免访问了表的数据行,效率不错。
. using where :这说明服务器在存储引擎收到行后讲进行过滤。
. using temporary :这意味着mysql对查询结果进行排序的时候使用了一张临时表
. using filesort :这个说明mysql会对数据使用一个外部的索引排序
注意当出现using temporary 和 using filesort时候说明需要优化操作 -
索引是根据B+树的结构来设计的,可以想象成平衡二叉树,通常我们使用的索引有B-tree,hash,fulltext(全文)三种索引
B-tree:我们通常最常使用的索引,那么它的查询速度就取决于树的高度,所以数据量的增大会影响索引对性能的损耗。我们平时所说的二分查找法可以方便我们理解索引的构造结构。首先数据一定是先进行了排序,所以我们说最好使用自增的主键(减少性能损耗),用PHP的代码展示应该是这样的
`<?php
//二分查找法
function binSearch($arr, $search)
{
$height = count($arr)-1;$low = 0;
while ($low <= $height) {
$mid = floor(($low + $height) / 2);//获取中间数
if ($arr[$mid] == $search) {
return $mid;//返回
} elseif ($arr[$mid] < $search) {//当中间值小于所查值时,则$mid左边的值都小于$search,此时要将$mid赋值给$low
$low = $mid + 1;
} elseif ($arr[$mid] > $search) {//中间值大于所查值,则$mid右边的所有值都大于$search,此时要将$mid赋值给$height
$height = $mid-1;
}
}
return "查找失败";
}`
hash:哈希索引值包含哈希值和行指针,而不存储字段值,所以不能使用索引的值避免数据库对于行的 读取(using index),这个值是无序且唯一的,所以在查找唯一数据的时候经常会用到,因为hash值不会跟随自增主键去自增,索引不适合用来范围查询
fulltext:全文索引只能应用于MYISAM的数据表,使用方式有点像正则表达式
where match(column1,column2) against('word1 word2 word3'),
+word:一个前导的加号表示该单词必须 出现在返回的每一行的开头位置。
-word: 一个前导的减号表示该单词一定不能出现在任何返回的行中。
(无操作符):在默认状态下(当没有指定 + 或–的情况下),该单词可有可无,但含有该单词的行等级较高。这和MATCH() ... AGAINST()不使用IN BOOLEAN MODE修改程序时的运作很类似。
( )括号用来将单词分成子表达式。括入括号的部分可以被嵌套。
~word:一个前导的代字号用作否定符, 用来否定单词对该行相关性的影响。 这对于标记“noise(无用信息)”的单词很有用。包含这类单词的行较其它行等级低。
word* :搜索以word开头的单词,只允许出现在单词的末尾
"word1 word" :给定单词必须出现在数据记录中,先后顺序也必须匹配,区分字母大小写
- 索引应该刨建在搜索、排序、归组等操作所涉及的数据列上,只在输出报告里出现的数据列不是好的候选。换句话说,那些在 WHERE子句、关联检索中的FROM子句、 ORDER BY或 GROUP BY子句中出现过的数据列最适合用来创建索引。只在 SELECT关键字后面的输出列清单里出现过的数据列并不是好的候选: