索引对性能到底有多少的影响??

索引到底对性能有多少影响?

这个问题估计是很多MySQL小白好奇的问题。当然我也是一样。因为之前的时候,并没有对索引有太多的注意,而且之前的工作经历,因为数据量很小,索引所起到的作用并不是很大,所以也没有太大注意。

事情的起点

我在公司是做后端开发(PHPer),除了日常的开发工作,也要兼职公司的运维。每周安排一个人跟进报警邮件,出现问题及时通报。

像很多创业公司的一样,我们选用的是阿里云的ECS+RDS。因为如果自己购买服务器,不管是运维成本还是物理成本都是比较高的。

一天将近半夜12点的时候,报警日志突然出现了MySQL server has gone away

遇到问题肯定是先Baidu,我找到了MySQL官方的解释,原因是查询的时候,出现的mysql断开的情况。我登录阿里云rds后台,发现wait_timeout时间长得很。不应该会出现超时的情况。

一个同事:“会不会和rds经常CPU报警有关?”

我勒个去,我查了一下rds监控,果然CPU持续升高。

问题跟进

rds自带了日志系统,可以方便。查看了一下慢日志系统,果然有很多的慢SQL日志。

我曹,每次扫描了8W多行。看来是没有使用到索引。加上次数频繁,解析的总次数高达 1762833762 行。

定位问题

查看MySQL的执行计划

mysql> explain extended SELECT * FROM `test` WHERE `is_deleted` =0 AND `a` = 81644;
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | test | ALL  | a_index      | NULL | NULL    | NULL | 86172 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
1 row in set, 3 warnings (0.01 sec)
mysql> show warnings;
| Level   | Code | Message                                                                                             
| Warning | 1739 | Cannot use ref access on index 'a_index' due to type or collation conversion on field 'a'                                                                                                 |
| Warning | 1739 | Cannot use range access on index 'a_index' due to type or collation conversion on field 'a'

果然是没有用到索引,全表扫描。

原来,由于a数据类型是varchar类型的。但是查询的时候,使用的int类型,在执行SQL语句的时候,由于类型原因,造成了隐式转换。没有用到索引。所以实际上,应该把原来的SQL语句更改成 SELECT * FROM test WHERE is_deleted =0 AND a = '81644'

虽然原因找到了,但是查询的SQL那么多,定位那具体的php文件以及对于的代码行数,也是一个难题。

PHP慢日志

为了能够定位代码的效率,PHP自带一个功能,那就是慢日志。如果PHP脚本,执行时间比较长的时候,那么PHP会认为这段代码是有问题的,PHP会把代码的基本信息打印到慢日志里面,能够方便开发者定位问题。

这么说来,如果找到慢日志里面关于执行这个SQL的代码,也就能够准确定位到对应的PHP文件。

索引对性能的影响!

接下来用对比图来比较下使用索引和没有使用索引的对比吧

优化之后的SQL执行效率,相比之前要高出很多,CPU占用率稳定保持在个位数,甚至 5%一下,相比之前80%左右,呈现指数的翻倍。

总结

其实隐式转换是MySQL索引经常遇到的问题。我最开始听说是前段时间,阿里云组织了一个慢SQL的优化大赛。虽然没有得到名次,但是确实通过大赛,学到了很多关于索引的知识。

awk命令的简单介绍

背景

awk算是Linux上面比较实用频繁的命令之一。第一次见到这个命令,是同事们分析一些日志实用,通过这个命令与其他命令结合,可以有效的分析nginx日志的一些访问情况。所以我也特意找了一些资料,查询了一下。

语法规则

awk的命令的语法规则是 awk '条件类型1{动作1} 条件类型2{动作2} ...' 文件名; 。awk条件类型后面的{}是满足条件后处理的一些动作。这些动作可以形成一套连续的操作。awk的处理单元是每一行。也就是每行处理之后,再对下一行进行处理。所以,awk并不适合对大量数据处理。

awk的处理原理

feilongdeMBP:~ feilong$ awk '{print $0}' /etc/passwd
##
# User Database
#
# Note that this file is consulted directly only when the system is running
# in single-user mode.  At other times this information is provided by
# Open Directory.
#
# See the opendirectoryd(8) man page for additional information about
# Open Directory.
##
nobody:*:-2:-2:Unprivileged User:/var/empty:/usr/bin/false
root:*:0:0:System Administrator:/var/root:/bin/sh
daemon:*:1:1:System Services:/var/root:/usr/bin/false
_uucp:*:4:4:Unix to Unix Copy Protocol:/var/spool/uucp:/usr/sbin/uucico
_taskgated:*:13:13:Task Gate Daemon:/var/empty:/usr/bin/false
_networkd:*:24:24:Network Services:/var/networkd:/usr/bin/false
_installassistant:*:25:25:Install Assistant:/var/empty:/usr/bin/false
_lp:*:26:26:Printing Services:/var/spool/cups:/usr/bin/false
.....

我们发现,这样输出的内容和执行cat /etc/passwd内容是一样的。

feilongdeMBP:~ feilong$ awk '{print $1}' /etc/passwd
##
#
#
#
#
#
#
#
#
##
nobody:*:-2:-2:Unprivileged
root:*:0:0:System
daemon:*:1:1:System
_uucp:*:4:4:Unix
_taskgated:*:13:13:Task
_networkd:*:24:24:Network
_installassistant:*:25:25:Install
_lp:*:26:26:Printing
_postfix:*:27:27:Postfix
_scsd:*:31:31:Service
_ces:*:32:32:Certificate
_mcxalr:*:54:54:MCX
_appleevents:*:55:55:AppleEvents
_geod:*:56:56:Geo
_serialnumberd:*:58:58:Serial
_devdocs:*:59:59:Developer
_sandbox:*:60:60:Seatbelt:/var/empty:/usr/bin/false
_mdnsresponder:*:65:65:mDNSResponder:/var/empty:/usr/bin/false
.....

随着print后面的变化,输出的内容也发生了变化

所以,awk的原理是这样的

除了这个,awk还有一些标量的含义

标量 含义
NR 当前的行号
NF 每一行拥有的字段总数
FS 每行的字段分隔符(默认空格)
RS 每行的结束符(默认\n)

实际操作

以分号进行分割
feilongdeMBP:~ feilong$ awk 'FS=":" {print $1}' /etc/passwd ## 或 awk -F ":" '{print $1}' /etc/passwd
##
# User Database
#
# Note that this file is consulted directly only when the system is running
# in single-user mode.  At other times this information is provided by
# Open Directory.
#
# See the opendirectoryd(8) man page for additional information about
# Open Directory.
##
nobody
root
daemon
_uucp
_taskgated
_networkd
_installassistant
_lp
_postfix
_scsd
_ces
_mcxalr
....
比如,只看 第20行到30行的内容
feilongdeMBP:~ feilong$ awk '{if(NR>=20 && NR<=30) {print "行号是: " NR " " $0}}' /etc/passwd
行号是: 20 _scsd:*:31:31:Service Configuration Service:/var/empty:/usr/bin/false
行号是: 21 _ces:*:32:32:Certificate Enrollment Service:/var/empty:/usr/bin/false
行号是: 22 _mcxalr:*:54:54:MCX AppLaunch:/var/empty:/usr/bin/false
行号是: 23 _appleevents:*:55:55:AppleEvents Daemon:/var/empty:/usr/bin/false
行号是: 24 _geod:*:56:56:Geo Services Daemon:/var/db/geod:/usr/bin/false
行号是: 25 _serialnumberd:*:58:58:Serial Number Daemon:/var/empty:/usr/bin/false
行号是: 26 _devdocs:*:59:59:Developer Documentation:/var/empty:/usr/bin/false
行号是: 27 _sandbox:*:60:60:Seatbelt:/var/empty:/usr/bin/false
行号是: 28 _mdnsresponder:*:65:65:mDNSResponder:/var/empty:/usr/bin/false
行号是: 29 _ard:*:67:67:Apple Remote Desktop:/var/empty:/usr/bin/false
行号是: 30 _www:*:70:70:World Wide Web Server:/Library/WebServer:/usr/bin/false
已知 test.txt 内容是 “I am Poe,my qq is 33794712″。过滤相应字符串,是输出结果为 “Poe 33794712”
feilongdeMBP:~ feilong$ awk -F "[ ,]+" '{print $3 " " $7}' test.txt
Poe 33794712

BEGIN和END模块

begin和end主要是只在awk执行开始(还没对第一行进行操作)和结束(对最后一行处理结束)后的行为。所以,begin和end只会操作一次。所以begin和end更像是 编程语言中的默认构造函数和析构函数。

统计用户的数量
feilongdeMBP:Downloads feilong$ awk 'BEGIN{count = 0} {if (NR > 10) { count ++} } { if (NR > 10 ) { print $1}} END{print "总的用户数量是: " count}' /etc/passwd ## 以为我的机器上面前10行不是用户的数据
nobody:*:-2:-2:Unprivileged
root:*:0:0:System
daemon:*:1:1:System
_uucp:*:4:4:Unix
_taskgated:*:13:13:Task
_networkd:*:24:24:Network
_installassistant:*:25:25:Install
_lp:*:26:26:Printing
_postfix:*:27:27:Postfix
_scsd:*:31:31:Service
_ces:*:32:32:Certificate
_mcxalr:*:54:54:MCX
_appleevents:*:55:55:AppleEvents
_geod:*:56:56:Geo
_serialnumberd:*:58:58:Serial
_devdocs:*:59:59:Developer
_sandbox:*:60:60:Seatbelt:/var/empty:/usr/bin/false
_mdnsresponder:*:65:65:mDNSResponder:/var/empty:/usr/bin/false
_ard:*:67:67:Apple
_www:*:70:70:World
_eppc:*:71:71:Apple
总的用户数量是: 93
总计金额
feilongdeMBP:~ feilong$ cat test.txt
Name    1st 2st 3st
Tyler   100 200 500
Start   59  30  444
Jack    345 222 67

feilongdeMBP:~ feilong$  awk 'BEGIN{ totle = 0;} NR==1{print "Name\t1st\t2st\t3st\tTotle"} NR>=2{totle = $2 + $3 + $4; print $1 "\t" $2 "\t" $3 "\t" $4 "\t"  totle}' test.txt
Name    1st 2st 3st Totle
Tyler   100 200 500 800
Start   59  30  444 533
Jack    345 222 67  634
统计字节数量
feilongdeMBP:~ feilong$ ll | grep JPG
-rw-r--r--@   1 feilong  access_bpf    255800  3  1 16:49 IMG_0898.JPG
-rw-r--r--@   1 feilong  access_bpf    258234  3  1 16:49 IMG_0899.JPG
-rw-r--r--@   1 feilong  access_bpf    338363  3  4 10:32 IMG_0930.JPG

feilongdeMBP:~ feilong$ ll | grep JPG | awk 'BEGIN{size = 0;} {size += $5} END{print "The .JPG file size:" size/1024/1024 "MB"}'
The .JPG file size:0.812909MB

awk还有丰富的运算符

awk支持大多数的运算符,这些运算符和编程语言基本类似

正则表达式

语法结构 awk '/正则表达式/{动作}' 文件

找出匹配包含root的行
feilongdeMBP:~ feilong$ awk '/root/{print $0}' /etc/passwd
root:*:0:0:System Administrator:/var/root:/bin/sh
daemon:*:1:1:System Services:/var/root:/usr/bin/false
_cvmsroot:*:212:212:CVMS Root:/var/empty:/usr/bin/false

其他

awk还有其他的功能,比如支持for循环,if语句,while循环等待

for 循环
feilongdeMBP:~ feilong$ awk '/root/{print $0; for(i=1; i< 4; i++) {print "test"}}' /etc/passwd
root:*:0:0:System Administrator:/var/root:/bin/sh
test
test
test
daemon:*:1:1:System Services:/var/root:/usr/bin/false
test
test
test
_cvmsroot:*:212:212:CVMS Root:/var/empty:/usr/bin/false
test
test
test

参考资料