mysql应用速查

mysql优化实践为和“数据库设计”笔记区分本篇侧重于记录“事后”的优化方法。在“数据库设计”中记录建库时的注意事项explain 关键字explainsql语句会输出几列分析的数据比如:select_typetabletyperows本行分析结果对应的表名ALL全表查询查询的行数参数解释2、select_typesimple不需要union的操作或者是不包含子查询的简单select语句。primary需要union操作或者含有子查询的select语句。union连接两个select查询第一个查询是dervied派生表第二个及后面的表select_type都是union。dependent union与union一样出现在union 或union all语句中但是这个查询要受到外部查询的影响。union result包含union的结果集。subquery除了from字句中包含的子查询外其他地方出现的子查询都可能是subquery。dependent subquery与dependent union类似表示这个subquery的查询要受到外部表查询的影响。derivedfrom字句中出现的子查询也叫做派生表其他数据库中可能叫做内联视图或嵌套select。3、table表名如果是用了别名则显示别名4、type依次从好到差systemconsteq_refreffulltextref_or_nullunique_subqueryindex_subqueryrangeindex_mergeindexALL除了all之外其他的type都可以使用到索引除了index_merge之外其他的type只可以用到一个索引。system表中只有一行数据或者是空表。const使用唯一索引或者主键返回记录一定是1行记录的等值where条件时通常type是const。eq_ref出现在要连接过个表的查询计划中驱动表只返回一行数据且这行数据是第二个表的主键或者唯一索引且必须为not null唯一索引和主键是多列时只有所有的列都用作比较时才会出现eq_ref。ref不像eq_ref那样要求连接顺序也没有主键和唯一索引的要求只要使用相等条件检索时就可能出现常见与辅助索引的等值查找。fulltext全文索引检索要注意全文索引的优先级很高若全文索引和普通索引同时存在时mysql不管代价优先选择使用全文索引。ref_or_null与ref方法类似只是增加了null值的比较。实际用的不多。unique_subquery用于where中的in形式子查询子查询返回不重复值唯一值。index_subquery用于in形式子查询使用到了辅助索引或者in常数列表子查询可能返回重复值可以使用索引将子查询去重。range索引范围扫描常见于使用,,is null,between ,in ,like等运算符的查询中。index_merge表示查询使用了两个以上的索引最后取交集或者并集常见and or的条件使用了不同的索引。index索引全表扫描把索引从头到尾扫一遍常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。all这个就是全表扫描数据文件然后再在server层进行过滤返回符合要求的记录。6、key查询真正使用到的索引。7、key_len用于处理查询的索引长度。9、rows执行计划中估算的扫描行数不是精确值。11、extra该字段信息较多这里就不一一叙述了。在实际的使用过程中我们需要重点去关注type、key、key_len、rows、extra这几个参数type要努力优化到range级别all要尽量少的出现在查询的过程中要尽量使用索引提高效率在extra里面出现Using filesort, Using temporary是不太好的要去优化提高性能。sql语句优化笔记先查id再根据id查内容不要直接Select *–对于数值型id在分页基础上优化使用 limit, offset当 offset 变大的时候执行效率会越来越低。因为 select 在执行过程中对于存储引擎返回的记录经过 server 层的 WHERE 条件筛选之后符合条件的前 offset 条记录会被直接抛弃直到符合条件的第 offset 1 条记录才开始发送给客户端发送了 limit 条记录之后查询结束。LIMIT OFFSET 分页慢的主要原因还是offset偏移量大了之后会多读取很多无效的数据伴随着回表取数然后丢弃的消耗。可以考虑join子查询的方式主查询只取主键id使用索引覆盖的特性提升效率然后通过主键 join 主表用到INLJ算法优化大数据量超过1kw就上ES吧不然太折腾了还不能持久。语法回顾先来简单的回顾一下 select 语句中 limit, offset 的语法MySQL 支持 3 种形式LIMIT limit: 因为没有指定 offset所以 offset 0表示读取符合 WHERE 条件的第 1 ~ limit 条记录。LIMIT offset, limit: 我们常用的就是这种了。LIMIT limit OFFSET offset: 这种不常用。offset 和 limit 的值都不能为负数在源码里这两个属性定义的是无符号整数并且在解析阶段就做了限制如果为负数直接报语法错误了。语法解析阶段在读取数据的过程中对于符合条件的前 offset 条记录会直接忽略不发送给客户端从符合条件的第 offset 1 条记录开始发送 limit 条记录给客户端。所以server 层实际上需要从存储引擎读取 offset limit 条记录源码里也是这么实现的语法解析阶段在验证了 offset 和 limit 都是大于等于 0 的整数之后就把 offset limit 的计算结果保存到一个叫做 select_limit_cnt 的属性里offset 也会保存到一个叫做 offset_limit_cnt 的属性里。发送数据阶段来到发送数据阶段此时的记录已经通过了 WHERE 条件的筛选接下来就是判断这条记录是不是要发送给客户端。第 1 步因为 offset 已经保存到 offset_limit_cnt 中了先来判断 offset_limit_cnt 是否大于 0如果大于 0这条记录就会被抛弃了不发送给客户端如果等于 0记录就具备了发送给客户端的资格了然后接着进入第 2 步。在抛弃记录之前还会干一件事对一个叫做 send_records 的属性进行加 1 操作就是假装这条记录已经发送了为什么这样干第 2 步会用到这个属性。offset_limit_cnt 是保证不会小于 0 的所以在这一步只需要判断是大于 0 还是等于 0 就可以了。第 2 步来到这一步记录就具备了发送给客户端的资格了至于要不要发就看客户端想不想要它了而客户端想不想要它取决于 select_limit_cnt。所以在这一步要判断已发送记录数量send_records和需要发送记的录数量select_limit_cnt之间的关系如果已发送记录数量大于等于需要发送的记录数量则结束查询否则就接着进入第 3 步。第 3 步在这里记录等待着被发送给客户端。等待的是网络缓冲区。最佳实践既然在 offset 变大之后使用 limit, offset 效率越来越低那应该怎么办呢以一个 SQL 为例select*fromt2wherei190000000limit8888,10为了取到 10 条记录要先找到 8888 条记录然后取到需要的 10 条前面 8888 条记录都白找了太浪费了可以这样修改一下select*fromt2wherei190000000andidLAST_MAX_IDlimit10LAST_MAX_ID 是上一次执行 SQL 时读取到的主键 ID 的最大值如果是第一次执行语句LAST_MAX_ID 0。不过这种方案也有个问题不支持跳着翻页只支持顺序翻页就是每次都点下一页的这种。如果要支持跳着翻页怎么办只用 MySQL 有点不够用了,还需要利用Redis可以把符合条件的记录的主键 ID 都读取出来存入到 Redis 的有序集合zset中用 zset 相应的函数读取到某一页应该展示的数据对应的那些主键 ID然后用这些主键 ID 去 MySQL 中查询对应的数据从而用间接的实现了分页功能。当然这个方案也是有适用场景的比如这个方案明显就不适用于这些场景符合条件的记录非常非常多导致存主键 ID 到 Redis 要占用很大的内存、记录更新频繁导致存主键 ID 的缓存经常被清除。如果碰到更复杂的场景就要结合业务具体情况具体分析了。根据最左前缀原则字段的基数大选择性好可对该字段单独建立索引字段基数很小选择性不好。传入的过滤条件where ***没有 station_nu 字段使用不到复合索引 IXFK_arrival_record 的 product_idstation_nosequencereceive_time 这几个字段。优化器当查询出数据以后会返回给执行器。执行器一方面将结果写到查询缓存里面当你下次再次查询的时候就可以直接从查询缓存中获取到数据了。另一方面直接将结果响应回客户端。mysql的编写过程和解析过程① 编写过程select dinstinct ..from ..join ..on ..where ..group by ..having ..order by ..limit ..② 解析过程from .. on.. join ..where ..group by ..having ..select dinstinct ..order by ..limit ..索引的优化实践USE INDEX(idx)只是告诉优化器只考虑这几个索引但优化器仍然可以自由选择全表扫描如果它觉得全表扫描更便宜。FORCE INDEX(idx)作用类似 USE INDEX但额外假设表扫描的代价非常高昂。换句话说只有找不到任何方式用指定索引来定位行时才会退化到全表扫描。IGNORE INDEX(idx)反过来明确排除某些索引。优化器判定该索引不可用时force仍然不会走索引而是会全表扫描。对索引列使用了函数WHERE YEAR(created_at) 2024即使 created_at 有索引也用不上。隐式类型转换索引列是 VARCHAR但 SQL 里用数字比较触发隐式转换索引失效。联合索引未命中最左前缀联合索引 (a,b,c)查询条件只有 WHERE b1 AND c2该索引不在候选列表里。统计信息严重过期大批量导入/删除后没跑 ANALYZE TABLE优化器基于错误的行数估算认为全表扫描更划算。索引选择性极低比如 gender 字段只有 M/F 两个值优化器评估走索引回表比全表扫描还慢。分区表 / JSON 字段 / 全文索引的特殊场景FORCE INDEX 可能在部分分区生效、其余分区仍全扫JSON 字段即使建了虚拟列索引FORCE INDEX 也常被忽略。没有索引100万条数据花了一秒左右查了90多万行100个用户登录就要等100秒才能完成这样好吗这样不好创建了索引后0.01秒就可以查的行数变少了。建立联合索引效率会更高尤其是在数据量较大单个列区分度不高的情况下在多表连接、where条件、排序、分组的字段上建立索引Where条件上不要使用运算函数以免索引失效阿里巴巴Java开发手册建议单表索引数量控制在5个以内组合索引字段数不允许超过5个其他建议每个Innodb表必须有个主键要注意组合索引的字段的顺序优先考虑覆盖索引避免使用外键约束索引失效常见的索引失效的场景有哪些以 % 开头的 LIKE 查询创建了组合索引但查询条件不满足 ‘最左匹配原则’。如创建联合索引 (type,status,uid)但是使用右边的 status 和 uid 作为查询条件。查询条件中使用 or且 or 的条件中有一个列没有索引则or中的其他列也不会走索引类型不匹配MySQL 的策略是将字符串转换为数字之后再比较。函数作用于表字段索引失效。后半句作用于表什么意思建议尽量少用or同时尽量用union all 代替union可以使用union all或者union代替or而这两者的区别是union是将两个结果合并之后再进行唯一性的过滤操作(合并重复数据)效率会比union all低很多。而union all要求两个数据集没有重复的数据因为不会合并两个查询结果中的相同数据因此会产生重复行。例子#ORselectename,job,fromt_empwherejobmanagerorjobsaleman;#可以改成selectename,job,fromt_empwherejobmanagerunionallselectename,job,fromt_empwherejobsaleman;优化关联查询在大数据场景下表与表之间通过一个冗余字段来关联要比直接使用JOIN有更好的性能。如果确实需要使用关联查询的情况下需要特别注意的是确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候如果优化器关联的顺序是A、B那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担一般来说除非有其他理由只需要在关联顺序中的第二张表的相应列上创建索引具体原因下文分析。确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列这样MySQL才有可能使用索引来优化。索引原理3.1 InnoDB索引实现InnoDB数据页由7个组成部分各个数据页可以组成一个双向链表。而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表。每个数据页都会为存储在它里面的记录生成一个页目录。在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽然后再遍历该槽对应分组中的记录即可快速找到指定的记录。页和记录的关系示意图如下索引同样存储在数据页中只不过目录项中的两个列是主键和页号。那InnoDB怎么区分一条记录是普通的用户记录还是目录项记录呢是根据记录头信息里的record_type属性它的各个取值代表的意思如下0普通的用户记录1目录项记录2最小记录3最大记录似乎可以先了解数据页3.2 查找步骤整体结构如下现在如果我们想根据主键值查找一条用户记录大致需要3个步骤以查找主键值为20的记录为例1、确定目录项记录页。2、通过目录项记录页确定用户记录真实所在的页。3、在页中定位到具体的记录。四、InnoDB中的索引分类4.1 聚簇索引上边介绍的B树索引。它有两个特点1、根据记录主键值的大小进行记录和页的排序这包括三个方面的含义页内的记录是按照主键的大小顺序排成一个单向链表。各个存放用户记录的页也是根据主键大小顺序排成一个双向链表。存放目录项记录的页分为不同的层次在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。2、B树的叶子节点存储的是完整的用户记录我们把具有这两种特性的B树称为聚簇索引所有完整的用户记录都存放在这个聚簇索引的叶子节点处。4.2 二级索引上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用因为B树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该咋办呢难道只能从头到尾沿着链表依次遍历记录么我们可以多建几棵B树不同的B树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则再建一棵B树效果如下图所示但是但是这个B树的叶子节点中的记录只存储了c2和c1也就是主键两个列所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。由于主键值具有唯一性二级索引不具有唯一性那么 新的问题来了在上图中如果我们想新插入一行记录其中c1、c2、c3的值分别是9、1、‘c’。那么在修改这个为c2列建立的二级索引对应的B树时便碰到了个大问题由于页3中存储的目录项记录是由c2列 页号的值构成的页3中的两条目录项记录对应的c2列的值都是1而我们新插入的这条记录的c2列的值也是1那我们这条新插入的记录到底应该放到页4中还是应该放到页5中啊懵逼了。为了让新插入记录能找到自己在那个页里我们需要保证在B树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的1、索引列的值2、主键值3、页号。我们为c2列建立二级索引后的示意图实际上应该是这样子的4.3 联合索引我们可以同时为多个列建立索引比方说我们想让B树按照c2和c3列的大小进行排序这个包含两层含义1、先把各个记录和页按照c2列进行排序2、在记录的c2列相同的情况下采用c3列进行排序。分页优化比如带上上次最大id、create_time而不用先查找前几条数据了。但是即使这一列没有索引能不能减少搜索量呢其他优化虽然 MySQL5.6 引入了物化特性但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成 JOIN。UPDATEoperation o SETstatusapplyingWHEREo.idIN(SELECTidFROM(SELECTo.id,o.statusFROMoperation oWHEREo.group123ANDo.statusNOTIN(done)ORDERBY o.parent,o.id LIMIT1)t);-----------------------------------------------------------------------------------------------------------------------------------------|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|-----------------------------------------------------------------------------------------------------------------------------------------|1|PRIMARY|o|index||PRIMARY|8||24|Using where;Using temporary||2|DEPENDENT SUBQUERY||||||||Impossible WHERE noticed after reading const tables||3|DERIVED|o|ref|idx_2,idx_5|idx_5|8|const|1|Using where;Using filesort|-----------------------------------------------------------------------------------------------------------------------------------------重写为 JOIN 之后子查询的选择模式从 DEPENDENT SUBQUERY 变成 DERIVED执行速度大大加快从7秒降低到2毫秒。UPDATEoperation oJOIN(SELECTo.id,o.statusFROMoperation oWHEREo.group123ANDo.statusNOTIN(done)ORDERBY o.parent,o.id LIMIT1)tONo.idt.id SETstatusapplyingMySQL 对待 EXISTS 子句时仍然采用嵌套子查询的执行方式。去掉 exists 更改为 join能够避免嵌套子查询Mysql技巧与经验比较范围不确定时不使用in如果使用需要保证范围是确定且有限的如指定in的条件而不是 XXX in(select id from XXX where XXX)这种范围为select子句in比较的数量可能不固定in 是不能命中索引的改成EXISTS查询效率提高select*fromt1whereNOTEXISTS(selectphonefromt2wheret1.phonet2.phone)in的替代方案1、用 EXISTS 或 NOT EXISTS 代替select*fromtest1whereEXISTS(select*fromtest2whereid2id1)select*FROMtest1whereNOTEXISTS(select*fromtest2whereid2id1)2、用JOIN 代替selectid1fromtest1INNERJOINtest2ONid2id1selectid1fromtest1LEFTJOINtest2ONid2id1whereid2ISNULLLEFT JOIN right id is null建表时最好不要有null?比如hibernate包装类型可能默认为null比较相等时可能null不和任何相等不知道如果要连接三个表的时候又该怎么办拆成多次从应用层访问数据层一次次搞筛选条件Mybatis批量插入有三种批量插入方式java代码中用foreach循环逐条插入性能最低MybatisPlus批量插入在xml文件中用foreach标签拼接sql语句但要注意一条sql语句有大小限制版本8.0.15为4M,sql语句过长会导致报错附录explain 其他参数ref常数等值查询显示const连接查询则显示表的关联字段。-possible_keys查询可能使用到的索引。id数字越大越先执行一样大则从上往下执行如果为NULL则表示是结果集不需要用来查询。filtered表示存储引擎返回的数据在server层过滤后剩下多少满足查询的记录数量的比例。