参考尚硅谷msyql高级课程的笔记,仅供学习交流使用
1、简介
安装mysql liunx版
mysql服务
mysql字符集
设置大小写不敏感
mysql权限管理
第三章msyql逻辑架构简介
1、连接层
2、服务层
3、引擎层
4、存储层
MyiSAM和InnoDB
AliSql和AliRedis
查看sql执行周期
sql查询流程
sql执行顺序
常见join查询(其中)
七种连接join实例,第六个是全连接 union 是并集去重,第七个是a和b的独有并集
索引优化
索引简介
索引就是排好序的快速查找数据结构
索引优缺点
索引分类
单值索引
唯一索引
主键索引
复合索引
索引使用基本语法
索引结构
1、btree索引(重点)
2、hash索引
3、full-text全文索引
3、R-tree索引
btree索引
B+tree索引
b树和b+树区别
聚簇索引和非聚簇索引(聚集索引 innodb有)
索引使用规范
ps:where 条件中用不到的字段不创建索引
数据重复且分布平均的表字段,也不应该建立索引
索引效率,索引字段不重复的值除以表的总记录数
这个数值越接近于1表示效率越高,如果接近与50%,这种低效率索引不会提高查询速度。
性能分析Explain性能分析
返回的信息
表的读取顺序(id)
数据读取操作的操作类型(select_type)
那些索引可以使用
那些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
id
select_type
type查询的访问类型 关键字段
一般而言,大数据量的查询,级别应该达到range级别以上,
最好到ref
system>const>eq_ref>ref>range>index>ALL
const和eq_ref的区别
range
possible_keys
key可以判断索引是否失效
查询中如果使用了覆盖索引,该索引仅出现在key列表中,而不在possible_key中
key_len
explain之ref
被用于与索引比较查找结果的值,如果比较没有用到索引,为null
extra
出现using filesort
解决方法:通过索引字段去排序
using temporary
优化前
优化后
**using index **
优化调大连接缓存
存储过程
优化小案例
一、单表优化案例
优化方案一
三字段复合索引
创建索引 index idx_article_ccv on article(cattegory_id,commnents,views)
方案一索引部分使用,有部分索引失效,因为范围后的索引会失效
优化方案二、
create index idx_article_cv on article(category_id,views);
多表索引优化案例
左连接(右连接原理相同)
优化前
在哪个表上建立card字段索引?
在book表,右表上建立索引
在左表建立索引
原理:因为左连接需要加载左表全部数据,建立索引也需要访问所有数据
因此索引建立在右表,有时候我们需要根据已经建立的索引来决定使用左连接还是右连接。
三表索引优化案例
其它两个表,用上面的book(bookid card )class(classid card)
优化思路同双表,在book和phone的card建立索引
索引优化
建表语句
口诀
1、全值匹配我最爱(覆盖索引) 查询字段中被索引字段覆盖
最佳左前缀法则
4索引列上不能有范围查询
前缀不能出现模糊匹配
解决模糊查询两边加百分号导致所以失效的问题
创建索引
创建覆盖索引后查询
索引面试题分析
上面使用了覆盖索引
sql优化流程
**小表驱动大表原则,让小数据表先执行查询 **
排序优化
4.1版本后才有单路排序
单路排序和双路排序的详解
使用索引排序的情况和不使用索引排序(文件排序)的情况
慢查询日志
查看是否开启慢查询日志和开启
开启慢查询日志(一次性,作用范围是当前数据库)
永久开启慢查询日志
查看和设置慢查询阈值
修改阈值后需要重新连接或者新开会话才可以看到修改生效
设置开启慢查询日志后,可以通过查看指定路径下的路径查看运行过慢的sql语句
mysql日志分析工具 msyqldumpslow
存储过程(批量插入数据)
设置函数参数
创建函数
函数必须有返回值
返回一个长度为n的随机字符串
随机产生部门编号
存储过程可以没有返回值,并且要把自动提交关闭,一次性提交所有数据
emp表的存储过程
dept表的存储过程
调用存储过程
call + 存储过程名(参数)
利用show profile进行sql分析