mysql索引

1.索引的优点和缺点
a.优点
·所有的字段类型都可以添加索引。
·可以为数据表中的一列或多列添加索引。
·能够极大地提高数据的查询性能。
·能够提高数据分组与排序的性能。
b.缺点
·索引本身需要占用一定的存储空间，如果大量地使用索引，则索引文件会占用大量的磁盘空间。
·索引的创建与维护需要耗费一定的时间，随着数据量的不断增长，耗费的时间会越来越长。
·对数据表中的数据进行增加、删除和修改操作时，MySQL 内部需要对索引进行动态维护，这也会消耗一定的维护时间。

2.索引的创建原则
a.尽量使用小的数据类型的列创建索引
b.尽量使用简单的数据类型的列创建索引
c.尽量不要在null值字段上创建索引

3.语法格式

CREATE TABLE table_name
column_namel data_typel [,column_name2 data_type2,...,column_namen data_typen]
[PRIMARY |UNIQUE |FULLTEXT | SPATIAL][INDEX|KEY]
[index name](column_name[length])
[ASC | DESC]

语法格式说明如下：
·CREATE TABLE:创建数据表语句。
·table_name:数据表名称。
·column_namel data_typel [,column_name2 data_type2,…,column_namen data_typen]:
创建数据表时定义的字段列表。
·[PRIMARY |UNIQUE |FULLTEXT |SPATIAL]:索引的类型，分别表示唯一索引、全文索引和空间索引，创建数据表时，索引类型可以省略。
·[INDEX|KEY]:作用基本相同，指定在数据表中创建索引。
·[index_name]:建的索引名称，名称可以省略。
·column_name:需要创建的索引列，可以是数据表中的单个列，也可以是数据表中的多个列。
·length:创建索引时，为索引指定的长度，参数可以省略。需要注意的是，只有字符串类型的字段才能为索引指定长度。
·[ASC|DESC]:指定以升序或者降序的方式来存储索引值，参数可省略。

4.创建索引实例
普通索引是所有索引类型中最基本的索引类型，没有唯一性等限制，能够加快数据的检效率。
例如，创建名称为t1的数据表，t1数据表中包含id、t_name、t_birthday、t_department_id
和t_create_time等字段，为t_department_id创建普通索引。

CREATE TABLE t1(
->id INT NOT NULL,
->t_name VARCHAR(30) NOT NULL DEFAULT '',
->t birthday DATE,
->t department_id INT NOT NULL DEFAULT 0,
->t create_time DATETIME,
->INDEX department id index (t_department_id)
->);

5.查看数据表的创建信息

show create table t1 \G;

6.创建唯一值索引
创建唯一索引的列值必须唯一，但是允许值为空。如果创建的唯一索引中包含多个字段，
也就是复合索引，则索引中包含的多个字段的值的组合必须唯一。
例如，创建名称为t3的数据表，并为其中的t_id_card字段创建唯一索引。

CREATE TABLE t3 (
->id INT NOT NULL,
->t_name VARCHAR(30)NOT NULL DEFAULT '',
->t_id_card VARCHAR(20),
->UNIQUE INDEX id_card_index(t_id_card)
->);

7.创建主键索引
主键索引是特殊类型的唯一索引，与唯一索引不同的是，主键索引不仅具有唯一性，而且不能为空，而唯一索引中的列的数据可能为空。
例如，创建数据表t4,并为t4数据表中的id字段创建主键索引。

CREATE TABLE t4(
->id INT NOT NULL PRIMARY KEY,
->t_name VARCHAR(30) NOT NULL DEFAULT 1
->);

也可以使用如下方式创建t4数据表。

CREATE TABLE t4 (
->id INT NOT NULL,
->t name VARCHAR(30)NOT NULL DEFAULT ,
->PRIMARY KEY(id)
->；

8.单列索引
单列索引表示在创建的索引中，只包含数据表中的单个字段或列。MySQL 中，支持在一张数据表中创建多个单列索引。
例如，创建名称为t5的数据表，并为id字段创建单列索引。

CREATE TABLE t5(
->id INT NOT NULL,
->t_name VARCHAR(30) NOT NULL DEFAULT '',
->INDEX id index(id)
->；

9.创建组合索引
组合索引表示在创建的索引中,包含数据表中的多个字段或列。 MySQL中,同样支持在一张数据表中创建多个组合索引。在使用组合索引查询数据时, MySQL支持最左匹配原则。
例如,创建数据表t6,并为数据表中的t_no、 t_name和 t_department_id字段创建复合
索引。

CREATE TABLE t6(
->id INT NOT NULL
->t_no VARCHAR(32) NOT NULL DEFAULT,
->t_name VARCHAR( 30) NOT NULL DEFAULT
->t_department_id INT NOT NULL DEFAULT 0
->INDEX no_name_department_index(t_no, t_ name, t_ department_id)
->);

名称为 no_name_department_index的复合索引在进行存储时,是按照 t_no/t_namel_department_id的顺序进行存放的。根据索引的最左匹配原则,当在查询数据时,使用(t_no)，(t_no, t_name)和(t_no, t_name, t _department_id)中的一种进行查询时, MySQL会使用索引。当使用（t_name）、( t_department_id)和 t name, t_department_id)查询数据时, MySQL不会使用索引。

10.查看表结构信息

show create table t6 \G;

11.使用expalin查看t6数据表中的索引的使用情况

explain select * from t6 where id='100' \G

12.创建全文索引
建全文索引时,对列的数据类型有一定的限制,只能为定义为CHAR、 VARCHAR和数据类型的列创建全文索引,全文索引不支持对列的局部进行索引。
例如,创建数据表t7,并将字段 t info设置为全文索引。

CREATE TABLE t7
->id INT NOT NULLI
->t_name VARCHAR (30) NOT NULL DEEAULT
->t_info VARCHAR(200)
->FULLTEXT INDEX info index (t_info)
->);

注意：mysql 5.7之前的版本中，只有myisam存储的数据类型支持全文索引。在部分mysql 5.7的版本中和mysql8.0版本中，InnoDB存储引擎也支持创建全文索引
13.为已有的数据添加索引
(1) ALTER TABLE语句的语法格式如下:

ALTER  TABLE table_name
ADD [PRIMARY I UNIQUE I FULLTEXT I SPATIALl] [INDEX I KEY]
[index_namel (column_name [length])
(ASC | DESC)

(2) CREATE INDEX语句的语法格式如下

CREATE [UNIQUE I FULLTEXT SPATIAL]
INDEX index_name
ON table_name
(column_name [length])
(ASCI DESC]

(3) 使用 CREATE TABLE语句创建普通索引
例如,为 tb_alter数据表的 t_category_id字段创建普通索引。
ALTER TABLE tb_alter ADD INDEX category_id_index (t_category_id)