MySQL 慢查询发现以及增删改查SQL优化

来自 Raye Wang 发表于

前言日常工作中，sql语句执行效率经常成为程序的性能瓶颈，如何快速的执行数据库的增删改查则是一个合格的后端程序员必备技能，本文描述了从发现执行慢的sql到如何优化增删改查的sql语句，以及从架构本身优化查询速度的一些方向，本文是我个人的经验之谈，不一定完全正确，如有错误，还望指出 优化前先了解一些基本的MySQL存储结构和索引基础知识在做性能优化之前，首先要对MySQL的数据存储结构要有一定的了解，只有当了解了结构，才能更清楚的知道为什么这样写sql执行会更快，为什么表结构这样设计，在同样的数据行的情况下，查询和写入效率都更高，由于MySQL常用的存储引擎是InnoDB，所以这里介绍的也是基于InnoDB的 InnoDB的存储结构InnoDB存储引擎中，表记录都是根据主键顺序组织存放，这种存储方式的表称为索引组织表，InnoDB的逻辑结构从大到小是表空间->段->区->页->行->列。 所有数据都被逻辑的存放在表空间中，表空间由各个段组成，常见的段：数据段、索引段、回滚段。 区是由连续的页组成的，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InonoDB存储引擎一次从磁盘申请4-5个区。在默认情况下，InnoDB存储引擎的页的大小为16KB，即一个区中应有64个连续的页。从InnoDB1.0.x开始，可以通过参数KEY_BLOCK_…

简单项目一步一步进化到大量数据、大量并发架构优化方案

来自 Raye Wang 发表于

前言   软件的架构都是从小到大的，除非是一开始就知道软件需要承受载的数据量和并发量，并且有成熟且有经验的团队以及资源来支撑，否则其他的项目都是先做基本架构，做出功能后期再根据实际情况做架构重构优化，相信这是大部分公司的处理办法。毕竟软件前期过度优化，不仅浪费大量时间和资源，还有可能适得其反，搞不好做了一个能承受亿级流量（现在大家都喜欢说亿级流量，好像搞得不管那个公司都能有亿级流量一样，但是我也要蹭蹭热闹）的程序，最后撑死也就服务个百万人的程序，但是仅仅是这套程序的基本运行都需要大量服务器来支撑，最后活活耗死公司，毕竟不是所有公司都有大量时间和资源来烧的。所以个人觉得架构不是越复杂越好，而是要符合产品定位，以及合理的后续优化计划。 当然合理的架构不代表就是简单的架构，而是要基于可以使用的时间，数据存储量，以及预估的并发量来设计架构，当然所有的架构设计也需要考虑后期的优化或者重构，毕竟如果完全没有考虑，导致后期根本无法扩展或者重构（无法重构是指重构难度已经太大，接近重做的成本或超过重做成本），到时候只能重做那么也不算是合理的架构 另外，本文只是表达我在工作总结和记录的一些解决方案的思路，没有实际处理方法或者对应的代码，同时后面的步骤可以根据实际情况来处理，不按照顺序或者跳过某些也是可以的，毕竟没有完美和通用的架构，只有更合适的架构 第一步：简单项目（单体项目+单机部署） 最简单也是开发最快捷的项目，所有东西都在同一个项目里面，一个基本的数据库，此时所有代码都在同一个项目里面（也有可能分了模块，但是最终打包成一个jar或者一个war包），部署也是单机部署，…

使用Percona xtrabackup对已在使用的MySQL做主从备份

来自 Raye Wang 发表在 Mysql 发表于

主库配置 首先要配置主库服务器的相关主从备份的配置，比如配置启用binlog日志等，涉及到配置修改如下，修改my.conf文件 [mysqld] ## 设置server_id，一般设置为IP,注意要唯一 server_id=100 ## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步） binlog-ignore-db=mysql ## 开启二进制日志功能，可以随便取，最好有含义（关键就是这里了） log-bin=mysql-bin ## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存 binlog_cache_size=1M ## 主从复制的格式（mixed,statement,row，默认格式是statement） binlog_format=mixed ## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。 expire_logs_days=7 ## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。…

Springcloud+Seata+nacos 分布式事务项目搭建 AT模式

来自 Raye Wang 发表在 分布式事务 发表于

前言本文要求对Springcloud有一定了解，对分布式事务本身有一定认知，如果不了解微服务，建议先看看Spring Cloud的基本认识和使用Spring Cloud的基本教程,再回头学习本文 为什么会出现分布式事务开发过程中，随着项目模块的增加以及分库分表的出现，传统事务已经无法满足业务需求，如分库，由于有多个数据源，而数据库事务又是基于数据库层，所以如果只用数据库原生事务，会导致数据库A成功提交，数据库B回滚，导致数据不一致，又比如多模块下，常见的订单流程，订单服务成功提交订单，调用库存服务扣减库存，由于是链式调用，库存成功扣减，然后回到订单服务时，出现异常，导致订单回滚，但是此时库存却未回滚，也会导致数据不一致，所以这些情况都需要分布式事务来解决这个问题（当然一般开发中，我们常用的做法是能避免就尽量避免，实在避免不了才使用分布式事务，因为分布式事务不管怎么样，性能，一致性，原子性等都会收到影响） 分布式事务目前的几种方案2PC（二阶段提交） 3PC（三阶段提交） TCC(Try - Confirm - Cancel) 最终一致性（消息队列等方式） 最大努力通知（数据不要求强一致）…

Springboot Druid 自定义加密数据库密码

来自 Raye Wang 发表在 JavaEE 发表于

前言开发过程中，配置的数据库密码通常是明文形式，这样首先第一个安全性不好（相对来说），不符合一个开发规范（如项目中不能出现明文账号密码），其实就是当出现特殊需求时，比如要对非运维人员开方服务器部分权限，但是又涉及项目部署的目录时，容易泄漏数据库密码，虽然一般生产环境中，数据库往往放入内网，访问只能通过内网访问，但是不管怎么说账号密码直接让人知道总归不好，甚至有些项目需要部署到客户环境中，但是可能共用一个公共数据库（数据库只向指定服务器开放外网端口或组建内网环境），这样的情况下，如果数据库密码再以明文形式存在，就非常危险了，常见的避免这些方式据我了解有2种 第一种：通过网络获取密码 这种是让密码通过接口获取，当然接口中可以做加密认证，但是如果是把获取地址和认证放在了配置文件中，那么稍微用心也能获取到密码，不过也可以跟第二种方法配合使用 第二种：密码加密 通过对密码加密，使得配置文件中配置的密码为密文，从而提升安全性，当然网络获取密码也可以获取加密后的密码，而国内常用的数据库连接池是Druid，所以此文主要是针对Druid的数据库密码加密方案，Druid默认再带了一个密码加密，所以Druid加密密码也有2种方案 Druid加解密密码的2种方案第一种：自带加密 优点：简单快捷，配置方便，无需改动代码（只需要改动配置文件即可） 缺点：自欺欺人式加密，因为密码和公钥都需要配置到配置文件中，只要别人有了配置文件，看出来了你是用的Druid,…

Java 浮点型（Double,Float）精度丢失解决方案

来自 Raye Wang 发表在 JavaEE 发表于

前言最近公司某小伙子做了一个商城的微信支付相关的接口，其中包含退款，在测试过程中发现部分单据没有退款，微信支付提示退款金额跟支付金额不匹配（大于支付金额），检查数据库和调试过程中，发现商品的单价和手工计算出来的总价是跟订单金额匹配的，实在无法确认问题原因最终bug转向我来排查，于是有了此文 排查由于手工计算出来的金额跟实际支付的金额是能匹配上的，所以一开始我以为是订单已经进行了部分退款，再次全部退款的话肯定会被微信阻止，因为这样的话就会出现超退。 第一步：是否已经部分退款 去微信支付平台排查是否有退款，结果发现并没有退款 由于微信平台没有查询到此单的退款，那么说明不是超退的问题，可能是传入的订单金额有问题，但是大部分单据又可以退款，所以也暂时陷入了僵局，不过为了保险起见，还是去看了一下传参处 第二步：检查传参 检查微信退款方法传参时，看了传入参数，由于微信传参的单位是分，所以看到传参处传入退款总金额代码(int)(100)*商品单价*退货数量（这里只是伪代码，多个商品肯定还要相加计算的）,粗略一看没有问题，仔细一看不对，总价是直接double类型的乘以数量在乘以100再强转为int类型传入的，按照理论上来说是没有问题的，因为我们商品价格都是精确到分的，但是只是理论上，实际上就是Java的double类型有个大坑（不只是Java，JavaScript，c#等很多语言都有这个问题），最终在代码中进行总价计算，发现确定是因为这个原因，导致计算出来的退款金额比应退金额多了1分钱，…

一次抢口罩的负载优化

来自 Raye Wang 发表在 服务器 发表于

前言由于这次令人讨厌的疫情，导致口罩一罩难求，虽然各大厂商都在努力生产，但是毕竟僧多粥少，也幸好疫情快要结束了，也希望早点结束。因为口罩实在难以购买，所以很多地方的口罩采用统一调配，但是如何把口罩发放到大众手里，也成为了一个问题，所以预约购买成了最为安全的购买方式，购买后可以邮寄到家或者根据选择的时间段到店自提，但是由于购买人数过多，也带来了一个新的问题，服务器并发的问题，至少我在二月份参与了很多预约购买口罩的平台，没有一个平台不崩溃的，大多都是10几分钟了，就没有正常请求过服务器一次，不过幸好现在大部分平台已经调整为抽签的形式，而不是之前的先到先得规则，这样相对于先到先得更加公平，不受限于网络和运气，因为我们公司也做了相应的预约平台，不过由于客户要求先到先得原则，所以了本文。不过由于使用我们平台的用户并没有一线城市那么夸张，动不动几百万人同时抢购，而且我们平台的服务器也没有那么好的配置，所以本文的方式只是在几万人同时抢购的情况下测试过，当然其中思路还是可以参考的一下的，毕竟没有万金油，负载优化肯定是要根据实际情况来处理的 要求首先第一个要求是不能超发，第二个要求是尽可能提交成功的失败率不会太高，也就是说不要100人都提交成功了，但是最后只有一个人抢到了口罩，第三就是要求服务器不要崩溃，尽可能的不要出现请求服务器失败的情况。 首先一个要求解决方案：因为考虑到大并发的情况的，而且还做了负载均衡，所以要么采用分布式锁或者队列， 分布式锁分布式锁的优点是可以实时知道抢购结果，坏处就是性能低下，处理不好容易造成服务器压力过大。 分布式锁可以采用zookeeper，当然还有别的分布式中间件或者框架，千万别直接用语言自带的锁，做了负载均衡后，…

基于IDEA热部署更新服务器Tomcat类，服务器Tomcat热更新

来自 Raye Wang 发表在 JavaEE 发表于

前言在开发过程中，如果我们是使用的IDEA，就会知道IDEA有一个热更新的功能，何为热更新？就是在不重启Tomcat的情况下让服务器中的代码变更为最新的。这样既能快速的更新代码，又不用担心Tomcat重启session过期的问题，如同更新jsp一般的方便，当然IDEA里面的热更新还是没有更新jsp那么方便，具体限制本文后面有提（只是我目前了解到的限制）。如果说本地更新还算快捷，那么服务器更新就更加麻烦了，一般我们更新服务器上面的程序有2种情况，一是全量更新，就是打包war包去更新，二是部分更新，就是修改了那个class，就去服务器替换某个class文件，然后重启服务器（jsp文件不算，因为更新jsp是不需要重启Tomcat的，具体原因自己行百度）。这样常规的更新有2个弊端，第一：耗费时间长，不管是文件上传的时间，还是重启服务的时间，尤其是在项目很大启动很慢的时候，有时候只是更新一个简单的东西，却要等待数分钟，而且这数分钟还不是一个人的时间，是所有当时在用这台服务器的人的时间，第二：session失效的问题，服务器一旦重启，所有登录的用户就需要重新登录（当然如果session不是保存在Tomcat中的就另当别论了）。 基于以上种种，如果更新服务器能像我们本地IDEA一样方便，那么会节省我们很多的时间，加快各方人马的开发进度。幸好这种方式是有的，可以实现在IDEA中直接更新服务器代码，此方式是基于Tomcat远程调试的（不清楚远程调试的同学建议百度，也是一个挺好用的功能），而且不需要做其他配置，所以直接进入配置环节 Tomcat远程调试配置因为只要配置好了IDEA的远程调试，…