mysql 高级第三天：
回顾：
- 7 种join 的链接写法！
扩展掌门人的练习！
总结：写多表关联的步骤！
2. 索引的类型：单一索引：主键索引：符合索引：唯一索引：3. explain + sqlid: 判断表的读取顺序！type：system>const>eq_ref>ref>range>index>ALLkey_len：使用到索引的长度，通常这个值越大越好！公式：rows：列显示MySQL认为它执行查询时必须检查的行数。值越小越好Extra；Using filesort： 如果额外的列中出现了这个标识 ， 则表示排序没有使用到索引！解决方案：给排序字段添加索引create index idx_name on tableName();Using temporary: 表示分组没有使用到索引！Using index：表示使用了覆盖索引 ：覆盖索引【后面的检索条件，正好在查询项中也存在！】using join buffer ：表示关联字段没有使用索引！4. 函数，存储过程！ 最好能记住！java ： 有返回值的方法！函数定义:CREATE [OR REPLACE] FUNCTION <过程名>[(参数列表)] RETURN 数据类型 IS[局部变量声明]BEGIN可执行语句 【普通的sql】EXCEPTION异常处理语句END [<过程名>];java ：没用返回值的方法！存储过程定义:CREATE [OR REPLACE] PROCEDURE <过程名>[(参数列表)] IS[局部变量声明]BEGIN可执行语句EXCEPTION异常处理语句END [<过程名>];
重点：
- 删除索引的存储过程！{了解}
emp： 50万
dept: 1万
删除：drop index idx_name on tname;#功能： public void del(String table_schema ，String table_name){List<String> sList = SELECT index_name FROM information_schema.STATISTICS WHERE table_name=table_name AND table_schema=table_schema;for(int i=0;i<sList.size();i++){DROP INDEX sList.get(i) ON emp;}}2. 索引使用：必须记住！a. 全值匹配我最爱CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd';b. 最佳左前缀法则EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd'; # 跳过了deptId 导致后面的name 不能使用索引！或者EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd'; # 直接跳过了age ,后面的都不能使用索引！# 创建的索引列都存在！ mysql 的优化器会自动给你排序！EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid=4 AND emp.age=30 AND emp.name = 'abcd';EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid=4 AND emp.name = 'abcd' AND emp.age=30;EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name = 'abcd' AND deptid=4 AND emp.age=30;c. 计算、函数导致索引失效d. 范围条件右边的列索引失效SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ;SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abc' AND emp.deptId>20 ;如果有范围条件查询的话，那么我们将其放在后面：建立对应的复合索引即可!e. 不等于(!= 或者<>)索引失效f. is not null无法使用索引，is null可使用索引g. like以通配符%开头索引失效h. 类型转换导致索引失效3. 关联查询优化left JoinEXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;优化应该在on 条件后面！情况一：在class 上添加索引create index idx_card on class(card);情况二：在book 上添加索引create index idx_card on book(card);综上所述：建立索引的时候，应该在从表{被驱动表}上建立！inner JoinEXPLAIN SELECT SQL_NO_CACHE * FROM class INNER JOIN book ON class.card=book.card;会自动选择驱动表！mysql 没有full join ！4. 子查询优化：优化方案：看where 筛选条件关联条件 on所有的条件：id : 主键索引ceo : dept.ceo 对应的掌门人deptid = 部门Id 忽略！优化方案： 优化ceo！子查询：explain SELECT a.name ,(SELECT c.name FROM t_emp c WHERE c.id=b.ceo) AS ceoname FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id ;left Joinexplain SELECT a.name,c.name AS ceoname FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id LEFT JOIN t_emp c ON b.ceo = c.id ;临时表：explain SELECT ab.name,c.name AS ceoname FROM ( SELECT a.name,b.ceo FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id ) ab LEFT JOIN t_emp c ON ab.ceo = c.id ;能使用left join 就不要使用子查询！能够使用not in 的 就尽量替换成 LEFT JOIN xxx ON xx WHERE xx IS NULLNOT IN -->LEFT JOIN xxx ON xx WHERE xx IS NULL
排序、分组优化
order by ``` a. 无过滤 不索引！b. 顺序错，必排序！
c. 方向反 必排序
当在查询数据的时候，如果将排序的字段用上了索引，那么效率必然会高！
总结：
whereonorder by索引的选择：当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。
双路排序和单路排序 了解：双路排序：两次io单路排序：一次io 但是需要调整sort_buffer_size 的容量，避免发生多次io！提高order by 的排序速度：增大sort_buffer_size参数的设置 1M-8M增大max_length_for_sort_data参数的设置 1024-8192字节减少select 后面的查询的字段。 禁止使用select *修改配置文件！
<br />GROUP BY关键字优化<br />group by 先排序再分组，遵照索引建的最佳左前缀法则<br />当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置<br />where高于having,能写在where限定的条件就不要写在having中了<br />group by没有过滤条件，也可以用上索引。Order By 必须有过滤条件才能使用上索引。2.覆盖索引：<br />使用explain + sql ，在额外的选项中出现了 Using index ，则就是使用到了覆盖索引！
select * from emp where id = 1; 表示只查询一次就可以找到数据！ select id,name from emp where name = ‘zhangsan’; name 不是索引！ 表示查询两次！ 上述这个过程叫回表！ 【了解】
引出覆盖索引！ select sql_no_cache id,age,deptid from emp where name like ‘%abc’; # 只需要查询一次！
结论： 查询的时候 尽量 使用覆盖索引！{查询字段，尽量是索引字段}
3.慢查询日志 {了解}<br />a. sql 语句执行的过程中超出了预定时间！<br />b. 修改my.cnf<br />slow_query_log =1<br />slow_query_log_file=/var/lib/mysql/localhost-slow.log <br />long_query_time=3<br />log_output=FILE
c. 直接使用mysqldumpslow 进行分析： mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
总结：<br />上午<br />a. 针对是where 条件后面的索引优化！哪些能用索引，哪些不能用索引！
b. 关联查询优化on 条件 ，在被驱动表上建立索引！c. 能用left join 就不使用子查询！d. order by group by
下午：
1. 排序、分组优化a. 无过滤 不索引！b. 顺序错，必排序！c. 方向反 必排序2. 覆盖索引 如何使用！尽量把查询项，与帅选项做成索引！select id,name,sex,age,addr,stuNo,email,createTimne,updateTime from stu where name = 'zs';3. 慢查询 - 了解！
```
若有收获，就点个赞吧
0 人点赞
