java---sql优化

explain

语法：Explain + SQL 语句;

1. id

• 1.1 id相同，执行顺序由上至下

explain select * from t1 where t1.id in (select t2.id from t2);

• 1.2 id 不同，如果是子查询，id 号会递增，id 值越大优先级越高，越先被执行

explain select * from t1 where t1.id = (select t2.id from t2);

2. select_type

simple  简单的 select 查询，查询中不包含子查询或者 union 查询

primary  SQL 语句中包含任何子查询，那么子查询的最外层会被标记为 primary

subquery  在 select 或者 where 里包含了子查询，那么子查询就会被标记为 subQquery

derived  在 from 中包含的子查询，会被标记为衍生查询，会把查询结果放到一个临时表中

union / union result  如果有两个 select 查询语句，他们之间用 union 连起来查询，那么第二个 select 会被标记为 union，union 的结果被标记为 union result

3. $\color{red}{type}$，性能高到低依次是 system > const > eq_ref > ref > range > index > all

一般来说，得保证查询至少是 range 级别，最好能达到 ref级别

system 表中只有一行记录
const  将主键索引或者唯一索引放到 where 条件中查询，MySQL 可以将查询条件转变成一个常量，只匹配一行数据，索引一次就找到数据了
eq_ref 在多表查询中，如 T1 和 T2，T1 中的一行记录，在 T2 中也只能找到唯一的一行，说白了就是 T1 和 T2 关联查询的条件都是主键索引或者唯一索引，这样才能保证 T1 每一行记录只对应 T2 的一行记录
ref    不是主键索引，也不是唯一索引，就是普通的索引，可能会返回多个符合条件的行。
range  一般出现在 where 条件中的 between、and、<、>、in 等范围查找中。
index  将所有的索引树都遍历一遍，查找到符合条件的行。索引文件比数据文件还是要小很多，所以全表扫描还是要快很多。
all   没用到索引，全表扫描，查找到符合条件的数据

4.$\color{red}{key}$ 此次查询中实际上用到的索引

5.$\color{red}{key_len}$ 长度越短查询效率越高

6.$\color{red}{rows}$ 大致估算说要找到所需记录需要读取的行数，rows 越小越好

索引

-- 添加索引
alter table 表名 add  index idx_name_age(name, age)

-- 测试(不走缓存)
select sql_no_cache * from user where name = "bwf" and age = 18

索引优化

• 1. 最左前缀法则（复合索引），带头大哥不能挂，桥不能断。
• 2. sql中不要对索引做如下处理，否则索引会失效

加减乘除 !=  <>  / is null / or  等
函数 sum() 等
类型转换  id = 1 (表中id是字符串)

• 3.索引字段不要放在范围查找的右边

比如复合索引（id,age,name） where id = ? and age > ? and name = ?
这个时候只能用到索引 id 和 age.

• 4.减少select * 的使用

select * 会破坏覆盖索引（覆盖索引就是where的列覆盖查询的列）

• 5.like的模糊搜索，like "%name%" 或者 like "%name" 会使索引失效
• 6.order by 后面的索引会失效，除非使用覆盖索引，建议java代码层做排序
• 7.联合索引失效原因：a是有序的，b是无序的，只有在a相等的情况下，b才是有序的

各种类型的索引

• hash 索引

hash索引缺点：  hash生成的值是无序的，不利于范围的查询和排序的操作

• 平衡二叉树索引(左子树和右子树的高度的相差<=1)

平衡二叉树索引缺点： 数据量多的时候树的高度就比较高，比较难查找到数据，另外譬如找大于某范围的值，需要回表查询

• B树

B树优点:相对于平衡二叉树，高度变矮，查找速度变快
B树缺点：依然存在回旋查找的问题

• B + 树

高度变矮,且有序排列，所以找大于某范围的也是很快

索引和表数据之间的关联