MySql优化 二

SPU和SKU的区别

1. spu（standard product unit）是标准的产品单位。
2. sku（stock keeping unit）是库存进出计量的基本单位。

SQL正确的编写顺序

<select_list> 
FROM <left_table> <join_type> 
JOIN <right_table> ON <join_condition> 
WHERE <where_condition> 
GROUP BY <group_by_list> 
HAVING <having_condition> 
ORDER BY <order_by_condtion> 
LIMIT <limit_number>

SQL真实的执行顺序

FROM <left_table> 
ON <join_condition> <join_type> JOIN <right_table>     //1. 确定了范围
WHERE<where_condition> 
GROUP BY <group_by_list> 
HAVING <having_condition>        //2. 做数据过滤 
SELECT DISTINCT <select_list>    //3. 获取子集 
ORDER BY <order_by_conditon>     //4. 排序 
LIMIT <limit_number>             //5. 分页

常用的聚合函数，分组过滤，分页，关联查询

事务机制

与事务机制相关的两个日志文件（undo和redo）

事务特性

1. 原子性是基础特性，隔离性是手段，一致性是约束条件，而持久性是我们 的目的。人称ACID

事务隔离级别

事务隔离级别带来的问题

1. 脏读：读到了其他事务还没有提交的数据。
2. 不可重复读：对某数据进行读取，前后两次读取的结果不同，也就是说没有读到相同的内容。这 是因为有其他事务对这个数据同时进行了修改或删除。
3. 幻读：事务 A 根据条件查询得到了 N 条数据，但此时事务 B 删除或者增加了 M 条符合事务 A 查询 条件的数据，这样当事务 A 再次进行查询的时候发现会有 N+M或者N-M 条数据，产 生了幻读。

不可重复读和幻读的区别

1. 不可重复读是对于同一条记录内容的“不可重复读”。
2. 幻读是对于某一范围的数据集，发现查询数据集的 行数多了或者少了，从而出现的不一致。
3. 所以不可重复读的原因是对于要查询的那条数据进行了 UPDATE或DELETE，而幻读是对于要查询的那个范围的数据集，进行了INSERT。

慢查询日志

1. 慢查询日志会把查询耗时超过规定时间的SQL语句记录下来，利用慢查询日志，定位分析性能的瓶颈。
2. show variables like ‘slow_query%’;

#查看慢查询日志： 
select @@slow_query_log; 
#开启： 
SET GLOBAL slow_query_log=1;
# 查看阈值： 
show variables like 'long%'
# 设置阈值：
set long_query_time=0.0001;（根据实际情况进行设置）
# 查询正在执行的SQL，实时性的方式
show processlist
# 等待分析的慢SQL
explain select * from t_user where name ='jiekami';

执行计划explain

select_type

type*

1. rows=3 索引扫描的次数
   

extra

EXPLAIN select price from t_product where name='华为笔记本88' order by price;
EXPLAIN select price from t_product order by price;

各种优化场景

1. 不要用selet * #要按需查询所需要的数据，可以减少网络传输的带宽
2. 谨慎使用模糊查询 # 不使用索引—>全文索引引擎
3. 优化排序
4. 少用null
5. 少用!=运算符
6. 少用or
7. 少用in或not in
8. 避免出现类型转换 5.7已经优化
9. 在表达式左侧使用运算符和函数都会让索引失效

联合索引的最左前缀原则*

匹配左前缀，和索引中的第一列进行匹配（联合索引,a,b,c）
EXPLAIN select * from t_index where a='a' and b='b' and c='c';
EXPLAIN select * from t_index where a='a' and b='b';
EXPLAIN select * from t_index where a='a' and c='c';
#必须有第一列参与
EXPLAIN select * from t_index where b='b' and c='c';
#顺序没关系 
EXPLAIN select * from t_index where b='b' and c='c' and a='a';

上百万条记录，如何优化分页?*

问题：为什么分页需要优化？

分页的问题在于，当偏移量很大时，例如limit 100000,10
mysql需要查询100010条记录，但是只返回最后10条，前面的100000条记录都将被抛弃，代价很高
select * from t_product limit 1000000,10

技术上优化方案：

采用覆盖索引+延迟加载的策略

select f.film_id,f.description 
from film f 
INNER JOIN 
(select film_id from film order by title limit 100000,10) b 
on f.film_id=b.film_id;

业务上的优化方案：

业务上优化，限定用户不能访问靠后的数据，比如百度搜索的设置
用户只能查看到有限的分页信息

大批量导入数据如何优化*

大批量插入数据

场景一：通过执行SQL执行

1. 删除表的索引
2. 采用Insert into table values (?,?,?),(?,?,?),(?,?,?); 减少交互次数
3. 插入成功之后，恢复表的索引。

场景二：通过程序执行

1. 采用JDBC提供的批处理方式
2. 且关闭事务的自动提交机制，由人工提交。connection.setAutoCommit(false);

场景三：通过命令行，以load的模式导入数据

1. 因为innodb是按照主键的顺序保存的，所以将导入的数据按照主键进行排序，可以提高导入数据的 效率。信息之间以“，”隔开，然后换行

load data local file '/usr/data.txt' into table 't_table' fields terminated by ',' line terminated by '\n'

2. 在执行导入之前，关闭唯一校验，set unique_checks=0,导入结束之后，再设置set unique_checks=1，进行恢复
3. 在执行导入前，关闭自动提交，set autocommit=0,导入结束之后，再设置set autocommit=1

MySQL什么时候会放弃使用索引？

1. EXPLAIN select * from film where language_id=1;
2. 但是这个索引字段的区分度不高，所以不会走索引
3. language`_id不是主键，所以走索引路线，会走两遍
4. 这种情况下，由于表的数据特点，language_id都是1，所以这个时候走全表扫描会更快一些

什么是覆盖索引？

1. 覆盖索引，表示我们需要获取的数据仅仅从索引树即可获得，不需要再回表查询。
2. 这是一种非常高效的查询结果：执行计划的结果extra的值为 using index