1.为什么要进行sql优化
2.Mysql安装启动配置(CentOS7)
1)版本介绍与选择
2)mysql安装-rpm
2.1检查服务器msyql安装情况,有就先卸载自带的mysql【Centos7 默认安装mariadb】
rpm -qa|grep 软件名字 【检查命令】
rpm -e --nodeps 软件包名 【卸载命令】
yum remove 软件包名 【卸载命令】
2.2下载安装包
2.3解压安装包到/usr/local/mysql
2.4按照顺序安装rpm安装包
2.5验证是否安装成功【查看mysql版本】
2.6mysql常用命令
2.7设置密码
2.8登录
2.9设置root用户远程连接权限和密码
2.10开放端口
3.逻辑分层,存储引擎,解析过程
1)逻辑分层
2)存储引擎
2.1常用引擎(InnoDB, MyISAM)
2.2区别
2.3存储结构
3)解析过程
3.1sql编写过程:
3.2sql解析过程:
4.索引
1)什么是索引
2)索引类型
3)创建索引
4)删除索引
drop index 索引名 on 表名;
5)查询索引
show index from 表名;
6)不适合创建索引的字段
5.执行计划
1)数据准备
2)语法
explain sql语句;
3)explain信息详解 id
id:sql语句执行的编号
id值相同,从上往下顺序执行。这个顺序受表数据量的大小影响,先查数据量小的,后查数据量大的
id值不同:先执行id值大的
4)explain信息详解 select_type
5)explain信息详解 table
6)explain信息详解 type
type 索引类型
常用到的类型system > const > eq_ref > ref > range > index > all
system 性能最高,all性能最低。 实际项目中达到 ref > range 性能就行
system : 表只有中一条数据的主查询
const : 查询结果只有一条数据的sql ,并且索引类型必须为主键索引或者唯一索引
eq_ref : 查询结果可以有多条数据,但满足where判断条件的每一条数据必须是唯一的一条数据(不能多条也不能为0条)。
mysql explain select t.tname from teacher t,teacherCard tc where t.tid = tc.tid;
ref : 索引查询返回匹配所有行(0条,多条)
mysql explain select t.tname from tab_teacher t where t.tname = 'ta';
range : 检索指定范围的行,where后面是一个范围查询(between , in , > , < 等 其中in可能会导致索引失效而变成 all)
mysql explain select tc.tcdesc from tab_teacher_card tc where tc.tid between 1 and 2;
index : 查询全部索引的数据
mysql explain select t.tid from tab_teacher t;
all :查询全部表的数据(sql 将表的所有数据都查了一遍) ,没有用到索引时常出现
mysql explain select st.stname from tab_school_timetable st;
7)explain信息详解 possible_keys
8)explain信息详解key
9)explain信息详解key_len
10)explain信息详解ref
11)explain信息详解rows
12)explain信息详解extra
准备工作
using filesort : 性能损耗大,需要额外的查询(排序) ,常见于 order by 语句中
using temporary:性能损耗大,用到了临时表,一般出现与 group by 语句中
using index: 性能提升,只从索引中查询数据,不需要回表查询
using where: 进行了回表查询
impossible where : where 查询条件永远为fasle
Using join buffer : MySQL引擎使用了连接缓存,表示sql语句太烂,性能低下
6.慢查询
1)慢查询有什么用
2)检查是否开启了慢查询
3)开启慢查询
4)慢查询阈值
5)查看超过慢查询阈值的sql次数
6)查看超过慢查询阈值具体的sql信息
1.查看slow_query_log_file 日志文件
2.使用mysqldumpslow工具
7.优化总结
- 复合索引保证最佳左前缀原则
- 小表驱动大表
- 索引建立在经常查询的字段上
- 复合索引,尽量使用全索引匹配(说明:假设使用了三个字段建立了一个复合索引,在sql查询中尽量让三个索引都用到)
- 不要在索引上进行任何操作(计算、函数、类型转换),否则索引失效
- 复合索引不能使用不等于( != , <> )和 is null, is not null ,否则索引失效
- like 尽量以 ‘常量’ 开头,不要使用 ‘%x%’ ,否则索引失效
- 尽量不要使用or ,否则索引失效
- 如果必须使用到索引失效的情况,尽量使用索引覆盖(using index),可能会使索引生效,达到性能优化
- 将含有in的范围查询放到where条件的最后面,防止索引失效(尽量不使用in)
- 连接查询 a.t = b.t 的情况下,将表数据量小的放在左边,表数据量大的放在右边会提高性能
- 连接查询 a.t = b.t 的情况下,将 a 表 t 字段加索引会提高性能
- 对于左外连接给左表加索引,右外连接给右表加索引
- exist 和 in,如果主查询的数据集大,则使用in,如果子查询的数据集大,使用exist
- 提高 order by 查询的策略
a、选择使用单路、双路;调整buffer的容量大小 b、避免使用 select * ... c、保证排序字段的 排序一致性