如何加快查询,最直接有效的办法就是增加索引,在不使用索引的情况下试图采用其他方式加快查询就是在浪费时间。本文先介绍下MySQL索引的基本数据结构,再对索引的基本规则做下总结。
通过索引查找的整体思路是避免遍历查找,而是通过已经建立好的索引结构找到目标数据, 或确认目标数据不存在,从而完成查询。如果说在单表不用索引的情况下遍历查询还可以忍受,那么在多表联合查询的情况下不使用索引时匹配次数将会达到天文数字。
再重温下这个常见的例子:3个没有索引的表table1,table2,table3。每个数据表都包含一个数据列c1,c2,c3,且每个数据列都从数字1到数字1000的1000个数据行。要查找这些数据表中具有相同数值的所有数据行的组合,其查询语句应该是下面这样:
select table1.c1,table2.c2,table3.c3 from table1 inner join table2 inner join table3 where table1.c1 = table2.c2 and table2.c2 = table3.c3
在不是用索引的情况下遍历查询需要从(1000*1000*1000) = 10亿的临时记录中匹配出实际需要的1000条数据,显然造成了极大的浪费。
在使用索引情况下来分析下关联查询的过程:
整个查询过程中table1遍历查询,但table2,table3都是带索引搜寻,直接将那些数据挑选出来,查询速度比不用索引时快了100万倍。
(图一)
(图二)
MyISAM按主Key范围查找的时候仍然可能会导致随机读取,这对机械磁盘来说是个悲剧。
(图三)
(图四)
1.经常出现在where、group by, order by,join子句后的列考虑建立索引。
2.建立的索引列需要有较好区分度。如果数据列的独一无二的值越多,区分度越高,索引效果越好。例如:对班级表格的性别字段的索引只有两个值’F’,’M’,采用性别别的索引无论怎么查询,得到的是数据表的一半左右的记录。MySQL的查询优化逻辑甚至不会选择对于这样的索引,而改成遍历搜索。
3.数据列尽量的短小。例如能用整型的就不要用字符串类型。
原因:短小数值可以让比较操作更快完成,加快查找速度;短小数值可以让索引体积更小,减少磁盘I/O;短小数值可以让缓存里容纳更多的索引信息。InnoDB存储引擎短小的主Key有助于减少辅助索引的体积;
4.为字符串的前缀设置索引。例如某列char(100),但大多数前10或20已经有足够的区分度就没必要为整个字符串列建立索引。原因还是出于磁盘I/O,缓存空间,匹配效率方面考虑。
5.在InnoDB中要用单调字段作为主键。原因:InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。
1.数据类型相同的数据列进行比较;例如int/init比较,bigint/bigint比较效率就要好于int/bitint的比较
2.带索引的数据列在比较表达式中单独出现;例如
where mycol*2 < 4;数据列中每一个值都要被读取并计算结果在比较
where mycol < 4/2;优化程序先计算表达式4/2,然后在索引里进行检索
3.复合索引充分利用最左前缀。创建了N个列的符合索引,实际上创建了MySQL能够使用的n个索引。例如某表的复合索引 index(国家,省份,城市)。那么可用索引为
A.国家
B.国家,省份
C.国家,省份,城市
但必须满足最左缀原理。如果查询条件里只有国家,城市就无法充分利用改符合索引。
4.不在like的开始部分使用通配符。例如:
where col_name like ‘%string%’;如果是要查询出现在数据列的字符串,这子句是正确的,此时并没有使用col_name的索引,但不要出于习惯将%放到string两侧。如果改成
Where col_name like ‘string%’查询依string开头的字符串就可以使用col_name上的索引
5.自动类型转换可能会阻止索引的使用;例如:
select * from mytbl where num_col = 4;
select* from mytbl where num_col = ‘4’;
如果num_col是int且有索引,那么第二条语句有可能会阻止索引使用。
6.验证各种查询优化时需要变化查询参数多次运行。有时会发现当运行一种查询优化时查速度慢,当换另外一种查询方式时速度变快,此时会觉得第二种查询方式的效果更好。然后再用第一种查询方式查询发现速度通用变快。实际上这只不过是第一次查询时的信息仍然保存再缓存内,当后续查询时发现查询速度变快
参考:
1、MySQL技术内幕:InnoDB存储引擎 2、MySQL技术内幕:第4版 3、MySQL高效编程 4、MySQL的官方手册 5、http://km.oa.com/articles/show/190056?kmref=search&from_page=1&no=1
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。