Mysql索引创建和使用

索引

什么是索引

菜鸟教程索引介绍
索引其实也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
索引类似于字典的目录（索引表），通过目录查询再定位所需数据在哪一页

为什么需要索引：
索引可以帮助我们提高数据查询效率。
索引怎么帮助我们提高速度的：
试想一下
假如有这样的数据，寻找id为5的。怎么寻找？

id	name
1	叶华
2	叶明
3	叶思
4	叶万
5	叶炟
6	叶烨
7	叶华

按照数据一次循环遍历 找到id=5的。但是假如数据有2亿，那查一次就需要2亿次遍历。
索引的目的就是减少查询次数，并且准确返回目的数据。最终体现的索引是数据结构。

索引的类型

普通索引(NORMAL)

仅加速查询,无限制
关键字: INDEX

唯一索引(UNIQUE)

加速查询 + 列值唯一(可以有null),如果是组合索引，则列值的组合必须唯一
关键字：UNIQUE

主键索引 (PRIMARY KEY)

加速查询 + 列值唯一(不可以有null)+ 表中只允许有一个主键索引, 是一种特殊的唯一索引
关键字：PRIMARY KEY

组合索引

多列值组成一个索引，专门用于组合搜索，其效率大于索引合并(索引合并：使用多个单列索引组合搜索)
只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
没有关键字，多个字段使用普通、唯一、主键索引就是组合索引

全文索引

关键字：FULLTEXT
todo

索引创建

从增删改的角度介绍

新增

1.在建表时创建索引：

• UNIQUE 唯一索引：如果有唯一数据可以用它 比如 身份证号码，如果存入数据库的数据已经存在会报错
• INDEX 一般索引：需要提高效率，可以建它 如某个字段是时间 作为where的查询条件，随意存储
• PRIMARY KEY 主键索引：一般必须有主键不能为null ，如果存入数据库的数据已经存在会报错，

例子：

唯一索引
CREATE TABLE test1 ( 
ID INT NOT NULL,
username VARCHAR ( 16 ) NOT NULL,

-- UNIQUE 唯一索引：如果有唯一数据可以用它 比如 身份证号码，如果存入数据库的数据已经存在会报错
-- INDEX  一般索引：需要提高效率，可以建它 如某个字段是时间 作为where的查询条件，随意存储
-- PRIMARY KEY 主键索引：一般必须有主键不能为null ，如果存入数据库的数据已经存在会报错，
-- 一个字段索引 以下依次为：索引类型 索引名称 索引字段（索引的长度） 
UNIQUE index_id ( username ( 8 ))
);

注意事项
如果是CHAR，VARCHAR类型，length（指的括号里的数字）可以小于字段实际长度；
如果是BLOB和TEXT类型，必须指定 length

组合唯一索引
CREATE TABLE test1 ( 
ID INT NOT NULL,
username VARCHAR ( 16 ) NOT NULL,
-- 组合唯一索引
UNIQUE index_id ( username ( 8 ),id)
);

组合一般索引
CREATE TABLE test2 ( 
ID INT NOT NULL,
username VARCHAR ( 16 ) NOT NULL,
-- 组合一般索引
INDEX index_id ( username ( 8 ),id)
);

组合主键索引
CREATE TABLE test3 ( 
ID INT NOT NULL,
username VARCHAR ( 16 ) NOT NULL,
-- 组合主键索引
-- 主键索引有且仅有一个 其中所有字段数据不全为null。例子：id为null则username不能为null
PRIMARY KEY index_id ( username ( 8 ),id)
);

2.建好表后添加索引：

CREATE INDEX “索引名称” ON ”表名“(”字段名称1“);创建单个索引
CREATE INDEX “索引名称” ON ”表名“(”字段名称1“,“字段名称2”,…);
ps:创建组合索引，不是给其中的字段每个创建一个索引

CREATE UNIQUE “索引名称” ON ”表名“(”字段名称1“);创建单个索引
CREATE UNIQUE “索引名称” ON ”表名“(”字段名称1“,“字段名称2”,…);

CREATE PRIMARY KEY “索引名称” ON ”表名“(”字段名称1“);创建单个索引
CREATE PRIMARY KEY “索引名称” ON ”表名“(”字段名称1“,“字段名称2”,…);

3.添加字段时添加索引：

测试只能新建unique 的索引 并且索引 名称默认为字段名称

alter table test3 add image varchar(20) UNIQUE;

使用Alter 命令创建索引

在创建完表之后建立索引
格式如：
ALTER TABLE 表名称 ADD 索引关键字 索引名字 (字段1（长度）,字段2,…):
注意主键索引：PRIMARY KEY 没有名字。一张表最多有一个主键，可以没有。
例子：

CREATE TABLE test5 (
 id int not NULL COMMENT'主键id',
 username VARCHAR(64) null COMMENT '用户名称',
 image VARCHAR(64) DEFAULT '冰冰.img' COMMENT '图片'
);

ALTER TABLE test5 ADD PRIMARY KEY (image);
ALTER TABLE test5 ADD UNIQUE UNIQUE_id_name_image (id,username,image);
ALTER TABLE test5 ADD INDEX INDEX_image (image);
ALTER TABLE test5 ADD FULLTEXT FULLTEXT_image (image);
-- 返回结果
ALTER TABLE test5 ADD PRIMARY KEY (image)
> OK
> 时间: 0.012s


ALTER TABLE test5 ADD UNIQUE UNIQUE_id_name_image (id,username,image)
> OK
> 时间: 0.015s


ALTER TABLE test5 ADD INDEX INDEX_image (image)
> OK
> 时间: 0.014s


ALTER TABLE test5 ADD FULLTEXT FULLTEXT_image (image)
> 1214 - The used table type doesn't support FULLTEXT indexes
> 时间: 0s；这里说我的数据库不支持全文索引。需要mysql 5.6.4以上才能支持。

删除

方式一：

DROP INDEX 索引名称 ON 表名字;

例子：
注意 INDEX 不能改 ，这里表示的是所有索引。看下面例子

DROP UNIQUE UNIQUE_id_name_image ON test5;
> 1064 - You have an error in your SQL syntax; 
> check the manual that corresponds to your MySQL server version for the right syntax 
> to use near 'UNIQUE UNIQUE_id_name_image ON test5' at line 1
> 时间: 0s

-- 删除唯一索引 使用 "index 索引名字"
 DROP INDEX UNIQUE_id_name_image ON test5;
> OK
> 时间: 0.011s

方式二：
格式：

ALTER TABLE 表名称DROP INDEX 索引名称;

例子：

> 注意如果是删除主键 直接使用 主键关键字；
ALTER TABLE test5 DROP PRIMARY KEY
> OK
> 时间: 0.012s
>
ALTER TABLE test5 ADD UNIQUE UNIQUE_id_name_image (id,username,image)
> OK
> 时间: 0.015s

ALTER TABLE test5 DROP INDEX UNIQUE_id_name_image
> OK
> 时间: 0.012s
>
> 但是使用方式一 来删除会报错
DROP PRIMARY KEY  ON test5
> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY KEY  ON test5' at line 1
> 时间: 0s

修改

使用先删除再新增的方式达到修改的目的;

查询索引

格式：
SHOW INDEX FROM 表名;
例子：

 SHOW INDEX FROM test5;

看第三列 key_name：索引名称
PRIMARY: 表示主键名称 ，它是数据库自己定义的默认名称。
UNIQUE_id_name_image：一共有三行，表示说有三个这样的索引，
INDEX_image：自己建的索引，名称叫这个。

看第五列 column_name:列名称
column_name:表示索引建立在哪个字段（列）上。

看第四列：seq_in_index:索引当中的顺序
PRIMARY 只有一个，对应1
UNIQUE_id_name_image 有三个，id对应1，username：对应2，image对应3，而在创建索引时的顺序正好是 id第一个，username第二个，image第三个

看index_type 列
表示使用的索引的类型：这里全是BTREE

索引使用

索引的使用体现在使用select 做查询时，并在where条件中使用。
如何查看我们建好的索引有没有生效，
使用mysql的解释计划

EXPLAIN 你的查询语句

例子：
执行完自己sql
EXPLAIN select …

看第四列
type：这里有ref 和eq_ref
ref表示根据索引查找一个或多个值
eq_ref表示搜索时使用primary key 或 unique类型
type共有以下几种类型：从最差到最好排序
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
个人认为用到最后三级就比较好
详细了解的请参考：
解释执行计划参考

索引失效问题

问题：既然建立好了索引，在where 条件里面放进去了。为什么还会有失效呢？
参考链接查看第四点：
参考链接

失效情况和解决办法
1.有null的单列索引字段。建议使用特殊字符代替
2.使用 or 作为查询条件 除非全部字段建立索引。建议使用union
3.使用like 进行前置匹配的查询。建议使用 全文索引
4.组合索引 没有使用最左原则的查询。建议开头条件 使用最左字段
5.没必要建索引的字段 ，mysql会认为全表扫描比走索引快。例如 只有0或者1

什么叫 like 进行前置匹配

例子：

selet * from test5 where username like "%李";
selet * from test5 where username like "%李%";
selet * from test5 where username like "_李";

即：%在前 ，_在前

-- 则会中不会是索引失效
selet * from test5 where username like "李%";

什么叫 组合索引的最左原则

例子
测试数据：

-- 参考 ALTER TABLE test5 ADD UNIQUE UNIQUE_id_name_image (id,username,image);
-- 这里给test5建立了 组合索引UNIQUE_id_name_image 由 id、name、image组合
select * from test5 where username = '老王' and image='冰冰.img' and id = 1010101;
select * from test5 where image='冰冰.img' and username = '老王' ;
select * from test5 where image='冰冰.img';
这里的例子都不会使用组合索引 因为where 条件里 id 、name 、image 的顺序 都不是id在前

能生效的例子

EXPLAIN select * from test5 where  id = 1010101 AND username = '老李' 
and image='冰冰.img';
EXPLAIN select * from test5 where  id = 1010101 AND username = '老李' ;
EXPLAIN select * from test5 where  id = 1010101 AND image='冰冰.img';

就是 说 id ，username，image 的相对顺序 一定是 id在前面，而 后面的字段无论什么顺序不影响索引的生效。