索引如何优化

article/2025/11/6 4:12:15

前言

  • 索引的相信大家都听说过，但是真正会用的又有几人？平时工作中写SQL真的会考虑到这条SQL如何能够用上索引，如何能够提升执行效率？

  • 此篇文章详细的讲述了索引优化的几个原则，只要在工作中能够随时应用到，相信你写出的SQL一定是效率最高，最牛逼的。

  • 文章的脑图如下：

索引优化规则

1、like语句的前导模糊查询不能使用索引

select * from doc where title like '%XX'；   --不能使用索引
select * from doc where title like 'XX%'；   --非前导模糊查询，可以使用索引

  • 因为页面搜索严禁左模糊或者全模糊，如果需要可以使用搜索引擎来解决。

2、union、in、or 都能够命中索引，建议使用 in

  1. union能够命中索引，并且MySQL 耗费的 CPU 最少。

select * from doc where status=1
union all
select * from doc where status=2;

  1. in能够命中索引，查询优化耗费的 CPU 比 union all 多，但可以忽略不计，一般情况下建议使用 in。

select * from doc where status in (1, 2);

  1. or 新版的 MySQL 能够命中索引，查询优化耗费的 CPU 比 in多，不建议频繁用or。

select * from doc where status = 1 or status = 2

  1. 补充：有些地方说在where条件中使用or，索引会失效，造成全表扫描，这是个误区：

  • ①要求where子句使用的所有字段，都必须建立索引；

  • ②如果数据量太少，mysql制定执行计划时发现全表扫描比索引查找更快，所以会不使用索引；

  • ③确保mysql版本5.0以上，且查询优化器开启了index_merge_union=on, 也就是变量optimizer_switch里存在index_merge_union且为on。

3、负向条件查询不能使用索引

  • 负向条件有：!=、<>、not in、not exists、not like 等。

  • 例如下面SQL语句：

select * from doc where status != 1 and status != 2;

  • 可以优化为 in 查询：

select * from doc where status in (0,3,4);

4、联合索引最左前缀原则

  • 如果在(a,b,c)三个字段上建立联合索引，那么他会自动建立 a| (a,b) | (a,b,c)组索引。

  • 登录业务需求，SQL语句如下：

select uid, login_time from user where login_name=? andpasswd=?

  • 可以建立(login_name, passwd)的联合索引。因为业务上几乎没有passwd 的单条件查询需求，而有很多login_name 的单条件查询需求，所以可以建立(login_name, passwd)的联合索引，而不是(passwd, login_name)。

  1. 建立联合索引的时候，区分度最高的字段在最左边

  1. 存在非等号和等号混合判断条件时，在建立索引时，把等号条件的列前置。如 where a>? and b=?，那么即使a 的区分度更高，也必须把 b 放在索引的最前列。

  1. 最左前缀查询时，并不是指SQL语句的where顺序要和联合索引一致。

  • 下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引：

select uid, login_time from user where passwd=? andlogin_name=?

  • 但还是建议 where 后的顺序和联合索引一致，养成好习惯。

  1. 假如index(a,b,c), where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用。

5、不能使用索引中范围条件右边的列（范围列可以用到索引），范围列之后列的索引全失效

  • 范围条件有：<、<=、>、>=、between等。

  • 索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

  • 假如有联合索引 (empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而title 和 from_date 则使用不到索引。

select * from employees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'

6、不要在索引列上面做任何操作（计算、函数），否则会导致索引失效而转向全表扫描

  • 例如下面的 SQL 语句，即使 date 上建立了索引，也会全表扫描：

select * from doc where YEAR(create_time) <= '2016';

  • 可优化为值计算，如下：

select * from doc where create_time <= '2016-01-01';

  • 比如下面的 SQL 语句：

select * from order where date < = CURDATE()；

  • 可以优化为：

select * from order where date < = '2018-01-2412:00:00';

7、强制类型转换会全表扫描

  • 字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。

  • 如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引。

select * from user where phone=13800001234

  • 可以优化为：

select * from user where phone='13800001234';

8、更新十分频繁、数据区分度不高的列不宜建立索引

  • 更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。

  • “性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

  • 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。

9、利用覆盖索引来进行查询操作，避免回表，减少select * 的使用

  • 覆盖索引：查询的列和所建立的索引的列个数相同，字段相同。

  • 被查询的列，数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。

  • 例如登录业务需求，SQL语句如下。

Select uid, login_time from user where login_name=? and passwd=?

  • 可以建立(login_name, passwd, login_time)的联合索引，由于 login_time 已经建立在索引中了，被查询的 uid 和 login_time 就不用去 row 上获取数据了，从而加速查询。

10、索引不会包含有NULL值的列

  • 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时，尽量使用not null 约束以及默认值。

11、is null, is not null无法使用索引

12、如果有order by、group by的场景，请注意利用索引的有序性

  1. order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort 的情况，影响查询性能。

  • 例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。

  1. 如果索引中有范围查找，那么索引有序性无法利用，如WHERE a>10 ORDER BY b;，索引(a,b)无法排序。

13、使用短索引（前缀索引）

  • 对列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果该列在前10个或20个字符内，可以做到既使得前缀索引的区分度接近全列索引，那么就不要对整个列进行索引。因为短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作，减少索引文件的维护开销。可以使用count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度。

  • 但缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引。

  • 不过很多时候没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

14、利用延迟关联或者子查询优化超多分页场景

  • MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

  • 示例如下，先快速定位需要获取的id段，然后再关联:

selecta.* from 表1 a,(select id from 表1 where 条件 limit100000,20 ) b where a.id=b.id；

15、如果明确知道只有一条结果返回，limit 1 能够提高效率

  • 比如如下 SQL 语句：

select * from user where login_name=?;

  • 可以优化为：

select * from user where login_name=? limit 1

  • 自己明确知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动。

16、超过三个表最好不要 join

  • 需要 join 的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。

  • 例如：left join是由左边决定的，左边的数据一定都有，所以右边是我们的关键点，建立索引要建右边的。当然如果索引在左边，可以用right join。

17、单表索引建议控制在5个以内

18、SQL 性能优化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好

  • consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。

  • ref：使用普通的索引（Normal Index）。

  • range：对索引进行范围检索。

  • 当 type=index 时，索引物理文件全扫，速度非常慢。

19、业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引

  • 不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

20.创建索引时避免以下错误观念

  1. 索引越多越好，认为需要一个查询就建一个索引。

  2. 宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度。

  3. 抵制惟一索引，认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

  4. 过早优化，在不了解系统的情况下就开始优化。

索引选择性与前缀索引

  • 既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。

  • 第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

  • 另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：

Index Selectivity = Cardinality / #T

  • 显然选择性的取值范围为(0, 1]``，选择性越高的索引价值越大，这是由B+Tree的性质决定的。例如，employees.titles表，如果title`字段经常被单独查询，是否需要建索引，我们看一下它的选择性：

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
|      0.0000 |
+-------------+

  • title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。

  • 有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

  • 假设employees表只有一个索引<emp_no>，那么如果我们想按名字搜索一个人，就只能全表扫描了：

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

  • 如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建<first_name>或<first_name, last_name>，看下两个索引的选择性：

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+

  • <first_name>显然选择性太低，``<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>`，看看其选择性：

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+

  • 选择性还不错，但离0.9313还是有点距离，那么把last_name前缀加到4：

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+

  • 这时选择性已经很理想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，我们把这个前缀索引建上：

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

  • 此时再执行一遍按名字查询，比较分析一下与建索引前的结果：

SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+

  • 性能的提升是显著的，查询速度提高了120多倍。

  • 前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。


http://chatgpt.dhexx.cn/article/rHJ4bA47.shtml

相关文章

MySQL高级-索引优化（超详细）

MySQL高级-索引优化（超详细）

性能分析 MySQL Query Optimizer Mysql中由专门负责优化SELECT语句的优化器&#xff0c;主要功能就是通过计算分析系统中收集到的统计信息&#xff0c;为客户端请求的Query提供他认为最优的执行计划&#xff08;他认为最优的&#xff0c;但**不一定是DBA觉得最优的&#xff0…
阅读更多...
mysql locate索引_MYSQL索引优化

mysql locate索引_MYSQL索引优化

1.查看sql的执行频率 MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级(当前连接)的计结果和 global 级(自数据库上次启动至今)的统计结…
阅读更多...
索引优化详解

索引优化详解

一、EXPLAIN 做MySQL优化&#xff0c;我们要善用 EXPLAIN 查看SQL执行计划。 下面来个简单的示例&#xff0c;标注(1,2,3,4,5)我们要重点关注的数据 type列&#xff0c;连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别 key列&#xff0c;使用到的索引名。如果…
阅读更多...
索引优化原则

索引优化原则

1、全值匹配 查询语句尽量使用全值匹配。 2、左前缀原则 如果一个索引是组合索引&#xff0c;索引了多列&#xff0c;要遵循左前缀原则&#xff0c;即查询从索引的左前缀开始&#xff0c;不能跳过索引中间的列。 3、不要在索引列上操作 操作包括&#xff1a;计算、函数、自动…
阅读更多...
项目中常用的MySQL优化你知道多少？

项目中常用的MySQL优化你知道多少？

项目中常用的MySQL优化 文章目录 项目中常用的MySQL优化前言一、mysql优化是什么&#xff1f;二、优化步骤1.EXPLAIN2.SQL语句中IN包含的值不应太多3.SELECT语句务必指明字段名称4.如果排序字段没有用到索引&#xff0c;就尽量少排序5.如果限制条件中其他字段没有索引&#xff…
阅读更多...
一文带你你搞懂索引如何优化！！！

一文带你你搞懂索引如何优化！！！

前言 索引的相信大家都听说过&#xff0c;但是真正会用的又有几人&#xff1f;平时工作中写SQL真的会考虑到这条SQL如何能够用上索引&#xff0c;如何能够提升执行效率&#xff1f;此篇文章详细的讲述了索引优化的几个原则&#xff0c;只要在工作中能够随时应用到&#xff0c;相…
阅读更多...
使用AJAX实现数据的增删改查

使用AJAX实现数据的增删改查

主页&#xff1a;index.html <!DOCTYPE html><html> <head> <meta charset"UTF-8"> <title></title> <script src"http://libs.baidu.com/jquery/2.1.4/jquery.min.js"></script> </head> <body…
阅读更多...
合肥工业大学数据库上机实验3（第3次实验）

合肥工业大学数据库上机实验3（第3次实验）

大三上学期的实验了&#xff0c;一直没来得及放到博客上面&#xff0c;博主所在学校的数据库实验都很简单。&#xff08;不明白为什么截图上传之后为什么那么糊&#xff09; 文章目录 实验9&#xff1a;触发器的创建与使用1. 实验目的2. 实验要求3. 实验内容4. 实验步骤5. 实验…
阅读更多...
第三章课后题

第三章课后题

3.有两个关系S(A,B,C,D)和T(C,D,E,F)&#xff0c;写出与下列等价的SQL表达式。 &#xff08;1&#xff09; σ A 10 ( S ) \sigma_{A10}(S) σA10​(S) 对行的选择&#xff0c;列全部显示。 SELECT * FROM S WHERE A10;&#xff08;2&#xff09; Π A , B ( S ) \Pi_{A,B}(…
阅读更多...
SQL 数据库上机实验（查询操作）

SQL 数据库上机实验（查询操作）

大二数据库上机实验 实验一实验二 实验一 实验步骤&#xff1a; 1&#xff1a;创建数据库“shiyan2”&#xff1b; 2&#xff1a;创建数据表’s’,’p’,’j’&#xff1b; 3&#xff1a;创建数据表’spj’并设定’sno’,’pno’,’jno’三列为外键&#xff0c;分别关联于s表的…
阅读更多...
实验3 SQL的复杂多表查询以及视图

实验3 SQL的复杂多表查询以及视图

第1关 查询所有“红色”的15公斤及以上的零件名 任务描述 查询所有“红色”的15公斤及以上的零件名 相关知识 零件表P由零件代码&#xff08;PNO&#xff09;、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成&#xff1b; P表如下图&#xff1a; 现已构建P表&#xff0c;结构信…
阅读更多...
【系统分析师之路】第十五章 复盘数据库系统（关系数据库应用）

【系统分析师之路】第十五章 复盘数据库系统（关系数据库应用）

【系统分析师之路】第十五章 复盘数据库系统&#xff08;关系数据库应用&#xff09; 复盘数据库系统—关系数据库应用 【系统分析师之路】第十五章 复盘数据库系统&#xff08;关系数据库应用&#xff09;前言部分 历年真题考点分析1&#xff09;考点分析2&#xff09;重要知识…
阅读更多...
数据库系统概论练习3

数据库系统概论练习3

一、填空题 能够唯一标识实体的属性或属性组称为____实体的码______。如果两个关系没有公共属性&#xff0c;则其自然联接操作与____笛卡尔积_____操作等价。SQL中聚合函数“COUNT&#xff08;*&#xff09;”的功能是___统计元组个数________。关系模式如果为1NF&#xff0c;则…
阅读更多...
数据库第七周作业——第三章课后习题

数据库第七周作业——第三章课后习题

3.有两个关系S&#xff08;A,B,C,D&#xff09;和T&#xff08;C,D,E,F&#xff09;,写出与下列查询等价的SQL表达式&#xff1a; (1&#xff09;σ A10(S&#xff09; SELECT * FROM S WHERE A10;(2&#xff09;π A , B ( S ) SELECT A,B FROM S;(3&#xff09;S ⋈ T SEL…
阅读更多...
第三章作业【数据库原理】

第三章作业【数据库原理】

第二章作业【数据库原理】 前言推荐第三章作业第3章第1题简答题第3章第2题&#xff08;关系代数->SQL代码&#xff09;第3章第3题&#xff08;创建SPJ数据库中的四张表&#xff09;模拟数据第3章第4题&#xff08;SPJ数据库上完成查询&#xff09;第3章第5题&#xff08;SPJ…
阅读更多...
设有一个工程供应数据库系统，包括如下四个关系模式：S(Sno，Sname， Status，City) ；P(Pno，Pname，Color ，Weight)；J(Jno，Jname， Ci

设有一个工程供应数据库系统，包括如下四个关系模式：S(Sno，Sname， Status，City) ；P(Pno，Pname，Color ，Weight)；J(Jno，Jname， Ci

设有一个工程供应数据库系统&#xff0c;包括如下四个关系模式&#xff1a; S(Sno&#xff0c;Sname&#xff0c; Status&#xff0c;City) &#xff1b; P(Pno&#xff0c;Pname&#xff0c;Color &#xff0c;Weight)&#xff1b; J(Jno&#xff0c;Jname&#xff0c; City) …
阅读更多...
高通WLAN框架学习（17）-- NIO和PNO

高通WLAN框架学习（17）-- NIO和PNO

本章详细介绍了preferred network offload (PNO)扫描概述、流、模式和配置文件。 18.1 PNO扫描概述 WLAN STA在通过AP模组连接AP之前，需要偶尔对环境进行扫描。 在WLAN请求程序中对扫描结果进行解析和处理，以确定是否至少有一个目标(附近)AP在附近。 为了偶尔发出扫描，应用…
阅读更多...
Wi-Fi PNO扫描流程（Android P）

Wi-Fi PNO扫描流程（Android P）

简介&#xff1a;当手机灭屏情况下&#xff0c;有保存网络时&#xff0c;若已连接&#xff0c;不扫描&#xff0c;否则&#xff0c;PNO扫描&#xff0c;即只扫描已保存的网络。最小间隔min20s&#xff0c;最大间隔max20s*360s PNO 即Preferred Network Offload&#xff0c;用于…
阅读更多...
[RK3288][Android6.0] WiFi之PNO功能了解

[RK3288][Android6.0] WiFi之PNO功能了解

Platform: Rockchip OS: Android 6.0 Kernel: 3.10.92 概念： PNO 即Preferred Network Offload，用于系统在休眠的时候连接WiFi 此功能是在Android3.1加入的 缺陷： 在使用PNO时，有潜在泄露个人隐私的风险。这里没明白，意思是说PNO会发送之前的连接过的网络给AP，然…
阅读更多...
二值化的方法

二值化的方法

1.二值化 图像二值化的目的是最大限度的将图像中感兴趣部分保留下来&#xff0c;在很多情况下&#xff0c;也是进行图像分析、特征提取与模式识别之前的必要的图像预处理过程。在这些庞大的图像二值化分类方法中&#xff0c;基于直方图的全局二值算法占有了绝对的市场份额&…
阅读更多...
推荐文章

Copyright @ 2022~2023