mysql 索引和sql优化

• 1、引擎介绍（innodb、MyISAM）
• 2、索引数据结构
• 3、索引类型
• 4、使用索引注意事项
• 5、索引排序
• 6、sql优化
• 7、mysql分表&分区
• 8、查询优化器

 

1、引擎介绍（innodb、MyISAM）

MySQL5.5以后默认使用InnoDB存储引擎，早期的Mysql版本默认的引擎是MyISAM

mysia------

mysiam 表是独立于操作系统的（可以直接拷贝数据文件移植）、它不支持事务，也不支持外键，访问速度快

mysiam 索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引

myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索 索引，然后通过索引找到磁盘相应数据

key_buffer 大小设置？

myisam中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录

在myisam中，主索引(Primary key)和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复

Primary key是否是主键？

InnoDB-----

表数据文件本身就是按B+Tree组织的一个索引结构(聚簇索引)

InnoDB一定会建立聚簇索引，把实际数据行和相关的键值保存在一块

1)  InnoDB通常根据主键值(primary key)进行聚簇

2) 如果没有创建主键，则会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引

        3) 上面二个条件都不满足，InnoDB会自己创建一个虚拟的聚集索引

InnoDB辅助索引叶子节点存储的是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据叶，再通过数据叶中的Page Directory找到数据行。

InnoDB类型支持事务，支持高并发，外键。

写操作innodb效率高于mysiam，在执行数据库写入的操作（insert,update,delete）的时候，mysiam表锁表，而innodb表锁行。所以并发性能上mysiam处理能力差、写操作innodb效率高于mysia

------

MyISAM适合：以查询为主、更新写入相对较少、业务数据完整性要求不高

InnoDB适合：大量的写入、更新，数据完整性要求比较高

2、索引数据结构

     名词解释：

• 聚簇索引：聚簇索引的顺序就是数据的物理存储顺序，索引的叶节点就是数据节点。所以一个表最多只能有一个聚簇索。（InnoDB支持、myisam不支持。myisam索引文件跟数据文件分开）
• 非聚簇索引：索引顺序与数据物理排列顺序无关 ，索引的叶节点仍然是索引节点（MyISM使用的都是非聚簇索引，MyISM存放数据地址、InnoDB存放主键值）
• 
• 

• MySQL目前主要有以下几种索引类型：B+Tree，HASH，FULLTEXT，RTREE

• MySQL 5.6.4里才添加了InnoDB引擎的Full-Text索引支持。

• myisam和innodb采用B+tree作为索引结构的

• B+Tree，HASH索引

  Hash 索引（适应于memory引擎）是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中；（索引键的hash值有可能相同）

• HASH检索效率非常高，索引的检索可以一次定位
• Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。
• Hash 索引无法被用来避免数据的排序操作。由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算
• Hash 索引不能避免表扫描（由于不同索引键存在相同 Hash 值，需要访问表中的实际数据进行相应的比较）
• Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高

• B+Tree

• B+Tree索引需要从根节点到枝节点，最后才能访问到叶节点
• B+Tree索引，使用大于，小于，BETWEEN，不等于，LIKE等操作符的时候都可以用。对索引字段进行范围查询的时候，只有BTREE索引可以通过索引访问。HASH索引实际上是全表扫描的。
• MyISAM的B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址、主索引与辅助索引区别并不大，只是主键索引不能有重复的关键字
• InnoDB的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引。非聚簇索引，叶子节点上的data是主键

• B+Tree插入

  example：这棵b+树的阶为3，则最多三个关键字，最多三个子树，最少2个关键字，最少两个子树。非叶子节点的第一个关键字是【上边界】  

  a)分别插入关键字 10、20、30，此时根节点为叶节点，同时没有满足分裂条件，故放到同一个叶节点里面

  

   

   

  b)插入关键字7，这时候根节点的关键字数量大于m（m=3），所以需要分裂成为两个子节点（叶节点）

  

  c)分别插入关键字8、25，叶子节点被填满，没有符合分裂条件，不需要分裂

  

  d)插入关键字6，注意，6是少于所有关键字的，6将放到最左边的叶子节点里面，并且递归设置每一个父亲的第一位数目（则该路径上第一个关键字都改6），同时，叶子节点关键字大于m（m=3），需要分裂

  

   

  3、索引类型

  一、普通索引：这是最基本的索引，它没有任何限制

  二、唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值

  三、主键索引：它是一种特殊的唯一索引，不允许有空值

  四、组合索引：使用时要满足最左前缀

   

  4、使用索引注意事项

  1.索引不会包含有NULL值的列

  只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

  2.使用短索引

  对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

  4.like语句操作

  一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。like查询是以%开头不会使用索引

  5.不要在列上进行运算

   select * from users where YEAR(adddate)<2007;

  7.组合索引必须满足最左前缀

  8.存储引擎不能使用索引中范围条件右边的列。

  例如，如果你的查询语句为WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE是范围查询。

  9.如果条件中有or(不同字段 a=‘’ or b='')，即使其中有条件带索引也不会使用

   

  5、索引排序

  MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。

  
  MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。

  
  通过索引优化来实现MySQL的ORDER BY语句优化：

  
  1、ORDER BY的索引优化。如果一个SQL语句形如：
  SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
  在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。

  
  2、WHERE + ORDER BY的索引优化，形如：
  SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
  建立一个联合索引(columnX,sort)来实现order by 优化。

  
  注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化
  SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

  查询优化器优化过 也可以使用

  
  3、WHERE+ 多个字段ORDER BY
  SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
  建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。

  
  MySQL Order By不能使用索引来优化排序的情况
  * 对不同的索引键做 ORDER BY ：(key1,key2分别建立索引)
  SELECT * FROM t1 ORDER BY key1, key2;

  
  * 在非连续的索引键部分上做 ORDER BY：(key_part1,key_part2建立联合索引;key2建立索引)
  SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

  
  * 同时使用了 ASC 和 DESC：(key_part1,key_part2建立联合索引)
  SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

  
  * 用于搜索记录的索引键和做 ORDER BY 的不是同一个：(key1,key2分别建立索引)
  SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

  
  * 如果在WHERE和ORDER BY的栏位上应用表达式(函数)时，则无法利用索引来实现order by的优化
  SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

  
  特别提示:
  1>mysql一次查询只能使用一个索引。如果要对多个字段使用索引，建立复合索引。

   

  7、sql优化

  count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）.

  在MyISAM存储引擎中，count(*)函数是直接读取数据表保存的行记录数并返回，而当添加有WHERE限定语句的时候Mysql需要对整个表进行检索从而得出count的数值

  count(*) 优化器自动选择效率最高的索引，innodb 辅助索引比聚簇索引查询效率高。

  内连接、外链接效率说法不一根据情况自行选择。
   select sql_no_cache t.*,ad.id from task t left join task_ad ad on t.ad_id = ad.id （高）
   select sql_no_cache t.*,ad.id from task t,task_ad ad where t.ad_id = ad.id （低）

   

  in和exists
  in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。子查询表大的用exists，子查询表小的用in

   

   

  尽量避免Select * 命令
  select a,b from … 比 select a,b,c from … 可以让数据库访问更少的数据量

  　　这个误区主要存在于大量的开发人员中，主要原因是对数据库的存储原理不是太了解。

  　　实际上，大多数关系型数据库都是按照行(row)的方式存储，而数据存取操作都是以一个固定大小的IO单元(被称作 block 或者 page)为单位，一般为4KB，8KB… 大多数时候，每个IO单元中存储了多行，每行都是存储了该行的所有字段(lob等特殊类型字段除外)。

  　　所以，我们是取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。

  　　当然，也有例外情况，那就是我们的这个查询在索引中就可以完成，也就是说当只取 a,b两个字段的时候，不需要回表，而c这个字段不在使用的索引中，需要回表取得其数据。在这样的情况下，二者的IO量会有较大差异。
         select * 大多数时候并不会影响到 IO 量，但是当我们还存在 order by 操作的时候，select 子句中的字段多少会在很大程度上影响到我们的排序效率（我理解排序需要将数据放到内存中 字段多占用内存空间多）

   

   

  利用limit 1取得唯一行

   有时要查询一张表时，你要知道需要看一行，你可能去查询一条独特的记录。你可以使用limit 1.来终止数据库引擎继续扫描整个表或者索引

   

   

  尽量少OR

   当where子句中存在多个条件以“或”并存的时候，Mysql的优化器并没有很好的解决其执行计划优化问题，再加上mysql特有的sql与Storage分层架构方式，造成了其性能比较地下，很多时候使用union all或者union(必要的时候)的方式代替“or”会得到更好的效果。

   

   

  尽量少排序

  　　对于MySQL来说，减少排序有多种办法，比如：

  　　通过利用索引来排序的方式进行优化

  　　减少参与排序的记录条数

  　　非必要不对数据进行排序

   

   

  尽量用 union all 代替 union

  　　union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。

   

  避免类型转换

  　　这里所说的“类型转换”是指 where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换：

  　　人为在column_name 上通过转换函数进行转换

  　　直接导致 MySQL(实际上其他数据库也会有同样的问题)无法使用索引，如果非要转换，应该在传入的参数上进行转换

  　　由数据库自己进行转换

  　　如果我们传入的数据类型和字段类型不一致，同时我们又没有做任何类型转换处理，MySQL 可能会自己对我们的数据进行类型转换操作，也可能不进行处理而交由存储引擎去处理，这样一来，就会出现索引无法使用的情况而造成执行计划问题。

   

  where 字段不参与计算

  添加索引

   

  尽可能对每一条运行在数据库中的SQL进行 explain

   

   

  8、mysql分表&分区

  分表的几种方式：

  1、mysql集群

  它并不是分表，但起到了和分表相同的作用。集群可分担数据库的操作次数，将任务分担到多台数据库上。集群可以读写分离，减少读写压力。从而提升数据库性能。

  2、自定义规则分表

  大表可以按照业务的规则来分解为多个子表，也可自己定义规则。

  example：

              创建日期作为规则分表，分别创建几个表：2011年：account_2011，2012年：account_2012。在读写的时候根据日期来查找对应的表名，需要手动来判定。

  3、利用merge存储引擎来实现分表

  MERGE存储引擎：一组数据表结构、顺序。索引定义完全相同的MyISAM数据表集合。

  example：

  merge分表，分为主表和子表，主表类似于一个壳子，逻辑上封装了子表，实际上数据都是存储在子表中的。

  我们可以通过主表插入和查询数据，如果清楚分表规律，也可以直接操作子表。

  子表2011年

  CREATE TABLE `account_2011` (
  `id`  int(11) NOT NULL AUTO_INCREMENT ,
  `name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
  `money`  float NOT NULL ,
  `tradeDate`  datetime NOT NULL 
  PRIMARY KEY (`id`)
  )
  ENGINE=MyISAM
  DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
  AUTO_INCREMENT=2
  CHECKSUM=0
  ROW_FORMAT=DYNAMIC
  DELAY_KEY_WRITE=0
  ;

  子表2012年

  CREATE TABLE `account_2012` (
  `id`  int(11) NOT NULL AUTO_INCREMENT ,
  `name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
  `money`  float NOT NULL ,
  `tradeDate`  datetime NOT NULL 
  PRIMARY KEY (`id`)
  )
  ENGINE=MyISAM
  DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
  AUTO_INCREMENT=2
  CHECKSUM=0
  ROW_FORMAT=DYNAMIC
  DELAY_KEY_WRITE=0
  ;

  主表，所有年

  CREATE TABLE `account_all` (
  `id`  int(11) NOT NULL AUTO_INCREMENT ,
  `name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
  `money`  float NOT NULL ,
  `tradeDate`  datetime NOT NULL 
  PRIMARY KEY (`id`)
  )
  ENGINE=MRG_MYISAM
  DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
  UNION=(`account_2011`,`account_2012`)
  INSERT_METHOD=LAST
  ROW_FORMAT=DYNAMIC
  ;
  

  创建主表的时候有个INSERT_METHOD，指明插入方式，取值可以是：0 不允许插入；FIRST 插入到UNION中的第一个表； LAST 插入到UNION中的最后一个表。

  通过主表查询的时候，相当于将所有子表合在一起查询。这样并不能体现分表的优势，建议还是查询子表。

  分区：

  分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。读写的时候操作的还是大表名字，db自动去组织分区的数据。

  自5.1开始对分区(Partition)有支持
  
  = 水平分区（根据列属性按行分）=
  举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。
  
  === 水平分区的几种模式：===
  * Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。 
  
  * Hash（哈希） – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表。 
  
  * Key（键值） – 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。 
  
  * List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。 
  
  * Composite（复合模式） - 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。 
  
  = 垂直分区（按列分）=
  举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

  [分区命令详解]
  
  = 分区例子 = 
  * RANGE 类型

• 1. CREATE TABLE users (  
  2.        uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
  3.        name VARCHAR(30) NOT NULL DEFAULT '',  
  4.        email VARCHAR(30) NOT NULL DEFAULT ''  
  5. )  
  6. PARTITION BY RANGE (uid) (  
  7.        PARTITION p0 VALUES LESS THAN (3000000)  
  8.        DATA DIRECTORY = '/data0/data'  
  9.        INDEX DIRECTORY = '/data1/idx',  
  10.   
  11.        PARTITION p1 VALUES LESS THAN (6000000)  
  12.        DATA DIRECTORY = '/data2/data'  
  13.        INDEX DIRECTORY = '/data3/idx',  
  14.   
  15.        PARTITION p2 VALUES LESS THAN (9000000)  
  16.        DATA DIRECTORY = '/data4/data'  
  17.        INDEX DIRECTORY = '/data5/idx',  
  18.   
  19.        PARTITION p3 VALUES LESS THAN MAXVALUE     DATA DIRECTORY = '/data6/data'   
  20.        INDEX DIRECTORY = '/data7/idx'  
  21. );  

• 在这里，将用户表分成4个分区，以每300万条记录为界限，每个分区都有自己独立的数据、索引文件的存放目录，与此同时，这些目录所在的物理磁盘分区可能也都是完全独立的，可以提高磁盘IO吞吐量。

  = 分区管理 =

  新增分区

  ALTER TABLE sale_data
  ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));
  

  删除分区
  --当删除了一个分区，也同时删除了该分区中所有的数据。
  ALTER TABLE sale_data DROP PARTITION p201010;
  
  分区的合并
  下面的SQL，将p201001 - p201009 合并为3个分区p2010Q1 - p2010Q3

  ALTER TABLE sale_data
  REORGANIZE PARTITION p201001,p201002,p201003,
  p201004,p201005,p201006,
  p201007,p201008,p201009 INTO
  (
  PARTITION p2010Q1 VALUES LESS THAN (201004),
  PARTITION p2010Q2 VALUES LESS THAN (201007),
  PARTITION p2010Q3 VALUES LESS THAN (201010)
  );

   

  享传分区例子

   

  1、创建一个新表结构跟要拷贝的表一致

  CREATE TABLE `task_ad_show_log_bak` (

    `id` varchar(20) NOT NULL,

    `task_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '任务ID',

    `task_ad_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '广告ID',

    `adcode_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '广告代码ID',

    `ad_position` tinyint(1) NOT NULL DEFAULT '0' COMMENT '广告位置1上2中3下',

    `adowner_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '广告主ID',

    `udid` varchar(32) NOT NULL DEFAULT '' COMMENT '用户唯一标识',

    `user_ip` varchar(15) NOT NULL DEFAULT '' COMMENT '用户IP',

    `brand` varchar(50) NOT NULL DEFAULT '' COMMENT '品牌',

    `model` varchar(50) NOT NULL DEFAULT '' COMMENT '机型',

    `os` tinyint(1) NOT NULL DEFAULT '0' COMMENT '系统1android2ios',

    `os_version` varchar(10) NOT NULL DEFAULT '' COMMENT '系统版本',

    `browser` varchar(50) NOT NULL DEFAULT '' COMMENT '浏览器',

    `browser_version` varchar(20) NOT NULL DEFAULT '' COMMENT '浏览器版本',

    `province` varchar(50) NOT NULL DEFAULT '' COMMENT '省份',

    `city` varchar(50) NOT NULL DEFAULT '' COMMENT '城市',

    `pay` tinyint(1) NOT NULL DEFAULT '0' COMMENT '广告支付0不支付1支付',

    `sub_time` datetime DEFAULT NULL COMMENT '创建时间',

    PRIMARY KEY (`id`,sub_time),

    KEY `idx_sub_time` (`sub_time`) USING BTREE

  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='任务点击日志'

  PARTITION BY RANGE (TO_DAYS(sub_time))(

          PARTITION p16_1031 VALUES LESS THAN (TO_DAYS('2016-11-01')),

          PARTITION p16_1101_1115 VALUES LESS THAN (TO_DAYS('2016-11-16')),

          PARTITION p16_1116_1130 VALUES LESS THAN (TO_DAYS('2016-12-01')),

          PARTITION p16_1201_1215 VALUES LESS THAN (TO_DAYS('2016-12-16')),

      PARTITION p16_1216_1231 VALUES LESS THAN (TO_DAYS('2017-01-01')),

          PARTITION p17_0101_0115 VALUES LESS THAN (TO_DAYS('2017-01-16')),

      PARTITION p17_0116_0131 VALUES LESS THAN (TO_DAYS('2017-02-01')),

      PARTITION p17_0201_0215 VALUES LESS THAN (TO_DAYS('2017-02-16')),

      PARTITION p17_0216_0228 VALUES LESS THAN (TO_DAYS('2017-03-01')),

          PARTITION p17_0301_0315 VALUES LESS THAN (TO_DAYS('2017-03-16')),

      PARTITION p17_0316_0331 VALUES LESS THAN (TO_DAYS('2017-04-01')),

      PARTITION p17_0401_0415 VALUES LESS THAN (TO_DAYS('2017-04-16')),

      PARTITION p17_0416_0430 VALUES LESS THAN (TO_DAYS('2017-05-01')),

          PARTITION p17_0501_0515 VALUES LESS THAN (TO_DAYS('2017-05-16')),

      PARTITION p17_0516_0531 VALUES LESS THAN (TO_DAYS('2017-06-01')),

          PARTITION p17_0601_0615 VALUES LESS THAN (TO_DAYS('2017-06-16')),

      PARTITION p17_0616_0630 VALUES LESS THAN (TO_DAYS('2017-07-01')),

          PARTITION pmax VALUES LESS THAN MAXVALUE

  );

  2、拷贝数据

  insert into task_ad_show_log_bak select * from task_ad_show_log;

   

  3、更新表明

  ALTER  TABLE task_ad_show_log RENAME TO task_ad_show_log_frozen;

   

  ALTER  TABLE task_ad_show_log_bak RENAME TO task_ad_show_log;

   

  4、查看分区数据

  SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'task_ad_show_log';

   

  5、查看查询用到哪个分区

  explain partitions select * from task_ad_show_log_bak where sub_time = '2017-09-30 17:18:25'

   

  6、拆分最大分区

   

  ALTER TABLE task_ad_show_log_bak reorganize partition pmax into (

      PARTITION p8 VALUES LESS THAN (TO_DAYS('2017-02-15')),

           PARTITION pmax VALUES LESS THAN (MAXVALUE)

  );

  6、查询优化器

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

  ---------------------
  select_type
  select 的类型，可能会有以下几种：
  simple: 简单的 select （没有使用 union或子查询）

  primary: 最外层的 select。

  union: 第二层，在select 之后使用了 union。

  dependent union: union 语句中的第二个select，依赖于外部子查询

  subquery: 子查询中的第一个 select

  dependent subquery: 子查询中的第一个 subquery依赖于外部的子查询

  derived: 派生表 select（from子句中的子查询）

  ---------------------

  table
  记录查询引用的表。

  ---------------------

  type
  表连接类型。以下列出了各种不同类型的表连接，依次是从最好的到最差的：

  system:表只有一行记录（等于系统表）。这是 const表连接类型的一个特例。

  const:表中最多只有一行匹配的记录，它在查询一开始的时候就会被读取出来。由于只有一行记录，在余下的优化程序里该行记录的字段值可以被当作是一个 恒定值。const表查询起来非常快，因为只要读取一次！const 用于在和 primary key 或unique 索引中有固定值比较的情形。

  eq_ref:从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。与const类型不同的是，这是最好的连接类型。它用在索引所有部 分都用于做连接并且这个索引是一个primary key 或 unique 类型。

  eq_ref可以用于在进行”=”做比较时检索字段。比较的值可以是固定值或者是表达式，表达示中可以使用表里的字段，它们在读表之前已经准备好 了。以下的几个例子中，mysql使用了eq_ref 连接来处理 ref_table：

  MySQL

   

  1 2	select * from ref_table,other_table whereref_table.key_column=other_table.column; select * fromref_table,other_table whereref_table.key_column_part1=other_table.column andref_table.key_column_part2=1;

   

  ref: 该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引（换句话说，就是连接程序无法根据键值只取得一条记录）的情况。

  当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。 ref还可以用于检索字段使用 =操作符来比较的时候。以下的几个例子中，mysql将使用 ref 来处理ref_table：

  MySQL

   

  1 2 3

  select * from ref_table where key_column=expr; select * fromref_table,other_table whereref_table.key_column=other_table.column; select * fromref_table,other_table whereref_table.key_column_part1=other_table.column andref_table.key_column_part2=1;

   

  ref_or_null: 这种连接类型类似 ref，不同的是mysql会在检索的时候额外的搜索包含null 值的记录。这种连接类型的优化是从mysql4.1.1开始的，它经常用于子查询。在以下的例子中，mysql使用ref_or_null 类型来处理 ref_table：

  MySQL

   

  1	select * from ref_table where key_column=expr or key_column is null;

   

  unique_subquery: 这种类型用例如一下形式的 in 子查询来替换 ref：

   MySQL

   

  1	value in (select primary_key from single_table where some_expr)

   

  unique_subquery: 只是用来完全替换子查询的索引查找函数效率更高了。

  index_subquery: 这种连接类型类似 unique_subquery。它用子查询来代替in，不过它用于在子查询中没有唯一索引的情况下，例如以下形式：

                MySQL

   

  1	value in (select key_column from single_table where some_expr)

   

  range: 只有在给定范围的记录才会被取出来，利用索引来取得一条记录。key字段表示使用了哪个索引。key_len字段包括了使用的键的最长部分。这种类型时 ref 字段值是 null。range用于将某个字段和一个定植用以下任何操作符比较时 =, <>, >,>=, <, <=, is null, <=>, between, 或 in：

                MySQL

   

  1	select * from tbl_name where key_column = 10; select * fromtbl_name where key_column between 10 and 20; select * from tbl_namewhere key_column in (10,20,30); select * from tbl_name wherekey_part1= 10and key_part2 in (10,20,30);

   

   

  index: 连接类型跟 all 一样，不同的是它只扫描索引树。它通常会比 all快点，因为索引文件通常比数据文件小。mysql在查询的字段知识单独的索引的一部分的情况下使用这种连接类型。

  all: 将对该表做全部扫描以和从前一个表中取得的记录作联合。这时候如果第一个表没有被标识为const的话就不大好了，在其他情况下通常是非常糟糕的。正常地，可以通过增加索引使得能从表中更快的取得记录以避免all。

  ---------------------

  possible_keys
  possible_keys字段是指 mysql在搜索表记录时可能使用哪个索引。注意，这个字段完全独立于explain 显示的表顺序。这就意味着 possible_keys里面所包含的索引可能在实际的使用中没用到。如果这个字段的值是null，就表示没有索引被用到。

  这种情况下，就可以检查 where子句中哪些字段那些字段适合增加索引以提高查询的性能。就这样，创建一下索引，然后再用explain 检查一下。详细的查看章节”14.2.2 alter tablesyntax”。想看表都有什么索引，可以通过 show index from tbl_name来看。

  ---------------------

  key
  key字段显示了mysql实际上要用的索引。当没有任何索引被用到的时候，这个字段的值就是null。想要让mysql强行使用或者忽略在 possible_keys字段中的索引列表，可以在查询语句中使用关键字force index, use index,或 ignore index。

  如果是 myisam 和 bdb 类型表，可以使用 analyzetable 来帮助分析使用使用哪个索引更好。如果是 myisam类型表，运行命令 myisamchk –analyze也是一样的效果。详细的可以查看章节”14.5.2.1 analyze tablesyntax”和”5.7.2 table maintenance and crash recovery”。

  ---------------------

  key_len
  key_len 字段显示了mysql使用索引的长度。当 key 字段的值为 null时，索引的长度就是 null。注意，key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。

  ---------------------

  ref
  ref 字段显示了哪些字段或者常量被用来和 key配合从表中查询记录出来。

  ---------------------

  rows
  rows 字段显示了mysql认为在查询中应该检索的记录数。

  ---------------------

  extra
  本字段显示了查询中mysql的附加信息。以下是这个字段的几个不同值的解释：

         range checked for each record

  没有找到合适的索引

  using filesort   

  MYSQL手册是这么解释的“MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。”目前不太明白

  using index 只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。这个比较容易理解，就是说明是否使用了索引

  using temporary

  为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。

  出现using temporary就说明语句需要优化了