先创建测试表并使用存储过程插入测试数据

CREATE TABLE `cif_student` (
  `id` varchar(40) NOT NULL,
  `name` varchar(40) DEFAULT NULL,
  `age` varchar(40) DEFAULT NULL,
  `class_id` varchar(40) DEFAULT NULL,
  `class_name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `cif_class` (
  `id` varchar(40) NOT NULL,
  `class_name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP PROCEDURE IF EXISTS proc_initData;-- 如果存在此存储过程则删掉
CREATE PROCEDURE proc_initData() -- 创建存储过程
BEGIN
    DECLARE i INT DEFAULT 1;
        DECLARE j INT ;
    WHILE i<=15000000 DO
                SET j = ROUND(RAND()*15000000);
                INSERT INTO cif_student(id,name,age,class_id,class_name) values(i,CONCAT('sname',i),15,j,CONCAT('cname',j));
                INSERT INTO cif_class(id,class_name) VALUES(i,CONCAT('cname',i));
        SET i = i+1;
    END WHILE;
END;
CALL proc_initData();-- 调用存储过程

一、避免回表查询

Innodb采用聚集索引的方式。表数据文件本身就是按 B+Tree 组织的一个索引结构，索引的 key 是数据表的主键，树的叶子节点data域保存了完整的数据记录。称为主索引。Innodb的辅助索引 data 域则存储相应记录主键的值。

所以按照主键搜索十分高效，但是按照辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录，这就是所谓回表查询。

避免回表查询，可以适当使用联合索引，将被查询的列加入索引中：

比如，若给student表的name列加上索引，使用如下查询：

SELECT t.id,t.name,t.class_id from cif_student t where t.name like 'sname1%';

会产生回表查询，因为class_id是name辅助索引列里没有的，必须获取到辅助索引记录的主键值，然后根据主键值回主索引查找。耗时约1.8s。

若给name,class_id加上联合索引，再执行上述SQL语句则不需要回表查询。耗时约0.5s，效率显著提升。

二、联合索引

如果经常需要同时对两个字段进行AND查询,那么使用两个单独索引不如建立一个复合索引，因为两个单独索引通常数据库只能使用其中一个，而使用复合索引因为索引本身就对应到两个字段上的，效率会有很大提高。

mysql联合索引会一直向右匹配直到遇到范围查询就停止匹配。如果建立(a,b,c,d)的联合索引，a = 1 and b=2 and c>3 and d=4，这里d是用不到索引的，如果建立(a,b,d,c)的联合索引则可以用到。a,b,c,d的查询顺序可以任意调整，mysql查询优化器会帮你优化成索引可以识别的形式。所以考虑建立联合索引的时候应该重点考虑联合索引的字段顺序，比如若想建立a,b字段联合索引，且b字段已有索引经常单独查询，则应建立(b,a)的联合索引。

三、避免索引失效

• or 语句前后没有同时使用索引。当 or 语句查询字段只有一个是索引，该索引失效，只有当 or 语句左右查询字段均为索引时，才会生效

• 对于复合索引，如果不使用前列，后续列也将无法使用

• like 关键字以 % 开头

• 数据类型出现隐式转化，那么会导致索引失效，造成全表扫描效率极低

• 索引列参与计算

• 索引列使用函数

• 使用 not，<>，!=，is not null

  不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

  tips:is null是可以使用到索引的，is not null才不能使用到索引。null值列不参与计算，<>符号和count函数会自动过滤为null的列，比如:where a != ‘1’，则不会查询出a为null的列。

四、使用执行计划优化

先看一个正常的连接查询两张表并排序取前10条，sql如下：

select * from cif_student p left join cif_class t on p.class_name=t.class_name order by p.class_name limit 10;

发现其效率及其低下（根本无法查出）。查看执行计划发现驱动表p和被驱动表t都使用了全表扫描(type列为ALL，扫描行数rows为全表记录)，且p表使用了临时表和非索引排序(extral列中Using temporary; Using filesort)。

优化方式一

首先想到的是连接查询，驱动表和被驱动表存在连接条件，则被驱动表连接字段需要加上索引。
给t表加上索引后执行，发现效率大为提升，耗时约0.5s。执行计划如下：

发现t表type为ref，即非主键非唯一索引等值扫描，但是p表排序还是未使用到索引，再给p表class_name字段加上索引后执行，耗时约0.005s，执行计划如下：

优化方式二

mysql连接查询执行顺序为先执行连接查询，再执行排序和limit截取。表p和表t各10多万条数据，执行连接查询就是亿级别数据，再排序时肯定效率就非常低了。可以使用子查询先缩小p表查询范围，再做连接。优化后sql如下：

select * from (select * from cif_student pp order by pp.class_name limit 10) p left join cif_class t on p.class_name=t.class_name;

发现不加索引，其耗时也仅为2s左右。两表加上索引后耗时约0.008秒，与方式一执行效率相当。

五、分页查询优化

Mysql的分页查询十分简单，但是当数据量大的时候一般的分页就吃不消了。

传统分页查询：

SELECT c1,c2,cn… FROM table LIMIT n,m

MySQL的limit工作原理就是先读取前面n条记录，然后抛弃前n条，读后面m条想要的，所以n越大，偏移量越大，性能就越差。

利用表的覆盖索引来加速分页查询

查询的语句中如果只包含了那个索引列（覆盖索引），不需要回表，所以效率很高

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

另一种写法也很赞

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id