mysql——查询性能优化

• 为什么查询会慢？

1.对于查询的快慢，一个重要的影响指标是响应时间。
2.若将查询看成一个任务，那么它由一系列的子任务组成，每个子任务都会消耗一定的时间。
3.优化查询，就要优化子任务，要么消除一些子任务，要么减少子任务的执行次数，要么让子任务执行的更快。
4.查询的生命周期：
	查询的生命周期大致可以分为：
		（1）从客户端到服务器
		（2）服务器解析，生成执行计划
		（3）执行，返回结果给客户端
	执行是整个生命周期的重要阶段，又可以分为：
		（1）为了检索数据到存储引擎的大量调用
		（2）调用后的数据处理（排序，分组等）
5.为了完成查询生命周期中的任务，需要在不同的地方花费时间，包括：
		网络
		cpu计算
		生成统计信息
		执行计划
		锁等待（互斥等待）等
	尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作，cpu操作和内存不足时导致的I/O操作上消耗时间。
	根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。
	

• 慢查询基础：优化数据访问（减少数据的访问量）

查询性能地下的基本原因就是：访问的数据太多。
	1.数据库是否请求的不需要的数据
		（1）查询不需要的记录（可以使用limit限制）
		（2）多表关联返回了全部的列，如：
		select * form a 
			inner join b using(a.id)
			inner join c using(c.id)
			where a.name='tom';
		如上的查询将会返回三个表的全部数据列，正确的写法应该是只写我们需要的列，a.*/b.*/c.*或者任意组合。
		（3）重复查询相同的数据（对于一个长期都可能不变化的数据，可以使用缓存，还不是每次都去查）
	2.mysql是否在扫描额外的记录
		（1）衡量查询开销最基本的三个指标：
			响应时间
			扫描的行数
			返回的行数
		响应时间是两部分之和：服务时间+排队时间
		服务时间：数据库处理查询所花的时间
		排队时间：服务器因等待某些资源而没有执行查询消耗的时间。
		（2）理想情况下扫描的行数和返回的行数应该相同，但是通常都是在1:1-10:1之间，某些情况下，这个比值可能非常大。
		（3）扫描的行数和类型：
			explain语句可以查看查询语句具体的执行信息：type列反应了访问类型：
			包含：全表扫描，索引扫描，范围扫描，唯一索引查询，常数引用等。（从慢到快）
		如果查询扫面大量的数据只返回少量的行，那么可以尝试以下的方式：
		（1）使用索引覆盖扫描（将所有需要查询的列放到索引中）
		（2）改变数据库表结构（使用单独的汇总表）
		（3）重写这个复杂的查询。	

• 重构查询方式

1.一个复杂查询，还是多个简单查询
	衡量是否可以将一个复杂的查询分成多个简单的查询。
2.切分查询，每个查询功能完全一样，但只完成一小部分，每次只返回一小部分查询结果。
	如：删除旧的数据，如果一次删除大量的数据，需要一次锁住很多数据，会阻塞其他小的查询。所以可以定时删除，每次删除一定量的数据。
	一次删除一百万行数据一般来说是一个比较高效且对服务器影响最小的做法。
3.分解关联查询：
	对关联进行分解，分解成单表查询，然后在应用程序中将结果进行关联。
	分解关联查询有以下好处：
		让缓存效率更高，应用可以更方便地缓存单表查询的结果对象。
		执行单个查询可以减少锁的竞争。
		在应用层关联，可以更容易对数据库进行拆分，更容易做到高性能和扩展性。
		减少冗余记录的查询。
		这样做相当于在应用层做了哈希关联，而不是mysql的嵌套循环关联。

• 查询执行的基础：

1.mysql执行查询简单过程：
	（1）.客户端发送一条查询给服务器。
	（2）.服务器先检查查询缓存，如果命中了缓存，从缓存中返回结果。否则进行下一阶段。
	（3）.服务器端进行sql解析，预处理，再优化器生成对应的执行计划。
	（4）.mysql根据生成的执行计划，调用存储引擎的API来执行查询。
	（5）.将结果返回给客户端。
2.mysql客户端/服务器通信协议
	mysql客户端和服务器之间的通信协议是半双工的。
	这意味着：在任何一个时刻，要么是服务器向客户端发送数据，要么是客户端向服务器端发送数据。
	这两个动作无法同时发生，无须将一个消息切成小块来独立发送。
3.查询状态；
	对于一个mysql连接或者说一个线程，任何时刻都有一个状态：
		可以使用show full processlist命令（返回结果中的command列就表示当前的状态）
		eg：
			Sleep；线程正在等待客户端发送新的请求。
			Query：线程正在执行查询或者正在将结果发送给客户端
			Locked：在服务器层，线程正在等待表锁。在存储引擎级别实现的锁，如InnoDB的行锁并不会出现在线程状态中。
			Analyzing and statistics：正在收集存储引擎的统计信息，并生成查询的执行计划。
			Copying to tmp table[on disk]：线程正在执行查询，并且将结果复制到一个临时表中。
			（这种状态要么是在做group by操作，要么是文件排序，或者union操作，要是有 on disk 标记，表示正在将一个内存临时表放到磁盘上）
			Sorting result：正在对结果集进行排序
			Sending data：存在多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。
4.查询优化器：
	一条查询可能存在多种执行方式，最后都返回相同的结果，优化器的作用就是找到其中最好的执行计划。
	mysql采用的是基于成本的优化器。
	在执行查询语句之后，可以通过查询当前会话的Last_query_cost值，来知道mysql计算当前查询的成本。
	show status like 'Last_query_cost';
	但是也会有很多种情况会导致优化器选择错误的执行计划。：
		统计信息不准确。
		执行计划中的成本估算不等于实际执行的成本。
		mysql从不考虑其他并发执行的查询。
		mysql不会考虑不控制的操作的成本。
		mysql也不是任何时候都是基于成本的优化。有时也会基于一些固定的规则，如存在match（）子句，就会采用全文索引，即使存在别的比这快的索引。
5.mysql可以处理的优化类型：
	（1）重新定义关联表的顺序
	（2）将外连接转化为内连接
	（3）使用等价变换规则
		eg：(5=5 and a>5) -> (a>5)
			(a<b and b=c) and a=5 -> b>5 and b=c and a=5
	（4）优化count(),min(),max()
	（5）预估并转化为常数表达式
		mysql检测到一条表达式如果可以转化成常数表达式，就会一直将该表达式作为常数进行优化。
	（6）覆盖索引扫描
	（7）子查询优化
	（8）提前终止查询
	（9）等值传播
		如果两个列的值通过等式关联，mysql能够把其中一个列的where条件传递到另一个列上。
		eg： select * from a left join b using(user_id)
			 where a.user_id > 5;
		这个时候where条件子句同时适用a表和表表中的user_id,相当于where a.user_id > 5 and b.user.id >5;
	（10）列表in()的比较
	一般让优化器按照它自己的方式工作就好了。
6.mysql如何执行关联查询：
	mysql中的关联一词意义比较广泛，mysql认为任何一个查询都是一次关联，并不是一个查询需要用到两个表匹配才叫关联。
	每一个查询，每一个片段（包括子查询，甚至基于单表的select）都可能是关联。（因为查询过程中可能会用到临时表）
7.执行计划：
	mysql生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回执行结果。
8.关联查询优化器：
	mysql优化器最重要的一部分就是关联查询优化，它决定了多个表关联的顺序。
	通过不同的顺序评估出一个代价最小的关联顺序。

• 优化特定的查询

1.count()是一个特殊的函数，可以统计某个列值的数量，也可以统计行数。
	在统计列值时要求列值是非空的（不统计null值），如果在count（）的括号中			指定了列或者列的表达式，则统计的就是这个表达式有值的结果集。
	当mysql确认括号内的表达式值不可能为空时，实际上就是统计行数。
	count(*)，通配符*并不会扩展成所有的列，实际上，它会忽略所有的列，而直接统计所有的行数。
2.优化关联查询
3.优化子查询（使用关联查询代替）
4.优化group by和distinct
5.优化group by with rollup
6.优化limit分页（偏移量很大的情况）
7.优化union查询
8.优化sql_calc_found_rows

• 这部分只是记录个大概，更加细的探究待续。