文章列表

Java设计模式(转)——2.抽象工厂模式

作者：じ☆ve宝贝

## 2.抽象工厂模式（Abstract Factory） 工厂方法模式有一个问题就是，类的创建依赖工厂类，也就是说，如果想要拓展程序，必须对工厂类进行修改，这违背了闭包原则，所以，从设计角度考虑，有一定的问题，如何解决？就用到抽象工厂模式，创建多个工厂类，这样一旦需要增加新的功能，直接增加新的工厂类就可以了，不需要修改之前的代码。因为抽象工厂不太好理解，我们先看看图，然后就和代码，就比较容易理解。 ![抽象工厂模式](/upload/content10.png "抽象工厂模式") 请看例子： ``` public interface Sender { public void Send(); } ``` 两个实现类： ``` public class MailSender implements Sender { @Override public void Send() { System.out.println("this is mailsender!"); } } public class SmsSender implements Sender { @Override public void Send() { System.out.println("this is smssender!"); } } ``` 再提供一个工厂接口： ``` public interface Provider { public Sender produce(); } ``` 两个工厂类： ``` public class SendMailFactory implements Provider { @Override public Sender produce(){ return new MailSender(); } } public class SendSmsFactory implements Provider{ @Override public Sender produce() { return new SmsSender(); } } ``` 测试类： ``` public class Test { public static void main(String[] args) { Provider provider = new SendMailFactory(); Sender sender = provider.produce(); sender.Send(); } } ``` 其实这个模式的好处就是，如果你现在想增加一个功能：发及时信息，则只需做一个实现类，实现Sender接口，同时做一个工厂类，实现Provider接口，就OK了，无需去改动现成的代码。这样做，拓展性较好！

漫画：删去k个数字后的最小值

作者：微信小助手

<section class="xmteditor" style="display:none;" data-tools="新媒体管家" data-label="powered by xmt.cn"></section> <p><span style="color: rgb(127, 127, 127);font-size: 16px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);"><br></span></p> <p><span style="color: rgb(127, 127, 127);font-size: 16px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);">本期封面作者：蒲公英</span></p> <p><br></p> <p><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/13b9105afab892beb5001f07e4442304.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/e31d2a4b8dc27ec00c06151be611894a.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="white-space: normal;text-align: center;"><br></p> <p style="white-space: normal;max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);text-align: center;box-sizing: border-box !important;word-wrap: break-word !important;"><br style="max-width: 100%;box-sizing: border-box !important;word-wrap: break-word !important;"></p> <p style="white-space: normal;max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);text-align: center;box-sizing: border-box !important;word-wrap: break-word !important;"><strong style="max-width: 100%;font-size: 18px;letter-spacing: 0.544px;box-sizing: border-box !important;word-wrap: break-word !important;">—————&nbsp; 第二天&nbsp; —————</strong></p> <p style="white-space: normal;max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;text-align: justify;background-color: rgb(255, 255, 255);box-sizing: border-box !important;word-wrap: break-word !important;"><br style="max-width: 100%;box-sizing: border-box !important;word-wrap: break-word !important;"></p> <p style="white-space: normal;max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;text-align: justify;background-color: rgb(255, 255, 255);box-sizing: border-box !important;word-wrap: break-word !important;"><br style="max-width: 100%;box-sizing: border-box !important;word-wrap: break-word !important;"></p> <p style="white-space: normal;max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);text-align: center;box-sizing: border-box !important;word-wrap: break-word !important;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" data-type="jpeg" data-w="650" src="/upload/9b485ab981a180119008c5809886bc10.jpg" style="box-sizing: border-box !important;word-wrap: break-word !important;width: 650px !important;visibility: visible !important;"></p> <p style="white-space: normal;max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);text-align: center;box-sizing: border-box !important;word-wrap: break-word !important;"><br style="max-width: 100%;box-sizing: border-box !important;word-wrap: break-word !important;"></p> <p style="white-space: normal;max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);text-align: center;box-sizing: border-box !important;word-wrap: break-word !important;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" data-type="jpeg" data-w="650" src="/upload/1eebd17c63bfb70f78f1f43620f75271.jpg" style="box-sizing: border-box !important;word-wrap: break-word !important;width: 650px !important;visibility: visible !important;"></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/31933b3c90e0357fbaca58146dc6db8.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><br></p> <p style="text-align: left;"><span style="font-size: 18px;">什么意思呢？让我们举几个栗子：</span></p> <p style="text-align: center;"><br></p> <p style="text-align: left;"><span style="font-size: 18px;">给定整数<strong>1593212</strong>，删去<strong>3</strong>个数字，新整数的最小情况是<strong>1212</strong></span></p> <p style="text-align: left;"><span style="font-size: 18px;"><strong><br></strong></span></p> <p style="text-align: left;"><img class="" data-ratio="0.43829113924050633" data-s="300,640" src="/upload/cd5887d75fc21d292d6843ab020a0e77.png" data-type="png" data-w="632" style="width: 425px;height: 186px;"></p> <p style="text-align: left;"><br></p> <p style="text-align: left;"><br></p> <p style="text-align: left;"><span style="font-size: 18px;"><span style="font-size: 18px;">给定整数</span><strong style="font-size: 18px;white-space: normal;">30200</strong><span style="font-size: 18px;">，删去<strong>1</strong>个数字，新整数的最小情况是<strong>200</strong></span></span></p> <p style="text-align: left;"><span style="font-size: 18px;"><strong><br></strong></span></p> <p style="text-align: left;"><img class="" data-ratio="0.614190687361419" data-s="300,640" src="/upload/a3e5632ba87048f7fd77cccbc8bffe33.png" data-type="png" data-w="451" style="width: 302px;height: 185px;"></p> <p style="text-align: left;"><span style="font-size: 18px;"></span><br></p> <p style="text-align: left;"><br></p> <p><span style="font-size: 18px;">给定整数</span><strong style="font-size: 18px;white-space: normal;">10</strong><span style="font-size: 18px;">，删去<strong>2</strong>个数字，新整数的最小情况是<strong>0</strong></span></p> <p><span style="font-size: 18px;"><strong><br></strong></span></p> <p style="text-align: left;"><img class="" data-ratio="1.547486033519553" data-s="300,640" src="/upload/9d5d4e46d505b48f6cf66f171a74fa54.png" data-type="png" data-w="179" style="width: 121px;height: 187px;"></p> <p><span style="font-size: 18px;"></span><br></p> <p><br></p> <p><span style="font-size: 18px;">需要注意的是，给定的整数大小可以超过long类型的范围，所以需要用字符串来表示。</span></p> <p><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/1bcb7193cc70af12b5b1c421f335cbe8.jpg" data-type="jpeg" data-w="650" style=""></p> <p><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/e17ef8adcf29b1af29954ffe30bc5c3b.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/1be889a7d9fb316cd84cb03b5fda022.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><br></p> <p><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/a2c3069b987e437ce6156a542552cdab.jpg" data-type="jpeg" data-w="650" style=""></p> <p><br></p> <p><br></p> <p style="text-align: center;"><img class="" data-ratio="0.3501896333754741" data-s="300,640" src="/upload/52f158793be2ce5f5d989d001fa7befc.png" data-type="png" data-w="791" style=""></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/9e4e06e08fc4ff1260eed63f09d15088.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/76b263eb7072c07ec8424793704dcbc4.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/3cd5875a46b1cb8674937710675b983d.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><br></p> <p style="white-space: normal;max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);text-align: center;box-sizing: border-box !important;word-wrap: break-word !important;"><span style="max-width: 100%;font-size: 18px;box-sizing: border-box !important;word-wrap: break-word !important;">————————————</span></p> <p style="white-space: normal;max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;text-align: justify;background-color: rgb(255, 255, 255);box-sizing: border-box !important;word-wrap: break-word !important;"><span style="max-width: 100%;font-size: 18px;box-sizing: border-box !important;word-wrap: break-word !important;"><br style="max-width: 100%;box-sizing: border-box !important;word-wrap: break-word !important;"></span></p> <p style="white-space: normal;max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;text-align: justify;background-color: rgb(255, 255, 255);box-sizing: border-box !important;word-wrap: break-word !important;"><span style="max-width: 100%;font-size: 18px;box-sizing: border-box !important;word-wrap: break-word !important;"><br style="max-width: 100%;box-sizing: border-box !important;word-wrap: break-word !important;"></span></p> <p style="white-space: normal;max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);text-align: center;box-sizing: border-box !important;word-wrap: break-word !important;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" data-type="jpeg" data-w="650" src="/upload/787d1ce39cc0fa73cc57580ca3587edf.jpg" style="box-sizing: border-box !important;word-wrap: break-word !important;width: 650px !important;visibility: visible !important;"></p> <p style="white-space: normal;max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);text-align: center;box-sizing: border-box !important;word-wrap: break-word !important;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" data-type="jpeg" data-w="650" src="/upload/1e56bbb1082a1f2e644097a23f0a9b8a.jpg" style="box-sizing: border-box !important;word-wrap: break-word !important;width: 650px !important;visibility: visible !important;"></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/f2f13214f05ff475d2df4535e29abcd5.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/699f50873792ade808fd7019d91d17fd.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/5b5c409547a50396d151c818158a72ee.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/c6d753d09d19d9b17a804124136200c0.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><br></p> <p style="white-space: normal;"><span style="font-size: 18px;">我们来举一个栗子：</span></p> <p style="white-space: normal;text-align: center;"><br></p> <p style="white-space: normal;"><span style="font-size: 18px;">给定整数 <strong>541270936</strong>，要求删去一个数，让剩下的整数尽可能小。</span></p> <p style="white-space: normal;"><span style="font-size: 18px;"><br></span></p> <p style="white-space: normal;"><span style="font-size: 18px;">此时，无论删除哪一个数字，最后的结果都是从9位整数变成8位整数。既然同样是8位整数，我们显然应该优先把高位的数字降低，这样对新整数的值影响最大。</span></p> <p style="white-space: normal;"><span style="font-size: 18px;"><br></span></p> <p style="white-space: normal;"><span style="font-size: 18px;"><br></span></p> <p style="text-align: center;"><img class="" data-ratio="0.36855036855036855" data-s="300,640" src="/upload/e53c0d109031e4ea3de2b5fd4b6b7023.png" data-type="png" data-w="814" style=""></p> <p style="white-space: normal;"><span style="font-size: 18px;"></span><br></p> <p><span style="color: rgb(127, 127, 127);font-size: 16px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);"><br></span></p> <p><span style="font-size: 18px;">如何把高位的数字降低呢？很简单，我们把<strong>原整数的所有数字从左到右进行比较，如果发现某一位的数字大于它右面的数字，那么在删除该数字后，必然会使得该数位的值降低</strong>，因为右面比它小的数字顶替了它的位置。</span></p> <p><span style="font-size: 18px;"><br></span></p> <p><span style="font-size: 18px;">在咱们这个例子中，数字5右侧的数字4小于5，所以删除数字5，最高位数字降低成了4。</span></p> <p><span style="color: rgb(127, 127, 127);font-size: 16px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);"><br></span></p> <p><span style="color: rgb(127, 127, 127);font-size: 16px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);"><br></span></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/4d9d389e08e5acd2a5df0144474d1eab.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/686693ea19c5c9c4f713bae03d6eaedf.jpg" data-type="jpeg" data-w="650" style=""></p> <p><span style="color: rgb(127, 127, 127);font-size: 16px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);"></span><br></p> <p><br></p> <p style="text-align: left;"><img class="" data-ratio="0.4149377593360996" data-s="300,640" src="/upload/8bae653ec872ff17a2e4ed29021a381.png" data-type="png" data-w="723" style="width: 531px;height: 220px;"></p> <p><span style="color: rgb(127, 127, 127);font-size: 16px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);"><br></span></p> <p><span style="color: rgb(127, 127, 127);font-size: 16px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);"><br></span></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/dc4552c6e43baf4498d7157240794eb3.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/590d249f6c468310aeff375d00060428.jpg" data-type="jpeg" data-w="650" style=""></p> <p><span style="color: rgb(127, 127, 127);font-size: 16px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);"></span><br></p> <p><br></p> <p style="text-align: left;"><img class="" data-ratio="0.47468354430379744" data-s="300,640" src="/upload/7b5030406ad52d1f4ad95a08967733b7.png" data-type="png" data-w="632" style="width: 474px;height: 225px;"></p> <p style="text-align: left;"><br></p> <p><span style="color: rgb(127, 127, 127);font-size: 16px;letter-spacing: 0.544px;background-color: rgb(255, 255, 255);"></span><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/7ff46e3d96df20897bf9274c6ecc11aa.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><br></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/18b6de0d3c34231bd30d7ff7578d82d4.jpg" data-type="jpeg" data-w="650" style=""></p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/38d8c968370bd4f5bc8be7483beec0de.jpg" data-type="jpeg" data-w="650" style=""></p> <p><img class="" data-copyright="0" data-ratio="0.6153846153846154" data-s="300,640" src="/upload/73c038d96f737feb973a258a41d7d886.jpg" data-type="jpeg" data-w="650" style=""></p> <p><br></p> <pre class="prettyprint linenums prettyprinted" style="box-sizing: border-box;margin-top: 0px;margin-bottom: 0px;padding: 8px 0px 6px;background-color: rgb(27, 25, 24);border-radius: 0px;overflow-y: auto;color: rgb(80, 97, 109);font-size: 10px;line-height: 12px;font-family: consolas, menlo, courier, monospace, &quot;Microsoft Yahei&quot;!important;border-width: 1px !important;border-style: solid !important;border-color: rgb(226, 226, 226) !important;"> <ol class="linenums list-paddingleft-2" style="list-style-type: none;"> <li><p><span style="box-sizing: border-box;color: rgb(74, 74, 74);display: block;line-height: 22px;font-size: 14px !important;word-break: inherit !important;"><code style="box-sizing: border-box;margin-left: -20px;display: flex;overflow: initial;line-height: 12px;word-wrap: normal;border-width: 0px;border-style: initial;border-color: initial;font-size: 10px;font-family: inherit !important;white-space: pre !important;"><span class="com" style="box-sizing: border-box;color: rgb(118, 110, 107);line-height: 20px;font-size: 13px !important;white-space: inherit !important;">/**</span></code></span></p></li> <li><p><span style="box-sizing: border-box;color: rgb(74, 74, 74);display: block;line-height: 22px;font-size: 14px !important;word-break: inherit !important;"><code style="box-sizing: border-box;margin-left: -20px;display: flex;overflow: initial;line-height: 12px;word-wrap: normal;border-width: 0px;border-style: initial;border-color: initial;font-size: 10px;font-family: inherit !important;white-space: pre !important;"><span class="com" style="box-sizing: border-box;color: rgb(118, 110, 107);line-height: 20px;font-size: 13px !important;white-space: inherit !important;"> * 删除整数的k个数字，获得删除后的最小值</span></code></span></p></li> <li><p><span style="box-sizing: border-box;color: rgb(74, 74, 74);display: block;line-height: 22px;font-size: 14px !important;word-break: inherit !important;"><code style="box-sizing: border-box;margin-left: -20px;display: flex;overflow: initial;line-height: 12px;word-wrap: normal;border-width: 0px;border-style: initial;border-color: initial;font-size: 10px;font-family: inherit !important;white-space: pre !important;"><span class="com" style="box-sizing: border-box;colo

MySQL不为人知的主键与唯一索引约束

作者：微信小助手

<p><span style="font-size: 14px;letter-spacing: 1px;">今天和大家简单聊聊MySQL的约束<strong><span style="font-size: 14px;letter-spacing: 1px;">主键与唯一索引约束</span></strong></span><span style="letter-spacing: 1px;font-size: 12px;">：</span></p> <p><span style="letter-spacing: 1px;font-size: 12px;">PRIMARY KEY and UNIQUE Index Constraints</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">文章不长，保证有收获。</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">&nbsp;</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">触发约束检测的时机：</span></p> <ul class=" list-paddingleft-2" style="list-style-type: disc;"> <li><p><span style="font-size: 14px;letter-spacing: 1px;">insert</span></p></li> <li><p><span style="font-size: 14px;letter-spacing: 1px;">update</span></p></li> </ul> <p><span style="font-size: 14px;letter-spacing: 1px;">&nbsp;</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">当检测到违反约束时，不同存储引擎的处理动作是不一样的。</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;"><br></span></p> <p><strong><span style="font-size: 14px;letter-spacing: 1px;">如果存储引擎支持事务，SQL会自动</span></strong><span style="color: rgb(255, 76, 0);"><strong><span style="font-size: 14px;letter-spacing: 1px;">回滚</span></strong></span><strong><span style="font-size: 14px;letter-spacing: 1px;">。</span></strong></p> <p><span style="font-size: 14px;letter-spacing: 1px;"><br></span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">例子：</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">create table t1 (</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">id int(10) </span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">primary key</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">)engine=</span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">innodb</span><span style="letter-spacing: 1px;font-size: 12px;">;</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">&nbsp;</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">insert into t1 values(1);</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">insert into t1 values(1);</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">&nbsp;</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">其中第二条insert会因为违反约束，而导致回滚。</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">&nbsp;</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">通常可以使用：</span></p> <p><span style="letter-spacing: 1px;font-size: 12px;">show warnings;</span></p> <p><img class="" data-copyright="0" data-ratio="0.2217391304347826" data-s="300,640" src="/upload/643b6aa4f4eed816cedf3460e419caca.png" data-type="png" data-w="460" style="text-align: center;"></p> <p><span style="font-size: 14px;letter-spacing: 1px;">来查看违反约束后的错误提示。</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">&nbsp;</span></p> <p><strong><span style="font-size: 14px;letter-spacing: 1px;">如果存储引擎不支持事务，SQL的执行会</span></strong><span style="color: rgb(255, 76, 0);"><strong><span style="font-size: 14px;letter-spacing: 1px;">中断</span></strong></span><strong><span style="font-size: 14px;letter-spacing: 1px;">，此时可能会导致后续有符合条件的行不被操作，出现不符合预期的结果。</span></strong></p> <p><span style="font-size: 14px;letter-spacing: 1px;"><br></span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">例子：</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">create table t2 (</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">id int(10) </span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">unique</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">)engine=</span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">MyISAM</span><span style="letter-spacing: 1px;font-size: 12px;">;</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">&nbsp;</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">insert into t2 values(</span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">1</span><span style="letter-spacing: 1px;font-size: 12px;">);</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">insert into t2 values(</span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">5</span><span style="letter-spacing: 1px;font-size: 12px;">);</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">insert into t2 values(</span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">6</span><span style="letter-spacing: 1px;font-size: 12px;">);</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">insert into t2 values(</span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">10</span><span style="letter-spacing: 1px;font-size: 12px;">);</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">&nbsp;</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">update t2 </span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">set id=id+1</span><span style="letter-spacing: 1px;font-size: 12px;">;</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">&nbsp;</span></p> <p><strong><span style="font-size: 14px;letter-spacing: 1px;">update执行后，猜猜会得到什么结果集？</span></strong></p> <p><span style="font-size: 14px;letter-spacing: 1px;">猜想一：2, 6, 7, 11</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">猜想二：1, 5, 6, 10</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">.</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">.</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">.</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">都不对，正确答案是：</span><span style="font-size: 14px;letter-spacing: 1px;color: rgb(255, 76, 0);">2</span><span style="font-size: 14px;letter-spacing: 1px;">, 5, 6, 10</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">&nbsp;</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">第一行id=1，加1后，没有违反unique约束，</span><span style="font-size: 14px;letter-spacing: 1px;color: rgb(255, 76, 0);">执行成功</span><span style="font-size: 14px;letter-spacing: 1px;">；</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">第二行id=5，加1后，由于id=6的记录存在，违反uinique约束，</span><span style="font-size: 14px;letter-spacing: 1px;color: rgb(255, 76, 0);">SQL终止</span><span style="font-size: 14px;letter-spacing: 1px;">，</span><span style="font-size: 14px;letter-spacing: 1px;color: rgb(255, 76, 0);">修改失败</span><span style="font-size: 14px;letter-spacing: 1px;">；</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">第三行id=6，第四行id=10便</span><span style="font-size: 14px;letter-spacing: 1px;color: rgb(255, 76, 0);">不再执行</span><span style="font-size: 14px;letter-spacing: 1px;">；</span></p> <p><span style="color: rgb(0, 82, 255);"><em><span style="font-size: 14px;letter-spacing: 1px;">画外音：这太操蛋了，一个update语句，部分执行成功，部分执行失败。</span></em></span></p> <p><br></p> <p><strong><span style="font-size: 14px;letter-spacing: 1px;">为了避免这种情况出现，请使用InnoDB存储引擎</span></strong><span style="font-size: 14px;letter-spacing: 1px;">，InnoDB在遇到违反约束时，会自动回滚update语句，一行都不会修改成功。</span></p> <p><span style="color: rgb(0, 82, 255);"><em><span style="font-size: 14px;letter-spacing: 1px;">画外音：大家把存储引擎换成InnoDB，把上面的例子再跑一遍，印象更加深刻。</span></em></span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">&nbsp;</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">另外，对于</span><span style="font-size: 14px;letter-spacing: 1px;color: rgb(255, 76, 0);">insert的约束冲突</span><span style="font-size: 14px;letter-spacing: 1px;">，可以使用：</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;color: rgb(255, 76, 0);">insert … on duplicate key</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">指出<strong>在违反主键或唯一索引约束时，需要进行的额外操作</strong>。</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;"><br></span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">例子：</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">create table t3 (</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">id int(10) </span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">unique</span><span style="letter-spacing: 1px;font-size: 12px;">,</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">flag char(10) </span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">default 'true'</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">)engine=</span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">MyISAM</span><span style="letter-spacing: 1px;font-size: 12px;">;</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">&nbsp;</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">insert into t3(id) values(1);</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">insert into t3(id) values(5);</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">insert into t3(id) values(6);</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">insert into t3(id) values(</span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">10</span><span style="letter-spacing: 1px;font-size: 12px;">);</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">&nbsp;</span></p> <p style="line-height: normal;"><span style="letter-spacing: 1px;font-size: 12px;">insert into t3(id) values(</span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">10</span><span style="letter-spacing: 1px;font-size: 12px;">) on duplicate key update flag='false';</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;"><br></span></p> <p><strong><span style="font-size: 14px;letter-spacing: 1px;">insert执行后，猜猜会发生什么？</span></strong></p> <p><img class="" data-copyright="0" data-ratio="0.7245409015025042" data-s="300,640" src="/upload/61d315114d56f4de06e382fc21eebb11.jpg" data-type="jpeg" data-w="599" style="text-align: center;width: 503px;height: 364px;"></p> <p><span style="font-size: 14px;letter-spacing: 1px;">插入id=10的记录，会违反unique约束，此时执行update flag=’false’，于是有一行记录被update了。</span><br><span style="font-size: 14px;letter-spacing: 1px;"></span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">&nbsp;</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">这<strong>相当于执行</strong>：</span></p> <p><span style="letter-spacing: 1px;font-size: 12px;">update t3 set flag='false' where id=10;</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">&nbsp;</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">仔细看，insert的结果返回，提示：</span></p> <p><span style="letter-spacing: 1px;font-size: 12px;">Query OK, </span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);">2</span><span style="letter-spacing: 1px;font-size: 12px;"> rows affected</span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">有意思么？</span></p> <p><span style="color: rgb(0, 82, 255);"><em><span style="font-size: 14px;letter-spacing: 1px;">画外音：本文所有实验，基于MySQL5.6。</span></em></span></p> <p><span style="font-size: 14px;letter-spacing: 1px;">&nbsp;</span></p> <p><strong><span style="font-size: 14px;letter-spacing: 1px;">总结</span></strong><span style="font-size: 14px;letter-spacing: 1px;">，对于</span><span style="font-size: 14px;letter-spacing: 1px;color: rgb(255, 76, 0);">主键与唯一索引约束</span><span style="font-size: 14px;letter-spacing: 1px;">：</span></p> <ul class=" list-paddingleft-2" style="list-style-type: disc;"> <li><p><span style="font-size: 14px;letter-spacing: 1px;">执行insert和update时，会触发约束检查</span></p></li> <li><p><strong><span style="font-size: 14px;letter-spacing: 1px;">InnoDB</span></strong><span style="font-size: 14px;letter-spacing: 1px;">违反约束时，会</span><span style="font-size: 14px;letter-spacing: 1px;color: rgb(255, 76, 0);">回滚对应SQL</span></p></li> <li><p><strong><span style="font-size: 14px;letter-spacing: 1px;">MyISAM</span></strong><span style="font-size: 14px;letter-spacing: 1px;">违反约束时，会</span><span style="font-size: 14px;letter-spacing: 1px;color: rgb(255, 76, 0);">中断对应的SQL</span><span style="font-size: 14px;letter-spacing: 1px;">，可能造成不符合预期的结果集</span></p></li> <li><p><span style="font-size: 14px;letter-spacing: 1px;">可以使用</span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);"> insert … on duplicate key </span><span style="font-size: 14px;letter-spacing: 1px;">来指定触发约束时的动作</span></p></li> <li><p><span style="font-size: 14px;letter-spacing: 1px;">通常使用</span><span style="letter-spacing: 1px;font-size: 12px;color: rgb(255, 76, 0);"> show warnings; </span><span style="font-size: 14px;letter-spacing: 1px;">来查看与调试违反约束的ERROR</span></p></li> </ul> <p><span style="font-size: 14px;letter-spacing: 1px;"><br>互联网大数据量高并发量业务，<strong>为了大家的身心健康，请使用InnoDB</strong>。</span></p> <p style="max-width: 100%;box-sizing: border-box;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);text-align: center;line-height: normal;word-wrap: break-word !important;"><span style="max-width: 100%;box-sizing: border-box;word-wrap: break-word;letter-spacing: normal;"><strong style="max-width: 100%;box-sizing: border-box;word-wrap: break-word;"><span style="max-width: 100%;box-sizing: border-box;word-wrap: break-word;font-size: 12px;"><img width="auto" data-ratio="1" data-type="jpeg" data-s="300,640" data-w="250" class="" src="/upload/70c6338917b0840a1d6c89c07c078365.jpg" style="max-width: 677px;box-sizing: border-box;word-wrap: break-word;visibility: visible !important;width: 130px !important;"></span></strong></span></p> <p style="max-width: 100%;box-sizing: border-box;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);text-align: center;line-height: normal;word-wrap: break-word !important;"><strong style="max-width: 100%;box-sizing: border-box;word-wrap: break-word;"><span style="max-width: 100%;box-sizing: border-box;word-wrap: break-word;font-size: 12px;letter-spacing: 1px;">架构师之路</span></strong><span style="max-width: 100%;box-sizing: border-box;word-wrap: break-word;font-size: 12px;letter-spacing: 1px;">-分享</span><span style="max-width: 100%;box-sizing: border-box;word-wrap: break-word;color: rgb(255, 76, 0);font-size: 12px;letter-spacing: 1px;">可落地</span><span style="max-width: 100%;box-sizing: border-box;word-wrap: break-word;font-size: 12px;letter-spacing: 1px;">的架构文章</span></p> <p style="max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;text-align: justify;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;word-wrap: break-word !important;"><span style="max-width: 100%;color: rgb(0, 0, 0);font-size: 14px;letter-spacing: 1px;box-sizing: border-box !important;word-wrap: break-word !important;"><br style="max-width: 100%;box-sizing: border-box !important;word-wrap: break-word !important;"></span></p> <p style="max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;text-align: justify;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;word-wrap: break-word !important;"><span style="max-width: 100%;color: rgb(0, 0, 0);font-size: 14px;letter-spacing: 1px;box-sizing: border-box !important;word-wrap: break-word !important;">相关推荐：</span><br style="max-width: 100%;box-sizing: border-box !important;word-wrap: break-word !important;"></p> <p style="max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;text-align: justify;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;word-wrap: break-word !important;"><span style="max-width: 100%;color: rgb(0, 0, 0);font-size: 14px;letter-spacing: 1px;box-sizing: border-box !important;word-wrap: break-word !important;">《<a href="http://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&amp;mid=2651961428&amp;idx=1&amp;sn=31a9eb967941d888fbd4bb2112e9602b&amp;chksm=bd2d0d888a5a849e7ebaa7756a8bc1b3d4e2f493f3a76383fc80f7e9ce7657e4ed2f6c01777d&amp;scene=21#wechat_redirect" target="_blank">互联网业务，为什么一定要使用InnoDB？</a>》</span></p> <p style="max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;text-align: justify;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;word-wrap: break-word !important;"><span style="max-width: 100%;color: rgb(0, 0, 0);letter-spacing: 1px;font-size: 14px;box-sizing: border-box !important;word-wrap: break-word !important;"><span yahei="yahei" sans="sans" helvetica="helvetica" px="px" none="none" normal="normal" gb="gb" neue="neue" left="left" style="max-width: 100%;float: none;background-color: transparent;box-sizing: border-box !important;word-wrap: break-word !important;">《</span><a href="http://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&amp;mid=2651961444&amp;idx=1&amp;sn=830a93eb74ca484cbcedb06e485f611e&amp;chksm=bd2d0db88a5a84ae5865cd05f8c7899153d16ec7e7976f06033f4fbfbecc2fdee6e8b89bb17b&amp;scene=21#wechat_redirect" target="_blank" style="color: rgb(87, 107, 149);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;background-color: transparent;box-sizing: border-box !important;word-wrap: break-word !important;">InnoDB，为什么并发如此之高？</a><span yahei="yahei" sans="sans" helvetica="helvetica" px="px" none="none" normal="normal" gb="gb" neue="neue" left="left" style="max-width: 100%;float: none;background-color: transparent;box-sizing: border-box !important;word-wrap: break-word !important;">》</span></span></p> <p style="max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;text-align: justify;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;word-wrap: break-word !important;"><span style="max-width: 100%;font-size: 14px;letter-spacing: 1px;box-sizing: border-box !important;word-wrap: break-word !important;"><span yahei="yahei" sans="sans" helvetica="helvetica" px="px" none="none" normal="normal" gb="gb" neue="neue" left="left" style="max-width: 100%;display: inline;float: none;box-sizing: border-box !important;word-wrap: break-word !important;">《</span><a href="http://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&amp;mid=2651961494&amp;idx=1&amp;sn=34f1874c1e36c2bc8ab9f74af6546ec5&amp;chksm=bd2d0d4a8a5a845c566006efce0831e610604a43279aab03e0a6dde9422b63944e908fcc6c05&amp;scene=21#wechat_redirect" target="_blank" style="color: rgb(87, 107, 149);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box !important;word-wrap: break-word !important;">聚集索引与普通索引究竟有什么差别？</a><span yahei="yahei" sans="sans" helvetica="helvetica" px="px" none="none" normal="normal" gb="gb" neue="neue" left="left" style="max-width: 100%;display: inline;float: none;box-sizing: border-box !important;word-wrap: break-word !important;">》</span></span></p> <p style="max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;text-align: justify;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;word-wrap: break-word !important;"><span style="max-width: 100%;letter-spacing: 1px;font-size: 14px;box-sizing: border-box !important;word-wrap: break-word !important;"><span yahei="yahei" sans="sans" helvetica="helvetica" px="px" none="none" normal="normal" gb="gb" neue="neue" left="left" style="max-width: 100%;display: inline;float: none;box-sizing: border-box !important;word-wrap: break-word !important;">《</span><a href="http://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&amp;mid=2651961486&amp;idx=1&amp;sn=b319a87f87797d5d662ab4715666657f&amp;chksm=bd2d0d528a5a84446fb88da7590e6d4e5ad06cfebb5cb57a83cf75056007ba29515c85b9a24c&amp;scene=21#wechat_redirect" target="_blank" style="color: rgb(87, 107, 149);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box !important;word-wrap: break-word !important;">索引，底层是如何实现的？</a><span yahei="yahei" sans="sans" helvetica="helvetica" px="px" none="none" normal="normal" gb="gb" neue="neue" left="left" style="max-width: 100%;display: inline;float: none;box-sizing: border-box !important;word-wrap: break-word !important;">》</span></span></p> <p style="max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;text-align: justify;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;word-wrap: break-word !important;"><span style="max-width: 100%;letter-spacing: 1px;font-size: 14px;box-sizing: border-box !important;word-wrap: break-word !important;"><br style="max-width: 100%;box-sizing: border-box !important;word-wrap: break-word !important;"></span></p> <p style="max-width: 100%;min-height: 1em;color: rgb(51, 51, 51);font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif;font-size: 17px;letter-spacing: 0.544px;text-align: justify;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;word-wrap: break-word !important;"><span style="max-width: 100%;letter-spacing: 1px;font-size: 14px;box-sizing: border-box !important;word-wrap: break-word !important;">了解了几个坑，也是好的，求</span><span style="max-width: 100%;letter-spacing: 1px;font-size: 14px;color: rgb(255, 76, 0);box-sizing: border-box !important;word-wrap: break-word !important;">转</span><span style="max-width: 100%;letter-spacing: 1px;font-size: 14px;box-sizing: border-box !important;word-wrap: break-word !important;">。</span></p>

MySQL UNIX_TIMESTAMP 1970 之前结果为0

作者：じ☆ve宝贝

> 在使用MySQL的函数UNIX_TIMESTAMP转换为秒值的时候出现如果时间为1970年以前的数据结果会为0 **解决方案（使用TIMESTAMPDIFF函数）：** ``` SELECT TIMESTAMPDIFF(second, FROM_UNIXTIME(0), ''1960-01-01 01:02:03'' ); SELECT TIMESTAMPDIFF(second, FROM_UNIXTIME(0), ''1990-01-01 00:00:00'' ); ``` 结果为： -315644277 631123200

我是这样手写Spring的，麻雀虽小五脏俱全

作者：微信小助手

<p>人见人爱的Spring已然不仅仅只是一个框架了。如今，Spring已然成为了一个生态。但深入了解Spring的却寥寥无几。这里，我带大家一起来看看，我是如何手写Spring的。我将结合对Spring十多年的研究经验，用不到400行代码来描述SpringIOC、DI、MVC的精华设计思想，并保证基本功能完整。</p> <p>首先，我们先来介绍一下Spring的三个阶段，配置阶段、初始化阶段和运行阶段（如图）：</p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.7452054794520548" data-s="300,640" src="/upload/a0105cede865dd4c31a4a315eb8470d8.png" data-type="png" data-w="1095" style=""></p> <p>配置阶段：主要是完成application.xml配置和Annotation配置。<br></p> <p>初始化阶段：主要是加载并解析配置信息，然后，初始化IOC容器，完成容器的DI操作，已经完成HandlerMapping的初始化。</p> <p>运行阶段：主要是完成Spring容器启动以后，完成用户请求的内部调度，并返回响应结果。</p> <p>先来看看我们的项目结构(如下图)</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="2.4693877551020407" src="/upload/561e8ef9baf4a34db5e175c27298c8af.png" data-type="png" data-w="343" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="343"> </ignore_js_op></p> <p>一、配置阶段</p> <p>我采用的是maven管理项目。先来看pom.xml文件中的配置，我只引用了servlet-api的依赖。</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.7997630331753555" src="/upload/423ca5cfd3e0e9ced0b54811acf15388.png" data-type="png" data-w="844" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>然后，创建GPDispatcherServlet类并继承HttpServlet，重写init()、doGet()和doPost()方法。</p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.5756698044895003" data-s="300,640" src="/upload/e2bc8bc6fab7785b684db808a6dbd3ba.png" data-type="png" data-w="1381" style=""></p> <p>在web.xml文件中配置以下信息：</p> <p style="text-align: center;"><img class="" data-copyright="0" data-ratio="0.7203791469194313" data-s="300,640" src="/upload/a6de40917d0ba861d31c39968115f9f4.png" data-type="png" data-w="1266" style=""></p> <p>在&lt;init-param&gt;中，我们配置了一个初始化加载的Spring主配置文件路径，在原生框架中，我们应该配置的是classpath:application.xml。在这里，我们为了简化操作，用properties文件代替xml文件。以下是properties文件中的内容：<br></p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.23738317757009345" src="/upload/11ae943bc4494b68b21bf9702c001737.png" data-type="png" data-w="535" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="535"> </ignore_js_op></p> <p>接下来，我们要配置注解。现在，我们不使用Spring的一针一线，所有注解全部自己手写。</p> <p>创建GPController注解：</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.4849921011058452" src="/upload/92953ff7015970dbf327639378a4f8db.png" data-type="png" data-w="633" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>创建GPRequestMapping注解：</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.4739010989010989" src="/upload/48b9ebbd596b8bcb7f56a87fabfcefa9.png" data-type="png" data-w="728" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>创建GPService注解：</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.43544303797468353" src="/upload/21a37a7a0a65a078d78fbba1acc126c4.png" data-type="png" data-w="790" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>创建GPAutowired注解：</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.43467336683417085" src="/upload/cf5ac4845b7d68b2bbdd97deffcfd284.png" data-type="png" data-w="796" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>创建GPRequestParam注释：</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.4398496240601504" src="/upload/7a2fad3749389a4136b276d9260c006.png" data-type="png" data-w="798" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>使用自定义注解进行配置：</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.923931623931624" src="/upload/122bd62422107d7ee29dc8af1e33171a.png" data-type="png" data-w="1170" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>到此，我们把配置阶段的代码全部手写完成。</p> <p>二、初始化阶段</p> <p>先在GPDispatcherServlet中声明几个成员变量：</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.5481139337952271" src="/upload/bf52a4d5c791bfdbf680607c0fb13442.png" data-type="png" data-w="1299" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>当Servlet容器启动时，会调用GPDispatcherServlet的init()方法，从init方法的参数中，我们可以拿到主配置文件的路径，从能够读取到配置文件中的信息。前面我们已经介绍了Spring的三个阶段，现在来完成初始化阶段的代码。在init()方法中，定义好执行步骤，如下：</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.7250470809792844" src="/upload/c8046a0197930166cb8fbe5a974cda2b.png" data-type="png" data-w="1062" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>doLoadConfig()方法的实现，将文件读取到Properties对象中：</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.47206005004170143" src="/upload/f960d302bf53f8a3d591e232f840308e.png" data-type="png" data-w="1199" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>doScanner()方法，递归扫描出所有的Class文件</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.2948717948717949" src="/upload/d81875be7f9e497d67da584a08a3a2bc.png" data-type="png" data-w="1560" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>doInstance()方法，初始化所有相关的类，并放入到IOC容器之中。IOC容器的key默认是类名首字母小写，如果是自己设置类名，则优先使用自定义的。因此，要先写一个针对类名首字母处理的工具方法。</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.2904" src="/upload/9f769022ef96611a83949b6e7d813fe.png" data-type="png" data-w="1250" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>然后，再处理相关的类。</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.7362177250523377" src="/upload/528d1ecd990357136ce1b4e1d730e214.png" data-type="png" data-w="1433" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>doAutowired()方法，将初始化到IOC容器中的类，需要赋值的字段进行赋值</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.6357894736842106" src="/upload/8922a7b78a11160c3ed88de57445e93e.png" data-type="png" data-w="1425" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>initHandlerMapping()方法，将GPRequestMapping中配置的信息和Method进行关联，并保存这些关系。</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.7292971468336813" src="/upload/acc6a834c2a26ad7db2a2e357fc4f7b1.png" data-type="png" data-w="1437" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>到此，初始化阶段的所有代码全部写完。</p> <p>三、运行阶段</p> <p>来到运行阶段，当用户发送请求被Servlet接受时，都会统一调用doPost方法，我先在doPost方法中再调用doDispach()方法，代码如下：</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.25630252100840334" src="/upload/2ff9ebc8c60a762c61a66fdf31290fd4.png" data-type="png" data-w="1428" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>doDispatch()方法是这样写的：</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="1.1699029126213591" src="/upload/df53a024c84d061ab4b910c2eb43372a.png" data-type="png" data-w="1442" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>到此，我们完成了一个mini版本的Spring，麻雀虽小，五脏俱全。我们把服务发布到web容器中，然后，在浏览器输入：http://localhost:8080/demo/query.json?name=Tom，就会得到下面的结果：</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.32667450058754405" src="/upload/85236814b3ac0e44c5032bd3356f558f.png" data-type="png" data-w="851" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>当然，真正的Spring要复杂很多，但核心设计思路基本如此。例如：Spring中真正的HandlerMapping是这样的：</p> <p> <ignore_js_op style="word-wrap: break-word;"> <img class="zoom" data-ratio="0.10071942446043165" src="/upload/4ac1b3b6c6e038f44443447e70528809.png" data-type="png" data-w="973" style="word-wrap: break-word;object-fit: cover;cursor: pointer;" width="600"> </ignore_js_op></p> <p>文章转自：http://bbs.gupaoedu.com/forum.php?mod=viewthread&amp;tid=572</p> <section class="_135editor" data-tools="135编辑器" data-id="85996" style="border-width: 0px;border-style: none;border-color: initial;box-sizing: border-box;"> <section style="text-align:center;"> <section style="background-color: rgb(244, 244, 244);box-sizing: border-box;padding: 40px;"> <section style="margin-bottom: 10px;"> <span style="font-size: 18px;"><span style="color: #030303;font-weight: 600;">Java高级架构&nbsp;</span><span style="color: #3f3f3f;">∣</span></span> <span style="color:#030303;" class="135brush" data-brushtype="text">干货｜学习</span> </section> <section> <section style="display:inline-block;vertical-align: top;margin-top: 14px;width: 50%;text-align: right;" data-width="50%"> <img border="0" class="" data-ratio="0.993993993993994" src="/upload/b65b49bf5b4b93db5800dd70c5faae54.png" data-type="png" data-w="666" height="auto" opacity="" style="width: 120px;" title="" width="120"> </section> <section style="display:inline-block;box-sizing:border-box;padding-left: 10px;width: 50%;text-align: left;" data-width="50%"> <img class="" data-ratio="1.39" src="/upload/9d2924f9c5be1305d2d10457a2988b10.png" data-type="png" data-w="100" style="width: 100px;"> </section> </section> <section style="margin-top: 6px;"> <span style="color: #0c0c0c;" class="135brush" data-brushtype="text">长按，识别二维码，加关注</span> </section> </section> </section> </section> <section class="_135editor" data-id="image-96175" style="border-width: 0px;border-style: none;border-color: initial;box-sizing: border-box;"> <section class="_135editor" data-tools="135编辑器" data-id="92353" style="border-width: 0px;border-style: none;border-color: initial;box-sizing: border-box;"> <section data-role="paragraph" class="_135editor" style="border-width: 0px;border-style: none;border-color: initial;box-sizing: border-box;"> <section style="padding: 10px;box-sizing: border-box;"> <section style="width: 130px;height: 30px;background-image: url(&quot;https://mmbiz.qpic.cn/mmbiz_png/YUYc62VIvE3KGibuNJ52wj525MFHFsFZE3iaCTrWkhxmHECicKc8S6umV7jFvWFNgg2EibhJyv4IfW3d6PthaBOicxg/640?wx_fmt=png&quot;);background-size: 100%;background-repeat: no-repeat;background-position: center center;margin-left: 25px;transform: translateZ(10px);"> <section style="width: 118px;height: 30px;text-align: center;line-height: 30px;color: #fefefe;letter-spacing: 3px;"> <p class="135brush" data-brushtype="text"><strong>温馨提示</strong></p> </section> </section> <section style="box-shadow: rgb(204, 204, 204) 0px 0px 7px;border-radius: 15px;color: rgb(51, 51, 51);padding: 15px;margin-top: -20px;box-sizing: border-box;"> <section style="line-height: 25px;margin-top: 15px;font-size: 14px;" class="135brush"> <p>如果你喜欢本文，请分享到朋友圈，想要获得更多信息，请关注我。关注本文说说你的看法吧，下方评论哦</p> </section> </section> </section> </section> </section> </section> <p><br></p>

并发扣款一致性优化，CAS下ABA问题，这个话题还没聊完！！！

作者：微信小助手

<section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">上一篇答星球水友提问，《</span> <a href="http://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&amp;mid=2651962738&amp;idx=1&amp;sn=d2d91a380bad06af9f7b9f7a80db26b3&amp;chksm=bd2d08ae8a5a81b8a7f044af52c5e6e77ec3df2bb4a9c91cd450c3fd932e8dade56afb09f784&amp;scene=21#wechat_redirect" target="_blank" data-linktype="2" data-itemshowtype="0"><span style="letter-spacing: 1px;font-size: 15px;">并发扣款，如何保证数据的一致性？</span></a> <span style="letter-spacing: 1px;font-size: 15px;">》中提到：用CAS乐观锁，可以在尽量不影响吞吐量的情况下，保证数据的一致性。</span> </section> <p><br></p> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">大家有非常多的留言，大概有这么几类：</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">（1）<strong>是否存在ABA问题？</strong></span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">（2）为什么不能用：</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 12px;"><em>UPDATE t_yue SET money=money-$diff AND <strong>money&gt;=$diff</strong>;</em></span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">（3）能否借助<strong>redis事务</strong>来扣减余额；</span> </section> <section style="line-height: 1.75em;"> <span style="color: rgb(0, 82, 255);letter-spacing: 1px;"><em><span style="color: rgb(0, 82, 255);letter-spacing: 1px;font-size: 15px;">画外音：</span></em><em><span style="color: rgb(0, 82, 255);letter-spacing: 1px;font-size: 15px;"></span></em><em><span style="color: rgb(0, 82, 255);letter-spacing: 1px;font-size: 15px;">请务必阅读前序文章：</span></em><em><span style="color: rgb(0, 82, 255);letter-spacing: 1px;font-size: 15px;"></span></em></span> <span style="letter-spacing: 1px;"><span style="color: rgb(0, 82, 255);letter-spacing: 1px;"><em><span style="color: rgb(0, 82, 255);letter-spacing: 1px;font-size: 15px;">《</span></em></span><span style="color: rgb(51, 51, 51);letter-spacing: 1px;background-color: transparent;"></span></span> <a style="background-color: transparent;color: rgb(87, 107, 149);" href="http://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&amp;mid=2651962738&amp;idx=1&amp;sn=d2d91a380bad06af9f7b9f7a80db26b3&amp;chksm=bd2d08ae8a5a81b8a7f044af52c5e6e77ec3df2bb4a9c91cd450c3fd932e8dade56afb09f784&amp;scene=21#wechat_redirect" target="_blank" data-linktype="2" data-itemshowtype="0"><span style="margin: 0px;padding: 0px;color: rgb(87, 107, 149);letter-spacing: 1px;font-size: 15px;background-color: transparent;">并发扣款，如何保证数据的一致性？</span></a> <span style="color: rgb(0, 82, 255);letter-spacing: 1px;"><em><span style="color: rgb(0, 82, 255);letter-spacing: 1px;font-size: 15px;">》。</span></em><em><span style="color: rgb(0, 82, 255);letter-spacing: 1px;font-size: 15px;"></span></em></span> </section> <p><br></p> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">问题比较多，今天先聊第一个问题，ABA。</span> <br> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;"><strong><span style="letter-spacing: 1px;font-size: 15px;">什么是ABA问题？</span></strong><strong><span style="letter-spacing: 1px;font-size: 15px;"></span></strong></span> </section> <section style="line-height: 1.75em;"> <br> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">CAS乐观锁机制确实能够提升吞吐，并保证一致性，但在极端情况下可能会出现ABA问题。</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;"><br></span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">考虑如下操作：</span> </section> <ul class=" list-paddingleft-2" style="list-style-type: disc;"> <li> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">并发1（上）：获取出数据的</span> <span style="letter-spacing: 1px;"><span style="color: rgb(255, 76, 0);letter-spacing: 1px;font-size: 15px;">初始值是A</span><span style="letter-spacing: 1px;font-size: 15px;">，后续计划实施CAS乐观锁，期望数据仍是A的时候，修改才能成功</span></span> </section></li> <li> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">并发2：将数据</span> <span style="color: rgb(255, 76, 0);letter-spacing: 1px;font-size: 15px;">修改成B</span> </section></li> <li> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">并发3：将数据</span> <span style="color: rgb(255, 76, 0);letter-spacing: 1px;font-size: 15px;">修改回A</span> </section></li> <li> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">并发1（下）：CAS乐观锁，检测</span> <span style="color: rgb(255, 76, 0);letter-spacing: 1px;font-size: 15px;">发现初始值还是A，进行数据修改</span> </section></li> </ul> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;"><span style="letter-spacing: 1px;font-size: 15px;">上述并发环境下，并发1在修改数据时，虽然还是A，但已经不是初始条件的A了，中间发生了A变B，B又变A的变化，</span><span style="color: rgb(255, 76, 0);letter-spacing: 1px;font-size: 15px;">此A已经非彼A，数据却成功修改，可能导致错误</span><span style="letter-spacing: 1px;font-size: 15px;">，这就是CAS引发的所谓的ABA问题。</span></span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;"><strong><span style="letter-spacing: 1px;font-size: 15px;">余额操作，出现ABA问题并不会对业务产生影响</span></strong><span style="letter-spacing: 1px;font-size: 15px;">，因为对于“余额”属性来说，前一个A为100余额，与后一个A为100余额，本质是相同的。</span></span> </section> <p><br></p> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">但其他场景未必是这样，举一个堆栈操作的例子：</span> </section> <section style="line-height: 1.75em;"> <img data-ratio="0.9132947976878613" data-type="png" data-w="173" src="/upload/15e8eb5cef0cd7cb845ff01b3bf6262.png" data-s="300,640"> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">并发1（上）：读取栈顶的元素为“A1”</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <img data-ratio="0.8011695906432749" data-type="png" data-w="171" src="/upload/f361e6399fe1656f9d6f0b31a27731f3.png" data-s="300,640"> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">并发2：进行了2次出栈</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <img data-ratio="0.6708074534161491" data-type="png" data-w="161" src="/upload/ddcd323e36dc0674f16499c74ba3703f.png" data-s="300,640"> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">并发3：又进行了1次出栈</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <img data-ratio="0.7379310344827587" data-type="png" data-w="145" src="/upload/52ed2fe68a4ac0513a5a5410475201f0.png" data-s="300,640"> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">并发1（下）：实施CAS乐观锁，发现栈顶还是“A1”，于是修改为A2</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <img data-ratio="0.6379310344827587" data-type="png" data-w="174" src="/upload/c2fd1f5d333f54e8052804285c04b9de.png" data-s="300,640"> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">此时会出现系统错误，因为此“A1”非彼“A1”</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;"><strong><span style="letter-spacing: 1px;font-size: 15px;">ABA问题可以怎么优化？</span></strong><strong><span style="letter-spacing: 1px;font-size: 15px;"></span></strong></span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">ABA问题导致的原因，是CAS过程中只简单进行了“值”的校验，再有些情况下，“值”相同不会引入错误的业务逻辑（例如余额），有些情况下，“值”虽然相同，却已经不是原来的数据了（例如堆栈）。</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;"><span style="letter-spacing: 1px;font-size: 15px;">因此，CAS不能只比对“值”，</span><span style="color: rgb(255, 76, 0);letter-spacing: 1px;font-size: 15px;">还必须确保是原来的数据</span><span style="letter-spacing: 1px;font-size: 15px;">，才能修改成功。</span></span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;"><strong><span style="letter-spacing: 1px;font-size: 15px;">常见的实践</span></strong><span style="letter-spacing: 1px;font-size: 15px;">是，</span><span style="color: rgb(255, 76, 0);letter-spacing: 1px;font-size: 15px;">将“值”比对，升级为“版本号”的比对</span><span style="letter-spacing: 1px;font-size: 15px;">，一个数据一个版本，<strong>版本变化，即使值相同，也不应该修改成功</strong>。</span></span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">余额并发读写例子，引入版本号的具体实践如下：</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">（1）余额表要升级。</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;"><em><span style="letter-spacing: 1px;font-size: 12px;">t_yue(uid, money)</span></em><em><span style="letter-spacing: 1px;font-size: 12px;"></span></em></span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">升级为：</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;"><span style="letter-spacing: 1px;font-size: 12px;"><em>t_yue(uid, money, </em></span><span style="color: rgb(255, 76, 0);letter-spacing: 1px;font-size: 12px;"><em><span style="letter-spacing: 1px;font-size: 12px;">version</span></em></span><span style="letter-spacing: 1px;font-size: 12px;"><em>)</em></span></span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">（2）查询余额时，同时查询版本号。</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 12px;"><em>SELECT money FROM t_yue WHERE sid=$sid</em></span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">升级为：</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;"><em><span style="letter-spacing: 1px;font-size: 12px;">SELECT money,</span></em><span style="color: rgb(255, 76, 0);letter-spacing: 1px;"><em><span style="letter-spacing: 1px;font-size: 12px;">version </span></em></span><em><span style="letter-spacing: 1px;font-size: 12px;">FROM t_yue WHERE sid=$sid</span></em><em><span style="letter-spacing: 1px;font-size: 12px;"></span></em></span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">假设有并发操作，都会将版本号查询出来</span> <mpchecktext> <span style="letter-spacing: 1px;font-size: 15px;">。</span> </mpchecktext> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">（3）设置余额时，必须版本号相同，并且版本号要修改。</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">旧版本“值”比对：</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 12px;"><em>UPDATE t_yue SET money=38 WHERE uid=$uid AND money=100</em></span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">升级为“版本号”比对：</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;"><span style="letter-spacing: 1px;font-size: 12px;"><em>UPDATE t_yue SET money=38, </em></span><span style="color: rgb(255, 76, 0);letter-spacing: 1px;font-size: 12px;"><em><span style="letter-spacing: 1px;font-size: 12px;">version=$version_new</span></em></span><span style="letter-spacing: 1px;font-size: 12px;"><em> WHERE uid=$uid AND </em></span><span style="color: rgb(255, 76, 0);letter-spacing: 1px;font-size: 12px;"><em><span style="letter-spacing: 1px;font-size: 12px;">version=$version_old</span></em><em><span style="letter-spacing: 1px;font-size: 12px;"></span></em></span></span> </section> <section style="line-height: 1.75em;"> <br> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">此时假设有并发操作，首先操作的请求会修改版本号，并发操作会执行失败。</span> </section> <section style="line-height: 1.75em;"> <span style="color: rgb(0, 82, 255);letter-spacing: 1px;"><em><span style="color: rgb(0, 82, 255);letter-spacing: 1px;font-size: 15px;">画外音：</span></em><em><span style="color: rgb(0, 82, 255);letter-spacing: 1px;font-size: 15px;"></span></em><em><span style="color: rgb(0, 82, 255);letter-spacing: 1px;font-size: 15px;">version通用，本例是强行用version举例而已，实际上本例可以用余额“值”比对。</span></em><em><span style="color: rgb(0, 82, 255);letter-spacing: 1px;font-size: 15px;"></span></em></span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">&nbsp;</span> </section> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">总结</span> </section> <ul class=" list-paddingleft-2" style="list-style-type: disc;"> <li> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">select&amp;set业务场景，在并发时会出现一致性问题</span> </section></li> <li> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">基于“值”的CAS乐观锁，可能导致ABA问题</span> </section></li> <li> <section style="line-height: 1.75em;"> <span style="letter-spacing: 1px;font-size: 15px;">CAS乐观锁，必须保证修改时的“此数据”就是“彼数据”，应该由“值”比对，优化为“版本号”比对</span> </section></li> </ul> <p><span style="text-align: justify;color: rgb(51, 51, 51);text-transform: none;text-indent: 0px;letter-spacing: 1px;font-family: &quot;mp-quote&quot;,-apple-system-font,BlinkMacSystemFont,&quot;Helvetica Neue&quot;,&quot;PingFang SC&quot;,&quot;Hiragino Sans GB&quot;,&quot;Microsoft YaHei UI&quot;,&quot;Microsoft YaHei&quot;,Arial,sans-serif;font-size: 15px;font-style: normal;font-variant: normal;font-weight: 400;text-decoration: none;word-spacing: 0px;display: inline !important;orphans: 2;float: none;-webkit-text-stroke-width: 0px;background-color: transparent;"><br></span></p> <section style="margin: 0px;padding: 0px;text-align: justify;color: rgb(51, 51, 51);text-transform: none;line-height: 1.75em;text-indent: 0px;letter-spacing: 0.54px;clear: both;font-size: 17px;font-style: normal;font-variant: normal;font-weight: 400;text-decoration: none;word-spacing: 0px;white-space: normal;word-wrap: break-word;min-height: 17px;max-width: 677px;box-sizing: border-box;orphans: 2;-webkit-text-stroke-width: 0px;background-color: rgb(255, 255, 255);"> <span style="margin: 0px;padding: 0px;letter-spacing: 1px;word-wrap: break-word;max-width: 100%;box-sizing: border-box;"><span style="margin: 0px;padding: 0px;color: rgb(255, 76, 0);letter-spacing: 1px;font-size: 15px;word-wrap: break-word;max-width: 100%;box-sizing: border-box;">思路</span><span style="margin: 0px;padding: 0px;letter-spacing: 1px;font-size: 15px;word-wrap: break-word;max-width: 100%;box-sizing: border-box;">比结论重要。</span></span> <span style="margin: 0px;padding: 0px;letter-spacing: 1px;font-size: 15px;word-wrap: break-word;max-width: 100%;box-sizing: border-box;"></span> </section> <section style="text-align: center;color: rgb(51, 51, 51);line-height: normal;clear: both;box-sizing: border-box;background-color: rgb(255, 255, 255);"> <span style="margin: 0px;padding: 0px;letter-spacing: 1px;font-size: 15px;word-wrap: break-word;max-width: 100%;box-sizing: border-box;"><strong style="box-sizing: border-box;margin-bottom: 0px;margin-left: 0px;margin-right: 0px;margin-top: 0px;max-width: 100%;padding-bottom: 0px;padding-left: 0px;padding-right: 0px;padding-top: 0px;word-wrap: break-word;"><img width="auto" style="box-sizing: border-box;height: 130px;margin-bottom: 0px;margin-left: 0px;margin-right: 0px;margin-top: 0px;max-width: 677px;padding-bottom: 0px;padding-left: 0px;padding-right: 0px;padding-top: 0px;visibility: visible;width: 130px;word-wrap: break-word;" data-ratio="1" data-type="jpeg" data-w="250" src="/upload/7ddc9700032e2c5cee163f1f1a37b46c.jpg" data-s="300,640"></strong></span> </section> <section style="text-align: center;color: rgb(51, 51, 51);line-height: normal;clear: both;box-sizing: border-box;background-color: rgb(255, 255, 255);"> <span style="margin: 0px;padding: 0px;letter-spacing: 1px;font-size: 12px;word-wrap: break-word;max-width: 100%;box-sizing: border-box;"><strong style="box-sizing: border-box;margin-bottom: 0px;margin-left: 0px;margin-right: 0px;margin-top: 0px;max-width: 100%;padding-bottom: 0px;padding-left: 0px;padding-right: 0px;padding-top: 0px;word-wrap: break-word;"><strong style="box-sizing: border-box;margin-bottom: 0px;margin-left: 0px;margin-right: 0px;margin-top: 0px;max-width: 100%;padding-bottom: 0px;padding-left: 0px;padding-right: 0px;padding-top: 0px;word-wrap: break-word;">架构师之路</strong>-分享技术思路</strong><strong style="box-sizing: border-box;margin-bottom: 0px;margin-left: 0px;margin-right: 0px;margin-top: 0px;max-width: 100%;padding-bottom: 0px;padding-left: 0px;padding-right: 0px;padding-top: 0px;word-wrap: break-word;"></strong><br style="box-sizing: border-box;margin-bottom: 0px;margin-left: 0px;margin-right: 0px;margin-top: 0px;max-width: 100%;padding-bottom: 0px;padding-left: 0px;padding-right: 0px;padding-top: 0px;word-wrap: break-word;"></span> </section> <section style="margin: 0px;padding: 0px;text-align: left;color: rgb(51, 51, 51);text-transform: none;line-height: 1.75em;text-indent: 0px;letter-spacing: 0.54px;clear: both;font-size: 17px;font-style: normal;font-variant: normal;font-weight: 400;text-decoration: none;word-spacing: 0px;white-space: normal;word-wrap: break-word;min-height: 17px;max-width: 677px;box-sizing: border-box;orphans: 2;-webkit-text-stroke-width: 0px;background-color: rgb(255, 255, 255);"> <span style="margin: 0px;padding: 0px;letter-spacing: 1px;font-size: 15px;word-wrap: break-word;max-width: 100%;box-sizing: border-box;">相关文章：</span> </section> <section style="margin: 0px;padding: 0px;text-align: left;color: rgb(51, 51, 51);text-transform: none;line-height: 1.75em;text-indent: 0px;letter-spacing: 0.54px;clear: both;font-size: 17px;font-style: normal;font-variant: normal;font-weight: 400;text-decoration: none;word-spacing: 0px;white-space: normal;word-wrap: break-word;min-height: 17px;max-width: 677px;box-sizing: border-box;orphans: 2;-webkit-text-stroke-width: 0px;background-color: rgb(255, 255, 255);"> <span style="margin: 0px;padding: 0px;letter-spacing: 1px;font-size: 15px;word-wrap: break-word;max-width: 100%;box-sizing: border-box;">《<a href="http://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&amp;mid=2651962738&amp;idx=1&amp;sn=d2d91a380bad06af9f7b9f7a80db26b3&amp;chksm=bd2d08ae8a5a81b8a7f044af52c5e6e77ec3df2bb4a9c91cd450c3fd932e8dade56afb09f784&amp;scene=21#wechat_redirect" target="_blank" data-linktype="2" data-itemshowtype="0">并发扣款，如何保证数据的一致性？</a>》</span> <br> </section> <p><span style="text-align: justify;color: rgb(51, 51, 51);text-transform: none;text-indent: 0px;letter-spacing: 1px;font-family: &quot;mp-quote&quot;,-apple-system-font,BlinkMacSystemFont,&quot;Helvetica Neue&quot;,&quot;PingFang SC&quot;,&quot;Hiragino Sans GB&quot;,&quot;Microsoft YaHei UI&quot;,&quot;Microsoft YaHei&quot;,Arial,sans-serif;font-size: 15px;font-style: normal;font-variant: normal;font-weight: 400;text-decoration: none;word-spacing: 0px;display: inline !important;orphans: 2;float: none;-webkit-text-stroke-width: 0px;background-color: transparent;"><br></span></p> <p><span style="text-align: justify;color: rgb(51, 51, 51);text-transform: none;text-indent: 0px;letter-spacing: 1px;font-family: &quot;mp-quote&quot;,-apple-system-font,BlinkMacSystemFont,&quot;Helvetica Neue&quot;,&quot;PingFang SC&quot;,&quot;Hiragino Sans GB&quot;,&quot;Microsoft YaHei UI&quot;,&quot;Microsoft YaHei&quot;,Arial,sans-serif;font-size: 15px;font-style: normal;font-variant: normal;font-weight: 400;text-decoration: none;word-spacing: 0px;display: inline !important;orphans: 2;float: none;-webkit-text-stroke-width: 0px;background-color: transparent;">关于并发扣款中</span><span style="text-align: justify;color: rgb(255, 76, 0);text-transform: none;text-indent: 0px;letter-spacing: 1px;font-family: &quot;mp-quote&quot;,-apple-system-font,BlinkMacSystemFont,&quot;Helvetica Neue&quot;,&quot;PingFang SC&quot;,&quot;Hiragino Sans GB&quot;,&quot;Microsoft YaHei UI&quot;,&quot;Microsoft YaHei&quot;,Arial,sans-serif;font-size: 15px;font-style: normal;font-variant: normal;font-weight: 400;text-decoration: none;word-spacing: 0px;display: inline !important;orphans: 2;float: none;-webkit-text-stroke-width: 0px;background-color: transparent;">幂等性</span><span style="text-align: justify;color: rgb(51, 51, 51);text-transform: none;text-indent: 0px;letter-spacing: 1px;font-family: &quot;mp-quote&quot;,-apple-system-font,BlinkMacSystemFont,&quot;Helvetica Neue&quot;,&quot;PingFang SC&quot;,&quot;Hiragino Sans GB&quot;,&quot;Microsoft YaHei UI&quot;,&quot;Microsoft YaHei&quot;,Arial,sans-serif;font-size: 15px;font-style: normal;font-variant: normal;font-weight: 400;text-decoration: none;word-spacing: 0px;display: inline !important;orphans: 2;float: none;-webkit-text-stroke-width: 0px;background-color: transparent;">，</span><span style="text-align: justify;color: rgb(255, 76, 0);text-transform: none;text-indent: 0px;letter-spacing: 1px;font-family: &quot;mp-quote&quot;,-apple-system-font,BlinkMacSystemFont,&quot;Helvetica Neue&quot;,&quot;PingFang SC&quot;,&quot;Hiragino Sans GB&quot;,&quot;Microsoft YaHei UI&quot;,&quot;Microsoft YaHei&quot;,Arial,sans-serif;font-size: 15px;font-style: normal;font-variant: normal;font-weight: 400;text-decoration: none;word-spacing: 0px;display: inline !important;orphans: 2;float: none;-webkit-text-stroke-width: 0px;background-color: transparent;">redis事务</span><span style="text-align: justify;color: rgb(51, 51, 51);text-transform: none;text-indent: 0px;letter-spacing: 1px;font-family: &quot;mp-quote&quot;,-apple-system-font,BlinkMacSystemFont,&quot;Helvetica Neue&quot;,&quot;PingFang SC&quot;,&quot;Hiragino Sans GB&quot;,&quot;Microsoft YaHei UI&quot;,&quot;Microsoft YaHei&quot;,Arial,sans-serif;font-size: 15px;font-style: normal;font-variant: normal;font-weight: 400;text-decoration: none;word-spacing: 0px;display: inline !important;orphans: 2;float: none;-webkit-text-stroke-width: 0px;background-color: transparent;">的问题，会在接下来几篇分享。希望大家有收获。</span></p>

抖音无水印解析教程总结

作者：じ☆ve宝贝

> 短视频上美女很多，虽然可以下载，奈何下载的视频有水印，作为一个追求完美的程序猿小(bu)哥(yao)哥(lian)，怎么可以容忍呢，因此做出该系列文章教大家一步步解析抖音高清无码小视频。 ## 已完成教程 1. [《Charles抓包工具学习》](https://www.studyjava.cn/topic/view/397721a9b8e641e6b8ab5c981df40c05.html) 2. [抖音抓包教程，抖音无水印地址解析](https://www.studyjava.cn/topic/view/75591f884cff456696c1a5c60ddb500b.html) 3. [抖音无水印下载Java源码](https://www.studyjava.cn/topic/view/30ca4318124b42fda00eb2f738b5e374.html) 4. (未开发)将抖音无水印的java代码做成一个web服务，让大家使用**https://www.studyjava.cn/douyin** **其实如上的教程不仅仅针对抖音，其他的短视频平台也是一样的，快手、火山之类的。希望同大家一起进步，一起学习。** ## 本站小程序 ![](/upload/762fd13b77664616808c3983a9896b5a.jpg)

项目中常用的19条MySQL优化

作者：微信小助手

<section class="xmteditor" style="display:none;" data-tools="新媒体管家" data-label="powered by xmt.cn"></section> <p style="text-align: center;"><img class="rich_pages" data-croporisrc="/upload/7802e540878c216f1316505c155aa74b.jpg" data-cropx1="0" data-cropx2="600" data-cropy1="0" data-cropy2="399" data-ratio="0.665" data-s="300,640" src="https://mmbiz.qpic.cn/mmbiz_jpg/iaIdQfEric9Ty7vUTk72JVTYSzUibeIpJpib5pGpD60fYUmHtNOBZImEVTVEBiaIqicPfeeEr1AicHPNuyeAGDKWRticDQ/640?wx_fmt=jpeg" data-type="jpeg" data-w="600" style="width: 600px;height: 399px;"></p> <p style="text-align: right;"><span style="font-size: 14px;">原文地址：</span><span style="font-size: 14px;text-decoration: underline;">https://segmentfault.com/a/1190000012155267</span></p> <p style="text-align: center;"><span style="color: rgb(63, 63, 63);font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;font-size: 22.4px;font-weight: bold;text-align: center;"><br></span></p> <p style="text-align: left;"><span style="color: rgb(63, 63, 63);font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;font-size: 22.4px;font-weight: bold;text-align: center;"><strong style="box-sizing: border-box;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;font-size: 16px;text-align: left;white-space: normal;color: rgb(255, 152, 0);line-height: 1.5;">声明一下：下面的优化方案都是基于 “ Mysql-索引-BTree类型 ”。&nbsp;</strong></span></p> <p style="text-align: left;"><span style="color: rgb(63, 63, 63);font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;font-size: 22.4px;font-weight: bold;text-align: center;"><strong style="box-sizing: border-box;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;font-size: 16px;text-align: left;white-space: normal;color: rgb(255, 152, 0);line-height: 1.5;"><br></strong></span></p> <p style="text-align: center;"><span style="color: rgb(63, 63, 63);font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;font-size: 22.4px;font-weight: bold;text-align: center;">一 善用EXPLAIN</span></p> <p style="text-align: center;"><span style="color: rgb(63, 63, 63);font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;font-size: 22.4px;font-weight: bold;text-align: center;"><br></span></p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">做MySQL优化，我们要善用&nbsp;<strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">EXPLAIN</strong>&nbsp;查看SQL执行计划。</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">下面来个简单的示例，标注(1,2,3,4,5)我们要重点关注的数据</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><img class="" data-ratio="0.12875" src="/upload/e8608b8b3b760540d0546cd497455830.jpg" data-type="jpeg" data-w="800" style="box-sizing: border-box;margin: 20px auto;line-height: 1.5;border-radius: 4px;display: block;width: 315px;" title="null"></p> <p style="box-sizing: border-box;margin-top: 20px;margin-right: 10px;margin-bottom: 20px;padding-left: 20px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.5;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><span style="box-sizing: border-box;margin: 10px;line-height: 1.5;text-indent: -20px;display: block;"><span style="">•</span><strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">type列:</strong>&nbsp;连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别</span><span style="box-sizing: border-box;margin: 10px;line-height: 1.5;text-indent: -20px;display: block;"><span style="">•</span><strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">key列:</strong>&nbsp;使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式</span><span style="box-sizing: border-box;margin: 10px;line-height: 1.5;text-indent: -20px;display: block;"><span style="">•</span><strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">key_len列:</strong>&nbsp;索引长度</span><span style="box-sizing: border-box;margin: 10px;line-height: 1.5;text-indent: -20px;display: block;"><span style="">•</span><strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">rows列:</strong>&nbsp;扫描行数。该值是个预估值</span><span style="box-sizing: border-box;margin: 10px;line-height: 1.5;text-indent: -20px;display: block;"><span style="">•</span><strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">Extra列:</strong>&nbsp;详细说明。注意常见的不太友好的值有：Using filesort, Using temporary</span></p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">二 SQL语句中IN包含的值不应过多</h2> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：<code style="box-sizing: border-box;padding: 3px 5px;color: rgb(255, 53, 2);line-height: 1.5;font-size: 14.4px;font-family: &quot;Operator Mono&quot;, Consolas, Monaco, Menlo, monospace;background: rgb(248, 245, 236);border-radius: 2px;">select id from table_name where num in(1,2,3)</code>&nbsp;对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。</p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">三 SELECT语句务必指明字段名称</h2> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">SELECT * 增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。</p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">四 当只需要一条数据的时候，使用limit 1</h2> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">这是为了使EXPLAIN中type列达到const类型</p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">五 如果排序字段没有用到索引,就尽量少排序</h2> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">六 如果限制条件中其他字段没有索引，尽量少用or</h2> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果</p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">七 尽量用union all代替union</h2> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。<strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">当然，union all的前提条件是两个结果集没有重复数据。</strong></p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">八 不使用ORDER BY RAND()</h2> <section class="code-snippet__fix code-snippet__js"> <ul class="code-snippet__line-index code-snippet__js"> <li></li> </ul> <pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">select</span> <span class="code-snippet__keyword">id</span> <span class="code-snippet__keyword">from</span> <span class="code-snippet__string">`table_name`</span> <span class="code-snippet__keyword">order</span> <span class="code-snippet__keyword">by</span> <span class="code-snippet__keyword">rand</span>() <span class="code-snippet__keyword">limit</span> <span class="code-snippet__number">1000</span>;</span></code></pre> </section> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">上面的sql语句，可优化为</p> <section class="code-snippet__fix code-snippet__js"> <ul class="code-snippet__line-index code-snippet__js"> <li></li> </ul> <pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">select</span> <span class="code-snippet__keyword">id</span> <span class="code-snippet__keyword">from</span> <span class="code-snippet__string">`table_name`</span> t1 <span class="code-snippet__keyword">join</span> (<span class="code-snippet__keyword">select</span> <span class="code-snippet__keyword">rand</span>() * (<span class="code-snippet__keyword">select</span> <span class="code-snippet__keyword">max</span>(<span class="code-snippet__keyword">id</span>) <span class="code-snippet__keyword">from</span> <span class="code-snippet__string">`table_name`</span>) <span class="code-snippet__keyword">as</span> nid) t2 ont1.id &gt; t2.nid <span class="code-snippet__keyword">limit</span> <span class="code-snippet__number">1000</span>;</span></code></pre> </section> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">九 区分in和exists,not in和not exists</h2> <section class="code-snippet__fix code-snippet__js"> <ul class="code-snippet__line-index code-snippet__js"> <li></li> </ul> <pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">select</span> * <span class="code-snippet__keyword">from</span> 表A <span class="code-snippet__keyword">where</span> <span class="code-snippet__keyword">id</span> <span class="code-snippet__keyword">in</span> (<span class="code-snippet__keyword">select</span> <span class="code-snippet__keyword">id</span> <span class="code-snippet__keyword">from</span> 表B)</span></code></pre> </section> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">上面sql语句相当于</p> <section class="code-snippet__fix code-snippet__js"> <ul class="code-snippet__line-index code-snippet__js"> <li></li> </ul> <pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">select</span> * <span class="code-snippet__keyword">from</span> 表A <span class="code-snippet__keyword">where</span> <span class="code-snippet__keyword">exists</span>(<span class="code-snippet__keyword">select</span> * <span class="code-snippet__keyword">from</span> 表B <span class="code-snippet__keyword">where</span> 表B.id=表A.id)</span></code></pre> </section> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以<strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。</strong>&nbsp;关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。<strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">如何高效的写出一个替代not exists的sql语句？</strong></p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">原sql语句</p> <section class="code-snippet__fix code-snippet__js"> <ul class="code-snippet__line-index code-snippet__js"> <li></li> </ul> <pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">select</span> colname … <span class="code-snippet__keyword">from</span> A表 <span class="code-snippet__keyword">where</span> a.id <span class="code-snippet__keyword">not</span> <span class="code-snippet__keyword">in</span> (<span class="code-snippet__keyword">select</span> b.id <span class="code-snippet__keyword">from</span> B表)</span></code></pre> </section> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">高效的sql语句</p> <section class="code-snippet__fix code-snippet__js"> <ul class="code-snippet__line-index code-snippet__js"> <li></li> </ul> <pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">select</span> colname … <span class="code-snippet__keyword">from</span> A表 <span class="code-snippet__keyword">Left</span> <span class="code-snippet__keyword">join</span> B表 <span class="code-snippet__keyword">on</span> <span class="code-snippet__keyword">where</span> a.id = b.id <span class="code-snippet__keyword">where</span> b.id <span class="code-snippet__keyword">is</span> <span class="code-snippet__literal">null</span></span></code></pre> </section> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">取出的结果集如下图表示，A表不在B表中的数据</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><img class="" data-ratio="0.6102719033232629" src="/upload/755ba5f90b1260f9f9ee25f5c5244566.jpg" data-type="jpeg" data-w="331" style="box-sizing: border-box;margin: 20px auto;line-height: 1.5;border-radius: 4px;display: block;width: 315px;" title="null"></p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">十 使用合理的分页方式以提高分页的效率</h2> <section class="code-snippet__fix code-snippet__js"> <ul class="code-snippet__line-index code-snippet__js"> <li></li> </ul> <pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">select</span> <span class="code-snippet__keyword">id</span>,<span class="code-snippet__keyword">name</span> <span class="code-snippet__keyword">from</span> table_name <span class="code-snippet__keyword">limit</span> <span class="code-snippet__number">866613</span>, <span class="code-snippet__number">20</span></span></code></pre> </section> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：</p> <section class="code-snippet__fix code-snippet__js"> <ul class="code-snippet__line-index code-snippet__js"> <li></li> </ul> <pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">select</span> <span class="code-snippet__keyword">id</span>,<span class="code-snippet__keyword">name</span> <span class="code-snippet__keyword">from</span> table_name <span class="code-snippet__keyword">where</span> <span class="code-snippet__keyword">id</span>&gt; <span class="code-snippet__number">866612</span> <span class="code-snippet__keyword">limit</span> <span class="code-snippet__number">20</span></span></code></pre> </section> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">十一 分段查询</h2> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">如下图这个sql语句，扫描的行数成百万级以上的时候就可以使用分段查询</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><img class="" data-ratio="0.125" src="/upload/2b11ad7d6f348d653c295b942087b221.jpg" data-type="jpeg" data-w="800" style="box-sizing: border-box;margin: 20px auto;border-radius: 4px;display: block;width:100%;height:auto;" title="null"></p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">十二 避免在 where 子句中对字段进行 null 值判断</h2> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">对于null的判断会导致引擎放弃使用索引而进行全表扫描。</p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">十三 不建议使用%前缀模糊查询</h2> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">例如LIKE “%name”或者LIKE “%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">那如何查询%name%？</strong></p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">如下图所示，虽然给secret字段添加了索引，但在explain结果果并没有使用</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><img class="" data-backh="146" data-backw="315" data-before-oversubscription-url="https://mmbiz.qpic.cn/mmbiz_jpg/iaIdQfEric9Ty7vUTk72JVTYSzUibeIpJpibmPVEzR3ALVBdjiaZoWYSOUoqaeLEiberN7V2BGzdyjUk5VbHpEtC5icMw/?wx_fmt=jpeg" data-ratio="0.4625" src="/upload/21e3624d5caa7896437f271e3c1ed8a3.jpg" data-type="jpeg" data-w="800" style="box-sizing: border-box;margin: 20px auto;line-height: 1.5;border-radius: 4px;display: block;width: 100%;height: auto;" title="null"></p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">那么如何解决这个问题呢，答案：<strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">使用全文索引</strong></p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">在我们查询中经常会用到select id,fnum,fdst from table_name where user_name like '%zhangsan%'; 。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">创建全文索引的sql语法是：</p> <section class="code-snippet__fix code-snippet__js"> <ul class="code-snippet__line-index code-snippet__js"> <li></li> </ul> <pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">ALTER</span> <span class="code-snippet__keyword">TABLE</span> <span class="code-snippet__string">`table_name`</span> <span class="code-snippet__keyword">ADD</span> FULLTEXT <span class="code-snippet__keyword">INDEX</span> <span class="code-snippet__string">`idx_user_name`</span> (<span class="code-snippet__string">`user_name`</span>);</span></code></pre> </section> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">使用全文索引的sql语句是：</p> <section class="code-snippet__fix code-snippet__js"> <ul class="code-snippet__line-index code-snippet__js"> <li></li> </ul> <pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">select</span> <span class="code-snippet__keyword">id</span>,fnum,fdst <span class="code-snippet__keyword">from</span> table_name <span class="code-snippet__keyword">where</span> <span class="code-snippet__keyword">match</span>(user_name) against(<span class="code-snippet__string">'zhangsan'</span> <span class="code-snippet__keyword">in</span> <span class="code-snippet__built_in">boolean</span> <span class="code-snippet__keyword">mode</span>);</span></code></pre> </section> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">注意：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别</strong></p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">十四 避免在where子句中对字段进行表达式操作</h2> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">比如</p> <section class="code-snippet__fix code-snippet__js"> <ul class="code-snippet__line-index code-snippet__js"> <li></li> </ul> <pre class="code-snippet__js" data-lang=""><code style="box-sizing: border-box;font-size: 14px;white-space: pre;display: flex;font-family: Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace;"><span class="code-snippet_outer" style="box-sizing: border-box;">select user_id,user_project from table_name where age*2=36;</span></code></pre> </section> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成</p> <section class="code-snippet__fix code-snippet__js"> <ul class="code-snippet__line-index code-snippet__js"> <li></li> </ul> <pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">select</span> user_id,user_project <span class="code-snippet__keyword">from</span> table_name <span class="code-snippet__keyword">where</span> age=<span class="code-snippet__number">36</span>/<span class="code-snippet__number">2</span>;</span></code></pre> </section> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">十五 避免隐式类型转换</h2> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><img class="" data-backh="175" data-backw="315" data-before-oversubscription-url="https://mmbiz.qpic.cn/mmbiz_jpg/iaIdQfEric9Ty7vUTk72JVTYSzUibeIpJpibWzkwykpzibtX1bXc6k0RoPFroZsnal7lWf1DiaVLDibqkt2zmPf7bibSyg/?wx_fmt=jpeg" data-ratio="0.555" src="/upload/9654f8e9a558e7f23f9056cdd2c22c88.jpg" data-type="jpeg" data-w="800" style="box-sizing: border-box;margin: 20px auto;line-height: 1.5;border-radius: 4px;display: block;width: 100%;height: auto;" title="null"></p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">十六 对于联合索引来说，要遵守最左前缀法则</h2> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">举列来说索引含有字段id,name,school，可以直接用id字段，也可以id,name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面</p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">十七 必要时可以使用force index来强制查询走某个索引</h2> <p><span style="color: rgb(63, 63, 63);font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;font-size: 16px;text-align: left;">有的时候MySQL优化器采取它认为合适的索引来检索sql语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用force index来强制优化器使用我们制定的索引。</span></p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">十八 注意范围查询语句</h2> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">对于联合索引来说，如果存在范围查询，比如between,&gt;,&lt;等条件时，会造成后面的索引字段失效。</p> <h2 style="box-sizing: border-box;margin: 80px 10px 40px;font-weight: bold;white-space: normal;text-align: center;color: rgb(63, 63, 63);line-height: 1.5;font-size: 22.4px;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">十九 关于JOIN优化</h2> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><img class="" data-ratio="0.33875" src="/upload/dd9f7099492799ff2f981b1c45f4fd3b.jpg" data-type="jpeg" data-w="800" style="box-sizing: border-box;margin: 20px auto;line-height: 1.5;border-radius: 4px;display: block;width: 315px;" title="null"></p> <p style="box-sizing: border-box;margin-top: 20px;margin-right: 10px;margin-bottom: 20px;padding-left: 20px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.5;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;list-style: circle;"><span style="box-sizing: border-box;margin: 10px;line-height: 1.5;text-indent: -20px;display: block;"><span style="">•</span>LEFT JOIN A表为驱动表</span><span style="box-sizing: border-box;margin: 10px;line-height: 1.5;text-indent: -20px;display: block;"><span style="">•</span>INNER JOIN MySQL会自动找出那个数据少的表作用驱动表</span><span style="box-sizing: border-box;margin: 10px;line-height: 1.5;text-indent: -20px;display: block;"><span style="">•</span>RIGHT JOIN B表为驱动表</span></p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">注意：MySQL中没有full join，可以用以下方式来解决</strong></p> <section class="code-snippet__fix code-snippet__js"> <ul class="code-snippet__line-index code-snippet__js"> <li></li> <li></li> <li></li> <li></li> </ul> <pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">select</span> * <span class="code-snippet__keyword">from</span> A <span class="code-snippet__keyword">left</span> <span class="code-snippet__keyword">join</span> B <span class="code-snippet__keyword">on</span> B.name = A.name </span></code><code><span class="code-snippet_outer"><span class="code-snippet__keyword">where</span> B.name <span class="code-snippet__keyword">is</span> <span class="code-snippet__literal">null</span></span></code><code><span class="code-snippet_outer"> <span class="code-snippet__keyword">union</span> <span class="code-snippet__keyword">all</span></span></code><code><span class="code-snippet_outer"><span class="code-snippet__keyword">select</span> * <span class="code-snippet__keyword">from</span> B;</span></code></pre> </section> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">尽量使用inner join，避免left join</strong></p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">合理利用索引</strong></p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">被驱动表的索引字段作为on的限制字段。</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">利用小表去驱动大表</strong></p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><img class="" data-ratio="0.8209718670076727" src="/upload/9a5c1120e4d85b3fae56846cfb32b492.png" data-type="png" data-w="391" style="box-sizing: border-box;margin: 20px auto;line-height: 1.5;border-radius: 4px;display: block;width: 315px;" title="null"></p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">巧用STRAIGHT_JOIN</strong></p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">inner join是由mysql选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。<strong style="box-sizing: border-box;color: rgb(255, 152, 0);line-height: 1.5;">在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。</strong></p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;"><img class="" data-ratio="0.6918138041733547" src="/upload/65204061480f39af420fe94908c34feb.jpg" data-type="jpeg" data-w="623" style="box-sizing: border-box;margin: 20px auto;line-height: 1.5;border-radius: 4px;display: block;width: 315px;" title="null"></p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">这个方式有时可能减少3倍的时间。</p> <p style="box-sizing: border-box;margin: 10px;font-size: 16px;white-space: normal;text-align: left;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;">这里只列举了上述优化方案，当然还有其他的优化方式，大家可以去摸索尝试，感谢关注。。</p> <p><br></p> <p style="margin-top: 5px;margin-bottom: 5px;max-width: 100%;min-height: 1em;letter-spacing: 0.544px;text-align: center;line-height: 1.75em;box-sizing: border-box !important;overflow-wrap: break-word !important;"><strong style="max-width: 100%;color: rgb(255, 169, 0);font-family: -apple-system, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Microsoft YaHei&quot;, &quot;Source Han Sans SC&quot;, &quot;Noto Sans CJK SC&quot;, &quot;WenQuanYi Micro Hei&quot;, sans-serif;font-size: 18px;letter-spacing: 1.5px;box-sizing: border-box !important;overflow-wrap: break-word !important;">推荐阅读</strong></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247485181&amp;idx=1&amp;sn=7a7a7ce0671e8c5456add8da098501c2&amp;scene=21#wechat_redirect" title="不就是个短信登录API嘛，有这么复杂吗？" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">不就是个短信登录API嘛，有这么复杂吗？</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247485159&amp;idx=1&amp;sn=c97c087c45ad6dfc0ef61d80a9d0f702&amp;scene=21#wechat_redirect" title="盘点阿里巴巴 15 款开发者工具" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">盘点阿里巴巴 15 款开发者工具</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247485147&amp;idx=1&amp;sn=90e525a83a451d8c20298a7ef2d35ab9&amp;scene=21#wechat_redirect" title="蚂蚁金服2019实习生面经总结(已拿口头offer)" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">蚂蚁金服2019实习生面经总结(已拿口头offer)</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247485070&amp;idx=1&amp;sn=31894a1bdda357d897962a9fc3a994b7&amp;chksm=cea24945f9d5c0531db568321f1d8d7a4e848e04aa2df18e589db9ba4aafee0fb0cebb965252&amp;token=463285003&amp;lang=zh_CN&amp;scene=21#wechat_redirect" title="记一次蚂蚁金服的面试经历" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">记一次蚂蚁金服的面试经历</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247485113&amp;idx=1&amp;sn=e4dd1bb22778e4e9139bf29d98a7492b&amp;chksm=cea24972f9d5c064e5b454b84b9bc0d42f4aec007f20f79b564398e6dec7c0cdcda0e64193b5&amp;token=183738038&amp;lang=zh_CN&amp;scene=21#wechat_redirect" title="Java学习必备书籍推荐终极版！" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">Java学习必备书籍推荐终极版！</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247485059&amp;idx=1&amp;sn=a881a1ba6013c1214b64bbed23420b88&amp;chksm=cea24948f9d5c05edbd94bdc705da6d10ccc5c4213b7320f64b88b9a503d1c3adfe2b88fc6d7&amp;token=463285003&amp;lang=zh_CN&amp;scene=21#wechat_redirect" title="我觉得技术人员该有的提问方式" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">我觉得技术人员该有的提问方式</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247484744&amp;idx=1&amp;sn=9db31dca13d327678845054af75efb74&amp;chksm=cea24a83f9d5c3956f4feb9956b068624ab2fdd6c4a75fe52d5df5dca356a016577301399548&amp;token=1082669959&amp;lang=zh_CN&amp;scene=21#wechat_redirect" title="Java 8 新特性最佳指南" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">Java 8 新特性最佳指南</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247484746&amp;idx=1&amp;sn=a519a9e3d638bff5c65008f7de167e4b&amp;scene=21#wechat_redirect" title="做公众号这一年的经历和一件“大事”(2018-03-10)" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">做公众号这一年的经历和一件“大事”(2018-03-10)</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247484789&amp;idx=1&amp;sn=2ad9fabb8fc7fae3bd3756ea05594344&amp;chksm=cea24abef9d5c3a889b6cb8e00cb18abbb694d189c84a24fa1ed337ad4c56194cd39316dc6a5&amp;token=1082669959&amp;lang=zh_CN&amp;scene=21#wechat_redirect" title="盘点一下Github上开源的Java面试/学习相关的仓库，看完弄懂薪资至少增加10k" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">盘点一下Github上开源的Java面试/学习相关的仓库，看完弄懂薪资至少增加10k</a>( 2018-12-24)</p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247484840&amp;idx=1&amp;sn=cf4611ac290ae3cbb381fe52aa76b60b&amp;chksm=cea24a63f9d5c375215f7539ff0f3d6320f091d5f8b73e95c724ec9a31d3b5baa98c3f5a1012&amp;token=1082669959&amp;lang=zh_CN&amp;scene=21#wechat_redirect" title="可能是一份最适合你的后端面试指南（部分内容前端同样适用）" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">可能是一份最适合你的后端面试指南（部分内容前端同样适用）</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247484858&amp;idx=1&amp;sn=8e222ea6115e0b69cac91af14d2caf36&amp;chksm=cea24a71f9d5c367148dccec3d5ddecf5ecd8ea096b5c5ec32f22080e66ac3c343e99151c9e0&amp;token=1082669959&amp;lang=zh_CN&amp;scene=21#wechat_redirect" title="redis 总结——重构版" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">redis 总结——重构版</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247484850&amp;idx=1&amp;sn=3238360bfa8105cf758dcf7354af2814&amp;chksm=cea24a79f9d5c36fb2399aafa91d7fb2699b5006d8d037fe8aaf2e5577ff20ae322868b04a87&amp;token=1082669959&amp;lang=zh_CN&amp;scene=21#wechat_redirect" title="史上最全Redis高可用技术解决方案大全" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">史上最全Redis高可用技术解决方案大全</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247485068&amp;idx=1&amp;sn=c37267fe59978dbfcd6a9a54eee1c502&amp;chksm=cea24947f9d5c051008233a6a938e802b710ccf919f4215f84dcc0bf1fdad7d0101d37497d33&amp;token=42880587&amp;lang=zh_CN&amp;scene=21#wechat_redirect" title="听说又被 JVM 内存区域方面的面试题给虐了？看看这篇文章吧！" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">听说又被 JVM 内存区域方面的面试题给虐了？看看这篇文章吧！</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247484877&amp;idx=1&amp;sn=f54d41b68f0cd6cc7c0348a2fddbda9f&amp;chksm=cea24a06f9d5c3102bfef946ba6c7cc5df9a503ccb14b9b141c54e179617e4923c260c0b0a01&amp;token=1082669959&amp;lang=zh_CN&amp;scene=21#wechat_redirect" title="搞定 JVM 垃圾回收就是这么简单" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">搞定 JVM 垃圾回收就是这么简单</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247485097&amp;idx=1&amp;sn=84c89da477b1338bdf3e9fcd65514ac1&amp;scene=21#wechat_redirect" title="一条SQL语句在MySQL中如何执行的" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">一条SQL语句在MySQL中如何执行的</a></p> <p style="margin: 10px;max-width: 100%;box-sizing: border-box;min-height: 1em;letter-spacing: 0.544px;font-size: 16px;text-align: center;color: rgb(63, 63, 63);line-height: 1.6;font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, &quot;PingFang SC&quot;, Cambria, Cochin, Georgia, Times, &quot;Times New Roman&quot;, serif;overflow-wrap: break-word !important;"><a href="https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&amp;mid=2247485085&amp;idx=1&amp;sn=01e5c29c49f32886bc897af7632b34ba&amp;chksm=cea24956f9d5c040a07e4d335219f11f888a2d32444c16cade3f69c294ae0a1e416bcd221fb6&amp;token=1613452699&amp;lang=zh_CN&amp;scene=21#wechat_redirect" title="一文带你轻松搞懂事务隔离级别(图文详解)" data-linktype="2" style="color: rgb(20, 140, 228);-webkit-tap-highlight-color: rgba(0, 0, 0, 0);max-width: 100%;box-sizing: border-box;overflow-wrap: break-word !important;">一文带你轻松搞懂事务隔离级别(图文详解)</a></p> <p style="max-width: 100%;min-height: 1em;font-size: 16px;text-align: center;letter-spacing: 1.5px;line-height: 1.75em;box-sizing: border-box !important;overflow-wrap: break-word !important;"><img class="rich_pages" data-copyright="0" data-ratio="1" data-s="300,640" src="/upload/f3ae1a3f6e273647b029f8dbb898bdcd.jpg" data-type="jpeg" data-w="258" style="box-sizing: border-box !important;overflow-wrap: break-word !important;visibility: visible !important;width: 175px !important;"></p> <p style="max-width: 100%;min-height: 1em;font-size: 16px;text-align: center;letter-spacing: 1.5px;line-height: 1.75em;box-sizing: border-box !important;overflow-wrap: break-word !important;"><span style="max-width: 100%;box-sizing: border-box;font-weight: 600;color: rgb(36, 41, 46);text-align: start;overflow-wrap: break-word !important;">欢迎关注<img class="" data-ratio="1" src="/upload/dcb8ad1f510316ba1893da9b8a07e274.png" data-type="png" data-w="19" style="display: inline-block;vertical-align: text-bottom;box-sizing: border-box !important;overflow-wrap: break-word !important;visibility: visible !important;width: 19px !important;">点个再看<img class="" data-ratio="1" src="/upload/5f7454d24a334e968c32b8ce9ae53c5.png" data-type="png" data-w="18" style="display: inline-block;vertical-align: text-bottom;box-sizing: border-box !important;overflow-wrap: break-word !important;visibility: visible !important;width: 18px !important;"></span></p> <p><br></p> <p><br></p>

Java设计模式(转)——23.解释器模式

作者：じ☆ve宝贝

## 23.解释器模式（Interpreter） 解释器模式是我们暂时的最后一讲，一般主要应用在OOP开发中的编译器的开发中，所以适用面比较窄。 ![解释器设计模式](/upload/content34.png "解释器设计模式") Context类是一个上下文环境类，Plus和Minus分别是用来计算的实现，代码如下： ``` public interface Expression { public int interpret(Context context); } ``` ``` public class Plus implements Expression { @Override public int interpret(Context context) { return context.getNum1()+context.getNum2(); } } ``` ``` public class Minus implements Expression { @Override public int interpret(Context context) { return context.getNum1()-context.getNum2(); } } ``` ``` public class Context { private int num1; private int num2; public Context(int num1, int num2) { this.num1 = num1; this.num2 = num2; } public int getNum1() { return num1; } public void setNum1(int num1) { this.num1 = num1; } public int getNum2() { return num2; } public void setNum2(int num2) { this.num2 = num2; } } ``` ``` public class User2 extends User { public User2(Mediator mediator){ super(mediator); } @Override public void work() { System.out.println("user2 exe!"); } } ``` 测试： ``` public class Test { public static void main(String[] args) { // 计算9+2-8的值 int result = new Minus().interpret((new Context(new Plus() .interpret(new Context(9, 2)), 8))); System.out.println(result); } } ``` 最后输出正确的结果：3。 基本就这样，解释器模式用来做各种各样的解释器，如正则表达式等的解释器等等！

【Java并发编程】面试如果还没法说透Synchronized底层原理，你就out了！

作者：微信小助手

<p style="white-space: normal;text-align: left;" data-mpa-powered-by="yiban.io"><img class="" data-ratio="0.6666666666666666" data-type="png" data-w="127" src="/upload/4c8b91599182ea5ba1ead110e9271956.png" style="height: 41px;vertical-align: bottom;width: 76px;"></p> <p style="white-space: normal;color: rgb(153, 80, 51);line-height: normal;"><span style="font-size: 15px;letter-spacing: 1px;">还没关注？伸出中指点这里！</span><br></p> <p style="white-space: normal;color: rgb(153, 80, 51);line-height: normal;"><br></p> <section class="KolEditor" data-tools-id="85858" style="white-space: normal;"> <section class="Powered-by-KolEditor V5" powered-by="KolEditor.us"> <section class=""> <section class="" style="margin-bottom: 5px;height: 5px;background-color: rgb(154, 157, 170);"></section> <section class="" style="margin-bottom: 5px;height: 5px;background-color: rgb(154, 157, 170);"></section> <section class="" style="margin-bottom: 5px;height: 5px;background-color: rgb(154, 157, 170);"></section> <section class="" style="margin-bottom: 5px;height: 5px;background-color: rgb(154, 157, 170);"></section> <section class="" style="margin-bottom: 5px;height: 5px;background-color: rgb(154, 157, 170);"></section> <section class="" style="margin-bottom: 5px;height: 5px;background-color: rgb(154, 157, 170);"></section> <section class="" style="margin-top: -40px;margin-right: 1.5em;margin-left: 1.5em;padding: 10px;background-color: rgb(226, 230, 0);"> <section class="Powered-by-KolEditor V5" powered-by="KolEditor.us"> <section class=""> <section class="" style="text-align: center;color: rgb(255, 255, 255);"> <section> <span style="color: rgb(0, 0, 0);font-size: 15px;">聊技术、论职场！</span> </section> <section> <span style="color: rgb(0, 0, 0);"><span style="font-size: 15px;">为IT人打造一个“有温度”的</span><strong style="font-size: 15px;"><span style="color: rgb(201, 56, 28);">狸猫技术窝</span></strong></span> </section> </section> </section> </section> </section> </section> </section> </section> <p style="white-space: normal;text-align: right;line-height: normal;"><span style="color: rgb(0, 0, 0);font-family: Microsoft YaHei, 微软雅黑;font-size: 12px;text-align: center;white-space: pre-wrap;">来源：</span><span style="color: rgb(0, 0, 0);font-family: Microsoft YaHei, 微软雅黑;font-size: 12px;text-align: center;white-space: pre-wrap;">http://h5ip.cn/me</span><span style="color: rgb(0, 0, 0);font-family: Microsoft YaHei, 微软雅黑;font-size: 12px;text-align: center;white-space: pre-wrap;">Hz</span></p> <p style="white-space: normal;text-align: right;line-height: normal;"><span style="color: rgb(0, 0, 0);font-family: Microsoft YaHei, 微软雅黑;text-align: center;white-space: pre-wrap;background-color: rgb(255, 255, 255);font-size: 12px;"> </span></p> <p style="white-space: normal;"><br></p> <p style="white-space: normal;line-height: 2em;"><span style="font-size: 14px;">一、Synchronized的基本使用</span></p> <p style="white-space: normal;line-height: 2em;"><span style="font-size: 14px;">二、Synchronized的底层原理</span></p> <p style="white-space: normal;line-height: 2em;"><span style="font-size: 14px;">三、运行结果解释</span></p> <p style="white-space: normal;line-height: 2em;"><span style="font-size: 14px;">四、总结</span></p> <p style="margin: 10px auto;"><br></p> <p style="margin: 10px auto;line-height: 2em;"><span style="font-size: 18px;"><strong><span style="line-height: 1.5;">一、Synchronized的基本使用</span></strong></span></p> <p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;">Synchronized是Java中解决并发问题的一种最常用的方法，也是最简单的一种方法。</span></p> <p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;"><br></span></p> <p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;">Synchronized的作用主要有三个：</span></p> <ol class=" list-paddingleft-2" style="list-style-type: decimal;"> <li><p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;">确保线程互斥的访问同步代码</span></p></li> <li><p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;">保证共享变量的修改能够及时可见</span></p></li> <li><p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;">有效解决重排序问题。</span></p></li> </ol> <p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;"><br></span></p> <p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;">从语法上讲，Synchronized总共有三种用法：</span></p> <p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;">　　（1）修饰普通方法</span></p> <p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;">　　（2）修饰静态方法</span></p> <p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;">　　（3）修饰代码块</span></p> <p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;"><br></span></p> <p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;">接下来我就通过几个例子程序来说明一下这三种使用方式（为了便于比较，三段代码除了Synchronized的使用方式不同以外，其他基本保持一致）</span></p> <p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;"><br></span></p> <p style="margin: 10px auto;line-height: 2em;"><span style="color: rgb(57, 137, 31);"><strong><span style="font-size: 15px;">1、没有同步的情况：</span></strong></span></p> <p style="margin: 10px auto;line-height: 2em;"><span style="font-size: 15px;">代码段一：</span></p> <section data-mpa-preserve-tpl-color="t" data-mpa-template="t" class="mpa-template" mpa-preserve="t" mpa-from-tpl="t"> <pre style="margin:0;padding:0;border-radius:none;background:none;"><p style="border-radius: 4px;font-size: 0.85em;margin: 0px 0.15em;background: rgb(40, 44, 52);color: rgb(171, 178, 191);display: block;padding: 6px;overflow-x: auto;white-space: nowrap;line-height: 2em;">package com.paddx.test.concurrent;<br><br>public class SynchronizedTest {<br>&nbsp; public void method1(){<br>&nbsp; &nbsp; <br>&nbsp; &nbsp; System.<span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 20px;text-decoration: none;font-weight: 400;font-style: normal;">out</span>.println(<span class="hljs-default-string" style="color: rgb(152, 195, 121);background: rgba(0, 0, 0, 0);display: inline;width: 105px;text-decoration: none;font-weight: 400;font-style: normal;">"Method 1 start"</span>);<br mpa-from-tpl="t"> <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 19px;text-decoration: none;font-weight: 400;font-style: normal;">&nbsp; &nbsp; try</span> {<br mpa-from-tpl="t">&nbsp; &nbsp; &nbsp; System.<span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 20px;text-decoration: none;font-weight: 400;font-style: normal;">out</span>.println(<span class="hljs-default-string" style="color: rgb(152, 195, 121);background: rgba(0, 0, 0, 0);display: inline;width: 119px;text-decoration: none;font-weight: 400;font-style: normal;">"Method 1 execute"</span>);<br mpa-from-tpl="t">&nbsp; &nbsp; &nbsp; Thread.sleep(<span class="hljs-default-number" style="color: rgb(209, 154, 102);background: rgba(0, 0, 0, 0);display: inline;width: 26px;text-decoration: none;font-weight: 400;font-style: normal;">3000</span>);<br mpa-from-tpl="t">&nbsp; &nbsp; } <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 33px;text-decoration: none;font-weight: 400;font-style: normal;">catch</span> (InterruptedException e) {<br mpa-from-tpl="t">&nbsp; &nbsp; &nbsp; &nbsp;e.printStackTrace();<br mpa-from-tpl="t">&nbsp; &nbsp; }<br mpa-from-tpl="t">&nbsp; &nbsp; System.<span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 20px;text-decoration: none;font-weight: 400;font-style: normal;">out</span>.println(<span class="hljs-default-string" style="color: rgb(152, 195, 121);background: rgba(0, 0, 0, 0);display: inline;width: 92px;text-decoration: none;font-weight: 400;font-style: normal;">"Method 1 end"</span>);<br mpa-from-tpl="t">&nbsp; }<br mpa-from-tpl="t"><br mpa-from-tpl="t"> <span class="hljs-default-function" style="color: rgb(171, 178, 191);background: rgba(0, 0, 0, 0);display: inline;width: 139px;text-decoration: none;font-weight: 400;font-style: normal;"><span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 40px;text-decoration: none;font-weight: 400;font-style: normal;">&nbsp; public</span> <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 27px;text-decoration: none;font-weight: 400;font-style: normal;">void</span> <span class="hljs-default-title" style="color: rgb(97, 174, 238);background: rgba(0, 0, 0, 0);display: inline;width: 47px;text-decoration: none;font-weight: 400;font-style: normal;">method2</span>(<span class="hljs-default-params" style="color: rgb(171, 178, 191);background: rgba(0, 0, 0, 0);display: inline;width: 0px;text-decoration: none;font-weight: 400;font-style: normal;"></span>)</span>{<br mpa-from-tpl="t">&nbsp; &nbsp; System.<span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 20px;text-decoration: none;font-weight: 400;font-style: normal;">out</span>.println(<span class="hljs-default-string" style="color: rgb(152, 195, 121);background: rgba(0, 0, 0, 0);display: inline;width: 105px;text-decoration: none;font-weight: 400;font-style: normal;">"Method 2 start"</span>);<br mpa-from-tpl="t"> <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 19px;text-decoration: none;font-weight: 400;font-style: normal;">&nbsp; &nbsp; try</span> {<br mpa-from-tpl="t">&nbsp; &nbsp; &nbsp; System.<span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 20px;text-decoration: none;font-weight: 400;font-style: normal;">out</span>.println(<span class="hljs-default-string" style="color: rgb(152, 195, 121);background: rgba(0, 0, 0, 0);display: inline;width: 119px;text-decoration: none;font-weight: 400;font-style: normal;">"Method 2 execute"</span>);<br mpa-from-tpl="t">&nbsp; &nbsp; &nbsp; Thread.sleep(<span class="hljs-default-number" style="color: rgb(209, 154, 102);background: rgba(0, 0, 0, 0);display: inline;width: 26px;text-decoration: none;font-weight: 400;font-style: normal;">1000</span>);<br mpa-from-tpl="t">&nbsp; &nbsp; } <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 33px;text-decoration: none;font-weight: 400;font-style: normal;">catch</span> (InterruptedException e) {<br mpa-from-tpl="t">&nbsp; &nbsp; &nbsp; e.printStackTrace();<br mpa-from-tpl="t">&nbsp; &nbsp; }<br mpa-from-tpl="t">&nbsp; &nbsp; System.<span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 20px;text-decoration: none;font-weight: 400;font-style: normal;">out</span>.println(<span class="hljs-default-string" style="color: rgb(152, 195, 121);background: rgba(0, 0, 0, 0);display: inline;width: 92px;text-decoration: none;font-weight: 400;font-style: normal;">"Method 2 end"</span>);<br mpa-from-tpl="t">&nbsp; }<br mpa-from-tpl="t"><br mpa-from-tpl="t"> <span class="hljs-default-function" style="color: rgb(171, 178, 191);background: rgba(0, 0, 0, 0);display: inline;width: 257px;text-decoration: none;font-weight: 400;font-style: normal;"><span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 40px;text-decoration: none;font-weight: 400;font-style: normal;">&nbsp; public</span> <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 40px;text-decoration: none;font-weight: 400;font-style: normal;">static</span> <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 26px;text-decoration: none;font-weight: 400;font-style: normal;">void</span> <span class="hljs-default-title" style="color: rgb(97, 174, 238);background: rgba(0, 0, 0, 0);display: inline;width: 26px;text-decoration: none;font-weight: 400;font-style: normal;">main</span>(<span class="hljs-default-params" style="color: rgb(171, 178, 191);background: rgba(0, 0, 0, 0);display: inline;width: 85px;text-decoration: none;font-weight: 400;font-style: normal;">String[] args</span>) </span>{<br mpa-from-tpl="t">&nbsp; &nbsp; final SynchronizedTest test = <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 19px;text-decoration: none;font-weight: 400;font-style: normal;">new</span> SynchronizedTest();<br mpa-from-tpl="t"><br mpa-from-tpl="t"> <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 19px;text-decoration: none;font-weight: 400;font-style: normal;">&nbsp; &nbsp; new</span> Thread(<span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 20px;text-decoration: none;font-weight: 400;font-style: normal;">new</span> Runnable() {<br mpa-from-tpl="t">&nbsp; &nbsp; &nbsp; @<span class="hljs-default-function" style="color: rgb(171, 178, 191);background: rgba(0, 0, 0, 0);display: inline;width: 197px;text-decoration: none;font-weight: 400;font-style: normal;">Override<br mpa-from-tpl="t"> <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 40px;text-decoration: none;font-weight: 400;font-style: normal;">&nbsp; &nbsp; &nbsp; public</span> <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 27px;text-decoration: none;font-weight: 400;font-style: normal;">void</span> <span class="hljs-default-title" style="color: rgb(97, 174, 238);background: rgba(0, 0, 0, 0);display: inline;width: 20px;text-decoration: none;font-weight: 400;font-style: normal;">run</span>(<span class="hljs-default-params" style="color: rgb(171, 178, 191);background: rgba(0, 0, 0, 0);display: inline;width: 0px;text-decoration: none;font-weight: 400;font-style: normal;"></span>) </span>{<br mpa-from-tpl="t">&nbsp; &nbsp; &nbsp; &nbsp; test.method1();<br mpa-from-tpl="t">&nbsp; &nbsp; &nbsp; }<br mpa-from-tpl="t">&nbsp; &nbsp; }).start();<br mpa-from-tpl="t"><br mpa-from-tpl="t"> <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 19px;text-decoration: none;font-weight: 400;font-style: normal;">&nbsp; &nbsp; new</span> Thread(<span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 20px;text-decoration: none;font-weight: 400;font-style: normal;">new</span> Runnable() {<br mpa-from-tpl="t">&nbsp; &nbsp; &nbsp; @<span class="hljs-default-function" style="color: rgb(171, 178, 191);background: rgba(0, 0, 0, 0);display: inline;width: 197px;text-decoration: none;font-weight: 400;font-style: normal;">Override<br mpa-from-tpl="t"> <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 40px;text-decoration: none;font-weight: 400;font-style: normal;">&nbsp; &nbsp; &nbsp; public</span> <span class="hljs-default-keyword" style="color: rgb(198, 120, 221);background: rgba(0, 0, 0, 0);display: inline;width: 27px;text-decoration: none;font-weight: 400;font-style: normal;">void</span> <span class="hljs-default-title" style="color: rgb(97, 174, 238);background: rgba(0, 0, 0, 0);display: inline;width: 20px;text-decoration: none;font-weight: 400;font-style: normal;">run</span>(<span class="hljs-default-params" style="color: rgb(171, 178, 191);background: rgba(0, 0, 0, 0);display: inline;width: 0px;text-decoration: none;font-weight: 400;font-style: normal;"></span>) </span>{<br mpa-from-tpl="t">&nbsp; &nbsp; &nbsp; &nbsp; test.method2();<br mpa-from-tpl="t">&nbsp; &nbsp; &nbsp; }<br mpa-from-tpl="t">&nbsp; &nbsp; }).start();<br mpa-from-tpl="t">&nbsp; }<br mpa-from-tpl="t">}</p></pre> </section> <p><span class="cnblogs_code_copy" style="padding-right: 5px;line-height: 1.5 !important;"></span><br></p> <p style="margin: 10px auto;line-height: 2em;"><span style="line-height: 1.5;font-size: 15px;">执行结果如下，线程1和线程2同时进入执行状态，线程2执行速度比线程1快，所以线程2先执行完成，这个过程中线程1和线程2是同时执行的。</span></p> <table cellpadding="0" cellspacing="0" width="848" height="NaN" style="width: 556px;"> <tbody style="border-radius: 0px !important;background: none !important;border-width: 0px !important;border-style: initial !important;border-color: initial !important;bottom: auto !important;float: none !important;height: auto !important;left: auto !important;line-height: 1.1em !important;outline: 0px !important;overflow: visible !important;right: auto !important;top: auto !important;vertical-align: baseline !important;width: auto !important;min-height: auto !important;"> <tr style="border-radius: 0px !important;background: none !important;border-width: 0px !important;border-style: initial !important;border-color: initial !important;bottom: auto !important;float: none !important;height: auto !important;left: auto !important;line-height: 1.1em !important;outline: 0px !important;overflow: visible !important;right: auto !important;top: auto !important;vertical-align: baseline !important;width: auto !important;min-height: auto !important;" class="firstRow"> <td class="code" style="padding: 8px 14px;border-color: silver;border-collapse: collapse;border-radius: 0px !important;background: none !important;bottom: auto !important;float: none !important