MYSQL语句优化（到处看看，到处总结~）

MYSQL语句优化（到处看看，到处总结~）

1. 将子查询转换为连接查询：子查询把内层查询结果作为外层查询的比较条件，需要创建临时表，查询完毕后再删除临时表。
2. limit分布优化，先利用ID定位，再分页。避免出现offset大页码的情况，因为limit会先把行数全查出来再抛弃offset之前的行数。
3. or条件优化，多个or条件可以用union all，对结果进行合并。【union和union all：union 取两个子查询的并集，重复数据只保留一行，通过建立一个带主键的临时表，可解决“去重”问题，通过临时表存储最终的结果集，同时进行默认规则的排序，可以在最后一个结果集指定order by子句改变排序规则；union all也是将两个子查询合并，但不解决重复问题，不用临时表。】对于索引列最好用union all，因为复杂的查询包括运算等，将是union和in上的索引失效而用全表扫描。对非索引字段可以用or或in
4. 用exist代替in： in在查询时，先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按条件筛选。exist指定一个子查询，检测行的存在，遍历循环外表，然后看外表中的记录有没有和内表的数据一样的，匹配则放入结果集中。【in适合外表大而内表小的情况，exist适合外表小而内表大的情况，in不对null进行处理】，not in内外表都进行全表扫描，没用到索引，not exist的子查询仍能用到相关索引。
5. where和having： select，group by和having是聚组函数唯一出现的地方，在where子句中不能使用聚组函数，where语句在group by语句之前，先进行where语句筛选再进行group by分组。having则在group by语句之后，先分组再进行having语句筛选。
6. 对多个字段等值查询时，建立联合索引
7. 尽量避免全表扫描，在where及order by设计的列上建立索引。 order by没有索引会将查询出的数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），从磁盘读影响性能，建立索引 alter table 表名 add index（字段名）。索引本身有序，直接按索引的索引的顺序和映射关系逐条取出数据。
8. 尽量用数字型字段，引擎在处理查询和连接时会逐个比较字符串中的每个字符而对数字型而言只需比较一次。
9. 不要用select *, 用具体字段代替。 如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据，只要有一个没有建立索引就会做全表扫描，这叫索引覆盖，尽可能在select后只写必要查询字段，增加索引覆盖几率。
10. 字段设计尽量使用not null，not null更高效，且不需要判断null

count(*)、 count(1)和count(字段名)

count(*)对于Innodb而言，需要把数据从磁盘中读出来然后累计计数，而MySIAM把一个表的总行数存在了磁盘，直接返回这个数，如果有where条件则和Innodb一样，对此优化，可以使用缓存，但要注意双写一致问题，还可以设计一张表用于存储count();
count(主键id)，innodb会遍历整张表，把每行id都取出来返回给server层，server拿到id判断是不可能为null的就按行累加。对count(1)来说，Innodb会遍历整张表，但不取值，server层对返回的每一行放一个"1"进去，判断是不可能为空的就按行累加，两个用法，count(1)比count(主键id)快，因为引擎返回id涉及到解析数据行以及拷贝字段值的操作。对于count(字段)来说，如果字段为not null，就一行行从数据中读出这个字段，判断不能为null，就按行累加，如果字段可以为null，执行时需要把字段取出进行判断，不是null才进行累加。
count(❤)不会把全部字段取出来，而是专门做了优化，不取值，按行累加
排序效率：
count(❤)=count(1)>count(主键id)>count(字段名)

索引失效的情况

1. where子句中使用！=或<>操作
2. 在where子句中对字段进行null判断
3. or连接的子句，必须每个子句上都有索引，不然会采用全表扫描
4. 在“=”左边进行函数算术运算
5. 当表字段重复记录过多，数据库认为走索引不是最佳选择，索引可能会失效。
6. like查询，不能以通配符开头
7. 符合索引只对第一个字段有效

不适用索引的情况

1. 数据重复且发布比较均匀的字段不适合做索引（唯一性太差的字段不适合做索引），如性别
2. 参与列计算的列不适合做索引
3. 频繁更新的字段不适合做索引

慢查询

set global slow_query_log=on

explain 执行计划：

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
select_type: SELECT 查询的类型.
table: 查询的是哪个表
partitions: 匹配的分区
type: join 类型
【
从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

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: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.

ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.
】
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引.
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息

常见的慢查询优化

1）索引没起作用
2）优化数据库结构：将字段很多的表分解成多个表；增加中间表，对于需要经常联合查询的表，建立一个中间表。
3）分解关联查询：将一个大的查询分解为多个小的查询
4）优化limit分页
【
1.先查询出主键id，然后直接通过id的值查询主键后的数据select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;
2.“关延迟联”：让MySQL扫描尽可能少的页面，获取需要的记录后再根据关联列回原表查询所需的所有列。
Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);
】
5）分析具体的SQL语句：

1. 两个表选哪个为驱动表，交给mysql查询优化器自己决定。
   把查询修改为inner join连接查询
   select * from a inner join b on a.id=b.id;
   不能用left join或right join，表之间的连接顺序被固定了，左连接就是必须先查左表全表扫描，然后一条一条的到另外表去查询，右连接同理。
2. 利用explain字段查看执行时运用到的key（索引）
   把两个表的连接条件的两个字段都各自建立上索引，然后explain 一下，查看执行计划，看mysql到底利用了哪个索引，最后再把没有使用索引的表的字段索引给去掉就行了。因为维持索引也需要消耗资源。