Mysql常见四种索引的使用

系统管理员 2022-03-15 02:54 209阅读 0赞

提到mysql优化，索引优化是必不可少的。其中一种优化方式 ——索引优化，添加合适的索引能够让项目的并发能力和抗压能力得到明显的提升。

我们知道项目性能的瓶颈主要是在"查(select)"语句，要提升"查"这一性能，mysql索引是必不可少的。接下来总结一下mysql常见的四种索引

一. 四种索引(主键索引/普通索引/全文索引/唯一索引)

1.索引的添加

**1.1主键索引的添加**

当一张表，把某个列设为主键的时候，则该列就是主键索引

create table a(  
    id int primary key auto_increment,  
    name varchar(20) not null default ''  
    );  
    //这里id就是表的主键

如果当创建表时没有指定主键索引，也可以在创建表之后添加：

alter table table_name add primary key (column name);

**1.2普通索引**

普通索引一般是在建表后再添加的，

create index 索引名 on table_name(column1,column2);
    
    alter table table_name add index 索引名(column1,column2);

**1.3全文索引**

首先，全文索引主要针对文本文件，比如文章，标题，全文索引只有MyISAM有效(mysql5.6之后InnoDB也支持了全文索引)

create table c(  
    id int primary key auto_increment ,  
    title varchar(20),  
    content text,  
    fulltext(title,content)  
    )engine=myisam charset utf8;  
      
    insert into c(title,content) values  
        ('MySQL Tutorial','DBMS stands for DataBase ...'),  
        ('How To Use MySQL Well','After you went through a ...'),  
        ('Optimizing MySQL','In this tutorial we will show ...'),  
        ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),  
        ('MySQL vs. YourSQL','In the following database comparison ...'),  
        ('MySQL Security','When configured properly, MySQL ...');

使用全文索引常见的错误：

select * from c where content like "%mysql%";

这里并不会使用全文索引，可以用explain进行查看。正确用法：

select *  from c where match(title,content) against ('MYSQL');

备注:

1.  在mysql中fulltext 索引只针对 myisam生效

2.  mysql自己提供的fulltext针对英文生效->sphinx(coreseek)技术处理中文

3.  使用方法是 match(字段名..) against(‘关键字’)

**1.4唯一索引**

CREATE UNIQUE INDEX indexName ON table(column(length))

相比主键索引，主键字段不能为null，也不能重复

2. 查询索引

show indexes from table_name;
    
    show keys from table_name;

3.删除索引

alter table table_name drop index 索引名;

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

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

**二. 索引的机制**

2.1 为什么我们添加完索引后查询速度为变快？

> 传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，mysql需要将表的数据从头到尾遍历一遍
> 
> 在我们添加完索引之后，mysql一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历(折半查找大幅查询效率)，找到相应的键从而获取数据

2.2 索引的代价

> 1. 创建索引是为产生索引文件的，占用磁盘空间
> 
> 2. 索引文件是一个二叉树类型的文件，可想而知我们的增删改操作同样也会对索引文件进行修改，所以性能会下降

2.3 在哪些column上使用索引？

> 1 .较频繁的作为查询条件字段应该创建索引 
> 
> 2. 唯一性太差的字段不适合创建索引，尽管频繁作为查询条件，例如gender性别字段
> 
> 3. 更新非常频繁的字段不适合作为索引
> 
> 4.不会出现在where子句中的字段不该创建索引

总结: 满足以下条件的字段，才应该创建索引.

a: 肯定在where条经常使用 b: 该字段的内容不是唯一的几个值 c: 字段内容不是频繁变化

**三.索引使用注意事项**

1.对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。

比如我们对title,content 添加了复合索引

select \* from table\_name where title = 'test';会用到索引

select \* from table\_name where content = 'test';不会用到索引

2.对于使用like的查询，查询如果是 ‘%a’不会使用到索引 ,而 like 'a%'就会用到索引。最前面不能使用%和\_这样的变化值

3.如果条件中有or，即使其中有条件带索引也不会使用。

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。

5.索引不会包含有null值的列  
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。

6.索引列排序  
查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

7.不要在列上进行运算  
这将导致索引失效而进行全表扫描

8.不使用not in和<>操作

**四.如何查看索引使用的情况:**

show status like‘Handler\_read%’;

注意：

handler\_read\_key:这个值越高越好，越高表示使用索引查询到的次数。

handler\_read\_rnd\_next:这个值越高，说明查询低效。

版权声明：本文： [https://blog.csdn.net/u013927110/article/details/46636765][https_blog.csdn.net_u013927110_article_details_46636765]

[https_blog.csdn.net_u013927110_article_details_46636765]: https://blog.csdn.net/u013927110/article/details/46636765

发表评论取消回复

表情：

评论列表（有 0 条评论，209人围观）

还没有评论，来说两句吧...

相关阅读

相关 Mysql索引介绍及常见索引的区别

Mysql索引概念：说说Mysql索引，看到一个很少比如：索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，有

小鱼儿/ 2024年02月19日 17:41/ 0 赞/ 46 阅读

相关四种常见的博弈论

巴什博弈：只有一堆n个物品，两个人轮流从这堆物品中取物，规定每次至少取一个，最多取m个。最后取光者得胜。显然，如果n=m+1，那么由于一次最多只能取m个，所以，无论先取

怼烎@/ 2024年01月16日 00:43/ 0 赞/ 117 阅读

相关【Mysql索引】五种索引类型

目录一、前期准备二、实操实战 1、主键索引 primary 2、唯一索引 unique ①、执行效果

- 日理万妓/ 2023年10月11日 21:01/ 0 赞/ 8 阅读

相关深入浅出MySQL索引（一）常见的索引模型

[深入浅出MySQL索引（一）常见的索引模型][MySQL] [深入浅出MySQL索引（二）InnoDB存储引擎的索引][MySQL_InnoDB] 深入浅出MySQL

傷城~/ 2023年07月05日 14:51/ 0 赞/ 7 阅读

相关 MySQL中的常见索引规则

在使用索引规则的时候，会有一个前置条件，就是索引的类型一般是联合索引，至于为什么要使用联合索引，是因为数据的增删改都会维护索引，所以过多的索引在方便我们快速查询的时候，同时还会

灰太狼/ 2022年10月29日 06:29/ 0 赞/ 9 阅读

相关 MySQL索引的使用

目录索引优缺点优点缺点创建索引 1、ALTER TABLE 2、CREATE INDEX 3、使用 CREATE TABLE 语句删除索引

向右看齐/ 2022年10月11日 15:00/ 0 赞/ 181 阅读

相关 [MySQL]--索引操作常见的SQL

> 这篇博文主要是做一些记录，关于MySQL中索引操作的一些常用命令的使用： 1. 查看某个表的索引 show index from table_name;

深藏阁楼爱情的钟/ 2022年07月10日 04:28/ 0 赞/ 192 阅读

相关 Mysql常见四种索引的使用

提到mysql优化，索引优化是必不可少的。其中一种优化方式 ——索引优化，添加合适的索引能够让项目的并发能力和抗压能力得到明显的提升。我们知道项目性能的瓶颈主要是在"查(s

系统管理员/ 2022年03月15日 02:54/ 0 赞/ 210 阅读

相关 mysql 数据库四种索引的基本使用

MYSQL数据库四种索引类型的简单使用主键索引: 主键是一种唯一性索引，但它必须指定为`PRIMARY KEY`，每个表只能有一个主键。唯一索

秒速五厘米/ 2022年02月16日 19:39/ 0 赞/ 269 阅读

相关 Mysql常见四种索引的使用

提到mysql优化，索引优化是必不可少的。其中一种优化方式 ——索引优化，添加合适的索引能够让项目的并发能力和抗压能力得到明显的提升。我们知道项目性能的瓶颈主要是在"查(s

爱被打了一巴掌/ 2021年11月23日 08:20/ 0 赞/ 228 阅读