mysql索引及查询优化

声明: 文章转载自:MySQL索引与查询优化 (opens new window)

About MySQL

MySQL（读作/maɪ ˈsiːkwəl/“My Sequel”）是一个开放源码的关系数据库管理系统，原开发者为瑞典的MySQL AB公司，目前为Oracle旗下产品。

被甲骨文公司收购后，自由软件社群们对于Oracle是否还会持续支持MySQL社群版（MySQL之中唯一的免费版本）有所隐忧，因此MySQL的创始人麦克尔·维德纽斯以MySQL为基础，成立分支计划MariaDB。原先一些使用MySQL的开源软件，部分转向了MariaDB或其它的数据库。

不可否认的是，MySQL由于其性能高、成本低、可靠性好，已经成为最流行的开源数据库之一，随着MySQL的不断成熟，它也逐渐用于更多大规模网站和应用，非常流行的开源软件组合LAMP中的“M”指的就是MySQL。

Why MySQL

在众多开源免费的关系型数据库系统中，MySQL有以下比较出众的优势：

1. 运行速度快
2. 易使用
3. SQL语言支持
4. 移植性好
5. 功能丰富
6. 成本低廉

对于其中运行速度，根据官方介绍，MySQL 8.0 比之前广泛使用的版本 MySQL 5.7 有了两倍的提升。

在其官方的Benchmarks (opens new window)中，只读的性能超过了每秒一百万次：

读写的性能接近每秒二十五万次：

(opens new window)

MySQL Index

Why Index

从概念上讲，数据库是数据表的集合，数据表是数据行和数据列的集合。当你执行一个SELECT语句从数据表中查询部分数据行的时候，得到的就是另外一个数据表和数据行的集合。

当然，我们都希望获得这个新的集合的时间尽可能地短，效率尽可能地高，这就是优化查询。

提升查询速度的技术有很多，其中最重要的就是索引。当你发现自己的查询速度慢的时候，最快解决问题的方法就是使用索引。索引的使用是影响查询速度的重要因素。在使用索引之前其他的优化查询的动作纯粹是浪费时间，只有合理地使用索引之后，才有必要考虑其他优化方式。 (opens new window)

索引是如何工作的

首先，在你的MySQL上创建t_user_action_log 表，方便下面进行演示。

CREATE DATABASE `ijiangtao_local_db_mysql` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE ijiangtao_local_db_mysql;

DROP TABLE IF EXISTS t_user_action_log;

CREATE TABLE `t_user_action_log` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` VARCHAR(32) DEFAULT NULL COMMENT '用户名',
  `ip_address` VARCHAR(50) DEFAULT NULL COMMENT 'IP地址',
  `action` INT4 DEFAULT NULL COMMENT '操作：1-登录，2-登出，3-购物，4-退货，5-浏览',
  `create_time` TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.1', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.3', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.4', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.1', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 5, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 5, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 4, CURRENT_TIMESTAMP);

假如我们要筛选 action为2的所有记录，SQL如下：

SELECT id, name, ip_address FROM t_user_action_log WHERE `action`=2;

通过查询分析器explain分析这条查询语句：

EXPLAIN SELECT id, name, ip_address FROM t_user_action_log WHERE `action`=2;

分析结果如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_action_log		ALL					1	100.00	Using where

其中type为ALL表示要进行全表扫描。这样效率无疑是极慢的。

下面为action列添加索引：

ALTER TABLE t_user_action_log ADD INDEX (`action`);

然后再次执行查询分析，结果如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_action_log		ref	action	action	5	const	1	100.00

我们看到这次查询就使用索引了。加索引前Extra的值是Using Where，加索引后Extra的值为空。

那么为什么索引会提高查询速度呢？原因是索引会根据索引值进行分类，这样就不用再进行全表扫描了。

比如上图，action值为2的索引值分类存储在了索引空间，可以快速地查询到索引值所对应的列。 (opens new window)

如何使用

下面介绍一下如何使用SQL创建、查看和删除索引。

创建索引

三种方式：

1. 使用CREATE INDEX创建，语法如下：

CREATE INDEX indexName ON tableName (columnName(length));

例如我们对ip_address这一列创建一个长度为16的索引：

CREATE INDEX index_ip_addr ON t_user_action_log (ip_address(16));

2. 使用ALTER语句创建，语法如下：

ALTER TABLE tableName ADD INDEX indexName(columnName);

ALTER语句创建索引前面已经有例子了。下面提供一个设置索引长度的例子：

ALTER TABLE t_user_action_log ADD INDEX ip_address_idx (ip_address(16));

SHOW INDEX FROM t_user_action_log;

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t_user_action_log	1	ip_address_idx	1	ip_address	A	1	16		YES	BTREE

3. 建表的时候创建索引：

CREATE TABLE tableName(
  id INT NOT NULL,
  columnName  columnType,
  INDEX [indexName] (columnName(length))
);

查看索引

可以通过show语句查看索引：

SHOW INDEX FROM t_user_action_log;

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t_user_action_log	0	PRIMARY	1	id	A	199,368				BTREE
t_user_action_log	1	action	1	action	A	4			YES	BTREE
t_user_action_log	1	index_ip_addr	1	ip_address	A	1	16		YES	BTREE

删除索引

使用ALTER命令可以删除索引，例如：

ALTER TABLE t_user_action_log DROP INDEX index_ip_addr;

索引的使用原则

索引由于其提供的优越的查询性能，似乎不使用索引就是一个愚蠢的行为了。但是使用索引，是要付出时间和空间的代价的。因此，索引虽好不可贪多。

下面介绍几个索引的使用技巧和原则，在使用索引之前，你应该对它们有充分的认识。 (opens new window)

1.写操作比较频繁的列慎重加索引

索引在提高查询速度的同时，也由于需要更新索引而带来了降低插入、删除和更新带索引列的速度的问题。一张数据表的索引越多，在写操作的时候性能下降的越厉害。 (opens new window)

2.索引越多占用磁盘空间越大

与没有加索引比较，加索引会更快地使你的磁盘接近使用空间极限。 (opens new window)

3.不要为输出列加索引

为查询条件、分组、连接条件的列加索引，而不是为查询输出结果的列加索引。

例如下面的查询语句：

select ip_address from t_user_action_log
where name='LiSi'
group by action
order by create_time;

所以可以考虑增加在 name action create_time 列上，而不是 ip_address。 (opens new window)

4.考虑维度优势

例如action列的值包含：1、2、3、4、5，那么该列的维度就是5。

维度越高（理论上维度的最大值就是数据行的总数），数据列包含的独一无二的值就越多，索引的使用效果越好。

对于维度很低的数据列，索引几乎不会起作用，因此没有必要加索引。

例如性别列的值只有男和女，每种查询结果占比大约50%。一般当查询优化处理器发现查询结果超过全表的30%的时候，就会跳过索引，直接进行全表扫描。 (opens new window)

4.对短小的值加索引

对短小的值加索引，意味着索引所占的空间更小，可以减少I/O活动，同时比较索引的速度也更快。

尤其是主键，要尽可能短小。

另外，InnoDB使用的是聚集索引（clustered index），也就是把主键和数据行保存在一起。主键之外的其他索引都是二级索引，这些二级索引也保留着一份主键，这样在查询到索引以后，就可以根据主键找到对应的数据行。如果主键太长的话，会造成二级索引占用的空间变大。

比如下面的action索引保存了对应行的id。

5.为字符串前缀加索引

前边已经讲过短小索引的种种好处了，有时候一个字符串的前几个字符就能唯一标识这条记录，这个时候设置索引的长度就是非常划算的做法。

前面已经提供了设置索引length的例子，这里就不举例子了。

6.复合索引的左侧索引

创建复合索引的语法如下：

CREATE INDEX indexName ON tableName (column1 DESC, column2 DESC, column3 ASC);

我们可以看到，最左侧的column1索引总是有效的。

7.索引加锁

对于InnoDB来说，索引可以让查询锁住更少的行，从而可以在并发情况下拥有更佳表现。

下面演示一下查询锁与索引之间的关系。

前面使用的t_user_action_log表目前有一个id为主键，还有一个二级索引action。

下面这条语句的修改范围是id值为1 2 3 4所在的行，查询锁会锁住id值为1 2 3 4 5所在的行。

update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where id<5;

1. 首先创建数据库连接1，开启事务，并执行update语句

set autocommit=0;

begin;

update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where id<5;

2. 然后开启另外一个连接2，分别执行下面几个update语句

-- 没有被锁
update ijiangtao_local_db_mysql.t_user_action_log set name='c2' where id=6;
-- 被锁
update ijiangtao_local_db_mysql.t_user_action_log set name='c2' where id=5;

你会发现id=5的数据行已经被锁定，id=6的数据行可以正常提交。

3. 连接1提交事务，连接2的id=1和id=5的数据行可以update成功了。

-- 在连接1提交事务
commit;

4. 如果不使用索引

ip_address没有索引的话，会锁定全表。

连接1开启事务以后commit;之前，连接2对该表的update全部需要等待连接1释放锁。

set autocommit=0;

begin;

update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where ip_address='8.8.8.1';

8.覆盖索引

如果索引包含满足查询的所有数据，就被称为覆盖索引(Covering Indexes)，覆盖索引非常强大，可以大大提高查询性能。

覆盖索引高性能的原因是：

• 索引通常比记录要小，覆盖索引查询只需要读索引，而不需要读记录。
• 索引都按照值的大小进行顺序存储，相比与随机访问记录，需要更少的I/0。
• 大多数数据引擎能更好的缓存索引，例如MyISAM只缓存索引。

ijiangtao_local_db_mysql表的action列包含索引。使用explain分析下面的查询语句，对于索引覆盖查询(index-covered query)，分析结果Extra的值是Using index，表示使用了覆盖索引 :

explain select `action` from ijiangtao_local_db_mysql.t_user_action_log;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_action_log		index		action	5		199,703	100.00	Using index

9.聚簇索引

聚簇索引(Clustered Indexes)保证关键字的值相近的元组存储的物理位置也相同，且一个表只能有一个聚簇索引。

字符串类型不建议使用聚簇索引，特别是随机字符串，因为它们会使系统进行大量的移动操作。

并不是所有的存储引擎都支持聚簇索引，目前InnoDB支持。

如果使用聚簇索引，最好使用AUTO_INCREMENT列作为主键，应该尽量避免使用随机的聚簇主键。

从物理位置上看，聚簇索引表比非聚簇的索引表，有更好的访问性能。

10.选择合适的索引类型

从数据结构角度来看，MySQL支持的索引类型有B树索引、Hash索引等。

• B树索引

B树索引对于<、<=、 =、 >=、 >、 <>、!=、 between查询，进行精确比较操作和范围比较操作都有比较高的效率。

B树索引也是InnoDB存储引擎默认的索引结构。

• Hash索引

Hash索引仅能满足=、<=>、in查询。

Hash索引检索效率非常高，索引的检索可以一次定位，不像B树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的I/O访问，所以Hash索引的查询效率要远高于B树索引。但Hash索引不能使用范围查询。

查询优化建议

下面提供几个查询优化的建议。

使用explain分析查询语句

前面已经演示过如何使用explain命令分析查询语句了，这里再解释一下其中几个有参考价值的字段的含义：

select_type

select_type表示查询中每个select子句的类型，一般有下面几个值:

• SIMPLE 简单SELECT,不使用UNION或子查询等。

• PRIMARY 查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY。

• UNION UNION中的第二个或后面的SELECT语句。

• DEPENDENT UNION UNION中的第二个或后面的SELECT语句，取决于外面的查询。

• UNION RESULT UNION的结果。

• SUBQUERY 子查询中的第一个SELECT。

• DEPENDENT SUBQUERY 子查询中的第一个SELECT，取决于外面的查询。

• DERIVED 派生表的SELECT, FROM子句的子查询。

• UNCACHEABLE SUBQUERY 一个子查询的结果不能被缓存，必须重新评估外链接的第一行。

type

type表示MySQL在表中找到所需行的方式，又称“访问类型”，常用的类型有：

ALL, index, range, ref, eq_ref, const, system, NULL。

从左到右，性能从差到好。

• ALL： Full Table Scan，MySQL将遍历全表以找到匹配的行。

• index: Full Index Scan，index与ALL区别为index类型只遍历索引树。

• range: 只检索给定范围的行，使用一个索引来选择行。

• ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

• eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件。

• const: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。 如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

• NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

Key

key列显示MySQL实际决定使用的键（索引），如果没有选择索引，键是NULL。

possible_keys

possible_keys指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上如果存在索引则该索引将被列出，但不一定被查询使用。

ref

ref表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

rows

rows表示MySQL根据表统计信息，以及索引选用的情况，找到所需记录需要读取的行数。这个行数是估算的值，实际行数可能不同。

声明NOT NULL

当数据列被声明为NOT NULL以后，在查询的时候就不需要判断是否为NULL，由于减少了判断，可以降低复杂性，提高查询速度。

如果要表示数据列为空，可以使用0等代替。

考虑使用数值类型代替字符串

MySQL对数值类型的处理速度要远远快于字符串，而且数值类型往往更加节省空间。

例如对于“Male”和“Female”可以用“0”和“1”进行代替。

考虑使用ENUM类型

如果你的数据列的取值是确定有限的，可以使用ENUM类型代替字符串。因为MySQL会把这些值表示为一系列对应的数字，这样处理的速度会提高很多。

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');

SELECT name, size FROM shirts WHERE size = 'medium';

总结

索引是一个单独的，存储在磁盘上的数据结构，索引对数据表中一列或者多列值进行排序，索引包含着对数据表中所有数据的引用指针。

本教程从MySQL开始讲起，又介绍了MySQL中索引的使用，最后提供了使用索引的几条原则和优化查询的几个方法。

无论你是DBA还是软件开发，菜鸟程序员还是资深工程师，相信本节提到的关于索引的知识，对你都会有所帮助。

声明: 文章转载自:MySQL索引与查询优化 (opens new window)