mysql基本介绍
mysql配置文件
1.二进制文件log-bin 【主从复制】
2.错误日志log-error 【默认是关闭的,记录严重的警告和错误信息】
3.查询日志log 【默认关闭,记录查询的sql,开启回影响mysql性能,因为记录日志需要时间】
4.数据文件 【.frm (存放表结构) .MYD (存放表数据) .MYI (存放表索引 )】
5.配置文件 【window(my.ini)linux (/etc/my.cnf)】
mysql存储引擎
innoDB和MyISAM (常用)
mysql索引
单值索引 | 唯一索引 | 复合索引 |
---|---|---|
一个索引只包含单个列,一个表可以有多个单值索引 | 索引列的值是唯一的,单可以为空值 | 一个索引包含多个列 |
mysql索引结构
Btree索引 | 哈希索引 | 全文索引 | R-tree索引 |
---|
哪些情况需要建立索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的外键需要建立索引
- 频繁更新的字段不适合建立索引【因为更新的不只是数据,还更新索引,加重了io负担】
- where条件里用不到的字段不创建索引
- 查询中排序的字段需要建立索引
- 查询中统计或分组需创建索引
哪些情况不需要创建索引
- 表记录较少
- 经常增删改
- 数据重复且分布较平均的字段
MySQL常见瓶颈
- cpu: cpu饱和的时候一般发生在数据存入内存或者发生io的时候
- io: io瓶颈一般发生在存入数据远大于内存容量的时候
- 服务器硬件内存瓶颈:top,free等来查看系统性能
Explain
1.explain是什么?
答:使用explain可以模拟并优化执行sql语句顺序,从而知道mysql是怎么执行sql语句的,分析sql语句,从而达到优化sql语句
2.能干嘛?
- 表的读取顺序
- 哪些索引可以使用
- 哪些索引实际被使用
- 表之间的引用
- 每张表有多少记录被优化器查询
怎么用?
答:Explain+sql语句
Expalin查询包含的信息
id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
---|---|---|---|---|---|---|---|---|---|
select查询的序列号,也就是sql语句执行顺序 。id相同,则顺序从上到下执行。否则id越大优先级越高 | 查询的类型,主要是区别查询是子查询,联合查询,普通查询等 | 查询时有那些表 | 显示查询类型 | 显示表有哪些索引可用,但是不一定会用到这些索引 | 实际使用的索引,如果为null则没有使用索引 | 表示索引中使用的字节数 | 显示索引的哪一列被使用 | 显示查询的行数 | 包含了不适合在其他列显示但十分重要的信息 |
select_type
SIMPLE | PRIMARY | SUBQUERY | DERIVED | UNION | UNION RESULT |
---|---|---|---|---|---|
简单的查询 | 主键 | 在select或者where包含了子查询 | 在from表里包含的子查询,mysql会递归的执行这些语句并放在临时表里 | 联合查询 | 从nuion表获取的结果的select |
type
system | const | eq_ref | ref | range | index | all |
---|---|---|---|---|---|---|
表只有一行数据,可以忽略 | 通过索引一次就找到了,只匹配一天数据 | 常见于主键和唯一索引扫描 | 非唯一性索引扫描,返回匹配某个单独值得行 | 返回在给定范围得行,如in,between等 | 全表扫描 | 全表扫描,区别于index是,index是从索引读取的数据,all是读的硬盘得数据 |
一般能达到range就可以了
Extra
Using filesort | Using temporary | Using index | Using where | Using join buffer | impossible where | select tables optimized | distanct |
---|---|---|---|---|---|---|---|
说明MySQL会对数据重排序,出现这个要注意 | 使用临时表保存结果,通常出现于order by和group by | 表示select语句使用了覆盖索引,效率不错!!! | 使用了where过滤 | 使用了连接缓存 | where子句得值总是false | 在没有group by的情况下,基于索引优化min/max | 优化distanct,在找到第一个匹配的元组后不查相同的记录 |
慢日志查询
查看是否开启慢日志查询
show variables like "%show_query_log%";【默认关闭】
开启慢日志
set global slow_query_log=1;
查看当前多少时间才算慢
show variables like "long_query_time%";【默认好像是10秒】
设置慢查询时间
set global long_query_time=3;
日志分析工具mysqldumpslow
s | c | l | r | t | al | ar | at | -t | -g |
---|---|---|---|---|---|---|---|---|---|
表示按照什么方式排序 | 访问次数 | 锁定时间 | 返回记录 | 查询时间 | 平均锁定时间 | 平均返回记录数 | 平均查询时间 | 返回前面的多少条数据 | 后面搭配一个正则匹配模式,大小写不敏感 |