sql调优

数据库层面

选择合适的存储引擎(以下是对innodb引擎)

表结构

1.表中要有主键
2.字段类型使用正确的类型 长度使用合适的长度 尽量not null设置一个默认值
3.短时间内批量生成数据表的主键尽量使用uuid(多个线程防止主键区间锁导致死锁)

索引失效的几个情况

1.最左前缀原则(复合索引)
	如果有一个 2 列的索引 (col1, col2)，则已经对 (col1)、(col1, col2) 上建立了索引.
	如果有一个 3 列索引 (col1, col2, col3)，则已经对 (col1)、(col1, col2)、(col1, col2, col3) 上建立了索引.
	注意情况
	1.col1, col2, col3顺序会被mysql自动优化
	2.范围查找当前索引有效后面的索引失效
	3.order排序情况也一致遵循最左

2.left join 连接字段数据类型(int 和 vachar)或者两个表字符编码不一样
3.like 以%开头(对大的文本进行搜索使用全文索引)
4.or语句前后没有同时使用索引(or左右查询字段只有一个是索引，该索引失效)
5.隐式转化
	规则
	1.以数字开头的字符串，截取前面的数字字符串，转换为对应的数字
	2.以字符开头的字符串，字符串即使包含了数字，也会转换为0
	3.超出整型范围时不同的数可能认为一致
	4.会导致查询出来的数据不准确(如果有删除修改操作会有严重影响)
	索引失效
	1.当where查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
	2.当where查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低(官网解释 在所有其他情况下，将参数作为浮点数（实数）进行比较。例如，将字符串和数字操作数进行比较，将其作为浮点数的比较)
	
6.在索引列上使用 IS NULL 或 IS NOT NULL操作(索引是不索引空值的) 解决方法(数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可)
7.对索引字段进行计算和函数操作
8.NOT IN(在索引字段上使用not) != 和<>操作(NOT IN 和 != 可以用NOT EXISTS代替 id<>3则可使用id>3 or id<3来代替)
9.如果MySQL估计使用索引比全表扫描更慢，则不使用索引
10.in/exists/表连接  A in(B) /A exists B/A join B  
	in B比A小
	exists B比A大
	最好使用连接而不用子查询(小表驱动大表(减少连接次数))

Mysql-Explain

	id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.有几个select就有几个id
		select、from、union。id大执行优先级高若相同则自上而下
	select_type: SELECT 查询的类型.
	table: 查询的是哪个表
	partitions: 匹配的分区
	type: join 类型
	possible_keys: 此次查询中可能选用的索引
	key: 此次查询中确切使用到的索引.
	key_len：索引中使用的最长索引字节数
	ref: 哪个字段或常数与 key 一起被使用
	rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
	filtered: 表示此查询条件所过滤的数据的百分比
	extra: 额外的信息

常用字段

	select_type
		SIMPLE, 表示此查询不包含 UNION 查询或子查询
		PRIMARY, 表示此查询是最外层的查询
		UNION, 表示此查询是 UNION 的第二或随后的查询
		DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
		UNION RESULT, UNION 的结果
		DERIVED,from后的字查询会产生临时表
		SUBQUERY, 子查询中的第一个 SELECT
		DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.	

SUBQUERY和DEPENDENT SUBQUERY区别
DEPENDENT SUBQUERY优化
type
system: 表中只有一条数据. 这个类型是特殊的 const 类型.
const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高
ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.
range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.
index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.
ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一
key_len
表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的计算规则如下:
字符串
char(n): n 字节长度
varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
数值类型:
TINYINT: 1字节
SMALLINT: 2字节
MEDIUMINT: 3字节
INT: 4字节
BIGINT: 8字节
时间类型
DATE: 3字节
TIMESTAMP: 4字节
DATETIME: 8字节
字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

Extra

		Using filesort 
			表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
		Using index
			"覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
		Using temporary
			查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.