mysql杂记漫谈[亲测有效]

　　Hello，大家好，我是烤鸭，这几天消失了一下，主要是线上系统出了点小bug和sql性能问题，在努力搬砖，就把之前的设计模式系列放了一下下，正好趁这个复习巩固了一下sql执行计划和sql优化等相关的东西，本篇文章我主要用来学习mysql的执行计划和索引分类，也和大家分享下吧，也请大神们不吝赐教。

　　先来熟悉一下索引吧。索引是在存储引擎中实现的，不同的存储引擎可能会使用不同索引，Myisam和InnoDB存储引擎只能支持BTREE索引，不能更换，而MEMORY/HEAP存储引擎支持HASH和BTREE索引；

　　常用的索引我们分为三大类：包括单列索引（普通索引、唯一索引、主键索引等）、组合索引、全文索引等；

　　1、单列索引：一个索引只包含一列，但是一张表中可以有多个单列索引；

　　　　1.1普通索引，没有什么限制，允许在定义索引列中插入空值和重复值，纯粹是为了查询数据更快点；

　　　　1.2唯一索引，索引列中的值必须是唯一的，允许空值；

　　　　1.3主键索引，是一种特殊的唯一索引，不允许有空值；

　　2、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用这些字段的左边字段时，索引才会被使用，使用组合索引时候遵循左前缀集合的原则，例如有id、name、age这3个字段构成的组合索引，索引行中就按照id/name/age的顺序存放，索引可以索引下面字段组合（id/name/age）、（id/name）、（id/age）、id，如果要查询的字段构不成索引的最左前缀，那么是不会使用索引的，如（name/age）、age组合就不会使用索引；

　　3、全文索引(fulltext索引)：mysql5.6之前的版本，只有在Myisam存储引擎上使用，mysql5.6之后的版本innodb和myisam存储引擎均支持全文索引，并且只能在char、varchar、text类型的字段上才能使用全文索引；全文索引主要用来查找文本中的关键字，而不是直接与索引中的值比较，更像是一个搜索引擎，而不是简单地where语句参数的匹配，在数据量较大的时候，先将数据写入到一张没有全文索引表中，再创建fulltext索引的速度，要比先为一张表建立fulltext索引，再将数据写入的速度快很多。

　　覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行（它包括在查询里的Select、Join和Where子句用到的所有列）。索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此不必读取整个数据行，索引的叶子节点存储了它们的索引数据，当能通过读取索引就可以得到所需要的数据，那就不需要读取数据行了，一个索引包含了（或者覆盖率）满足查询结果的数据就叫做覆盖索引。

　　索引的创建和简单用法参见：

　　那么我们怎么知道是否使用了覆盖索引呢？如果使用了覆盖索引，在Extra字段会输出Using index字段，那么我们就可以知道当前查询使用了覆盖索引。

　　下面开始学习sql执行计划啦！！！

　　我使用的mysql版本是5.6.16-log，建表语句如下：　　

CREATE TABLE `actor` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `film` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO actor (id,name,update_time) VALUES
     (1,"郭德纲",NULL),
     (2,"周润发",NULL),
     (3,"刘华强",NULL);

INSERT INTO film (id,name) VALUES
     (4,"九层妖塔"),
     (2,"建国大业"),
     (3,"烈火英雄"),
     (1,"百团大战");

INSERT INTO film_actor (id,film_id,actor_id,remark) VALUES
     (1,1,1,NULL),
     (2,2,1,NULL),
     (3,2,2,NULL),
     (4,4,3,NULL),
     (5,3,3,NULL);

　　mysql执行计划的定义：通过explain关键字来模拟sql优化器执行sql语句，从而分析sql的执行过程；

　　sql语句执行过程：

　　　　1、客户端发送一条sql查询请求给数据库服务器；

　　　　2、数据库服务器检查缓存，如果命中，直接返回存储在缓存中的结果，否则进入下一阶段；

　　　　3、服务器进行sql解析、预处理、再由优化器生成对应的执行计划；

　　　　4、服务器在根据生成的执行计划，调用存储引擎的API来执行查询；

　　　　5、将结果返回给客户端，同时缓存查询结果；

　　explain中的列：

　　　1、id(查询执行顺序)

　　　　id值相同时从上向下执行；id值相同被视为一组；id值越大优先级越高，最先执行，id值为Null则最后执行，示例如下；

　　2、select_type

　　　　simple：表示查询中不包含子查询或者union，示例如下;

　　　　primary：当查询中包含任何复杂的子部分，最外层的查询会被标记为primary，示例如下;

　　　　derived：在from的列表中包含的子查询会被标记为derived，示例如下;

　　　　subquery：在select或者where列表中包含了子查询，则子查询中被标记成了subquery，示例如下;

　　　　union:：两个select查询时前一个标记为primary,后一个标记为union。union出现在from从句子查询中，外层的select会被标记为primary，union中的第一个查询为derived, 第二个子查询标记为union;

　　　　unionresult:从union表获取结果的select被标记成union result，示例如下;

　　3、table

　　　　显示这行数据是关于哪张表的，当from子句中有子查询时，table用<derivedN>表示，按照N的序号从大到小执行；当有union时，select_type类型为union result，table列的值为<union1,2>，1、2表示参与union的select的行id;

　　4、type(重要)

　　　　显示连接使用了何种类型，从最好到最差的连接类型为system>constant>eq_ref>ref>range>index>all，查询一般的保证达到range级别，最好达到ref;

　　　　a、null：mysql能够在优化阶段分解查询语句，在执行阶段不用再访问表或索引，例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表;

　　　　b、system：表中只有一行数据。属于const的特例，如果物理表中就一行数据则为All;

　　　　c、const：查询结果最多有一个匹配行，可以被视为常量，只读一次查询速度非常快，一般情况下把主键或者唯一索引作为唯一查询条件的查询的情况都是const，mysql优化器就会将该查询转换成一个常量;

　　　　d、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引，常见于联合查询，读取主表和关联表中的每一行组成新的一行，当连接部分使用索引的时候，索引是主键索引或者非空唯一索引的时候会使用此种连接类型，eq_ref可用于=运算比较的索引列，比较值可以是常量或使用此表之前读取的表中的列的表达式；

　　　　e、ref：不使用唯一性索引扫描，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值比较，可能会匹配到多个符合条件的行；

　　　　　　1、简单select语句，假设某个表中存在一列name，name是普通索引（非唯一索引）；

　　　　　　2、关联表查询，采用了联合索引的左边前缀部分；

　　　　f、range：扫描部分索引、索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询；

　　　　g、index：扫描索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树的根节点开始快速查找，而是直接对二级索引的叶子节点进行遍历和扫描，速度还是比较慢的，这种查询一般使用覆盖索引，二级索引一般比较小，通常比ALL快一点；

　　　　h、all：即全表扫描，扫描聚簇索引的所有叶子结点，通常情况就需要增加索引来优化了；

　　　　index merge：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并; 

　　5、possible_keys

　　　　a、查询条件字段涉及到的索引，可能没有使用；

　　　　b、explain时可能出现possible_keys列有值，而key没有值的情况，这种情况是因为表中的数据不多，mysql认为索引对此查询帮助不大，选择了全表查询；

　　　　c、如果该列是null，则没有相关索引。在这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后查看explain查看效果；

　　6、key

　　　　实际使用的索引，如果为null，则没有使用索引；如果想强制mysql使用或者忽略possible_key是列中的所列，在查询中使用force index、ignore index;

　　7、key_len

　　　　表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好，key_len是根据表定义计算而得出的，不是通过表内检索出的。

　　　　例如：explain select * from film_actor where film_id = 2;

　　　　索引长度的计算规则如下表：

 　　　　　从这个我们也可以看出varchar(n)保存的字符串的最大长度是65535；

　　8、ref：显示索引的哪一列被使用了，如果可能的话，是一个常量const;

　　9、rows：表统计信息，大致估算找出所需的记录所需要读取的行数，注意这个不是结果集里的行数。

　　10、Extra常见类型：

　　　　Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql无法利用索引完成的排序称为“文件排序”；

　　　　Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于order by和group by；

　　　　Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率还可以，如果同时出现了Using Where,表示索引被用来执行索引键值的查找；如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作，覆盖索引的含义是所查询的列是和建立的索引字段和个数是一一对应的；

　　　　Using where: 表示使用了where过滤；

　　　　Using join buffer：表明使用了连接缓存，如果在查询的时候有多次join，则可能会产生临时表；

　　　　impossible where：表示where子句后面的条件总是false，不能用来获取任何元素；

　　　　distinct：优化distinct操作，mysql一旦找到了第一个匹配的行之后就不再进行搜索了；

　　常用的SQL使用注意事项和一些建议：

　　　　1、尽量不要使用select *，而是使用具体的字段，避免了不需要的列返回给客户端调用，节约流量，可能会用到覆盖索引，直接从索引中获取要查询的列数据，减少了回表查询，调高查询效率；

　　　　2、避免在where子句中使用OR来进行条件关联，有可能造成索引失效，示例如下；

　　　　第一中写法：

　　　　aac表中code列上面建立了索引，order_type列上没有索引，where查询条件后面采用了or连接过滤条件，导致了全表扫描，可以将以上面写法改成下面：　　

　　　　第二种写法：

 　　　　　对于以上两种写法：第一中写法，假设先走了code索引，但是order_type没有索引还得来一遍全表扫描；而第二种写法是分为三个步骤的：全表扫描+索引扫描+结果合并，直接一次全表扫描就行了。　　

　　　　3、尽量使用数值类型代替字符串；处理引擎在执行查询和连接时候，如果是字符串类型则会逐个比较字符，要是数值类型的话直接比较一次就可以了，字符串的连接性能也会大大降低。

　　　　4、使用varchar替代char，varchar的存储是按实际长度来存储的，可以节省存储空间，而char是按照定义长度来存储的，不足补充空格；

　　　　5、应尽量避免在where子句中使用!=或<>操作符，这种情况可能会造成索引失效，经过sql优化器优化，执行引擎发现使用索引的代价比不走索引还要大，就会放弃使用索引直接走全表扫描；

 　　　 6、在inner join 、left join、right join都满足条件的状况下，优先使用inner join;inner join内连接，只保留左右两张表中都匹配的结果集；left join 左连接，以左表为主表，返回左表中的所有行，即使右表中没有匹配的行；right join右连接，以右表为主表，返回右表中的所有数据，即使坐标中没有匹配的行；如果是inner join等值连接，返回的行数比较小，所以效率较高；左右连接的话，按照“小表驱动大表的原则”，用小表作为主表；

　　　　7、按照上一条“小表驱动大表”的原则，在含有复杂子查询的sql语句中，在满足条件的情况下，应该将小表放在里面层层过滤，缩小查询的范围；

　　　　8、分组过滤的时候，应该先过滤，再分组，调高效率；

　　　　9、执行delete或update语句，加个limit或者循环分批次删除，降低误删数据的代价，避免长事务，数据量大的话，容易把cpu打满，一次性删除数据太多的话可能造成锁表；

　　　　10、union会对筛选掉重复的记录，所以会在连接后对所产生的结果集先进行排序运算，然后在删除重复记录返回，如果数据量比较大的情况下可能会使用磁盘排序，在union all也满足条件的情况下，可以用union all替代union;

　　　　11、多条写数据，建议采用批量提交减少事务提交的次数，提高性能；

　　　　12、关联查询的表连接不要太多，关联表的个数越多，编译的时间和开销也越大，每次关联在内存中都会产生一个临时表；

　　　　13、索引并不是越多越好，索引虽然提高了查询性能，但是会降低数据写入的速度，并且索引的存储是要占用空间的，索引也是排序的，排序是要花费时间的，insert和update操作可能会导致重建索引，如果数据量巨大，这笔消耗也是非常惊人的；

　　　　14、查询时候在索引列上使用数据库的内置函数会导致索引失效；

　　　　15、创建联合索引，进行查询的时候，必须满足最左原则；

　　　　16、优化like查询，进行模糊查询的时候，应当使用右模糊查询，全模糊和左模糊查询会导致索引失效；

　　　　17、where后面的字段，留意其数据类型的隐式转换，查询条件类型和数据库列字段不匹配的话，可能会造成数据类型的隐式转换，造成索引失效；

　　　　18、索引不适合建在有大量重复数据的字段上，比如性别，排序字段应创建索引，索引列的数据应该最够的“散列”，这样查询效果可能会更好；

　　　　19、去重distinct过滤字段要少，数据库引擎对数据的比较、过滤是一个很耗费资源的操作；

　　　　20、尽量避使用游标；

　　　　21、表设计时候增加必要的注释，说明字段的用途。

　　好了，暂时先给本篇文章画个句号，以后等想起来啥再慢慢补充吧，欢迎大神们批评指正，希望大神们不吝赐教，共同学习进步！