mysql 索引

mysql 索引

数据库的索引 ：

是一个排序的列表 存储引值和这个值对应的物理地址

无须对整个表进行扫描 通过物理地址就可以找到所需数据

是表中一列或者若干列值排序方法

需要额外的磁盘空间

目的：加快搜索 表中记录的内容 方便你加快看到你想看道德东西

索引的作用

1.数据库利用各种快速定位技术，能够大大加快查询速率

2.当表很大或查询涉及到多个表时，可以成千上万倍地提高查询速度
3.可以降低数据库的IO成本，并且还可以降低数据库的排序成本
4.通过创建唯一性索引保证数据表数据的唯一性可以加快表与表之间的连接
5.在使用分组和排序时可大大减少分组和排序时间

索引的副作用:

.索引需要古用额外的磁盘空间。
对于MyISAM引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。而InnoDB引擎的表数据文件本身就是索引文件。
.在插入和修改数据时要花费更多的时间，因为索引也要随之变动。

普通索引

最基本的索引类型 没有唯一性之类的限制

创建普通索引的方式

直接创建索引：

CREATE INDEX 索引名 ON 表名

唯一索引：

与普通索引类似 但区别是唯一索引的每个值都是唯一

直接创建唯一索引 ：

CREATE UNIQUE INDEX 索引名 ON 表名 （列名）

[外链图片转存中…(img-xaPtGGCs-1660033468200)]

show create table wang

[外链图片转存中…(img-Omx72FuT-1660033468201)]

修改表方式创建：

ALTER TABLE 表名 ADD UNIQUE 索引名 （列名）

[外链图片转存中…(img-BMIyIisS-1660033468201)]

show create table wang

[外链图片转存中…(img-DmQ9JQXt-1660033468201)]

创建表的时候指定索引 ：

CREATE TABLE 表名 （字段 数据类型 字段2 数据类型 等等）INDEX 索引名（列名）；

[外链图片转存中…(img-32hserzi-1660033468202)]

show create table amd1

[外链图片转存中…(img-25Oe01My-1660033468202)]

主键索引

是一种特殊的唯一索引 必须指定为 primary key

一个表只能有一个主键 不允许有空值 添加主键将自动创建主键索引

创建表的时候指定

CREATE TABLE 表名 （。。。）,PEIMARY KEY (列名)

test1：

[外链图片转存中…(img-U4lnTMWP-1660033468202)]

[外链图片转存中…(img-pjMfPSpY-1660033468203)]

test2：

[外链图片转存中…(img-AkkYa8FY-1660033468203)]

[外链图片转存中…(img-k0zyAGs8-1660033468203)]

组合索引

可以是单列上创建的索引 也可以是在多列上创建 的索引，需要满足最左侧原则 因为select语句条件是依次从左到右的字段顺序执行的

CREATE TABLE 表名 （列名 数据类型 列名2.。。。 ）

select * from 表名 where 列名1=“…”

[外链图片转存中…(img-5agRI4uM-1660033468203)]

全文索引

适合在模糊查询的时候使用 可用于一篇中检索文本信息

直接创建检索

create fulltext index 索引名 表名（列名）

修改表方式创建

ALTER TABLE 表名 ADD FULLTEXT索引名（列名）

创建表的时侯指定引索

create table 表名 （字段1 数据类型。。）FULLTEXT 索引名 （列名）

使用全文索引查询

select *from 表名 match（列名）against （’查询内容‘）

查看索引

show index form 表名

show index from 表名 \G

show key from 表名

show key from 表名 \G

Table 表的名称
Non unique 如果索引内容唯一，则为0:如果可以不唯一，则为1。
Key name 索引的名称。
Seq in index 索引中的列序号，从1开始。limit2.3
Column name 列名称。
Collation 列以什么方式存储在索引中。在MySQL中，有值’A’(升序)或NULL(无分类)
Cardinality索引中唯一值数目的估计值。 Sub part
如果列只是被部分地编入索引，则为被编入索引的字符的数目(zhangsan)。如果整列被编入索引，则为 NULL
Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Indextype 用过的索引方法(BTREE，FULLTEXTHASHRTREE)。 Comment 备注。

小结:

索引分为:
普通索引:针对所有字段，没有特殊的需求/规则

唯一索引 :针对唯一性的字段，仅允许出现一次空值组合索引(多列/多字段组合形式的索引)

全文索引：(varchar char text)只对文章文字进行检索

主键索引: 针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引

创建索引

在创建表的时候，直接指定index

alter修改表结构的时候，进行add添加index

直接创建索引index

PS:主键索引一》直接创建主键即可

删除索引

直接删除索引

DROP INDEX索引名 ON表名;
例:drop index name indexon member;

[外链图片转存中…(img-FNho3lir-1660033468204)]

[外链图片转存中…(img-8dcdqyeG-1660033468204)]**

修改表方式删除索引
ALTER TABLE 表名 DROP INDEX 索引名;
例:alter table member drop indexid index; show index from member;
删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;

1660033468204)]**

修改表方式删除索引
ALTER TABLE 表名 DROP INDEX 索引名;
例:alter table member drop indexid index; show index from member;
删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;