Mysql 查询优化

优化原则:小表驱动大表,即小的数据集驱动大的数据集

     建表sql

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bookName` varchar(20) DEFAULT NULL,
  `price` decimal(6,2) DEFAULT NULL,
  `author` varchar(20) DEFAULT NULL,
  `bookTypeId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_type` (`bookTypeId`)
) ENGINE=InnoDB AUTO_INCREMENT=1331 DEFAULT CHARSET=utf8;

/*Data for the table `book` */

insert  into `book`(`id`,`bookName`,`price`,`author`,`bookTypeId`) values (1,'茶花女','12.00','大仲马',1),(1101,'sss','12.00','wad',1),(1310,'test','122.00','里斯',23),(1311,'test','122.00','里斯',23),(1312,'test','122.00','里斯',23),(1313,'test','122.00','里斯',23),(1314,'test','122.00','里斯',23),(1315,'test','122.00','里斯',23),(1316,'test','122.00','里斯',23),(1317,'test','122.00','里斯',23),(1318,'test','122.00','里斯',22),(1319,'test','122.00','里斯',22),(1320,'test','122.00','里斯',22),(1321,'test','122.00','里斯',22),(1322,'test','122.00','里斯',22),(1323,'test','122.00','里斯',22),(1324,'test','122.00','里斯',22),(1325,'test','122.00','里斯',22),(1326,'test','122.00','里斯',22),(1327,'test','122.00','里斯',22),(1328,'test','122.00','里斯',22),(1329,'test','122.00','里斯',22),(1330,'test','122.00','里斯',22);

/*Table structure for table `booktype` */

DROP TABLE IF EXISTS `booktype`;

CREATE TABLE `booktype` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bookTypeName` varchar(20) DEFAULT NULL,
  `bookNum` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `booktype` */

insert  into `booktype`(`id`,`bookTypeName`,`bookNum`) values (1,'计算机类',18),(2,'文学类',4),(3,'教育类',4);

 Exists和in 对比

SELECT * FROM book WHERE bookTypeId IN (SELECT id FROM booktype);
等价于 SELECT id FROM booktype
	SELECT * FROM book WHERE book.bookTypeId=booktype.id

 当bookType的数据集合小于book的数据集的时候，用in优于Exists

SELECT * FROM book WHERE EXISTS  (SELECT 1 FROM booktype WHERE book.bookTypeId=booktype.id);
	等价于  SELECT * FROM book
	        SELECT * FROM booktype WHERE booktype.id=book.bookTypeId

 当book的数据集合小于bookType的数据集的时候，用Exists优于in

   使用Exlain执行如下

explain执行计划中第一个执行的就是驱动表。。。。

EXISTS 

  select ...from table where EXISTS (subquery)

 该语法可解释为:将主查询的数据，放到子查询中做条件验证,根据验证结果（true或false）来决定主查询的数据结果是否得以保留.

 注意：

1.EXISTS(subquery)只返回true，或者false,因此子查询中的select*也可以是select1或其他,官方说实际执行中会忽略select清单,因此没有区别

2.EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比,如果担忧效率问题,可进行实际检验以确定是否有效率问题

3.EXISTS子查询往往也可以用条件表达式,其他子查询或者join来替代，何种最优需要具体问题具体分析。

Mysql支持二种方式的排序,FileSort和Index，Index效率高，指Mysql扫描索引本身完成排序,FileSort效率比较低.

order by 满足两种情况,会使用Index方式排序:

1.order by 语句使用索引最左前列

2.使用where子句与order by 子句条件列组合满足索引最左前列.

  Mysql4.1之前是使用双路排序，从磁盘取排序字段,在buffer进行排序，再从磁盘取其他字段.->就是两次扫描磁盘,最终得到数据.读取行指针和order by列,对他们进行排序，然后在扫描已经排序号的列表，按照列表中的值重新从列表中读取相应的数据输出.

  无疑,两次扫描磁盘比较耗时，4.1之后出现了单路排序.

提高order by的速度

1.order by时seelct*是一个大忌，只Query需要的字段,这点非常重要,在这里的影响时

 (1).当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是test|blob类型时,会使用改进后的算法-单路排序,否则使用老算法-多路排序

（2).两种算法的数据都有可能超出sort_buffer的容量,超过之后，会创建temp文件进行合并排序,导致多次I/O，但是使用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。

2.尝试提高sort_buffer_size

  不管使用哪种算法,提高这个参数都会提高效率.当然,要根据系统的能力去提高,因为这个参数是针对每个进程的.

3.尝试提高max_length_for_sort_data

     提高这个参数,会增加用改进算法的概率,但是如果设的太高，数据总总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率.

sort_buffer_size可以查看 https://blog.csdn.net/yjaspire/article/details/81097233

max_length_for_sort_data 可以查看 https://blog.csdn.net/san_er/article/details/46006199