乐趣区

关于数据库:MySQL-索引是如何提高查询效率的

jiezi

有两种药包治百病：工夫和缄默。

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 中，只读的性能超过了每秒一百万次：

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

MySQL Index

Why Index

从概念上讲，数据库是数据表的汇合，数据表是数据行和数据列的汇合。当你执行一个 SELECT 语句 从数据表中查问局部数据行的时候，失去的就是另外一个数据表和数据行的汇合。

当然，咱们都心愿取得这个新的汇合的工夫尽可能地短，效率尽可能地高，这就是优化查问。

晋升查问速度的技术有很多，其中最重要的就是索引。当你发现自己的查问速度慢的时候，最快解决问题的办法就是应用索引。索引的应用是影响查问速度的重要因素。在应用索引之前其余的优化查问的动作纯正是浪费时间，只有正当地应用索引之后，才有必要思考其余优化形式。

索引是如何工作的

首先，在你的 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;

剖析后果如下：

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

上面为 action 列增加索引：

ALTER TABLE t_user_action_log ADD INDEX (`action`);

而后再次执行查问剖析，后果如下：

那么为什么索引会进步查问速度呢？起因是索引会依据索引值进行分类，这样就不必再进行全表扫描了。咱们看到这次查问就应用索引了。加索引前 Extra 的值是 Using Where，加索引后 Extra 的值为空。

比方上图，action值为 2 的索引值分类存储在了索引空间，能够疾速地查问到索引值所对应的列。

如何应用

上面介绍一下如何应用 SQL 创立、查看和删除索引。

创立索引

三种形式：

应用 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));

应用 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;


建表的时候创立索引：

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

查看索引

能够通过 show 语句查看索引：

SHOW INDEX FROM t_user_action_log;

删除索引

应用 ALTER 命令能够删除索引，例如：

ALTER TABLE t_user_action_log DROP INDEX index_ip_addr;

索引的应用准则

索引因为其提供的优越的查问性能，仿佛不应用索引就是一个愚昧的行为了。然而应用索引，是要付出工夫和空间的代价的。因而，索引虽好不可贪多。

上面介绍几个索引的应用技巧和准则，在应用索引之前，你应该对它们有充沛的意识。

写操作比拟频繁的列谨慎加索引

索引在进步查问速度的同时，也因为须要更新索引而带来了升高插入、删除和更新带索引列的速度的问题。一张数据表的索引越多，在写操作的时候性能降落的越厉害。

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

与没有加索引比拟，加索引会更快地使你的磁盘靠近应用空间极限。

不要为输入列加索引

为查问条件、分组、连贯条件的列加索引，而不是为查问输入后果的列加索引。

例如上面的查问语句：

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

所以能够思考减少在 name action create_time 列上，而不是 ip_address。

思考维度劣势

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

维度越高（实践上维度的最大值就是数据行的总数），数据列蕴含的举世无双的值就越多，索引的应用成果越好。

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

例如性别列的值只有男和女，每种查问后果占比大概 50%。个别当查问优化处理器发现查问后果超过全表的 30% 的时候，就会跳过索引，间接进行全表扫描。

对短小的值加索引

对短小的值加索引，意味着索引所占的空间更小，能够缩小 I / O 流动，同时比拟索引的速度也更快。

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

另外，InnoDB 应用的是汇集索引（clustered index），也就是把主键和数据行保留在一起。主键之外的其余索引都是二级索引，这些二级索引也保留着一份主键，这样在查问到索引当前，就能够依据主键找到对应的数据行。如果主键太长的话，会造成二级索引占用的空间变大。

比方上面的 action 索引保留了对应行的 id。

为字符串前缀加索引

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

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

复合索引的左侧索引

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

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


咱们能够看到，最左侧的 column1 索引总是无效的。

索引加锁

对于 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;
  1. 而后开启另外一个连贯 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的数据行能够失常提交。

  1. 连贯 1 提交事务，连贯 2 的 id=1 和id=5的数据行能够 update 胜利了。
-- 在连贯 1 提交事务
commit;
  1. 如果不应用索引

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';

笼罩索引

如果索引蕴含满足查问的所有数据，就被称为笼罩索引(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;

聚簇索引

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

字符串类型不倡议应用聚簇索引，特地是随机字符串，因为它们会使零碎进行大量的挪动操作。

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

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

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

抉择适合的索引类型

从数据结构角度来看，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 依据表统计信息，以及索引选用的状况，找到所需记录须要读取的行数。这个行数是估算的值，理论行数可能不同。

用好 explain 命令是查问优化的第一步 !

申明 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 还是软件开发，菜鸟程序员还是资深工程师，置信本节提到的对于索引的常识，对你都会有所帮忙。

退出移动版