陇南市响应式网站建设,com网站域名注册,外贸仿牌网站被封的后果,网站的前台后台索引介绍索引是对数据库表中一列或者多列的值进行排序的一种结构#xff0c;使用索引可提高数据库中特定数据的查询速度。索引是一个单独的、存储在磁盘上的数据库结构#xff0c;它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值得…索引介绍 索引是对数据库表中一列或者多列的值进行排序的一种结构使用索引可提高数据库中特定数据的查询速度。 索引是一个单独的、存储在磁盘上的数据库结构它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值得行所有MySQL列类型都可以被索引对相关列使用索引是提高查询操作速度的最佳途径。 索引是在存储引擎中实现的因此每种存储引擎的索引都不一定完全相同并且每种存储引擎也不一定支持索引类型。 根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引总索引长度至少为256字节。 MyISAM InnoDB支持btree索引Memory 支持 btree和hash索引索引的优势加快查询速度创建唯一索引来保证数据表中数据的唯一性实现数据的完整性加速表和表之间的连接减少分组和排序的时间增加索引增加索引也有许多不利主要表现在如下几个方面创建索引和维护索引要耗费时间并且随着数据量的增加所耗费的时间也会增加。索引需要占磁盘空间除了数据表占数据空间之外每一个索引还要占一定的物理空间如果有大量的索引索引文件可能比数据文件更快达到最大文件尺寸。当对表中的数据进行增加、删除和修改的时候索引也要动态地维护这样就降低了数据的维护速度。索引的分类唯一索引和普通索引 普通索引是MySQL中的基本索引类型允许在定义索引的列中插入重复值和空值。唯一索引索引列的值必须唯一但允许有空值。如果是组合索引则列值的组合必须唯一。主键索引是一种特殊的唯一索引不允许有空值。单列索引和组合索引 单列索引即一个索引只包含单个列一个表可以有多个单列索引。 组合索引指在表的多个字段组合上创建的索引。只有在查询条件中使用了这些字段的左边字段时索引才会被使用。使用组合索引时遵循最左前缀集合。全文索引 fulltext 全文索引类型为FULLTEXT在定义索引的列上支持值得全文查找允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。空间索引 空间索引是对空间数据类型的字段建立的索引MySQL中的空间数据类型有4中分别是geometry、point、linstring和polygon 。MySQL使用SPATIAL关键字进行扩展使得能够用于创建空间索引的列必须将其声明为NOT NULL空间索引只能在存储引擎为MyISAM的表中创建。创建索引的规则 (1)创建索引并非是越多越好一个表中如果有大量的索引不仅占用磁盘空间而且会影响insert、delete、update等语句的性能。因为当表中的数据更改时索引也会进行调整和更新。 (2)数据量小得表最好不要创建索引由于数据较少查询花费的时间可能比遍历索引的时间还要长。 (3)避免对经常更新的数据创建索引。而对经常用于查询的字段应该创建索引。 (4)在条件表达式中经常用到的不同值较多的列创建索引 (5)当唯一性是某种数据本身的特征时我们创建唯一性索引 (6)在频繁进行排序或分组的列上建立索引如果排序的列有多个可以创建组合索引创建索引Create index 创建索引 alter table 添加索引 Create table 表名[字段名 数据类型] [unique唯一索引|fulltext全文索引|spatial空间索引] [index|key] [索引名] (col_name [length]) [asc |desc]普通索引 普通索引是最基础的索引类型没有唯一性的限制。作用是只加快对数据的访问速度。 先创建book表创建索引为year_publicationmysql CREATE TABLE book- (- bookid INT NOT NULL,- bookname VARCHAR(255) NOT NULL,- authors VARCHAR(255) NOT NULL,- info VARCHAR(255) NULL,- comment VARCHAR(255) NULL,- year_publication YEAR NOT NULL,- INDEX(year_publication)- );
Query OK, 0 rows affected (0.03 sec)查看创建的索引 注\G结尾表示纵向查看mysql show create table book\G
*************************** 1. row ***************************Table: book
Create Table: CREATE TABLE book (bookid int(11) NOT NULL,bookname varchar(255) COLLATE utf8_bin NOT NULL,authors varchar(255) COLLATE utf8_bin NOT NULL,info varchar(255) COLLATE utf8_bin DEFAULT NULL,comment varchar(255) COLLATE utf8_bin DEFAULT NULL,year_publication year(4) NOT NULL,KEY year_publication (year_publication)
) ENGINEInnoDB DEFAULT CHARSETutf8 COLLATEutf8_bin
1 row in set (0.00 sec)explain 使用该命令判断索引是否正在被使用mysql explain select * from book where year_publication1999\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: bookpartitions: NULLtype: ref
possible_keys: year_publicationkey: year_publicationkey_len: 1ref: constrows: 1filtered: 100.00Extra: Using index condition
1 row in set, 1 warning (0.00 sec)TYPE的取值范围 System const ref eq_ref index all range唯一索引 创建唯一索引 唯一索引主要原因是减少查询索引列操作的执行时间。尤其是对比比较庞大的数据表。与普通索引类似不同点在于索引列的值必须唯一但允许有空值。如果是组合索引则列值的组合必须唯一。创建唯一索引mysql CREATE TABLE t1- (- id INT NOT NULL,- name CHAR(30) NOT NULL,- UNIQUE INDEX UniqIdx(id)- );
Query OK, 0 rows affected (0.00 sec)查看唯一索引mysql show create table t1\G
*************************** 1. row ***************************Table: t1
Create Table: CREATE TABLE t1 (id int(11) NOT NULL,name char(30) COLLATE utf8_bin NOT NULL,UNIQUE KEY UniqIdx (id)
) ENGINEInnoDB DEFAULT CHARSETutf8 COLLATEutf8_bin
1 row in set (0.00 sec)单列索引 单列索引是在数据表中的某一字段上创建的索引一个表中可以创建多个单列索引。 创建单列索引mysql CREATE TABLE t2- (- id INT NOT NULL,- name CHAR(50) NULL,- INDEX SingleIdx(name)- );
Query OK, 0 rows affected (0.01 sec)查看单列索引mysql show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE t2 (id int(11) NOT NULL,name char(50) COLLATE utf8_bin DEFAULT NULL,KEY SingleIdx (name)
) ENGINEInnoDB DEFAULT CHARSETutf8 COLLATEutf8_bin
1 row in set (0.00 sec)组合索引 组合索引是在多个字段上创建一个索引。遵循最左前缀原则。最左前缀 索引最左边的列来匹配行 创建组合索引mysql CREATE TABLE t3- (- id INT NOT NULL,- name CHAR(30) NOT NULL,- age INT NOT NULL,- info VARCHAR(255),- INDEX MultiIdx(id,name,age)- );
Query OK, 0 rows affected (0.02 sec)查看组合索引mysql show create table t3\G
*************************** 1. row ***************************Table: t3
Create Table: CREATE TABLE t3 (id int(11) NOT NULL,name char(30) COLLATE utf8_bin NOT NULL,age int(11) NOT NULL,info varchar(255) COLLATE utf8_bin DEFAULT NULL,KEY MultiIdx (id,name,age)
) ENGINEInnoDB DEFAULT CHARSETutf8 COLLATEutf8_bin
1 row in set (0.00 sec) 组合索引可以起几个索引的作用但是使用时并不是随意查询哪个字段都是可以使用索引。而是遵循最左前缀利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。全文索引全文索引FULLTEXT可以用于全文搜索5.7版本以前只有MyISAM存储引擎支持而5.7版本以后innodb存储引擎也支持并且只为CHAR\VARCHAR和TEXT 列。索引总是对整个列进行不支持局部索引适合大型数据的表创建全文索引mysql CREATE TABLE t4- (- id INT NOT NULL,- name CHAR(30) NOT NULL,- age INT NOT NULL,- info VARCHAR(255),- FULLTEXT INDEX FullIdx(info(100))- )ENGINEMyISAM;
Query OK, 0 rows affected (0.00 sec)查看全文索引mysql show create table t4\G
*************************** 1. row ***************************Table: t4
Create Table: CREATE TABLE t4 (id int(11) NOT NULL,name char(30) COLLATE utf8_bin NOT NULL,age int(11) NOT NULL,info varchar(255) COLLATE utf8_bin DEFAULT NULL,FULLTEXT KEY FullIdx (info)
) ENGINEMyISAM DEFAULT CHARSETutf8 COLLATEutf8_bin
1 row in set (0.00 sec)例创建xx34表info列为全文索引并搜索。MATCH (col1,col2,...) AGAINST (expr [search_modifier])create table xx34(id int,name varchar(20),info varchar(255),fulltext index chidx(info) with parser ngram);select * from xx34 where match(info) against(主义);空间索引空间索引必须在MyISAM类型的表中创建且空间类型的字段必须为非空。 创建空间索引mysql CREATE TABLE t5- (- g GEOMETRY NOT NULL,- SPATIAL INDEX spaIdx(g) - )ENGINEMyISAM;
Query OK, 0 rows affected (0.01 sec)查看空间索引mysql show create table t5\G
*************************** 1. row ***************************Table: t5
Create Table: CREATE TABLE t5 (g geometry NOT NULL,SPATIAL KEY spaIdx (g)
) ENGINEMyISAM DEFAULT CHARSETutf8 COLLATEutf8_bin
1 row in set (0.00 sec)在已经存在的表上创建索引Alter table 表名 add [unique唯一索引|fulltext全文索引|spatial空间索引] [index|key] [默认索引名] (定义索引名[length]) [asc|desc]添加索引在book表添加索引mysql alter table book add index BKNameIdx(bookname(30));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0查看索引mysql show index from book\G
*************************** 1. row ***************************Table: bookNon_unique: 1Key_name: year_publicationSeq_in_index: 1Column_name: year_publicationCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment:
Index_comment:
*************************** 2. row ***************************Table: bookNon_unique: 1Key_name: BKNameIdxSeq_in_index: 1Column_name: booknameCollation: ACardinality: 0Sub_part: 30Packed: NULLNull: Index_type: BTREEComment:
Index_comment:
2 rows in set (0.00 sec)添加唯一索引添加索引mysql alter table book add unique index Uniqldx(bookid);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0查看索引mysql show index from book\G
*************************** 1. row ***************************Table: bookNon_unique: 0Key_name: UniqldxSeq_in_index: 1Column_name: bookidCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment:
Index_comment:
*************************** 2. row ***************************Table: bookNon_unique: 1Key_name: year_publicationSeq_in_index: 1Column_name: year_publicationCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment:
Index_comment:
*************************** 3. row ***************************Table: bookNon_unique: 1Key_name: BKNameIdxSeq_in_index: 1Column_name: booknameCollation: ACardinality: 0Sub_part: 30Packed: NULLNull: Index_type: BTREEComment:
Index_comment:
3 rows in set (0.00 sec)添加单列索引添加索引mysql alter table book add index BKidex(comment(50));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0查看索引mysql show index from book\G
*************************** 1. row ***************************Table: bookNon_unique: 0Key_name: UniqldxSeq_in_index: 1Column_name: bookidCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment:
Index_comment:
*************************** 2. row ***************************Table: bookNon_unique: 1Key_name: year_publicationSeq_in_index: 1Column_name: year_publicationCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment:
Index_comment:
*************************** 3. row ***************************Table: bookNon_unique: 1Key_name: BKNameIdxSeq_in_index: 1Column_name: booknameCollation: ACardinality: 0Sub_part: 30Packed: NULLNull: Index_type: BTREEComment:
Index_comment:
*************************** 4. row ***************************Table: bookNon_unique: 1Key_name: BKidexSeq_in_index: 1Column_name: commentCollation: ACardinality: 0Sub_part: 50Packed: NULLNull: YESIndex_type: BTREEComment:
Index_comment:
4 rows in set (0.00 sec)添加全文索引添加索引mysql CREATE TABLE t6- (- id INT NOT NULL,- info CHAR(255)- )ENGINEMyISAM;
Query OK, 0 rows affected (0.00 sec)mysql ALTER TABLE t6 ADD FULLTEXT INDEX InfoFULIdx(info);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0查看全文索引mysql show index from t6\G
*************************** 1. row ***************************Table: t6Non_unique: 1Key_name: InfoFULIdxSeq_in_index: 1Column_name: infoCollation: NULLCardinality: NULLSub_part: NULLPacked: NULLNull: YESIndex_type: FULLTEXTComment:
Index_comment:
1 row in set (0.00 sec)添加组合索引添加索引mysql ALTER TABLE book ADD INDEX BKAUthAndInfoIdx(authors(20),info(50));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0查看索引mysql show index from book\G
*************************** 1. row ***************************Table: bookNon_unique: 0Key_name: UniqldxSeq_in_index: 1Column_name: bookidCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment:
Index_comment:
*************************** 2. row ***************************Table: bookNon_unique: 1Key_name: year_publicationSeq_in_index: 1Column_name: year_publicationCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment:
Index_comment:
*************************** 3. row ***************************Table: bookNon_unique: 1Key_name: BKNameIdxSeq_in_index: 1Column_name: booknameCollation: ACardinality: 0Sub_part: 30Packed: NULLNull: Index_type: BTREEComment:
Index_comment:
*************************** 4. row ***************************Table: bookNon_unique: 1Key_name: BKidexSeq_in_index: 1Column_name: commentCollation: ACardinality: 0Sub_part: 50Packed: NULLNull: YESIndex_type: BTREEComment:
Index_comment:
*************************** 5. row ***************************Table: bookNon_unique: 1Key_name: BKAUthAndInfoIdxSeq_in_index: 1Column_name: authorsCollation: ACardinality: 0Sub_part: 20Packed: NULLNull: Index_type: BTREEComment:
Index_comment:
*************************** 6. row ***************************Table: bookNon_unique: 1Key_name: BKAUthAndInfoIdxSeq_in_index: 2Column_name: infoCollation: ACardinality: 0Sub_part: 50Packed: NULLNull: YESIndex_type: BTREEComment:
Index_comment:
6 rows in set (0.00 sec)添加空间索引添加索引mysql CREATE TABLE t7- (- g GEOMETRY NOT NULL- )ENGINEMyISAM;
Query OK, 0 rows affected (0.01 sec)mysql ALTER TABLE t7 ADD SPATIAL INDEX SpatIdx(g);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0查看索引mysql show index from t7\G
*************************** 1. row ***************************Table: t7Non_unique: 1Key_name: SpatIdxSeq_in_index: 1Column_name: gCollation: ACardinality: NULLSub_part: 32Packed: NULLNull: Index_type: SPATIALComment:
Index_comment:
1 row in set (0.00 sec)创建索引 创建一个book1表mysql create table book1 (bookid int not null, bookname varchar(255) not null,
- authors varchar(255) not null, info varchar(255) null, comment varchar(255) null,
- year_publication year not null );
Query OK, 0 rows affected (0.01 sec)普通索引mysql create index bknameidex on book1(bookname);
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0单列索引mysql create index bkcmtidex on book1 (comment(50));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0组合索引mysql create index bkauthandinfoidex on book1(authors(30),info(50));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0全文索引mysql drop table t6;
Query OK, 0 rows affected (0.00 sec)
mysql create table t6 ( id int not null, info char(255))enginemyisam;
Query OK, 0 rows affected (0.00 sec)
mysql CREATE FULLTEXT INDEX FullIdex ON t6(info);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0唯一索引mysql create unique index uniqidx on book1(bookid);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0空间索引mysql drop table t7;
Query OK, 0 rows affected (0.00 sec)
mysql create table t7 ( g geometry not null )enginemyisam;
Query OK, 0 rows affected (0.00 sec)
mysql create spatial index spaidx on t7(g);
Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0删除索引 先查看book表的索引mysql show create table book\G
*************************** 1. row ***************************Table: book
Create Table: CREATE TABLE book (bookid int(11) NOT NULL,bookname varchar(255) COLLATE utf8_bin NOT NULL,authors varchar(255) COLLATE utf8_bin NOT NULL,info varchar(255) COLLATE utf8_bin DEFAULT NULL,comment varchar(255) COLLATE utf8_bin DEFAULT NULL,year_publication year(4) NOT NULL,KEY year_publication (year_publication),KEY bknameidx (bookname(30))
) ENGINEInnoDB DEFAULT CHARSETutf8 COLLATEutf8_bin
1 row in set (0.00 sec) 使用alter命令删除索引后再次查看book表已经删除bknameidx索引。mysql alter table book drop index bknameidx;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql show create table book\G
*************************** 1. row ***************************Table: book
Create Table: CREATE TABLE book (bookid int(11) NOT NULL,bookname varchar(255) COLLATE utf8_bin NOT NULL,authors varchar(255) COLLATE utf8_bin NOT NULL,info varchar(255) COLLATE utf8_bin DEFAULT NULL,comment varchar(255) COLLATE utf8_bin DEFAULT NULL,year_publication year(4) NOT NULL,KEY year_publication (year_publication)
) ENGINEInnoDB DEFAULT CHARSETutf8 COLLATEutf8_bin
1 row in set (0.00 sec)使用drop index删除mysql show create table t7\G
*************************** 1. row ***************************Table: t7
Create Table: CREATE TABLE t7 (g geometry NOT NULL,SPATIAL KEY spatidx (g)
) ENGINEMyISAM DEFAULT CHARSETutf8 COLLATEutf8_bin
1 row in set (0.00 sec)mysql drop index spatidx on t7;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql show create table t7\G
*************************** 1. row ***************************Table: t7
Create Table: CREATE TABLE t7 (g geometry NOT NULL
) ENGINEMyISAM DEFAULT CHARSETutf8 COLLATEutf8_bin
1 row in set (0.00 sec)
