mysql-黑马-day02- 优化与索引

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优化

1. 查看sql语句的执行频次，哪些操作执行的比较频繁(以插入还是查询为主)

show global status;
-- 查询操作次数
show global status like 'Com_______';
-- 查询Innodb存储引擎下操作的行的数量
show global status like 'Innodb_rows_%';

2. 定位效率低的SQL

   
   image.png

-- 查看实时状态-检测每个客户端正在执行的慢SQL
show processlist;

3. 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

4. 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;

5. 优化器

   
   image.png

四、索引的使用

• 索引能解决大多数mysql的查询性能问题。
• 正确创建索引，并且正确使用索引，才能提高查询效率。
• 避免索引失效:
• 创建索引

create index idx_username_usermobile_cityname on project_user_visit(visit_user_name,visit_user_mobile,visit_city);

1. 全值匹配，对索引中所有的列都指定具体值。

explain select * from project_user_visit where visit_user_name='天文' and visit_user_mobile='123' and visit_city='上海市';

image.png

2. 最左前缀法则

• 如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左列开始，并且不能跳过索引中的列。（查询条件需要包含索引中的最左列，且不能跳过索引中的某一列）

image.png
image.png

与查询条件出现的顺序无关，只与是否出现索引的最左列有关。

image.png

• 如果跳过了索引中的某些列，则只有前几列索引有效。如下面的例子中，其实只走了visit_user_name的索引。

  
  image.png

3. 范围查询右边的列，不能使用索引。

   
   image.png
   image.png

4. 不要在索引列上进行运算操作，否则索引将失效

   
   image.png

5. 字符串不加单引号会导致索引失效

• why：因为mysql会对字段进行隐式转换，而这个转换过程被认为是对字段的运算操作。导致索引失效。

6. 尽量查询覆盖索引（索引完全包含查询列），不要使用select *，避免回表查询。

7. 两个OR关联的条件，OR之后的条件没有索引，则整个查询都不走索引。

explain
select *
from project_user_visit
where visit_user_name = '天文'
   or visit_user_position_name = '促销员';

image.png

8. 以%开头的like查询不走索引，只加在后面走索引。

   
   image.png

• 解决方案：使用覆盖索引

  
  image.png

9. 如果Mysql评估使用索引比全表扫描更慢，则不走索引。

10. is null, is not null，有时走索引，有时不走索引。

• 需要看数据列，mysql会判断走索引还是全表扫描更快。
  • 比如某一列的值的数据基本都是null，那么在查询is null的时候，就不会走索引，而是全表扫描。

11. in走索引。not in 不走索引。---测试下来结果不对

• https://segmentfault.com/a/1190000023825926

我测试下来如果查询的列被索引字段覆盖了就都走，如果没覆盖就都不走
还要看in里面的内容，如果过长也不会走索引。

image.png

12. 尽量使用复合索引，而少使用单列索引。
    如: 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. 大批量插入数据

1. 在Innodb存储引擎下，使导入的主键是有序的。

   
   image.png

image.png

2. 关闭唯一性校验

   
   image.png

3. 手动提交事务

   
   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