java架构学习——21. MySQL优化2

本篇博文主要包含：

• 索引的实现原理（B+树）
  -主键索引
  -全文索引（一般使用第三方框架）
  -普通索引
  -唯一索引
• 索引的创建与删除
• 索引的优缺点
• 适合创建索引的列
• MySQL explain执行计划解读
• SQL优化技巧
• MySQL数据引擎

一、索引
索引用来快速地寻找那些具有特定值的记录，所有MySQL索引的实现通常使用 B 树及其变种 B+ 树。树的形式保存，采用折半查询。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

1. 索引的分类
   1.1 主键索引
   主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。

   1.2 全文索引
   将非结构化数据中的一部分信息提取出来，重新组织，使其变得有一定结构，然后对此有一定结构的数据进行搜索，从而达到搜索相对较快的目的。全文索引在企业中一般不使用，通常使用第三方框架如solr、es。
   设置条件:
   a. 表的存储引擎是MyISAM，默认存储引擎InnoDB不支持全文索引（新版本MYSQL5.6的InnoDB支持全文索引）
   b. 字段类型：char、varchar和text

   配置全文索引查询关键词最小长度限制:
   my.ini配置文件中添加:ft_min_word_len = 10
   保存后重启MYSQL，执行SQL语句
   查看ft_min_word_len是否设置成功:show VARIABLES like 'ft_min_word_len'

   1.3 普通索引
   普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDERBY column）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

   1.4 唯一索引
   这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。
   unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复，但是不能存有重复的空字符串’。

2. 创建索引
   2.1 MySQL在创建数据表的时候创建索引
   基本的语法格式如下：

CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
                  [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
                  [索引名](字段名1 [(长度)] [ASC | DESC])
)engine=[引擎类型] charset utf8;

• UNIQUE：可选。表示索引为唯一性索引。

• FULLTEXT；可选。表示索引为全文索引。

• SPATIAL：可选。表示索引为空间索引。

• INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。

• 索引名：可选。给创建的索引取一个新名称。

• 字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。

• 长度：可选。指索引的长度，必须是字符串类型才可以使用。

• ASC：可选。表示升序排列。

• DESC：可选。表示降序排列。

  2.2 在已有表中创建索引
  使用 alter table的方式：

alter 表名 索引名 add [UNIQUE | FULLTEXT | SPATIAL] index index_name (字段名[(长度)]);

或
create index 添加索引：

create [UNIQUE | FULLTEXT | SPATIAL] index 索引名 On 表名(字段名[(长度)] );

3. 删除索引
   直接使用 drop index(注意：没有 drop fulltext index 这种用法)：

 DROP INDEX 索引名 ON 表名 ;

或
使用 alter table的方式：

 ALTER TABLE 表名 DROP INDEX 索引名;

4. 查询索引信息

show index from 表名
show keys from 表名

实例：
a. 创建全文索引

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;

INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...'),
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');

查询语句：

select * from articles where match(title,body) against ( 'database')

说明:
在mysql中fulltext 索引只针对 myisam生效
mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文
使用方法是 match(字段名…) against(‘关键字’)
全文索引：停止词, 因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.比如（a，b，mysql，the）
查询匹配度：

select match(title,body) against ('database') from articles;（输出的是每行和database的匹配度）

b. 创建多列普通索引

create index title_body_index On  articles(title,body);

二、索引的实现原理
数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B 树及其变种 B+ 树。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

上图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在 O(log2n)的复杂度内获取到相应数据。

1. 创建索引的优点：
   第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
   第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
   第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
   第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
   第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

2. 创建索引的缺点：
   第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
   第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
   第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

3. 不应该创建索引的的这些列具有下列特点：
   第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
   第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
   第三，对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
   第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

4. 那些列上适合添加索引
   查询作为查询条件字段应该创建索引
   唯一性太差的字段不适合单独创建索引,即使频繁
   Select * from emp where sex=’男’
   频繁更新字段，也不要定义索引。
   不会出现在where语句的字段不要创建索引

总结:满处一下条件的字段，才应该创建索引
①肯定在where条件经常使用
②该字段的内容不是唯一的几个值
③字段内容不是频繁变化

三、MySQL explain执行计划解读
MySql提供了EXPLAIN语法用来进行查询分析查询要涉及多少行、使用哪些索引、运行时间，在SQL语句前加一个"EXPLAIN"即可。
SQL语句：
explain select * from table where table.id = 1

运行上面的sql语句后你会看到，下面的表头信息：

table | type | possible_keys | key | key_len | ref | rows | Extra

EXPLAIN列的解释
table
显示这一行的数据是关于哪张表的
type
这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
说明：不同连接类型的解释（按照效率高低的顺序排序）
system：表只有一行：system表。这是const连接类型的特殊情况。
const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待。
eq_ref：在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。
ref：这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。
range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。
index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。
ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。
possible_keys
显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key
实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len
使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数
rows
MYSQL认为必须检查的用来返回请求数据的行数
Extra
关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
说明：extra列返回的描述的意义
Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。
Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
Range checked for each Record（index map:#） ：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。
Using filesort ：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
Using index ：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
Using temporary ：看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。
Where used ：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

四、SQL优化技巧

1. 使用group by 分组查询是，默认分组后，还会排序，可能会降低速度，
   在group by 后面增加 order by null 就可以防止排序.
   explain select * from emp group by deptno order by null;

2. 使用组合索引
   组合索引中未使用组合索引中第一个索引字段时，不会使用组合索引查找,而是使用全表扫描。

3. 条件中使用like
   使用like时，如果使用%开头不会使用索引。如：like ‘%aaa’。

4. 使用or时，只要有一个条件不加索引，不会使用索引。

5. 判断是否为null，使用=null时不会使用索引，而is null 会使用索引。

6. 使用in和not in时不会使用索引。

7. 用 > 代替 >= ,因为 >= 要判断两次。

8. 查询量非常大，使用缓存、分表、分页。

五、MySQL数据引擎
使用的存储引擎 myisam / innodb/ memory
myisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表，回复表.
INNODB 存储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.
MyISAM 和 INNODB的区别

1. 事务安全（MyISAM不支持事务，INNODB支持事务）
2. 查询和添加速度（MyISAM批量插入速度快）
3. 支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）
4. 锁机制（MyISAM时表锁，innodb是行锁）
5. 外键 MyISAM 不支持外键， INNODB支持外键. (在PHP开发中，通常不设置外键，通常是在程序中保证数据的一致)
   Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快. （如果mysql重启的话，数据就不存在了）
   
   Myisam注意事项
   如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理
   举例说明:
   create table test100(id int unsigned ,name varchar(32))engine=myisam;
   insert into test100 values(1,’aaaaa’);
   insert into test100 values(2,’bbbb’);
   insert into test100 values(3,’ccccc’);
   insert into test100 select id,name from test100;
   我们应该定义对myisam进行整理
   optimize table test100;