批量处理的万级数据的优化方案 业务场景需求：一个【手机号码】的文本list ，需要导入到数据库中 （并在根据这个号码写入一些XX字段、判断一些手机号的一些校验、一些远程请求调用的服务~）一些棘手的问题查询订单在是否存在这个手机号，存在就标记查询订单是否有多次下单，存在再标记查询用户是否vip用户，是是标记根据“手机号”远程调用【用户微服务】接口等，校验用户的真实身份根据用户取到用户手机号，再把用户信息存储redis上........N次的 （20次的查询IO 操作），还做了Redis的查询读写操作...等等测试结果：测试 ，导致100条数据查询 ，都需要30秒。比如 需要处理1-10W的数据，然后批量添加，每个$v的都需要处理很多的sql 查询的IO操作，还要多次的http请求 跨服务的调用，大部分的耗时都卡在“数据库”层面，导致的。目前优化方案每个查询都尽量走索引、减少回表操作，能合理的用到覆盖索引批量拼接sql 一次处理，分批处理 （错误示例 不要再循环中处理每条inster/update 操作）持续优化方案目前执行的慢，原因是卡在DB的IO操作，每条查询都要走20次的查询！！！需要优化sql的连接池 （配置文件连接池根据业务情况设置大一些）看能不能减少多余的判断操作（DB的操作IO ，减少一条是一条）能不走DB ，就走redis，看能不能全走缓存层，去掉db层最好。如果不行，就试试把一些判断数据用布隆过滤器 去走redis的一级缓存大数据的情况下，如果数据是冷数据，不是热数据不经常操作，可以把数据全部存入clickhouse ，然后再查，速度快的一批，缺点：更新删除操作 重新建立树结构。

MySQL如何优化超大的分页查询？ 前文在你开发程序的时候，使用 LIMIT 子句做分页查询可能是非常频繁的，这是一个非常常见的业务场景。那你在使用 limit 子句的时候有没有遇到过什么问题呢，比如说性能不好？曾经遇到过不少由于分页查询性能差，需要优化的案例。那常见的原因基本上也都是分页过多。过大的分页查询为什么会慢？下面我们先构造一个例子：/**生成3百万行的测试数据**/ CREATE TABLE `limit_optimize_tbl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account` varchar(50) NOT NULL, `order_id` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2000002 DEFAULT CHARSET=utf8 create index idx_account on limit_optimize_tbl(account); create index idx_order_id on limit_optimize_tbl(order_id); DELIMITER CREATE PROCEDURE limit_optimize_test() BEGIN DECLARE i INT; SET i=1000000; WHILE i<=3000000 DO INSERT INTO limit_optimize_tbl(account,order_id) VALUES('test_123',concat('order', i)); SET i=i+1; END WHILE; END DELIMITER ; call limit_optimize_test();下面的这行 SQL 是一个常见的分页查询的语句：mysql> select * from limit_optimize_tbl order by order_id limit 1000000,10;这种写法是最简单的，但同时也是最容易出问题的。曾经有人做过调查，用户在浏览前端页面时，假如这个页面是分页浏览的（例如淘宝），用户只会浏览前面几页，一般翻页超过 10 页很多人就开始表现的不耐烦了。在翻页比较少的情况下，LIMIT 子句并不会表现出性能问题。但是假如用户要直接跳到最后一页呢？通常情况下，由于要保证所有的页面都可以正常跳转，因此可能不会使用如下这种语句：mysql> select * from limit_optimize_tbl order by order_id desc limit 0,10;而是继续采用正序顺序做分页查询：mysql> select * from limit_optimize_tbl order by order_id limit 1000000,10;采用这种 SQL 查询的话，此时从 MySQL 中取出这 10 行数据的代价是非常大的，需要先排序出前面 1000010 条记录，然后抛弃前面的 1000000 条。查询数据和排序的代价非常高。我们再来看一下上面这个 SQL 语句的执行计划：mysql> explain select * from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select * from limit_optimize_tbl order by order_id limit 1000000,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1994816 filtered: 100.00 Extra: Using filesort 1 row in set, 1 warning (0.00 sec)从执行计划中你可以看到，在大分页的时候，MySQL 并没有走索引扫描，而是使用了全表扫描的方式。那这又是为什么呢？MySQL 数据库采用了基于代价的查询优化器，而查询代价的估算是基于 CPU 代价和 IO 代价。由于现在机械硬盘还没有被完全淘汰掉，因此在类似这种局部扫描的动作中，随机 IO 的代价，仍然被MySQL 的查询优化器认为是非常高的。对于局部扫描，MySQL 会根据数据量的情况和数据获取的条件，去做代价估算，决定是采用顺序扫描还是随机读取存储系统。如果 MySQL 在查询代价估算中，认为采取顺序扫描方式比局部随机扫描的效率更高的话，就会放弃索引，转向顺序扫描的方式。这就是为什么在大分页中 MySQL 数据库走了全表扫描的原因。下面我们还是使用刚刚的 SQL 语句，再来实验一下：mysql> explain select * from limit_optimize_tbl order by order_id limit 5660,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 5670 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select * from limit_optimize_tbl order by order_id limit 5661,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1994816 filtered: 100.00 Extra: Using filesort 1 row in set, 1 warning (0.00 sec) 从上面的实验你可以看到2条SQL，在这个临界点上，MySQL 分别采用了索引扫描和全表扫描的查询优化方式。你可以自行运行下这两个 SQL 语句，比较下执行时间。由于 MySQL 的查询优化器的算法核心我们是无法人工干预的，因此我们的优化思路应该着眼于如何让分页维持在最佳的效率区间。下面我们就来看下如何优化。优化可以从两个角度进行分析：SQL 改写优化业务角度优化。SQL改写优化方案1：索引覆盖由于索引是有序的，因此这种优化方式的思路是直接在索引上完成排序和分页的操作。先来说说什么是覆盖索引。如果一个 SQL 语句，通过索引即可直接获取查询结果，而不再需要回表查询，就称这个索引覆盖了这条SQL 语句。也就是平时所说的不需要回表操作。在 MySQL 数据库中使用执行计划查看，如果 extra 这一列中显示 Using index ，就表示这条 SQL 语句使用了覆盖索引。下面我们看下刚刚的那条 SQL 语句：mysql> explain select * from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select order_id from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec) mysql> explain select id from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec)从这个实验中可以看到，除了 select * 的那条 SQL 语句，其他两个都使用了覆盖索引。你也可以对比一下使用了覆盖索引的大分页和和没有使用覆盖索引的性能差异。在我的环境中执行实验中的 “LIMIT 1000000,10” 的分页查询，没有使用覆盖索引的 SQL 语句执行时间是 2.51s使用了覆盖索引的 SQL 语句执行时间是 0.16s优化效果还是非常明显的。方案2：子查询优化由于在 SELECT 语句中我们很少会只查询某一两个列，因此上述覆盖索引的适用范围就比较有限。可以通过将分页的 SQL 语句改写成子查询的方法获得性能的提升。示例如下：mysql> select * from limit_optimize_tbl where id >= (select id from limit_optimize_tbl order by order_id limit 1000000,1) limit 10; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.16 sec)执行时间和上一节的使用了覆盖索引的 SQL 语句基本一致。不知道你有没有观察到，这种优化方法也有其局限性：首先，分页的数据必须是连续的其次，WHERE 子句里面不能再添加别的条件方案3：延迟关联和上述子查询的做法类似，我们也可以使用 JOIN 的语法，先在索引上完成分页的操作，然后再回表获取需要的数据列。示例如下：mysql> select a.* from limit_optimize_tbl a inner join (select id from limit_optimize_tbl order by order_id limit 1000000,10) b on a.id=b.id; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.15 sec)你可以和上一小节的子查询优化的方式做个对比，在采用了 JOIN 语法改写之后，上面的两个限制都解除了，并且 SQL 的执行效率没有损失方案4： 记录书签和上述使用覆盖索引的思路不同，记录书签的优化思路是使用书签记录上一页数据的位置，下次分页时直接从这个书签的位置开始扫描，从而避免 MySQL 扫描大量的数据行再丢弃的操作。示例如下：mysql> select * from limit_optimize_tbl where id>=1000001 limit 0,10; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.00 sec)从上面的 SQL 语句你可以看到，由于使用了主键索引来做分页的操作，SQL 语句的性能是极佳的。使用其他列做书签也是可以的：mysql> select * from limit_optimize_tbl where order_id>='order2000000' limit 0,10; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.01 sec)这里要给你提个醒，如果没有使用主键索引或唯一索引做这个书签，排序的字段有大量重复值的情况下，输出的结果不一定是准确的，不适合使用这种写法。方案5： 反向查找反向查找即我们在本文的开头提到的例子：mysql> select * from limit_optimize_tbl order by order_id desc limit 0,10;这种优化的思路来自于二分查找，也就是说，当偏移量超过记录数的一半时，就可以使用这种写法来获得性能的提升不过这种方法需要在分页前知道符合条件的总的记录条数，但是在 InnoDB 存储引擎中，COUNT (*) 的开销其实也不小。因此建议你仅在一些特殊情况下选用，例如直接跳到尾页。业务角度优化下面我们一起来看看从业务角度如何优化大分页查询。其实这个优化思路要看具体的业务内容，业务是千变万化的，因此本文中提到的这几个案例，不一定就适合你们公司的业务类型，只是起个抛砖引玉的作用。翻页限制不允许翻过多的页 , 一言以蔽之，就是不给你查了。把 LIMIT 分页的偏移量做一个限制，超过某个阈值就停止。我们以淘宝网为例，使用比较热门的 “男鞋” 的关键词进行搜索，网站仅仅提供了 100 个数据页。很多大型互联网公司由于数据量巨大，都有使用这种方法。流式分页这种分页方式比较适用于移动端，即只能一页一页的向前或向后加载，不提供跳转的功能。可以在上一级入口中提供业务列表给用户选择，从而减少分页。这种分页方式在电商和新闻类 APP 上应用的非常广泛，你也可以试试。总结总的来说，大分页的优化思路就是让分页的SQL语句尽量在最佳的性能区间内执行，不要触发全表顺序扫描，也不要扫描太多的数据行。SQL 的优化有两个方向，一个是 SQL 语义的优化，另一个是从业务角度对 SQL 语句进行优化。SQL 语义的优化所能发挥的功力有限，在 SQL 优化的工作中大概只有3成的SQL 能通过 SQL 的改写完成优化；而从业务角度的调优占了有 7 成。因此在对 SQL 语句进行优化时，不妨多从业务角度入手，想想看有没有好的解决方案。参考资料《数据库查询优化器的艺术》，李海翔著《高性能 MySQL》第三版慕课专栏-MySQL 开发高频面试题精选

mysql使用or条件使索引失效 原因很多人都喜欢用 OR 去解决，虽然语句简单，但是就会发生下面的场景，容易走不到索引失效指的是，条件中如果有or，只要其中一个条件没有索引，其他字段有索引也不会使用。原sql语句SELECT `user`, `check_money`, `is_freeze_up`, `freeze_up_month`, `freeze_money`, `freeze_up_time`, `check_money_2_month`, `check_money_2`, `check_money_2_time`, `tc_qr`, `log_id`, 1 AS type, `branch` FROM `pxs_custom_log_bv_sale` `s` LEFT JOIN `pxs_custom_money_log` `l` ON `s`.`log_id` = `l`.`id` WHERE ( l.lock_month = '2020-11' OR s.freeze_up_month = '2020-11' OR s.check_money_2_month = '2020-11' ) 因为表设计这种结构并且要求这种业务场景。这里的lock_month 和freeze_up_month、check_money_2_month虽然都加了索引，但是EXPLAIN分析下， type是 all ，还是走全表扫描（ALL），非常慢。解决方案把3个where的字段分开几个sql段。使用union或者union all去连接多个 SELECT 语句的结果组合到一个结果集合中 。 EXPLAIN SELECT `user`, `check_money`, `is_freeze_up`, `freeze_up_month`, `freeze_money`, `freeze_up_time`, `check_money_2_month`, `check_money_2`, `check_money_2_time`, `tc_qr`, `log_id`, 1 AS type, `branch` FROM `pxs_custom_log_bv_sale` `s` LEFT JOIN `pxs_custom_money_log` `l` ON `s`.`log_id` = `l`.`id` WHERE ( l.lock_month = '2020-11') UNION all SELECT `user`, `check_money`, `is_freeze_up`, `freeze_up_month`, `freeze_money`, `freeze_up_time`, `check_money_2_month`, `check_money_2`, `check_money_2_time`, `tc_qr`, `log_id`, 1 AS type, `branch` FROM `pxs_custom_log_bv_sale` `s` LEFT JOIN `pxs_custom_money_log` `l` ON `s`.`log_id` = `l`.`id` WHERE s.freeze_up_month = '2020-11' union all SELECT `user`, `check_money`, `is_freeze_up`, `freeze_up_month`, `freeze_money`, `freeze_up_time`, `check_money_2_month`, `check_money_2`, `check_money_2_time`, `tc_qr`, `log_id`, 1 AS type, `branch` FROM `pxs_custom_log_bv_sale` `s` LEFT JOIN `pxs_custom_money_log` `l` ON `s`.`log_id` = `l`.`id` WHERE s.check_money_2_month = '2020-11'执行结果再看，这个时候type 是range级别,这样在生产环境上测试，会比原先快很多。解决思路优化的方法是改成union分成多个 sql，走各自的索引。如果可能有重复就用union， 不用union all。or 条件在数据库上很难优化，能在代码里优化逻辑，不至于拖垮数据库。只有在 or 条件下无需索引时（且需要比较的数据量小），才考虑。相同字段or可改成in，如 f_id=1 or f_id=100 -> f_id in (1,100)。上述优化情景都是存储引擎在 InnoDB 情况下，在MyISAM有不同

一些php建议优化技巧 1、比如循环把变量赋值到一个新数组前先定义好初始化。比如<?php //最好养成 先定义一个初始化的数组，要不然会报define的警告的报错， $newArr = []; foreach($list as $key =>$vue){ $newArr = $vue['title']; }2、优先使用单引号$row['id'] 的效率 是$row[id]的7倍；原因是：不加单引号的row的 里面的id【key】去循环常量里面找，发现没有找到然后就去数组里找这个key。所以浪费了时间，加了单引号直接从数组里找。3、用 “1 == $a " 替换  " $a ==1"下面不仔细看的话是没错的，但是$uid是赋值状态，漏了一个等于号！ 所以是永远是true，执行$user['uid'] = 0 这行代码，因为赋值关系，不会报错，很多代码的情况下，一行行的调试就非常浪费时间，如果我们把 0= $uid 变量    这样写做比较 就知道是这行报错了！错误：【parse error:syntax error unexpected '=' in C:xxx.php on line 20】<?php //查错误的地方 $user = array(); if($uid = 0){ $user['uid'] = 0; $user['groupid'] = 0; }else{ $user = $this->getByuid($uid); }4、防御式编程思想 尽管前端做了过滤判断，在我们后台存入数据的数据要做严禁的数据过滤，要不然数据存入乱七八糟的东西，保护程序免遭非法输入数据的危害错误处理技术异常处理隔离程序，使之相互影响小，5、字符编码统一PHP编码 和模板编码 和数据库编码保持一致，才不容易出现编码乱码之类的现象6、尽量使用系统内置函数（优化）比如获取数组的键名 array_key ，不要太着急自己去实现函数效果，先想想或者查查系统是否疯狂有该函数。建议多看手册的 “字符串”、“数组”的内置函数了解。7、屏蔽错误非常低效一些waring 警告的错误提示，是可以用@ 去屏蔽就不显示出来，非常不好的习惯8、时刻备份源代码本地需要一份，ftp需要有，云盘也需要有，以防万一。总之总要的文件不能只有一份。代码不能只有一份，启用编辑器的自动备份，用代码管理工具备份。9、记住有效期的原则不要随便相信网上的那些PHP优化50则之类的东西，记住一切都是有效期的，要善于自己去验证，需要自己验证才能相信它，要报有怀疑的角度去想，因为技术东西更新的很快，比如有些文字发布的技术可能是几年前的东西，可能函数被抛弃了。10、PHP语法糖用strtr代替str_replace<?php $str = 'hi,php'; //数组形式 $res = strtr($str,array('hi'=>'hello','php'=>'jap')); $res2 = str_replace('php','jsp',$str); var_dump($res); $res2 = str_replace(['p,','h','php'],['j','s','p'],$str);//多次循环去替换用strtr好处是：只执行一遍，而str_replace是多次循环替换，但是strtr注意的是最好用数组的形式去写，要不然如果 from 与 to 长度不相等，那么多余的字符部分将被忽略。错误例子： strtr($str,'hi,'hello'); //得出是 he,php数组定义用 [ ] 中括号的写法虽然说array()这样比较兼容老版，从PHP5.4支出 [ ] 写法，这样保存语法简洁。11、PHP用yield实现协程能够节省很多内存，    12、用“...”定义变长参数数组function add(...$num){ return array_sum($num); } add(1,2,3,4,5,6);13、问号运算符$userName = $_GET['user_name'] ? $_GET['user_name'] : '黎明强森'; $userName = $_GET['user_name'] ?: '黎明强森'; $userName = $_GET['user_name'] ?? '黎明强森';//php7语法双问号的 ??写法是只支持PHP7以上的。简洁。14、一句话木马PHP内置的eval函数，把字符串穿作为PHP代码执行，动态执行任何的PHP代码。这功能非常强。<?php //比如执行输出 eval('echo 123;'); //123 eval('echo `dir`'); //就可以把你的目录输出出来，注意``是运算符，相当于调用系统的命令。15、PHP代码的优化尽可能少用循环，能直接输出数据最好的。比如嵌套ifelse，ifelse四五层流程控制这样可读性比较差、维护性又高。（注意：少用不是不用）！16、If else的技巧写法定义一个if初始值if(1 == $orderStatus){ $orderTile = '已发货'; }else{ $orderTile = '未发货'; } return $orderTile ; //改成如下格式-------- $orderTile = '未发货'; if(1 == $orderStatus){ $orderTile = '已发货'; } return $orderTile ;用&&写法（不过这种简写法也是看公司的规范）&&直接结束语句。if的使用技巧之“三元运算符替换也是可以简化三元运算符。