公司展示网站模板免费下载,网站设计与实现毕业设计,亚马逊在电子商务网站建设,淘宝关闭网站建设类目文章目录 6.索引操作6.1 创建主键索引6.2 创建唯一索引6.3 创建普通索引6.4 创建全文索引6.5 查询索引6.6 删除索引6.7 索引创建原则6.8 复合索引6.9 索引最左匹配原则6.10 索引覆盖 6.索引操作
6.1 创建主键索引
第一种方式#xff1a;
– 在创建表的时候#xff0c;直接… 文章目录 6.索引操作6.1 创建主键索引6.2 创建唯一索引6.3 创建普通索引6.4 创建全文索引6.5 查询索引6.6 删除索引6.7 索引创建原则6.8 复合索引6.9 索引最左匹配原则6.10 索引覆盖 6.索引操作
6.1 创建主键索引
第一种方式
– 在创建表的时候直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));第二种方式 – 在创建表的最后指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));第三种方式
– 创建表以后再添加主键
create table user3(id int, name varchar(30));
alter table user3 add primary key(id);主键索引的特点 一个表中最多有一个主键索引当然可以使符合主键 主键索引的效率高主键不可重复 创建主键索引的列它的值不能为null且不能重复 主键索引的列基本上是int
6.2 创建唯一索引
第一种方式
– 在表定义时在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);第二种方式
– 创建表时在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));第三种方式
create table user6(id int primary key, name varchar(30)
alter table user6 add unique(name);唯一索引的特点
一个表中可以有多个唯一索引 查询效率高 如果在某一列建立唯一索引必须保证这列不能有重复数据 如果一个唯一索引上指定not null等价于主键索引
6.3 创建普通索引
第一种方式
create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定义最后指定某列为索引
);第二种方式
create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引第三种方式
create table user10(id int primary key, name varchar(20),
email varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);普通索引的特点
一个表中可以有多个普通索引普通索引在实际开发中用的比较多
如果某列需要创建索引但是该列有重复的值那么我们就应该使用普通索引
6.4 创建全文索引
当对文章字段或有大量文字的字段进行检索时会使用到全文索引。MySQL提供全文索引机制但是有要求要求表的存储引擎必须是MyISAM而且默认的全文索引支持英文不支持中文。如果对中文进行全文检索可以使用sphinx的中文版(coreseek)。
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engineMyISAM;INSERT INTO articles (title,body) VALUES
(MySQL Tutorial,DBMS stands for DataBase ...),
(How To Use MySQL Well,After you went through a ...),
(Optimizing MySQL,In this tutorial we will show ...),
(1001 MySQL Tricks,1. Never run mysqld as root. 2. ...),
(MySQL vs. YourSQL,In the following database comparison ...),
(MySQL Security,When configured properly, MySQL ...);查询有没有database数据
如果使用如下查询方式虽然查询出数据但是没有使用到全文索引 select * from articles where body like %database%;可以用explain工具看一下是否使用到索引 explain select * from articles where body like %database%\G使用全文索引
SELECT * FROM articlesWHERE MATCH (title,body) AGAINST (database);通过explain来分析这个sql语句 explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST(database)\G6.5 查询索引
第一种方法 show keys from 表名
show keys from goods\G第二种方法: show index from 表名;
第三种方法信息比较简略 desc 表名 6.6 删除索引
第一种方法-删除主键索引 alter table 表名 drop primary key;
第二种方法-其他索引的删除 alter table 表名 drop index 索引名 索引名就是show keys from 表名中的 Key_name 字段
第三种方法方法 drop index 索引名 on 表名
6.7 索引创建原则
比较频繁作为查询条件的字段应该创建索引 唯一性太差的字段不适合单独创建索引即使频繁作为查询条件 更新非常频繁的字段不适合作创建索引 不会出现在where子句中的字段不该创建索引
6.8 复合索引
复合索引也称为组合索引、联合索引或多列索引是数据库索引的一种形式它允许数据库系统根据两个或更多列的值来快速检索数据。与单列索引相比复合索引可以更有效地处理那些经常基于多个列进行搜索、排序或分组操作的查询。
工作原理 复合索引的创建方式是在表的多个列上定义一个索引这些列的组合顺序在索引定义时指定。当数据库执行查询时它会检查查询条件是否可以利用到复合索引。如果可以利用数据库就会按照索引中列的顺序来检索数据直到找到一个不满足条件的列或查询条件全部满足为止。
示例 假设有一个名为employees的表包含last_name、first_name和department_id三个列。如果经常需要根据员工的姓氏和名字来搜索员工并且偶尔还需要根据部门ID来过滤结果那么可以创建一个复合索引如CREATE INDEX idx_employee_name ON employees(last_name, first_name, department_id);。这个索引将首先按照last_name排序然后在每个last_name内按照first_name排序最后在每个first_name内按照department_id排序。
6.9 索引最左匹配原则
索引的最左匹配原则Leftmost Prefix Principle是数据库索引优化中的一个重要概念特别是在处理复合索引联合索引或多列索引时尤为重要。该原则的基本含义是当数据库执行查询操作时如果查询条件中使用了复合索引那么索引的使用将遵循从左到右的顺序即索引中的第一列最左边的列是查询条件中必须包含的如果查询条件中只包含了索引的一部分列并且这些列不是从索引的最左边开始那么索引可能无法被有效利用。
具体内容
列的顺序复合索引中的列顺序至关重要。查询条件中必须包含索引的最左边列否则索引可能不会被使用。例如如果有一个(A, B, C)的复合索引那么查询条件为A或A AND B或A AND B AND C时都可以利用这个索引。但如果查询条件仅为B或B AND C那么这个索引就不会被使用。
范围查询的影响如果查询条件中包含了范围查询如、、BETWEEN、LIKE等那么范围查询右边的列将停止使用索引。例如在(A, B, C)的复合索引上查询条件A 1 AND B 2时只有A和B会利用到索引而C则不会。
优化器的自动调整虽然查询条件中的列顺序需要遵循索引的最左匹配原则但数据库的查询优化器会尝试自动调整查询条件中列的顺序以匹配索引但这并不是总能成功的特别是在复杂查询和涉及多个表连接的情况下。
应用场景
复合索引的设计在设计复合索引时应该根据查询的实际情况来确定索引中列的顺序。通常将选择性较高的列放在索引的最前面可以提高索引的效率。
查询优化在编写查询语句时应该尽量遵循索引的最左匹配原则以充分利用索引提高查询性能。如果查询条件无法完全匹配索引的最左边列可以考虑通过调整查询条件或创建额外的索引来优化查询。
索引维护随着数据库数据的增加和查询模式的变化可能需要定期审查和调整索引策略以确保索引始终能够高效地支持查询操作。
6.10 索引覆盖
索引覆盖Covering Index是数据库索引优化中的一种重要技术它指的是一个索引包含了查询所需的所有列使得数据库在执行查询时能够直接从索引中获取所需的数据而无需再回表到主表中获取额外的数据。
定义索引覆盖是指索引上的信息足够满足查询请求不需要再回到主键或数据表中去取数据。当查询可以完全利用索引中的数据满足查询需求时就称之为索引覆盖。
原理在数据库中索引是用于加快数据检索速度的数据结构。通常情况下当执行查询时数据库需要根据查询条件在索引中找到匹配的行并通过索引中的指针回表到主表中获取完整的数据。这个过程称为回表操作或二次查询。如果索引中已经包含了查询所需的所有列那么就不需要进行回表操作可以直接从索引中获取到所需的数据这就是索引覆盖的原理。
实现方式
联合索引通过创建包含多个列的联合索引来实现索引覆盖。当查询条件中的列与联合索引中的列顺序一致并且查询的字段全部包含在联合索引中时就可以实现索引覆盖。
选择合适的列在设计索引时需要仔细考虑哪些列应该包含在索引中。通常应该选择那些经常出现在查询条件、排序和分组操作中的列作为索引的组成部分。
