MySQL InnoDB索引创建语句
一般，我们看到术语“索引”和“键”交换使用，但实际上这两个是不同的。索引是存储在数据库中的一个物理结构，键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。索引和键的混淆通常是由于数据库使用索引来实施完整性约束。单列索引和多列索引
单列索引：即一个索引只包含单个列，一个表可以有多个单列索引。
多列索引：即一个索引包含多个列，但是此时列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列（一般用在WHERE字句带AND操作会有用）。
1）普通索引
普通索引是MySQL最基本的索引类型，它没有任何限制。
2）唯一索引
它与前面的”普通索引”类似，不同的就是：索引列的值必须唯一，但允许为NULL。如果是联合唯一索引，则列值的组合必须唯一。
这里要说明一点，唯一键约束和主键约束创建时都会默认隐式创建同名的唯一索引，以键名为索引名。唯一键约束索引列的值必须唯一，但允许为NULL。而主键约束索引不允许有空值，且在一张表中只允许有一个主键约束，但允许存在复合主键索引，也就是两个字符共同组成一个主键索引，两个字段组合必须唯一。一般是在建表的时候同时创建主键约束。
唯一键约束和主键约束与唯一索引的区别是，当主键约束或者唯一键约束失效时，隐式创建的唯一索引会被删除。但你可以先创建唯一索引，后创建主键约束或唯一键约束。这样显式创建的唯一索引不受主键约束或唯一键约束失效的影响。
3）全文索引
FULLTEXT类型索引，可以在CHAR,VARCHAR或者TEXT类型的列上创建，之前仅仅是MYISAM存储引擎支持，从MySQL5.6版本开始支持InnoDB引擎的全文索引，语法层面上大多数兼容之前MyISAM的全文索引模式。全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。
4）空间索引
MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从素有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善，所以大部分人都不会使用这个特性。开源关系数据库系统中对GIS的解决方案做得比较好的就是PostgreSQL的PostGIS。
索引的创建
第一种：普通索引
直接创建索引
1
mysql> create index INDEX_NAME on TABLE_NAME(Column) ;
修改表结构添加索引
1
mysql>> 创建表时创建索引
1
mysql> create table TABLE_NAME( [...],INDEX (Column) ;
可选，如果是CHAR或VARCHAR类型，length可以小于字段实际长度。如果是 BLOB 和 TEXT 类型，必须指定 length。
可选，btree或hash都是用来排序算法，默认是btree。
第二种：唯一索引
直接创建索引
1
mysql> create unique index INDEX_NAME on TABLE_NAME(column(length));
修改表结构添加索引
1
mysql>> 创建表时创建索引
1
mysql> create table TABLE_NAME ( [...], UNIQUE (Column(length));
创建唯一键约束
1
mysql>> 创建主键约束
1
mysql> create table TABLE_NAME(id INT NOT NULL AUTO_INCREMENT, Name VARCHAR(16) NOT NULL,PRIMARY KEY(id));
这里就体现出来了，我们使用唯一索引和唯一键约束都可以创建一条唯一索引。在创建唯一键约束时会自动创建一条唯一键索引。
第三种：全文索引
创建表时指定索引
1
mysql> create table TABLE_NAME([...],FULLTEXT INDEX INDEX_NAEM(info));
第四种：联合索引
直接创建索引（联合唯一索引）
1
mysql> create unique index INDEX_NAME on TABLE_NAME(FIELD1,FIELD2);
修改表结构添加索引（联合唯一索引）
1
mysql>> 创建表时创建索引（普通联合索引）
1
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 (100));
索引删除
1
mysql> drop index INDEX_NAME on TABLE_NAME;
查看索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show create table TABLE_NAME;
mysql> show index from TABLE_NAME;
Table: user（表名）
Non_unique: 0（是否为唯一键）
Key_name: PRIMARY（索引名字）
Seq_in_index: 1（索引中的列顺序号从1开始）
Column_name: account_id（列名）
Collation: A（列怎样在索引中被排序。在MySQL中，这可以有值A（升序) 或NUL）
Cardinality: 21941（索引中唯一值的数量。这可通过运行isamchk -a更改）
Sub_part: NULL（如果列只是部分被索引，索引字符的数量。NULL，如果整个键被索引）
Packed: NULL
Null:
Index_type: BTREE（索引类型）
Comment:
如何检测所创建的索引是否有效（或者说索引使用情况）
1
mysql> show status like 'Handler_read%';
Handler_read_key越高越好。
Handler_read_rnd_next越高越不好。
索引性能测试
1）分别在没有索引和有索引的情况下执行查询。
2）分别在没有索引和有索引的情况下新增数据。
创建没有索引的表
1
2
3
mysql> create database it;
mysql> use it;
mysql> create table indextest(id int,number int,password varchar(50));
插入数据
1
$ for ((i=1;i create table indextest01(id int,number int,password varchar(50));
mysql> insert into indextest01 select * from indextest;
mysql> create index iid on indextest01(id) using btree;
查看表
1
2
3
4
5
mysql> show tables\G
*************************** 1. row ***************************
Tables_in_it: indextest
*************************** 2. row ***************************
Tables_in_it: indextest01
刷新查询缓存，如果没有效果重启MySQL是最有效的
1
mysql> reset query cache;
1
2
3
4
5
6
7
8
9
10
11
mysql> select number,password from indextest where> 300000 rows in set (6.31 sec)
mysql> select number,password from indextest where> 300000 rows in set (0.23 sec)
mysql> select number,password from indextest01 where> 300000 rows in set (0.35 sec)
mysql> select number,password from indextest01 where> 300000 rows in set (0.18 sec)
执行查询计划
1
mysql> explain select number,password from indextest01 where> 查询计划可以用于输出查询的详细信息，可以看出此条SQL语句有没有用到索引。
另外一个没有索引的表在接收数据导入时比有索引的表要快很多。因为当一个表建立索引之后，导入数据时。每导入一行数据随之索引就需要变化，从而导致导入数据慢。
本文出自https://www.ywnds.com/?p=7796
页:
[1]