当前位置：Gxlcms > 数据库问题 > 我的MYSQL学习心得（九） 索引

我的MYSQL学习心得（九） 索引

时间：2021-07-01 10:21:17 帮助过：21人阅读 

  1. CREATE TABLE book (
  2.   bookid INT NOT NULL,
  3.   bookname VARCHAR (255) NOT NULL,
  4.   AUTHORS VARCHAR (255) NOT NULL,
  5.   info VARCHAR (255) NULL,
  6.   COMMENT VARCHAR (255) NULL,
  7.   year_publication YEAR NOT NULL,
  8.   INDEX (year_publication)
  9. ) ;
技术分享

使用SHOW CREATE TABLE查看表结构

技术分享 
  1. CREATE TABLE `book` (
  2.   `bookid` INT(11) NOT NULL,
  3.   `bookname` VARCHAR(255) NOT NULL,
  4.   `authors` VARCHAR(255) NOT NULL,
  5.   `info` VARCHAR(255) DEFAULT NULL,
  6.   `comment` VARCHAR(255) DEFAULT NULL,
  7.   `year_publication` YEAR(4) NOT NULL,
  8.   KEY `year_publication` (`year_publication`)
  9. ) ENGINE=MYISAM DEFAULT CHARSET=latin1
技术分享

可以发现，book表的year_publication字段成功建立了索引其索引名字为year_publication

如果不加索引名，那么MySQL会以索引的第一个字段的名字来命名

  1. CREATE TABLE customer5(id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  2. NAME VARCHAR(20),
  3. dd DATETIME NOT NULL ,
  4. KEY (NAME,dd),
  5. CONSTRAINT idx_pri PRIMARY KEY (id))

技术分享

 而如果一个表下有多个索引的第一个字段都是相同的，那么索引名会在字段名后加序数

技术分享 
  1. CREATE TABLE customer6(id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  2. NAME VARCHAR(20),
  3. dd DATETIME NOT NULL ,
  4. KEY (NAME,dd),
  5. KEY (NAME),
  6. CONSTRAINT idx_pri PRIMARY KEY (id))
技术分享

技术分享

 

 

我们向表插入一条数据，然后使用EXPLAIN语句查看索引是否有在使用

  1. INSERT INTO BOOK VALUES(12,‘NIHAO‘,‘NIHAO‘,‘文学‘,‘henhao‘,1990)
  2. EXPLAIN SELECT * FROM book WHERE year_publication=1990

 

因为语句比较简单，系统判断有可能会用到索引或者全文扫描

技术分享

EXPLAIN语句输出结果的各个行的解释如下：

select_type： 表示查询中每个select子句的类型（简单 OR复杂）

type：表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下：（从上至下，效果依次变好）

possible_keys ：指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

key： 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

ref ：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows ：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra ：包含不适合在其他列中显示但十分重要的额外信息 如using where，using index

 

参考：MySQL学习系列2--MySQL执行计划分析EXPLAIN

唯一索引

唯一索引列的值必须唯一，但允许有空值。如果是复合索引则列值的组合必须唯一

建表

  1. CREATE TABLE t1
  2. (
  3.  id INT NOT NULL,
  4.  NAME CHAR(30) NOT NULL,
  5.  UNIQUE INDEX UniqIdx(id)

SHOW CREATE TABLE t1 查看表结构

  1. SHOW CREATE TABLE t1 
  1.  CREATE TABLE `t1` (                                                                                                                        
  2.           `id` int(11) NOT NULL,                                                                                                                   
  3.           `name` char(30) NOT NULL,                                                                                                                
  4.           UNIQUE KEY `UniqIdx` (`id`)                                                                                                              
  5.         ) ENGINE=MyISAM DEFAULT CHARSET=utf8

可以看到id字段上已经成功建立了一个名为UniqIdx的唯一索引

 

创建复合索引

技术分享 
  1. CREATE TABLE t3 (
  2.   id INT NOT NULL,
  3.   NAME CHAR(30) NOT NULL,
  4.   age INT NOT NULL,
  5.   info VARCHAR (255),
  6.   INDEX MultiIdx (id, NAME, age (100))
  7. )
技术分享 技术分享 
  1. SHOW CREATE TABLE t3
  2. CREATE TABLE `t3` (                                                                                                                                                                                             
  3.           `id` int(11) NOT NULL,                                                                                                                                                                                        
  4.           `NAME` char(30) NOT NULL,                                                                                                                                                                                     
  5.           `age` int(11) NOT NULL,                                                                                                                                                                                       
  6.           `info` varchar(255) DEFAULT NULL,                                                                                                                                                                             
  7.           KEY `MultiIdx` (`id`,`NAME`,`age`)                                                                                                                                                                            
  8.         ) ENGINE=MyISAM DEFAULT CHARSET=utf8
技术分享

由结果可以看到id,name,age字段上已经成功建立了一个名为MultiIdx的复合索引

 

我们向表插入两条数据

  1. INSERT INTO t3(id ,NAME,age,info) VALUES(1,‘小明‘,12,‘nihao‘),(2,‘小芳‘,16,‘nihao‘)

使用EXPLAIN语句查看索引使用情况

  1. EXPLAIN SELECT * FROM t3 WHERE id=1 AND NAME=‘小芳‘

可以看到  possible_keys和 key 为MultiIdx证明使用了复合索引

  1.     id  select_type  table   type    possible_keys  key       key_len  ref            rows  Extra      
  2. ------  -----------  ------  ------  -------------  --------  -------  -----------  ------  -----------
  3.      1  SIMPLE       t3      ref     MultiIdx       MultiIdx  94       const,const       1  Using where

如果我们只指定name而不指定id

  1. EXPLAIN SELECT * FROM t3 WHERE  NAME=‘小芳‘
  1.     id  select_type  table   type    possible_keys  key     key_len  ref       rows  Extra      
  2. ------  -----------  ------  ------  -------------  ------  -------  ------  ------  -----------
  3.      1  SIMPLE       t3      ALL     (NULL)         (NULL)  (NULL)   (NULL)       2  Using where

结果跟SQLSERVER一样，也是不走索引， possible_keys和key都为NULL

 

全文索引

FULLTEXT索引可以用于全文搜索。只有MYISAM存储引擎支持FULLTEXT索引，并且只支持CHAR、VARCHAR和TEXT类型

全文索引不支持过滤索引。

技术分享 
  1. CREATE TABLE t4 (
  2.   id INT NOT NULL,
  3.   NAME CHAR(30) NOT NULL,
  4.   age INT NOT NULL,
  5.   info VARCHAR (255),
  6.   FULLTEXT INDEX FulltxtIdx (info)
  7. ) ENGINE = MYISAM
技术分享

由于MYSQL5.6默认存储引擎为InnoDB，这里创建表的时候要修改表的存储引擎为MYISAM，不然创建索引会出错

  1. SHOW CREATE TABLE t4
技术分享 
  1. Table   Create Table                                                                                                                                                                                                    
  2. ------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  3. t4      CREATE TABLE `t4` (                                                                                                                                                                                             
  4.           `id` int(11) NOT NULL,                                                                                                                                                                                        
  5.           `name` char(30) NOT NULL,                                                                                                                                                                                     
  6.           `age` int(11) NOT NULL,                                                                                                                                                                                       
  7.           `info` varchar(255) DEFAULT NULL,                                                                                                                                                                             
  8.           FULLTEXT KEY `FulltxtIdx` (`info`)                                                                                                                                                                            
  9.         ) ENGINE=MyISAM DEFAULT CHARSET=utf8
技术分享

由结果可以看到，info字段上已经成功建立名为FulltxtIdx的FULLTEXT索引。

全文索引非常适合大型数据集合

 

在SQLSERVER里使用全文索引比MYSQL还要复杂

详细可以参考下面两篇文章：

关于SQLSERVER的全文目录跟全文索引的区别

[SQLSERVER]SQL中的全文检索(转邹建)

空间索引

空间索引必须在 MYISAM类型的表中创建，而且空间类型的字段必须为非空

建表t5

  1. CREATE TABLE t5
  2. (g GEOMETRY NOT NULL ,SPATIAL INDEX spatIdx(g))ENGINE=MYISAM
技术分享 
  1. SHOW CREATE TABLE t5
  2. TABLE   CREATE TABLE                                                                                                   
  3. ------  ---------------------------------------------------------------------------------------------------------------
  4. t5      CREATE TABLE `t5` (                                                                                            
  5.           `g` GEOMETRY NOT NULL,                                                                                       
  6.           SPATIAL KEY `spatIdx` (`g`)                                                                                  
  7.         ) ENGINE=MYISAM DEFAULT CHARSET=utf8
技术分享

可以看到，t5表的g字段上创建了名称为spatIdx的空间索引。注意创建时指定空间类型字段值的非空约束

并且表的存储引擎为MYISAM

已经存在的表上创建索引

在已经存在的表中创建索引，可以使用ALTER TABLE或者CREATE INDEX语句

 

1、使用ALTER TABLE语句创建索引，语法如下

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY]

[index_name](col_name[length],...)[ASC|DESC]

 

与创建表时创建索引的语法不同，在这里使用了ALTER TABLE和ADD关键字，ADD表示向表中添加索引

在t1表中的name字段上建立NameIdx普通索引

  1. ALTER TABLE t1 ADD INDEX NameIdx(NAME)

添加索引之后，使用SHOW INDEX语句查看指定表中创建的索引

技术分享 
  1. SHOW INDEX FROM t1
  2. TABLE   Non_unique  Key_name  Seq_in_index  Column_name  COLLATION  Cardinality  Sub_part  Packed  NULL    Index_type  COMMENT  Index_comment
  3. ------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
  4. t1               0  UniqIdx              1  id           A                    0    (NULL)  (NULL)          BTREE                             
  5. t1               1  NameIdx              1  NAME         A               (NULL)    (NULL)  (NULL)          BTREE
技术分享

各个参数的含义

1、TABLE：要创建索引的表

2、Non_unique：索引非唯一，1代表是非唯一索引，0代表唯一索引

3、Key_name：索引的名称

4、Seq_in_index：该字段在索引中的位置，单列索引该值为1，复合索引为每个字段在索引定义中的顺序

5、Column_name：定义索引的列字段

6、Sub_part：索引的长度

7、NULL：该字段是否能为空值

8、Index_type：索引类型

 

可以看到，t1表已经存在了一个唯一索引

 

在t3表的age和info字段上创建复合索引

  1. ALTER TABLE t3 ADD INDEX t3AgeAndInfo(age,info)

使用SHOW INDEX查看表中的索引

  1. SHOW INDEX FROM t3
技术分享 
  1. Table   Non_unique  Key_name      Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment
  2. ------  ----------  ------------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
  3. t3               1  MultiIdx                 1  id           A               (NULL)    (NULL)  (NULL)          BTREE                             
  4. t3               1  MultiIdx                 2  NAME         A               (NULL)    (NULL)  (NULL)          BTREE                             
  5. t3               1  MultiIdx                 3  age          A               (NULL)    (NULL)  (NULL)          BTREE                             
  6. t3               1  t3AgeAndInfo             1  age          A               (NULL)    (NULL)  (NULL)          BTREE                             
  7. t3               1  t3AgeAndInfo             2  info         A               (NULL)    (NULL)  (NULL)  YES     BTREE
技术分享

可以看到表中的字段的顺序，第一个位置是age，第二个位置是info，info字段是可空字段

技术分享

 技术分享

 

创建表t6，在t6表上创建全文索引

  1. CREATE TABLE t6
  2. (
  3.   id INT NOT NULL,
  4.   info CHAR(255)
  5. )ENGINE= MYISAM;

注意修改ENGINE参数为MYISAM，MYSQL默认引擎InnoDB不支持全文索引

使用ALTER TABLE语句在info字段上创建全文索引

  1. ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx(info)

使用SHOW INDEX查看索引情况

  1. SHOW INDEX FROM t6
  1. Table   Non_unique  Key_name   Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment
  2. ------  ----------  ---------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
  3. t6               1  infoFTIdx             1  info         (NULL)          (NULL)    (NULL)  (NULL)  YES     FULLTEXT

 

创建表t7，并在空间数据类型字段g上创建名称为spatIdx的空间索引

  1. CREATE TABLE t7(g GEOMETRY NOT NULL)ENGINE=MYISAM;

使用ALTER TABLE在表t7的g字段建立空间索引

  1. ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g)

使用SHOW INDEX查看索引情况

  1. SHOW INDEX FROM t7
  1. Table   Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment
  2. ------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
  3. t7               1  spatIdx              1  g            A               (NULL)        32  (NULL)          SPATIAL

 

 

2、使用CREATE INDEX语句创建索引，语法如下

CREATE [UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name

ON table_name(col_name[length],...)  [ASC|DESC]

 

可以看到CREATE INDEX语句和ALTER INDEX语句的基本语法一样，只是关键字不同。

我们建立一个book表

技术分享 
  1. CREATE TABLE book (
  2.   bookid INT NOT NULL,
  3.   bookname VARCHAR (255) NOT NULL,
  4.   AUTHORS VARCHAR (255) NOT NULL,
  5.   info VARCHAR (255) NULL,
  6.   COMMENT VARCHAR (255) NULL,
  7.   year_publication YEAR NOT NULL
  8. )
技术分享

 

建立普通索引

  1. CREATE INDEX BkNameIdx ON book(bookname)

 

建立唯一索引

  1. CREATE UNIQUE INDEX UniqidIdx ON book(bookId)

 

建立复合索引

  1. CREATE INDEX BkAuAndInfoIdx ON book(AUTHORS(20),info(50))

 

建立全文索引，我们drop掉t6表，重新建立t6表

技术分享 
  1. DROP TABLE IF EXISTS t6
  2. CREATE TABLE t6
  3. (
  4.   id INT NOT NULL,
  5.   info CHAR(255)
  6. )ENGINE= MYISAM;
  7. CREATE FULLTEXT INDEX infoFTIdx ON t6(info);
技术分享

 

建立空间索引，我们drop掉t7表，重新建立t7表

  1. DROP TABLE IF EXISTS t7
  2. CREATE TABLE t7(g GEOMETRY NOT NULL)ENGINE=MYISAM;
  3. CREATE SPATIAL INDEX spatIdx  ON t7(g)

删除索引

MYSQL中使用ALTER TABLE或者DROP INDEX语句来删除索引，两者实现相同功能

1、使用ALTER TABLE删除索引

 语法

ALTER TABLE table_name DROP INDEX index_name

  1. ALTER TABLE book DROP INDEX UniqidIdx
  1. SHOW CREATE TABLE book
技术分享 
  1. Table   Create Table                                                                                                                                                                                                                                                                                                                                                      
  2. ------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  3. book    CREATE TABLE `book` (                                                                                                                                                                                                                                                                                                                                             
  4.           `bookid` int(11) NOT NULL,                                                                                                                                                                                                                                                                                                                                      
  5.           `bookname` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                                                                               
  6.           `authors` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                                                                                
  7.           `info` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                               
  8.           `comment` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                            
  9.           `year_publication` year(4) NOT NULL,                                                                                                                                                                                                                                                                                                                            
  10.           KEY `BkNameIdx` (`bookname`),                                                                                                                                                                                                                                                                                                                                   
  11.           KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))                                                                                                                                                                                                                                                                                                                 
  12.         ) ENGINE=MyISAM DEFAULT CHARSET=utf8
技术分享

可以看到，book表中已经没有名为UniqidIdx的唯一索引，删除索引成功

 

注意：AUTO_INCREMENT约束字段的唯一索引不能被删除！！

 

2、使用DROP INDEX 语句删除索引

DROP INDEX index_name ON table_name

  1. DROP INDEX BkAuAndInfoIdx ON book
技术分享 
  1. SHOW CREATE TABLE book;
  2. Table   Create Table                                                                                                                                                                                                                                                                                                   
  3. ------  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  4. book    CREATE TABLE `book` (                                                                                                                                                                                                                                                                                          
  5.           `bookid` int(11) NOT NULL,                                                                                                                                                                                                                                                                                   
  6.           `bookname` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                            
  7.           `authors` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                             
  8.           `info` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                            
  9.           `comment` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                         
  10.           `year_publication` year(4) NOT NULL,                                                                                                                                                                                                                                                                         
  11.           KEY `BkNameIdx` (`bookname`)                                                                                                                                                                                                                                                                                 
  12.         ) ENGINE=MyISAM DEFAULT CHARSET=utf8
技术分享

可以看到，复合索引BkAuAndInfoIdx已经被删除了

 

提示：删除表中的某列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。

如果索引中的所有列都被删除，则整个索引将被删除！！

总结

这一节介绍了MYSQL中的索引，索引语句的创建和删除和一些简单用法，希望对大家有帮助

我的MYSQL学习心得（九） 索引

标签：分组   数据库表   相关   constrain   参考   too   删除   命令   eid   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！