附上学习资料
sql优化
1、查找需要优化的sql:
一、慢日志
二、Explain:
System
Cost
三、Profile
optimizer
2、需要优化的情况
explain using temporary (需要临时表)、 using filesort(需要外部排序)、 ,Using index condition(需要回表) seletctType 是All 需要优化 用不到索引:前模糊匹配 多列索引不满足最左前缀匹配 or 左右没全用到索引 对索引进行运算(类型转换,计算,函数)
3、sql优化
插入优化(批量插入,先禁用索引,禁用约束检查,如唯一性等) order by ,group by ,关联查询 ,嵌套查询优化(in not in,当子查询是sql时) limit 优化 count()优化 (count索引更快)
4、表结构优化:
分库分表,字段冗余,选择合适字段类型:数值,字符,日期 timestamp占四个字节,datetime 占八个字节 (较小类型够用,不用较大类型)
数据库和实例
MYSQL:
Oracle:
每个实例可以创建多个表空间,每个表空间下可以创建多个用户(同时用户也属于表空间对应的实例)和数据库文件,用户可以创建多个表(每个表随机存储在一个或多个数据库文件中)
innodb与myisam的区别:
innodb
支持外键、支持事务、支持行锁(也支持表锁)、mvvc、innodb存储引擎采用聚簇的方式即聚集索引,表的存储是按照主键的顺序进行存放的
myisam
不支持事务,只支持表锁,支持全文索引,数据文件和索引是分开存放的
其他
master thread / io thread / purge thread /page cleaner
lru /free /flush list 重做日志缓冲
提交时先写重做日志再修改数据 write ahead log
checkpoint
将脏页刷刷回磁盘 ,时机:
- 缓存不够用
- 重做日志不可用
- 时间到
- sharp fuzzy
master 线程
脏页的刷新 合并插入缓冲 undo页的回收
插入缓存(提高性能) ,两次写(可靠性,写失效)自适应hash索引 刷新邻接表
参数文件
- 错误日志
- 查询日志
- 慢查询日志
- 二进制日志(所有存储引擎,sql,对数据的修改情况,逻辑修改)
- 表空间文件
- 重做日志文件(单个存储引擎,物理修改,对page的修改情况)
4、
表空间 段 区 页 行
行记录格式 compact 默认 不存储null 值 只存储null标志位 varchar 字符 65532(行) char 也是代表字符长度
5、
B+树插入(分裂) 删除(合并)填充因子
/ 聚集索引、辅助索引 两张图
索引分类
- 主键索引:唯一非空索引,一个表只有一个主键索引
- 唯一索引:索引列中的值必须是唯一的,但是允许为空值。唯一索引和主键索引的区别是:唯一约束的列可以为null且可以存在多个null值。唯一索引的用途:唯一标识数据库表中的每条记录,主要是用来防止数据重复插入。
- 组合索引:多列值组成一个索引,用于组合搜索,效率大于索引合并,需遵循最左前缀原则
- 普通索引:基本的索引类型,没有唯一性的限制,允许为NULL值。
- 全文索引:只有在MyISAM引擎上才能使用,只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。是目前搜索引擎使用的一种关键技术,对文本的内容进行分词、搜索。
/ 选择性
/ 联合索引 覆盖索引 离散读取 顺序读取 /自适应hash索引 全文索引(不支持中文这些没有单词界定符的语言)
事务
锁的类型
行锁的三种算法
表锁
LOCK TABLES t_org
WRITE/READ ;
读读不互斥,读写互斥 需要在不同会话
意向锁也是表级锁
SELECT * FROM t_account
LOCK IN SHARE MODE; s锁
SELECT * FROM t_account
FOR UPDATE; x锁
mvcc(读)
死锁、阻塞
- redo ahead 持久性
- undo 回滚 ,原子性
- mvcc 一致性
行转列
SELECT SUM(b),SUM(g) FROM (
SELECT COUNT() AS b,0 AS g FROM t_user
WHERE user_type=‘男’
UNION ALL
SELECT 0 AS b,COUNT() AS g FROM t_user
WHERE user_type=‘女’
) t
Sum+case when then else end
SELECT
SUM(CASE WHEN user_type=‘男’ THEN c ELSE 0 END) ‘男’ , SUM(CASE WHEN user_type=‘女’ THEN c ELSE 0 END) ‘女’
FROM
( SELECT user_type,COUNT(*) c FROM t_user
GROUP BY user_type )
T
Sum+if(course=‘语文’, score, NULL)