MYSQL学习记录，比较全

附上学习资料

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)

列转行