1. Mysql

数据库（粗粒度学习）

1.1. 索引简介

定义：索引是帮助mysql高效获取数据的--数据结构（自己理解：排好序的快速查找的数据结构） 索引并非存储在内存中，一般以文件形式存储在磁盘上

1.1.1. 为什么维护逻辑删除字段

为了数据分析
为了索引树，经常物理删除数据容易造成页分裂

1.1.2. 索引分类

单值索引:一个索引包含单个列
唯一索引:索引列的值必须唯一，允许有空值
复合索引:一个索引包含多列

1.1.3. 什么时候需要创建索引

需要创建
1. 主键自动创建唯一索引
2. 频繁作为查询条件的字段
3. 排序字段
4. 查询中统计或分组字段
无须创建
1. 表记录太少
2. 经常增删改的表
3. 数据重复且分布平均的字段，比如类别、性格无须创建

1.1.4. 索引优/劣势

优势
- 提高数据检索效率，降低数据库的IO
- 通过所以列对数据进行排序，降低数据排序成本，降低CPU消耗
劣势
- 影响插入/修改/删除性能
- 索引列要占用空间

1.2. 性能分析Explain

mysql常见瓶颈

CPU负担重，数据在装入内存或者从磁盘读取数据时
IO负担重，装入数据远大于内存容量时
服务器硬件性能瓶颈

1.2.1. Explain

查询执行计划，再执行sql之前增加explain

id

select查询的序列号，表示查询中执行select子句或操作表的顺序

值三种情况

相同，执行顺序由上至下
id不同，如果是自查询，id序号递增，id值越大优先级越高，越先被执行
id相同不同，id值越大，优先级越高，越先执行；相同，从上往下顺序执行

select_type

查询类型，如普通查询，子查询，联合查询等等

SIMPLE
PRIMARY 主键查询
SUBQUERY
DERIVED(临时表，衍生表)
UNION 联合查询
UNION RESULT

type(关键参数)

访问类型,现实查询使用了何种类型，从最好到最差排序为：system>const>eq_ref>ref>range>index>ALL.（优化至少达到range级别，最好能达到ref.）

system：表只有一行记录，等于系统表，const类型的特例
const：通过索引一次就找到。会转换为常量
eq_ref：唯一性索引扫描，每个索引键，表中只有一条记录与之匹配；主键索引
ref：非唯一性索引扫描，匹配某个单独值的所有行。
range：只检索给定范围的行，使用一个索引来选择行，一般是where语句中出现了between,<,>,in等查询
index：full index scan,index与all区别为index类型只遍历索引树，index也是读全表，但是读的是索引树，而All是从硬盘中读全表
ALL：从硬盘中读全表

possible_keys

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引被列出，不一定被查询实际使用

key

实际使用到的索引，如果为NULL,则没有使用索引
若查询中使用了覆盖索引，则该索引仅出现在key列表中

key_len

表示索引中使用的字节数，通过该列计算查询中使用的索引的长度，不损失精度性的情况下，长度越短越好
值为索引字段的最大可能长度，并非实际使用长度，根据表定义计算而得，不是通过表内检索出

ref

显示索引的哪一列被使用。哪些列或常量被用于查找索引列上的值

rows

根据表统计信息及索引选用情况，大致估算出找到所需记录需要读取的行数

extra

包含不再其他列中，但十分重要的额外信息

using filesort：mysql会对数据使用一个外部的索引排序，无法利用索引完成的排序操作称为“文件排序”。性能极差
using temporary：需要建立临时表来暂存中间结果，性能也较差
using index：sql所需要的数据均在一棵索引树上，无需访问实际的行记录。此类sql往往查询性能较好
using where：sql使用了where条件过滤数据
using join buffer：需要进行嵌套循环计算，也往往较差，需要优化
impossible where
select tables optimized away

1.3. 索引优化

索引失效场景

全值匹配
未遵循最佳左前缀原则
不再索引上有函数操作
存储引擎不能使用索引中范围条件（>,<）右边的列
减少select *,按需取数据
使用不等于（!=或者><）的时候无法使用索引会导致全标扫描
is null,is not null也无法使用索引
like通配符开头（%abc...）会导致索引失效；（使用覆盖索引可以解决此问题，如age加索引，select age from table where name like '%qwe%'可以使用索引）
字符串不加引号导致索引失效（mysql做类型转换，如int转换为字符串）
少用or,用or连接也会导致索引失效

1.4. 锁机制

从数据操作的类型分类（读/写）
1. 读锁：共享锁，针对同一份数据，多个读操作可以同时进行而不会相互影响
2. 写锁：排他锁，当前写锁没有完成前，它会阻塞其他写锁和读锁
从对数据操作的粒度分类（表锁，行锁）
1. 表锁：偏读，偏向MyISAM存储引擎，开销小，加锁快，无死锁
2. 行锁：偏写
3. 页锁

更详细可以查看

系统学习Mysql