三、索引简介
是什么:
- 索引(Iindex)是帮助MySQL高效获取数据的数据结构。即索引的本质就是数据结构。
- 理解为:
排好序
的快速查找
的数据结构。 - 数据本身之外,数据库还维护着一个
满足特定查找算法的数据结构
,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
- 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。
- 我们平常所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种类型的索引之外,还有哈稀索引(hash index)等。
优势:
- 类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO(输入和输出)成本
- 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
劣势:
- 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT,UPDATE和DELETE. 因为更新表时,MysQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
- 索引只是提高效率的一个因素,如果你的MysQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句
索引分类:
1、单值索引
一个索引只包含单个列,一个表可以有多个单值索引(一般不超过五个)
2、唯一索引
索引列的值必须唯一,但允许有空值。关键字 unique
3、复合索引
一个索引包含多个列。
索引基本语法:
创建
方法1:
# indexName 自定义的索引名字
# mytable 表名 columnname(length) 字段
create [unique] INDEX indexName
ON mytable(columnname(length)) ;
方法2:
ALTER mytable ADD [UNIQUE] INDEX [indexName】 ON (columnname(length))
删除
DROP INDEX [indexName】 ON mytable;
查看
SHOW INDEX from mytable;
常见ALTER添加索引的方法:
# 主键索引:主键意味着索引值必须唯一且非空
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
# 唯一索引,索引的值必须唯一(除了NULL)
ALTER TABLE 表名 ADD UNIQUE index_name(column_list);
# 普通索引,索引可多次出现
ALTER TABLE 表名 ADD INDEX index_name(column_list);
# 全文索引
ALTER TABLE 表名 ADD FULLTEXT index_name(column_list);
MySQL索引结构
- BTree索引(目前主要用的)
- Hash索引
- full-text索引
- R-tree索引
初始化介绍 - 一颗
b+树
,浅蓝色的块我们称之为一个磁盘块
,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项
17和35,包含指针P1,P2,P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。 - 真实的数据存在于叶子节点即3、5、9,10,13.15,28,29、36,60,75,79,90、99.
- 非叶子节点只
不存储
真实的数据,只存储指引搜索方向的数据项
,如17、35并不真实存在于数据表中。
查找过程
如果要查找数据项29
,那么首先把磁盘块1
由磁盘加载到内存
,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针
,内存时间因为非常短(相比磁盘的10)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。
分析
真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有素引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。
哪些情况需要创建索引
- 主键自动建立唯一索引;
- 频繁作为查询条件的字段应该创建索引;
- 查询中与 其他表关联的字段,外键关系建立索引;
- 频繁更新的字段不适合创建索引(因为更新记录的同时,索引也会更新)
- Where条件里用不到的字段不创建索引;
- 单键/组合索引的选择问题,在高并发下倾向创建组合索引;
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度;
- 查询中统计或者分组字段。
那些情况不需要创建索引
- 表的记录较少;
- 经常增删改的表;(提高了查询的速度,同时也会降低更新表的速度,如对表进行INSERT、UPDATA、DELETE。因为更新表的时候,MySQL不仅要保存数据 ,还要保存一下索引文件。)
- 数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。如性别等
四、性能分析
-
MySQL Query Optimizer(数据库查询优化器)
不是大牛,一般都是用的默认的(框架中服务层中的优化器)
原理:
MySQL中有专门负责优化SELECT语句的优化器模块,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的Query提供他认为最优的执行计划。(他认为最优的数据检索方式,不见得就是DBA认为最优的,这部分就是最耗时的。) -
MySQL常见的瓶颈
- CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
- IO:磁盘IO瓶颈发生在装入数据远大于内存存储的时候
- 服务器硬件的性能瓶颈:top、free、iostat、vmstat来查看系统的性能状态
- Explain:当上述两个都没有问题得时候,使用这个生成报告检查问题
是什么(执行计划):
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
能干什么:
- 表的读取顺序;
- 数据读取操作的操作类型;
- 哪些索引可以使用;
- 哪些索引被实际使用;
- 表之间的引用;
- 每张表有多少行被优化器查询。
怎么玩
explain+sql语句
select * from tbl_emp;
explain select * from tbl_emp;
执行计划主要包含的内容:
id,表的读取顺序
select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序;常见有三种情况:
1、id相同,mysql执行顺序由上至下;
2、id不同,如果是子查询,id的序号会递增,id的值越大优先级越高,越先被执行。t3>t1>t2
3、id相同不同,同时存在。id相同可以认为是一组,从上至下顺序执行;在所有组中,都是id越大,优先级越高,越先被执行。其中derived表示衍生的虚拟表,如图中的derived2表示id2对应的t3生成的虚拟表s1。
select_type,数据读取操作的操作类型
类型:
- simple 简单查询,简单的select查询,查询中不包活子查询或者union
- primary 基础查询,相当于鸡蛋壳,最后查询的东西,即查询中若包含任何复杂子部分,最外层查询被标记为primary
- subquery 子查询,在select或者where中包含了子查询
- derived 衍生,在from列表中包含的子查询被标记为衍生,mysql会递归这些子查询,把结果放在临时的表里面。
- union 联合,若第二个select出现在union之后,则被标记为union;如若union包含在from子句的子查询中,外层select将被标记为derived。
- union result 复杂查询,从union获取的结果的select。
table
显示这一行数据是关于那张表的
type,显示访问的类型,是较为常用的一个指标。
常用类型:
-
system,表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,可以
忽略
这种情况的出现。 -
const,表示通过索引
一次
就能找到,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。如将表主键置于where列表中,mysql就能将该查询转换为一个常量。(主键索引和唯一索引)
-
eq_ref,
唯一性
索引扫描,对于每个索引建,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。(一个公司只有一个ceo)
-
ref,
非唯一性
索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。(一个公司有好多程序员) -
range,只检索给定范围的行,使用一个索引来选择行。Key列显示使用了哪个索引,一般就是在你的where语句中出现了
between、<、>、in
等的查询。这种范围扫描索引比全表扫描要好,因为他只需要开始于索引的某一点,结束于另一点,不需要扫描全部索引。
-
index,Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比All快,因为索引文件通常比数据文件小。也就是说,虽然all和index都是读全表,但是index是从索引中读取,而all从硬盘中读取。
-
ALL,full table scan,将遍历全表以找到匹配的行。
结果值从最好到最差的排序:一般来说保证在range级别,最好能到ref
system > const> eq_ref > ref > range > index > ALL
possible_keys VS key
possible_keys:显示可能应用在这张表中的索引,一个或多个。
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
。
key:实际使用的索引。如果为NULL,则没有使用索引。查询中若使用了覆盖索引(查找的和*保持了一致),则该索引仅出现在Key列表中。
即,类比结婚宴席,possible_keys表示预计请客人数,key表示实际到的人数。
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度
,即key_len是根据表定义计算而得,不是通过表内检索出的。
同样的查询结果,key_len越小越好。
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。
rows,每张表有多少行被优化器优化
根据表统计信息及索引选用情况,大致估算找到所需的记录所需要读取的行数。越小越好。
Extra
包含不适合在其他列中显示但十分重要的额外信息。类似于“备注”
-
Using filesort(文件内排序):说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序"。(拖慢运行)
排序的顺序尽量要和索引的顺序保持一致。 -
Using temporary:使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。(拖慢运行)
-
Using index:表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,
效率不错!
如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。(好的,加速运行的)
覆盖索引(Covering Index),也说为索引覆盖。就是select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
其他额外信息(了解即可):
案例分析:
执行顺解析:id等于几,就是第几个select,越大表示越先被执行
1、id=4优先级最高,类别为union,说明第四个select是union整体的第二个select,最先被执行【select name,id from t2】;
2、id=3,第二步被执行,类别为衍生,在整个查询表里第三个select部分【select id,name from t1 where other_column=‘’】 d1,因查询在from中所以是衍生,执行的表是t1;
3、id=2,第三步被执行,类别为子查询subquery,selet后面,整个查询表中的第二个select,执行表为t3;
4、最后一个执行,di=1,基础查询,第一个select,外壳,外层查询(primary),table为衍生表derived3,其中3表示第三个select,即id=3的查询结果作为衍生表。
5、最后汇总,union result ,代表从union的临时表中读取行的阶段,table为【union,4】表示用第一个和第四个select的查询结果。