用十万级数据进行讲解MySQL索引基础

我会带着你远行 2023-07-24 02:06 38阅读 0赞

### 文章目录 ###

*  索引简介
 *  主键索引
 *  唯一索引
 *  普通索引
 *  组合索引
 *  全文索引

# 索引简介 #

索引是为了提高数据库查询效率而生的。对于一些查询多，修改少的字段很适合用索引，以提高查询效率。如果是修改多的话，用索引会降低其效率，因为维护这个索引表很耗时。索引分为，**主键索引(primary key)**、**唯一索引(unique index)**、**普通索引(index)**、**组合索引、全文索引**。  
下面先演示一下用索引和不用索引的区别。

*  **查看表中数据数量**  
    `select count(*) from 表名;`  
    ![在这里插入图片描述][20200409081904260.png]
 *  **查看表中索引**  
    `show index from 表名;`  
    ![在这里插入图片描述][20200409082358301.png]  
    可以看出，这里只有一个主键索引(userId)。而username字段不是索引字段。  
    这里我们通过查询username为70000的数据，进行比较用索引和不用索引的区别。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hlWmhpWWluZ18_size_16_color_FFFFFF_t_70]  
    可以清楚的看到，用索引查询速度很快。
 *  **删除索引**  
    `drop index 索引名 on 表名;`
 *  **删除主键索引，也就是删除了该字段**  
    `alter table 表名 drop 主键字段名;`

# 主键索引 #

主键索引，也就是我们常用的主键，主键只能有一个，但一个主键里可以有多个字段。  
**创建表的时候创建主键索引**

create table test(
       id int(11),
       name varchar(25),
       primary key (id)
    );

*  **创建表之后给表添加主键索引**  
    `alter table test add constraint id primary key(id);`

# 唯一索引 #

唯一索引，索引字段中的值必须是唯一的，但是允许为空值。

*  **创建表的时候创建唯一索引**

create table test(   
    	id int(11),   
    	name varchar(25),   
    	unique index_unique_test_name (name)
    );

*  **创建表之后创建唯一索引**  
    `create unique index index_unique_test_name on test(name);`
 *  **修改表结构为唯一索引**  
    `alter table test add unique index index_unique_test_name (name);`

# 普通索引 #

普通索引是最基本的索引，仅用来加速查询。

create table test(   
    	id int(11),   
    	name varchar(25),   
    	index index_test_name (name)
    );

*  **创建表之后创建普通索引**  
    `create index index_test_name on test(name);`
 *  **修改表结构为普通索引**  
    `alter table test add index index_test_name (name);`

# 组合索引 #

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

create table test(   
    	id int(11),   
    	name varchar(25),   
    	index index_test_id_name (id,name)
    );

*  **创建表之后创建组合索引**  
    `create index index_test_id_name on test(id,name);`
 *  **修改表结构为普通索引**  
    `alter table test add index index_test_id_name (id,name);`

# 全文索引 #

全文索引，是在char，varchar，text类型字段上使用全文索引。就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"欢迎阅读此文章"通过此文章，可能就可以找到该条记录。

*  **创建表的时候创建全文索引**

create table test(   
    	id int(11),   
    	name varchar(25),   
    	content text,
    	fulltext (text)
    );

*  **创建表之后创建组合索引**  
    `create fulltext index index_content on test(content);`
 *  **修改表结构为普通索引**  
    `alter table test add fulltext index index_content (content);`

[20200409081904260.png]: /images/20230528/2748eb64bb4e46a79e677a66ffe062e9.png
[20200409082358301.png]: /images/20230528/6eeb50781d5c441d8d230e21b35cee34.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hlWmhpWWluZ18_size_16_color_FFFFFF_t_70]: /images/20230528/26e7360a8c5640d4977d1ee624164b26.png