1 SQL优化show status查看各种SQL执行效率# 查看本session的sql执行效率mysqlshowstatuslikeCom_%;--------------------------------------------|Variable_name|Value|--------------------------------------------|Com_admin_commands|0|...# 查看全局的统计结果SHOWGLOBALSTATUSLIKECom_%# 查看服务器的状态showglobalstatus;结果Com_select:执行select操作的次数依次查询之累加1Com_insert:执行insert操作的次数对于批量插入的insert操作只累加依次Com_update:执行update操作的次数Com_delete:执行delete的次数上面是对所有存储引擎的表进行累计下面是针对InnoDB存储引擎的累加算法略不同Innodb_rows_read SELECT查询返回的行数Innodb_rows_inserted 执行inser操作插入的行数Innodb_rows_updated 执行UPDATE操作更新的行数Innodb_rows_deleted 执行DELETE操作删除的行数了解当前数据库应用是插入更新 or 查询操作为主及各类SQL执行比例。更新操作的计算是对执行次数的计数无论提交or回滚都累加对于事务型应用通过Com_commit和Com_rollback可以了解事务提交和回滚的情况回滚操作频繁数据库可能意味应用编写存在问题Connections:试图连接 MySQL 服务器的次数Uptime服务器工作时间Slow_queries:慢查询次数2 定位执行效率低SQL2.1 定位问题慢查询日志--log-show-queries[file_name]选项启动mysqlId写一个包含所有执行时间超过long_querty_time秒的sql语句的日志文件# Overall: 982.57k total, 17 unique, 24.80 QPS, 20.63x concurrency# Time range: 2019-08-26T15:00:05 to 2019-08-27T02:00:15# Attribute total min max avg 95% stddev median# Exec time 817129s 200ms 3s 832ms 1s 274ms 777ms# Lock time 90s 23us 17ms 91us 103us 87us 89us# Rows sent 363.69k 0 544 0.38 0 4.77 0# Rows examine 24.81G 0 1.28M 26.48k 25.99k 16.78k 24.75k# Query size 524.62M 90 946 559.86 563.87 32.03 537.02# Profile# Rank Query ID Response time Calls R/Call V/M Item# # 1 0x1E33FEA9BC0 814216.09 99.6% 970941 0.8386 0.09 SELECT t_bname# MISC 0xMISC 2913.29 0.4% 11632 0.2505 0.0 16 ITEMS# Query_time distribution# 1ms# 10ms# 100ms ################################################################# 1s ################################重点RankResponse timeRows examineminshow processlist慢查询日志在查询结束后才记录所以在应用反应执行效率出现问题时查询慢查询日志并不能定位问题。可用show processlist查看当前MySQL在进行的线程包括线程的状态是否锁表等实时查看sql执行情况同时对一些锁表进行优化。mysqlshowprocesslist;------------------------------------------------------------------------------------------------------------|Id|User|Host|db|Command|Time|State|Info|------------------------------------------------------------------------------------------------------------|5|event_scheduler|localhost|NULL|Daemon|242579|Waitingonempty queue|NULL||16|root|localhost|common_mistakes|Sleep|488||NULL||17|root|localhost|common_mistakes|Query|0|starting|showprocesslist|------------------------------------------------------------------------------------------------------------监控监控3要素1、采集间隔2、指标计算方法最大值、最小值、平均值3、数据来源读出信息1、TPS和延迟异常飙升2、两指标的危害?持续延迟可能导致高可用失效3、两指标的依赖关系? TPS推升导致延迟升高4、推到出什么性能瓶颈? TPS15K时目前架构的性能瓶颈3 explain分析SQL的执行计划4 简单的优化方法4.1 分析和存储表的关键字分布分析的结果可以使得系统得到准确的统计信息使得sql能够生成正确的执行计划。如果用户感觉实际执行计划并不预期的执行计划执行一次分析表可能会解决问题mysqlanalyzetablepayments;------------------------------------------------------------------------------------------------ | Table | Op | Msg_type | Msg_text | ------------------------------------------------------------------------------------------------ | common_mistakes.payments | analyze | Error | Table common_mistakes.payments doesnt exist | | common_mistakes.payments | analyze | status | Operation failed | ------------------------------------------------------------------------------------------------4.2 检查表检查一个表或多个表是否有错误也可以检查视图是否错误checktablepayment;------------------------------------------------------------------------------------------- | Table | Op | Msg_type | Msg_text | ------------------------------------------------------------------------------------------- | common_mistakes.payment | check | Error | Table common_mistakes.payment doesnt exist | | common_mistakes.payment | check | status | Operation failed | -------------------------------------------------------------------------------------------4.3 优化表若删除了表的一大部分或已对可变长度的行表含varchar、blob、text列的表进行改动则用optimize进行表优化这个命令可以使表中的空间碎片进行合并、并且可以消除由于删除或者更新造成的空间浪费optimizetablepayment;对于innodb引擎的表可以通过设置innodb_file_per_taable参数设置InnoDb为独立表空间模式这样每个数据库的每个表都会生成一个独立的idb文件用于存储表的数据和索引可以一定程度减少Innodb表的空间回收问题,另外在删除大量数据后Innodb表可以通过alter table但是不锈钢引擎方式来回收不用的空间altertablepayment enigineinnodb;ANALYZE,CHECK,OPTIMIZE,ALTER TABLE执行期间都是对表进行锁定因此要在数据库不频繁的时候执行相关的操作4.4 拆分表分区将数据在物理上分隔开不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样当对这个表进行查询时只需要在表分区中进行扫描而不必进行全表扫描明显缩短了查询时间另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。4.5 存储过程 v.s 触发器触发器不能用EXECUTE语句调用而是在用户执行Transact-SQL语句时自动触发激活执行。触发器是在一个修改了指定表中的数据时执行的存储过程。一般创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。触发器不同于存储过程触发器主要是通过事件执行触发而被执行的存储过程可通过存储过程名称名字而直接调用。当对某表进行UPDATE、INSERT、DELETE时SQL SERVER就会自动执行触发器所定义的SQL语句从而确保对数据的处理必须符合这些SQL语句所定义的规则。4.6 数据库优化根据服务层面配置mysql性能优化参数从系统层面增强mysql的性能优化数据表结构、字段类型、字段索引、分表分库、读写分离等从数据库层面增强性能优化SQL语句合理使用字段索引从代码层面增强性能使用缓存和NoSQL数据库方式存储如MongoDB/Memcached/Redis来缓解高并发下数据库查询的压力减少数据库操作次数尽量使用数据库访问驱动的批处理方法不常使用的数据迁移备份避免每次都在海量数据中去检索提升数据库服务器硬件配置或者搭建数据库集群编程手段防止SQL注入使用JDBC PreparedStatement按位插入或查询正则表达式过滤非法字符串过滤大批量插入数据当用load导入数据适当的设置可以提供导入的速度对于MyISAM存储引擎的表可以通过以下方式快速导入大量的数据altertabletab_namedisablekeys;loading thedataaltertabletab_namedisablekeys;disable keys和enable keys 用来打开或者关闭MyISAM表非索引的更新。在导入大量的数据到一个非空的MyISAM表通过设置这两个命令可以提高导入的效率对于Innodb类型的表不能使用上面的方式提高导入效率因为Innodb类型的表是按照主键的顺序保存所有将导入的数据按照主键的顺序排序可以有效地提高导入数据的效率在导入数据强执行SET UNIQUE_CHECKS0关闭唯一性校验在导入结束后执行SET UNIQUE_CHECKS1.恢复唯一性校验可以提高导入的效率如果应用使用自动提交的方式建议在导入前执行SET AUTOCOMMIT0时关闭自动提交导入结束后再执行SET AUTOCOMMIT1打开自动提交也可以提高导入的效率优化insert语句如果同时从一个客户端插入很多行应尽量使用多个值表的insert语句这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗使得效率比分开执行的单个insert语句快大部分情况下使用多个值表的insert语句那比单个insert语句快上好几倍。insertintotestvalues(1,2),(1,3)...如果从不同客户插入很多行可以通过使用insert delayed语句提高更高的速度delayed的含义是让insert语句马上执行其实数据都被放到内存的队列中并没有真正写入磁盘这比每条语句分别插入要快的多LOW_PRIORITY刚好相反在所有其他用户对表的读写完成后才可以进行将索引文件和数据文件分在不同的磁盘上存放利用建表中的选项如果进行批量插入可以通过增加bulk_insert_buffer_size变量值的方法来通过速度但是这只能对MyISAM表使用。当从一个文本文件装载一个表时使用LOAD DATA INFILE。这通常比使用很多INSERT语句块快20倍优化ORDER BY语句第一种通过有序排序索引顺序扫描这种方式在使用explain分析查询的时候显示为Using Index不需要额外的排序操作效率较高-innodb引擎条件索引的列顺序和Order By子句的顺序完全一致索引中所有列的方向(升序,降序)和Order by子句完全一致Order by中的字段全部在关联表中的第一张表中explainselectcustomer_id from customer order by store_id;表mysqlSHOWCREATETABLErental_myisam;CREATETABLErental_myisam(rental_idint(11)NOTNULLDEFAULT0,rental_datedatetimeNOTNULL,inventory_idmediumint(8)unsignedNOTNULL,customer_idsmallint(5)unsignedNOTNULL,return_datedatetimeDEFAULTNULL,staff_idtinyint(3)unsignedNOTNULL,last_updatetimestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(rental_id),KEYrental_date(rental_date,inventory_id,customer_id))ENGINEMyISAMDEFAULTCHARSETutf8违反规则,则filesortmysqlexplainselect*fromrental_myisamwhererental_date2005-01-01orderbyrental_id\G***************************1.row***************************id:1select_type:SIMPLEtable: rental_myisamtype:ALLpossible_keys: rental_datekey:NULLkey_len:NULLref:NULLrows:16044Extra:Usingwhere;Usingfilesort违反上述规则时即使条件走了索引排序也会退化为 filesortmysqlexplainselect*fromrentalwhererental_date2005-05-09orderbyinventory_iddesc,customer_id\G***************************1.row***************************id:1select_type:SIMPLEtable: rentaltype: ref possible_keys: rental_datekey: rental_date key_len:5ref: constrows:1Extra:Usingwhere;Usingfilesort第二张通过返回数据进行排序也就是通常说的Filesort排序所有不是通过索引直接返回排序结果的排序豆角Filesort排序。Filesort并不代表通过磁盘文件进行排序而只是说明进行了一个排序操作至于排序操作是否进行了磁盘文件或临时表等则取决于MySql服务器对排序参数的设置和需要排序数据的大小-myshim引擎explainselect* from customer order by store_id;Filesort是通过相应的排序算法将取得的数据在sort_buffer_size系统变量设置的内存排序区进行排序如果内存装载不下它会将磁盘上的数据进行分块再对各个数据块进行排序然后将各个块合并成有序的结果集。sort_buffer_size设置的排序区是每个线程独占的所有同一个时刻MySql存在多个sort buffer排序区优化目标:尽量减少额外的排序通过索引直接返回有序数据.where和ordery by 使用相同的索引并且order by的顺序和索引顺序相同并且order by的字段都是升序或者都是降序。否则肯定需要额外的排序操作这样就会出现filesort优化group by 语句如果查询包括group by 但用户想要避免排序结果的消耗可以指定group by null优化嵌套查询子查询可以被更有效率的连接替代explainselect*fromcustomerwherecustomer_idnotin(selectcustomer_idfrompayment)explainselect*fromcustomer aleftjoinpayment bona.customer_idb.customer_idwhereb.customer idisnull连接之所用更有效率是因为mysql不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作优化分页查询一般分页查询通过创建覆盖索引能够比较好地提高性能。一个场景是limit 1000,20,此时Mysql排序出前1020条数据后仅仅需要返回第1001到1020条记录前1000条数据都被抛弃查询和排序代价非常高优化方式可以增加一个字段last_page_record.记录上一页和最后一页的编号通过explainselect...wherelast_page_record...desclimt..如果排序字段出现大量重复字段不适用这种方式进行优化5 MySQL常用技巧5.1 正则表达式序列序列说明^字符串的开始处进行排序$在字符串的末尾处进行匹配.匹配任意单个字符串包括换行服[…]匹配括号内的任意字符{FNXXXXFN}匹配不出括号内任意字符a*匹配零个或多个a(包括空串)a匹配一个或多个a不包括字符串)a?匹配一个或零个aa1\a2a(m)匹配m个aa(m,)匹配m个或更多aa(m,n)匹配m到n个aa(,n)匹配0到n个a(…)将模式元素组成单一元素使用select abcdefg regexp ^a; .....abcdefg要被匹配的字符串源字符串REGEXPMySQL 的正则表达式匹配运算符RLIKE作用完全一样^a正则表达式模式^是锚点表示“字符串的开始处”a表示匹配字符a本身所以^a的意思是字符串必须以字母a开头。abcdefg正好是以a开头的因此SELECTabcdefgREGEXP^a;-- 结果1MySQL 的REGEXP返回值只有三种1匹配成功真0匹配失败假NULL如果任一操作数为NULL与 LIKE 的区别特性 LIKE REGEXP / RLIKE通配符 只有 %任意多字符和 _单个字符 完整正则表达式^, $, ., *, , ?, [], {m,n} 等灵活性 简单模式匹配 强大的模式匹配性能 通常更快更容易利用索引 相对较慢通常无法利用普通索引所以 REGEXP 适合用于复杂的字符串校验比如验证手机号、邮箱格式、以某字符开头等但在海量数据的 WHERE 子句中要注意性能问题。5.2 如果range()提取随机行随机抽取某些行select * from categrory order by rand() limit 5;5.3 利用group by的with rollup 子句使用Group By的with rollup可以检索更多分组聚合信息selectdate_from(payment_date,%Y-%M),staff_id,sum(amount)frompaymentgroupbydate_formate(payment_date,%Y-%M),staff_id;5.4 用BIT GROUP FUNCTIONS做统计使用GROUP BY语句和BIT_AND、BIT_OR函数完成统计工作这两个函数的一般用途就是做数值之间的逻辑6 优化数据库对象6.1 优化表类型表需要使用何种数据类型工具应用来判断虽然考虑字段的长度会有一定的冗余但是不推荐让很多字段都留有大量的冗余这样既浪费磁盘的存储空间同时在应用操作时也浪费物理内存mysql可以使用函数procedure analyse对当前的表进行分析//输出的每一类信息都对数据表中的列的数据类型提出优化建议。第二语句高数procedure anaylse不要为那些包含的值多余16个或者256个字节的enum类型提出建议如果没有这个限制输出的信息可能很长ENUM定义通常很难阅读通过输出信息可以将表中的部分字段修改为效率更高的字段 select * from tb1_name procedure analyse(); select * from tb2_name procedure analyse(16,256);6.2 通过拆分提高表的访问效率垂直拆分把主码和一些列放到一个表然后把住码和另外的列放到另一个表 好处可以将常用的列放在一起不常用的列放在一起使得数据行变少一个数据页可以存放更多的数据在查询时会减少I/O次数缺点管理冗余查询所有数据需要用join操作水平拆分。根据一列或多列数据把数据行放到两个独立的表中水平拆分会给应用增加复杂度它通常在查询时需要多个表名查询所有数据需要UNION操作缺点只要索引关键字不大则在索引查询时表中增加了2-3倍的数据量查询时也增加了读一个索引的磁盘次数所有说拆分要考虑数据量的增长速度。常用场景表很大分割后可以降低在查询时需要读的数据和索引的页数同时也降低了索引的层数提高查询速度表中的数据本来就有独立性例如表中分别记录各个地区的数据或者不同时期的数据特别是有些数据常用而有些数据不常用需要把数据存放在多个介质上如账单最近三个月数据存在一个表中3个月之前的数据存放在另一个表成功一年的可以存储在单独的存储介质中。6.3 逆规范化数据库设计时需要瞒住规范化但是规范化程度越高产生的关系就越多关系越多直接结果就是表直接的连接操作越频繁而表连接的操作是性能较低的操作直接影响到查询的数据。反规范化的好处在于降低连接操作的需求降低外码和索引的数目还可以减少表的树木相应带来的问题可能出现数据的完整性问题。加快查询速度但是降低修改速度。好的索引和其他方法经常能够解决性能问题而不必采用反规范这种方法采用的反规范化技术增加冗余列指在多个表中具有相同的列它常用来在查询时避免连接操作增加派生列指增加的列来自其他表中的数据由其他表中的数据经过计算生成。增加的派生列其他作业是在查询时减少连接操作避免使用集函数重新组表指如果许多用户需要查看两个表连接出来的结果数据则把这两个表查询组成一个表来减少连接而提高性能分割表维护数据的完整性批处理维护是指对复制列或派生列的修改积累一定的时间后运行一批处理作业或修改存储过程对复制或派生列进行修改这只能对实时性要求不高的情况下使用数据的完整性也可由应用逻辑来实现这就要求必须在同一事务中对所有涉及的表进行增、删、改操作。用应用逻辑来实现数据的完整性风险较大因为同一逻辑必须在所有的应用中使用和维护容易遗漏。特别是在需求变化时不易于维护使用触发器对数据的任何修改立即触发对复制列或者派生列的相应修改触发器是实时的而且相应的处理逻辑只在一个地方出现易于维护一般来说是解决这类问题比较好的方法使用中间表提高统计查询速度对于数据量较大的表在其上进行统计查询通常会效率很低并且还要考虑统计查询是否会对在线的应用产生负面影响。通常在这种情况下使用中间表可以提高统计查询的效率session 表记录了客户每天的消费记录表结构如下CREATETABLEsession(cust_idvarchar(10),--客户编号cust_amountDECIMAL(16,2),--客户消费金额cust_dateDATE,--客户消费时间cust_ipvarchar(20)–客户IP 地址)由于每天都会产生大量的客户消费记录,所以session 表的数据量很大,现在业务部门有一具体的需求希望了解最近一周客户的消费总金额和近一周每天不同时段用户的消费总金额。针对这一需求我们通过2 种方法来得出业务部门想要的结果。方法1在session 表上直接进行统计,得出想要的结果。selectsum(cust_amount)fromsessionwherecust_dateadddate(now(),-7);方法2创建中间表tmp_session表结构和源表结构完全相同。CREATETABLEtmp_session(cust_idvarchar(10),--客户编号cust_amountDECIMAL(16,2),--客户消费金额cust_dateDATE,--客户消费时间cust_ipvarchar(20)–客户IP 地址);insertintotmp_sessionselect*fromsessionwherecust_dateadddate(now(),-7);转移要统计的数据到中间表,然后在中间表上进行统计得出想要的结果。在中间表上给出统计结果更为合适,原因是源数据表(session 表)cust_date 字段没有索引并且源表的数据量较大所以在按时间进行分时段统计时效率很低这时可以在中间表上对cust_date 字段创建单独的索引来提高统计查询的速度。中间表在统计查询中经常会用到其优点如下:中间表复制源表部分数据并且与源表相“隔离”在中间表上做统计查询不会对在线应用产生负面影响.中间表上可以灵活的添加索引或增加临时用的新字段,从而达到提高统计查询效率和辅助统计查询作用。简单的SQL可能带来大的问题where条件中注意数据类型,避免类型转换隐式