MySQl高级篇-查询优化篇

SQL调优优化慢 SQL 遵循一个核心原则“先定位再分析后优化”。盲目去加索引或改代码往往事倍功半。️ 第一步定位慢 SQL精准排查开启慢查询日志Slow Query Log在 MySQL 中配置slow_query_log 1设置long_query_time 1记录超过 1 秒的 SQL生产环境可设更低如 0.5s将慢 SQL 收集到日志文件中。 第二步分析执行计划找到根因找到慢 SQL 后绝对不要直接改代码先在 SQL 前加EXPLAIN关键字执行查看执行计划。重点关注以下几列字段关注重点优化目标type访问类型从优到劣systemconsteq_refrefrangeindexALL尽量达到ref或range严禁出现ALL全表扫描possible_keys可能会用到的索引如果为空说明没有合适的索引key实际用到的索引如果为NULL说明没用上索引key_len索引使用的字节长度越短越好复合索引中可以看到命中了前几个字段rows预估扫描的行数越小越好值太大说明检索效率极低Extra额外信息危险信号1.Using filesort内存/磁盘排序2.Using temporary使用了临时表尽量优化为Using index覆盖索引 第三步落地优化策略四大维度导致慢 SQL 的原因通常分为索引没用好、SQL 结构写得差、数据量太庞大、架构/硬件限制。针对性解决策略如下维度 1索引优化性价比最高遵守“最左前缀法则”联合索引(a, b, c)查询条件必须从a开始不能跳过中间列。例如WHERE b 1 AND c 2无法使用该索引。避免“索引失效”避坑防线❌隐式类型转换字符串列查询时不加单引号如phone 13800000000MySQL 会隐式调用函数转换导致索引失效。❌OR连接非索引列WHERE indexed_col 1 OR non_indexed_col 2会导致整个查询不走索引。利用“覆盖索引”Covering Index尽量只SELECT需要的字段且这些字段刚好被联合索引覆盖。**避免SELECT ***这样可以省去一次“回表Bookmark Lookup”的磁盘 IO 操作。利用“索引下推”ICP, Index Condition PushdownMySQL 5.6 默认开启。在联合索引中过滤条件直接在存储引擎层完成减少回表次数。维度 2SQL 语句编写优化深分页优化Deep Paging /LIMIT 1000000, 10痛点LIMIT 1000000, 10需要先扫描 1,000,010 条数据再丢弃前 100 万条极其缓慢。解法 1子查询/延迟关联先只在索引覆盖表中找出自增 ID再关联原表SELECT*FROMtJOIN(SELECTidFROMtORDERBYidLIMIT1000000,10)bONt.idb.id;解法 2游标/标签法如果 ID 是连续增长的记录上一页最大的 IDSELECT*FROMtWHEREid1000000LIMIT10;小表驱动大表INvsEXISTS外表大内表小用IN例如大表 WHERE id IN (SELECT id FROM 小表)。外表小内表大用EXISTS。优化JOIN连接用小表过滤后的结果集小作为驱动表。被驱动表的连接字段必须建索引触发 Block Nested-Loop Join 会极慢。维度 3表结构与数据层优化字段类型选择能用小整数就不用大整数如TINYINTvsINT。尽量定义为NOT NULL给字段设置默认值NULL会使索引维护更复杂、占用空间。适度冗余反范式设计对于高频跨表联查可以在主表中冗余 1~2 个不常修改的字段减少JOIN性能开销。维度 4架构与外部优化当 SQL 已无法再优化时如果 SQL 已经优化到极致但数据量太庞大如几千万、上亿条就必须借力架构层引入缓存Redis / Memcached读写分离与主从复制分库分表Sharding数据冷热分离 / 归档SQL性能分析SQL性能下降原因查询语句写的烂索引失效数据变更关联查询太多join设计缺陷或不得已的需求服务器调优及各个参数设置缓冲、线程数等SQL调优过程观察至少跑1天看看生产的慢SQL情况。开启慢查询日志设置阙值比如超过5秒钟的就是慢SQL并将它抓取出来。explain 慢SQL分析。show profile。运维经理 or DBA进行SQL数据库服务器的参数调优。SQL执行频率MySQL客户端连接成功后通过 show [session l global] status 命令可以提供服务器状态信息。通过如下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:SHOW GLOBAL STATUS LIKECom_______;Mysql 查询优化小表驱动大表当B表是小表时用in优于existIN 会先把子查询结果集全部生成可能很大然后再去匹配。select*fromAwhereidin(selectidfromB)当A表是小表时用exist优于inselect*fromAwhereidexists(select1fromBwhereA.idB.id)EXISTS语法SELECT...FROMtableWHEREEXISTS(subquery)该语法可以理解为将主查询的数据放到子查询中做条件验证根据验证结果TRUE或FALSE来决定主查询的数据结果是否得以保留Group by 优化group by实质是先排序后进行分组遵照索引建的最佳左前缀。当无法使用索引列增大sort_buffer_size参数的设置增大max_length_for_sort_data参数的设置可以提高排序和分组的效率。where高于having能写在where限定的条件就不要去having限定了Show Profile进行sql分析重中之重Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have profiling参数能够看到当前MySQL是否支持profile操作:SELECThave_profiling;默认profiling是关闭的可以通过set语句在session / global级别开启profiling:SETprofiling1;随便执行一条sqlselect*FROMt_blog;执行一系列的业务SQL的操作然后通过如下指令查看指令的执行耗时:查看每一条SQL的耗时基本情况showprofiles;查看指定query id的SQL语句各个阶段的耗时情况showprofileforquery query_id;查看指定query_id的SQL语句CPU的使用情况和lO相关开销showprofile cpu,block ioforquery query_id;参数备注写在代码中show profile cpu,block io for query 3;如此代码中的cpu,blockALL显示所有的开销信息。BLOCK IO显示块lO相关开销。CONTEXT SWITCHES上下文切换相关开销。CPU显示CPU相关开销信息。IPC显示发送和接收相关开销信息。MEMORY显示内存相关开销信息。PAGE FAULTS显示页面错误相关开销信息。SOURCE显示和Source_functionSource_fileSource_line相关的开销信息。SWAPS显示交换次数相关开销的信息。日常开发需要注意的Status列中的出现此四个问题严重converting HEAP to MyISAM查询结果太大内存都不够用了往磁盘上搬了。Creating tmp table创建临时表拷贝数据到临时表用完再删除Copying to tmp table on disk把内存中临时表复制到磁盘危险!locked锁了MySQL中如何定位慢查询?在MySQL中也提供了慢日志查询的功能可以在MySQL的系统配置文件中开启这个慢日志的功能并且也可以设置SQL执行超过多少时间来记录到一个日志文件中我记得上一个项目配置的是2秒只要SQL执行的时间超过了2秒就会记录到日志文件中我们就可以在日志文件找到执行比较慢的SQL了。explain执行计划EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息包括在SELECT语句执行过程中表如何连接和连接的顺序。语法:直接在select语句之前加上关键字explain/descEXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;字段解释id表的读取顺序select查询的序列号包含一组数字表示查询中执行select子句或操作表的顺序id相同执行顺序从表格由上至下id不同如果是子查询id的序号会递增id值越大优先级越高越先被执行select_type 数据读取操作的操作类型查询的类型主要是用于区别普通查询、联合查询、子查询等的复杂查询。SIMPLE简单的select查询,查询中不包含子查询或者UNIONPRIMARY查询中若包含任何复杂的子部分最外层查询则被标记为最后加载的那个SUBQUERY在SELECT或WHERE列表中包含了子查询DERIVED在FROM列表中包含的子查询被标记为DERIVED衍生MySQL会递归执行这些子查询把结果放在临时表里UNION若第二个SELECT出现在UNION之后则被标记为UNION若UNION包含在FROM子句的子查询中外层SELECT将被标记为DERIVEDUNION RESULT从UNION表获取结果的SELECT两个select语句用UNION合并table显示执行的表名显示这一行的数据是关于哪张表的type sql的连接的类型这条sql的连接的类型性能由好到差为NULL、system、const、eq_ref、ref、range、 index、allsystem查询系统中的表const根据主键索引查询eq_ref主键索引查询或唯一索引查询表中只有一条记录与之匹配。常见于主键或唯一索引扫描。ref索引查询非唯一性索引扫描返回匹配某个单独值的所有行本质上也是一种索引访问它返回所有匹配某个单独值的行然而它可能会找到多个符合条件的行所以他应该属于查找和扫描的混合体range范围查询只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、、、in等的查询。这种范围扫描索引扫描比全表扫描要好因为它只需要开始于索引的某一点而结束语另一点不用扫描全部索引index索引树扫描index与ALL区别为index类型只遍历索引列。这通常比ALL快因为索引文件通常比数据文件小也就是说虽然all和Index都是读全表但index是从索引中读取的而all是从硬盘中读的all全盘扫描possible_key 当前sql可能会使用到的索引显示可能应用在这张表中的索引一个或多个。查询涉及到的字段火若存在索引则该索引将被列出但不一定被查询实际使用系统认为理论上会使用某些索引key当前sql实际命中的索引实际使用的索引。如果为NULL则没有使用索引要么没建要么建了失效查询中若使用了覆盖索引则该索引仅出现在key列表中key_len索引占用的大小可通过该列计算查询中使用的索引的长度。含义是The length of the chosen key所选键的长度。其单位是字节。根据这个值就可以判断索引使用情况。比如当key_len列显示为NULL时key列也就会显示为NULL, 说明语句没有用到索引。比如在使用组合索引的时候判断是否所有的索引字段是否都被用到。如何根据key_len的值判断是否所有的索引字段都被用到就要知道key_len的计算规则。key_len的计算规则可以为NULL的列的key长度比非NULL列的key长度大1。CREATETABLEa_test(idint(4)unsignedNOTNULLAUTO_INCREMENT,server_idint(4)NOTNULLDEFAULTspan stylecolor:#98c3790/span,user_idint(4)DEFAULTNULL,PRIMARYKEY(id),KEYidx_server_id(server_id),KEYidx_user_id(user_id))ENGINEInnoDBDEFAULTCHARSETlatin1如上所示当使用idx_user_id索引时key_len的值是5(int类型长度41而使用idx_server_id索引时key_len的值是4仅为int类型长度4。如果索引列是字符型char字段则索引列数据类型本身占用空间跟字符集有关。不同的字符集下同一个字符存储到表中的时候它所占用的空间大小是不同的。一个字符存储在表中到底占用多少个字节byte需要根据不同的字符集来分别计算。常用的几种字符集下字符character和字节byte的换算关系如下字符集1个字符占用字节数MaxlenGBK2UTF83UTF8mb44latin11如果索引列是变长的(比如varchar)则在索引列数据类型本身占用空间的基础上再加2。我们把上面的char类型替换成varchar。ref表之间的引用显示索引的哪一列被使用了如果可能的话是一个常数。哪些列或常量被用于查找索引列上的值。Extra额外的优化建议如果一条sql执行很慢的话我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况比如在这里面可以通过key和key_len检查是否命中了索引如果本身已经添加了索引也可以判断索引是否有失效的情况第二个可以通过type字段查看sql是否有进一步的优化空间是否存在全索引扫描或全盘扫描第三个可以通过extra建议来判断是否出现了回表的情况如果出现了可以尝试添加索引或修改返回字段来修复面试官了解过索引吗什么是索引候选人嗯索引在项目中还是比较常见的它是帮助MySQL高效获取数据的数据结构主要是用来提高数据检索的效率降低数据库的IO成本同时通过索引列对数据进行排序降低数据排序的成本也能降低了CPU的消耗面试官索引的底层数据结构了解过嘛 ?候选人MySQL的默认的存储引擎InnoDB采用的B树的数据结构来存储索引选择B树的主要的原因是第一阶数更多路径更短第二个磁盘读写代价B树更低非叶子节点只存储指针叶子阶段存储数据第三是B树便于扫库和区间查询叶子节点是一个双向链表