关闭

索引实战

标签: 数据库索引优化性能
228人阅读 评论(0) 收藏 举报
分类:

索引

无论是面试,还是实际工作中,对于一个Java程序员来说,数据库优化是避不开的一个技术点,关于数据库的优化,在性能达不到要求的情况下,我大致给出以下几个方向:

(1)优化表结构,对常用字段和非常用的字段分开存储

(2)优化SQL,合理使用索引

(3)做数据库读写分离,减少IO压力,由于数据库对记录做了持久化并存储在磁盘上,对磁盘的I/O又是非常消耗性能的操作,因此读、写都在一个库中会大大增加I/O的压力

(4)尝试使用缓存,不要让数据都走数据库

(5)对业务做垂直拆分

(6)对表做水平拆分,这一步比较麻烦,要注意主键生成规则以及请求路由规则

以上6个点是有优先级的,本文关注的是第二点的索引部分。正确合理地使用索引对于数据库性能提升是至关重要的,本文暂时不分析索引原理,只是从实战的角度,总结一下索引的使用技巧,理论结合实践,印象会更深一些。

当然,事前我已经建立了一张很简单的student表并向表中插入了10万条数据,SQL为:

复制代码
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `s_id` int(11) NOT NULL AUTO_INCREMENT,
  `s_name` varchar(100) DEFAULT NULL,
  `s_age` int(11) DEFAULT NULL,
  `s_phone` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`s_id`),
  KEY `s_name` (`s_name`)
) ENGINE=InnoDB, CHARSET=utf8;
复制代码

 

使用普通索引与不使用普通索引的区别

先看一下不使用普通索引,进行查询,执行SQL语句:

select * from student where s_name = "99999ssss";

看一下查询时间:

花费了0.179秒,使用explain查看一下该条SQL语句的执行情况:

分析几个关键信息:

  • select_type:SIMPLE,这个不是很关键,只是表示这是一次简单的查询,没有join,没有union,没有中间表
  • type:ALL,表示该次SQL进行了全表查询
  • key:MySQL使用的索引名,这里null表示此次SQL查询MySQL并没有使用索引
  • rows:这个是最关键的,表示这次SQL查询了100665条记录

OK,接下来给s_name这一列加上普通索引:

alter table student add index s_name(s_name);

看一下运行结果:

看到在s_name上加上索引之后,查询速度马上快了3倍以上。

从分析结果上来看,由于此次SQL对列s_name使用了索引,因此rows只查了1条记录,大大提升了查询效率。

 

把索引建立在有大量重复数据的字段上

把索引建立在有大量重复数据的字段上,并不能有效地提升SQL效率,比如我的s_phone的取值为"00000000"~"99999999",此时对s_phone做查询,未加索引的时候:

看到这条select语句的查询时间是0.05秒,而给s_phone字段加了索引之后:

反而变为了0.064秒,并没有显著地提升查询效率,反而更加缓慢。通过explain语句,发现此次SQL通过索引查询了18000条rows,再去定位这18000多条数据,自然会慢一点。

这说明了,即使查询的时候用到了索引,也未必能提升查询的效率,索引建立在重复数据量很少的字段上效果才明显,但是这也将导致索引的增大,不过大多数时候这并不是太大的问题。

 

索引与like

不建议对索引列使用like语句,比如说执行以下两句SQL:

select * from student where s_name like "%99999ssss%";
select * from student where s_name like "%99999ssss";

看一下explain出来的结果,都是一样的:

发现没有用到索引,这是对索引列使用like的限制,要对索引列使用like,通配符只能在结尾,开头不可以有任何的通配符,比如:

select * from student where s_name like "99999ssss%";

此时再explain看一下:

看到这么实用like则使用到了索引,这不得不说是一个限制。

 

索引与函数

在索引列上使用MySQL函数也会导致索引失效,看一个例子:

select * from student where "99999ssss" = left(s_name, 9);

这条SQL语句非常好理解,查询s_name列中从左边开始截取9个字符后的字符串为"99999ssss"的记录,查看一下explain的结果:

结果很明显,没有用到索引,这表明对索引列使用函数将导致索引失效。

一个技巧是,依然使用=,但是索引列不使用函数而对常数项使用函数,这样索引就有效了,当然这条语句是无法这么优化的。

0
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

【Lucene】Apache Lucene全文检索引擎架构之入门实战

Lucene是一套用于全文检索和搜寻的开源程式库,由Apache软件基金会支持和提供。Lucene提供了一个简单却强大的应用程式接口,能够做全文索引和搜寻。在Java开发环境里Lucene是一个成熟的...
  • eson_15
  • eson_15
  • 2016-07-07 19:23
  • 8088

【课程分享】基于Lucene4.6+Solr4.6+Heritrix1.14+S2SH实战开发从无到有垂直搜索引擎

对这个课程有兴趣的朋友,可以加我的QQ2059055336和我联系,可以和您分享。  课程介绍:最有前途的软件开发技术——搜索引擎技术  搜索引擎作为互联网发展中至关重要的一种应用,已经成为互联网...
  • u013019627
  • u013019627
  • 2014-05-09 10:39
  • 1452

基于Lucene4.6+Solr4.6+Heritrix1.14+S2SH实战开发垂直搜索引擎视频教程

《基于Lucene4.6+Solr4.6+Heritrix1.14+S2SH实战开发从无到有垂直搜索引擎》 课程讲师:小叶子  课程分类:Java 适合人群:初级 课时数量:69课时 用到技...
  • xkqueen2
  • xkqueen2
  • 2014-08-13 14:30
  • 1095

R语言数据分析与挖掘实战_PDF电子书下载_带索引书签目录_张良均,云伟标,王路著

  • 2017-11-04 12:06
  • 79.98MB
  • 下载

树莓派(Raspberry Pi)实战指南 手把手教你掌握100个精彩案例_PDF电子书下载 带索引书签目录_柯博文编著

  • 2017-11-13 15:08
  • 92.60MB
  • 下载

elasticsearch技术解析与实战_PDF电子书下载+高清+带索引书签目录_样章_朱林(著)_机械工业出版社

  • 2017-12-27 09:37
  • 12.98MB
  • 下载

郑保卫 - 索引优化策略及实战

  • 2015-11-26 16:30
  • 20.34MB
  • 下载

ElasticSearch实战 (三)索引的Mapping映射那些事

引言思考         在这篇博客中,我们谈一下es的映射,es中为什么要存在映射?以及它起到的作用,需不需要重视它的应用,与其它类似产品有没有这种概念?这些都是我们在学习一个新概念时应有的思...
  • lilongsheng1125
  • lilongsheng1125
  • 2016-12-24 22:57
  • 1774

MySQL实战系列3:视图、存储过程、函数、索引全解析

一、视图 视图是查询命令结果构成的一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需使用【名称】即可获取结果集合,并可以当作表来查询使用。 ...
  • Bennett2251
  • Bennett2251
  • 2016-11-21 13:52
  • 197

Mysql性能优化实战:数据库锁的介绍与索引查找原理

行级锁&表级锁 什么是行级锁?什么是表级锁?学过程序的同学一定都对锁的概念有一定了解,顾名思义一个是锁住一行,一个是锁住一整张表,具体来看下面的例子: 典型案例 现有表T_A结构和数据如下 开启一...
  • lxf2323881
  • lxf2323881
  • 2017-12-27 14:39
  • 107
    个人资料
    • 访问:41637次
    • 积分:777
    • 等级:
    • 排名:千里之外
    • 原创:19篇
    • 转载:112篇
    • 译文:0篇
    • 评论:3条
    文章分类
    最新评论