今天给大家分享Mysql的索引优化分析

性能下降SQL慢的原因

1- 执行时间长、等待时间长

2- 查询数据过多

能不能将sql拆分,条件过滤尽量变少

3- 关联了太多的表,太多join

join 原理。用A表的每一条数据扫描B表的所有数据。所以尽量先过滤。

4- 没有利用到索引

索引针对列建索引。但并不可能每一列都建索引

索引并非越多越好。当数据更新了,索引会进行调整。也会很消耗性能。

且 mysql 并不会把所有索引都用上,只会根据其算法挑一个索引用。所以索引怎样建很重要。

5- 服务器调优及各个参数设置(缓冲、线程数等)(DBA的工作)

常见通用的Join查询

1- SQL执行顺序

·· 程序员手写

·· 机读

随着Mysql版本的更新换代,其优化器也在不断的升级,优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。

下面是经常出现的查询顺序:

·· 总结鱼翅图

2- sql join图

3- 7种JOIN

CREATE TABLE `t_dept` ······;

CREATE TABLE `t_emp` ······;

1 A、B两表共有

select * from t_emp a inner join t_dept b on a.deptId = b.id;

2 A、B两表共有+A的独有

select * from t_emp a left join t_dept b on a.deptId = b.id;

3 A、B两表共有+B的独有

select * from t_emp a right join t_dept b on a.deptId = b.id;

4 A的独有

select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null;

5 B的独有

select * from t_emp a right join t_dept b on a.deptId = b.id where a.deptId is null;

#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法

#left join + union(可去除重复数据)+ right join

SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id

SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id

这里因为要联合的缘故,不能考虑到小表驱动大表的情况。只能用right join。要保证查询出来的数字要一致。

7 A的独有+B的独有

SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL

SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;

4- join与子查询比较

两者区别:

思想上的区别:

子查询理解:①先知道需要查询并将数据拿出来(若from 后的表也是一个子查询结果)。②在去寻找满足判断条件的数据(where,on,having 后的参数等)。而这些查询条件通常是通过子查询获得的。

子查询是一种根据结果找条件的倒推的顺序。比较好理解与判断

join理解:执行完第一步后的结果为一张新表。在将新表与 t_emp 进行下一步的 left join 关联。

先推出如何获得条件,再像算数题一样一步一步往下 join。可以交换顺序,但只能是因为条件间不相互关联时才能交换顺序。

join 比子查询难一点

但是:join 能用到索引,子查询出来的表会使索引失效。

求所有人物对应的掌门:

id  deptName  address       CEO

1       华山派          华山          2

id  name     age  deptId

1  风清扬     90         1

1.使用子查询(不推荐,影响后续用索引)

步骤:a.创建子查询  查询出每个门派对应的ceo

b. 根据t_emp 对应的 deptId 关联子查询表查询出所有人物对应的 ceo

SELECT a.name,f.deptName,f.name FROM t_emp a

LEFT JOIN (SELECT d.`id`,e.`name` ,d.`deptName` FROM t_dept d

LEFT JOIN t_emp e

ON d.`CEO`=e.`id`) f

ON a.deptId = f.id

2.使用join(推荐)

步骤:a. 关联出每个人物对应的门派

b.通过门派的 ceo 关联对应的掌门

SELECT e.`name`, d.`deptName`,f.`name` ceo FROM t_dept d

RIGHT JOIN t_emp e

ON d.`id` = e.`deptId` ##第一步--->得到关联了部门的一张新的联合表

LEFT JOIN t_emp f

ON d.`CEO`=f.`id`       ##第二步  --->通过新的联合表中的数据与另一张表关联

索引简介

1- 是什么

·· 定义

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。

可以得到索引的本质:索引是数据结构。

索引的目的在于提高查询效率,可以类比字典,假如如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。

·· 你可以简单理解为“排好序的快速查找的数据结构”

在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,

这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。下图是一种示例:

左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址

为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取到相应数据,从而快速的检索出符合条件的记录。

二叉树弊端之一:二叉树很可能会发生两边不平衡的情况。

B-TREE: (B:balance)  会自动根据两边的情况自动调节,使两端无限趋近于平衡状态。可以使性能最稳定。(myisam使用的方式)

B-TREE弊端:(插入/修改操作多时,B-TREE会不断调整平衡,消耗性能)从侧面说明了索引不是越多越好。

B+TREE:Innodb 所使用的索引

一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上

我们平常所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引,次要索引,覆盖索引,

复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种类型的索引之外,还有哈稀索引(hash index)等。

2- 建索引的优势

类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本

通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗

3- 建索引的劣势

实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的

虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。

因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,

都会调整因为更新所带来的键值变化后的索引信息

索引只是提高效率的一种方式,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句(需要结合实际业务)

4- mysql索引结构

·· BTree索引(Myisam普通索引)

【初始化介绍】

一颗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指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。

真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

·· B+Tree索引(innodb的普通索引)

B+TREE 第二级的数据并不能直接取出来,只作索引使用。在内存有限的情况下,查询效率高于 B-TREE

B-TREE 第二级可以直接取出来,树形结构比较重,在内存无限大的时候有优势。

·· B树和B+树的区别

在内存有限的情况下,B+TREE 永远比B-TREE好。无限内存则后者方便

1)B-树的关键字和记录是放在一起的,叶子节点可以看作外部节点,不包含任何信息;B+树叶子节点中只有关键字和指向下一个节点的索引,记录只放在叶子节点中。(一次查询可能进行两次i/o操作)

2)在B-树中,越靠近根节点的记录查找时间越快,只要找到关键字即可确定记录的存在;而B+树中每个记录的查找时间基本是一样的,都需要从根节点走到叶子节点,而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好,而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据,这样每个节点可容纳的元素个数比B-树多,树高比B-树小,这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多,但是一次磁盘访问的时间相当于成百上千次内存比较的时间,因此实际中B+树的性能可能还会好些,而且B+树的叶子节点使用指针连接在一起,方便顺序遍历(例如查看一个目录下的所有文件,一个表中的所有记录等),这也是很多数据库和文件系统使用B+树的缘故。

思考:为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引?

1) B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

2) B+树的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

·· 聚簇索引与非聚簇索引(了解)

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。

术语‘聚簇’表示数据行和相邻的键值进错的存储在一起。

如下图,左侧的索引就是聚簇索引,因为数据行在磁盘的排列和索引排序保持一致。

聚簇索引的好处:

按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以节省了大量的io操作。

聚簇索引的限制:

对于mysql数据库目前只有innodb数据引擎支持聚簇索引,而Myisam并不支持聚簇索引。

由于数据物理存储排序方式只能有一种,所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。

为了充分利用聚簇索引的聚簇的特性,所以innodb表的主键列尽量选用有序的顺序id,而不建议用无序的id,比如uuid这种。(参考聚簇索引的好处。)

这里说明了主键索引为何采用自增的方式:1、业务需求,有序。2、能使用到聚簇索引

·· full-text全文索引(了解)

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

CREATE TABLE `article` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(200) DEFAULT NULL,

`content` text,

PRIMARY KEY (`id`),

FULLTEXT KEY `title` (`title`,`content`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

不同于like方式的的查询:

SELECT * FROM article WHERE content LIKE ‘%查询字符串%’;

全文索引用match+against方式查询:

SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’);

明显的提高查询效率。

mysql5.6.4以前只有Myisam支持,5.6.4版本以后innodb才支持,但是官方版本不支持中文分词,需要第三方分词插件。

5.7以后官方支持中文分词。

随着大数据时代的到来,关系型数据库应对全文索引的需求已力不从心,逐渐被 solr,elasticSearch等专门的搜索引擎所替代。

·· Hash索引(了解)

Hash索引只有Memory, NDB两种引擎支持,Memory引擎默认支持Hash索引,如果多个hash值相同,出现哈希碰撞,那么索引以链表方式存储。

NoSql采用此中索引结构。

·· R-Tree索引(了解)

R-Tree在mysql很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。

相对于b-tree,r-tree的优势在于范围查找。

5- mysql索引分类

·· 主键索引

设定为主键后数据库会自动建立索引,innodb为聚簇索引

随表一起建索引:

CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),

PRIMARY KEY(id)

unsigned (无符号的)

使用  AUTO_INCREMENT 关键字的列必须有索引(只要有索引就行)。

CREATE TABLE customer2 (id INT(10) UNSIGNED   ,customer_no VARCHAR(200),customer_name VARCHAR(200),

PRIMARY KEY(id)

单独建主键索引:

ALTER TABLE customer

add PRIMARY KEY customer(customer_no);

删除建主键索引:

ALTER TABLE customer

drop PRIMARY KEY ;

修改建主键索引:

必须先删除掉(drop)原索引,再新建(add)索引

·· 单值索引

即一个索引只包含单个列,一个表可以有多个单列索引

索引建立成哪种索引类型?

根据数据引擎类型自动选择的索引类型

除开 innodb 引擎主键默认为聚簇索引 外。 innodb 的索引都采用的 B+TREE

myisam 则都采用的 B-TREE索引

随表一起建索引:

CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),

PRIMARY KEY(id),

KEY (customer_name)

随表一起建立的索引 索引名同 列名(customer_name)

单独建单值索引:

CREATE  INDEX idx_customer_name ON customer(customer_name);

删除索引:

DROP INDEX idx_customer_name ;

·· 唯一索引

索引列的值必须唯一,但允许有空值

随表一起建索引:

CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),

PRIMARY KEY(id),

KEY (customer_name),

UNIQUE (customer_no)

建立 唯一索引时必须保证所有的值是唯一的(除了null),若有重复数据,会报错。

单独建唯一索引:

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

删除索引:

DROP INDEX idx_customer_no on customer ;

·· 复合索引

即一个索引包含多个列

在数据库操作期间,复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)

当表的行数远大于索引列的数目时可以使用复合索引

随表一起建索引:

CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),

PRIMARY KEY(id),

KEY (customer_name),

UNIQUE (customer_name),

KEY (customer_no,customer_name)

单独建索引:

CREATE  INDEX idx_no_name ON customer(customer_no,customer_name);

删除索引:

DROP INDEX idx_no_name  on customer ;

·· 基本语法

··· 创建

ALTER mytable ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length))

··· 删除

DROP INDEX [indexName] ON mytable;

··· 查看

SHOW INDEX FROM table_name\G;

non_unique: 是否是唯一索引  1:是   0:不是

seq_in_index:列 在索引中的 序列。针对符合索引(一个索引对应多个列)。针对同一个复合索引 按照创建复合索引时的顺序进行排序

cardinality:

Null:是否允许 null 值

index_comment:

··· 使用ALTER命令

有四种方式来添加数据表的索引:

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。

·· 哪些情况需要创建索引

1.主键自动建立唯一索引

2.频繁作为查询条件的字段应该创建索引(where 后面的语句)

3.查询中与其它表关联的字段,外键关系建立索引

A表关联B表:A join B  on 后面的连接条件 既 A 表查询 B 表的条件

所以B表被关联的字段建立索引能大大提高查询效率

因为在join中,join左边的表会用每一个字段去遍历B表的所有的关联数据,相当于一个查询操作

4.单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)

5.查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度

group by 和 order by 后面的字段有索引大大提高效率

6.查询中统计或者分组字段

·· 哪些情况不要创建索引

1.表记录太少

2.经常增删改的表

Why:提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。

因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件

3.Where条件里用不到的字段不创建索引

索引建多了影响 增删改 的效率

4.数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。

注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。

性能分析

1- MySQL常见瓶颈

SQL中对大量数据进行比较、关联、排序、分组

实例内存满足不了缓存数据或排序等需要,导致产生大量 物理 IO。

查询执行效率低,扫描过多数据行。

不适宜的锁的设置,导致线程阻塞,性能下降。

死锁,线程之间交叉调用资源,导致死锁,程序卡住。

·· 服务器硬件的性能瓶颈

top,free, iostat和vmstat来查看系统的性能状态

2- Explain分析

·· 是什么(查看执行计划)

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

·· 能干嘛

表的读取顺序

哪些索引可以使用

数据读取操作的操作类型

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

·· 怎么玩

Explain + SQL语句

执行计划包含的信息:

·· 各字段解释

id:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序

三种情况

-1 id相同,执行顺序由上至下

-2 id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

-3 有id相同的也有id不同的

id如果相同,可以认为是一组,从上往下顺序执行;

在所有组中,id值越大,优先级越高,越先执行

衍生表 = derived2 --> derived + 2 (2 表示由 id =2 的查询衍生出来的表。type 肯定是 all ,因为衍生的表没有建立索引)

select_type

-1 有哪些

查询的类型,主要是用于区别

普通查询、联合查询、子查询等的复杂查询

SIMPLE:简单的 select 查询,查询中不包含子查询或者UNION

PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为Primary

在FROM列表中包含的子查询被标记为DERIVED(衍生)

MySQL会递归执行这些子查询, 把结果放在临时表里。

SUBQUERY:在SELECT或WHERE列表中包含了子查询

DEPENDENT SUBQUERY:在SELECT或WHERE列表中包含了子查询,子查询基于外层

UNCACHEABLE SUBQUREY:无法被缓存的子查询

图中的 @@ 表示查的环境参数 。没办法缓存

若第二个SELECT出现在UNION之后,则被标记为UNION;

若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED

UNION RESULT 两个语句执行完后的结果

UNION RESULT:从UNION表获取结果的SELECT

table:显示这一行的数据是关于哪张表的

type:type显示的是访问类型,是较为重要的一个指标

结果值从最好到最坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL

一般来说,得保证查询至少达到range级别,最好能达到ref。

system>const>eq_ref>ref>range>index>ALL

--1 system

表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计

--2 const

表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快

例如将主键置于where列表中,MySQL就能将该查询转换为一个常量

--3 eq_ref

唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描

--4 ref

非唯一性索引扫描,返回匹配某个单独值的所有行.

本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,

它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体

--5 range

只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引

一般就是在你的where语句中出现了between、<、>、in等的查询

这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。

--6 index

Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。

(也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)

--7 all

Full Table Scan,将遍历全表以找到匹配的行

--8 index_merge

在查询过程中需要多个索引组合使用,通常出现在有 or 的关键字的sql中

--9 ref_or_null

对于某个字段既需要关联条件,也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。

--10 index_subquery

利用索引来关联子查询,不再全表扫描。

--11 unique_subquery

该联接类型类似于index_subquery。 子查询中的唯一索引

总结:一般来说,得保证查询至少达到range级别,最好能达到ref。

possible_keys:

显示可能应用在这张表中的索引,一个或多个。

查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用

key:实际使用的索引。如果为NULL,则没有使用索引

查询中若使用了覆盖索引,则该索引和查询的select字段重叠

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。

key_len字段能够帮你检查是否充分的利用上了索引

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

rows列显示MySQL认为它执行查询时必须检查的行数。(越少越好)

包含不适合在其他列中显示但十分重要的额外信息

--1 Using filesort

说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。

MySQL中无法利用索引完成的排序操作称为“文件排序”

查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度

分情况:当通过前面的查询语句 筛选大部分条件后,只剩下很少的数据。using filesort 性能影响不大。需要综合考虑

--2 Using temporary

使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

--3 USING index

表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!

如果同时出现using where,表明索引被用来执行索引键值的查找;

如果没有同时出现using where,表明索引只是用来读取数据而非利用索引执行查找。

覆盖索引(Covering Index):

索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。①一个索引 ②包含了(或覆盖了)[select子句]与查询条件[Where子句]中 ③所有需要的字段就叫做覆盖索引。

select id , name from t_xxx where age=18;

有一个组合索引  idx_id_name_age_xxx 包含了(覆盖了),id,name,age三个字段。查询时直接将建立了索引的列读取出来了,而不需要去查找所在行的其他数据。所以很高效。

(个人认为:在数据量较大,固定字段查询情况多时可以使用这种方法。)

如果要使用覆盖索引,一定要注意select列表中只取出需要的列,不可select *,

因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降。

--4 Using where

表明使用了where过滤

--5 using join buffer

使用了连接缓存

出现在当两个连接时

驱动表(被连接的表,left join 左边的表。inner join 中数据少的表) 没有索引的情况下。

给驱动表建立索引可解决此问题。且 type 将改变成 ref

--6 impossible where

where子句的值总是false,不能用来获取任何元组

--7 select tables optimized away

在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者

对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,

查询执行计划生成的阶段即完成优化。

3- 如何使用索引

-1 全值匹配我最爱

-2 最佳左前缀法则

如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

AND 忽略左右关系。既即使没有没有按顺序 由于优化器的存在,会自动优化。

经过试验结论  建立了 idx_nameAge 索引  id 为主键

1.当使用覆盖索引的方式时,(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件)),即使不是以 name 开头,也会使用 idx_nameAge 索引。

既 select 后的字段 有索引,where 后的字段也有索引,则无关执行顺序。

2.除开上述条件才满足最左前缀法则。

-3 不要在索引列上做任何操作(计算、函数、(自动or手动)类型转换),这样会导致索引失效而转向全表扫描

-4 存储引擎不能使用索引中范围条件右边的列

-5 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *

-6 mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

-7 is not null 也无法使用索引,但是is null是可以使用索引的

-8 like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

-9 字符串不加单引号索引失效

-10 少用or,用它来连接时会索引失效

-12 一般性建议

对于单键索引,尽量选择针对当前query过滤性更好的索引

在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。(避免索引过滤性好的索引失效)

在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的