网站搭建公司,做爰全过程免费的网站视频,设计之家软件,桂林两江四湖地图#x1f4a1; 索引 在关系型数据库中#xff0c;索引是一种单独的、物理上的对数据库表中的一列或多列的值进行排序的一种存储结构#xff0c;他是某个表中的一列或着若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单#xff08;类似于图书目录#x… 索引 在关系型数据库中索引是一种单独的、物理上的对数据库表中的一列或多列的值进行排序的一种存储结构他是某个表中的一列或着若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单类似于图书目录通过图书页码迅速找到所需内容。一个索引是存储的表中一个特定列的值数据结构。索引是在表的列上创建。索引包含一个表中列的值并且这些值存储在一个数据结构中。简单来说不需要加内存不用改程序不用调SQL查询速度就可以提高百倍千倍。 优点 提高数据的检索速度降低数据排序的成本。 缺点 会降低更新表的速度。 举例说明创建一张表表中存了8000000条数据这时查询数据的速度就会变慢。
SELECT * FROM emp WHERE enamehPBmJv会发现数据检索的时间是4.591s非常的慢。 在表的存放文件夹中可以看到emp表现在的大小是655360KB。 之后创建索引再次查询
-- ename_index :索引名称
-- ON emp(ename) 表示在emp表的ename列创建索引
CREATE INDEX ename_index ON emp(ename);
-- 然后再次查询数据
SELECT * FROM emp WHERE enameQZABhi可以看到检索速度明显变快只用了0.034s就完成查询。但是相应的我们再次查看emp表的.ibd文件就会发现此时的文件大小为827392KB,可以得出创建索引索引本身也会占用空间。 总结:在创建索引前emp.ibd文件大小为655360KB检索时间为4.591s创建索引后文件内存增加至827392KB检索速度提升至0.034s。
常见的索引类型包括以下几种
主键索引Primary Key Index主键索引是一种特殊的唯一索引它默认为表的主键即用来唯一标识每一行数据的索引。主键索引可以保证数据的唯一性并且可以快速定位到指定的行。唯一索引Unique Index唯一索引也是一种保证数据唯一性的索引与主键索引不同的是唯一索引可以定义多个一个表中可以有多个唯一索引。唯一索引可以加速数据查找并且可以帮助避免重复数据的插入。普通索引Normal Index普通索引是最基本的索引类型它可以加速数据的查找。对于非唯一值普通索引可以在查找操作中提供一个快速的匹配过程。全文索引Fulltext Index全文索引是用于大文本字段的索引类型可以支持高速的文本搜索。 索引机制
索引的原理
没有索引为什么会慢 如果SQL查询语句没有合适的索引那么它将执行全表扫描full table scan这种方式需要对整个表进行遍历查找符合查询条件的数据会产生以下几种性能问题
大量的I/O操作全表扫描会将整个表的数据读入内存导致额外的 I/O 操作包括磁盘读取、内存分配等占用大量计算资源。低效的查询速度由于全表扫描需要遍历整个表因此查询速度较慢尤其是当表中数据量很大时。阻塞其他操作全表扫描的过程中会占用大量的 CPU 和内存资源从而影响数据库的性能阻塞其他操作的执行。 相比之下使用索引能够降低查询的时间复杂度快速定位到符合条件的数据提高查询效率。因为索引可以直接跳过不满足查询条件的数据只对符合条件的数据进行读取和处理避免了无谓的数据读取和处理。此外索引还可以减少 I/O 操作、缩短查询时间、提高数据库的并发能力。
使用索引为什么会快 SQL 索引是一种用于加速数据库查询操作的数据结构它可以降低查询所需的时间复杂度提高查询效率从而优化数据库的性能。索引通常是通过 B-tree 或哈希表等数据结构来实现的。 B-tree 索引是 SQL 中最常见的索引类型之一它是一种基于平衡树balanced tree的数据结构可以快速定位需要的数据。B-tree 索引在每个节点上存储一些关键字和指向下层子节点的指针根据关键字的大小对节点进行排序并将其分裂成较小的节点从而保证平衡树的深度不会太大。 当查询语句中包含索引列时数据库会通过索引快速地定位到相关数据的物理位置然后进行数据的读取和处理。这种方式称为索引扫描index scan相比于全表扫描table scan可以大大减少查询所需的时间和资源。 索引的选择是一个重要的问题。虽然索引可以加速查询操作但过多的索引可能会降低数据库的性能因为索引的维护也需要消耗计算和存储资源。一般来说应该仅在必要的列上创建索引优先考虑高频使用、复杂查询或者需要排序 / 分组的列并且要注意避免对索引列进行计算或类型转换等操作否则可能会导致索引失效。 此外当数据量较小时索引对性能的提升可能并不显著而当数据量很大或者查询条件较为复杂时合理使用索引可以大大提高查询效率和性能。因此在使用索引时需要根据具体情况进行权衡和调整以达到最佳的性能优化效果。
索引的代价
虽然SQL索引可以提高查询性能和效率但也会增加一些代价主要包括以下几个方面
空间代价索引需要占用一定的磁盘空间特别是当表中数据很大时会消耗大量的存储空间。此外在建立索引的过程中需要对索引列进行排序和分组也会增加额外的存储开销。更新代价当表中数据被修改时索引也需要相应地进行维护和更新。这会耗费计算资源和时间并且在大规模更新操作时可能会导致锁定表的情况影响数据库的并发性能。查询代价虽然索引可以提高查询效率但是在某些情况下索引查询可能比全表扫描更慢尤其是当索引列不优化或者数据分布不均匀时。此时索引查询还需要读取索引文件、I/O 操作等会增加一定的查询代价。维护代价当索引不再需要或者失效时也需要对其进行删除或者清理操作。此操作也需要消耗一定的时间和资源如果索引失效不及时清理还可能产生额外的性能损失和异常情况。 因此在选择和使用索引时需要根据数据库的实际情况和需求综合考虑上述代价因素做出合理的决策和管理调整以实现优化查询性能的目标。
索引使用
在使用索引前先创建一张表测试方便使用索引举例。
CREATE TABLE IF NOT EXISTS student(id INT,name VARCHAR(32)
);查询索引
-- 查询表是否有索引table_name是需要查询索引的表名。
-- 在sql中可以使用 SHOW INDEX 或者 SHOW INDEXES 语句来查询表的索引信息
SHOW INDEX FROM table_name;
-- 例查询刚才创建的表的索引
SHOW INDEX FROM student;创建索引
主键索引
在 SQL 中添加主键索引有两种方式在表创建时添加主键索引或者在表创建后通过 ALTER TABLE 语句添加主键索引。
在表创建时添加主键索引
CREATE TABLE table_name (column1 datatype PRIMARY KEY,column2 datatype,column3 datatype,
);在创建表时添加主键索引需要在列名和数据类型之间加上 PRIMARY KEY 关键字表示该列是主键并且需要建立主键索引。 在表创建后添加主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column1);在表创建后添加主键索引需要使用 ALTER TABLE 语句使用 ADD PRIMARY KEY 子句来指定添加主键索引的列名。 需要注意的是每个表只能有一个主键所以无论是在表创建时还是在表创建后添加主键索引都只能为一个列指定主键属性。
唯一索引
在 SQL 中添加唯一索引有两种方式在表创建时添加唯一索引或者在表创建后通过 ALTER TABLE 语句添加唯一索引。
在表创建时添加唯一索引
CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype,UNIQUE (column1)
);在创建表时添加唯一索引需要在列名和数据类型之后添加 UNIQUE 关键字并指定需要建立唯一索引的列名。这样就可以为该列或多个列建立唯一索引。 在表创建后添加唯一索引
ALTER TABLE table_name ADD UNIQUE (column1);在表创建后添加唯一索引需要使用 ALTER TABLE 语句使用 ADD UNIQUE 子句来指定添加唯一索引的列名。 需要注意的是每个表可以有多个唯一索引但每个唯一索引只能包含一个或多个列且不能重复。
普通索引
在 SQL 中添加普通索引有两种方式在表创建时添加普通索引或者在表创建后通过CREATE INDEX或ALTER TABLE语句添加普通索引。
在表创建时添加普通索引
CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype,INDEX index_name (column1)
);在创建表时添加普通索引需要使用 INDEX 关键字指定需要建立索引的列名并指定索引名称。这样就可以为该列或多个列建立普通索引。 在表创建后添加普通索引
CREATE INDEX index_name ON table_name (column1);或者
ALTER TABLE table_name ADD INDEX index_name (column1);在表创建后添加普通索引可以使用 CREATE INDEX 或 ALTER TABLE 语句。其中CREATE INDEX 语句用于创建新的索引而 ALTER TABLE 语句用于在现有表中添加索引。需要指定索引名称和需要建立索引的列名。 需要注意的是每个表可以有多个普通索引但每个普通索引只能包含一个或多个列。普通索引可以在等值查询如 和 IN 等操作和范围查询如 、、BETWEEN 等操作时提高查询效率。
全文索引 在 SQL 中添加全文索引需要先确保使用的数据库支持全文索引功能如 MySQL、SQL Server 等在此基础上可以通过以下步骤添加全文索引
创建包含全文索引列的表
CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype,FULLTEXT (column1, column2)
);在创建表时指定需要包含全文索引的列并使用 FULLTEXT 关键字表示这是一个全文索引列 添加全文索引
ALTER TABLE table_name ADD FULLTEXT (column1, column2);在已有的表中添加全文索引使用 ALTER TABLE 语句并指定需要添加全文索引的列。 使用全文索引进行查询 使用全文索引进行查询需要使用 MATCH AGAINST 函数。该函数会根据关键词匹配全文索引列的内容并返回匹配度得分可以单独使用也可以与其他条件合并使用。
SELECT * FROM table_name WHERE MATCH (column1, column2) AGAINST (search_keyword);注意全文索引通常只适用于大型文本数据如文章、博客等。对于结构化数据如数值、日期等使用普通索引或主键索引会更有效率。同时全文索引可能会占用较多的存储空间和内存资源需要谨慎使用。
删除索引
主键索引
使用 ALTER TABLE 语句来删除表中的主键索引具体步骤如下
查看表中的主键约束名
SHOW INDEX FROM table_nmae;
-- 或者使用下面的语句
SHOW CREATE TABLE table_name;在结果中找到主键的约束名通常以 PRIMARY KEY 关键字开头形如 PRIMARY KEY (column_name)。 删除主键索引
ALTER TABLE table_name DROP PRIMARY KEY;其中 PRIMARY KEY 表示要删除的主键约束名可以替换为实际的约束名。 注意删除主键索引会同时删除主键约束如果该列还存在外键依赖则无法删除主键索引。此外删除主键索引会影响到已有数据的完整性需要谨慎操作。因此在删除主键索引之前最好先备份数据并确认没有外键依赖。
唯一索引
使用 ALTER TABLE 语句来删除表中的唯一索引具体步骤如下
查看表中的唯一索引名
使用以下 SQL 语句查看表中的唯一索引名
SHOW CREATE TABLE table_name;
-- 或者使用下面的语句
SHOW CREATE TABLE table_name;在结果中找到需要删除的唯一索引通常以 UNIQUE KEY 关键字开头形如 UNIQUE KEY index_name (column_name)。
删除唯一索引
使用以下 SQL 语句删除唯一索引
ALTER TABLE table_name DROP INDEX index_name;其中 index_name 表示需要删除的唯一索引名称可以替换为实际的索引名称。
注意删除唯一索引会影响到已有数据的完整性需要谨慎操作。因此在删除唯一索引之前最好先备份数据并确认该列没有对应的外键依赖。
普通索引
你可以使用 ALTER TABLE 语句来删除表中的普通索引具体步骤如下
查看表中的普通索引名
使用以下 SQL 语句查看表中的普通索引名
SHOW CREATE TABLE table_name;
-- 或者使用下面的语句
SHOW CREATE TABLE table_name;在结果中找到需要删除的普通索引通常以 KEY 关键字开头形如 KEY index_name (column_name)。
删除普通索引
使用以下 SQL 语句删除普通索引
ALTER TABLE table_name DROP INDEX index_name;其中 index_name 表示需要删除的普通索引名称可以替换为实际的索引名称。
注意删除普通索引会影响到查询性能因此需要谨慎操作。在删除索引之前最好先确认该索引没有被其他查询语句使用。
全文索引
使用 ALTER TABLE 语句来删除表中的全文索引具体步骤如下
查看表中的全文索引名
使用以下 SQL 语句查看表中的全文索引名
SHOW CREATE TABLE table_name;在结果中找到需要删除的全文索引通常以 FULLTEXT KEY 关键字开头形如 FULLTEXT KEY index_name (column_name)。
删除全文索引
使用以下 SQL 语句删除全文索引
ALTER TABLE table_name DROP INDEX index_name;其中 index_name 表示需要删除的全文索引名称可以替换为实际的索引名称。
注意删除全文索引会影响到查询性能和搜索功能因此需要谨慎操作。在删除索引之前最好先确认该索引没有被其他查询语句或搜索功能使用。 创建索引规则
在 SQL 中创建索引有以下几个规则 索引只应该针对经常被查询的列创建而不是所有列都要创建索引。 索引的名称应该具有描述性以便于识别和维护并且名称应该唯一。 索引应该基于选择性较高的列进行创建。选择性是指该列中包含的唯一值的数量与表中总记录数之比。选择性较高的列通常可以提供更好的搜索性能因为它们可以过滤掉大量的记录。 如果多个列一起经常用于查询或排序则应该将它们放在一个联合索引中而不是各自创建单独的索引。这样可以提高这些列的查询效率。 如果某个列包含 NULL 值的百分比很高那么就不应该为其创建索引因为索引对 NULL 值的过滤效果很差。 如果表中的数据量很小则可能没有必要为其创建索引因为索引的建立和维护本身也会带来一定的开销。 对于频繁更新的表过多的索引可能会增加更新时间和磁盘空间占用因此需要谨慎创建索引。
总之在创建索引时需要根据具体情况进行综合考虑权衡索引的使用效果和成本以达到优化查询性能的目的。
