1题记最近公司项目增加了新的功能。上线后发现部分功能列表查询耗时较长。原因是新函数使用了老函数的接口，这些老接口的SQL查询语句关联了5、6张表，写的不够规范，导致MySQL在执行SQL的时候索引失败语句并执行全表扫描。原本负责优化的同事请假回家，于是优化查询数据的问题就落到了笔者的手上。笔者在查阅了网上的SQL优化资料后成功解决了问题。在这里，我将从全局的角度，记录和总结MySQL查询优化的相关技巧。2、优化思路数据查询慢并不代表SQL语句的写法有问题。首先，我们要找到问题的根源，才能“对症下药”。笔者用一张流程图来展示MySQL的优化思路：话不多说，从图中可以清楚的看出数据查询慢的原因有很多，比如：缓存失效，由于期间访问高并发这段时间导致MySQL服务器崩溃；SQL语句编写问题；MySQL服务器参数问题；硬件配置限制MySQL服务性能问题等3.查看MySQL服务器的运行状态。如果系统并发请求数不高，查询速度慢，可以忽略这一步，直接进入SQL语句调优步骤。执行命令：showstatus由于返回的结果太多，这里就不贴出结果了。其中，在返回的结果中，我们主要关注“Queries”、“Threadsconnected”和“Threadsrunning”的值，即查询数、线程连接数和线程运行数。我们可以通过执行以下脚本来监控MySQL服务器的运行状态#!/bin/bashwhitletruedomysqladmin-uroot-p"password"ext|awk'/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d%d%d\n",q,c,r)}'>>status.txtsleep1done执行脚本24小时，获取status.txt中的内容，并通过awk再次计算==每秒MySQL服务请求数==awk'{q=$1-last;last=$1}{printf("%d%d%d\n",q,$2,$3)}'status.txt计算内容在Excel中生成图表，观察数据的周期性。如果观察到的数据周期性变化，如上图说明，则需要修改缓存失效策略。例如：通过随机数获取区间[3,6,9]中的其中一个值作为缓存过期时间，分散了缓存过期时间，节省了一部分内存消耗。在访问高峰期，将一部分请求分发到未过期的缓存中，另一部分访问MySQL数据库，减轻了MySQL服务器的压力。4、获取需要优化的SQL语句4.1方法一：查看运行线程执行命令：showprocesslist返回结果：mysql>showprocesslist;+----+-----+-----------+--------+--------+------+--------+-----------------+|Id|User|Host|db|Command|Time|State|Info|+----+-----+-----------+-------+--------+------+----------+--------------+|9|root|localhost|test|Query|0|starting|showprocesslist|+----+-----+---------+------+--------+------+----------+----------------+1rowinset(0.00sec)从返回结果可以知道线程执行了什么命令/SQL语句，执行时间。在实际应用中，查询的返回结果会有N条记录。其中，返回的State值是我们判断性能的关键。如果出现如下值，说明这一行记录的SQL语句需要优化：ConvertingHEAPtoMyISAM#查询结果过大时，把结果放到磁盘上，严重的Createtmptable#CreateTemporarytable，严重的Copyingtotmptableondisk#Copythememorytemporary表到磁盘，严重锁定#被其他查询锁定，严重logginslowquery#记录慢查询Sortingresult#排序4.2方法二：打开慢查询日志【配置文件my.cnf中的mysqld】在该行下面添加两个参数：slow_query_log=1slow_query_log_file=/var/lib/mysql/slow-query.loglong_query_time=2log_queries_not_using_indexes=1其中slowquerylog=1表示开启慢查询；slowquerylogfile表示存储慢查询日志的位置；longquerytime=2表示查询>=2秒后才记录日志；logqueriesnotusing_indexes=1记录没有使用索引的sql语句。注意：slowquerylog_file的路径不能随便写，否则MySQL服务器可能没有权限将日志文件写入指定目录。建议直接复制上面的路径。修改保存文件后，重启MySQL服务。slow-query.log日志文件将在/var/lib/mysql/目录下创建。连接MySQL服务器，执行以下命令查看配置。显示像“slow_query%”这样的变量；显示像“long_query_time”这样的变量；测试慢查询日志：mysql>selectsleep(2);+----------+|sleep(2)|+--------+|0|+----------+1rowinset(2.00sec)打开慢查询日志文件[root@localhostmysql]#vim/var/lib/mysql/slow-query.log/usr/sbin/mysqld,Version:5.7.19-log(MySQLCommunityServer(GPL)).startedwith:Tcpport:0Unixsocket:/var/lib/mysql/mysql.sockTimeIdCommandArgument#Time:2017-10-05T04:39:11.408964Z#User@Host:root[root]@localhost[]Id:3#Query_time:2.001395Lock_time:0.000000Rows_sent:1Rows_examined:0usetest;SETtimestamp=1507178351;selectsleep(2);我们可以看到刚才执行了2秒的SQL语句被记录了下来。虽然查询慢SQL信息记录在慢查询日志中，但是日志记录的内容比较密集，查阅起来比较困难。因此，我们需要通过工具过滤掉SQL。MySQL提供了mysqldumpslow工具来分析日志。我们可以使用mysqldumpslow--help查看命令相关的用法。常用参数如下：-s：排序方式，后面跟着参数c：访问次数l：锁定时间r：返回记录t：查询时间al：平均锁定时间ar：平均返回记录at：平均查询时间-t：返回前面多少条数据-g：遍历匹配一个正则表达式，不区分大小写大小写：获取返回最多的10条记录集sqlmysqldumpslow-sr-t10/var/lib/mysql/slow-query.log获取访问次数Thetop10sqlmysqldumpslow-sc-t10/var/lib/mysql/slow-query.log获取按时间排序的top10包含leftjoin的查询语句mysqldumpslow-st-t10-g"leftjoin"/var/lib/mysql/slow-query.log5、分析SQL语句5.1方法一：explain过滤掉有问题的SQL，我们可以使用MySQL提供的explain查看SQL的执行计划（关联表、表查询顺序、索引使用情况等）。)。用法：解释选择*来自类别；返回结果：mysql>explainselect*fromcategory;+----+------------+--------+-----------+------+----------------+------+--------+------+------+----------+--------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+------------+----------+------------+-----+----------------+------+--------+------+-------+-----------+--------+|1|SIMPLE|类别|NULL|ALL|NULL|NULL|NULL|NULL|1|100.00|NULL|+-----+-----------+------------+------------+------+----------------+------+--------+------+------+---------+--------+1rowinset,1warning(0.00sec)字段说明：1）id：选择查询序列号。如果id相同，则执行顺序为从上到下；id不同，id值越大，优先级越高，越早执行withthisvaluederived：from列表中包含的子查询被标记为这个值，MySQL会递归执行这些子查询，结果放在临时表中union：如果union之后出现第二个select，则标记为this价值。如果from的子查询中包含union，则外层select被标记为derivedunionresult：从union表中获取结果的select3）table：显示行数据是关于哪个表的4）partitions：匹配的分区5）type：表连接类型，其值，性能从高到低排列如下：system：表只有一行记录，相当于系统表const：通过索引查找一次，仅匹配一行数据eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引扫描ref：非唯一索引扫描，返回匹配单个值的所有行。=、<或>运算符的索引列范围：仅检索给定范围的行，使用索引来选择行。一般使用between,>,<情况索引：只遍历索引树ALL：全表扫描，性能最差注意：前5种情况是比较理想的索引使用方式。通常至少优化到range级别，最好到ref6)possible_keys：表示MySQL使用哪个索引来查找这个表中的行记录。如果该值为NULL，表示没有使用索引，可以建立索引来提高性能7)key：显示MySQL实际使用的索引。如果为NULL，则不使用索引查询8）key_len：表示索引使用的字节数，通过它计算查询使用的索引长度。在不损失准确性的情况下，长度越短越好。显示索引字段的最大长度，而不是实际使用的长度。9）ref：显示表的哪个字段与表的索引字段相关联。10)rows：根据Table统计信息和选择，粗略估计需要查找或读取的行数，值越小越好11)filtered：返回的行数占读取的行数的百分比，值越大越好12)extra：包含不适合在其他栏中显示但非常重要的额外信息。常用值如下：usingfilesort：表示MySQL将使用外部索引对数据进行排序，而不是按照表中的索引顺序读取。当出现这个值时，SQLusingtemporary应该进行优化：使用临时表保存中间结果，MySQL在对查询结果进行排序时使用临时表。常见于排序orderby和分组查询groupby。当出现这个值时，SQLshouldbeoptimizedusingindex：表示对应的select操作使用覆盖索引，避免访问表的数据行，效率很高。usingwhere：where子句用于限制哪一行usingjoinbuffer：useconnectioncachedistinct：找到第一个匹配后，停止为当前行组合搜索更多行注意：SQL语句必须针对前2个值进行优化.5.2方法二：profiling使用profiling命令可以了解SQL语句消耗资源的详细信息（每一步执行的成本）。5.2.1查看profile开启状态select@@profiling；返回结果：mysql>select@@profiling;+------------+|@@profiling|+--------------+|0|+-----------+1rowinset,1warning(0.00sec)0表示关闭，1表示开启5.2.2Enableprofilesetprofiling=1;返回结果：mysql>setprofiling=1;QueryOK,0rowsaffected,1warning(0.00sec)mysql>select@@profiling;+------------+|@@profiling|+------------+|1|+------------+1rowinset,1warning(0.00sec)连接关闭后，profiling状态自动设置为off。5.2.3查看执行过的SQL列表showprofiles；返回结果：mysql>showprofiles;+------------+------------+---------------------------+|Query_ID|Duration|Query|+--------+-------------+------------------------------+|1|0.00062925|select@@profiling||2|0.00094150|showtables||3|0.00119125|showdatabases||4|0.00029750|SELECTDATABASE()||5|0.00025975|showdatabases||6|0.00023050|showtables||7|0.00042000|showtables||8|0.00260675|descrole|0.00074900|selectname,is_keyfromrole|+----------+------------+-----------------------------+9rowsinset,1warning(0.00sec)在这条命令执行之前，需要执行其他的SQL语句来记录。5.2.4查询指定ID的执行详情showprofileforqueryQuery_ID；返回结果：mysql>showprofileforquery9;+--------------------+-----------+|Status|Duration|+--------------------+--------+|开始|0.000207||检查权限|0.000010||打开表|0.000042||初始化|0.000050||系统锁|0.000012||优化|0.000003||统计|0.000011||准备|0.000011||执行|0.00002||0|0|0|0enddata|060queryend|0.000006||closingtables|0.000006||freeingitems|0.000011||cleaningup|0.000013|+--------------------+----------+15rowsinset,1warning(0.00sec)每行是状态变化的过程及其持续时间。Status栏目与Stateofshowprocesslist一致。因此，需要优化的注意点与上述相同。5.2.5获取CPU、BlockIO等信息showprofileblockio,cpuforqueryQuery_ID;showprofilecpu,blockio,memory,swaps,contextswitches,sourceforqueryQuery_ID;showprofileallforqueryQuery_ID;6．优化方法主要从查询优化、索引使用、表结构设计等方面进行讲解。6.1查询优化1)避免SELECT*，需要什么数据就查询对应的字段。2）小表带动大表，即小数据集带动大数据集。举例：以两张表A和B为例，两张表通过id字段关联。当B表的数据集小于A表的数据集时，使用in优化exist；usein，两张表的执行顺序是先查表B，再查表Auseexists，两张表的执行顺序是先查A表，再查B表join，MySQL不会在内存中创建临时表。4）适当增加冗余字段，减少表关联。5)合理使用索引（如下所述）。如：为排序和分组字段建立索引，避免出现filesort。更多：这里是一个MySQL索引数据结构和优化列表6.2索引的使用6.2.1适合使用索引的场景1）主键自动创建唯一索引2）常用字段作为查询条件3）关联其他表的字段query4)查询中的排序字段5)查询中的统计或分组字段6.2.2不适合使用索引的场景1)频繁更新的字段2)where条件中不用的字段3)表记录太少4)频繁增删改查5）字段的值差异不大或重复性高6.2.3索引的创建和使用原则1）单表查询：以哪一列作为查询条件，对该列建立索引2)多表查询：leftjoinrightjoin时，索引添加到右表的关联字段；rightjoin时，索引添加到左表的关联字段3）不对索引列进行任何操作（计算、函数、类型转换）4）索引列中不使用!=、<>Not等于5）索引列不能为空，不要用isnull或isnotnull来判断6）索引字段为字符串类型，查询条件的值要加上''单引号避免底层类型的自动转换。违反以上原则可能导致6.2.4索引失效除了违反索引创建和使用原则外，以下情况也会导致索引失效：1）使用模糊查询时，以%开头2）使用or时，例如：字段1（非索引）或字段2（索引）将导致索引失败。3）使用复合索引时，不使用第一个索引列。index(a,b,c)，以字段a,b,c为复合索引为例：6.3.1选择合适的数据类型6.3数据库表结构设计1)使用能存储数据的最小数据类型2)易于使用的数据类型。int在mysql中比varchar类型更容易处理3）尽量使用tinyint、smallint、mediumint作为整数类型而不是int4）尽可能使用notnull来定义字段，因为null占用4个字节的空间5）使用texttype尽量少，不使用如果不行，最好考虑分表6）尽量用timestamp代替datetime7）单表字段不要太多，建议是20以内6.3.2分表当数据库中的数据非常大时，查询优化方案无法解决问题当查询速度较慢时，可以考虑分表，减少每张表的数据量，从而提高查询效率。1）垂直拆分：将一个表中的多列拆分成不同的表。比如user表中有些字段是经常访问的，把这些字段放在一个表中，把一些不常用的字段放在另一个表中。插入数据时，使用事务来保证两张表数据的一致性。2）水平拆分：按行拆分。比如在user表中，使用用户ID，用户ID取10的余数，将用户数据均匀分布到0到9的10个用户表中，查找时也是按照这个查询数据规则。6.3.3读写分离一般来说，对于数据库来说，是“读多写少”。也就是说，数据库的压力大部分是由于大量的读取数据的操作造成的。我们可以采用数据库集群方案，以一个库作为主库，负责写入数据；其他库为从库，负责读取数据。这样可以减轻数据库访问的压力。7.服务器参数调优7.1内存相关sortbuffersize排序缓冲区内存大小joinbuffersize使用连接缓冲区大小readbuffersize全表扫描时分配的缓冲区大小7.2IO相关Innodblogfile_size事务日志大小Innodblogfilesingroup事务日志个数Innodblogbuffer_size事务日志缓冲区大小Innodbflushlogattrx_commit事务日志刷新策略，其取值如下：0：每秒将日志写入缓存一次，并刷新到磁盘1：每次事务提交时执行日志写入缓存，并将日志刷新到磁盘2：每次事务提交,执行日志数据写入缓存,每秒执行一次flushlog到磁盘7.3security-relatedexpirelogsdays指定自动清除binlog的天数maxallowedpacket控制MySQL可以接收的数据包大小skipnameresolvedisableDNSlookupread_onlyprohibit非超级权限用户免写权限skipsslavestart级别你useslaveautomaticrecovery7.4Othersmax_connections控制允许的最大连接数tmptablesize临时表大小maxheaptable_size最大内存表大小作者没有使用这些参数来调优MySQL服务器。详情和性能效果请参考文末资料或单独百度。八、硬件选择和参数优化硬件的性能直接决定了MySQL数据库的性能。硬件的性能瓶颈直接决定了MySQL数据库的运行数据和效率。作为软件开发程序员，我们主要关注软件优化内容。下面的硬件优化就足够理解了。8.1内存相关内存IO比硬盘快很多，可以增加系统的缓冲能力，将数据保存在内存中。8.2磁盘I/O相关1）使用SSD或PCleSSD设备，IOPS至少提升数百倍甚至万倍2）同时购买带有CACHE和BBU模块的阵列卡，可以显着提升IOPS3）使用RAID-10尽可能代替RAID-58.3配置CUP相关在服务器的BIOS设置中，调整如下配置：1)选择PerformancePerWattOptimized(DAPC)模式以最大化CPU的性能2)DisableC1E和CStates提高CPU效率3）MemoryFrequency（内存频率）选择MaximumPerformance
