Mysql优化-索引

查看MYSQL版本

select version();

InnoDB引擎的要求MYSQL版本5.6及以上支持全文索引 MyISAM各版本均支持全文索引

概念

Innodb和Myisam是两种类类型

下面介绍一下他们的区别:

区别

Innodb

Myisam

事务

安全

非安全

行级

表级

效率

索引

聚集索引

非聚集索引

外键

支持

不支持

使用环境

需要事务,大量增,改

多查询,不需要事务

下面说说他们的区别 聚集索引:

  1. 属于Innodb。
  2. 按照主键B+树的排列方式存放,子节点存放的就是数据。 如果没有主键,以第一列为聚集索引.
  3. 只有一个聚集索引。
  4. 普通索引指向聚集索引。

非聚集索引:

  1. 属于MyIsam。
  2. 普通索引和非聚集索引没什么区别。
  3. 存放的是地址。

聚集索引与非聚集索引

  1. 聚集索引,常见就是主键,一个表中只能拥有一个聚集索引。一个表中可以拥有多个非聚集索引。
  2. 使用聚集索引的查询效率要比非聚集索引的效率要高,但是如果需要频繁去改变聚集索引的值,写入性能并不高,因为需要移动对应数据的物理位置。
  3. 非聚集索引在查询的时候可以的话就避免二次查询,这样性能会大幅提升。
  4. 不是所有的表都适合建立索引,只有数据量大表才适合建立索引,且建立在选择性高的列上面性能会更好。

单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上; 用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引);

禁用缓存

因为我们要测试添加索引的效果,所以就先禁用缓存,防止影响测试结果。

show global variables like '%query_cache%';
set global query_cache_size=0;
set global query_cache_type=0;

查询表引擎:

show variables like '%storage_engine%';

表引擎使用innodb.第一次查询也会走数据文件,第二次直接走buffer_pool,也比直接查询数据文件要快

哪些字段可以加索引?

  1. 表的主键、外键必须有索引;
  2. 数据量超过300的表应该有索引;
  3. 经常与其他表进行连接的表,在连接字段上应该建立索引;
  4. 经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
  5. 索引应该建在较高选择性的字段上;
  6. 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
  7. 复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
    • 复合索引中的主列字段,要是使用较高选择性的字段;
    • 复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
    • 如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
    • 如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
    • 如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
  8. 频繁进行数据操作的表,不要建立太多的索引;
  9. 删除无用的索引,避免对执行计划造成负面影响;

较高选择性:就是通过该字段就可以筛选出满足条件的尽可能少的数据

以上是一些普遍的建立索引时的判断依据。 一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。 因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。 另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的,不但影响查询性能 还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大

索引的生效规则

对于一个不包含子查询的SQL来说,最终只可能有一个索引生效。

独立索引生效规则

  • 一条sql语句只能使用一个索引是错误的。
  • 生效的索引跟建立索引的顺序以及查询的顺序均无关,和字段的选择性有关,较高选择性的字段对应的索引优先生效。

复合索引的生效规则

如果第一个条件不能单独提供较高的选择性,复合索引将会非常有用。

较高选择性:就是通过该字段作为条件就可以筛选出满足条件的尽可能少的数据。

在复合索引中,索引第一位的column很重要,只要查询语句包含了复合索引的第一个条件,基本上就会使用到该复合索引(可能会使用其他索引)。我们在建符合索引的时候应该按照column的重要性从左往右建。

生效规则

  • 多列索引发挥作用,需要满足左前缀要求
  • 只要包含第一个条件,索引都生效,跟顺序无关

以index(a,b,c)为例

语句

索引是否发挥作用

where a=3

where a=3 and b=5

where a=3 and b=5 and c=4

where b=3

where c=4

where a=3 and c=4

a列能用到索引,c不能

where a=3 and b>10 and c=7

a能,b能,c不能

where a=3 and b like ‘xxx%’ and c=7

a能,b能,c不能

where b=5 and c=4 and a=3

where b=5 and c=4

不能

where b=2 and c=3 and d=5 and a=1

复合索引的选择

比如我们有这样一个SQL

select * from userresult where askid=800808 and uid=110996854;

我们有两种复合索引选择:

  1. idx_1 (askid,uid)
  2. idx_2 (uid,askid)

那到底用哪一个呢,利用【sarg】方法:

select sum(askid=800808),sum(uid=110996854) from userresult;

比如结果如下

  1. sum(askid=800808): 6
  2. sum(uid=110996854): 2

因为通过uid过滤后的数据更少,所以我们用idx_2

索引越多越好?

  • 大多数情况下索引能大幅度提高查询效率,但是过多的索引反而会影响速度。
  • 过小的表,建索引可能会更慢哦。
  • 已有某字段为主索引的多列索引,就没必要添加该字段的独立索引。

索引什么时候生效?

生效的情况

  • like ‘xxxx%’
  • <,<=,=,>,>=,BETWEEN

索引什么时候会失效?

  • 最佳左前缀法则
  • 在索引列上做任何操作(计算、函数、(手动或自动)类型转换),会导致索引失效而转向全表扫描
  • 存储引擎不能使用索引中范围条件右边的列
  • <>,not in ,!=
  • is null,is not null
  • like ‘%xxxx%’
  • 字符串不加单引号索引失效(自动类型转换)
  • or左边有索引、右边没索引也会失效

IN是否能用到索引?

IN能使用到索引,但是当表内存在多个单列索引时,MySQL不会自动选择in条件使用的索引,即使它是最优索引。

使用IN查询时,数据为相同类型的数据是可以正常使用索引的

但是,当IN里面嵌套子查询时索引就失效了!

EXPLAIN 
SELECT SQL_NO_CACHE
* 
FROM t_question 
WHERE testid IN (
	SELECT
		questionid
	FROM
		t_question_knowledge_point
	WHERE
		`pointid` = 105
)
LIMIT 0,10;

索引的使用情况

原因是当使用select之后使用了函数内部转换,mysql是不支持函数索引的。

怎么解决呢?

直接与子查询进行关联,这种写法相当于IN子查询写法,而且效率有不少的提高

EXPLAIN 
SELECT SQL_NO_CACHE
	a.*
FROM
	t_question a,(
	SELECT
		questionid
	FROM
		t_question_knowledge_point
	WHERE
		`pointid` = 105
	) t2
WHERE
	a.testid = t2.questionid
LIMIT 0,10;

索引的使用情况

不要用下面的写法

EXPLAIN 
SELECT SQL_NO_CACHE
	a.*
FROM
	t_question a LEFT JOIN(
	SELECT
		questionid
	FROM
		t_question_knowledge_point
	WHERE
		`pointid` = 105
	) t2
ON
	a.testid = t2.questionid
LIMIT 0,10;

索引使用情况

全文索引(LIKE优化)

优化的方式就是建立全文检索FULLTEXT

使用Mysql全文检索FULLTEXT的先决条件

  • MyISAM 引擎表和 InnoDB 引擎表(MySQL 5.6 及以上版本)都支持中文全文检索。
  • 建立全文检索的字段类型必须是char,varchar,text
  • InnoDB引擎的要求mysql版本5.6及以上 MyISAM没有版本约束
  • 表没有进行分区,进行分区的表是无法创建全文索引的

参数设置

#

参数名称

默认值

最小值

最大值

作用

1

ft_min_word_len

4

1

3600

MyISAM 引擎表全文索引包含的最小词长度

2

ft_query_expansion_limit

20

0

1000

MyISAM引擎表使用 with query expansion 进行全文搜索的最大匹配数

3

innodb_ft_min_token_size

3

0

16

InnoDB 引擎表全文索引包含的最小词长度

4

innodb_ft_max_token_size

84

10

84

InnoDB 引擎表全文索引包含的最大词长度

show global variables like 'ft_%'; --查看 MyISAM 引擎表全文检索相关参数
show global variables like 'innodb_ft%'; --查看 InnoDB 引擎表全文检索相关参数

建立全文检索 在建表中用FullText关键字标识字段,已存在的表用 ALTER TABLE (或 CREATE INDEX) 创建索引

CREATE fulltext INDEX index_name ON table_name(colum_name);

比如我们要搜索这样的题目

关于静摩擦力,下列说法正确的是

我们会用到这样的语句

select * from t_question where `docHtml` like '%关于静摩擦力%' limit 0,10;

优化方式 对docHtml2字段添加FULLTEXT 用以下语句查询

SELECT * FROM t_question WHERE MATCH (docHtml) AGAINST ('+关于静摩擦力*' IN BOOLEAN MODE) limit 0,10;

MATCH() 函数的所有参数必须是从来自于同一张表的列,同时必须是同一个FULLTEXT索引中的一部分,除非MATCH()IN BOOLEAN MODE的。

多个字段建立复合全文索引,MATCH() 函数的参数也必须是多个字段

比如 A+B建立索引

SELECT * FROM t_question WHERE MATCH (A,B) AGAINST ('+关于静摩擦力*' IN BOOLEAN MODE) limit 0,10;

FULLTEXT解析器用“ ”(空格)、“,”(逗号)“.”(点号)作为默认的单词分隔符,因此对于不使用这些分隔符的语言如汉语来说FULLTEXT解析器不能正确的识别单词,对于这种情况需做额外处理。

如果我们用AGAINST ('+关于静摩擦' IN BOOLEAN MODE)是搜索不到的, 因为原语句被拆分为了关于静摩擦力下列说法正确的是 要想查询到就要添加*号 如:AGAINST ('+关于静摩擦力*' IN BOOLEAN MODE)。 另外我们想要用AGAINST ('+静摩擦力*' IN BOOLEAN MODE)是搜索不到的,前面加*号也没用。

order by关键字优化

  • 尽量使用index方式排序,避免使用filesort方式。
  • order by满足两种情况会使用index排序:
    • ①、order by语句使用索引最左前列,
    • ②、使用where子句与order by子句条件列组合满足索引最左前列
  • 双路排序:MySQL4.1之前,两次扫描磁盘
  • 单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列进行输出,效率更高一点,但是它会使用更多的空间,因为它把每一行都保存在内存中了

优化策略: 增大sort_buffer_size参数的设置、增大max_length_for_sort_data参数的设置

group by关键字优化

实质是先排序后进行分组,遵照索引键的最佳左前缀,

当无法使用索引列时,增大sort_buffer_size+max_length_for_sort_data参数的设置

怎样查看索引是否生效?

explain显示了MySQL如何使用索引来处理select语句以及连接表。

可以帮助选择更好的索引和写出更优化的查询语句。

使用方法,在select语句前加上explain就可以了:

如:

explain select surname,first_name form a,b where a.id=b.id

EXPLAIN列的解释:

字段

含义

id

id列数字越大越先执行,如果说数字一样大,那么就从上往下依次执行,id列为null的就表是这是一个结果集,不需要使用它来进行查询。

select_type

A:simple:表示不需要union操作或者不包含子查询的简单select查询。有连接查询时,外层的查询为simple,且只有一个B:primary:一个需要union操作或者含有子查询的select,位于最外层的单位查询的select_type即为primary。且只有一个C:union:union连接的两个select查询,第一个查询是dervied派生表,除了第一个表外,第二个以后的表select_type都是unionD:dependent union:与union一样,出现在union 或union all语句中,但是这个查询要受到外部查询的影响E:union result:包含union的结果集,在union和union all语句中,因为它不需要参与查询,所以id字段为nullF:subquery:除了from字句中包含的子查询外,其他地方出现的子查询都可能是subqueryG:dependent subquery:与dependent union类似,表示这个subquery的查询要受到外部表查询的影响H:derived:from字句中出现的子查询,也叫做派生表,其他数据库中可能叫做内联视图或嵌套select

table

显示的查询表名,如果查询使用了别名,那么这里显示的是别名,如果不涉及对数据表的操作,那么这显示为null,如果显示为尖括号括起来的<derived N>就表示这个是临时表,后边的N就是执行计划中的id,表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>,与<derived N>类似,也是一个临时表,表示这个结果来自于union查询的id为M,N的结果集。

type

依次从好到差:system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL,除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一个索引

possible_keys

显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句。

key

查询真正使用到的索引,select_type为index_merge时,这里可能出现两个以上的索引,其他的select_type这里只会出现一个。

key_len

用于处理查询的索引长度,如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查询不一定都能使用到所有的列,具体使用到了多少个列的索引,这里就会计算进去,没有使用到的列,这里不会计算进去。留意下这个列的值,算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意,mysql的ICP特性使用到的索引不会计入其中。另外,key_len只计算where条件用到的索引长度,而排序和分组就算用到了索引,也不会计算到key_len中。

ref

如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func。

rows

这里是执行计划中估算的扫描行数,不是精确值。

Extra

关于MYSQL如何解析查询的额外信息。但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢。

filtered

使用explain extended时会出现这个列,5.7之后的版本默认就有这个字段,不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数。

type列返回的描述的意义

依次从好到差:system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL。 除了all之外,其他的type都可以使用到索引。 除了index_merge之外,其他的type只可以用到一个索引。

含义

system

表中只有一行数据或者是空表,且只能用于myisam和memory表。如果是Innodb引擎表,type列在这个情况通常都是all或者index

const

使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时,通常type是const。其他数据库也叫做唯一索引扫描

eq_ref

出现在要连接过个表的查询计划中,驱动表只返回一行数据,且这行数据是第二个表的主键或者唯一索引,且必须为not null,唯一索引和主键是多列时,只有所有的列都用作比较时才会出现eq_ref

ref

不像eq_ref那样要求连接顺序,也没有主键和唯一索引的要求,只要使用相等条件检索时就可能出现,常见与辅助索引的等值查找。或者多列主键、唯一索引中,使用第一个列之外的列作为等值查找也会出现,总之,返回数据不唯一的等值查找就可能出现。

fulltext

全文索引检索,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引

ref_or_null

与ref方法类似,只是增加了null值的比较。实际用的不多。

unique_subquery

用于where中的in形式子查询,子查询返回不重复值唯一值

index_subquery

用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重。

range

索引范围扫描,常见于使用>,<,is null,between ,in ,like等运算符的查询中。

index_merge

表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取所个索引,性能可能大部分时间都不如range

index

索引全表扫描,把索引从头到尾扫一遍,常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

all

这个就是全表扫描数据文件,然后再在server层进行过滤返回符合要求的记录。

Extra列返回的描述的意义

关键词

含义

Distinct

在select部分使用了distinct关键字。

Not exists

MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了。

Range checked for each Record

没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一。

Using filesort

排序时无法使用到索引时,就会出现这个。常见于order by和group by语句中。

Using index

查询时不需要回表查询,直接通过索引就可以获取查询的数据。

Using temporary

看到这个的时候,查询需要优化了。创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上。

Using where

表示存储引擎返回的记录并不是所有的都满足查询条件,需要在server层进行过滤。查询条件中分为限制条件和检查条件,5.6之前,存储引擎只能根据限制条件扫描数据并返回,然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性,可以把检查条件也下推到存储引擎层,不符合检查条件和限制条件的数据,直接不读取,这样就大大减少了存储引擎扫描的记录数量。

using sort_union,using_union,using intersect,using sort_intersection

using intersect:表示使用and的各个索引的条件时,该信息表示是从处理结果获取交集using union:表示使用or连接各个使用索引的条件时,该信息表示从处理结果获取并集using sort_union和using sort_intersection:与前面两个对应的类似,只是他们是出现在用and和or查询信息量大时,先查询主键,然后进行排序合并后,才能读取记录并返回。

using join buffer(block nested loop),using join buffer(batched key accss)

5.6.x之后的版本优化关联查询的BNL,BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。

firstmatch(tb_name)

5.6.x开始引入的优化子查询的新特性之一,常见于where字句含有in()类型的子查询。如果内表的数据量比较大,就可能出现这个。

loosescan(m..n)

5.6.x之后引入的优化子查询的新特性之一,在in()类型的子查询中,子查询返回的可能有重复记录时,就可能出现这个。

查看索引数据大小

所有表

SELECT  
    TABLE_NAME,  
    concat(truncate(data_length/1024/1024,2),' MB') '库大小',  
    concat(truncate(index_length/1024/1024,2),' MB')  '索引大小'  
FROM  
    information_schema. TABLES  
WHERE  
    TABLE_SCHEMA = 'xhkjedu_ques_new'  
GROUP BY  
    TABLE_NAME  
ORDER BY  
    data_length DESC;

某个表总大小

SELECT  
    TABLE_SCHEMA '库名',  
    concat(TRUNCATE (sum(data_length) / 1024 / 1024,2),' MB')  '库大小',  
    concat(TRUNCATE (sum(index_length) / 1024 / 1024,2),'MB')  '索引大小'  
FROM  
    information_schema. TABLES  
WHERE  
    table_schema = 'xhkjedu_ques_new'  
AND table_name = 't_question';   
GROUP BY  
    TABLE_SCHEMA  
ORDER BY  
    data_length DESC;

某个表每个索引大小

SELECT
       table_name 表名,
       index_name 索引名,
			 sum(stat_value) 数据页页数,
       concat(round(sum(stat_value)* @@innodb_page_size/10000000,2),'M')  大小
FROM
       mysql.innodb_index_stats
WHERE
			 database_name = 'xhkjedu_question'
       AND table_name = 't_question'
       AND stat_description LIKE 'Number of pages in the index'
GROUP BY
       table_name, index_name;

删除无用索引后索引大小不变

当您的库中删除了大量的数据或者索引后,您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。不但占用空间,还会影响查询速度。

在多数的设置中,您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新,您也不需要经常运行,每周一次或每月一次即可,只对特定的表运行。

注意该操作会锁定表,数据量大时所需时间较长。

optimize table t_question;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏程序源代码

微信商城信息管理系统(java后台+小程序)

前一段时间在公众号中发布了一套微信商城信息管理系统(java后台+小程序),也许大家可能都还记得。

85150
来自专栏程序猿讲故事

CentOS上搭建MariaDB复制

一直没有自己配置过mysql复制,这两天空闲一些,在电脑上装了免费的VMWare Workstation Player,然后下载CentOS的最新版,开始配置一...

8520
来自专栏杨建荣的学习笔记

计算MySQL表碎片的SQL整理

当然整理的过程不光是知识梳理的过程,也是转化为实践场景的一个过程,通过这样一个体系,对于整个MySQL对象生命周期管理有了较为深入的认识,这里我来抛砖引玉,来作...

12010
来自专栏程序猿讲故事

制作mysql大数据表并验证覆盖索引的查询效率

昨天跟同事聊起数据表性能的问题,能不能仅用覆盖索引实现数据的汇总统计。找了一个开发环境已有的数据表进行测试,通过explain命令,能看到mysql通过覆盖索引...

11420
来自专栏程序猿讲故事

灵活使用Maven Profile

项目中一直应用Maven的profile特性解决不同环境的部署问题。最近在尝试解决本地调试环境的时候碰到一些问题,顺便仔细研究了一下。因为项目仍然在用普通Spr...

11130
来自专栏23号杂货铺

Springboot之多数据源的配置使用

现在的企业服务逐渐地呈现出数据的指数级增长趋势,无论从数据库的选型还是搭建,大多数的团队都开始考虑多样化的数据库来支撑存储服务。例如分布式数据库、Nosql数据...

32330
来自专栏程序猿讲故事

Mysql使用ReplicationDriver驱动实现读写分离

数据库的主从复制环境已经配好,该要解决系统如何实现读写分离功能了。Mysql的jdbc驱动提供了一种实现ReplicationDriver。

10430
来自专栏快速搭建 Discuz! 论坛

如何快速搭建 Discuz! 论坛

Discuz!全称:Crossday Discuz! Board,是一套免费使用的社区论坛软件系统,由北京康盛新创科技有限责任公司推出,目前最新版本是Discu...

37940
来自专栏直播、短视频那些事儿

在直播app制作过程中,服务器是如何配置的?

不论是一对多直播还是一对一直播app制作,关于服务器的配置和成本是大多数运营商比较关心和头疼的问题。一般来说,在直播app运营的每个阶段,所安排的服务器台数和负...

16630
来自专栏程序源代码

你认识的“索引”那些事儿

其实提到索引这个名词,有些抽象我们不太好理解这个名词。你转换下思路和学习方法。你会发现其实生活中的索引无处不在。

9520

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励