mysql优化-索引篇

MySQL优化总结

MySQL优化总结

MySQL索引

MySQL索引的建立对于MySQL的高效运行是很重要的。对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧下降。如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。

下面介绍几种常见的MySQL索引类型。

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

1、MySQL索引类型

(1) 主键索引 PRIMARY KEY

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

(2) 唯一索引 UNIQUE

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

ALTER TABLE table_name ADD UNIQUE (column)

(3) 普通索引 INDEX

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

ALTER TABLE table_name ADD INDEX index_name (column)

(4) 组合索引 INDEX

组合索引，即一个索引包含多个列。可以在创建表的时候指定，也可以修改表结构，如：

ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)

(5) 全文索引 FULLTEXT

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

可以在创建表的时候指定，也可以修改表结构，如：

ALTER TABLE table_name ADD FULLTEXT (column)

2、索引结构及原理

mysql中普遍使用B+Tree做索引，但在实现上又根据聚簇索引和非聚簇索引而不同

聚簇索引：

在Mysql的Innodb引擎中 每张表默认使用主键生成聚集索引，如果没有主键，Innodb会使用唯一索引替代，如果都没有，Innodb会隐式的生成一个不为空的唯一列。通过聚集索引生成B+树，在叶子节点存储数据（页----- 页是InnoDB存储引擎管理数据库的最小磁盘单位。），所以聚集索引生成的B+树存储了全部数据。

非聚簇索引：

也叫辅助索引，Innodb通过辅助索引生成的B+树并不会存储所有数据，而是只存储索引字段的数据和相关主键，如果要通过辅助索引查找非索引字段的话，会查找出主键，通过主键查找到相关字段，称之为回表。查询字段是索引字段当中的字段，称之为索引覆盖。

b+树介绍

下面这张b+树的图片在很多地方可以看到，之所以在这里也选取这张，是因为觉得这张图片可以很好的诠释索引的查找过程。

如上图，是一颗b+树。浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

真实的数据存在于叶子节点，即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

查找过程

在上图中，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

性质

(1) 索引字段要尽量的小。

通过上面b+树的查找过程，或者通过真实的数据存在于叶子节点这个事实可知，IO次数取决于b+数的高度h。

假设当前数据表的数据量为N，每个磁盘块的数据项的数量是m，则树高h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；

而m = 磁盘块的大小/数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的；如果数据项占的空间越小，数据项的数量m越多，树的高度h越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。

(2) 索引的最左匹配特性。

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

建索引的几大原则

(1) 最左前缀匹配原则

对于多列索引，总是从索引的最前面字段开始，接着往后，中间不能跳过。比如创建了多列索引(name,age,sex)，会先匹配name字段，再匹配age字段，再匹配sex字段的，中间不能跳过。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

一般，在创建多列索引时，where子句中使用最频繁的一列放在最左边。

看一个补符合最左前缀匹配原则和符合该原则的对比例子。

实例：表c2c_db.t_credit_detail建有索引(Flistid,Fbank_listid)

不符合最左前缀匹配原则的sql语句：

select * from t_credit_detail where Fbank_listid='201108010000199'\G

该sql直接用了第二个索引字段Fbank_listid，跳过了第一个索引字段Flistid，不符合最左前缀匹配原则。用explain命令查看sql语句的执行计划，如下图：

从上图可以看出，该sql未使用索引，是一个低效的全表扫描。

符合最左前缀匹配原则的sql语句：

select * from t_credit_detail where Flistid='2000000608201108010831508721' and Fbank_listid='201108010000199'\G

该sql先使用了索引的第一个字段Flistid，再使用索引的第二个字段Fbank_listid，中间没有跳过，符合最左前缀匹配原则。用explain命令查看sql语句的执行计划，如下图：

从上图可以看出，该sql使用了索引，仅扫描了一行。

对比可知，符合最左前缀匹配原则的sql语句比不符合该原则的sql语句效率有极大提高，从全表扫描上升到了常数扫描。

(2) 尽量选择区分度高的列作为索引。

比如，我们会选择学号做索引，而不会选择性别来做索引。

(3) =和in可以乱序

比如a = 1 and b = 2 and c = 3，建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

(4) 索引列不能参与计算，保持列“干净”

比如：Flistid+1>‘2000000608201108010831508721‘。原因很简单，假如索引列参与计算的话，那每次检索时，都会先将索引计算一次，再做比较，显然成本太大。

(5) 尽量的扩展索引，不要新建索引。

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

索引的不足

虽然索引可以提高查询效率，但索引也有自己的不足之处。

索引的额外开销：

(1) 空间：索引需要占用空间；

(2) 时间：查询索引需要时间；

(3) 维护：索引须要维护（数据变更时）；

不建议使用索引的情况：

(1) 数据量很小的表

(2) 空间紧张

慢查询

慢查询，顾名思义，就是查询超过指定时间 long_query_time 的 SQL 语句查询称为"慢查询"。 慢查询帮我们找到执行慢的 SQL，方便我们对这些 SQL 进行优化。

慢查询开启方法

long_query_time 是用来定义慢于多少秒的才算"慢查询"。查询 long_query_time 的值如下：

我们可以将其设置设置 long_query_time=2，如下。

开启慢查询的方法，一是可以通过在配置文件 my.cnf 或 my.ini 中设置配置参数，二是可以通过命令行设置变量来即时启动慢查询日志，个人比较喜欢第二种即时性的。由下图可知，记录慢查询日志已开启，slow_query_log=ON。

slow_query_log 是否打开记录慢查询日志

slow_query_log_file 日志存放位置

MySQLdumpslow命令

接下来看看慢查询日志的格式是怎么样。例如，在 MySQL 中运行 select sleep(3);

打开慢查询日志文件 MySQL-slow.log 的信息格式如下，说明这条 sql 语句执行用时 5.000183s，锁了 0s，查询返回 1 行，一共查了 0 行。

随着 MySQL 数据库服务器运行时间的增加，可能会有越来越多的 SQL 查询被记录到了慢查询日志文件中，这时要分析慢查询日志就显得不是很容易了。MySQL 提供的 MySQLdumpslow 命令，可以很好地解决这个问题。

MySQLdumpslow 的主要功能是统计不同慢 sql 的：

●执行次数（count）
●执行最长时间（time）
●累计总耗费时间（time）
●等待锁的时间（lock）
●发送给客户端的行总数（rows）
●扫描的行总数（rows）

进入 MySQL/bin 目录，输入 MySQLdumpslow -help 或--help 可以看到这个工具的参数。

-s，是表示按照何种方式排序，c、t、l、r 分别是按照执行次数、执行时间、等待锁时间、返回的记录数来排序，ac、at、al、ar 表示相应的平均值；

●-r，是前面排序的逆序；
●-t，是 top n 的意思，即为返回排序后前面多少条的数据；
●-g，后边可以写一个正则匹配模式，大小写不敏感的；

比如，执行./MySQLdumpslow -s c -t 5/data/zftMySQLData/MySQL-slow.log，得到执行次数最多的前 5 个查询，如下图所示。

执行./MySQLdumpslow -s r -t 10 /data/zftMySQLData/MySQL-slow.log，得到返回记录数最多的前 10 个查询。

使用 MySQLdumpslow 命令可以非常明确的得到各种我们需要的查询语句，对 MySQL 查询语句的监控、分析、优化是 MySQL 优化的第一步，也是非常重要的一步。

explain 分析查询

在分析查询性能时，EXPLAIN 关键字同样很管用。EXPLAIN 关键字一般放在 SELECT 查询语句的前面，使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。这可以帮助分析查询语句效率低下的原因或是表结构的性能瓶颈。通过 explain 命令可以得到：

– 表的读取顺序

– 数据读取操作的操作类型

– 哪些索引可以使用

– 哪些索引被实际使用

– 表之间的引用

– 每张表有多少行被优化器查询

Explain的用法

Explain tablename 或

Explain [EXTENDED] SELECT select_options

前者可以得出一个表的字段结构等等，后者主要是给出相关的一些索引信息，本文要讲述的重点是后者。

首先看看 explain 的输出参数：

这些参数中，各个参数的含义如下,

Id：本次 select 的标识符。在查询中每个 select 都有一个顺序的数值。

Select_type：select 类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。主要有这几种：

●SIMPLE：这个是简单的 sql 查询，不使用 UNION 或者子查询。
●PRIMARY：子查询中最外层的 select。
●UNION：UNION 中的第二个或后面的 SELECT 语句。
●DEPENDENT UNION：UNION 中的第二个或后面的 SELECT 语句，取决于外面的查询。
●UNION RESULT：UNION 的结果。
●SUBQUERY：子查询中的第一个 SELECT。
●DEPENDENT SUBQUERY：子查询中的第一个 SELECT，取决于外面的查询。
●DERIVED：派生表的 SELECT(FROM 子句的子查询)。

Table：输出行所引用的表。

Type：联合查询所使用的类型。

type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

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

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

possible_keys：指出 MySQL 能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验 WHERE 子句，看是否引用某些字段，或者检查字段不是适合索引。

Key：显示 MySQL 实际决定使用的键。如果没有索引被选择，键是 NULL。

key_len：显示 MySQL 决定使用的键长度。如果键是 NULL，长度就是 NULL。文档提示特别注意这个值可以得出一个多重主键里 MySQL 实际使用了哪一部分。

Ref：显示哪个字段或常数与 key 一起被使用。

Rows：这个数表示 MySQL 要遍历多少数据才能找到，在 innodb 上是不准确的。

Extra：如果是 Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。

如果是 where used，就是使用上了 where 限制。

如果是 impossible where 表示用不着 where，一般就是没查出来啥。

如果此信息显示 Using filesort 或者 Using temporary 的话会很吃力，WHERE 和 ORDER BY 的索引经常无法兼顾，如果按照 WHERE 来确定索引，那么在 ORDER BY 时，就必然会引起 Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。

现在我们再用 explain 来看看前面案例的 sql 执行情况。首先，先看看 t_file_count 的表结构如下，该表的索引是 FId。

未执行完的 sql 语句是 update c2c_zwdb.t_file_count set Fcount=Fcount 1 where FFileName='1001_招商银行 (1).txt' and Ftype=2

将其转换为 select 语句，select count(*) from c2c_zwdb.t_file_count where FFileName='1001_招商银行 (1).txt' and Ftype=2。执行explain命令如下：

由上图可见，type=all，key=NULL，该 sql 未使用索引，是一个效率非常低的全表扫描，在数据量很大的情况下，性能情况可想而知。

常用优化总结

优化语句很多，需要注意的也很多，针对平时的情况总结一下几点：

1、有索引但未被用到的情况（不建议）

(1) Like的参数以通配符开头时

尽量避免Like的参数以通配符开头，否则数据库引擎会放弃使用索引而进行全表扫描。

以通配符开头的sql语句，例如：select * from t_credit_detail where Flistid like '%0'\G

这是全表扫描，没有使用到索引，不建议使用。

不以通配符开头的sql语句，例如：select * from t_credit_detail where Flistid like '2%'\G

很明显，这使用到了索引，是有范围的查找了，比以通配符开头的sql语句效率提高不少。

(2) where条件不符合最左前缀原则时

例子已在最左前缀匹配原则的内容中有举例。

(3) 使用！= 或 <> 操作符时

尽量避免使用！= 或 <>操作符，否则数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。

select * from t_credit_detail where Flistid != '2000000608201108010831508721'\G\

(4) 索引列参与计算

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

select * from t_credit_detail where Flistid +1 > '2000000608201108010831508722'\G\

(5) 对字段进行null值判断

应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： 低效：select * from t_credit_detail where Flistid is null ;

可以在Flistid上设置默认值0，确保表中Flistid列没有null值，然后这样查询： 高效：select * from t_credit_detail where Flistid =0;

(6) 使用or来连接条件

应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： 低效：select * from t_credit_detail where Flistid = '2000000608201108010831508721' or Flistid = '10000200001';

可以用下面这样的查询代替上面的 or 查询： 高效：select from t_credit_detail where Flistid = '2000000608201108010831508721' union all select from t_credit_detail where Flistid = '10000200001';

2、避免select

在解析的过程中，会将'' 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

所以，应该养成一个需要什么就取什么的好习惯。

3、order by 语句优化

任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

方法：1.重写order by语句以使用索引；

4、GROUP BY语句优化

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉

低效:

SELECT JOB , AVG(SAL)

FROM EMP

GROUP by JOB

HAVING JOB = ‘PRESIDENT'

OR JOB = ‘MANAGER'

高效:

SELECT JOB , AVG(SAL)

FROM EMP

WHERE JOB = ‘PRESIDENT'

OR JOB = ‘MANAGER'

GROUP by JOB

5、用 exists 代替 in

很多时候用 exists 代替 in 是一个好的选择： select num from a where num in(select num from b) 用下面的语句替换： select num from a where exists(select 1 from b where num=a.num)

6、使用 varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

7、能用DISTINCT的就不用GROUP BY

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

可改为：

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

8、能用UNION ALL就不要用UNION

UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源。

9、在Join表的时候使用相当类型的例，并将其索引

如果应用程序有很多JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。

而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）