搜索到
15
篇与
MySQL
的结果
-
MySQL-隐式类型转换可能导致索引失效以及可能的查询数据异常 MySQL-隐式类型转换导致索引失效以及可能的查询数据异常前言数据库优化是一个任重而道远的任务，想要做优化必须深入理解数据库的各种特性。在开发过程中我们经常会遇到一些原因很简单但造成的后果却很严重的疑难杂症，这类问题往往还不容易定位，排查费时费力最后发现是一个很小的疏忽造成的，又或者是因为不了解某个技术特性产生的。于数据库层面，最常见的恐怕就是索引失效了，且一开始因为数据量小还不易被发现。但随着业务的拓展数据量的提升，性能问题慢慢的就体现出来了，处理不及时还很容易造成雪球效应，最终导致数据库卡死甚至瘫痪。造成索引失效的原因可能有很多种，相关技术博客已经有太多了，今天我要记录的是隐式转换造成的索引失效。数据准备首先使用存储过程生成 1000 万条测试数据， 测试表一共建立了 7 个字段（包括主键），num_int和num_str保存的是和ID一样的顺序数字，其中num_str是字符串类型。 type1和type2保存的都是主键对 5 的取模，目的是模拟实际应用中常用类似 type 类型的数据，但是type2是没有建立索引的。 str1和str2都是保存了一个 20 位长度的随机字符串，str1不能为NULL，str2允许为NULL，相应的生成测试数据的时候我也会在str2字段生产少量NULL值（每 100 条数据产生一个NULL值）。-- 创建测试数据表 DROP TABLE IF EXISTS `test1`; CREATE TABLE `test1` ( `id` int NOT NULL, `num_int` int NOT NULL DEFAULT 0, `num_str` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', `type1` int NOT NULL DEFAULT 0, `type2` int NOT NULL DEFAULT 0, `str1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', `str2` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_num_int`(`num_int` ASC) USING BTREE, INDEX `idx_num_str`(`num_str` ASC) USING BTREE, INDEX `idx_type1`(`type1` ASC) USING BTREE, INDEX `idx_str1`(`str1` ASC) USING BTREE, INDEX `idx_str2`(`str2` ASC) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- 创建存储过程 DROP PROCEDURE IF EXISTS pre_test1; DELIMITER; CREATE PROCEDURE `pre_test1`() BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; WHILE i < 10000000 DO SET i = i + 1; SET @str1 = SUBSTRING(MD5(RAND()),1,20); -- 每100条数据str2产生一个null值 IF i % 100 = 0 THEN SET @str2 = NULL; ELSE SET @str2 = @str1; END IF; INSERT INTO test1 (`id`, `num_int`, `num_str`, `type1`, `type2`, `str1`, `str2`) VALUES (CONCAT('', i), CONCAT('', i), CONCAT('', i), i%5, i%5, @str1, @str2); -- 事务优化，每一万条数据提交一次事务 IF i % 10000 = 0 THEN COMMIT; END IF; END WHILE; END; DELIMITER; -- 执行存储过程 CALL pre_test1();数据量比较大，还涉及使用MD5生成随机字符串，所以速度有点慢，稍安勿躁，耐心等待即可。1000 万条数据，30分钟左右才跑完（实际时间跟你电脑硬件配置有关）。这里贴几条生成的数据，大致长这样。SQL 测试先来看这组 SQL，一共四条，我们的测试数据表num1是int类型，num2是varchar类型，但是存储的数据都是跟主键id一样的顺序数字，两个字段都建立有索引。SELECT * FROM `test1` WHERE num_int = 10000; SELECT * FROM `test1` WHERE num_int = '10000'; SELECT * FROM `test1` WHERE num_str = 10000; SELECT * FROM `test1` WHERE num_str = '10000';这四条 SQL 都是有针对性写的，1、2 查询的字段是 int 类型，3、4 查询的字段是varchar类型。1、2 或 3、4 查询的字段虽然都相同，但是一个条件是数字，一个条件是用引号引起来的字符串。这样做有什么区别呢？先不看下边的测试结果你能猜出这四条 SQL 的效率顺序吗？经测试这四条 SQL 最后的执行结果却相差很大，其中 124 三条 SQL 基本都是瞬间出结果，大概在 0.01~0.05 秒，在千万级的数据量下这样的结果可以判定这三条 SQL 性能基本没差别了。但是第三条 SQL，多次测试耗时基本在 4.5~4.8 秒之间。为什么 34 两条 SQL 效率相差那么大，但是同样做对比的 12 两条 SQL 却没什么差别呢？查看一下执行计划，下边分别 1234 条 SQL 的执行计划数据：可以看到，124 三条 SQL 都能使用到索引，连接类型都为ref，扫描行数都为 1，所以效率非常高。再看看第三条 SQL，没有用上索引，所以为全表扫描，rows直接到达 1000 万了，所以性能差别才那么大。仔细观察你会发现，34 两条 SQL 查询的字段num2是varchar类型的，查询条件等号右边加引号的第 4 条 SQL 是用到索引的，那么是查询的数据类型和字段数据类型不一致造成的吗？如果是这样那 12 两条 SQL 查询的字段num1是int类型，但是第 2 条 SQL 查询条件右边加了引号为什么还能用上索引呢。查阅 MySQL 相关文档发现是隐式转换造成的隐式转换MySQL 8.0 的官方文档：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html，以下规则描述了比较操作如何进行转换：如果一个或两个参数是NULL，则比较的结果是NULL，但NULL-safe <=> 相等比较运算符除外。对于NULL <=> NULL，结果为真。无需转换。如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较。如果两个参数都是整数，则将它们作为整数进行比较。如果不与数字比较，十六进制值将被视为二进制字符串。如果其中一个参数是 a TIMESTAMP或 DATETIME列，而另一个参数是常量，则在执行比较之前将常量转换为时间戳。这样做是为了对 ODBC 更友好。这不适用于 的参数 IN()。为了安全起见，在进行比较时，请始终使用完整的日期时间、日期或时间字符串。例如，要在使用 BETWEEN日期或时间值时获得最佳结果，请使用CAST()将值显式转换为所需的数据类型。来自一个或多个表的单行子查询不被视为常量。例如，如果子查询返回要与值进行比较的整数DATETIME ，则比较将作为两个整数进行。整数不会转换为时间值。要将操作数作为 DATETIME值进行比较，请使用 CAST()将子查询值显式转换为DATETIME.如果其中一个参数是十进制值，则比较取决于另一个参数。如果另一个参数是十进制或整数值，则将参数作为十进制值进行比较，如果另一个参数是浮点值，则将其作为浮点值进行比较。在所有其他情况下，参数将作为浮点（双精度）数字进行比较。例如，字符串和数字操作数的比较是作为浮点数的比较进行的。按照上述规则的最后一条，我们的查询SQL中，字符串与整数的比较会被转换成两个浮点数比较，左边是字符串类型 "1" 转换成浮点数为1.0，右边 INT类型的 1 转换成浮点数 1.0 。根据官方文档的描述，我们的第 23 两条 SQL 都发生了隐式转换，第 2 条 SQL 的查询条件num1 = '10000'，左边是int类型右边是字符串，第 3 条 SQL 相反，那么根据官方转换规则第 7 条，左右两边都会转换为浮点数再进行比较。先看第 2 条 SQL：SELECT * FROMtest1WHERE num1 = '10000'; 左边为 int 类型10000，转换为浮点数还是10000，右边字符串类型'10000'，转换为浮点数也是10000。两边的转换结果都是唯一确定的，所以不影响使用索引。第 3 条 SQL：SELECT * FROMtest1WHERE num2 = 10000; 左边是字符串类型'10000'，转浮点数为 10000 是唯一的，右边int类型10000转换结果也是唯一的。但是，因为左边是检索条件，'10000'转到10000虽然是唯一，但是其他字符串也可以转换为10000，比如'10000a'，'010000'，'10000'等等都能转为浮点数10000，这样的情况下，是不能用到索引的。关于这个隐式转换我们可以通过查询测试验证一下，先插入几条数据，其中num2='10000a'、'010000'和'10000'：INSERT INTO `test1` (`id`, `num_int`, `num_str`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000001', '10000', '10000a', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523'); INSERT INTO `test1` (`id`, `num_int`, `num_str`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000002', '10000', '010000', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523'); INSERT INTO `test1` (`id`, `num_int`, `num_str`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000003', '10000', ' 10000', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');然后使用第三条 SQL 语句SELECT * FROM test1 WHERE num_str = 10000;进行查询：从结果可以看到，后面插入的三条数据也都匹配上了。那么这个字符串隐式转换的规则是什么呢？为什么num_str='10000a'、'010000'和'10000'这三种情形都能匹配上呢？查阅相关资料发现规则如下：不以数字开头的字符串都将转换为0。如'abc'、'a123bc'、'abc123'都会转化为0；以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止。比如'123a'会转换为123，'0123abc'会转换为0123也就是123，'03.8abc'会转换为3.8，其他同理。现对以上规则做如下测试验证：SELECT 123 = '123a'; # 1 SELECT 123 = '0123a'; # 1 SELECT 3.8 = '03.8abc'; # 1 SELECT -3.80 = '-03.8000abc'; # 1如此也就印证了之前的查询结果了。再次写一条 SQL 查询 str1 字段：SELECT * FROM test1 WHERE str1 = 1234;分析和总结通过上面的测试我们发现 MySQL 使用操作符的一些特性：当操作符左右两边的数据类型不一致时，会发生隐式转换。当查询字段为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。当查询字段为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。所以，我们在写 SQL 时一定要养成良好的习惯，查询的字段是什么类型，等号右边的条件就写成对应的类型。特别当查询的字段是字符串时，等号右边的条件一定要用引号引起来标明这是一个字符串，否则会造成索引失效触发全表扫描1、索引列是字符串时，如果传入的条件参数是整数，会先转换成浮点数，再全表扫描，导致索引失效；2、条件参数要尽可能与列的类型相同，避免隐式转换，或者把传入的条件参数转换成索引列的类型。参考阅读MySQL 5.7官方文档： https://dev.mysql.com/doc/refman/5.7/en/type-conversion.htmlMySQL 8.0 的官方文档：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html浅析 MySQL 的隐式转换：https://xiaomi-info.github.io/2019/12/24/mysql-implicit-conversion/MySQL-隐式类型转换导致索引失效 https://mp.weixin.qq.com/s/DGEvqiHPhf3BrutgheC4WgMySQL性能优化：MySQL中的隐式转换造成的索引失效 https://www.guitu18.com/post/2019/11/24/61.html附录1、MySQL 中字符串转浮点型时的规则如下：不以数字开头的字符串都将转换为0：SELECT CAST('abc' AS UNSIGNED) -----------------------+ 0|以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止：SELECT CAST(' 0123abc' AS UNSIGNED) ----------------------------+ 123| -
查看Navicat保存的密码 使用步骤查找(导出)密码Navicat（11.2.7、12.1.15、15.1.17、16.0.6上述版本均已通过测试）1.1. 注册表使用【Win】+ 【R】组合快捷键，快速打开运行命令框，在打开后面键入命令：【Regedit】打开注册表编辑器路径计算机HKEY_CURRENT_USERSOFTWAREPremiumSoftNavicatServers\ 以Navicat16为例，通过上方路径，找到注册表中存储密码值的位置（如下图），选中要查看密码的连接名称，双击Pwd项，复制对应的值，至此获得了密文! 1.2. Navicat导出（推荐） 以Navicat16为例，菜单栏上点击文件，选择导出连接...，一定要选中导出密码！！！导出格式为*.ncx（该ncx本质上是xml文件，文件中包含连接的全部信息）注：Navicat11版本没有勾选导出密码选项，直接导出即可；Navicat12+版本要勾选【导出密码】选项！在线运行解密https://www.nhooo.com/tool/java8/复制粘贴一下Java解密代码 import javax.crypto.Cipher; import javax.crypto.spec.IvParameterSpec; import javax.crypto.spec.SecretKeySpec; import javax.xml.bind.DatatypeConverter; import java.nio.charset.StandardCharsets; import java.security.MessageDigest; import java.util.Arrays; public class Main { public static void main(String []args) { //navicat11解密 Navicat11Cipher de = new Navicat11Cipher(); System.out.println(de.decryptString("15057D7BA390")); //navicat12+解密 Navicat12Cipher de12 = new Navicat12Cipher(); System.out.println(de12.decryptString("503AA930968F877F04770B47DD731DC0")); } static class Navicat11Cipher { public static final String DefaultUserKey = "3DC5CA39"; private static byte[] _IV; private static SecretKeySpec _Key; private static Cipher _Encryptor; private static Cipher _Decryptor; private static void initKey(String UserKey) { try { MessageDigest sha1 = MessageDigest.getInstance("SHA1"); byte[] userkey_data = UserKey.getBytes(StandardCharsets.UTF_8); sha1.update(userkey_data, 0, userkey_data.length); _Key = new SecretKeySpec(sha1.digest(), "Blowfish"); } catch (Exception e) { e.printStackTrace(); } } private static void initChiperEncrypt() { try { // Must use NoPadding _Encryptor = Cipher.getInstance("Blowfish/ECB/NoPadding"); _Encryptor.init(Cipher.ENCRYPT_MODE, _Key); } catch (Exception e) { e.printStackTrace(); } } private static void initChiperDecrypt() { try { // Must use NoPadding _Decryptor = Cipher.getInstance("Blowfish/ECB/NoPadding"); _Decryptor.init(Cipher.DECRYPT_MODE, _Key); } catch (Exception e) { e.printStackTrace(); } } private static void initIV() { try { byte[] initVec = DatatypeConverter.parseHexBinary("FFFFFFFFFFFFFFFF"); _IV = _Encryptor.doFinal(initVec); } catch (Exception e) { e.printStackTrace(); } } private void xorBytes(byte[] a, byte[] b) { for (int i = 0; i < a.length; i++) { int aVal = a[i] & 0xff; // convert byte to integer int bVal = b[i] & 0xff; a[i] = (byte) (aVal ^ bVal); // xor aVal and bVal and typecast to byte } } private void xorBytes(byte[] a, byte[] b, int l) { for (int i = 0; i < l; i++) { int aVal = a[i] & 0xff; // convert byte to integer int bVal = b[i] & 0xff; a[i] = (byte) (aVal ^ bVal); // xor aVal and bVal and typecast to byte } } static { initKey(DefaultUserKey); initChiperEncrypt(); initChiperDecrypt(); initIV(); } private byte[] Encrypt(byte[] inData) { try { byte[] CV = Arrays.copyOf(_IV, _IV.length); byte[] ret = new byte[inData.length]; int blocks_len = inData.length / 8; int left_len = inData.length % 8; for (int i = 0; i < blocks_len; i++) { byte[] temp = Arrays.copyOfRange(inData, i * 8, (i * 8) + 8); xorBytes(temp, CV); temp = _Encryptor.doFinal(temp); xorBytes(CV, temp); System.arraycopy(temp, 0, ret, i * 8, 8); } if (left_len != 0) { CV = _Encryptor.doFinal(CV); byte[] temp = Arrays.copyOfRange(inData, blocks_len * 8, (blocks_len * 8) + left_len); xorBytes(temp, CV, left_len); System.arraycopy(temp, 0, ret, blocks_len * 8, temp.length); } return ret; } catch (Exception e) { e.printStackTrace(); return null; } } public String encryptString(String inputString) { try { byte[] inData = inputString.getBytes(StandardCharsets.UTF_8); byte[] outData = Encrypt(inData); return DatatypeConverter.printHexBinary(outData); } catch (Exception e) { e.printStackTrace(); return ""; } } private byte[] Decrypt(byte[] inData) { try { byte[] CV = Arrays.copyOf(_IV, _IV.length); byte[] ret = new byte[inData.length]; int blocks_len = inData.length / 8; int left_len = inData.length % 8; for (int i = 0; i < blocks_len; i++) { byte[] temp = Arrays.copyOfRange(inData, i * 8, (i * 8) + 8); temp = _Decryptor.doFinal(temp); xorBytes(temp, CV); System.arraycopy(temp, 0, ret, i * 8, 8); for (int j = 0; j < CV.length; j++) { CV[j] = (byte) (CV[j] ^ inData[i * 8 + j]); } } if (left_len != 0) { CV = _Encryptor.doFinal(CV); byte[] temp = Arrays.copyOfRange(inData, blocks_len * 8, (blocks_len * 8) + left_len); xorBytes(temp, CV, left_len); for (int j = 0; j < temp.length; j++) { ret[blocks_len * 8 + j] = temp[j]; } } return ret; } catch (Exception e) { e.printStackTrace(); return null; } } public String decryptString(String hexString) { try { byte[] inData = DatatypeConverter.parseHexBinary(hexString); byte[] outData = Decrypt(inData); return new String(outData, StandardCharsets.UTF_8); } catch (Exception e) { e.printStackTrace(); return ""; } } } static class Navicat12Cipher { private static SecretKeySpec _AesKey; private static IvParameterSpec _AesIV; static { _AesKey = new SecretKeySpec("libcckeylibcckey".getBytes(StandardCharsets.UTF_8), "AES"); _AesIV = new IvParameterSpec("libcciv libcciv ".getBytes(StandardCharsets.UTF_8)); } public String encryptString(String plaintext) { try { Cipher cipher = Cipher.getInstance("AES/CBC/PKCS5Padding"); cipher.init(Cipher.ENCRYPT_MODE, _AesKey, _AesIV); byte[] ret = cipher.doFinal(plaintext.getBytes(StandardCharsets.UTF_8)); return DatatypeConverter.printHexBinary(ret); } catch (Exception e) { e.printStackTrace(); return ""; } } public String decryptString(String ciphertext) { try { Cipher cipher = Cipher.getInstance("AES/CBC/PKCS5Padding"); cipher.init(Cipher.DECRYPT_MODE, _AesKey, _AesIV); byte[] ret = cipher.doFinal(DatatypeConverter.parseHexBinary(ciphertext)); return new String(ret, StandardCharsets.UTF_8); } catch (Exception e) { e.printStackTrace(); return ""; } } } } 总结项目源码：github : https://github.com/Zhuoyuan1/navicat_password_decrypt如果github打不开，码云地址：https://gitee.com/lzy549876/navicat_password_decrypt 另外网上大多数流传的都是PHP语言版本的，而本人是一名JAVA开发者，故做出了这一工具，希望能帮助到大家！ 声明：该工具源码仅仅只作学术性研究，不可商业用途！参考 https://blog.csdn.net/kkk123445/article/details/122514124
-
MybatisPlus拦截器打印完整SQL、分页、自动填充处理 MybatisPlus拦截器打印完整SQLMybatisPlus 虽然也自带了一个打印 SQL 的配置，但是不方便查看，也没有时间统计mybatis-plus: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl拦截器PrintSqlInterceptor/** * @author CoderKK * @date 2020-09-01 00:13 */ @Slf4j @Intercepts({ @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}), @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})}) public class PrintSqlInterceptor implements Interceptor { @Override public Object intercept(Invocation invocation) throws Throwable { MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0]; Object parameter = null; if (invocation.getArgs().length > 1) { parameter = invocation.getArgs()[1]; } String sqlId = mappedStatement.getId(); BoundSql boundSql = mappedStatement.getBoundSql(parameter); Configuration configuration = mappedStatement.getConfiguration(); long start = System.currentTimeMillis(); Object returnValue = invocation.proceed(); long time = System.currentTimeMillis() - start; showSql(configuration, boundSql, time, sqlId); return returnValue; } private static void showSql(Configuration configuration, BoundSql boundSql, long time, String sqlId) { Object parameterObject = boundSql.getParameterObject(); List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); //替换空格、换行、tab缩进等 String sql = boundSql.getSql().replaceAll("[\\s]+", " "); if (parameterMappings.size() > 0 && parameterObject != null) { TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { sql = sql.replaceFirst("\\?", getParameterValue(parameterObject)); } else { MetaObject metaObject = configuration.newMetaObject(parameterObject); for (ParameterMapping parameterMapping : parameterMappings) { String propertyName = parameterMapping.getProperty(); if (metaObject.hasGetter(propertyName)) { Object obj = metaObject.getValue(propertyName); sql = sql.replaceFirst("\\?", getParameterValue(obj)); } else if (boundSql.hasAdditionalParameter(propertyName)) { Object obj = boundSql.getAdditionalParameter(propertyName); sql = sql.replaceFirst("\\?", getParameterValue(obj)); } } } } logs(time, sql, sqlId); } private static String getParameterValue(Object obj) { String value; if (obj instanceof String) { value = "'" + obj + "'"; } else if (obj instanceof Date) { DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA); value = "'" + formatter.format(new Date()) + "'"; } else { if (obj != null) { value = obj.toString(); } else { value = ""; } } return value.replace("$", "\\$"); } private static void logs(long time, String sql, String sqlId) { StringBuilder sb = new StringBuilder() .append(" Time：").append(time) .append(" ms - ID：").append(sqlId) .append(StringPool.NEWLINE).append("Execute SQL：") .append(sql).append(StringPool.NEWLINE); log.info(sb.toString()); } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties0) { } }MybatisPlus配置 MybatisPlusConfigpackage com.example.demojs.config; import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer; import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor; import com.example.demojs.common.PrintSqlInterceptor; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import java.util.Properties; @Configuration public class MybatisPlusConfig { /** * mybatis-plus分页插件 */ /* // 旧版 @Bean public PaginationInterceptor paginationInterceptor() { PaginationInterceptor paginationInterceptor = new PaginationInterceptor(); // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求 默认false // paginationInterceptor.setOverflow(false); // 设置最大单页限制数量，默认 500 条，-1 不受限制 // paginationInterceptor.setLimit(500); // 开启 count 的 join 优化,只针对部分 left join paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true)); return paginationInterceptor; } // 新版 @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return interceptor; } // 如何区分新旧版呢？其实你把这两种代码都复制到你的项目里，哪个类存在就用哪个方法。 // 3.4.0版本对此部分有更新，如果是旧版本升级，会出现分页失效问题，同时idea会提示PaginationInterceptor过时，新版本改用了MybatisPlusInterceptor */ /** *自定义mybatis插件 注入方式一 */ /* @Bean public MybatisPlusInterceptor mybatisInterceptor() { MybatisPlusInterceptor mybatisInterceptor = new MybatisPlusInterceptor(); Properties properties = new Properties(); properties.setProperty("name", name); mybatisInterceptor.setProperties(properties); return mybatisInterceptor; } */ /** *自定义mybatis插件 注入方式二 */ @Bean public ConfigurationCustomizer configurationCustomizer() { return configuration -> { //插件拦截链采用了责任链模式，执行顺序和加入连接链的顺序有关 //MybatisInterceptor mybatisInterceptor = new MybatisInterceptor(); MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor(); //设置参数，比如阈值等，可以在配置文件中配置 Properties properties = new Properties(); //properties.setProperty("name", name); mybatisPlusInterceptor.setProperties(properties); //自定义打印SQL PrintSqlInterceptor printSqlInterceptor = new PrintSqlInterceptor(); configuration.addInterceptor(printSqlInterceptor); }; } /** * mybatis-plus SQL执行效率插件【生产环境可以关闭】 3.3.2版本移除了该功能，3.0.3和3.0.3之前版本支持。 */ /* @Bean public PerformanceInterceptor performanceInterceptor() { return new PerformanceInterceptor(); } */ }打印效果2022-08-29 16:42:03.782 INFO 17476 --- [nio-8080-exec-2] c.e.demojs.common.PrintSqlInterceptor : Time：397 ms - ID：com.example.demojs.dao.UserMapper.insert Execute SQL：INSERT INTO test_user ( id, user_id, phone, birthday, sex ) VALUES ( 1564171509361389569, 'abc', '13988776664', 2022-08-29T16:41:59.514, 1 )自动填充插件/** * @author CoderKK * @date 2020/9/6 15:21 * @desc 自动填充处理器类 */ @Component public class MyMetaObjectHandler implements MetaObjectHandler { /** * 插入时的填充策略 * * @param metaObject */ @Override public void insertFill(MetaObject metaObject) { this.setFieldValByName("createTime", LocalDateTime.now(), metaObject); this.setFieldValByName("updateTime", LocalDateTime.now(), metaObject); } /** * 更新时的填充策略 * * @param metaObject */ @Override public void updateFill(MetaObject metaObject) { this.setFieldValByName("updateTime", LocalDateTime.now(), metaObject); } } 实体类记得加注解 @TableField(value = "create_time", fill = FieldFill.INSERT) @JsonFormat(pattern = "yyyy-MM-dd HH:mm", timezone = "GMT+8") private LocalDateTime createTime; @TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE) @JsonFormat(pattern = "yyyy-MM-dd HH:mm", timezone = "GMT+8") private LocalDateTime updateTime; 分页插件注入分页插件后，如何使用呢？mapper写法：// 这是mapper自己提供的方法，参数是wrapper 适用于单表查 <P extends IPage<T>> P selectPage(P page, @Param("ew") Wrapper<T> queryWrapper); // 自定义sql，适用于多表联查 IPage<UserVO> queryUserList(Page<UserVO> page, @Param("dto") ConditionDTO conditionDTO);**
-
MySQL原理及优化实战-笔记 一、索引的概述1.为什么要使用索引在海量数据中进行查询某条记录的场景是经常发生的，那么如何提升查询性能，就跟要查询的数据字段是否有索引有关系。如果字段加了索引，那么查询的性能就非常快！——就是为了快！索引为什么快？索引到底是什么？在使用索引的是要注意什么样的事项？2.索引是什么查字典的方式？“数”shu--通过目录来查，能够快速的定位到目标数据所在的页码。没有使用索引的时候，数据的查询需要进行多次IO读写，这样的性能较差——全表扫描的过程。为数据库的某个字段创建索引，相当是为这个字段的内容创建了一个目录。通过这个目录可以快速的实现数据的定位，也就是通过索引能够快速的找到某条数据所在磁盘的位置。现在的疑问？索引存放位置索引的分类及如何创建索引使用了哪种数据结构：各种数据结构的查询性能进行分析3.索引存放的位置对于mac系统在/usr/local/mysql文件夹中，对于win系统c:/programdata/mysql(隐藏文件夹)InnoDB存储引擎的表：将索引和数据存放在同一个文件里。（为什么？有什么优势？）*.ibdMyISAM存储引擎的表：索引和数据分开两个文件来存储。索引：*.MYI ; 数据：MYD4.索引的分类主键索引：主键自带索引效果，也就意味着通过主键来查询表中的记录，性能是非常好的。普通索引：为普通列创建的索引。创建索引的命令：# 格式 create index 索引名称 on 表名(列名) # 例子 create index idx_name on employees(name)唯一索引：就像是唯一列，列中的数据是唯一的。比普通索引的性能要好。# 格式 create unique index 索引名称 on 表名(列名) # 例子 create unique index idx_unique_name on employees(name)联合索引（组合索引）：一次性为表中的多个字段一起创建索引，最左前缀法则（如何命中联合索引中的索引列）。注意：一个联合索引建议不要超过5个列# 格式 create index 索引名称 on 表(列1,列2,列3) # 例子 create index idx_name_age_position on employees(name,age,position)全文索引进行查询的时候，数据源可能来自于不同的字段或者不同的表。比如去百度中查询数据，千锋教育，来自于网页的标题或者网页的内容 。MyISAM存储引擎支持全文索引。在实际生产环境中，并不会使用MySQL提供的MyISAM存储引擎的全文索引功能来是实现全文查找。而是会使用第三方的搜索引擎中间件比如ElasticSearch（多）、Solr。二、索引使用的数据结构使用索引查找数据性能很快，避免了全表扫描的多次磁盘IO读写。但是我们发现，使用索引实际上也需要在索引中查找数据，而且数据量是一样的，那么凭什么索引就能快呢？这就跟索引使用了哪种数据结构支持快速查找。什么叫数据结构：存放数据的结构。比如：数组、链表、栈、堆、队列等等这些概念。1.线性表：线性的维护数据的顺序。对于线性表来说，有两种数据结构来支撑：线性顺序表：相邻两个数据的逻辑关系和物理位置是相同的。线性链式表：相邻两个数据的逻辑关系和物理存放位置没有关系。数据是有先后的逻辑关系，但是数据的物理存储位置并不连续。单向链表：能够通过当前结点找到下一个节点的位置，以此来维护链表的逻辑关系结点结构： 数据内容+下一个数据的指针双向链表：能够通过当前结点找到上一个或下一个节点的位置，双向都可找。结点结构： 上一个数据的指针+数据内容+下一个数据的指针顺序表和链式表的区别：- 数组：进行数据的查询性能（可以通过数组的索引/下标） ：时间复杂度（比较次数）/空间复杂度（算法需要使用多少个变量空间） 数组的查询性能非常好： 时间复杂度是O(1)。 数组的增删性能是非常差的：- 链表：查询的性能是非常差的： 时间复杂度是O(n)。 增删性能是非常好的：2.栈、队列、串、广义表栈：先进后出，有顺序栈、链式栈队列：先进先出，有顺序队列、链式队列串：String 定长串、StringBuffer/Stringbuilder动态串广义表：更加灵活的多维数组，可以在不同的元素中创建不同的维度的数组。3.树查找树的查找性能是明显比线性表的性能要好，那么接下来我们就要学习这么几种树：1）多叉树非二叉树2）二叉树一个结点最多只能有2个子结点，可以是0、1、2子结点。3）二叉查找树二叉查找树的查找性能是ok的，查询性能跟树的高度有关，树的高度又根你插入数据的顺序有关系。特点：二叉树的根结点的数值是比所有左子树的结点的数值大，比右子树的几点的数值小。这样的规律同样满足于他的所有子树。4）平衡二叉树（理想概念的树）我们知道二叉查找树不能非常智能的维护树的高度，因此二叉查找树在某些情况下查询性能是不ok的，此时平衡二叉树就出现了。特点： 平衡二叉树中的树及其所有子树都应满足：左子树和右子树的深度差不能超过1如果平衡二叉树不满足这个特点，那么平衡二叉树要进行自己旋转，如何自己旋转：左旋、右旋、双向（先左后右、先右后左）5）红黑树（平衡二叉树的一种体现）平衡二叉树为了维护树的平衡，在一旦不满足平衡的情况就要进行自旋，但是自旋会造成一定的系统开销。因此红黑树在自旋造成的系统开销和减少查询次数之间做了权衡。因此红黑树有时候并不是一颗平衡二叉树。红黑树已经是在查询性能上得到了优化，但索引依然没有使用红黑树作为数据结构来存储数据，因为红黑树在每一层上存放的数据内容是有限的，导致数据量一大，树的深度就变得非常大，于是查询性能非常差。因此索引没有使用红黑树。6）B树B树允许一个结点存放多个数据。这样可以使更小的树的深度来存放更多的数据。但是，B树的一个结点中到底能存放多少个数据，决定了树的深度。通过数值计算，B树的一个结点最多只能存放15个数据，因此B树依然不能满足海量数据的查询性能优化。7）B+树B+树的特点：非叶子结点冗余了叶子结点中的键。叶子结点是从小到大、从左到右排列的叶子结点之间提供了指针，提高了区间访问的性能只有叶子结点存放数据，非叶子结点是不存放数据的，只存放键8）哈希表使用哈希表来存取数据的性能是最快的，O(1)，但是不支持范围查找（区间访问）三、InnoDB和MyISAM的区别InnoDB和MyISAM都是数据库表的存储引擎。那么在互联网公司，或者追求查询性能的场景下，都会使用InnoDB作为表的存储引擎。为什么？1.InnoDB引擎——聚集索引把索引和数据存放在一个文件中，通过找到索引后就能直接在索引树上的叶子结点中获得完整的数据。可以实现行锁/表锁2.MyISAM存储引擎——非聚集索引把索引和数据存放在两个文件中，查找到索引后还要去另一个文件中找数据，性能会慢一些。除此之外，MyISAM天然支持表锁，而且支持全文索引。四、索引常见的面试题1.问题一：为什么非主键索引的叶子节点存放的数据是主键值如果普通索引中不存放主键，而存放完整数据，那么就会造成：数据冗余，虽然提升了查询性能，但是需要更多的空间来存放冗余的数据维护麻烦：一个地方修改数据，需要在多棵索引树上修改。2.问题二：为什么InnoDB表必须创建主键创建InnoDB表不使用主键能创建成功吗？如果能创建功能，能不能为这张表的普通列创建索引？如果没有主键，MySQL优化器会给一个虚拟的主键，于是普通索引会使用这个虚拟主键——也会造成性能开销。为了性能考虑，和设计初衷，那么创建表的时候就应该创建主键。3.问题三：为什么使用主键时推荐使用整型的自增主键1）为什么要使用整型：主键-主键索引树-树里的叶子结点和非叶子结点的键存放的是主键的值，而且这颗树是一个二叉查找树。数据的存放是有大小顺序的。整型： 大小顺序是很好比较的字符串：字符串的自然顺序的比较是要进行一次编码成为数值后再进行比较的。（字符串的自然顺序，A Z）uuid随机字符串2）为什么要自增：如果不用自增： （10 1 6。 200。 18。29）使用不规律的整数来作为主键，那么主键索引树会使用更多的自旋次数来保证树索引树的叶子节点中的数据是从小到大-从左到右排列，因此性能必然比使用了自增主键的性能要差！五、联合索引和最左前缀法则1.联合索引的特点在使用一个索引来实现多个表中字段的索引效果。2.联合索引是如何存储的3.最左前缀法则最左前缀法则是表示一条sql语句在联合索引中有没有走索引（命中索引/不会全表扫描）# 创建联合索引 create index idx_a_b_c on table1(a,b,c); # sql语句有没有命中索引 select * from table1 where a = 10; select * from table1 where a = 10 and b=20; select * from table1 where a = 10 and b=20 and c=30; select * from table1 where b = 10; select * from table1 where b = 10 and c=30; select * from table1 where a = 10 and c=30; select * from table1 where c = 30; select * from table1 where a = 10 and c = 30 and b = 20; (abc全走)=》mysql有一个内部优化器 会做一次内部优化。 六、SQL优化SQL优化的目的是为了SQL语句能够具备优秀的查询性能，实现这样的目的有很多的途径：工程优化如何实现：数据库标准、表的结构标准、字段的标准、创建索引SQL语句的优化：当前SQL语句有没有命中索引。1.工程优化如何实现参考《MySQL军规升级版》2.Explain执行计划——SQL优化神器得知道当前系统里有哪些SQL是慢SQL，查询性能超过1s的sql，然后再通过Explain工具可以对当前SQL语句的性能进行判断——为什么慢，怎么解决。要想知道哪些SQL是慢SQL，有两种方式，一种是开启本地MySQL的慢查询日志；另一种是阿里云提供的RDS（第三方部署的MySQL服务器），提供了查询慢SQL的功能。explain SELECT * from employees where name like "customer100%"通过在SQL语句前面加上explain关键字，执行后并不会真正的执行sql语句本身，而是通过explain工具来分析当前这条SQL语句的性能细节：比如是什么样的查询类型、可能用到的索引及实际用到的索引，和一些额外的信息。3.MySQL的内部优化器在SQL查询开始之前，MySQL内部优化器会进行一次自我优化，让这一次的查询性能尽可能的好。当前执行的SQLexplain select * from tb_book where id=1; show warnings;内部优化器优化后的效果：/* select#1 */ select '1' AS `id`,'千锋Java厉害' AS `name` from `db_mysql_pro`.`tb_book` where true4.select_type列关闭 MySQL 对衍生表的合并优化：set session optimizer_switch='derived_merge=off'; 执行了这样的计划：EXPLAIN select (select 1 from tb_author where id=1) from (select * from tb_book where id=1) der;derived：第一条执行的sql是from后面的子查询，该子查询只要在from后面，就会生成一张衍生表，因此他的查询类型：derivedsubquery：在select之后 from之前的子查询primary：最外部的selectsimple：不包含子查询的简单的查询union：使用union进行的联合查询的类型5.table列当前查询正在查哪张表6.type列type列可以直观的判断出当前的sql语句的性能。type里的取值和性能的优劣顺序如下：null > system > const > eq_ref > ref > range > index > all对于SQL优化来说，要尽量保证type列的值是属于range及以上级别。null性能最好的，一般在使用了聚合函数操作索引列，结果直接从索引树获取即可，因此是性能最好。system很少见。直接和一条记录进行匹配。const使用主键索引或唯一索引和常量进行比较，这种性能非常好eq_ref在进行多表连接查询时。如果查询条件是使用了主键进行比较，那么当前查询类型是eq_refEXPLAIN select * from tb_book_author left JOIN tb_book on tb_book_author.book_id = tb_book.idref简单查询：EXPLAIN select * from tb_book where name='book1' 如果查询条件是普通列索引，那么类型ref复杂查询：EXPLAIN select book_id from tb_book left join tb_book_author on tb_book.id = tb_book_author.book_id 如果查询条件是普通列索引，那么类型refrange:使用索引进行范围查找explain select * from tb_book where id>1index查询没有进行条件判断。但是所有的数据都可以直接从索引树上获取(book表中的所有列都有索引)explain select * from tb_bookall没有走索引，进行了全表扫描explain select * from tb_author7.id列在多个select中，id越大越先执行，如果id相同。上面的先执行。8.possible keys列这一次的查询可能会用到的索引。也就是说mysql内部优化器会进行判断，如果这一次查询走索引的性能比全表扫描的性能要查，那么内部优化器就让此次查询进行全表扫描——这样的判断依据我们可以通过trace工具来查看EXPLAIN select * from employees where name like 'custome%'这条sql走索引查询的行数是500多万，那么总的数据行数也就500多万，因此直接进行全表扫描性能更快9.key列实际该sql语句使用的索引10.rows列该sql语句可能要查询的数据条数11.key_len列键的长度，通过这一列可以让我们知道当前命中了联合索引中的哪几列。EXPLAIN select * from employees where name = 'customer10011' # 74 EXPLAIN select * from employees where name = 'customer10011' and age=30 # 74 4 = 78 EXPLAIN select * from employees where name = 'customer10011' and age=30 and position='dev' # 74 4 62 = 140 EXPLAIN select * from employees where name = 'customer10011' and position='dev' # 74name长度是74，也就是当看到key-len是74，表示使用了联合索引中的name列计算规则：- 字符串 1. char(n): n字节长度 2. varchar(n): 2字节存储字符串长度,如果是utf-8,则长度3n + 2 - 数值类型 1. tinyint: 1字节 2. smallint: 2字节 3. int: 4字节 4. bigint: 8字节 - 时间类型 1. date: 3字节 2. timestamp: 4字节 3. datetime: 8字节 如果字段允许为NULL,需要1字节记录是否为NULL 索引最大长度是768字节,当字符串过长时, mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引12.extra列extra列提供了额外的信息，是能够帮助我们判断当前sql的是否使用了覆盖索引、文件排序、使用了索引进行查询条件等等的信息。Using index:使用了覆盖索引所谓的覆盖索引，指的是当前查询的所有数据字段都是索引列，这就意味着可以直接从索引列中获取数据，而不需要进行查表。使用覆盖索引进行性能优化这种手段是之后sql优化经常要用到的。EXPLAIN select book_id,author_id from tb_book_author where book_id = 1 -- 覆盖索引 EXPLAIN select * from tb_book_author where book_id = 1 -- 没有使用覆盖索引using where使用了普通索引列做查询条件EXPLAIN select * from tb_author where name > 'a'using index condition查询结果没有使用覆盖索引，建议可以使用覆盖索引来优化EXPLAIN select * from tb_book_author where book_id > 1Using temporary在非索引列上进行去重操作就需要使用一张临时表来实现，性能是非常差的。当前name列没有索引EXPLAIN select DISTINCT name from tb_authorUsing filesort使用文件排序： 会使用磁盘+内存的方式进行文件排序，会涉及到两个概念：单路排序、双路排序EXPLAIN select * from tb_author order by nameSelect tables optimized away直接在索引列上进行聚合函数的操作，没有进行任何的表的操作EXPLAIN select min(id) from tb_book七、Trace工具在执行计划中我们发现有的sql会走索引，有的sql即使明确使用了索引也不会走索引。这是因为mysql的内部优化器任务走索引的性能比不走索引全表扫描的性能要差，因此mysql内部优化器选择了使用全表扫描。依据来自于trace工具的结论。set session optimizer_trace="enabled=on", end_markers_in_json=on; -- 开启trace select * from employees where name > 'a' order by position; -- 执行查询 SELECT * FROM information_schema.OPTIMIZER_TRACE; -- 获得trace的分析结果{ "steps": [ { "join_preparation": { -- 阶段1:进入到准备阶段 "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { -- 阶段2: 进入到优化阶段 "select#": 1, "steps": [ { "condition_processing": { -- 条件处理 "condition": "WHERE", "original_condition": "(`employees`.`name` > 'a')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`employees`.`name` > 'a')" }, { "transformation": "constant_propagation", "resulting_condition": "(`employees`.`name` > 'a')" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`employees`.`name` > 'a')" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ -- 表依赖详情 { "table": "`employees`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`employees`", "range_analysis": { "table_scan": { "rows": 5598397, "cost": 576657 } /* table_scan */, "potential_range_indexes": [ -- 可能使用到的索引 { "index": "PRIMARY", -- 主键索引 "usable": false, "cause": "not_applicable" }, { "index": "idx_name_age_position", -- 联合索引 "usable": true, "key_parts": [ "name", "age", "position", "id" ] /* key_parts */ }, { "index": "idx_hire_time", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "idx_name_age_position", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { -- 分析各个索引使用的成本 "range_scan_alternatives": [ { "index": "idx_name_age_position", "ranges": [ "a < name" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": true, "index_only": false, -- 是否使用了覆盖索引 "rows": 2799198, -- 要扫描的行数 "cost": 2.08e6, -- 要花费的时间 "chosen": false, -- 是否选择使用这个索引 "cause": "cost" -- 不选择的原因：开销比较大 } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`employees`", "best_access_path": { -- 最优访问路径 "considered_access_paths": [ -- 最后选择的访问路径 { "rows_to_scan": 5598397, -- 全表扫描的行数 "access_type": "scan", -- 全表扫描 "resulting_rows": 5.6e6, -- 结果的行数 "cost": 576655, -- 花费的时间 "chosen": true, -- 选择这种方式 "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 5.6e6, "cost_for_plan": 576655, "sort_cost": 5.6e6, "new_cost_for_plan": 6.18e6, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`employees`.`name` > 'a')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`employees`", "attached": "(`employees`.`name` > 'a')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`employees`.`position`", "items": [ { "item": "`employees`.`position`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`employees`.`position`" } /* simplifying_order_by */ } /* optimizing_distinct_group_by_order_by */ }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ ] /* steps */, "index_order_summary": { "table": "`employees`", "index_provides_order": false, "order_direction": "undefined", "index": "unknown", "plan_changed": false } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, { "finalizing_table_conditions": [ { "table": "`employees`", "original_table_condition": "(`employees`.`name` > 'a')", "final_table_condition ": "(`employees`.`name` > 'a')" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`employees`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ { "adding_sort_to_table": "employees" } /* filesort */ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "employees", "filesort_information": [ { "direction": "asc", "expression": "`employees`.`position`" } ] /* filesort_information */, "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" } /* filesort_priority_queue_optimization */, "filesort_execution": [ ] /* filesort_execution */, "filesort_summary": { "memory_available": 262144, "key_size": 40, "row_size": 190, "max_rows_per_buffer": 1379, "num_rows_estimate": 5598397, "num_rows_found": 5913852, "num_initial_chunks_spilled_to_disk": 1954, "peak_memory_used": 262144, "sort_algorithm": "std::stable_sort", "sort_mode": "<fixed_sort_key, packed_additional_fields>" } /* filesort_summary */ } ] /* steps */ } /* join_execution */ } ] /* steps */ }八、SQL优化实战1.order by优化在排序应用场景中，很容易出现文件排序的问题，文件排序会对性能造成影响，因此需要优化# using filesort Explain select * from employees where name='customer' order by position; # 没有使用文件排序 Explain select * from employees where name='customer' order by age, position; # 不满足最左前缀法则，使用了文件排序 Explain select * from employees where name='customer' order by position, age; # 满足最左前缀法则，使用索引排序 Explain select * from employees where name='customer' and age=20 order by position, age; show WARNINGS; /* select#1 */ select `db_mysql_pro`.`employees`.`id` AS `id`,`db_mysql_pro`.`employees`.`name` AS `name`,`db_mysql_pro`.`employees`.`age` AS `age`,`db_mysql_pro`.`employees`.`position` AS `position`,`db_mysql_pro`.`employees`.`hire_time` AS `hire_time` from `db_mysql_pro`.`employees` where ((`db_mysql_pro`.`employees`.`age` = 20) and (`db_mysql_pro`.`employees`.`name` = 'customer')) order by `db_mysql_pro`.`employees`.`position` # 排序方向不同，没有使用索引排序 Explain select * from employees where name='customer' and age=20 order by age, position desc; # 使用范围查询，使用了文件排序 Explain select * from employees where name in ('customer','aa') order by age, position; # 使用范围查询，使用了文件排序 Explain select * from employees where name > 'a' order by name;优化手段：如果排序的字段创建了联合索引，那么尽量在业务不冲突的情况下，遵循最左前缀法则来写排序语句。如果文件排序没办法避免，那么尽量想办法使用覆盖索引。all->index2.group by优化group by 的原理是先排序后分组，因此对于group by 的优化参考order by3.文件排序的原理在执行文件排序的时候，会把查询的数据的大小与系统变量：max_length_for_sort_data的大小进行比较（默认是1024字节）,如果比系统变量小，那么执行单路排序，反之则执行双路排序单路排序 把所有的数据扔到sort_buffer内存缓冲区中，进行排序，然后结束双路排序取数据的排序字段和主键字段，在内存缓冲区中排序完成后，将主键字段做一次回表查询，获取完整数据。4.分页优化对于这样的优化查询，mysql会把全部的10010数据拿到，并舍弃掉前面的10000条-- 一次行获取10010，再舍弃掉前10000条 Explain select * from employees limit 1000000,10如果在主键连续的情况下，可以使用主键来做条件，但是这种情况是很少见的Explain select * from employees where id>100000 limit 10对于主键不连续情况下的例子：Explain select * from employees order by name limit 1000000,10 -- 通过先进行覆盖索引的查找，然后在使用join做连接查询获取所有数据。这样比全表扫描要快 explain select * from employees a inner join (select id from employees order by name limit 1000000,10) b on a.id = b.id;5.join优化在join中会涉及到大表（数据量大）和小表（数据量小）的概念。MySQL内部优化器会根据关联字段是否创建了索引来使用不同的算法：Nlj(嵌套循环算法)：如果关联字段使用了索引，mysql会对小表做全表扫描，用小表的数据去和大表的数据去做索引字段的关联查询（type：ref）bnlj（块嵌套循环算法）：如果关联字段没有使用索引，mysql会提供一个join buffer缓冲区，先把小表放到缓冲区中，然后全表扫描大表，把大表的数据和缓冲区中的小表数据在内存中进行匹配。结论：使用join查询时，一定要建立关联字段的索引，且两张表的关联字段在设计之初就要做到字段类型、长度是一致的，否则索引失效。6.in和exists优化在sql中如果A表是大表，B表是小表，那么使用in会更加合适。反之应该使用exists。in: B的数据量<A的数据量select * from A where id in (select id from B) # 相当于： for(select id from B){ //B的数据量少，所以循环次数少。 select * from A where A.id = B.id }exists: B的数据量>A的数据量 (10: id 1. 2. 3. 4)select * from A where exists (select 1 from B where B.id = A.id) true / false 等价于 for(select * from A){ select * from B where B.id = A.id }7.count优化对于count的优化应该是架构层面的优化，因为count的统计是在一个产品会经常出现，而且每个用户访问，所以对于访问频率过高的数据建议维护在缓存中。九、锁的定义和分类1.锁的定义锁是用来解决多个任务（线程、进程）在并发访问同一共享资源时带来的数据安全问题。虽然使用锁解决了数据安全问题，但是会带来性能的影响，频繁使用锁的程序的性能是必然很差的。对于数据管理软件MySQL来说，必然会到任务的并发访问。那么MySQL是怎么样在数据安全和性能上做权衡的呢？——MVCC设计思想。2.锁的分类1）从性能上划分：乐观锁和悲观锁悲观锁：悲观的认为当前的并发是非常严重的，所以在任何时候操作都是互斥。保证了线程的安全，但牺牲了并发性。——总有刁民要害朕。乐观锁：乐观的认为当前的并发并不严重，因此对于读的情况，大家都可以进行，但是对于写的情况，再进行上锁。以CAS自旋锁，在某种情况下性能是ok的，但是频繁自旋会消耗很大的资源。——天网恢恢疏而不漏2）从数据的操作细粒度上划分：表锁和行锁表锁：对整张表上锁行锁：对表中的某一行上锁。3）从数据库的操作类型上划分：读锁和写锁这两种锁都是属于悲观锁读锁（共享锁）：对于同一行数据进行”读“来说，是可以同时进行但是写不行。写锁（拍他锁）：在上了写锁之后，及释放写锁之前，在整个过程中是不能进行任何的其他并发操作（其他任务的读和写是都不能进行的）。3.表锁对整张表进行上锁。MyISAM存储引擎是天然支持表锁的，也就是说在MyISAM的存储引擎的表中如果出现并发的情况，将会出现表锁的效果。MyISAM不支持事务。InnoDB支持事务在InnoDB中上一下表锁:# 对一张表上读锁/写锁格式： lock table 表名 read/write; # 例子 lock table tb_book read; # 查看当前会话对所有表的上锁情况 show open tables; # 释放当前会话的所有锁 unlock tables;读锁： 其他任务可以进行读，但是不能进行写写锁：其他任务不能进行读和写。4.行锁MyISAM只支持表锁，但不支持行锁，InnoDB可以支持行锁。在并发事务里，每个事务的增删改的操作相当于是上了行锁。上行锁的方式：update tb_book set name='qfjava2101' where id=8; 对id是8的这行数据上了行锁。select * from tb_book where id=5 for update; 对id是5的这行数据上了行锁。十、MVCC设计思想MySQL为了权衡数据安全和性能，使用了MVCC多版本并发控制的设计。1.事务的特性原子性：一个事务是一个最小的操作单位（原子），多条sql语句在一个事务中要么同时成功，要么同时失败。一致性：事务提交之前和回滚之后的数据是一致的。持久性：事务一旦提交，对数据的影响是持久的。隔离性：多个事务在并发访问下，提供了一套隔离机制，不同的隔离级别会有不同的并发效果。2.事务的隔离级别read uncommitted（读未提交）： 在一个事务中读取到另一个事务还没有提交的数据——脏读。Read committed（读已提交）: 已经解决了脏读问题，在一个事务中只会读取另一个事务已提交的数据，这种情况会出现不可重复读的问题。就是：在事务中重复读数据，数据的内容是不一样的。repeatable read（可重复读）：在一个事务中每次读取的数据都是一致的，不会出现脏读和不可重复读的问题。会出现虚读（幻读）的问题。什么是幻读：解决方案：通过上行锁来解决虚读问题：Serializable:串行化的隔离界别直接不允许事务的并发发生，不存在任何的并发性。相当于锁表，性能非常差，一般都不考虑脏读、不可重复读、虚读（幻读）3.MVCC思想解读MySQL在读和写的操作中，对读的性能做了并发性的保障，让所有的读都是快照读，对于写的时候，进行版本控制，如果真实数据的版本比快照版本要新，那么写之前就要进行版本（快照）更新，这样就可以既能够提高读的并发性，又能够保证写的数据安全。十一、死锁和间隙锁1.死锁所谓的死锁，就是开启的锁没有办法关闭，导致资源的访问因为无法获得锁而处于阻塞状态。演示：事务A和事物B相互持有对方需要的锁而不释放，造成死锁的情况。2.间隙锁行锁只能对某一行上锁，如果相对某一个范围上锁，就可以使用间隙锁。间隙锁给的条件where id>13 and id<19，会对13 和19 所处的间隙进行上锁。
-
