MySQL数据库索引_create table test(1)，2024年最新实现原理讲解

最新推荐文章于 2024-08-08 09:08:53 发布

2401_84181801

最新推荐文章于 2024-08-08 09:08:53 发布

阅读量1k

点赞数 22

分类专栏： 2024年程序员学习文章标签：数据库 mysql

本文链接：https://blog.csdn.net/2401_84181801/article/details/137853724

版权

2024年程序员学习专栏收录该内容

74 篇文章 0 订阅

订阅专栏

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新大数据全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip204888 （备注大数据）

正文

(列名(length)): length是可选项。如果忽略length的值，则使用整个列的值作为索引。如果指定使用列前的length 个字符来创建索引，这样有利于减小索引文件的大小。

索引名建议以" index"结尾。

例子: create index phone_index on member (phone);
select phone from member;
show create table member;


 ![](https://img-blog.csdnimg.cn/29c6465894fd4886bd8b5d56b8228b99.png)


![](https://img-blog.csdnimg.cn/beaa0191d7e84d848d62fc21391e409d.png)


### 3.修改表结构的方式创建

alter table 表名 add index 索引名(列名);
例子: alter table member add index id_index (id);
select id from member;
select id, name from member;


![](https://img-blog.csdnimg.cn/88f8c96509a744f9b6a2956fcc4db67d.png)


### 4.创建表的时候指定索引

create table 表名(字段1 数据类型,字段2 数据类型[,…], index索引名(列名));

例: create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));

show create table test;


![](https://img-blog.csdnimg.cn/70bae1d23ccb4209a362dcfcd992d6ea.png)


![](https://img-blog.csdnimg.cn/b48b826c75f942dc9c850da6017d26fc.png)  


![](https://img-blog.csdnimg.cn/ad53afeed3d348c184598709711ec25b.png)


![](https://img-blog.csdnimg.cn/115de9a357db4d6b9a5e771ea263739a.png)


![](https://img-blog.csdnimg.cn/4dd853ebed2c473d9248d44b5ff0c48c.png)


![](https://img-blog.csdnimg.cn/8a125b42df6d4b17a889f5951676a689.png)


### 5.唯一索引


        唯一键   unique  key


        与普通索引类似，但区别是唯一索引列的每个值都唯一。  
         唯一索引允许有空值(注意和主键不同)。如果是用组合索引创建，则列值的组合必须唯一”。添加唯 一键将自动创建唯一索引。


#### (1)直接创建唯一索引

create unique index 索引名 on 表名(列名);

例:
select * from member;
create unique index address_index on member (address);
create unique index name_index on member (name);
show create table member;


#### (2)修改表方式创建

alter table 表名 add unique 索引名(列名);

例: alter table member add unique cardid_index (cardid) ;


![](https://img-blog.csdnimg.cn/ca5ea4dc12314a7c86308a806a79e475.png)


![](https://img-blog.csdnimg.cn/956865c838a94f96af4a8817d4a18f8f.png)  


#### (3)创建表的时候指定

create table 表名(字段1 数据类型,字段2 数据类型[…],unique索引名(列名));

例:
create table amd2 (id int,name varchar (20),unique id_index (id));
show creat table amd2;
create table test2 (id int, name varchar(40),age int (5),primary key (id));


![](https://img-blog.csdnimg.cn/3fd1a9e23de34bb29a324088d0818d88.png)


![](https://img-blog.csdnimg.cn/fcc4921a6c7c4e29a69083be361a45a1.png)


 ![](https://img-blog.csdnimg.cn/827a2e16dbbb45da8afc9fdb7f523d55.png)


![](https://img-blog.csdnimg.cn/d04f71068cbe417e98810e0deb2c278f.png)


![](https://img-blog.csdnimg.cn/b39d0e897d784e8d9b9986d9968c425f.png)


![](https://img-blog.csdnimg.cn/4d15acef160b41d18705ba807dfd94c6.png)


![](https://img-blog.csdnimg.cn/38c3db9f8f5c4a11828203282857fca8.png)


###  6.主键索引


是一种特殊的唯一索引，必须指定为"primary key”。  
 一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。


#### (1)创建表的时候指定

create table 表名([…], primary key (列名));

例:
create table test1 (id int primary key, name varchar (20));
create table test2 (id int,name varchar(20),primary key (id));

show create table test1;
show create table test2;


#### (2)修改表方式创建

alter table 表名 add primary key (列名);


### 7.组合索引(单列索引与多列索引)


可以是单列上创建的索引，也可以是在多列上创建的索引。

create table 表名(列名1数据类型,列名2数据类型,列名3数据类型,index 索引名(列名1,列名2,列名3));
@select * from 表名where 列名1=‘…’ AND列名2-‘…’ AND列名3=’ …';

例:create table amd1 (id int not null, name varchar (20) ,cardid varchar(20) , index index_ amd (id, name)) ;
show create table amd1 ;
insert into amd1 values (1, ‘zhangsan’, ‘123123’) ;
select * from amd1 where name= ’ zhangsan’ and id=1;

小结:
组合索引创建的字段顺序是其触发索引的查询顺序

例如:
| test3 | create table “test3” (
“id” int(11) NOT NULL,
“name” varchar(50) default null,
“age” int(5) default null,
key “index_idname” (“id”,“name”)
) engine-innoDB default charset-utf8 |

对以上的表进行select

select id, name from test3; 会触发组合索引

而:

select name, id from test3; 按照索引从左到右检索的顺序，则不会触发组合索引.


![](https://img-blog.csdnimg.cn/563c1e7e429443f6947ec02b36b9c926.png)


![](https://img-blog.csdnimg.cn/d0717ff8c1ad47b08b48943addb3a50c.png)


###  8.全文索引(full text)


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


        在MySQL5.6版本以前FULLTEXT索引仅可用于MyISAM引擎，在5.6版本之后innodb 引擎也支持FULLTEXT索引。全文索引可以在CHAR、VARCHAR 或者TEXT类型的列上创建。每个表只允许有一个全文索引。


#### (1)直接创建索引

create full text index 索引名 on 表名(列名);

例:
select * from member;
create fulltext index remark_index on member (remark);


#### (2)修改表方式创建

alter table 表名 add full text 索引名(列名);


#### (3)创建表的时候指定索引

create table 表名(字段1 数据类型[, …], full text 索引名(列名));

数据类型可以为char、varchar 或者text


#### (4)使用全文索引查询

select * from 表名 where match (列名) against(‘查询内容’);

例:
select * from member where match (remark) against(‘this is vip’);
or
select * from member where remark=‘this is vip’;


## 五、查看索引

show index from表名;
show index from 表名\G; 竖向显示表索引信息
show keys from 表名;
show keys from表名\G;


![](https://img-blog.csdnimg.cn/49188fe0592540c9a8602b2aa44b17fe.png) 


![](https://img-blog.csdnimg.cn/4af6b1630c264322a5caf7350fceab2c.png)  


各字段的含义如下:




|  |  |
| --- | --- |
| table | 表的名称 |
| Non\_unique  | 如果索引内容唯一，则为0；如果可以不唯一，则为1。 |
| Key\_name | 索引的名称 |
| Seq\_in\_index | 索引中的列序号，从1开始。limit 2,3 |
| Column\_name | 列名称 |
| Collation | 列以什么方式存储在索引中。在MySQL中，有值'A’(升序)或NULL (无分类)。 |
| Cardinality | 索引中唯一值数目的估计值。 |
| Sub\_part  | 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。 |
| Packed | 指示关键字如何被压缩。如果没有被压缩，则为NULL。 |
| Null | 如果列含有NULL， 则含有YES。如果没有，则该列含有NO。 |
| Index\_type | 用过的索引方法( BTREE， FULLTEXT， HASH， RTREE)  |
| Comment | 备注 |


索引分类:  
 1.普通索引:针对所有字段，没有特殊的需求/规则  
 2.唯一索引:针对唯一性的字段，仅允许出现一次空值  
 3.组合索引:(多列/多字段组合形式的索引)  
 4.全文索引:(varchar char text)  
 5.主键索引:针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引


创建索引:  
 1.在创建表的时候，直接指定index  
 2.alter修改表结构的时候，进行add添加index  
 3.直接创建索引index  
 4.PS:主键索引→直接创建主键即可 


## 六、删除索引


### 1.直接删除索引

drop index 索引名 on 表名;
例: drop index name_index on member;


![](https://img-blog.csdnimg.cn/3d515f5b7f164468b99fbfffba9d5ba6.png)  


![](https://img-blog.csdnimg.cn/8c6b7a7dc219423088860124e364ac66.png)  


### 2.修改表方式删除索引





**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**需要这份系统化的资料的朋友，可以添加V获取：vip204888 （备注大数据）**
![img](https://img-blog.csdnimg.cn/img_convert/c0cb116cf9d256b301163da9c8d37aa1.png)

**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**


![](https://img-blog.csdnimg.cn/8c6b7a7dc219423088860124e364ac66.png)  


### 2.修改表方式删除索引





**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**需要这份系统化的资料的朋友，可以添加V获取：vip204888 （备注大数据）**
[外链图片转存中...(img-xuYCV25u-1713312189432)]

**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**