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在前面,而 后面的字段无论什么顺序不影响索引的生效。