mysql高级三sql性能优化+索引优化+慢查询日志
内容介绍
单表索引失效案例
0、思考题:如果把100万数据插入MYSQL ,如何提高插入效率 (1)关闭自动提交,只手动提交一次 (2)删除除主键索引外其他索引 (3)拼写mysql可以执行的长sql,批量插入数据 (4)使用java多线程 (5)使用框架,设置属性,实现批量插入 1、计算、函数导致索引失效 CREATE INDEX idx_name ON emp (NAME); EXPLAIN SELECT * FROM emp WHERE emp.name LIKE 'abc%'; EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; ----索引失效 2 LIKE以%开头索引失效 EXPLAIN SELECT * FROM emp WHERE NAME LIKE '%'; ----索引失效 3、不等于(!= 或者<>)索引失效 EXPLAIN SELECT * FROM emp WHERE emp.name = 'abc' ; EXPLAIN SELECT * FROM emp WHERE emp.name <> 'abc' ; ----索引失效 4、IS NOT NULL 和 IS NULL EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL; EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; ----索引失效 5、类型转换导致索引失效 EXPLAIN SELECT * FROM emp WHERE NAME='123'; EXPLAIN SELECT * FROM emp WHERE NAME= 123; ----索引失效 6、全值匹配我最爱 EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptid = 4 AND emp.name = 'abcd'; CREATE INDEX idx_age ON emp(age); CREATE INDEX idx_age_deptid ON emp(age,deptid); CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`); 7、最佳左前缀法则 EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' ; CREATE INDEX idx_age_name ON emp (age,NAME); EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd'; EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND emp.deptid=1 AND emp.name = 'abcd'; CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`); EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd' AND emp.age = 30; 8、索引中范围条件右边的列失效 CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`); EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.name = 'abc' AND emp.deptId>1000 ; CREATE INDEX idx_age_name_deptid ON emp(age,`name`,deptid); |
关联查询优化
1、数据准备
2、左外连接实例 (1)明确角色 (2)优化 EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; CREATE INDEX idx_class_card ON class(card); CREATE INDEX idx_book_card ON book(card); *使用LEFT JOIN,前面的是驱动表、后面是被驱动表 针对两张表的连接条件涉及的列,索引要创建在被驱动表上,驱动表尽量是小表
3、内连接实例 EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card; CREATE INDEX idx_class_card ON class(card); CREATE INDEX idx_book_card ON book(card); *使用INNER JOIN,驱动表、被驱动表不固定,mysql选择 MySQL优化器也会自动选择驱动表,自动选择驱动表的原则是:索引创建在被驱动表上,驱动表是小表。 4、分析4种查询sql(mysql5)
5、总结
|
其他优化
1、子查询优化 (1)获取非掌门人成员 #获取非掌门人成员 CALL proc_drop_index("atguigudb","emp"); CALL proc_drop_index("atguigudb","dept"); SELECT * FROM t_emp a WHERE a.id NOT IN (SELECT b.ceo FROM t_dept b WHERE b.ceo IS NOT NULL); EXPLAIN SELECT * FROM emp a WHERE a.id NOT IN (SELECT b.ceo FROM dept b WHERE b.ceo IS NOT NULL); #子查询优化NOT IN EXPLAIN SELECT * FROM emp a LEFT JOIN dept b ON a.id = b.ceo WHERE b.id IS NULL; (2)结论 尽量不要使用NOT IN 或者 NOT EXISTS,用LEFT JOIN xxx ON xx = xx WHERE xx IS NULL替代 2、排序优化 (1)实例 CALL proc_drop_index("atguigudb","emp"); CALL proc_drop_index("atguigudb","dept"); CREATE INDEX idx_age_deptid_name ON emp (age,deptid,`name`); #无过滤,不索引 EXPLAIN SELECT * FROM emp ORDER BY age,deptid; EXPLAIN SELECT * FROM emp ORDER BY age,deptid LIMIT 10; EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid; #顺序错,不索引 EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, `name`; EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, empno; CREATE INDEX idx_age_deptid_empno ON emp (age,deptid,`empno`); EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY `name`, deptid; EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age; #方向反,不索引 EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid DESC, `name` DESC; EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid ASC, `name` DESC;
无过滤,不索引 顺序错,不索引 方向反,不索引 3、mysql索引选择 EXPLAIN SELECT * FROM emp WHERE age =30 AND empno <101000 ORDER BY `name`; CREATE INDEX idx_age_empno ON emp (age,`empno`); CREATE INDEX idx_age_name ON emp (age,NAME); *当【范围条件】和【group by 或者 order by】的字段出现二选一时,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然。 也可以将选择权交给MySQL:索引同时存在,mysql自动选择最优的方案:(对于这个例子,mysql选择idx_age_empno),但是,随着数据量的变化,选择的索引也会随之变化的。 4、双路排序和单路排序 (1)双路排序(慢)
(2)单路排序(快) 它的效率更快一些,因为 5、分组优化
6、覆盖索引优化 总结
|
慢查询日志
1、如何对系统查询慢做索引优化 (1)找运维人员开启生产数据库慢查询日志 (2)等待1-2周时间,积累慢查询日志 (3)借助工具获取慢查询次数最多和查询时间最长的几个sql进行优化 (4)在生产数据库,使用EXPLAIN进行sql分析,找到瓶颈,创建索引优化 (5)关闭慢查询日志。 2、是什么 一种日志记录,查看哪些SQL超出了我们的最大忍耐时间值。 3、使用 (1)开启slow_query_log SET GLOBAL slow_query_log=1; SHOW VARIABLES LIKE '%slow_query_log%'; (2)修改long_query_time阈值 SHOW VARIABLES LIKE '%long_query_time%'; -- 查看值:默认10秒 SET GLOBAL long_query_time=0.1; -- 设置一个比较短的时间,便于测试 (3)运行sql (4)查看慢查询日志 (5)使用工具分析慢查询日志 -- 查看mysqldumpslow的帮助信息 mysqldumpslow --help -- 工作常用参考 -- 1.得到返回记录集最多的10个SQL mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log -- 2.得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log -- 3.得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log -- 4.另外建议在使用这些命令时结合 | 和more 使用 ,否则语句过多有可能出现爆屏情况 mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more |
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhggfjee
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01 -
怎样阻止微信小程序自动打开
PHP中文网 06-13