EXPLAIN、索引优化、索引的存储结构、存储优化、数据库结构优化

 EXPLAIN
EXPLAIN可以帮助开发人员分析SQL问题，EXPLAIN显示了MySQL如何使用使用SQL执行计划，可以帮助开发人员写出更优化的查询语句。使用方法，在select语句前加上Explain就可以了：

EXPLAIN SELECT * FROM products

结果的列的说明如下：

1) id
SELECT识别符。这是SELECT查询序列号。这个不重要

2) select_type
表示SELECT语句的类型。

simple:简单select（不使用union或子查询）。
primary:最外面的select。
union:union中的第二个或后面的select语句。
dependent union:union中的第二个或后面的select语句，取决于外面的查询。
union result:union的结果。
subquery:子查询中的第一个select。
dependent subquery:子查询中的第一个select，取决于外面的查询。
derived:导出表的select（from子句的子查询）。

3) table
显示这查询的数据是关于哪张表的。

4) type
区间索引，这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为：

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别,最好能达到ref。

system：表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计。
const：数据表最多只有一个匹配行，因为只匹配一行数据，所以很快
eq_ref：mysql手册是这样说的:"对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。
ref(最好在这)：查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。
ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
unique_subquery：该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range：只检索给定范围的行,使用一个索引来选择行。
index：该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL：对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差）
 
5) possible_keys
指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。

6) key
实际使用到的索引。如果为NULL，则没有使用索引。如果为primary的话，表示使用了主键。

7) key_len
最长的索引宽度。如果键是NULL，长度就是NULL。在不损失精确性的情况下，长度越短越好。

8) ref
显示使用哪个列或常数与key一起从表中选择行。

9) rows
显示MySQL认为它执行查询时必须检查的行数。

10) Extra
执行状态说明，该列包含MySQL解决查询的详细信息

Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

 

 

 

 

索引优化
索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧下降。

索引的目的在于提高查询效率，大家可以回忆之前学习的全文检索技术。类似使用字典，如果没有目录（索引），那么我们要从字典的第一个字开始查询到最后一个字才能有结果，可能要把字典中所有的字看一遍才能找到要结果，而目录（索引）则能够让我们快速的定位到这个字的位置，从而找到我们要的结果。

索引的类型
1.主键索引 PRIMARY KEY

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。

PRIMARY KEY (`id`)
 

2.唯一索引 UNIQUE

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以在创建表的时候指定，也可以修改表结构。

UNIQUE KEY `num` (`number`) USING BTREE
 

3.普通索引 INDEX

这是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以修改表结构

KEY `num` (`number`) USING BTREE
 

4.组合索引 INDEX

索引分单列索引和组合索引(联合索引)。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

KEY `num` (`number`,`name`) USING BTREE
注意，组合索引前面索引必须要先使用，后面的索引才能使用。
 

5.全文索引 FULLTEXT

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

 

索引的存储结构
1.BTree索引
在前面的例子中我们看见有USING BTREE，这个是什么呢？这个就是MySQL所使用的索引方案，MySQL中普遍使用B+Tree做索引，也就是BTREE。

特点：

BTREE索引以B+树的结构存储数据
BTREE索引能够加快数据的查询速度
BTREE索引更适合进行行范围查找
 

使用的场景：

全值匹配的查询，例如根据订单号查询 order_sn='98764322119900'
联合索引时会遵循最左前缀匹配的原则,即最左优先
匹配列前缀查询，例如：order_sn like '9876%'
匹配范围值的查找，例如：order_sn > '98764322119900'
只访问索引的查询

2.哈希索引(面试中存在感较高)
Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。

特点：

Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；
Hash索引无法被利用来避免数据的排序操作；
Hash索引不能利用部分索引键查询；
Hash索引在任何时候都不能避免表扫描；
Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；
 

3.Full-text全文索引
Full-text索引也就是我们常说的全文索引，MySQL中仅有MyISAM和InnoDB存储引擎支持。

对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成Full-text索引时，会为文本生成一份单词的清单，在索引时根据这个单词的清单来索引。

注意：

    对于较大的数据集，把数据添加到一个没有Full-text索引的表，然后添加Full-text索引的速度比把数据添加到一个已经有Full-text索引的表快。
    针对较大的数据，生成全文索引非常的消耗时间和空间。
    5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本和之后InnoDB存储引擎开始支持全文索引。
    在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。
    在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节。

 

索引的使用
虽然索引能够为查找带来速度上的提升，但是也会对性能有一些损失。

    索引会增加写操作的成本
    太多的索引会增加查询优化器的选择时间
当创建索引带来的好处多过于消耗的时候，才是最优的选择~

 

使用索引的场景
    主键自动建立唯一索引；
    经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
    作为排序的列要建立索引；
    查询中与其他表关联的字段，外键关系建立索引
    高并发条件下倾向建立组合索引；
    用于聚合函数的列可以建立索引，例如使用count(number)时，number列就要建立索引

不使用索引的场景
    有大量重复的列不单独建立索引
    表记录太少不要建立索引，因为没有太大作用。
    不会作为查询的列不要建立索引

 

 

 

存储优化
MySQL中索引是在存储引擎层实现的，这里我们会讲解存储引擎。

执行查询引擎的命令show engines，可以看到MySQL支持的存储引擎结果

存储引擎介绍
1.InnoDB存储引擎
特点：

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。相比较MyISAM存储引擎，InnoDB写的处理效率差一点并且会占用更多的磁盘空间保留数据和索引。
提供了对数据库事务ACID（原子性Atomicity、一致性Consistency、隔离性Isolation、持久性Durability）的支持，实现了SQL标准的四种隔离级别。
设计目标就是处理大容量的数据库系统，MySQL运行时InnoDB会在内存中建立缓冲池，用于缓冲数据和索引。
执行“select count(*) from table”语句时需要扫描全表，因为使用innodb引擎的表不会保存表的具体行数，所以需要扫描整个表才能计算多少行。
InnoDB引擎是行锁，粒度更小，所以写操作不会锁定全表，在并发较高时，使用InnoDB会提升效率。即存在大量UPDATE/INSERT操作时，效率较高。
InnoDB清空数据量大的表时，是非常缓慢，这是因为InnoDB必须处理表中的每一行，根据InnoDB的事务设计原则，首先需要把“删除动作”写入“事务日志”，然后写入实际的表。所以，清空大表的时候，最好直接drop table然后重建。即InnoDB一行一行删除，不会重建表。
 

使用场景：

经常UPDETE/INSERT的表，使用处理多并发的写请求
支持事务，必选InnoDB。
可以从灾难中恢复（日志+事务回滚）
外键约束、列属性AUTO_INCREMENT支持
 

 

2.MyISAM存储引擎
特点：

MyISAM不支持事务，不支持外键，SELECT/INSERT为主的应用可以使用该引擎。
每个MyISAM在存储成3个文件，扩展名分别是：
　　1) frm：存储表定义（表结构等信息）

　　2) MYD(MYData)，存储数据

　　3) MYI(MYIndex)，存储索引

不同MyISAM表的索引文件和数据文件可以放置到不同的路径下。
MyISAM类型的表提供修复的工具，可以用CHECK TABLE语句来检查MyISAM表健康，并用REPAIR TABLE语句修复一个损坏的MyISAM表。
在MySQL5.6以前，只有MyISAM支持Full-text全文索引
 

使用场景：

经常SELECT/INSERT的表，插入不频繁，查询非常频繁
不支持事务
做很多count 的计算。

存储优化
1. 禁用索引
对于使用索引的表，插入记录时，MySQL会对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。为了解决这个问题，可以在批量插入数据之前禁用索引，数据插入完成后再开启索引。

禁用索引的语句： ALTER TABLE table_name DISABLE KEYS 开启索引语句： ALTER TABLE table_name ENABLE KEYS

MyISAM对于空表批量插入数据，则不需要进行操作，因为MyISAM引擎的表是在导入数据后才建立索引。

 
2. 禁用唯一性检查
唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。

禁用唯一性检查的语句：SET UNIQUE_CHECKS = 0; 开启唯一性检查的语句：SET UNIQUE_CHECKS = 1;

3. 禁用外键检查
插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提供插入速度。

禁用：SET foreign_key_checks = 0; 开启：SET foreign_key_checks = 1;

4.批量插入数据
插入数据时，可以使用一条INSERT语句插入一条数据，也可以插入多条数据。

5.禁止自动提交
插入数据之前执行禁止事务的自动提交，数据插入完成后再恢复，可以提高插入速度。

禁用：SET autocommit = 0; 开启：SET autocommit = 1;

 

 

 

数据库结构优化
优化表结构

1.尽量将表字段定义为NOT NULL约束，这时由于在MySQL中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂。
2.对于只包含特定类型的字段，可以使用enum、set 等数据类型。
3.数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型。例如IP地址可以使用int类型。
4.尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED。但对整数类型指定宽度，比如INT(11)，没有任何用，因为指定的类型标识范围已经确定。
5.VARCHAR的长度只分配真正需要的空间
6.尽量使用TIMESTAMP而非DATETIME，但TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且7.TIMESTAMP的值因时区不同而不同。
8.单表不要有太多字段，建议在20以内
9.合理的加入冗余字段可以提高查询速度。
 

 

表拆分

1.垂直拆分
垂直拆分按照字段进行拆分，其实就是把组成一行的多个列分开放到不同的表中，这些表具有不同的结构，拆分后的表具有更少的列。例如用户表中的一些字段可能经常访问，可以把这些字段放进一张表里。另外一些不经常使用的信息就可以放进另外一张表里。

插入的时候使用事务，也可以保证两表的数据一致。缺点也很明显，由于拆分出来的两张表存在一对一的关系，需要使用冗余字段，而且需要join操作。但是我们可以在使用的时候可以分别取两次，这样的来说既可以避免join操作，又可以提高效率。

 

2.水平拆分
水平拆分按照行进行拆分，常见的就是分库分表。以用户表为例，可以取用户ID，然后对ID取10的余数，将用户均匀的分配进这 0-9这10个表中。查找的时候也按照这种规则，又快又方便。

有些表业务关联比较强，那么可以使用按时间划分的。例如每天的数据量很大，需要每天新建一张表。这种业务类型就是需要高速插入，但是对于查询的效率不太关心。表越大，插入数据所需要索引维护的时间也就越长。