MySQL InnoDB索引创建语句

hongblue

　　一般，我们看到术语“索引”和“键”交换使用，但实际上这两个是不同的。索引是存储在数据库中的一个物理结构，键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。索引和键的混淆通常是由于数据库使用索引来实施完整性约束。
　　单列索引和多列索引
　　单列索引：即一个索引只包含单个列，一个表可以有多个单列索引。
　　多列索引：即一个索引包含多个列，但是此时列的顺序也十分重要，因为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[length]) [using btree|hash];
　　修改表结构添加索引
　　1

　　mysql>>　　创建表时创建索引
　　1
　　mysql> create table TABLE_NAME( [...],INDEX [INDEX_NAME] (Column[length]) [using btree|hash];
　　[length]可选，如果是CHAR或VARCHAR类型，length可以小于字段实际长度。如果是 BLOB 和 TEXT 类型，必须指定 length。
　　[using btree|hash]可选，btree或hash都是用来排序算法，默认是btree。
　　第二种：唯一索引
　　直接创建索引
　　1
　　mysql> create unique index INDEX_NAME on TABLE_NAME(column(length));
　　修改表结构添加索引
　　1

　　mysql>>　　创建表时创建索引
　　1
　　mysql> create table TABLE_NAME ( [...], UNIQUE [INDEX_NAME] (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