SQL索引的初步入门,索引白小

SQL索引

MySQL索引类似数组的下标,它类似数据库中记录的下标,索引是一种能加快SQL查询的数据结构,目前版本MySQL数据库的索引采用的是B树的数据结构用来维护,如果不了解什么是B树结构,可以去查一下,本质是一直二叉树

使用索引的好处:
1:增加查询效率,我们甚至可以在几次IO操作就可以完成百万数据量的查询(一个烂大街的图书馆找书的例子)
2:可以增加排序效率,
坏处:
1.索引不适合  "写"  这种操作,频繁的更改会让数据库一遍维护表,一遍维护索引反而降低效率

索引是保存在硬盘中的,不是在内存之中,索引实际上在数据库中也是保存了一张表

基本语法

UNIQUE 唯一索引
ON 在哪一张表上的哪些字段,我们进行建立索引

B+树查找过程

磁盘IO操作比内存中的二分查询要慢的多

索引的分类

单值索引:一个索引只包含单个列,一张表可以包含多个索引
唯一索引:就是索引包含的字段,该字段保持唯一性,允许为空
复合索引:就是一个索引包含多列

索引的使用场景

哪些情景可以创建索引

哪些情景不会使用索引
1.表的记录太少
(一般一张表的记录超过三百万的时候,就可以考虑给表建立索引了,虽然SQL官方建议是五百万到八百万)
2.经常变更的字段
(不会给经常变更的字段建立索引,虽然会提高查询效率,但是会降级修改的效率,原因就是数据库不仅要保存数据,还要保存一些索引文件)
3.内容重复率高的字段
(某些字段的内容重复率太高,例如国籍,性别,重复率过高也就没有创建的必要了,不重复记录数/总记录数=索引选择性,索引选择性越接近1,越有创建的必要)

索引的性能分析

MySQL有自己的索引执行顺序,可能不会按照DBA设计的SQL语句的本意去执行SQL,这个时候我们可以通过explain来查看SQL执行

使用explain关键字可以模拟优化执行器的SQL查询语句,从而让我们知道MySQL是如何执行SQL语句的,这样就可以分析我们的SQL语句或者是表结构的性能瓶颈.

怎么使用explain
explain+SQL语句
记住下面查询到的这些字段,

explain能干嘛

id:决定表的执行顺序
select_type: 决定数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的应用
每张表有多少行被优化器查询

id属性: 决定表的执行顺序

下面的是第三种情况

select_type属性
声明查询的类型是什么 主要是用于区别普通查询,联合查询,子查询等复杂的查询.

观察下面这个例子,

table属性:
指明是哪一张表

type属性:
访问类型排列,
显示查询使用了何种类型,
从最好到最差依次是:
System>const>eq_ref>ref>range>index>ALL
这是是精简版的排列,还有更复杂的不过我们已经够用了
一般来说百万级别的数据我们要达到range级别,最好达到ref级别

在工作中能够达到ref级别,已经是一条非常优秀的SQL语句了
展示一部分测试过程

possible_keys属性:
理论上SQL语句能够使用的索引
显示可能被使用到的索引,显示一个或者多个,并且查询涉及到的字段上有没有设计索引,如果有也会展示出来,但是不一定被查询使用这条索引

key属性
所谓的索引失效,为null的话代表要么没有建立索引,要么建立的索引,索引失效了
展示实际使用索引,如果为null,则代表没有使用索引.
如果查询中出现了覆盖索引,则该索引只出现在key值列表中.

简单说一下什么是**覆盖索引:**就是我们select 后面查询的字段刚好符合索引的个数和内容,这个时候就是覆盖索引
查询的数据要和建立的索引是一样的,

下面这个查询中就是一个覆盖索引,只查询了col1,col2,而我们给这张表建立的两个索引也是col1,col2

上面这两个属性组合在一起可以供我们分析SQL语句是否使用了索引,可能会出现索引失效的情况,也可能出现possible_keys为null 但是key却不为null 的情况,这些各种各样复杂的情况,体现了SQL运行可能和程序员手写SQL时的本意不一样的情况

key_len属性
**表示索引中使用的字节数,**可以通过这个属性来计算索引的使用长度,在不损失精度的情况下,长度自然是越短越好,key_len显示的值为索引字段的最大可能长度,并不是实际的使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的

举例来看

不同的SQL语句我们得出相同的查询结构,这个时候第一种情况优于第二种情况,两条SQL语句通过key可以知道,他们使用了相同的索引,但是第二种情况的SQL限定条件更多,也代表在缩影中使用的字节更长,通过key_len就可以观察分析出SQL语句的好坏.

ref属性
显示索引的哪一列被使用了,如果可能的话,是一个常数.那些列或常数被用于查找索引列上的值.

t1.col1索引和shared库的t2表的col1字段进行比对.
t1.col2索引和一个常数"ac"进行比较

row属性
统计索引的选用情况,大致估算出找到所需的记录所需要读取的行数.
通过下面的操作可以看出建立索引之后,查询的行数确实有所减少.

Extra属性
显示十分重要的额外信息(指明SQL的一些重要操作)

Using filesort
没有按照索引顺序进行读取排序
MySQL无法利用索引完成排序称为"文件排序"

说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,
而是MySQL重新给自己设计了一条排序路径,我们都知道索引实现排序功能,
但是我们设计的索引对SQL中的排序没有生效,SQL没有按照我们预定的索引去排序,
而是按照自己的规则去排序,这样就拖慢了SQL执行的速度

当出现这条属性的时候,这条SQL语句就很危险了,这条SQL的执行速度在处理大数据的时候很可能会很慢.
从下面可以知道,order by在排序的时候可以遵从预定的索引,这样就不会另外创建排序方法.

using temporary
使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表.常见于排序order by 和分组查询 group by .
在使用索引分组的时候,要按照索引顺序来,不要一步登天,这样他同样会自己建立分组规则

using index
表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率不错,如果同时出现useing where表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而不是查找数据

using where
SQL语句使用查找操作,

using join buffer
SQL语句使用了连接缓存.
如果SQL语句join的表特别的,可以去配置文件的缓冲区中的join buffer 配置调大一点

impossible where
说明这个SQL语句的where查询的结果总是false,也就是说按照查询条件,SQL根本就查询不到记录.

select tables optimized away

distinct
优化distinct操作,在找到第一匹配的元组后即可停止找同样值的动作,