Mysql 索引专题

最新推荐文章于 2024-10-03 14:47:06 发布
最新推荐文章于 2024-10-03 14:47:06 发布
阅读量176 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_37946518/article/details/88868851
本文深入解析MySQL中的各种索引类型，包括普通索引、唯一索引、全文索引等，并探讨了索引在不同条件下的使用规则。此外，还介绍了如何根据业务需求选择合适的索引策略，以及索引在实际应用中的优化技巧。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

参考：

https://www.cnblogs.com/doudouxiaoye/p/5831449.html

http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

https://blog.youkuaiyun.com/u011277123/article/details/72627011

MySQL索引的类型

1. 普通索引

最基本的索引

2. 唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

写多读少的场景，不推荐使用唯一索引，用应用程序保证唯一性

3. 全文索引（FULLTEXT）

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。////对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

4. 单列索引、多列索引

多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

5. 组合索引（最左前缀）

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。

 

SQL什么条件会使用索引？

当字段上建有索引时，通常以下情况会使用索引：

INDEX_COLUMN = ?

INDEX_COLUMN > ?

INDEX_COLUMN >= ?

INDEX_COLUMN < ?

INDEX_COLUMN <= ?

INDEX_COLUMN between ? and ?

INDEX_COLUMN in (?,?,...,?)

INDEX_COLUMN like ?||'%'（后导模糊查询）

T1. INDEX_COLUMN=T2. COLUMN1（两个表通过索引字段关联）

SQL什么条件不会使用索引？

查询条件

不能使用索引原因

INDEX_COLUMN <> ?

INDEX_COLUMN not in (?,?,...,?)

不等于操作不能使用索引

function(INDEX_COLUMN) = ?

INDEX_COLUMN + 1 = ?

INDEX_COLUMN || 'a' = ?

经过普通运算或函数运算后的索引字段不能使用索引

INDEX_COLUMN like '%'||?

INDEX_COLUMN like '%'||?||'%'

含前导模糊查询的Like语法不能使用索引

INDEX_COLUMN is null

B-TREE索引里不保存字段为NULL值记录，因此IS NULL不能使用索引

NUMBER_INDEX_COLUMN='12345'

CHAR_INDEX_COLUMN=12345

Oracle在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据类型不同时会对字段值隐式转换，相当于加了一层函数处理，所以不能使用索引。

a.INDEX_COLUMN=a.COLUMN_1

给索引查询的值应是已知数据，不能是未知字段值。

注：

经过函数运算字段的字段要使用可以使用函数索引，这种需求建议与DBA沟通。

有时候我们会使用多个字段的组合索引，如果查询条件中第一个字段不能使用索引，那整个查询也不能使用索引

如：我们company表建了一个id+name的组合索引，以下SQL是不能使用索引的

Select * from company where name=?

Oracle9i后引入了一种index skip scan的索引方式来解决类似的问题，但是通过index skip scan提高性能的条件比较特殊，使用不好反而性能会更差。

 

我们一般在什么字段上建索引？

这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：

1、字段出现在查询条件中，并且查询条件可以使用索引；

2、语句执行频率高，一天会有几千次以上；

3、通过字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？

这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快速评估：

小表(记录数小于10000行的表)：筛选比例<10%；

大表：(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16

      单条记录长度≈字段平均内容长度之和+字段数*2

 

以下是一些字段是否需要建B-TREE索引的经验分类：

 

 

 

字段类型

常见字段名

需要建索引的字段

主键

ID,PK

外键

PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID

有对像或身份标识意义字段

HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO

索引慎用字段,需要进行数据分布及使用场景详细评估

日期

GMT_CREATE,GMT_MODIFIED

年月

YEAR,MONTH

状态标志

PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG

类型

ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE

区域

COUNTRY,PROVINCE,CITY

操作人员

CREATOR,AUDITOR

数值

LEVEL,AMOUNT,SCORE

长字符

ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT

不适合建索引的字段

描述备注

DESCRIPTION,REMARK,MEMO,DETAIL

大字段

FILE_CONTENT,EMAIL_CONTENT

 

 

如何知道SQL是否使用了正确的索引？

简单SQL可以根据索引使用语法规则判断，复杂的SQL不好办，判断SQL的响应时间是一种策略，但是这会受到数据量、主机负载及缓存等因素的影响，有时数据全在缓存里，可能全表访问的时间比索引访问时间还少。要准确知道索引是否正确使用，需要到数据库中查看SQL真实的执行计划，这个话题比较复杂，详见SQL执行计划专题介绍。

 

索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少？

这个没有固定的比例，与每个表记录的大小及索引字段大小密切相关，以下是一个普通表测试数据，仅供参考：

索引对于Insert性能降低56%

索引对于Update性能降低47%

索引对于Delete性能降低29%

因此对于写IO压力比较大的系统，表的索引需要仔细评估必要性，另外索引也会占用一定的存储空间。

 

只通过索引访问数据-组合索引

有些时候，我们只是访问表中的几个字段，并且字段内容较少，我们可以为这几个字段单独建立一个组合索引，这样就可以直接只通过访问索引就能得到数据，一般索引占用的磁盘空间比表小很多，所以这种方式可以大大减少磁盘IO开销。

如：select id,name from company where type='2';

如果这个SQL经常使用，我们可以在type,id,name上创建组合索引

create index my_comb_index on company(type,id,name);

有了这个组合索引后，SQL就可以直接通过my_comb_index索引返回数据，不需要访问company表。

还是拿字典举例：有一个需求，需要查询一本汉语字典中所有汉字的个数，如果我们的字典没有目录索引，那我们只能从字典内容里一个一个字计数，最后返回结果。如果我们有一个拼音目录，那就可以只访问拼音目录的汉字进行计数。如果一本字典有1000页，拼音目录有20页，那我们的数据访问成本相当于全表访问的50分之一。

切记，性能优化是无止境的，当性能可以满足需求时即可，不要过度优化。在实际数据库中我们不可能把每个SQL请求的字段都建在索引里，所以这种只通过索引访问数据的方法一般只用于核心应用，也就是那种对核心表访问量最高且查询字段数据量很少的查询。

 

MySQL索引的优化

  • 分页查询很重要，如果查询数据量超过30%，MYSQL不会使用索引。

  • 单表索引数不超过5个、单个索引字段数不超过5个。

  • 字符串可使用前缀索引，前缀长度控制在5-8个字符。

  • 字段唯一性太低，增加索引没有意义，如：是否删除、性别。

哪些情况需要建索引：

  • 主键，唯一索引

  • 经常用作查询条件的字段需要创建索引

  • 经常需要排序、分组和统计的字段需要建立索引

  • 查询中与其他表关联的字段，外键关系建立索引

哪些情况不要建索引：

  • 表的记录太少，百万级以下的数据不需要创建索引

  • 经常增删改的表不需要创建索引

  • 数据重复且分布平均的字段不需要创建索引，如 true,false 之类。

  • 频发更新的字段不适合创建索引

  • where条件里用不到的字段不需要创建索引

MySQL索引最全详细汇总
qq_53518630的博客
05-21 1970
目录 1、概念 2、索引的优势和劣势 3、MYSQL索引数据结构 各存储引擎的支持 1）二叉搜索树 2）红黑树 3）B Trees 4）B+ Trees 4、索引分类 5、什么情况建立索引 6、什么情况不建立索引 7、操作索引 1）创建语法 2）创建语法二 3）全文检索 4）查看索引 5）删除索引 1、概念 MYSQL官方对索引的定义为：索引（Index）是帮助MYSQL高效获取数据的数据结构。从而可以得到索引的本质：索引是数据结构。（索引文件）
MySQL面试专题-索引
Tony_yitao的博客
10-17 1238
MySQL面试专题-索引
Mysql索引专题
weixin_44143114的博客
08-23 189
一、索引管理 1，什么是索引 索引是帮助Mysql高效查询数据的数据结构。 2，索引的优势和劣势 优势： 提升数据检索的效率，降低数据库的IO成本，类似于书本的目录。 通过索引列对数据进行排序，降低数据排序的成本，降低cpu的损耗。 a：被索引的列会自动进行排序。 b：如果按照索引列的顺序来排序，那么对应order by来说效率就会提高很多。 劣势： 索引会额外占据磁盘空间。 索引虽然会提高查询效率，但是会降低更新表的效率。每次mysql对数据进行增删改操作，不仅要保存数据，也要保
MySQL深度剖析之索引专题(2021)
happy19870612's blog
10-20 533
9.1 为什么需要使用索引 第一：减少了MySQL需要扫描的数据量，尤其是全表扫描 第二：随机IO变成顺序IO，提升查询速度 可以快速匹配where子句、排序和分组也可以使用索引，匹配原则最左匹配、特定索引列的min和max计算、 9.2 MySQL 使用了哪些索引 MySQL 提供了BTree和Hash索引，一般使用的是BTree索引，Hash索引很少使用或者几乎不使用 9.3 MySQL使用的BTree索引是基于什么数据结构的，为什么不使用B-Tree而是B+Tree呢?这两者有何相
MySQL索引专题
weixin_51052174的博客
09-02 1195
索引与数据分开存储，叶子节点存储的是聚簇索引的值（通常是主键值），这样可以通过非聚簇索引快速定位到聚簇索引，然后再获取完整数据。列表示实际使用的索引，如果为 NULL，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠。显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。相比之下，使用业务字段作为主键时，数据插入的顺序可能不一致，导致频繁的页分裂和数据移动，从而增加了写入成本。查找聚簇索引获取完整行数据。
mysql 索引专题
hemeinvyiqiluoben的专栏
12-29 423
1.添加PRIMARY KEY（主键索引）  mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )  2.添加UNIQUE(唯一索引)  mysql>ALTER TABLE `table_name` ADD UNIQUE (  `column`  )  3.添加INDEX(普通索引)  mysql>ALTER TABL
MySQL索引专题一 认识索引
weixin_34413357的博客
07-20 100
关于这个专题 想写MySQL的索引专题是源于之前自己在学习MySQL索引时痛苦的经历，你在网上搜索关于MySQL的索引的文章，大多是支离破碎，没有系统性的对知识点的罗列堆砌，文章中会说明你要如何如何做，但是很少涉及去讲为什么要这么做，哪些不能做，很难对MySQL有一个系统性的认知，学习如果没有系统性的话，就很难在实际的项目中灵活运用，出于...
MySQL-索引
2301_80418043的博客
10-03 1356
MySQL的索引是一种数据结构，它可以帮助数据库高效地查询，更新数据表中的数据。索引通过一定的规则排列数据表中的记录，使得对表的查询可以通过对索引的搜索来加快速度。MySQL索引类似于书籍的目录，通过指向数据行的位置，可以快速定位和访问表中的数据，比如汉语字典的目录（索引）页，我们可以按照笔画，偏旁部首，拼音等排序目录（索引）快速查找到需要的字。显⽽易⻅，使⽤索引的⽬的只有⼀个，就是提升数据检索的效率，在应⽤程序的运⾏过程中，查询操作的频率远远⾼于增删改的频率。时间复杂度是O(1),查询速度非常快，但是My
MySQL索引专题之认识索引
zhutouzip的专栏
08-15 170
想写MySQL的索引专题是源于之前自己在学习MySQL索引时痛苦的经历，你在网上搜索关于MySQL的索引的文章，大多是支离破碎，没有系统性的对知识点的罗列堆砌，文章中会说明你要如何如何做，但是很少涉及去讲为什么要这么做，哪些不能做，很难对MySQL有一个系统性的认知，学习如果没有系统性的话，就很难在实际的项目中灵活运用，出于此目的，自己就打算写一个关于MySQL索引的专题系列，算是自己一个学习的总...
MySQL中字符串索引对update的影响分析
09-10
在MySQL数据库中，索引是一种优化查询性能的重要手段，它能加速...对于更深入的学习，可以参考相关的MySQL专题，如事务操作、存储过程、数据库锁机制以及函数应用，这些都对理解和优化MySQL数据库有着重要的指导作用。
MySQL 索引优化全攻略
12-14
而用的多，并且是mysql默认的是二叉树算法 BTREE，通过BTREE算法建立索引的字段，比如扫描20行能得到未使用BTREE前扫描了2^20行的结果，具体的实现方式后续本博客会出一个算法专题里面会有具体的分析讨论;...
MySQL 查询结果取交集的实现方法
weixin_37946518的博客
08-16 4384
1 MySQL中如何实现以下SQL查询 (SELECT S.Name FROM STUDENT S, TRANSCRIPT T WHERE S.StudId = T.StudId AND T.CrsCode = 'CS305') INTERSECT (SELECT S.Name FROM STUDENT S, TRANSCRIPT T WHERE S.StudId = T.StudId AND T...
常用sql场景
weixin_37946518的博客
09-18 955
mysql count 多表 1. https://blog.youkuaiyun.com/gwd1154978352/article/details/78606888 2.https://www.jb51.net/article/96254.htm
Mysql 优化专题
weixin_37946518的博客
04-26 572
参考： https://blog.youkuaiyun.com/long690276759/article/details/79571421 https://www.cnblogs.com/czhang2-12/p/9633127.html https://mp.weixin.qq.com/s/qtWGOu46_ShYtuTMJLHB4A https://mp.weixin.qq.com/s/o...
Mysql数据查询in的时候如何排序
weixin_37946518的博客
07-23 275
Mysql数据查询in的时候如何排序 https://www.jianshu.com/p/ea3b76c3f78a 1、select * from table_name where id in (6,2,1,10,5,7..) order by find_in_set(id,'6,2,1,10,5,7'); 2、select * from table_name where id in ...
Mysql 基础专题
weixin_37946518的博客
09-29 212
mysql 登录 --本地连接 mysql –uroot -p --远程连接 mysql –uwgb –hXXX.XXX.XXX.XXX –p mysql查询分析 https://www.cnblogs.com/xpp142857/p/7373005.html mysql 运程访问 MySql Workbench -> GRANT ALL ON jihui88_m...
mysql查询区分大小写
weixin_37946518的博客
07-30 130
查询时加 binary select * from my_user where binary name="Aa";
MPU9250九轴传感器数据融合：扩展卡尔曼滤波（EKF）算法解析与应用——以四元数、陀螺仪和加速度计为核心 · 四元数
最新发布
08-13
MPU9250 九轴传感器的工作原理及其应用背景，重点讲解了如何利用扩展卡尔曼滤波 (EKF) 进行数据融合。文中解释了选择四元数作为状态量、三轴陀螺仪的漂移作为控制量以及三轴加速度计和磁偏角作为观测量的原因。通过这种方式，在短时间尺度上优先信任陀螺仪提供的高精度角速度数据，而在长时间尺度上则依赖于加速度计提供的稳定姿态信息。此外，还提供了 Python 实现的简单 EKF 数据融合算法代码示例。 适合人群：对传感器数据融合感兴趣的电子工程技术人员、自动化控制领域的研究人员、从事机器人导航系统开发的技术人员。 使用场景及目标：适用于需要精确测量和稳定控制的场合，如无人机飞行控制系统、自动驾驶汽车的姿态感知模块、智能穿戴设备的动作捕捉单元等。目的是提高运动信息的准确性与稳定性。 其他说明：本文不仅理论阐述详尽，而且附有实际代码示例，便于读者理解和实践。对于希望深入了解传感器数据融合技术并掌握其实现细节的专业人士而言，是一份非常有价值的参考资料。
掌握Mysql索引优化技巧，提升数据库性能
根据您提供的文件信息，文件标题和描述都是“Mysql优化选择最佳索引的方法共2页.pdf.zip”，这意味着文件是一个关于MySQL数据库优化的文档，特别...通过上述的知识点，可以系统地学习和掌握MySQL索引优化的技术和方法。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值