3.mysql-索引与慢查询和sql优化

###############################查询数据库的存储引擎 
show ENGINES
########################设置隔离级别--只针对于当前会话有用
##未提交读
set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
##已提交读
set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
##########################查询CRUD次数#########################
show STATUS like 'com_insert' #执行多少次插入
show STATUS like 'com_update' #执行多少次更新
show STATUS like 'com_select' #执行多少次查询
show STATUS like 'com_delete' #执行多少次删除

#########################开启慢查询####################
#查询数据所有配置文件
show VARIABLES 
##查询文件存放的目录
show VARIABLES  like 'datadir'
##查看是否开启慢查询
show variables like 'slow_query%';
show variables like 'long%'
##开始慢查询
set GLOBAL slow_query_log =1 #开启慢查询
SET GLOBAL long_query_time = 0 #设置查询时间
select *from omp_trashpoint LEFT JOIN omp_community on omp_trashpoint.objid = omp_community.id

mysql –uroot

慢查询

开启慢查询

慢查询分析工具

下载链接

索引与执行计划

索引分类

执行计划分析

#查询所有小区有多少个投放点和设备总数量
SELECT
	omp_community.NAME,
	count( omp_trashpoint.NAME ) AS trashNums,
	sum( trashResult.qeuripNums ) AS qeuripNums 
FROM
	omp_community
	LEFT JOIN omp_trashpoint ON omp_trashpoint.objid = omp_community.id
	LEFT JOIN (
		SELECT
			count( omp_equiparchives.id ) AS qeuripNums,
			omp_trashpoint.id 
		FROM
			omp_trashpoint
			LEFT JOIN omp_equiparchives ON omp_trashpoint.id = omp_equiparchives.trashpoint 
		GROUP BY
			omp_trashpoint.NAME,
			omp_trashpoint.id 
	) trashResult ON omp_trashpoint.id = trashResult.id 
GROUP BY
	omp_community.NAME

const级别

const级别：一张表根据id进行数据查询，并且结果只有一条

eq_ref级别

唯一性索引扫描，对于每个索引值，表中只有一条记录与之匹配，常用于主键或者唯一索引扫描

ref级别

非唯一性索引，返回某个值匹配的所有行

range级别

一般是在where条件后面进行范围内条件筛选，比如between,>,<,!=,in等。

index级别

查询的结果全部为索引列。

key分析

possible_keys：可能用到的key，
key：实际用到的key，如果为nulll，则没使用索引

执行计划-Extra

Sql优化策略

全值匹配

最佳左前缀原则

简单就是说，覆盖索引，你在where条件，必须先使用从最左边的索引，才可以索引有效。举个列子联合索引构成有火车头、火车身、火车尾巴，这时候没有身体和尾巴车头可以正常运行，但是火车没有头，只有身子和尾巴可以跑起来了嘛。如下图的一个覆盖索引

这时候看下sql语句

不要在索引上做任何操作

简单说就是不可以在索引上做计算、函数、类型转换等操作，都会使索引失效

下面对索引进行操作，做法会导致索引失效

范围条件放在最后

覆盖索引尽量使用

尽量使用覆盖索引，尽量保证查询结果列和索引列一致，减少select *

不等于慎用

like匹配

llike '%XX%'或者like '%XX ’ 会导致索引失效
like ‘XX%’，不会导致索引失效

or改union效率高

优化口诀