MySQL索引
数据库索引
- 是一个排序的列表,存储着索引值和这个值所对应的物理地址
- 无须对整个表进行扫描,通过物理地址就可以找到所需数据
- 是表中一列或者若干列值排序的方法
- 需要额外的磁盘空间
索引的作用
- 数据库利用各种快速定位技术,能够大大加快查询速率
- 当表很大或查询涉及到多个表时,可以成干上万倍地提高查询速度
- 可以降低数据库的IO成本,并且还可以降低数据库的排序成本
- 通过创建唯一性索引保证数据表数据的唯一性
- 可以加快表与表之间的连接
- 在使用分组和排序时,可大大减少分组和排序时间
索引的副作用
- 索引需要占用额外的磁盘空间。
对于MyISAM引擎而言,索引文件和数据文件是分离的,索引文件用于保存数据记录的地址。而InnoDB引擎的表数据文件本身就是索引文件。 - 在插入和修改数据时要花费更多的时间,因为索引也要随之变动。
创建索引的原则依据
索引虽可以提升数据库查询的速度,但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源,在有索引的情况下,数据库会先进行索引查询,然后定位到具体的数据行,如果索引使用不当,反而会增加数据库的负担。
- 表的主键、外键必须有索引。因为主键具有唯一性,外键关联的是子表的主键,查询时可以快速定位
- 记录数超过300行的表应该有索引。如果没有索引,需要把表遍历一遍,会严重影响数据库的性能。
- 经常与其他表进行连接的表,在连接字段上应该建立索引。
- 唯一性太差的字段不适合建立索引。
- 更新太频繁地字段不适合创建索引。
- 经常出现在where子句中的字段,特别是大表的字段,应该建立索引。
- 索引应该建在选择性高的字段上。
- 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引。
小结
MySQL的优化哪些字段/场景适合创建索引
1、小字段
2、唯一性强的字段
3、更新不频繁,但查询率很高的字段
4、表记录超过300+行
5、主键、外键、唯一键
索引的分类
普通索引
最基本的索引类型,没有唯一性之类的限制。
模板:
mysql -u root -p
create database zy;
use zy;
create table ky20(id int(10) not null,name char(40),cardid varchar(18),phone varchar(8),address varchar(50),remark text);
desc desc zy.ky20;
insert into ky20 values(1,'qjm',123,1234,'gw','this is 1');
insert into ky20 values(2,'lgx',432,1142,'mc','this is 2');
insert into ky20 values(3,'cl',124,213,'ts','this is 3');
insert into ky20 values(4,'zy',2154,42,'sl','this is 4');
select * from ky20;
直接创建索引
CREATE INDEX 索引名 ON 表名(列名[(length)]);
(列名(length)):length是可选项。
如果忽略length的值,则使用整个列的值作为索引。
如果指定使用列前的length个字符来创建索引,这样有利于减小索引文件的大小。
索引名建议以"index"结尾。
示例:create index name_index on ky20 (name);
show create table ky20;
修改表方式创建
ALTER TABLE 表名 ADD INDEX 索引名(列名);
alter table ky20 add index cardid (cardid);
select id from ky20;
select id,name from ky20;
show create table ky20;
创建表的时候指定索引
CREATE TABLE表名(字段1数据类型,字段2数据类型[,···],INDEX索引名(列名));
create table ky20 (id int(10),index id_index (id));
show create table ky20;
唯一索引
与普通索引类似,但区别是唯一索引列的每个值都唯一。
唯一索引允许有空值(注意和主键不同)。如果是用组合索引创建,则列值的组合必须唯一。添加唯一键将自动创建唯一索引。
直接创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);
create unique index address_index on ky20 (address);
show create table ky20;
修改表方式创建
ALTER TABLE 表名 ADD UNIQUE 索引名(列名)
alter table ky20 add unique id_index (id);
创建表的时候指定
CREATE TABLE表名(字段1数据类型,字段2数据类型[,··],UNIQUE索引名(列名)):
create table ww (id int(10),name varchar(20),unique id_index (id));
show creat table ww;
主键索引
主键索引是一种特殊的唯一索引,必须指定为“primary key”。
一个表只能有一个主键,不允许有空值,添加主键将自动创建主键索引
创建表的时候指定
create table 表名 (字段1 数据类型,...字段n 数据类型,primary key (字段名));
create table ab (id int,name char(10),primary key(id));
desc ab;
show create table ab;
修改表的方式创建主键索引
alter table 表名 add primary key (列名);
alter table test drop primary key; #先删除主键
alter table test add primary key(name); 然后再增加主键
注:在增加主键之前,必须先把反复的d删除掉。
组合索引(单列索引与多列索引)
可以是单列上创建的索引,也可以是在多列上创建的索引。需要满足最左原则,因为select语句的where条件是依次从左往右执行的,所以在使用select语句查询时where条件使用的字段顺序必须和组合索引中的排序一致,否则索引将不会生效。
创建表时指定索引组
create table 表名 (列名1 数据类型,...列名n 数据类型,index 索引名 (列名1,...,列名n));
create table www (id int,name char,tel int,hobby char,sex char,index www_index (name,sex));
show create table www;
修改表的方式创建索引组
alter table www add index name_index (name,hobby);
show create table www;
se1ect * from 表名 where 列名l=',,,' AND列名2=',,,'AND列名3=',,.';
组合索引创建的字段顺序是其触发索引的查询顺序
全文索引
适合在进行模糊查询的时候使用,可用于在一篇文章中检索文本信息。在MysQL5.6版本以前FULLTEXT索引仅可用于MyISAM引擎,在5.6版本之后 innodb引擎也支持FULLTEXT索引。全文索引可以在 CHAR、VARCHAR或者TEXT类型的列上创建。每个表只允许有一个全文索引。
直接创建索引
create fulltext index 索引名 on 表名 (列名);
show index from 表名;
show keys from 表名;
修改表方式创建全文索引
alter table 表名 add fulltext index 索引名 (列名);
创建表的时候指定索引
CREATE TABLE 表名(字段1数据类型[,··.],FULLTEXT索引名(列名));
create table wtf (id int,name char,tel int,fulltext name_index(name));
使用全文索引查询
select * from 表名 where match(列名) against('查询内容');
查看索引
show index from 表名;
show index from 表名\G; #竖向显示表索引信息
show keys from 表名;
show keys from 表名\G;
参数含义
Table:表的名称
Non_unique:如果索引不能包括重复词,则为0;如果可以,则为1
Key_name:索引的名称
seq_in_index:索引中的列序号,从1开始
column_name:列名称
collation:列以什么方式存储在索引中。在 MySQL中,有值’A(升序)或 NULL(无分类)
Cardinality:索引中唯一值数目的估计值
sub_part:如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL
Packed:指示关键字如何被压缩。如果没有被压缩,则为NULL
Null:如果列含有NULL,则含有YES。如果没有,则该列含有NO
lndex_type:用过的索引方法(BTREE,FULLTEXT,HASH,RTREE)
comment:备注
小结
索引分为:
普通索引:针对所有字段,没有特殊的需求/规则
唯一索引:针对唯一性的字段,仅允许出现一次空值
组合索引(多列/多字段组合形式的索引)
全文索引(varchar char text)
主键索引:针对唯一性字段、且不可为空,同时一张表只允许包含一个主键索引
创建索引:
在创建表的时候,直接指定index
alter修改表结构的时候,进行add添加index
直接创建索引index
PS:主键索引一》直接创建主键即可
删除索引
直接删除索引
DROP INDEX 索引名 ON 表名:
例:drop index name_index on member;
修改表方式删除索引
ALTER TABLE 表名 DROP INDEX 索引名;
例:alter table member drop index id_index;
show index from member;
删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;
删除索引
DROP INDEX 索引名 ON 表名:
例:drop index name_index on member;
修改表方式删除索引
ALTER TABLE 表名 DROP INDEX 索引名;
例:alter table member drop index id_index;
show index from member;
删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;