移动互联与网站开发,编辑网站教程,广东阳江网络问政平台,wordpress三合一模板目录
一、索引的概述
1、索引的概念
2、索引的作用
3、索引的副作用
4、创建索引的原则依据
5、索引优化
6、索引的分类
7、数据文件与索引文件
二、管理数据库索引
1、查询索引
2、创建索引
2.1 创建普通索引
2.2 创建唯一索引
2.3 创建主键索引
2.4 创建组合…目录
一、索引的概述
1、索引的概念
2、索引的作用
3、索引的副作用
4、创建索引的原则依据
5、索引优化
6、索引的分类
7、数据文件与索引文件
二、管理数据库索引
1、查询索引
2、创建索引
2.1 创建普通索引
2.2 创建唯一索引
2.3 创建主键索引
2.4 创建组合索引
2.5 创建全文索引
3、删除索引
3.1 直接删除索引
3.2 修改表格式方式删除索引
3.3 删除主键索引
三、总结
1、索引类型
2、索引创建的三大方法 一、索引的概述
1、索引的概念
索引是一个排序的列表在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址类似于C语言的链表通过指针指向数据记录的内存地址使用索引后可以不用扫描全表来定位某行的数据而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据因此能加快数据库的查询速度索引就好比是一本书的目录可以根据目录中的页码快速找到所需的内容索引是表中一列或者若干列值排序的方法建立索引的目的是加快对表中记录的查找或排序
2、索引的作用
加快数据检索速度通过索引数据库系统可以更快地定位到需要的数据而不必扫描整个表能够大大加快查询速率。当表很大或查询涉及到多个表时可以成干上万倍地提高查询速度加速数据排序索引可以帮助数据库系统快速排序数据例如在ORDER BY子句中使用索引可以提高排序的效率优化连接操作对连接操作进行优化特别是在多表连接时索引可以显著提升查询性能约束唯一性可以使用索引来确保某些列或列组的数值在表中是唯一的这种约束可以通过UNIQUE索引或主键索引来实现降低数据库的IO成本当没有索引可用时数据库可能需要进行全表扫描以找到匹配的数据。而有了索引数据库可以避免或减少全表扫描的情况大大减少了IO操作的次数和数据量 3、索引的副作用 占用存储空间索引需要额外的存储空间。对于大型表来说索引可能会占据相当可观的存储空间 降低写操作性能当进行插入、更新和删除等写操作时索引也需要进行维护这可能导致写操作的性能下降。特别是对于频繁更新的列索引维护成本较高 增加维护成本随着数据的变化索引的效率也会发生变化。因此需要定期对索引进行优化和重建这增加了维护成本 过多索引影响性能如果表上存在过多或不必要的索引会增加查询优化器的选择路径可能导致性能下降 可能引起锁问题在某些情况下索引可能会引发锁问题尤其是在并发环境中需要谨慎处理索引以避免锁竞争 统计信息不准确有时候索引的统计信息可能不准确导致查询优化器做出不恰当的执行计划从而影响性能
4、创建索引的原则依据
索引虽可以提升数据库查询的速度但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源在有索引的情况下数据库会先进行索引查询然后定位到具体的数据行如果索引使用不当反而会增加数据库的负担。
表的主键、外键必须有索引。因为主键具有唯一性外键关联的是主表的主键查询时可以快速定位记录数超过300行的表应该有索引。如果没有索引每次查询都需要把表遍历一遍会严重影响数据库的性能经常与其他表进行连接的表在连接字段上应该建立索引唯一性太差的字段不适合建立索引更新太频繁地字段不适合创建索引经常出现在 where 子句中的字段特别是大表的字段应该建立索引在经常进行 GROUP BY、ORDER BY 的字段上建立索引索引应该建在选择性高的字段上索引应该建在小字段上对于大的文本字段甚至超长字段不要建索引
5、索引优化 经常用于查询条件的列对于经常出现在 WHERE 子句中的列特别是用作连接条件的列创建索引可以提高查询性能 频繁被用来排序的列如果某些列经常用于排序操作例如在ORDER BY子句中为这些列创建索引可以加快排序操作的速度 用作连接条件的列在多表连接时连接条件的列应该建立索引以提高连接操作的效率 唯一性约束列对于需要唯一性约束的列如主键或UNIQUE约束的列应当创建唯一索引 频繁被用于聚合函数的列如果某些列经常用于聚合函数如SUM、AVG等为这些列创建索引可以提高聚合查询的性能
6、索引的分类 B-tree索引这是最常见的索引类型适用于各种数据类型。B-tree索引通过对索引列的值进行排序构建一个类似于树形结构的索引从而加快数据的检索速度 哈希索引哈希索引基于哈希算法构建适用于等值查询例如使用或IN操作符的查询。相比B-tree索引哈希索引在等值查询时具有更好的性能但不支持范围查询和排序操作 全文索引用于全文搜索的场景例如对文本内容进行搜索。全文索引可以实现对文本内容的关键词搜索并支持模糊匹配等操作 空间索引适用于地理空间数据类型如Point、LineString、Polygon等。空间索引可以加速地理位置相关的查询例如查找某个区域内的所有点 组合索引即将多个列组合起来创建的索引可以同时提高多个列的查询效率特别是在涉及多列的查询条件时 唯一索引确保索引列的数值在表中是唯一的通常用于约束某些列或列组的唯一性 主键索引主键索引是一种特殊的唯一索引用于唯一标识表中的每一行数据
以上这些索引类型可以根据不同的场景和需求选择合适的索引策略以提高数据库的查询性能
7、数据文件与索引文件
MySQL数据库的数据文件存放在/usr/local/mysql/data目录下每个数据库对应一个子目录用于存储数据表文件。每个数据表对应为三个文件扩展名分别为“.frm”、“.MYD”和“.MYI”
“.MYD”文件是MyISAM存储引擎专用存放MyISAM表的数据。每一个MyISAM表都会有一个“.MYD”文件与之对应同样存放于所属数据库的文件夹下和“.frm”文件在一起。
“.MYI”文件也是专属于 MyISAM 存储引擎的主要存放 MyISAM 表的索引相关信息。对于 MyISAM 存储来说可以被 cache 的内容主要就是来源于“.MYI”文件中。每一个MyISAM 表对应一个“.MYI”文件存放于位置和“.frm”以及“.MYD”一样。
MyISAM 存储引擎的表在数据库中每一个表都被存放为三个以表名命名的物理文件 frm,myd,myi。 每个表都有且仅有这样三个文件做为 MyISAM 存储类型的表的存储也就是说不管这个表有多少个索引都是存放在同一个“.MYI”文件中。
还有“.ibd”和ibdata文件这两种文件都是用来存放 Innodb 数据的之所以有两种文件来存放 Innodb 的数据包括索引是因为Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据还是独享表空间存放存储数据。独享表空间存储 方式使用“.ibd”文件来存放数据且每个表一个“.ibd”文件文件存放在和 MyISAM 数据相同的位置。如果选用共享存储表空间来存放数据则会使用 ibdata 文件来存放所有表共同使用一个或者多个可自行配置ibdata 文件 二、管理数据库索引
1、查询索引
方法一show create table 表名;方法二
show index from 表名;
show index from 表名\G #竖向显示表索引信息方法三
show keys from 表名;
show keys from 表名\G; #竖向显示表索引信息 注各字段含义 字段含义Table表的名称Non_unique如果索引内容唯一则为 0如果可以不唯一则为 1Key_name索引的名称Seq_in_index索引中的列序号从 1 开始。 limit 2,3Column_name列名称Collation列以什么方式存储在索引中。在 MySQL 中有值‘A’升序或 NULL无分类Cardinality索引中唯一值数目的估计值Sub_part如果列只是被部分地编入索引则为被编入索引的字符的数目(zhangsan)。如果整列被编入索引则为 NULLPacked指示关键字如何被压缩。如果没有被压缩则为 NULLNull如果列含有 NULL则含有 YES。如果没有则该列含有 NOIndex_type用过的索引方法BTREE, FULLTEXT, HASH, RTREEComment备注 2、创建索引
2.1 创建普通索引
普通索引也被称为单列索引它仅基于单个列的数值进行排序和搜索
方法一直接创建普通索引
create index 索引名 on 表名 (列名);
create index score_index on dianzi (score);
create index name_index on dianzi (name); #使用索引实现查询
select score from dianzi;
select name from dianzi;
#查看索引信息
show create table dianzi; 方法二修改表结构方式添加普通索引
alter table 表名 add index 索引名 (列名);
alter table dianzi add index index_id (id); 方法三创建表时添加索引
create table 表名 (字段1 数据类型,字段2 数据类型,...,index 索引名 (字段));create table tongxin (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text,index index_cardid(cardid)); #插入点数据为普通索引查询
insert into tongxin values(1,cxz,13938592221,14892283,江苏南京,组长);
insert into tongxin values(2,shd,29410875347,89422959,江苏无锡,成员);
insert into tongxin values(3,djs,14870235055,50964966,江苏徐州,成员);
insert into tongxin values(4,fhy,2950435883,98503856,浙江杭州,组长);
insert into tongxin values(5,tss,22857035003,285973503,浙江宁波,成员);
insert into tongxin values(6,xhd,24289592847,284973503,浙江绍兴,成员); 注 性能影响虽然索引可以显著提高查询速度但它们也会占用额外的磁盘空间并可能降低插入、删除和更新数据的速度因为数据库需要同时更新索引选择索引列选择哪些列作为索引应该基于查询的需要。频繁作为查询条件的列或经常出现在join、where、order by等子句中的列是创建索引的好候选索引管理随着时间的推移表结构和查询模式可能会变化因此定期审查和调整索引是很重要的以确保数据库的性能最优化 2.2 创建唯一索引
唯一索引Unique Index在MySQL数据库中是一种确保数据列中每个值都是唯一的索引。通过创建唯一索引你可以保证某一列或列组合中的数据值不会重复这对于维护数据的完整性非常重要如防止同一个电子邮件地址被注册多次
与普通索引类似但区别是唯一索引列的每个值都唯一唯一索引允许有空值注意和主键不同如果是用组合索引创建则列值的组合必须唯一添加唯一键将自动创建唯一索引 方法一直接创建唯一索引
create unique index 索引名 on 表名(列名);
create unique index index_name on tongxin (name); 方法二修改表结构方式添加唯一索引
alter table 表名 add unique 索引名 (列名);
alter table tongxin add unique index_phone (phone); 方法三创建表时添加唯一索引
create table 表名 (字段1 数据类型,字段2 数据类型,...,unique 索引名 (列名));
create table jisj (id int(3) not null,name varchar(10),cardid char(15),unique index_cardid (cardid));
#插入点数据方便索引查询
insert into jisj values(1,cxz,29857012035);
insert into jisj values(2,xsf,29857012035);
insert into jisj values(2,xsf,32894385901);
insert into jisj values(3,sdh,29496291384);方法四创建表时添加唯一键会自动创建唯一索引
create table wulian (id int(3) not null,name varchar(10) unique key,cardid char(15));
#unique key表此字段唯一键约束此字段数据不可重复一个表中可有多个唯一键 #插入点数据方便唯一索引查询
insert into wulian values(1,cxz,29857012035);
insert into wulian values(2,xsf,32894385901);
insert into wulian values(3,sdh,29496291384);注 空值处理唯一索引允许列中存在空值NULL但是如果索引是多列的每列的组合必须是唯一的。对于唯一索引MySQL视多个NULL值为不同的值因此允许多个NULL值存在于唯一索引列中性能影响虽然唯一索引可以提高查询性能并保证数据的唯一性但它也可能增加插入和更新操作的开销因为每次这些操作发生时MySQL都需要检查唯一性约束使用场景唯一索引非常适合用于需要强制数据唯一性的场景如用户注册邮箱、身份证号码等 2.3 创建主键索引
主键索引是一种特殊的唯一索引不仅确保索引列的数据唯一性还能标识表中的每一行数据必须指定为“primary key”。主键的特点包括
每个表只能有一个主键主键列不能有NULL值主键自动创建唯一索引确保列值的唯一性
方法一创建表时指定主键索引
create table 表名 (字段1 数据类型,字段2 数据类型,...,primary key (列名));
create table class (id int(3) not null,name varchar(10) not null,cardid char(11) not null,primary key(id)); #插入数据方便使用主键索引查询
insert into class values(1,sjg,9432005023);
insert into class values(2,sfh,2389420509);
insert into class values(2,dkg,49848658802);
insert into class values(3,jkd,32840437588); 方法二修改表结构方式添加主键索引
alter table 表名 add primary key (列名);
alter table class add primary key (cardid);
alter table wulian add primary key (id); 注 如果表中已经有数据给列添加主键前需要确保该列的所有值都是唯一的且没有NULL值。考虑到性能和存储效率主键通常设置为整型或UUID。主键的选择对数据库性能有重要影响应谨慎选择能唯一标识每行数据的列作为主键。 2.4 创建组合索引
组合索引也称为复合索引或多列索引是指基于表中两个或多个列创建的索引。组合索引可以提高查询性能特别是在查询条件涉及多个列时。它按照索引中列的顺序存储数据这使得数据库能够高效地利用索引来加速查询和排序操作
需要满足最左原则因为select语句的 where条件是依次从左往右执行的所以在使用select 语句查询时where条件使用的字段顺序必须和组合索引中的排序一致否则索引将不会生效
方法一创建表时指定组合索引
create table 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,……,index 索引名 (列名1,列名2,列名3,……));
create table bak (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text,index index_bak(name,cardid,address)); #插入点数据为组合索引查询
insert into bak values(1,cxz,13938592221,14892283,江苏南京,组长);
insert into bak values(2,shd,29410875347,89422959,江苏无锡,成员);
insert into bak values(3,djs,14870235055,50964966,江苏徐州,成员); 方法二修改表结构方式添加组合索引
alter table 表名 add index 索引名 (字段1,字段2,字段3,……);
alter table wulian add index index_wulian (id,name); 注 索引顺序在定义组合索引时列的顺序非常重要。应根据查询中最常用的列来安排索引中的列顺序性能影响虽然索引可以提高查询性能但过多的索引会增加插入、更新和删除操作的开销因为数据库需要维护这些索引。因此应根据实际需要创建索引前导列优化尽可能利用索引的前导列进行查询以充分利用组合索引的优势 2.5 创建全文索引
全文索引通过创建文本数据的索引来加速对文本的搜索查询它会分析文本列中的内容将文本分解成词元tokens或关键词并建立一个搜索关键词的索引。这样当进行文本搜索时数据库可以直接利用这个索引来快速定位包含特定关键词的记录而不是逐行扫描整个表。
全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。每个表只允许有一个全文索引
方法一直接创建全文索引
create fulltext index 索引名 on 表名 (列名);
create fulltext index index_address on tongxin (address); select * from tongxin where address浙江杭州; #全文索引查询关键字“浙江杭州” 方法二修改表结构方式添加全文索引
alter table 表名 add fulltext 索引名 (列名);alter table bak add fulltext index_remark (remark); select * from bak where remark组长; #使用全文索引搜索其中关键字是“组长” 方法三创建表时指定全文索引
create table 表名 (字段1 数据类型,字段2 数据类型,……,fulltext 索引名 (列名));
create table zhizao (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text,index,fulltext index_address (address)); #插入点数据为全文索引查询
insert into zhizao values(1,cxz,13938592221,14892283,江苏南京,组长);
insert into zhizao values(2,shd,29410875347,89422959,江苏无锡,成员);
insert into zhizao values(3,djs,14870235055,50964966,江苏盐城,成员);
select * from zhizao where address江苏南京; 注 存储引擎限制全文索引最初只支持MyISAM存储引擎。从MySQL 5.6版本开始InnoDB存储引擎也支持全文索引性能考虑虽然全文索引可以显著提高文本搜索的速度但它也会占用额外的磁盘空间并可能增加文本数据更新操作的开销查询语法使用全文索引时可以利用match() ... against()语法进行搜索查询这允许执行包含布尔文本搜索和自然语言搜索的复杂查询 3、删除索引
3.1 直接删除索引
drop index 索引名 on 表名;
drop index index_address on zhizao; 3.2 修改表格式方式删除索引
alter table 表名 drop index 索引名;
alter table bak drop index index_bak;
alter table bak drop index index_remark; 3.3 删除主键索引
alter table 表名 drop primary key;
alter table class drop primary key; 三、总结
1、索引类型
索引名称键标识说明一张表索引数量普通索引key针对所有字段没有特殊的需求/规则可多个唯一键索引unique key针对唯一性的字段仅允许出现一次空值可多个主键索引primary key针对唯一性字段、且不可为空同时一张表只允许包含一个主键索引一个组合索引key多列/多字段组合形式的索引可多个全文索引fulltext keyvarchar char text全文搜索内容可多个
2、索引创建的三大方法
①直接创建索引
索引名称格式普通索引create index 索引名 on 表名 (列名);唯一键索引create unique index 索引名 on 表名(列名);主键索引无组合索引无全文索引create fulltext index 索引名 on 表名 (列名);
②alter修改表结构的时候进行add添加索引
索引名称格式普通索引alter table 表名 add index 索引名 (列名);唯一键索引alter table 表名 add unique 索引名 (列名);主键索引alter table 表名 add primary key (列名); 组合索引alter table 表名 add index 索引名 (字段1,字段2,字段3,……);全文索引alter table 表名 add fulltext 索引名 (列名);
③在创建表的时候直接指定创建索引
索引名称格式普通索引create table 表名 (字段1 数据类型,字段2 数据类型,...,index 索引名 (字段));唯一键索引create table 表名 (字段1 数据类型,字段2 数据类型,...,unique 索引名 (列名));主键索引create table 表名 (字段1 数据类型,字段2 数据类型,...,primary key (列名));组合索引create table 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,……,index 索引名 (列名1,列名2,列名3,……));全文索引create table 表名 (字段1 数据类型,字段2 数据类型,……,fulltext 索引名 (列名));
