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

About MySQL

MySQL（读作/ma sikwl/“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_logwhere name='LiSi'group by actionorder 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还是软件开发，菜鸟程序员还是资深工程师，置信本节提到的对于索引的常识，对你都会有所帮忙。