Mysql索引知识概括

最新推荐文章于 2024-10-14 23:10:20 发布

GeorgeLin98

最新推荐文章于 2024-10-14 23:10:20 发布

阅读量1.7k

点赞数

分类专栏：持久层文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/weixin_41005188/article/details/121689141

版权

Mysql索引知识概括

索引由来
索引简介
索引分类
索引分析
索引延伸
执行计划与资源消耗

索引由来

性能下降SQL慢、执行时间长、等待时间长：

查询语句写的烂
索引失效
关联查询太多join(设计缺陷或不得已的需求)
服务器调优及各个参数设置(缓冲\线程数等)

SQL执行顺序：

手写：
机读：
总结：

常见通用的join查询： 在这里插入图片描述

索引简介

索引简介：

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高校获取数据的数据结构。
①可以得到索引的本质：索引是数据结构。
②索引的目的在于提高查询效率，可以类比字典，
③你可以简单理解为"排好序的快速查找数据结构"。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：
①为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引健值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快逮的检索出符合条件的记录。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上。索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。
我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然,除了B+树这种类型的索引之外，还有哈希索引(hash index)等。
索引优势：
①类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本
②通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗
索引劣势：
①实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录,所以索引列也是要占用空间的
②虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。
因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
③索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句

mysql索引分类：

普通索引(NORMAL)：
①单值索引： MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
②复合索引：用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引），复合索引可以代替多个单一索引，相比多个单一索引，复合索引所需的开销更小
<1>窄索引：窄索引是指索引列为1-2列的索引
<2>宽索引：索引列超过2列的索引
<3>设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效
<4>复合索引使用注意事项：
1、何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响
2、如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；
3、如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高
唯一索引(UNIQUE)： 索引列的值必须唯一，但允许有空值。（唯一约束就是一个唯一索引）
①主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束就是一个主键索引）
全文索引：
①全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。
②查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。但也很少用到。
<1>注意：在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持
空间索引：
①空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建
建议：
①一张表索引不要超过5个且优先考虑复合索引
②当索引的列名太长可以转换成前缀索引。
链接：索引的类型分类、区别、优缺点

MySQL创建表时建立索引和在已存在表中添加索引：

创建表时建立索引：
①UNIQUE | FULLTEXT | SPATIAL| NORMAL 这三个是可选项，UNIQUE 为唯一索引，FULLTEXT为全文索引，SPATIAL 为空间索引，其中SPATIAL InnoDB 存储引擎不支持，MyISAM存储引擎支持，Normal表示普通索引，大多数情况下都可以使用。
②INDEX 和 KEY 为同义词，创建索引时使用它们中任何一个都可以，INDEX_NAME 为索引名称，可选参数，如果不指定，缺省（默认值）为COLUMN_NAME ，
③LENGTH 为可选参数，可以指定索引的长度，需注意的是，只有字符串类型的列才能指定索引长度，ASC 或 DESC 指定升序或者降序的索引值存储。

CREATE TABLE Y1 (
 COLUMN_NAME  DATA_TYPE  SCHEME,
  [UNIQUE | FULLTEXT | SPATIAL |NORMAL]  [INDEX | KEY ]  INDEX_NAME([LENGTH],.....) 
  [ASC | DESC]
)

在已存在表中添加索引：
①基本语法分两种：

第一种：
ALTER TABLE TABLE_NAME ADD  [UNIQUE | FULLTEXT | SPATIAL|NORMAL]  [INDEX | KEY ]  
INDEX_NAME(COLUMN_NAME(LENGTH),.....)  [ASC | DESC]

第二种：
CREATE [UNIQUE | FULLTEXT | SPATIAL|NORMAL]  [INDEX | KEY ]  INDEX_NAME ON 
TABLE_NAME(COLUMN_NAME(LENGTH),.....)  [ASC | DESC]

索引分类

唯一索引，前缀索引，全文索引：

唯一索引：
①普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。
②如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。
③这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是,MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。
④事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
前缀索引：
①当要索引的列字符很多时，索引则会很大且变慢。（对于 BLOB、TEXT 和 VARCHAR 类型的列）
②因此可以只用索引列开始的部分字符串，节约索引空间，从而提高索引效率。
③索引的选择性，是指不重复的索引数量除以总记录数，范围是(0,1]。
④但前提要保证截取后的索引列与截取前选择性是一样的！
⑤链接：前缀索引详细链接
全文索引：
①通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。
②你可能会说，用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + %在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N倍，速度不是一个数量级，但是全文索引可能存在精度问题。
③你可能没有注意过全文索引，不过至少应该对一种全文索引技术比较熟悉：各种的搜索引擎。虽然搜索引擎的索引对象是超大量的数据，并且通常其背后都不是关系型数据库，不过全文索引的基本原理是一样的。
④使用字符串列的索引规范中的语法，您可以创建仅使用列首字符的索引。以这种方式仅索引列值的前缀可以使索引文件小得多。为a 或 column 编制索引时，必须为索引指定前缀长度。例如：col_name(N)NBLOBTEXT
⑤链接：全文索引详解

按索引底层的数据结构分类mysql索引：

链接：MySQL索引数据结构
正如上文中说到，索引是提高查询效率的数据结构，而能够提高查询效率的数据结构有很多，如二叉搜索树，红黑树，跳表，哈希表（散列表）等，而MySQL中用到了B+Tree和散列表（Hash表）作为索引的底层数据结构。
Hash索引：
①需要注意的是，MySQL并没有显式支持Hash索引，而是作为内部的一种优化，对于热点的数据会自动生成Hash索引，也叫自适应Hash索引。
②Hash索引在等值查询中，可以O（1）时间复杂度定位到数据，效率非常高，但是不支持范围查询。在许多编程语言以及数据库中都会用到这个数据结构，如Redis支持的Hash数据结构。具体结构如下：
B+Tree索引：
①提到B+Tree首先不得不提B-Tree，B-Tree（多路搜索树，并不是二叉的）是一种常见的数据结构。使用B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。