mysql索引优化 - 单表如何使用索引优化 以及 常见的索引失效的原因分析

news/2024/5/18 22:20:02

1. 全值匹配我最爱,查询的字段按照顺序在索引中都可以匹配到!
建立索引 
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'

SQL 中查询字段的顺序,跟使用索引中字段的顺序,没有关系。优化器会在不影响 SQL 执行结果的前提下,给 你自动地优化。如下图

 2. 最佳左前缀法则
查询字段与索引字段顺序的不同会导致,索引无法充分使用,甚至索引失效! 原因:使用复合索引,需要遵循最佳左前缀法则,即如果索引了多列,要遵守最左前缀法则。指的是查询从索 引的最左前列开始并且不跳过索引中的列。 结论:过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无 法被使用。

3. 不要在索引列上做任何计算 
不在索引列上做任何操作(计算、函数、(自动 or 手动)类型转换),会导致索引失效而转向全表扫描。
不要在查询列上使用函数,结论:等号左边不要计算
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30; (推荐)
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;



不要在查询列上做数据类型转换,结论:等号右边不要做转换 
explain select sql_no_cache * from emp where name='30000'; (推荐)
explain select sql_no_cache * from emp where name=30000;

4. 索引列上不能有范围查询
建议:将可能做范围查询的字段的索引顺序放在最后

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd'; (推荐)
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<=5 AND emp.name = 'abcd';



5. 尽量使用覆盖索引
即查询列和索引列一致,不要写 select * 

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt'; 
explain SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';(
推荐

6. 使用不等于(!= 或者<>)的时候
mysql 在使用不等于(!= 或者<>)时,有时会无法使用索引会导致全表扫描


7. 字段的 is not null 和 is null 【当字段允许为 Null 的条件下】
is not null 用不到索引,is null 可以用到索引。

 
8. like 的前后模糊匹配,%不要出现在最左侧

 9. 减少使用 or

使用 union all 或者 union 来替代:


口诀
全职匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
LIKE 百分写最右,覆盖索引不写*
不等空值还有 OR,索引影响要注意;
VAR 引号不可丢,SQL 优化有诀窍。


https://dhexx.cn/news/show-17244.html

相关文章

c#解析Josn(解析多个子集,数据,可解析无限级json)

首先引用 解析类库 using System; using System.Collections.Generic; using System.Linq; using System.Text;namespace BPMS.WEB.Common {public class CommonJsonModel : CommonJsonModelAnalyzer{private string rawjson;private bool isValue false;private bool isModel…

mysql索引优化 - 多表关联查询优化

1 left joinEXPLAIN SELECT * FROM class LEFT JOIN book ON class.card book.card;LEFT JOIN条件用于确定如何从右表搜索行&#xff0c; 左边一定都有&#xff0c; #所以右边是我们的关键点&#xff0c;一定需要建立索引。结论&#xff1a;在优化关联查询时&#xff0c;只有在…

mysql索引优化 - 子查询优化

结论&#xff1a; 在范围判断时&#xff0c;尽量不要使用 not in 和 not exists&#xff0c;使用 left join on xxx is null 代替。 取所有不为掌门人的员工&#xff0c;按年龄分组&#xff01; select age as 年龄, count(*) as 人数 from t_emp where id not in (select ceo…

树莓派折腾---红外探测

先上个图&#xff1a; 用到的配件&#xff1a; 1.主角&#xff1a;树莓派 2.配角&#xff1a;红外探测 3.打杂&#xff1a;面包板&#xff0c;杜邦线&#xff0c;蜂鸣器&#xff0c;LED&#xff0c;电阻 红外探测有三个针脚&#xff0c;两端的是供电&#xff0c;中间是信号输出…

mysql索引优化 - 排序分组优化

where 条件和 on 的判断这些过滤条件&#xff0c;作为优先优化的部分&#xff0c;是要被先考虑的&#xff01; 其次&#xff0c;如果有分组和排序&#xff0c;那么 也要考虑 grouo by 和 order by。1. 必须有过滤&#xff0c;才会用到索引 结论&#xff1a;where&#xff0c;li…

UIView详解

来源&#xff1a;http://blog.csdn.net/chengyingzhilian/article/details/7894276 UIView表示屏幕上的一块矩形区域&#xff0c;它在App中占有绝对重要的地位&#xff0c;因为IOS中几乎所有可视化控件都是UIView的子类。负责渲染区域的内容&#xff0c;并且响应该区域内发生的…

jmeter基础入门(HTTP,TCP,SQL查询,新增,查看报告)

示例下载地址 https://download.csdn.net/download/qq_41712271/20398149有坑的地方 1 发送TCP请求&#xff0c;注意Tcp client classname,如下图&#xff0c;这里发送16进制&#xff0c;所以写 BinaryTCPClientImpl TCPClientImpl&#xff1a;纯文本为内容进行发送 BinaryT…

这么方便吗?用ChatGPT生成Excel(详解步骤)

文章目录前言使用过 ChatGPT 的人都知道&#xff0c;提示占据非常重要的位置。而 Word&#xff0c;Excel、PPT 这办公三大件中&#xff0c;当属 Excel 最难搞&#xff0c;想要熟练掌握它&#xff0c;需要记住很多公式。但是使用提示就简单多了&#xff0c;和 ChatGPT 聊聊天就能…

jenkins持续集成入门1

jenkins持续集成相关的软件安装分布架构图 软件安装的列表如下&#xff1a; jdk8或以上 maven git GitLab-EE Docker Harbor &#xff08;docker私服&#xff09; jenkins SonarQube &#xff08;代码审查&#xff09; Tomcat

HTML5新增Canvas标签及对应属性、API详解(基础一)

知识说明&#xff1a; HTML5新增的canvas标签&#xff0c;通过创建画布&#xff0c;在画布上创建任何想要的形状&#xff0c;下面将canvas的API以及属性做一个整理&#xff0c;并且附上时钟的示例&#xff0c;便于后期复习学习&#xff01;Fighting&#xff01; 一、标签原型 &…