MySQL的索引学习

索引

数据库的索引就像一本书的目录，能够加快数据库的查询速度。索引是一种用来提高查询效率的机制，索引是一种用来在数据库中加速表查询的数据库对象，通过索引值和索引的数据结构快速定位数据，可有效较少磁盘 I/O 操作，提高访问性能。

MySQL 索引有四种 PRIMARY、INDEX、UNIQUE、FULLTEXT， 其中PRIMARY、INDEX、UNIQUE 是一类，FULLTEXT 是一类。

这四种都是单列索引，也就是他们都是作用于单个列，所以称单列索引；但是一个索引也可以作用于多个列上，称为组合索引或联合索引或复合索引。

注意：创建索引的字段的值可以重复，也可以为空，但是不建议为空。

自动创建索引

如果数据表有 PK/Unique 两种约束，相关的字段会自动创建索引，除此以外，索引必须手动创建。

手动创建索引

创建表的时候定义联合索引：

mysql> CREATE TABLE `user` ( 
  `id` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `age` tinyint(1) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL, 
  KEY `idx_name_age` (`name`,`age`)  USE BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

或

CREATE TABLE `user` ( 
  `id` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `age` tinyint(1) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL, 
  INDEX `idx_name_age` (`name`,`age`)  USE BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

创建表的时候定义单列索引：

CREATE TABLE `user` (
  `id` bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `NAME` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `NAME` (`NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |

给表添加索引：

ALTER TABLE table_name ADD INDEX index_name (column_list); -- 添加普通索引，INDEX 可以改成 KEY
ALTER TABLE table_name ADD UNIQUE index_name (column_list); -- UNIQUE KEY 或者 UNIQUE INDEX 或者 UNIQUE 都表示唯一索引
ALTER TABLE table_name ADD PRIMARY KEY index_name (column_list); -- 添加主键索引
ALTER TABLE table_name ADD KEY index_name (column_list); -- 添加普通索引

创建索引：

CREATE INDEX index_name ON table_name(column_list); -- 创建普通索引
CREATE KEY index_name ON table_name(column_list); -- 创建普通索引
CREATE UNIQUE INDEX index_name ON table_name(column_list); -- 创建唯一索引
CREATE UNIQUE KEY index_name ON table_name(column_list); -- 创建唯一索引
CREATE UNIQUE index_name ON table_name(column_list); -- 创建唯一索引

注意：索引名称和字段名称千万不要加单引号或者双引号，要么添加反引号，要么都不添加。

删除索引

alter table commodity drop index idx_spec_value;

注：删除不存在的索引会报错。

使用索引

select * from student where id = 1001; -- 字段id是主键索引，所以使用id字段查询，会使用id索引
select * from student where name = 'zhangsan'; -- 字段name没有创建索引，所以使用name查询数据，不会用到索引，而是全表扫描数据，效率低

单列索引

新建一张测试表

CREATE TABLE t_user
  (
     id       INT NOT NULL,
     username VARCHAR(16) NOT NULL
  ); 

PRIMARY

主键索引。索引列唯一且不能为空；一张表只能有一个主键索引（主键索引通常在建表的时候就指定）

CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,PRIMARY KEY(ID))

NORMAL

普通索引。索引列没有任何限制。

建表时指定：

CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,INDEX USERNAME_INDEX(USERNAME(16))) # 给列USERNAME建普通索引USERNAME_INDEX

ALTER语句指定：

ALTER TABLE T_USER ADD INDEX U_INDEX (USERNAME) # 给列USERNAME建普通索引 U_INDEX

UNIQUE

唯一索引。索引列的值必须是唯一的，但允许有空。

建表时指定

CREATE TABLE t_user(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,UNIQUE U_INDEX(USERNAME)) # 给列USERNAME添加唯一索引T_USER

ALTER语句指定

ALTER TABLE t_user ADD UNIQUE u_index(USERNAME) # 给列T_USER添加唯一索引u_index

FULLTEXT

全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。索引的新建和删除和上面一致，这里不再列举…

组合索引（复合索引/联合索引）

新建一张表：

CREATE TABLE T_USER(
  ID INT NOT NULL,
  USERNAME VARCHAR(16) NOT NULL,
  CITY VARCHAR(10),
  PHONE VARCHAR(10),
  PRIMARY KEY(ID)
);

组合索引就是把多个列加入到一个索引中，如新建的表T_USER，我们给USERNAME+CITY+PHONE创建一个组合索引

ALTER TABLE t_user ADD INDEX name_city_phone(USERNAME,CITY,PHONE);  # 组合普通索引
ALTER TABLE t_user ADD UNIQUE name_city_phone(USERNAME,CITY,PHONE); # 组合唯一索引

这样的组合索引，其实相当于分别建立了（USERANME,CITY,PHONE USERNAME,CITY USERNAME,PHONE）三个索引。

为什么没有（CITY,PHONE）索引呢？这是因为MySQL 组合查询“最左前缀”的结果。简单的理解就是只从最左边开始组合。

并不是查询语句包含这三列就会用到该组合索引： 　　 这样的查询语句才会用到创建的组合索引

SELECT * FROM t_user where USERNAME="parry" and CITY="广州" and PHONE="180";
SELECT * FROM t_user where USERNAME="parry" and CITY="广州";
SELECT * FROM t_user where USERNAME="parry" and PHONE="180"; 

这样的查询语句是不会用到创建的组合索引

SELECT * FROM t_user where CITY="广州" and PHONE="180";
SELECT * FROM t_user where CITY="广州";
SELECT * FROM t_user where PHONE="180";

索引不足之处

（1）索引提高了查询的速度，但是降低了INSERT、UPDATE、DELETE的速度，因为在插入、修改、删除数据时，还要同时操作一下索引文件；

（2）建立索引文件会占用一定的磁盘空间。

索引使用注意事项

（1）只要列中包含NULL值将不会被包含在索引中，组合索引只要有一列含有NULL值，那么这一列对于组合索引就是无效的，所以我们在设计数据库的时候最好不要让字段的默认值为NULL

（2）使用短索引 如果可能应该给索引指定一个长度，例如：一个VARCHAR(255)的列，但真实储存的数据只有20位的话，在创建索引时应指定索引的长度为20，而不是默认不写。如下：

ALTER TABLE t_user add INDEX U_INDEX(USERNAME(16)) 优于 ALTER TABLE t_user add INDEX U_INDEX(USERNAME);

使用短索引不仅能够提高查询速度，而且能节省磁盘操作以及I/O操作。

（3）索引列排序 MySQL 在查询的时候只会使用一个索引，因此如果 where 子句已经使用了索引的话，那么order by中的列是不会使用索引的，所以order by尽量不要包含多个列的排序，如果非要多列排序，最好使用组合索引。

（4）Like 语句 一般情况下不是鼓励使用 like,如果非使用，那么需要注意 like '%aaa%' 不会使用索引；但 like 'aaa%' 会使用索引。

（5）不使用 NOT IN 和 操作

索引方式 Hash 和 BTree 比较

1.Hash

仅用于对等比较，如 “=” 和 " "，Hash 不能用于范围查询。

2.BTree BTree 索引看名字就知道索引以树形结构存储，通常用在像“=，>，>=，