image.png
image.png
image.png
存储引擎是作用在表上的
image.png
image.png
- 事务
-- Innodb
-- 事物
-- 开启事务
start transaction;
-- 操作
insert into project_user_visit(id, visit_user_name)
values ('10', '喜欢天文的pony');
-- 提交事务
commit;
-- 回滚事务
rollback;
MyISAM
image.png
image.png
image.png
三、 SQL优化
- 查看sql语句的执行频次，哪些操作执行的比较频繁(以插入还是查询为主)
show global status;
-- 查询操作次数
show global status like 'Com_______';
-- 查询Innodb存储引擎下操作的行的数量
show global status like 'Innodb_rows_%';
定位效率低的SQL
image.png
-- 查看实时状态-检测每个客户端正在执行的慢SQL
show processlist;
-
explain分析执行计划
image.png
id
- 如果id值一样，则按照从上到下顺序查询，
- 如果id不一样，则按照数值从大到小查询表。
select_type
image.png
table
- 查询的是哪张表
type
image.png
image.png
possible_keys
- 可能用到的索引
key
- 实际用到的索引
key_len
- 索引的长度(越短越好)
rows
- 扫描的行数
extra
image.png
- show profiles分析SQL
-- 是否开启
select @@have_profiling;
-- 在当前Session会话开启profiling
select @@profiling;
-- 在当前会话开启profiling;
set profiling = 1;
-- 查看记录
show profiles;
image.png
- 各阶段时间分析
show profile for query [id]
image.png
show profile all for query 2;
show profile cpu for query 2;
优化器
image.png
四、索引的使用
- 索引能解决大多数mysql的查询性能问题。
- 正确创建索引，并且正确使用索引，才能提高查询效率。
- 避免索引失效:
- 创建索引
create index idx_username_usermobile_cityname on project_user_visit(visit_user_name,visit_user_mobile,visit_city);
- 全值匹配，对索引中所有的列都指定具体值。
explain select * from project_user_visit where visit_user_name='天文' and visit_user_mobile='123' and visit_city='上海市';
image.png
- 最左前缀法则
- 如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左列开始，并且不能跳过索引中的列。（查询条件需要包含索引中的最左列，且不能跳过索引中的某一列）
image.png
image.png
与查询条件出现的顺序无关，只与是否出现索引的最左列有关。
image.png
如果跳过了索引中的某些列，则只有前几列索引有效。如下面的例子中，其实只走了visit_user_name的索引。
image.png
范围查询右边的列，不能使用索引。
image.png
image.png
不要在索引列上进行运算操作，否则索引将失效
image.png
字符串不加单引号会导致索引失效
- why：因为mysql会对字段进行隐式转换，而这个转换过程被认为是对字段的运算操作。导致索引失效。
尽量查询覆盖索引（索引完全包含查询列），不要使用select *，避免回表查询。
两个OR关联的条件，OR之后的条件没有索引，则整个查询都不走索引。
explain
select *
from project_user_visit
where visit_user_name = '天文'
or visit_user_position_name = '促销员';
image.png
以%开头的like查询不走索引，只加在后面走索引。
image.png
解决方案：使用覆盖索引
image.png
如果Mysql评估使用索引比全表扫描更慢，则不走索引。
is null, is not null，有时走索引，有时不走索引。
- 需要看数据列，mysql会判断走索引还是全表扫描更快。
- 比如某一列的值的数据基本都是null，那么在查询is null的时候，就不会走索引，而是全表扫描。
- in走索引。not in 不走索引。---测试下来结果不对
我测试下来如果查询的列被索引字段覆盖了就都走，如果没覆盖就都不走
还要看in里面的内容，如果过长也不会走索引。
image.png
- 尽量使用复合索引，而少使用单列索引。
如: create index idx_xx on tab(a,b,c); 相当于建立了三个索引
- a
- a + b
- a + b + c
如果分别在单个索引上建立索引，则只会走一个最佳的索引，不会每个索引都走。所以效率没有组合索引高。
查看索引的使用情况
-- 查看索引的使用情况
show global status like 'Handler_read%';
image.png
五、 SQL优化
1. 大批量插入数据
在Innodb存储引擎下，使导入的主键是有序的。
image.png
image.png
关闭唯一性校验
image.png
手动提交事务
image.png
2. 优化insert语句
image.png
- values(),(),()组合在一起，使用一条sql插入。
- 事务提交改为手动提交，并批量开启事务，如每1W条提交一次。
- 主键顺序插入。
3. 排序优化
image.png
image.png
image.png
4. group by语句优化
image.png
5. 优化嵌套查询
使用多表连接查询代替子查询
image.png
6. OR的优化
- 需要保证OR的每个查询条件都有索引，如果有一个条件没有索引，则整个OR条件都不走索引。
- 使用union代替OR
7. 优化分页查询
image.png
8. 使用sql提示
image.png
image.png
image.png
