mysql语句优化增加索引_MYSQL学习笔记——sql语句优化之索引

墨蓝 2023-01-12 10:22 240阅读 0赞

上一篇博客讲了可以使用慢查询日志定位耗时sql，使用explain命令查看mysql的执行计划，以及使用profiling工具查看语句执行真正耗时的地方，当定位了耗时之后怎样优化呢？这篇博客会介绍mysql中最简单快速的优化方法——添加索引。

一、索引的添加

mysql一共有四类索引，分别是主键索引、唯一索引、普通索引以及全文索引。

1.1、主键索引的添加

创建一张表时，把某个列设为主键的时候，则该列就是主键索引。添加主键索引有两种方法，我们可以在建表的时候指定主键，这样就可以在建表时添加索引；我们也可以在建表以后添加主键索引，下面的table1的主键索引是在建表时创建，table2是在建表后添加的主键索引：

create table table1(

id int unsigned primary key auto\_increment ,

name varchar(32) not null

);

//添加主键索引语法：alter table 表名 add primary key (列名);

create table table2(

id int unsigned,

name varchar(32) not null

);

alter table table2 add primary key (id);

1.2、唯一索引的添加

当表的某列被指定为unique约束时，这列就是一个唯一索引。同样，唯一索引的添加也可以分创建时添加和创建后添加，下例中table3的唯一索引是创建时添加，table4的唯一索引是创建后添加的：

create table table3(

id int unsigned primary key auto\_increment ,

name varchar(32) unique

);

//添加唯一索引语法：create unique index 索引名 on 表名 (列表..);

create table table4(

id int unsigned primary key auto\_increment ,

name varchar(32)

);

create unique index my\_uni on table4(name);

1.3、普通索引的添加

普通索引应该是mysql中最常用的索引，因为一张表只能有一个主键索引，数量较少，唯一索引又必须保证内容唯一，限制较多，所以在mysql中最常用的还是普通索引。普通索引的创建也可以分建表时创建和建表后创建，下面例子中table5的普通索引是建表时创建，table6是的普通索引是建表后创建的：

create table table5(

id int unsigned,

name varchar(32),

KEY KEY\_ID\_NAME(id, name)

);

//添加普通索引语法：create index 索引名 on 表 (列1,列名2);

create table table6(

id int unsigned,

name varchar(32)

);

create index KEY\_ID\_NAME on table6 (id, name);

1.4、全文索引

全文索引主要是针对文本或者文件的搜索，在这里就不再举例介绍了。

二、索引的查询

索引的查询很简单，可以使用以下两条命令显示表上的索引：

show index from 表名

或

show keys from 表名

以table5为例，我们可以看看怎么查询索引：

mysql> show index from table5\\G;

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: table5

Non\_unique: 1

Key\_name: KEY\_ID\_NAME

Seq\_in\_index: 1

Column\_name: id

Collation: A

Cardinality: NULL

Sub\_part: NULL

Packed: NULL

Null: YES

Index\_type: BTREE

Comment:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: table5

Non\_unique: 1

Key\_name: KEY\_ID\_NAME

Seq\_in\_index: 2

Column\_name: name

Collation: A

Cardinality: NULL

Sub\_part: NULL

Packed: NULL

Null: YES

Index\_type: BTREE

Comment:

2 rows in set (0.00 sec)

从查询结果可以看出，索引类型是B树，两列结果的Key\_name一样，说明是复合索引等等信息。不过这样看很累，在平时查看索引时，我喜欢使用'show create table'命令查看表上的索引：

mysql> show create table table5\\G;

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: table5

Create Table: CREATE TABLE \`table5\` (

\`id\` int(10) unsigned DEFAULT NULL,

\`name\` varchar(32) DEFAULT NULL,

KEY \`KEY\_ID\_NAME\` (\`id\`,\`name\`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

这样看结果就清晰多了，'KEY KEY\_ID\_NAME(id, name)'直接把索引名称，索引作用列以及索引类型都显示出来了，清晰明了。

三、索引的删除

可以使用以下命令删除索引：alter table 表名 drop index 索引名; 例如我们想删除table5上的KEY\_ID\_NAME索引只需使用以下命令即可：

alter table table5 drop index KEY\_ID\_NAME;

比较特殊的是删除主键索引，删除主键索引的语法如下：alter table tablename drop primary key; 例如我们要删除table2上的主键索引可以使用以下命令：

alter table table2 drop primary key;

四、索引的利弊

索引为我们带来的好处主要有以下两点：1、缩小检索范围，加快检索速度；2、在索引上排序及group by资源消耗极低。

同时，大量使用索引也会给我们带来以下弊端：1、增删改操作将比原来更加耗时；2、索引的存储会占用存储空间。

五、创建索引的考虑点

当要为表创建索引时，我们可以从以下几个方面考虑是否应该创建索引：

1、较频繁的作为查询条件的字段应该创建索引；

2、唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件;

3、更新非常频繁的字段不适合创建索引，因为添加索引后字段更新将更加耗时;

六、使用索引的注意项

1、对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。例如在a,b,c列上创建索引，那么查询条件"a=5","a=5 and b=6","a=7 and b=8 and c=6"都会使用该索引；

2、对于使用like的查询，查询如果是‘%aaa’不会使用到索引‘aaa%’会使用到索引；

3、如果条件中有or，那么除非or条件都带有索引，否则还是会全表扫描；

4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引；

5、如果字符串较长时，直接使用'create index KEY\_NAME on table6 (name)' 命令创建的索引也将较长，浪费磁盘空间；这时候我们可以通过'create index KEY\_NAME on table6 (name(4))' 命令限定索引长度；

6、连接查询时，在连接键上增加索引可以加快速度，同时遵循小表驱动大表原则；

7、如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

七、直观感受索引的威力

上一篇博客中我们创建了一张包含1000w数据的数据表，在该表上我们没有加任何索引，然后查询一条数据话费了6.5s时间。这次我们依然构造一张包含1000w数据的同样结构数据表，不同的是我们为它加上索引，然后查询数据，看一下索引优化的效果。

首先，创建数据库表：

CREATE TABLE emp

(

empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '编号',

ename VARCHAR(20) NOT NULL DEFAULT "" COMMENT '名字',

job VARCHAR(9) NOT NULL DEFAULT "" COMMENT '工作',

mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级编号',

hiredate DATE NOT NULL COMMENT '入职时间',

sal DECIMAL(7,2) NOT NULL COMMENT '薪水',

comm DECIMAL(7,2) NOT NULL COMMENT '红利',

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '部门编号',

KEY KEY\_NO(empno)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后调用存储过程插入1000w条数据：

call insert\_emp(10000000);

最后，调用和上篇博客一模一样的查询语句，看这次查询需要多长时间：

mysql> select \* from emp where empno=413345;

\+--------+--------+----------+-----+------------+---------+--------+--------+

\+--------+--------+----------+-----+------------+---------+--------+--------+

| 413345 | JpugvK | SALESMAN | 1 | 2014-10-27 | 2000.00 | 400.00 | 157 |

\+--------+--------+----------+-----+------------+---------+--------+--------+

1 row in set (0.10 sec)

查询时间从原来的6.5s提升到了0.1s，提高了65倍。

mysql语句优化增加索引_MYSQL学习笔记——sql语句优化之索引

发表评论取消回复

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

相关阅读

相关 MySQL SQL优化与索引优化

相关 SQL语句（五）—— 索引和SQL优化

相关 Mysql之sql语句优化

相关 mysql语句优化增加索引_MYSQL学习笔记——sql语句优化之索引

相关【mysql】sql优化之学习索引（一）

相关 Mysql 语句操作索引SQL语句

相关 MYSQL数据库优化之SQL及索引优化-学习笔记（2）

相关 MYSQL数据库优化之SQL及索引优化-学习笔记（1）

相关 MySQL(二) —— MySQL性能优化之 SQL语句优化

相关【mysql】sql语句优化