mysql后续

SELECT * FROM test03;

ALTER TABLE test03 ADD INDEX idx_test_c1234(c1,c2,c3,c4);
SHOW INDEX FROM test03;

EXPLAIN SELECT * FROM test03 WHERE c1='a1';
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 = 'a2';
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 = 'a2' AND c3 = 'a3';
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 = 'a2' AND c3 > 'a3' AND c4 = 'a4';
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 = 'a2' AND c4 = 'a4' AND c3 = 'a3' ;
EXPLAIN SELECT * FROM test03 WHERE c4='a1' AND c3 = 'a2' AND c2 = 'a4' AND c1 = 'a3' ;
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 = 'a2' AND c4 > 'a4' AND c3 = 'a3' ;
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 = 'a2' AND c4 = 'a4' ORDER BY c3; #c3用到了但是c3的作用在于排序
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 = 'a2' ORDER BY c3;  #3个
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 = 'a2' ORDER BY c4;  # using filesort 
# 定值，范围还是排序，一搬order by 是给个范围 group by 基本上都需要进行排序，会有临时表产生


# 分析 
#1.观察，至少跑1天，看看生产的慢sql情况
#2.开启慢查询日志，设置阙值，比如超过5秒钟的就是慢sql，将他抓取出来
#3.explain + 慢sql分析
#4.show profile
#5.运维经理orDBA ，进行sql数据库服务器的参数调优

1.慢查询的开去并捕获
2.explain + 慢sql分析
3.show PROFILE 查询sql在服务器里面的执行细节和生命周期情况
4.sql数据库服务器的参数调优

小表驱动大表
查询优化


SELECT * FROM tbl_emp;
SELECT * FROM tbl_dept;
SELECT * FROM tbl_emp e WHERE e.deptid IN (SELECT id FROM tbl_dept d);

SELECT * FROM tbl_emp e WHERE EXISTS (SELECT 1 FROM tbl_dept d WHERE d.id = e.deptid);
SELECT * FROM tbl_emp e WHERE EXISTS (SELECT 3 FROM tbl_dept d WHERE d.id = e.deptid);
SELECT * FROM tbl_emp e WHERE EXISTS (SELECT 'X' FROM tbl_dept d WHERE d.id = e.deptid);

永远小表驱动大表

类似嵌套循环 NestedLoop

order by 关键字优化

　　

 

 

 

 

 

 

• order by 字句，尽量使用index方式排序，避免使用Filesort方式排序
• 尽可能在使用索引列上完成排序操作，遵循索引键的最佳左前缀
• 如果不再索引列上，filesort有两种算法
  • mysql就要启动双路排序和单路排序
    • 双路排序
      • mysql 4.1 之前使用双路排序，字面意思就是扫描两次磁盘最终得到数据
      • 读取行指针和order by列，对他们进行排序，然后扫描已经排好的续
    • 单路排序
      • 从磁盘读取查询需要的所有列，按照order by 列在buffer对他们进行排序，排序扫描后的
      • 列表进行输出效率快避免了第二次读取数据。并且把随机io变为了顺序io，但是会使用更多空间，因为他把每一行都保存在内存中了。
    问题： 由于单路是后出的，总体而言好过双路，但是用单路有问题

    • 

  • 优化策略：
    • 
  • 总结：
    • 为排序使用索引
• 　　　　

  

   

   

   

   

 

 

 

SHOW VARIABLES LIKE '%slow_query_log%';
SET GLOBAL slow_query_log = 1;
#主机-slow.log
SHOW VARIABLES LIKE '%long_query_time%'; #默认10秒
SET GLOBAL long_query_time = 3;
SHOW  GLOBAL VARIABLES LIKE '%long_query_time%';


SELECT SLEEP(4);
#/var/lib/mysql/Zyp-slow.log 主机名-slow.log 路径
#cat 主机名-slow.log  查看具体语句

SELECT * FROM staffs;
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
#mysqldumpslow --help mysql提供日志查询工具

mysqldumpslow --help;