1.索引问题

索引是数据库优化中最常用也是最重要的手段之一,通过索引通常可以帮助用户解决大多数 的sql性能问题。本章节将对mysql中的索引的分类、存储、使用方法做详细的介绍。

2.索引的存储分类

myisam存储引擎的表数据和索引是自动分开存储的,各自是独立的一个文件;innodb存储引擎的表数据和索引是存储在同一个表空间里面,但可以有多个文件组成。mysql中索引的存储类型目前只有两种(btree和hash),具体和表的存储引擎相关:myisam和innodb存储引擎都只支持btree索引;memory/heap存储引擎可以支持hash和btree索引。mysql目前不支持函数索引,但是能对列的前面某一部分进索引,例如上章节库存表goods_stock.lotno批次字段,可以只取model的前4个字符进行索引,这个特性可以大大缩小索引文件的大小,我们在设计表结构的时候也可以对文本列根据此特性进行灵活设计。下面是创建前缀索引的一个例子:

explain select * from goods_stock where lotno like '2021%';

-- 创建前缀索引
create index idx_stock_2 on goods_stock (lotno(4));

3.如何使用索引

索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高select操作性能的最佳途径。查询要使用索引最主要的条件是查询条件中需要使用索引关键字,如果是多列索引,那么只有查询条件使用了多列关键字最左边的前缀时,才可以使用索引,否则将不能使用索引。

3.1使用索引

在mysql中,下列几种情况下有可能使用到索引。

对于创建的多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用, 举例说明如下:

-- 首先在库存表goods_stock按model,brand的顺序创建一个复合索引
create index idx_stock_1 on goods_stock (model,brand);

然后按model进行表查询,具体命令如下:

explain select * from goods_stock where model='lm358dt';

可以发现即便where条件中不是用model与brand字段的组合条件,索引仍然能用到,这就是索引的前缀特性(按照索引列顺序查询)。但是如果只按brand条件查询表,那么索引就不会被用到,具体如下:

explain select * from goods_stock where brand='ti';

对于使用like的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用,来看下面两个执行计划:

explain select * from goods_stock where model like '%lm358';

explain select * from goods_stock where model like 'lm358%';

可以发现第一个sql没有使用索引,而第二个sql就能够使用索引,区别就在于“%”的位置不同,前者把“%”放到第一位就不能用到索引,而后者没有放到第一位就使用了索引。另外,如果如果like后面跟的是一个列的名字,那么索引也不会被使用。如果对大的文本进行搜索,使用全文索引而不要使用like ‘%…%’。

如果列名是索引,使用column_name is null时候将会使用索引。如下例中查询lotno为null的记录时候就会用到索引:

explain select * from goods_stock where lotno is null;

3.2存在索引但不使用索引

在下列情况下,虽然存在索引,但是mysql并不会使用相应的索引。

如果mysql估计使用索引比全表扫描更慢,则不使用索引。例如如果列 key_part1 均匀分布在 1 和 100 之间,下列查询中使用索引就不是很好:

select * from table_name where key_part1 > 1 and key_part1 < 90;

如果使用memory/heap表并且where条件中不使用“=”进行索引列,那么不会用到索引。heap表只有在“=”的条件下才会使用索引。
用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到,例如:

show index from goods_stock;

通过命令可以看到goods_stock库存表有两个索引,然后我们再执行如下语句看是否使用索引:

explain select * from goods_stock where lotno='20200821' or packageunit='包';

可见虽然在lotno这个列上存在索引idx_stock_2,但是这个sql语句并没有用到这个索引,原因就是or中有一个条件中的列没有索引。

如果列类型是字符串,那么一定记得在where条件中把字符常量值用引号引起来,否则即便这个列上有索引,mysql也不会用到的,因为mysql默认把输入的常量值进行转换以后才进行检索,请看如下例子:

desc goods_stock;

通过desc命令我们可以看到goods_stock库存表中的lotno字段是字符型,如果我们在sql语句中的lotno字段加入一个数值型为20200821的条件值,因此即便在lotno上有索引,mysql也不能正确地用上索引,而是继续进行全表扫描,具体如下:

explain select * from goods_stock where lotno=20200821;

4.查看索引使用情况

如果索引正在工作,handler_read_key的值将很高,这个值代表了一个行被索引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用。handler_read_rnd_next的值高则意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描,handler_read_rnd_next的值较高,则通常说明表索引不正确或写入的查询没有利用索引。可以先刷新状态再查询,具体如下:

flush status;
show status like 'handler_read%';

参数解释如下:

  • handler_read_first:此选项表明sql是在做一个全索引扫描,注意是全部,而不是部分,所以说如果存在where语句,这个选项是不会变的。
  • handler_read_key:此选项数值如果很高,mysql高效的使用了索引,一切运转良好。
  • handler_read_next:此选项表明在进行索引扫描时,按照索引从数据文件里取数据的次数。
  • handler_read_prev:此选项表明在进行索引扫描时,按照索引倒序从数据文件里取数据的次数,一般就是order by … desc。
  • handler_read_rnd:就是查询直接操作了数据文件,很多时候表现为没有使用索引或者文件排序。
  • handler_read_rnd_next:此选项值较高时候,则通常说明表索引不正确或写入的查询没有利用索引。

5.两个简单实用的优化方法

对于大多数开发人员来说,可能只希望掌握一些简单实用的优化方法,对于更多更复杂的优化,更倾向于交给专业dba来做。本小节将向大家介绍两个简单适用的优化方法。

5.1定期分析表和检查表

分析表的语法如下:

analyze [local | no_write_to_binlog] table tbl_name [, tbl_name] ...

本语句用于分析和存储表的关键字分布,分析的结果将可以使得系统得到准确的统计信息,使得sql能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划,执行一次分析表可能会解决问题。在分析期间,使用一个读取锁定对表进行锁定。这对于myisam, bdb和innodb表有作用。对于myisam表,本语句与使用myisamchk -a相当,下例中对goods_stock表做了表分析:

analyze table goods_stock;

检查表的语法如下:

check table tbl_name [, tbl_name] ... [option] ... option = {quick | fast | medium | extended | changed}

检查表的作用是检查一个或多个表是否有错误。check table对myisam和innodb表有作用。对于myisam表,关键字统计数据被更新,例如:

check table goods_stock;

check table也可以检查视图是否有错误,比如在视图定义中被引用的表已不存在,举例如下:

(1)首先我们创建一个表。

create table test
(
  id int(11)
);

(2)再创建一个视图。

create view test_view as select * from test;

(3)然后check一下该视图,发现没有问题。

check table test_view;

(4)现在删除掉视图依赖的表。

drop table test;

(5)再来check一下刚才的视图,发现报错了。

check table test_view;

5.2定期优化表

优化表的语法如下:

optimize [local | no_write_to_binlog] table tbl_name [, tbl_name] ...

如果已经删除了表的一大部分,或者如果已经对含有可变长度行的表(含有varchar、blob或text列的表)进行了很多更改,则应使用optimize table 命令来进行表优化。这个命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费,但optimize table命令只对myisam、bdb和innodb表起作用。以下例子显示了优化goods_stock库存表的过程:

-- 先查看下goods_stock库存表是什么表类型
show table status like 'goods_stock%';

optimize table goods_stock;

注意:analyze、check、optimize执行期间将对表进行锁定,因此一定注意要在数据库不繁忙的情况下执行相关的操作。

到此这篇关于mysql使用索引优化性能的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持。