MySQL 逻辑架构

第一层：连接层。负责连接处理、授权认证、安全方案。
第二层：服务层。SQL接口、解析、优化、缓存。
第三层：引擎层。MyISAM、InnoDB、NDB、Memory ... ...
第四层：存储层。NTFS、ext2、ext3 ... ...

MySQL 数据文件

frm ：存放表结构。
myd：存放表数据。
myi ：存放表索引。

MySQL 瓶颈

CPU瓶颈：从磁盘读取数据时。
IO瓶颈：装入数据大于内存容量时。
服务器硬件的性能瓶颈

MySQL 存储引擎

查看提供什么存储引擎：show engines;
查看当前默认存储引擎：show variables like '%storage_engine%';

MySQL SQL执行顺序

FROM <left table>
ON <join condition>
<join type> JOIN< right table>
WHERE <where condition>
GROUP BY< group_ by_ _list>
HAVING <having condition>
SELECT
DISTINCT <select list>
ORDER BY <order by condition>
LIMIT< limit number>

---

MySQL 索引

索引是数据结构（特定查找算法的数据结构，BTree，Hash，full-text，R-Tree）。
索引两大功能：查询（Where）和排序（Order by）。

MySQL 索引分类
主键索引：特殊的唯一索引。一个表只能有一个主键索引。
单值索引：默认BTree索引。char、varchar创建索引时length可以小于实际长度，BLOB、TEXT必须指定length。
唯一索引：唯一（只能有一个值为null）。可以创建组合唯一索引。
组合索引：创建索引时选多个列。

MySQL 创建索引
创建 CREATE [UNIQUE] INDEX indexName ON mytable(columeName(length));
　　 ALTER mytable ADD [UNIQUE] index [indexName] ON (columeName(length));
删除 DROP INDEX [indexName] on mytable;
查看 SHOW INDEX FROM table_name;

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 相当于创建了usernname,city,age 、usernname,city 、usernname 。这样创建组合索引比三个列都创建单个索引效率要高。这就是"最左前缀"（简单地理解就是从最左边开始组合）。

MySQL 建立索引的时机
　一般来说在Where与Join中出现的列要建立索引，但也不是完全如此。
　MySQL只对< 、<= 、=、>、>= 、Between、IN 以及某些时候的LIKE（不是以通配符%和_开头作查询）才会使用索引。

MySQL 索引的优势与不足
优势：快速查找排序
不足：降低增删改速度，索引文件会占用磁盘空间。

---

MySQL 锁机制

从操作的类型分：
　读锁（共享锁）：可多锁同时读。
　写锁（排它锁）：阻塞。

从操作粒度分：
　表级锁（偏读）：开销小，加锁快，无死锁，粒度大，发生锁冲突的概率最高，并发低。偏向MyISAM存储引擎
　行级锁（偏写）：开销大，加锁慢，有死锁，粒度小，发生锁冲突的概率最低，并发高。
　页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MySQL 隔离级别，隔离等级，从宽松到严格
READ UNCOMMITTED：读未提交。隔离级别最低，并发性能最高
READ COMMITTED： 读已提交。
REPEATABLE READ： 可重复读。
SERIALIZABLE： 序列化。隔离级别最高，消耗资源最低，代价最高。

使用总结
什么都不防止：READ UNCOMMITTED
防止脏读：READ COMMITTED
防止不可重复读：REPEATABLE READ
防止幻读：SERIALIZABLE

查看当前隔离级别
show variables like 'tx_isolation'

MySQL 表级锁
表级锁加锁解锁
lock table 表名字 read(write), 表名字2 read(write)....
unlock tables;
查看表锁
show open tables;
查看表级锁的状态
show status like 'table%';
其中两个变量：
Table_locks_immediate：产生表级锁定的次数
Table_locks_waited：表级锁争夺而发生等待的次数

MySQL 行级锁
并发处理事务带来的问题
更新丢失（lost update）
　说明：后修改的修改了前面修改的。
　避免：不能同时访问同一个文件则可避免。
脏读（Dirty Reads）
　说明：事务A读取了事务B修改但未提交的数据。
　避免：事务隔离级别READ COMMITTED
不可重复读（Non-Repeatable Reads）
　说明：一个事务内两次读取的数据不一样
　避免：事务隔离级别REPEATABLE READ
幻读（Phantom Reads）
　说明：事务A读取到了事务B的新增数据
　避免：事务隔离级别SERIALIZABLE

锁定一行: select xxx... for update; 直到commit

查看行级锁状态
show status like 'innodb_row_lock%'
说明
Innodb_row_lock_current_waits：当前正在等待锁定的数量
Innodb_row_lock_time：启动到现在锁定总时间长度
Innodb_row_lock_time_avg：平均等待时间
Innodb_row_lock_time_max：最大等待时间
Innodb_row_lock_waits：启动到现在共等待的次数

注意！！索引失效，行锁变表锁

间隙锁
间隙：范围条件记录 - 已有记录=间隙（GAP）
InnoDB对符合条件的已有记录与间隙都会加锁。
危害：某些场景下会对系统性能造成很大影响。