文档章节

Mysql性能法宝之一:索引

boonya
 boonya
发布于 2019/06/11 12:40
字数 453
阅读 62
收藏 0

3 月,跳不动了?>>>

很多企业在面试时都会问到MySQL性能优化这方面的。那么如何快速处理查询慢的问题?最简单的就是为表字段建立索引。

下面是一个很耗时的语句:

SELECT * FROM (SELECT COUNT(1) AS TOTAL FROM XH_XXDKQ_TB_13304319115 WHERE 1=1 AND SBSJ <= str_to_date('2019-06-11 23:59:59','%Y-%m-%d %H:%i:%s') AND HLY_ID IN (SELECT CAST(HLY_ID AS CHAR) AS HLY_ID FROM XH_HLY_TB_13304319115 WHERE 1=1 AND NSJGID IN (405,404,403,402,401,400,399,398,397,396,395,394,393,392,391,390,389,388,387,386,385,384,383,382,381,380,379,378,376,375,374,373,372,371,370,369,368,367,366,365,364,363,362,361,360,359,358,357,356,355,354,353,351,350,349,348,347,346,345,344,343,342,341,340,339,338,337,336,335,334,333,332,331,330,329,328,327,326,321,320,319,318,317,316,315,314,313,312,311,310,309,308,307,305,304,303,302,301,300,299,298,297,296,295,294,293,292,291,290,289,288,287,286,285,284,283,282,281,280,279,278,277,276,275,274,273,272,271,270,269,268,267,266,265,264,263,262,261,260,259,258,257,256,255,254,253,252,251,250,249,248,247,246,245,244,243,242,241,240,239,238,237,236,235,234,233,232,231,230,229,228,227,226,225,224,223,222,221,220,219,218,217,216,215,214,213,212,211,210,209,208,207,206,205,204,203,202,201,200,199,198,197,196,195,194,193,192,191,190,189,188,187,186,185,184,183,182,181,180,179,178,177,176,175,174,173,172,171,170,169,168,167,166,165,164,163,162,161,160,159,158,157,156,155,154,153,152,151,150,149,148,147,146,145,144,143,142,141,140,139,138,137,136,135,134,133,132,131,130,129,128,127,126,125,124,123,122,121,120,119,118,117,116,115,111,110,109,107,106,105,104,103,102,101,100,99,98,97,96,95,94,93,92,91,90,89,88,87,86,85,84,83,82,81,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,64,63,62,61,60,59,58,57,56,55,54,53,52,51,50,49,48,47,46,45,44,43,42,41,40,39,38,37,36,35,34,33,32,31,30,28,27,26,25,24,23,22,21,20,19,18,17,16,15,14,13,12,10,9,8,7,5,4,3,2,1)) ) AS T

不建任何索引查询的信息输出如下:

SELECT * FROM (SELECT COUNT(1) AS TOTAL FROM XH_XXDKQ_TB_13304319115 WHERE 1=1 AND SBSJ <= str_to_date('2019-06-11 23:59:59','%Y-%m-%d %H:%i:%s') AND HLY_ID IN (SELECT CAST(HLY_ID AS CHAR) AS HLY_ID FROM XH_HLY_TB_13304319115 WHERE 1=1 AND NSJGID IN (405,404,403,402,401,400,399,398,397,396,395,394,393,392,391,390,389,388,387,386,385,384,383,382,381,380,379,378,376,375,374,373,372,371,370,369,368,367,366,365,364,363,362,361,360,359,358,357,356,355,354,353,351,350,349,348,347,346,345,344,343,342,341,340,339,338,337,336,335,334,333,332,331,330,329,328,327,326,321,320,319,318,317,316,315,314,313,312,311,310,309,308,307,305,304,303,302,301,300,299,298,297,296,295,294,293,292,291,290,289,288,287,286,285,284,283,282,281,280,279,278,277,276,275,274,273,272,271,270,269,268,267,266,265,264,263,262,261,260,259,258,257,256,255,254,253,252,251,250,249,248,247,246,245,244,243,242,241,240,239,238,237,236,235,234,233,232,231,230,229,228,227,226,225,224,223,222,221,220,219,218,217,216,215,214,213,212,211,210,209,208,207,206,205,204,203,202,201,200,199,198,197,196,195,194,193,192,191,190,189,188,187,186,185,184,183,182,181,180,179,178,177,176,175,174,173,172,171,170,169,168,167,166,165,164,163,162,161,160,159,158,157,156,155,154,153,152,151,150,149,148,147,146,145,144,143,142,141,140,139,138,137,136,135,134,133,132,131,130,129,128,127,126,125,124,123,122,121,120,119,118,117,116,115,111,110,109,107,106,105,104,103,102,101,100,99,98,97,96,95,94,93,92,91,90,89,88,87,86,85,84,83,82,81,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,64,63,62,61,60,59,58,57,56,55,54,53,52,51,50,49,48,47,46,45,44,43,42,41,40,39,38,37,36,35,34,33,32,31,30,28,27,26,25,24,23,22,21,20,19,18,17,16,15,14,13,12,10,9,8,7,5,4,3,2,1)) ) AS T

受影响的行: 0
时间: 15.036s

建立索引:

再次执行查询:

[SQL]SELECT * FROM (SELECT COUNT(1) AS TOTAL FROM XH_XXDKQ_TB_13304319115 WHERE 1=1 AND SBSJ <= str_to_date('2019-06-11 23:59:59','%Y-%m-%d %H:%i:%s') AND HLY_ID IN (SELECT CAST(HLY_ID AS CHAR) AS HLY_ID FROM XH_HLY_TB_13304319115 WHERE 1=1 AND NSJGID IN (405,404,403,402,401,400,399,398,397,396,395,394,393,392,391,390,389,388,387,386,385,384,383,382,381,380,379,378,376,375,374,373,372,371,370,369,368,367,366,365,364,363,362,361,360,359,358,357,356,355,354,353,351,350,349,348,347,346,345,344,343,342,341,340,339,338,337,336,335,334,333,332,331,330,329,328,327,326,321,320,319,318,317,316,315,314,313,312,311,310,309,308,307,305,304,303,302,301,300,299,298,297,296,295,294,293,292,291,290,289,288,287,286,285,284,283,282,281,280,279,278,277,276,275,274,273,272,271,270,269,268,267,266,265,264,263,262,261,260,259,258,257,256,255,254,253,252,251,250,249,248,247,246,245,244,243,242,241,240,239,238,237,236,235,234,233,232,231,230,229,228,227,226,225,224,223,222,221,220,219,218,217,216,215,214,213,212,211,210,209,208,207,206,205,204,203,202,201,200,199,198,197,196,195,194,193,192,191,190,189,188,187,186,185,184,183,182,181,180,179,178,177,176,175,174,173,172,171,170,169,168,167,166,165,164,163,162,161,160,159,158,157,156,155,154,153,152,151,150,149,148,147,146,145,144,143,142,141,140,139,138,137,136,135,134,133,132,131,130,129,128,127,126,125,124,123,122,121,120,119,118,117,116,115,111,110,109,107,106,105,104,103,102,101,100,99,98,97,96,95,94,93,92,91,90,89,88,87,86,85,84,83,82,81,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,64,63,62,61,60,59,58,57,56,55,54,53,52,51,50,49,48,47,46,45,44,43,42,41,40,39,38,37,36,35,34,33,32,31,30,28,27,26,25,24,23,22,21,20,19,18,17,16,15,14,13,12,10,9,8,7,5,4,3,2,1)) ) AS T

受影响的行: 0
时间: 0.180s

很多时候我们都知道要做数据库级别的优化,但在开发过程中往往被忽略掉对这部分的优化,这点很值得注意。

索引查询范围标识:

1.const 
如果是根据主键或唯一索引 只取出确定的一行数据。是最快的一种。 
2.range 
索引或主键,在某个范围内时 
3.index 
仅仅只有索引被扫描 
4.all 
全表扫描,最令人心痛

© 著作权归作者所有

上一篇: Apache Hive
boonya
粉丝 79
博文 388
码字总数 77424
作品 0
海淀
高级程序员
私信 提问
加载中

评论(0)

【黑魔法】Covering Indexes、STRAIGHT_JOIN

今天给大家介绍两个黑魔法,这都是压箱底的法宝。大家在使用时,一定要弄清他们的适用场景及用法,用好了,就是一把开天斧,用不好那就是画蛇添足。自从看过耗子哥(左耳朵耗子)的博客,都会...

夏雪冬日
2018/08/13
0
0
一个不可思议的MySQL慢查分析与解决

转载自For DBA 一、前言 开发需要定期的删除表里一定时间以前的数据,SQL如下 前段时间在优化的时候,已经在相应的查询条件上加上了索引 但是实际执行的SQL依然非常慢,为什么呢,我们来一步...

江大白
2018/09/10
0
0
面试宝典系列-explain分析sql语句执行效率

explain用来分析sql查询语句的执行效率,desc命令同样的效果。 语法:explain 查询语句 举个栗子:explain select * from news; 输出: +----+-------------+-------+-------+--------------...

suyain
2018/08/14
163
0
索引初识一 MySql

1 mysql索引类型【主要分4类索引】 创建索引: 1.添加PRIMARY KEY(主键索引) 【主键:一种唯一性索引,必须指定为primary key 】 mysql> ALTER TABLE ADD PRIMARY KEY ( ) 2.添加UNIQUE(唯...

技术林工
2017/05/18
0
0
MySQL 8.0 在关系数据库方面有这些新特性

你可能已经知道 MySQL 从版本 5.7 开始提供了 NoSQL 存储的功能,在 8.0 中这部分功能也得到了一些改进,但鉴于这个在实际当中用得极少,本人也是没用过,所以本文不会介绍这方面的东西,而是...

技术小能手
2018/05/14
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Scala的运算符

在Scala中运算符的表示法不局限于字符, 中缀运算符可以是任何方法接受参数的方法 前缀运算符放在值的前面,只能是 + - ! ~ 四者之一, 方法名为unary_*(*为运算符) 后缀运算符是任何不接受参...

dreamness
28分钟前
17
0
GeJJSON

根据GeoJSON规范(RFC 7946):GeoJSON是一种对各种地理数据结构进行编码的格式。 GeoJSON对象可以表示几何、特征或者特征集合。GeoJSON支持下面几何类型:点、线、面、多点、多线、多面和几...

东东笔记
34分钟前
11
0
读写接口

针对一些输入/输出的场景,一般可能会涉及到读/写操作。比如,对一段字符串进行gzip压缩,或者解压缩,golang下主要通过两个接口来实现: 读的接口声明如下: type Reader interface {R...

道道法
昨天
19
0
mysql 运维命令

数据库备份导入 mysqldump -u root -p main_3_2_0 > /main_3_2_0.sqluse main_3_2_0source /main_3_2_0.sql #查询正在执行的sql show full processlist# 使用 kill id 来杀死进......

jxlgzwh
昨天
17
0
简易laravel路由权限开关(附黑名单防刷)

背景 : 今天开始这个版本的项目开发的时候,有一部分是优化系统的功能结构,其中就包括这个路由权限控制 (原本的意思是针对几个主要功能模块,做一个开关,以备线上可以做一些应急处理,后面...

冻结not
昨天
13
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部