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使用索引优化性能的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持。