Mysql优化-索引

发表于 | 分类于
Mysql优化-索引

查看MYSQL版本

1
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. 不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。

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

禁用缓存

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

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

查询表引擎：

1
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

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

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

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

那到底用哪一个呢，利用【sarg】方法：

1
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里面嵌套子查询时索引就失效了！

1
2
3
4
5
6
7
8
9
10
11
12
13
EXPLAIN 
SELECT SQL_NO_CACHE
* 
FROM t_question 
WHERE testid IN (
	SELECT
		questionid
	FROM
		t_question_knowledge_point
	WHERE
		`pointid` = 105
)
LIMIT 0,10;

索引的使用情况

Jietu20190814-122825@2x

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

怎么解决呢？

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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;

索引的使用情况

Jietu20190814-122914@2x

不要用下面的写法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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;

索引使用情况

Jietu20190814-122640

全文索引(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 引擎表全文索引包含的最大词长度 
1
2
show global variables like 'ft_%'; --查看 MyISAM 引擎表全文检索相关参数
show global variables like 'innodb_ft%'; --查看 InnoDB 引擎表全文检索相关参数

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

1
CREATE fulltext INDEX index_name ON table_name(colum_name);

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

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

我们会用到这样的语句

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

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

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

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

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

比如 A+B建立索引

1
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就可以了：

如：

1
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都是union
D：dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
E：union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
F：subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
G：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()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个。

查看索引数据大小

所有表

1
2
3
4
5
6
7
8
9
10
11
12
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;

某个表总大小

1
2
3
4
5
6
7
8
9
10
11
12
13
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;

某个表每个索引大小

1
2
3
4
5
6
7
8
9
10
11
12
13
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。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。

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

1
optimize table t_question;