mysql优化：索引+SQL性能检测

MySQL优化

1. 索引概述

1.1 索引介绍

索引是帮助MySQL高效获取数据的数据结构（有序）。

在没有建立索引时，查询语句是全盘扫描，性能较低。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序

1.2 索引的优劣势

优势

1、索引能够提高数据检索的效率，降低数据库的IO成本。

2、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性，创建唯一索引

3、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间

4、加速两个表之间的连接，一般是在外键上创建索引

劣势

1、需要占用物理空间,建立的索引越多需要的空间越大

2、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

1.3 索引结构

1.3.1 BTREE结构

BTree又叫多路平衡搜索树，一颗m叉的BTree特性：

• 树种每个节点最多包含m个孩子。

• 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。

• 若根节点不是叶子节点，则至少有两个孩子

• 每个非叶子节点由n个key与n+1个指针组成

  例如5叉Btree：每个节点最多只能有5-1=4个元素，如果有了4个元素，再插入时会把中间那个元素分裂出去作为key

1.3.2 B+TREE结构

B+TRee为BTree的变种，两种区别为：

1. B+Tree的叶子节点保存所有的可以信息，依key大小顺序排列

2. B+Tree叶子节点元素维护了一个单向链表

3. 所有的非叶子节点都可以看作是key的索引部分

由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率跟家稳定

1.3.3 MySQL中的B+Tree

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示

1.4 索引的分类

1. 单值索引：即一个索引只包含单个列，一个表中可以有多个单列索引
2. 唯一索引：索引列的值必须唯一，单可以是空值
3. 复合索引：即一个索引包含多个列

1.5 索引的语法

索引在创建表的时候，可以同时创建，也可以随时增加新的索引

查看索引

show index from 表名;

创建索引

create index 索引名 on 表名(添加索引的字段名1,添加索引的字段名1,添加索引的字段名1...);   

索引名命名规范：唯一性索引用uni_开头，后面跟表名和字段名。一般性索引用ind_开头，后面跟表名和字段名。 如：uni_student_id_… ind_studnet_name_…

添加的索引字段只有1个就是单列索引，多个就是复合索引，复合索引遵循最左前缀原则（后面3. 避免索引失效有讲）

删除索引

drop index 索引名 on 索引所在的表名;

alter命令

1.6索引的设计原则

1. 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2. 为经常需要排序、分组和联合操作的字段建立索引

经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3. 为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

注意：常查询条件的字段不一定是所要选择的列，换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。

4. 限制索引的数目

索引的数目不是“越多越好”。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。在修改表的内容时，索引必须进行更新，有时还可能需要重构。因此，索引越多，更新表的时间就越长。

如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最佳索引。

5. 尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个 CHAR(100) 类型的字段进行全文检索需要的时间肯定要比对 CHAR(10) 类型的字段需要的时间要多。

6. 数据量小的表最好不要使用索引

由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

7. 尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

8. 删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

2. SQL性能检测

2.1 查看SQL执行频率

show status like 'Com_______';

注意：有7个下划线；

show status like 'innodb_rows_%';

这两种方式都可以查看当前数据库增删改查语句使用的频率。就可以判断当前数据库是以插入为主还是以查询为主

2.2 Explain分析执行计划

在所有查询语句前加上explain，查询的就不是数据，而是这条数据的执行计划

explain select * from student;

2.2.1 id（参考意义不大）

不是代表主键，而是代表表结构的执行顺序，如果有多表查询就会有多行记录，如果：

1. id值相同，加载表的顺序就是从上到下加载；
2. id值不同，加载表的顺序是先加载值大的；
3. 如果id值有相同，有不同，就把id值相同的看作一组，id值越大的一组优先级加载，并按照，从上到下加载

2.2.2 select_type（参考意义不大）

表示select的类型，主要有

(1) SIMPLE(简单SELECT，不使用UNION或子查询等)

(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

2.2.3 table

表示哪一张表

2.2.4 type（重要的指标）

表示访问类型

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

一般来说，我们需要保证查询至少达到range级别，最好达到ref。

2.2.5 possible_keys（较重要的指标）

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

2.2.6 Key（重要的指标）

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

2.2.7 key_len（重要的指标）

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好

2.2.8 ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

2.2.9 rows（重要的指标）

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

2.2.10 Extra

该列包含MySQL解决查询的详细信息

2.3 show profile分析SQL

MySQL从5.0.37版本开始增加对它的支持

查看当前mysql是否支持（yes为支持）

select @@have_profiling;

查看是否开启(0：关闭，默认 1：开启)

 select @@profiling;

开启profiling

set profiling=1;

写几条查询语句

然后执行

show profiles;

这样就可以看到每条SQL语句执行所需的时间，可以判断出执行的效率；

2.4 慢查询日志

MySQL 慢查询日志是排查问题 SQL 语句，以及检查当前 MySQL 性能的一个重要功能。

show variables like 'slow_query%';

show variables like 'long_query_time';

说明：

slow_query_log :慢查询开启状态
slow_query_log_file :慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
long_query_time :查询超过多少秒才记录

配置

默认没有开启慢查询日志记录，通过命令临时开启：

set global slow_query_log='ON';

set global slow_query_log_file='存放的位置';

set global long_query_time=时间（秒）;

如果查询语句所用时间大于所设定的时间，就会写入文件

3. 避免索引失效

3.1 复合索引遵循最左前缀原则

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。

create index ind_username_password_age on user(username,password,age);   //先创建一个联合索引

然后使用explain查询,发现使用了索引,并且索引的长度也是随查询索引的字段减少而减少

explain select * from user where username="asdf" and password="aa" and age=18;
explain select * from user where username="asdf" and password="aa";
explain select * from user where username="asdf";

1. 没有包含索引第一列，会发现没有用索引

explain select * from user where password="aa" and age=18;
explain select * from user where age=18;

注意：如果还有用到索引，可能是你添加了主键约束，删除主键约束就行了

在查询当中包含（顺序不一定要在最左边）索引最左边的列，索引会生效，如果不包含，索引就会失效，如果想让后面的列走索引，那就再建两个索引

2. 跨列查询

explain select * from user where username="asdf" and age=18;

如果跳过一个列，就只有左边的列有索引，右边的列就没有索引

3. 用>,< 的范围查询（不包含>=,<=），右边的列也是没有索引的

4. 在索引列上进行运算操作，索引也会失效

5. 字符串的字段不加单引号，该字段的索引也会失效

6. 尽量覆盖索引（查询的字段在索引中已经覆盖），不适用select * 查询，要写出具体字段，select * 会进行回表查询

7. 使用or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引到不会被用到

8. 以%开头的like模糊查询，索引会失效

9. MySQL内部评估，如果用索引比全表扫描更慢，就不会使用索引（如：is null和is not null，in和not in）

10. 尽量使用复合索引，少使用单列索引（因为复合索引可以一下创建多个索引如：字段1，字段1+字段2，字段1+字段2+…）

常见SQL优化

4.1优化insert语句

1. 使用多行插入，少使用单行插入
2. 手动开启，提交事务
3. 主键有序插入

4.2 优化order by语句

1. 使用覆盖索引
2. 尽量同使用升序或降序，少使用一个升序一个降序

4.3 优化group by语句

1. 分组查询会默认进行排序，如果没必要可以关闭，在语句后面加order by null;

4.4 优化嵌套（子）查询

1. 少使用子查询，多实用连接查询

4.5 优化分页查询

select * from stu limit 100000,10;

​ 如上查询语句：使用limit分页查询时,是先查询100000+10条的全部数据，然后抛弃100000条数据只显示10条数据

1. 当数据量大时可以先查询所需显示数据的id，然后根据id用子查询查询查询出这10条数据的全部内容。如：

select * from stu s,(select id from stu limit 100000,10) t where s.id=t.id;

2. 用判断(弊端：1必须是主键自增长。2不能有断层。)知道就好不常用

select * from stu wehre id>100000 limit 10;