Mysql_索引的基本操作 亦凉 2021-07-26 19:49 390阅读 0赞 ### Mysql索引 ### * 一.Mysql索引 * * 1.索引的概念 * 2.索引的作用 * * 作用 * 副作用 * 3.创建索引的原则依据(mysql优化) * 二.索引的分类和创建 * * 创建一个表,插入数据 * 1.普通索引 * * 直接创建索引 * 修改表方式创建索引 * 创建表的时候指定索引 * 2.唯一索引 * * 直接创建唯一索引: * * 修改表方式创建 * 创建表的时候指定 * 3.主键索引 * * 创建表的时候指定 * 修改表方式创建 * 4.组合索引(单列索引与多列索引) * 5.全文索引(FULLTEXT) * * * 直接创建索引 * * 修改表方式创建 * 创建表的时候指定索引 * * 使用全文索引查询 * 三.查看索引 * 四.删除索引 * * * 直接删除索引 * 修改表方式删除索引 * 删除主键索引 * 总结: # 一.Mysql索引 # ## 1.索引的概念 ## * 索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址 * 使用索引后可以不用描述全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据,**因此能加快数据库的查询速度**。 * 索引就好比一本书的目录,可以根据目录中的页码快速找到所需的内容 * 索引是表中一列或者若干列数值排序的方法 * **建立索引的目的是加快对表中记录的查找或排序** ## 2.索引的作用 ## ### 作用 ### * 设置了合适的索引后,数据库利用各种快速定位技术,**能够大大加快查询速度**,这是创建索引的最主要的原因 * 当表很大或查询涉及到多个表时,**使用索引可以成千上万倍地提高查询速度** * 可以降低数据库的IO成本,并且**索引还可以降低数据库的排序成本** * 通过创建唯一性索引,可以保证数据表中每一行数据的唯一性 * 可以加快表于表之间的连接 * 在使用分组排序时,可大大减少分组和排序的时间 ### 副作用 ### * 索引需要**占用额外的磁盘空间** * 对于MylSAM 引擎而言,索引文件和数据文件时分离的,索引文件用于保存数据记录的地址 * 而InnoDB 引擎的表数据文件本身就是索引文件 * 在插入和修改数据时要花费更多时间,因为索引也要随之变动 ## 3.创建索引的原则依据(mysql优化) ## 索引随可以提示数据库查询的速度,但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源,在有索引的情况下,数据库会进行索引查询,然后定位到具体的数据行,如果索引使用不当,会增加数据库的负担 * **表的主键、外键必须有索引**。因为主键具有唯一性,外键关联的是子表的主键,查询时可以快速定位。 * **记录数超过300行的表应该有索引**。如果没有索引,需要把表 遍历一遍,会严重影响数据库的性能 * **经常与其他表连接的表**,在连接字段上应该建立索引。 * **唯一性太差的字段不适合建立索引** * **更新太频繁字段不适合创建索引** * 经常出现在where 子句中的字段,特别是大表的字段,应该建立索引 * **索引应该建立选择高的字段上** * **索引应该建立小的字段上**,对于大的字段或者超长的字段,不要用索引 ## 二.索引的分类和创建 ## ### 创建一个表,插入数据 ### mysql -u root -p create database number; use number; create table class( id int(10) not null, name varchar(20) not null, sex char(2) not null, cardid varchar(20) not null, phone varchar(11), address varchar(50)); desc class; mysql> insert into class values ('1','张三','男','123456','111111','苏州'); mysql> insert into class values ('2','李四','男','123123','22222','南京'); mysql> insert into class values ('3','王五','女','123333','333333','南京'); mysql> insert into class values ('4','赵六','女','122222','444444','苏州'); mysql> insert into class values ('5','赵七','男','1444444','55555','上海'); select * from class; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center] ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 1] ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 2] ### 1.普通索引 ### * 最基本的索引类型,没有唯一性之类的限制 #### 直接创建索引 #### CREATE INDEX 索引名 ON 表名 (列名[(length)]); #(列名(length)):length是可选项,下同。如果忽略 length 的值,则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引,这样有利于减小索引文件的大小。 #索引名建议以“_index”结尾。 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 3] #### 修改表方式创建索引 #### ALTER TABLE 表名 ADD INDEX 索引名 (列名); 例: alter table class add index id_index (id); show index from class\G; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 4] #### 创建表的时候指定索引 #### CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名)); create table class1( id int(10) not null, name varchar(20) not null, index id_index(id)); show index from class1\G; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 5] ## 2.唯一索引 ## * 唯一索引:与普通索引类似,但区别是唯一索引列的每个值都唯一。唯一索引允许有空值(注意和主键不同)。如果是用组合索引创建,则列值的组合必须唯一。添加唯一键将自动创建唯一索引。 ### 直接创建唯一索引: ### CREATE UNIQUE INDEX 索引名 ON 表名(列名); create unique index cardid_index on class(cardid); show index from class\G; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 6] ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 7] #### 修改表方式创建 #### ALTER TABLE 表名 ADD UNIQUE 索引名 (列名); alter table class add unique id_index(id); show index from class\G; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 8] #### 创建表的时候指定 #### CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名)); create table class2( id int(10) not null, name varchar(20) not null, unique id_index(id)); show index on class2; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 9] ### 3.主键索引 ### 是一种特殊的唯一索引,必须指定为“PRIMARY KEY”。一个表只能有一个主键,不允许有空值。 添加主键将自动创建主键索引。 #### 创建表的时候指定 #### CREATE TABLE 表名 ([...],PRIMARY KEY (列名)); create table class3( id int(10) not null, name varchar(20) not null, primary key(id)); show index from class3\G; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 10] #### 修改表方式创建 #### ALTER TABLE 表名 ADD PRIMARY KEY (列名); alter table class add primary key (name); show index from class\G; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 11] # 4.组合索引(单列索引与多列索引) # * 可以是单列上创建的索引,也可以是在多列上创建的索引。需要满足最左原则,因为 select 语句的 where 条件是依次从左往右执行的,所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致,否则索引将不会生效。 CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3)); select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...'; create table class5( id int(10) not null, name varchar(20) not null, sex char(2) not null, index index_class5(id,name,sex)); show index from class5\G; insert into class5 values(1,'zhangsan','男'); select * from class5 where id=1 and name='zhangsan'; select * from class5 where id=1; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 12] ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 13] ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 14] # 5.全文索引(FULLTEXT) # * 适合在进行模糊查询的时候使用,可用于在一篇文章中检索文本信息。 * 在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 引擎, * 在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。 * 全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。每个表只允许有一个全文索引。 ### 直接创建索引 ### CREATE FULLTEXT INDEX 索引名 ON 表名 (列名); create fulltext index name_index on class5(name); show index from class5; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 15] ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 16] #### 修改表方式创建 #### ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名); alter table class5 add fulltext name_index(name); show index from class5\G; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 17] ### 创建表的时候指定索引 ### CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名)); #数据类型可以为 CHAR、VARCHAR 或者 TEXT create table class6 ( id int(10) not null, name varchar(20) not null, address varchar(50), fulltext name_index(name)); show index from class6\G; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 18] #### 使用全文索引查询 #### SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容'); insert into class6 values ('3','zhangsan','南京'); insert into class6 values ('4','lisi','上海'); insert into class6 values ('2','liliu','北京'); select * from class6 where match(name) against('zhangsan'); select * from class6 where match(name) against('lisi'); ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 19] ### 三.查看索引 ### show index from 表名; show keys from 表名; 各字段的含义如下: 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。 Index_type:用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。 Comment:备注。 show index from class6\G; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 20] ## 四.删除索引 ## #### 直接删除索引 #### DROP INDEX 索引名 ON 表名; drop index name_index on class6; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 21] #### 修改表方式删除索引 #### ALTER TABLE 表名 DROP INDEX 索引名; alter table class6 drop index id_index; show index from class6\G; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 22] #### 删除主键索引 #### ALTER TABLE 表名 DROP PRIMARY KEY; alter table class drop primary key; show index from class\G; ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 23] ### 总结: ### **索引**:一个排序的列表,通过整个列表可以找到对应的数据 **索引的作用**: ①快速定位、减少查询时间、加快查询速度 ②降低数据库的IO(输入输出)、排序成本 ③加快表之间的连接 ④建立唯一索引 保证唯一性 ⑤分组和排序时,减少分组排序时间 **索引的副作用**:①占用额外的磁盘空间 ②插入和修改数据时候,需要花费更多的时间 **mysql优化=创建索引** **创建索引得条件** 建立在: 选择高、小的字段上 ①表的主键、外键必须有索引 ②超过300行的表,要有索引 ③经常与其他表连接的表,要有索引 ④唯一性差不适合 ⑤更新频繁不时候 **索引分类**: 普通索引 唯一索引 主键索引 组合索引 全文索引 [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center]: /images/20210726/c6e97fb7944740628149779fd6a2ca2a.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20210726/2f3763d7e58d49ee9bb26f0f61b20fbd.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20210726/bce34826277247ddbc954fd257918977.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20210726/fcac965bff8240158fe80e1093757246.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20210726/9ed9af5c19b941c2b93fac9c5c1fdf16.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20210726/d9fa91d0f3584d629a55c555d78623f9.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20210726/7ca76373cc98458795791f61fe64b8fb.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20210726/1dba2fadfcd049cdbe4528757454b614.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20210726/737890c40fa4406baae0a8fa95a991e4.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20210726/ce2bbefb843743b19040bc93123b0a3b.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 10]: /images/20210726/a7de70a139bc45b2b507bcfaaa58bceb.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 11]: /images/20210726/40693e3cd093496ba9301831f2f4a69a.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 12]: /images/20210726/8e0dbdbbe05c4a609925f3e294a48a5d.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 13]: /images/20210726/39d80e51865740a4a76f244c1e74b779.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 14]: /images/20210726/e9bd62282b6b40d59100fb37ee02d8ef.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 15]: /images/20210726/182104ed205d443cb947d2b107e29f15.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 16]: /images/20210726/171695c56df34914921aafce2fa2924c.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 17]: /images/20210726/95b50b50a9c04e60906c7a7e3effd07d.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 18]: /images/20210726/05daeced9580443b8d913fd1be51aabf.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 19]: /images/20210726/b2a8e8171c894be8801eec88378325e2.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 20]: /images/20210726/0f2cf7758fdb48eca96b666eaaaf63f9.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 21]: /images/20210726/86eb6de5b35e4bcb8fbe59040b8d2aa2.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 22]: /images/20210726/d4b65736f53146e1ab03343f742d8e42.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dzeHlp_size_16_color_FFFFFF_t_70_pic_center 23]: /images/20210726/94b528c49752453091271662c55bc37b.png
还没有评论,来说两句吧...