北京做网站公司推荐,山东省建设工程造价管理协会网站,做营销的网站推广,简单企业网站通俗的来说#xff0c;索引就相当于一个字典的目录#xff0c;为了方便查找书中的内容#xff0c;通过对内容建立索引形成目录#xff0c;索引是一个文件#xff0c;它是要占据物理内存的。 官方的来说索引它是一种数据结构#xff0c;数据库索引是数据库管理系统中一个排… 通俗的来说索引就相当于一个字典的目录为了方便查找书中的内容通过对内容建立索引形成目录索引是一个文件它是要占据物理内存的。 官方的来说索引它是一种数据结构数据库索引是数据库管理系统中一个排序的数据结构以协助快速查询更新数据表中的数据。 MySQL支持多种类型的索引主要包括以下几种 B-Tree索引B-Tree是最常见的索引类型可以用于全部比较操作包括、、、、、、BETWEEN和LIKE。它适合处理全值匹配和范围查询。大部分MySQL引擎默认的索引类型即为B-Tree索引。 Hash索引Hash索引基于哈希表实现只包含哈希值和行指针因此大小通常小于B-Tree索引。它非常快但只能用于等值查询使用或操作符不能用于范围查询。记住仅有MEMORY存储引擎支持Hash索引。 R-Tree索引R-Tree索引又名空间索引被用于地理数据存储而且只有MyISAM、InnoDB和NDB引擎支持。它支持对二维对象做精确查找和近似查找。 FULLTEXT索引FULLTEXT索引用于全文搜索。只有Char、Varchar、Text列可以创建全文索引并且目前只有MyISAM、InnoDB和Mroonga存储引擎支持FULLTEXT索引。 Clustered索引聚簇索引又称为主键索引是一种将数据行与键值紧密相连的索引。如果一个表有聚簇索引那么该表的数据行实际上是按照索引键来排序存储的。InnoDB存储引擎的表会自动创建主键聚簇索引。 Secondary索引副索引或者非聚簇索引在一个表中可以有多个而且不需要和数据存放在一起。它包含索引字段值和对应行数据地址的指针。
这些索引类型在使用时的具体选择取决于您的数据以及您需要进行的查询类型。
1. 索引的分类
MySQL目前主要有以下几种索引类型
普通索引唯一索引主键索引组合索引全文索引
1.1 普通索引 普通索引是 MySQL 中最基本的索引类型它没有任何限制唯一任务就是加快系统对数据的访问速度。 普通索引允许在定义索引的列中插入重复值和空值。 创建普通索引时通常使用的关键字是 INDEX 或 KEY。 【这是最基本的索引它没有任何限制。也被称为非唯一索引。】 创建普通索引的方式
创建索引
create index index_name on table_name (column_name);如果是CHARVARCHAR类型length可以小于字段实际长度如果是BLOB和TEXT类型必须指定 length。
修改表结构添加索引
alter table table_name add index index_name (column_name);创建表的时候直接指定
create table mytable
(id int not null,username varchar(16) not null,index index_name (column_name)
);删除索引的语法
drop index index_name on table_name; 1.2 唯一索引 唯一索引与普通索引类似不同的是创建唯一性索引的目的不是为了提高访问速度而是为了避免数据出现重复。 唯一索引列的值必须唯一允许有空值。如果是组合索引则列值的组合必须唯一。 创建唯一索引通常使用 UNIQUE 关键字。 【唯一索引不允许索引列有重复值。如果是组合索引则列值的组合必须是唯一的。】 创建唯一索引的方式
创建索引
create unique index index_name on table_name (column_name);
修改表结构
alter table table_name add unique index_name (column_name);创建表的时候直接指定
create table table_name
(id int not null,username varchar(16) not null,unique index_name (column_name)
); 1.3 主键索引 主键索引就是专门为主键字段创建的索引也属于索引的一种。 主键索引是一种特殊的唯一索引不允许值重复或者值为空。 创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。 【这是特殊的唯一索引不允许有空值。一个表只能有一个主键索引。】 创建主键索引的方式
创建索引
create table table_name
(id int not null,username varchar(16) not null,primary key (column_name)
);修改索引
alter table table_name add primary key (column_name);1.4 组合索引 组合索引也称为复合索引或多列索引。相对于单列索引来说组合索引是将原表的多个列共同组成一个索引。 多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段可以通过这几个字段进行查询。 但是只有查询条件中使用了这些字段中第一个字段时索引才会被使用。 【组合索引是多个列作为一个索引键用于处理查询条件中涉及多列的情况。】 创建索引
create index index_name on table_name (column_name1, column_name2);1.5 全文索引 全文索引主要用来查找文本中的关键字只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。全文索引允许在索引列中插入重复值和空值。不过对于大容量的数据表生成全文索引非常消耗时间和硬盘空间。创建全文索引使用 FULLTEXT 关键字。 【在全文索引中存储引擎会对所有数据进行分析找出每个单词的位置信息并且在查询时利用这些信息进行快速查找常用于全文搜索。注意目前仅MyISAM、InnoDB和Mroonga存储引擎支持FULLTEXT索引。】 创建索引
create fulltext index index_name on table_name (column_name);修改索引
alter table table_name add fulltext index_name (column_name);和常用的like模糊查询不同全文索引有自己的语法格式使用 match 和 against 关键字比如
select * from user where match(name) against(aaa);全文索引使用注意事项 全文索引必须在字符串、文本字段上建立。全文索引字段值必须在最小字符和最大字符之间的才会有效。innodb3-84myisam4-84全文索引字段值要进行切词处理按syntax字符进行切割例如baaa切分成b和aaa全文索引匹配查询默认使用的是等值匹配例如a匹配a不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*
select * from user where match(name) against(a* in boolean mode);2. MySQL索引的优缺点
2.1 优点
1通过创建唯一性索引可以保证数据库表中每一行数据的唯一性。 2可以大大加快数据的检索速度这也是创建索引的最主要的原因。 3可以加速表和表之间的连接特别是在实现数据的参考完整性方面特别有意义。 4在使用分组和排序子句进行数据检索时同时可以显著减少查询中分组和排序的时间。
2.2 缺点
1创建索引和维护索引需要时间成本这个成本随着数据量的增加而加大。 2创建索引和维护索引需要空间成本每一条索引都要占据数据库的物理存储空间数据量越大占用及空间也越大数据表占据的是数据库的数据空间 3会降低表的增删改的效率因为每次增删改索引需要进行动态维护导致时间边长。 3. 索引的使用场景
3.1 选择唯一性索引
唯一性索引的值是唯一的可以更加速的通过该索引来确定某条记录。 就比如学生表中学生的学号是具有唯一性的字段为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话可能会存在同名现象从而降低查询速度。
3.2 经常需要排序分组联合操作的字段建立索引
经常需要 order bygroup by distinct union 等操作的字段排序操作会浪费很多时间为其建立索引可以有效地避免排序操作。
3.3 常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件那么该字段的查询速度会影响整个表的查询速度为这样的字段建立索引可以提高整个表的查询速度。
3.4 限制索引的数目
索引的数目不是越多越好每个索引都需要占用磁盘空间索引越多需要的磁盘空间就会越大。修改表的时候对索引的重构和更新都会很麻烦索引越多更新表就会越浪费时间。
3.5 尽量使用数据量少的索引
索引的值越长查询的速度会越慢。例如一个char100类型的字段进行全文检索需要的时间比char10类型的字段需要的时间更多。
3.6 删除不再使用或很少使用的索引
表中的数据大量更新或者是数据的使用方式被改变后原有的一些索引可能不在需要数据库管理员应当定期找出这些索引将他们删除从而减少索引对更新操作的影响。
3.7 尽量的扩展索引不要新建索引
比如表中已经有a 的索引现在要加ab的索引那么只需要修改原来的索引即可
