首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

【本人秃顶程序员】开发人员不可错过的MySQL索引和查询优化

作者：细妹很快乐 | 来源：互联网 | 2023-08-07 15:33
←←←←←←←←←←←←快！点关注一、索引相关1.索引基数基数是数据列所包含的不同值的数量。例如，某个数据列包含值1、3、7、4、7、3，

←←←←←←←←←←←← 快&＃xff01;点关注

一、索引相关

1.索引基数

基数是数据列所包含的不同值的数量。例如&＃xff0c;某个数据列包含值1、3、7、4、7、3&＃xff0c;那么它的基数就是4。索引的基数相对于数据表行数较高&＃xff08;也就是说&＃xff0c;列中包含很多不同的值&＃xff0c;重复的值很少&＃xff09;的时候&＃xff0c;它的工作效果最好。如果某数据列含有很多不同的年龄&＃xff0c;索引会很快地分辨数据行。如果某个数据列用于记录性别&＃xff08;只有"M"和"F"两种值&＃xff09;&＃xff0c;那么索引的用处就不大。如果值出现的几率几乎相等&＃xff0c;那么无论搜索哪个值都可能得到一半的数据行。在这些情况下&＃xff0c;最好根本不要使用索引&＃xff0c;因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候&＃xff0c;它一般会忽略索引&＃xff0c;进行全表扫描。惯用的百分比界线是"30%"。

2.索引失效原因

  • 对索引列运算&＃xff0c;运算包括&＃xff08;&＃43;、-、*、/、&＃xff01;、<>、%、like&＃39;%_&＃39;&＃xff08;%放在前面&＃xff09;
  • 类型错误&＃xff0c;如字段类型为varchar&＃xff0c;where条件用number。
  • 对索引应用内部函数&＃xff0c;这种情况下应该建立基于函数的索引 如select * from template t where ROUND(t.logicdb_id) &＃61; 1 此时应该建ROUND(t.logicdb_id)为索引&＃xff0c;mysql8.0开始支持函数索引&＃xff0c;5.7可以通过虚拟列的方式来支持&＃xff0c;之前只能新建一个ROUND(t.logicdb_id)列然后去维护
  • 如果条件有or&＃xff0c;即使其中有条件带索引也不会使用&＃xff08;这也是为什么建议少使用or的原因&＃xff09;&＃xff0c;如果想使用or&＃xff0c;又想索引有效&＃xff0c;只能将or条件中的每个列加上索引
  • 如果列类型是字符串&＃xff0c;那一定要在条件中数据使用引号&＃xff0c;否则不使用索引&＃xff1b;
  • B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
  • 组合索引遵循最左原则

3.索引的建立

  • 最重要的肯定是根据业务经常查询的语句
  • 尽量选择区分度高的列作为索引&＃xff0c;区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率&＃xff0c;比率越大我们扫描的记录数就越少
  • 如果业务中唯一特性最好建立唯一键&＃xff0c;一方面可以保证数据的正确性&＃xff0c;另一方面索引的效率能大大提高

二、EXPLIAN中有用的信息

1.基本用法

  • desc 或者 explain 加上你的sql
  • extended explain加上你的sql&＃xff0c;然后通过show warnings可以查看实际执行的语句&＃xff0c;这一点也是非常有用的&＃xff0c;很多时候不同的写法经过sql分析之后实际执行的代码是一样的

2.提高性能的特性

  • 索引覆盖(covering index)&＃xff1a;需要查询的数据在索引上都可以查到不需要回表 EXTRA列显示using index
  • ICP特性(Index Condition Pushdown)&＃xff1a;本来index仅仅是data access的一种访问模式&＃xff0c;存数引擎通过索引回表获取的数据会传递到MySQL server层进行where条件过滤,5.6版本开始当ICP打开时&＃xff0c;如果部分where条件能使用索引的字段&＃xff0c;MySQL server会把这部分下推到引擎层&＃xff0c;可以利用index过滤的where条件在存储引擎层进行数据过滤。EXTRA显示using index condition。需要了解mysql的架构图分为server和存储引擎层
  • 索引合并(index merge)&＃xff1a;对多个索引分别进行条件扫描&＃xff0c;然后将它们各自的结果进行合并(intersect/union)。一般用OR会用到&＃xff0c;如果是AND条件&＃xff0c;考虑建立复合索引。EXPLAIN显示的索引类型会显示index_merge&＃xff0c;EXTRA会显示具体的合并算法和用到的索引

3.extra字段

  • using filesort&＃xff1a; 说明MySQL会对数据使用一个外部的索引排序&＃xff0c;而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” &＃xff0c;其实不一定是文件排序&＃xff0c;内部使用的是快排
  • using temporary&＃xff1a; 使用了临时表保存中间结果&＃xff0c;MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
  • using index&＃xff1a; 表示相应的SELECT操作中使用了覆盖索引&＃xff08;Covering Index&＃xff09;&＃xff0c;避免访问了表的数据行&＃xff0c;效率不错。
  • impossible where&＃xff1a; WHERE子句的值总是false&＃xff0c;不能用来获取任何元组
  • select tables optimized away&＃xff1a; 在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作&＃xff0c; 不必等到执行阶段再进行计算&＃xff0c;查询执行计划生成的阶段即完成优化
  • distinct&＃xff1a; 优化distinct操作&＃xff0c;在找到第一匹配的元祖后即停止找同样值的操作

using filesort,using temporary这两项出现时需要注意下&＃xff0c;这两项是十分耗费性能的&＃xff0c;在使用group by的时候&＃xff0c;虽然没有使用order by&＃xff0c;如果没有索引&＃xff0c;是可能同时出现using filesort,using temporary的&＃xff0c;因为group by就是先排序在分组&＃xff0c;如果没有排序的需要&＃xff0c;可以加上一个order by NULL来避免排序&＃xff0c;这样using filesort就会去除&＃xff0c;能提升一点性能。

4.type字段

  • system&＃xff1a;表只有一行记录&＃xff08;等于系统表&＃xff09;&＃xff0c;这是const类型的特例&＃xff0c;平时不会出现
  • const&＃xff1a;如果通过索引依次就找到了&＃xff0c;const用于比较主键索引或者unique索引。 因为只能匹配一行数据&＃xff0c;所以很快。如果将主键置于where列表中&＃xff0c;MySQL就能将该查询转换为一个常量
  • eq_ref&＃xff1a;唯一性索引扫描&＃xff0c;对于每个索引键&＃xff0c;表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  • ref&＃xff1a;非唯一性索引扫描&＃xff0c;返回匹配某个单独值的所有行。本质上也是一种索引访问&＃xff0c;它返回所有匹配 某个单独值的行&＃xff0c;然而它可能会找到多个符合条件的行&＃xff0c;所以它应该属于查找和扫描的混合体
  • range&＃xff1a;只检索给定范围的行&＃xff0c;使用一个索引来选择行。key列显示使用了哪个索引&＃xff0c;一般就是在你的where语句中出现between、<、>、in等的查询&＃xff0c;这种范围扫描索引比全表扫描要好&＃xff0c;因为只需要开始于缩印的某一点&＃xff0c;而结束于另一点&＃xff0c;不用扫描全部索引
  • index&＃xff1a;Full Index Scan &＃xff0c;index与ALL的区别为index类型只遍历索引树&＃xff0c;这通常比ALL快&＃xff0c;因为索引文件通常比数据文件小。 &＃xff08;也就是说虽然ALL和index都是读全表&＃xff0c; 但index是从索引中读取的&＃xff0c;而ALL是从硬盘读取的&＃xff09;
  • all&＃xff1a;Full Table Scan&＃xff0c;遍历全表获得匹配的行

三、字段类型和编码

  1. mysql返回字符串长度&＃xff1a;CHARACTER_LENGTH方法(CHAR_LENGTH一样的)返回的是字符数&＃xff0c;LENGTH函数返回的是字节数&＃xff0c;一个汉字三个字节
  2. varvhar等字段建立索引长度计算语句&＃xff1a;select count(distinct left(test,5))/count(*) from table; 越趋近1越好
  3. mysql的utf8最大是3个字节不支持emoji表情符号&＃xff0c;必须只用utf8mb4。需要在mysql配置文件中配置客户端字符集为utf8mb4。jdbc的连接串不支持配置characterEncoding&＃61;utf8mb4&＃xff0c;最好的办法是在连接池中指定初始化sql&＃xff0c;例如&＃xff1a;hikari连接池&＃xff0c;其他连接池类似spring.datasource.hikari.connection-init-sql&＃61;set names utf8mb4。否则需要每次执行sql前都先执行set names utf8mb4。
  4. msyql排序规则(一般使用_bin和_genera_ci)&＃xff1a;
  • utf8_genera_ci不区分大小写&＃xff0c;ci为case insensitive的缩写&＃xff0c;即大小写不敏感&＃xff0c;
  • utf8_general_cs区分大小写&＃xff0c;cs为case sensitive的缩写&＃xff0c;即大小写敏感&＃xff0c;但是目前MySQL版本中已经不支持类似于***_genera_cs的排序规则&＃xff0c;直接使用utf8_bin替代。
  • utf8_bin将字符串中的每一个字符用二进制数据存储&＃xff0c;区分大小写。

那么&＃xff0c;同样是区分大小写&＃xff0c;utf8_general_cs和utf8_bin有什么区别&＃xff1f; cs为case sensitive的缩写&＃xff0c;即大小写敏感&＃xff1b;bin的意思是二进制&＃xff0c;也就是二进制编码比较。 utf8_general_cs排序规则下&＃xff0c;即便是区分了大小写&＃xff0c;但是某些西欧的字符和拉丁字符是不区分的&＃xff0c;比如ä&＃61;a&＃xff0c;但是有时并不需要ä&＃61;a&＃xff0c;所以才有utf8_bin utf8_bin的特点在于使用字符的二进制的编码进行运算&＃xff0c;任何不同的二进制编码都是不同的&＃xff0c;因此在utf8_bin排序规则下&＃xff1a;ä<>a

  • sql yog中初始连接指定编码类型使用连接配置的初始化命令

四、SQL语句总结

1.常用的但容易忘的&＃xff1a;

  • 如果有主键或者唯一键冲突则不插入&＃xff1a;insert ignore into
  • 如果有主键或者唯一键冲突则更新,注意这个会影响自增的增量&＃xff1a;INSERT INTO room_remarks(room_id,room_remarks) VALUE(1,"sdf") ON DUPLICATE KEY UPDATE room_remarks&＃61;"234"
  • 如果有就用新的替代&＃xff0c;values如果不包含自增列&＃xff0c;自增列的值会变化&＃xff1a; REPLACE INTO room_remarks(room_id,room_remarks) VALUE(1,"sdf")
  • 备份表&＃xff1a;CREATE TABLE user_info SELECT * FROM user_info
  • 复制表结构&＃xff1a;CREATE TABLE user_v2 LIKE user
  • 从查询语句中导入&＃xff1a;INSERT INTO user_v2 SELECT * FROM user或者INSERT INTO user_v2(id,num) SELECT id,num FROM user
  • 连表更新&＃xff1a;UPDATE user a, room b SET a.num&＃61;a.num&＃43;1 WHERE a.room_id&＃61;b.id
  • 连表删除&＃xff1a;DELETE user FROM user,black WHERE user.id&＃61;black.id

2.锁相关(作为了解&＃xff0c;很少用)

  • 共享锁&＃xff1a; select id from tb_test where id &＃61; 1 lock in share mode;
  • 排它锁&＃xff1a; select id from tb_test where id &＃61; 1 for update

3.优化时用到&＃xff1a;

  • 强制使用某个索引&＃xff1a; select * from table force index(idx_user) limit 2;
  • 禁止使用某个索引&＃xff1a; select * from table ignore index(idx_user) limit 2;
  • 禁用缓存(在测试时去除缓存的影响)&＃xff1a; select SQL_NO_CACHE from table limit 2;

4.查看状态

  • 查看字符集 SHOW VARIABLES LIKE &＃39;character_set%&＃39;;
  • 查看排序规则 SHOW VARIABLES LIKE &＃39;collation%&＃39;;

SQL编写注意

  • where语句的解析顺序是从右到左&＃xff0c;条件尽量放where不要放having
  • 采用延迟关联(deferred join)技术优化超多分页场景&＃xff0c;比如limit 10000,10,延迟关联可以避免回表
  • distinct语句非常损耗性能&＃xff0c;可以通过group by来优化
  • 连表尽量不要超过三个表

五、踩坑

  1. 如果有自增列&＃xff0c;truncate语句会把自增列的基数重置为0&＃xff0c;有些场景用自增列作为业务上的id需要十分重视
  2. 聚合函数会自动滤空&＃xff0c;比如a列的类型是int且全部是NULL&＃xff0c;则SUM(a)返回的是NULL而不是0
  3. mysql判断null相等不能用“a&＃61;null”,这个结果永远为UnKnown&＃xff0c;where和having中,UnKnown永远被视为false&＃xff0c;check约束中&＃xff0c;UnKnown就会视为true来处理。所以要用“a is null”处理

六、千万大表在线修改

mysql在表数据量很大的时候&＃xff0c;如果修改表结构会导致锁表&＃xff0c;业务请求被阻塞。mysql在5.6之后引入了在线更新&＃xff0c;但是在某些情况下还是会锁表&＃xff0c;所以一般都采用pt工具( Percona Toolkit) 如对表添加索引&＃xff1a;

pt-online-schema-change --user&＃61;&＃39;root&＃39; --host&＃61;&＃39;localhost&＃39; --ask-pass --alter "add index idx_user_id(room_id,create_time)" D&＃61;fission_show_room_v2,t&＃61;room_favorite_info --execute

七、慢查询日志

有时候如果线上请求超时&＃xff0c;应该去关注下慢查询日志&＃xff0c;慢查询的分析很简单&＃xff0c;先找到慢查询日志文件的位置&＃xff0c;然后利用mysqldumpslow去分析。查询慢查询日志信息可以直接通过执行sql命令查看相关变量&＃xff0c;常用的sql如下&＃xff1a;

-- 查看慢查询配置
-- slow_query_log 慢查询日志是否开启
-- slow_query_log_file 的值是记录的慢查询日志到文件中
-- long_query_time 指定了慢查询的阈值
-- log_queries_not_using_indexes 是否记录所有没有利用索引的查询
SHOW VARIABLES LIKE &＃39;%quer%&＃39;;-- 查看慢查询是日志还是表的形式
SHOW VARIABLES LIKE &＃39;log_output&＃39;-- 查看慢查询的数量
SHOW GLOBAL STATUS LIKE &＃39;slow_queries&＃39;;

八、查看sql进程和杀死进程

如果你执行了一个sql的操作&＃xff0c;但是迟迟没有返回&＃xff0c;你可以通过查询进程列表看看他的实际执行状况&＃xff0c;如果该sql十分耗时&＃xff0c;为了避免影响线上可以用kill命令杀死进程&＃xff0c;通过查看进程列表也能直观的看下当前sql的执行状态&＃xff0c;如果当前数据库负载很高&＃xff0c;在进程列表可能会出现&＃xff0c;大量的进程夯住&＃xff0c;执行时间很长。命令如下&＃xff1a;

--查看进程列表
SHOW PROCESSLIST;
--杀死某个进程
kill 183665

如果你使用的sqlyog&＃xff0c;那么也有图形化的页面&＃xff0c;在菜单栏-工具-显示-进程列表。在进程列表页面可以右键杀死进程。如下所示&＃xff1a;

九、一些数据库性能的思考

在对公司慢查询日志做优化的时候&＃xff0c;很多时候可能是忘了建索引&＃xff0c;像这种问题很容易解决&＃xff0c;加个索引就行了。但是有两种情况就不是简单能加索引能解决了&＃xff1a;

  1. 业务代码循环读数据库&＃xff1a; 考虑这样一个场景&＃xff0c;获取用户粉丝列表信息 加入分页是十个 其实像这样的sql是十分简单的&＃xff0c;通过连表查询性能也很高&＃xff0c;但是有时候&＃xff0c;很多开发采用了取出一串id&＃xff0c;然后循环读每个id的信息&＃xff0c;这样如果id很多对数据库的压力是很大的&＃xff0c;而且性能也很低
  2. 统计sql&＃xff1a;很多时候&＃xff0c;业务上都会有排行榜这种&＃xff0c;发现公司有很多地方直接采用数据库做计算&＃xff0c;在对一些大表的做聚合运算的时候&＃xff0c;经常超过五秒&＃xff0c;这些sql一般很长而且很难优化&＃xff0c; 像这种场景&＃xff0c;如果业务允许&＃xff08;比如一致性要求不高或者是隔一段时间才统计的&＃xff09;&＃xff0c;可以专门在从库里面做统计。另外我建议还是采用redis缓存来处理这种业务
  3. 超大分页: 在慢查询日志中发现了一些超大分页的慢查询如limit 40000,1000&＃xff0c;因为mysql的分页是在server层做的&＃xff0c;可以采用延迟关联在减少回表。但是看了相关的业务代码正常的业务逻辑是不会出现这样的请求的&＃xff0c;所以很有可能是有恶意用户在刷接口&＃xff0c;所以最好在开发的时候也对接口加上校验拦截这些恶意请求。 这篇文章就总结到这里&＃xff0c;希望能够对你有所帮助&＃xff01;

不止MySQL&＃xff0c;想成为架构师的话可以参加下面这个专题&＃xff0c;这个换题详细的解释了Linux、Netty、并发编程、JVM、Tomcat等技术&＃xff01;

同时小编这边也给大家准备了一些针对以上专题的学习资料和视频&＃xff0c;希望能够帮助到大家&＃xff01;

资料领取方式&＃xff1a;加入粉丝群963944895&＃xff0c;【点击加入群聊】私信管理员即可


转:https://juejin.im/post/5c91e8f75188252da759cccb



推荐阅读
  • const

    电话号码的字母组合解题思路和代码示例

    电话号码的字母组合解题思路和代码示例
    本文介绍了力扣题目《电话号码的字母组合》的解题思路和代码示例。通过使用哈希表和递归求解的方法，可以将给定的电话号码转换为对应的字母组合。详细的解题思路和代码示例可以帮助读者更好地理解和实现该题目。 ... [详细]
  • const

    vue使用

    vue使用
    关键词： ... [详细]
  • rsa

    Linux服务器密码过期策略、登录次数限制、私钥登录等配置方法

    Linux服务器密码过期策略、登录次数限制、私钥登录等配置方法
    本文介绍了在Linux服务器上进行密码过期策略、登录次数限制、私钥登录等配置的方法。通过修改配置文件中的参数，可以设置密码的有效期、最小间隔时间、最小长度，并在密码过期前进行提示。同时还介绍了如何进行公钥登录和修改默认账户用户名的操作。详细步骤和注意事项可参考本文内容。 ... [详细]
  • config

    VScode格式化文档换行或不换行的设置方法

    VScode格式化文档换行或不换行的设置方法
    本文介绍了在VScode中设置格式化文档换行或不换行的方法，包括使用插件和修改settings.json文件的内容。详细步骤为：找到settings.json文件，将其中的代码替换为指定的代码。 ... [详细]
  • int

    Android 新闻App的本地服务器搭建教程

    本文介绍了在开发Android新闻App时，搭建本地服务器的步骤。通过使用XAMPP软件，可以一键式搭建起开发环境，包括Apache、MySQL、PHP、PERL。在本地服务器上新建数据库和表，并设置相应的属性。最后，给出了创建new表的SQL语句。这个教程适合初学者参考。 ... [详细]
  • config

    [从头学数学] 第101节 比例的相关问题研究和修炼

    本文介绍了[从头学数学]中第101节关于比例的相关问题的研究和修炼过程。主要内容包括[机器小伟]和[工程师阿伟]一起研究比例的相关问题，并给出了一个求比例的函数scale的实现。 ... [详细]
  • utf-8

    eclipse学习（第三章：ssh中的Hibernate）——11.Hibernate的缓存（2级缓存，get和load）

    eclipse学习（第三章：ssh中的Hibernate）——11.Hibernate的缓存（2级缓存，get和load）
    本文介绍了eclipse学习中的第三章内容，主要讲解了ssh中的Hibernate的缓存，包括2级缓存和get方法、load方法的区别。文章还涉及了项目实践和相关知识点的讲解。 ... [详细]
  • js

    《数据结构》学习笔记3——串匹配算法性能评估

    《数据结构》学习笔记3——串匹配算法性能评估
    本文主要讨论串匹配算法的性能评估，包括模式匹配、字符种类数量、算法复杂度等内容。通过借助C++中的头文件和库，可以实现对串的匹配操作。其中蛮力算法的复杂度为O(m*n)，通过随机取出长度为m的子串作为模式P，在文本T中进行匹配，统计平均复杂度。对于成功和失败的匹配分别进行测试，分析其平均复杂度。详情请参考相关学习资源。 ... [详细]
  • int

    游标的使用笔记

    本文介绍了游标的使用方法，并以一个水果供应商数据库为例进行了说明。首先创建了一个名为fruits的表，包含了水果的id、供应商id、名称和价格等字段。然后使用游标查询了水果的名称和价格，并将结果输出。最后对游标进行了关闭操作。通过本文可以了解到游标在数据库操作中的应用。 ... [详细]
  • config

    HDFS2.x新特性

    HDFS2.x新特性
    一、集群间数据拷贝scp实现两个远程主机之间的文件复制scp-rhello.txtroothadoop103:useratguiguhello.txt推pushscp-rr ... [详细]
  • js

    高质量SQL书写的30条建议

    高质量SQL书写的30条建议
    本文提供了30条关于优化SQL的建议，包括避免使用select *，使用具体字段，以及使用limit 1等。这些建议是基于实际开发经验总结出来的，旨在帮助读者优化SQL查询。 ... [详细]
  • const

    Linux环境变量函数getenv、putenv、setenv和unsetenv详解

    Linux环境变量函数getenv、putenv、setenv和unsetenv详解
    本文详细解释了Linux中的环境变量函数getenv、putenv、setenv和unsetenv的用法和功能。通过使用这些函数，可以获取、设置和删除环境变量的值。同时给出了相应的函数原型、参数说明和返回值。通过示例代码演示了如何使用getenv函数获取环境变量的值，并打印出来。 ... [详细]
  • const

    006_Redis的List数据类型

    006_Redis的List数据类型
    1.List类型是一个链表结构的集合,主要功能有push,pop,获取元素等。List类型是一个双端链表的结构,我们可以通过相关操作进行集合的头部或者尾部添加删除元素,List的设 ... [详细]
  • replace

    php 主动断掉http,怎么在PHP项目中实现一个HTTP断点续传功能

    怎么在PHP项目中实现一个HTTP断点续传功能发布时间：2021-01-1916:26:06来源：亿速云阅读：96作者：Le ... [详细]
  • python

    Python爬虫技术基础篇面向对象高级编程（中）的多重继承

    本文介绍了Python爬虫技术基础篇面向对象高级编程（中）中的多重继承概念。通过继承，子类可以扩展父类的功能。文章以动物类层次的设计为例，讨论了按照不同分类方式设计类层次的复杂性和多重继承的优势。最后给出了哺乳动物和鸟类的设计示例，以及能跑、能飞、宠物类和非宠物类的增加对类数量的影响。 ... [详细]
author-avatar
细妹很快乐
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有