一、逻辑架构
连接层：连接池、权限验证。服务器为每一个客户端请求创建一个线程或者分配一个空闲的线程（连接池），因此当多个请求（线程）共同操作同一个数据时可能会引起数据安全问题。
服务层：sql优化、缓存、sql解析。
引擎层：mysql5.5 以前默认使用的数据库引擎为MyISAM,5.5版本以后默认使用的搜索引擎为InnoDB.
存储层：文件系统。
二、事务
事务：具有四个特性（ACID特性）：原子性、一致性、隔离性、持久性。
①原子性：一个事务是一个最小执行单元，不可分割。要么全部成功，要么全部失败。
②一致性：一个事务必须使数据库从一个一致性状态转变为另一个一致性状态。
③隔离性：一个事务的执行不能被其他事务影响，每个并发事务之间的操作是隔离的，互不干扰的。
④持久性：事务一旦被提交，对数据库中数据的修改是永久性的，即使数据库发生故障也不会丢失提交的事务操作。
如果不考虑事务的隔离性，会发生的几种问题：
①脏读：一个事务在处理过程中，读取了另一个事务未提交的数据。
②不可重复读：一个事务多次查询却返回不同结果，是因为在多次查询间隔被另一事务修改了数据。
③幻读：用户A将表中的所有值都改为A，这时用户B插入了一条值为B的数据，用户A修改结束后发现自己好像还有一条数据没有修改。
mysql 事务隔离性级别
①Serializable(可串行化)：可避免脏读、不可重复读、幻读的发生。
②repeatable read（可重读读）:可避免脏读、不可重复读的发生。
③read commited(读已提交):可避免脏读的发生。
④read uncommited（读未提交）:最低级别，任何情况都无法保证
级别越高，执行效率越低。mysql 默认的是repeatable read 级别。设置数据库的隔离级别一定要在开启事务之前，且隔离级别的设置只对当前connection有效。
解决不可重复读只需锁住满足条件的行，解决幻读需要锁表。
相关sql：
查看隔离级别：show variables like '%tx_isolation%';
设置隔离级别：set session transaction isolation level read committed;
开启事务：begin/start transaction/begin work
事务回滚：rollback
事务提交：commit
查看是否自动提交： show variables like '%autocommit%'
取消自动提交：set autocommit=0;
还原点：savepoint
set autocommit=0;
insert into...;
savepoint s1;
insert into....;
savepoint s2;
insert into...;
savepoint s3;
rollback to savepoint s2
事务隔离级别为可重复读时，如果有索引（包括主键索引），以索引列为条件更新数据，会存在间隙锁、行锁、页锁的问题，如果没有索引，更新数据时会锁住整张表。
事务隔离级别为串行化时，读写数据都会锁住整张表。
三、mysql 逻辑架构：服务层
这一层主要功能是sql语句的解析、优化、缓存查询等。
发送用户请求的时候，会首先去尝试命中缓存。命中缓存则返回结果，否则执行sql 查询结果。
查询缓存是否开启：show variables like '%query_cache_type%'； OFF 为关闭，ON 为开启
开启缓存的方式:
1、Mysql 核心配置文件将query_cache_type=0 改为 query_cache_type=1。
2、需要设置缓存大小：set global query_cache_size=1024;
四、存储引擎
mysql 在存储数据时，每种搜索引擎存储数据的文件都有不同，但都会有一个后缀为frm的文件，这个文件（tableName.frm）存储的是表的结构。
1、MyISAM
Mysql 5.5 以前默认的存储引擎。MyISAM 存储的数据是由后缀分别为.frm/MYD/MYI组成。
①tableName.frm:存储的是tableName 的表结构。
②tableName.MYD:存储的是tableName的数据库文件。
③tableName.MYI:存储的是tableName的索引。
这些文件可以在mysql 数据存储目录中找到：C:\ProgramData\MySQL\MySQL Server 5.7\Data（默认安装的mysql在这下面）
可以通过navicat 执行命令：show variables like '%data%'；查询到这个目录。
执行:show variables like '%data%'
结果：
MyISAM 支持的是表级锁、因此支持全文检索，因为表数据和索引是存在不同的文件下（后缀MYD/MYI），因此也支持数据压缩。
压缩命令：myisampack -b -f tableName.MYI;压缩后产生新文件OLD 文件。
MyISAM 适用场景：
空间类应用：地图（MyISAM 默认提供了很多空间函数）
只读类应用 (MyISAM 支持表级锁，表级锁在写数据的时候会把整张表锁了，效率很低)
非事务型应用：日志，报表，数据仓库 (MyISAM 不支持事务)
2、InnoDB
Mysql 5.5 后默认的存储引擎。数据存储在后缀为frm/ibd 的两个文件中，数据库文件和索引是存在一个文件中的。
①tableName.frm:存储tableName 的表结构。
②tableName.ibd:存储tableName的数据库文件和索引文件。
这里又涉及到独立表空间和系统表空间。
1、独立表空间：tableName.frm,tableName.ibd 两个文件都存储在与上MyISAM引擎同样的目录下。
2、系统表空间：tableName.frm 存储的位置与独立表空间是一样的，但是它的数据库文件和索引文件存储的ibdata1文件中
。采用InnoDB 系统表空间建立的表，这些表的索引和数据库文件都存储在这一个文件（ibata1）中。
系统表空间无法简单的收缩文件大小且会产生IO瓶颈，而独立表空间可以通过optimize table tableName 收缩表空间（所谓收缩相当于整理磁盘）。
InnoDB 是支持事务的，且支持行级锁，所以并发程度相比于MyISAM的表锁更高。
MyISAM 只缓存索引，不缓存数据。而InnoDB 缓存索引也缓存真实数据。
3、CSV
数据以文本方式存储在文件中，可以通过excel 或其他工具编辑打开。构成：
①tableName.csv :文件存储内容
②tableName.csm:存储表的元数据（表状态、数据量等）
③tableName.frm：存储表结果
CSV 不支持索引，所有列都不能为null,虽然可以对文件进行直接编辑，但是局限性是很明显的。
4、Archive
只支持insert 和select 操作，只允许在自增ID列上加索引。适用日志和数据采集应用。
以zlib对表数据进行压缩，磁盘空间I/O 更少，数据存储在后缀为ARZ的文件中。构成：后缀frm和ARZ 两个文件。
5、Memory
也称HEAP存储引擎，数据保存在内存中，
支持Hash索引和BTree索引，所有字段都是固定长度varchar(10)=char(10)
不支持Blog和Text 等大字段。
使用表级锁，最大大小由max_heap_table_size 参数决定。
可使用Memory表做临时表。
6、Ferderated
提供了访问远程Mysql服务器上表的方法，本地不需要存储数据，只需要保存表结构和远程服务器的连接信息，数据全部放在远程服务器上。默认是禁止的。
mysql://user_name[:password]@hostname[:port_num]/db_name/table_name
五、锁
表级锁：开销小，加锁快，锁粒度大，并发最低，不会出现死锁的情况。
行级锁：开销大，加锁慢，锁粒度相对较小，并发最高，会出现死锁。
页面锁：开销、加锁时间、锁粒度和并发程度位于前两者之间。
1、MyISAM 表级锁有两种模式：
①表共享读锁：
加上读锁后的表A，会阻塞所有的写请求。
给表加此锁的事务：可以读A表，但是对A表的写操作会报错，对其他表进行读写会报错。
其他事务：可以读A 表，对A 表的写操作会陷入等待，操作其他表无影响。
sql：lock table 表名 read;
②表独占写锁：
给表加此锁的事务：可对该表CRUD操作，对其他表的操作会报错。
所有其他用户对表的读写操作都会被阻塞。
sql：lock table 表名 write;
2、InooDB 行锁
共享锁：读锁，当一个事务对某几行数据上读锁时，允许其他事务对这几行数据进行读操作，但不允许其进行写操作，其他事务可给这几行数据上共享锁，不可上排它锁。
排它锁：写锁，当一个事务对某几行数据上写锁时，不允许其他事务写，但允许读，不允许其他事务给这几行数据任何锁。
上共享锁：select * from 表 where 条件 lock in share mode;
上排他锁：selecr * from 表 where 条件 for update;
注意：
①两个事务不能锁同一个索引
②insert，delete，update 在事务中都会默认加上排它锁。
③行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
InooDB 的表锁：
和MyISAM 差不多，开启一个新事务的时候会解锁表
六、数据类型
浮点型：
float：存储空间：4字节，非精确的数据类型。
double：存储空间：8字节，非精确的数据类型。
decimal：每4个字节存9个数字，小数点占1个字节， 是精确的数据类型。
日期类型：timestamp（与时期有关）/ datatime
七、慢查询
mysql 把执行时间超过long_query_time 的SQL语句信息全部记录到日志中。默认慢查询日志是关闭的。
set global slow_query_log=1 -- 启用慢查询
set global long_query_time=0; -- 指定sql执行时间阈值，查过则放入日志
set global log_output='FILE,TABLE' -- 可选值：【TABLE】【FILE】【FILE,TABLE】存储为文件或数据库表
set global log_queries_not_using_indexes=1 -- 记录下未使用索引的sql语句
set global slow_query_log_file ='' -- 指定慢查询日志得存储路径及文件（默认和数据文件放一起）八、索引和执行计划
1、索引
索引是帮助Mysql高效获取数据的数据机构。
- 普通索引：一个索引只包含单个列。
- 惟一索引：索引列的值必须惟一，允许为空。
- 复合索引：一个索引包含多个列。
- 聚集索引：并不是一种单独的索引类型，而是一种数据存储方式。有不同的实现方式，InnoDB 的聚集索引就是在同一个结构中保存了B-Tree索引（技术上来说是B+Tree）和数据行。
- 非聚集索引:不是聚集索引，就是非聚集索引。
查看索引：show index from table_name
创建索引：
create [unique] index 索引名 on 表名(列名(length));
alter table 表名 add [unique] index 索引名 on 表名(列名(length))
删除索引：drop index [索引名] on 表名
2、执行计划
通过 explain +sql 语句，我们可以知道执行这条sql 语句时：表的读取顺序、数据读取操作的操作类型、使用了哪些索引等等。以此来分析查询语句或者表结构的性能瓶颈。
执行计划中包含的信息：
2.1 执行计划—id
select 查询的序列号，显示的是数字，表示查询中执行select 子句或操作表的顺序，三种情况：
- id相同，执行顺序由上至下
- id不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行。
- id 有相同有不同，先执行序号大的，相同需要的再由上至下执行。
2.2 执行计划—slect_type
查询的类型，主要用于区别普通查询、联合查询、子查询等的复杂查询。
| simple | 简单的select 查询，查询中不包含子查询或者union |
| primary | 查询中若包含任何复杂的子部分，最外层查询则被标记为primary |
| subquery | 在select 或where 列表中包含了子查询 |
| derived | 在from 列表中包含的子查询被标记为derived(衍生),mysql会递归执行这些子查询，把结果放在临时表里。 |
| union | 若第二个select 出现在union 之后，则被标记为union;若union包含在from 子句的子查询中，外层select 将被标记为derived。 |
| union result | 从union 表中获取结果的select |
2.3 执行计划—table
显示这一行的数据是关于哪张表的。
2.4 执行计划—type
显示的是访问类型，是较为重要的一个指标，结果值最好到最坏依次是：system>const>eq_ref>ref>range>index>All
system：表只有一行记录，这是const类型的特列，平时不会出现。
const:表示通过索引一次就找到了，const用于比较primary key 或者unique 索引。因为只匹配一行数据，所以很快。
eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条数据与之匹配，常见于主键或唯一性索引扫描。
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行。
range：只检索给定范围的行，使用一个索引来选择行。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于零一点，不用扫描全部索引。
all：遍历全表以找到匹配的行。
2.5 执行计划—possible_keys
实际使用的索引，如果为Null，则没有使用索引
2.6 执行计划——key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。key_len 为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。
不同字符编码占用的存储空间不同，latin1 占用一个字节，gbk 2个字节，utf8 3个字节。
char 类型+允许为Null=n*(utf8=3,gbk=2,latin1=1)+1(NULL)
char 类型+不允许为Null=n*(utf8=3,gbk=2,latin1=1)
varchar(n)变长字段+允许Null=n*(utf8=3,gbk=2,latin1=1)+1(NULL)+2
varchar(n)变长字段+不允许Null=n*(utf8=3,gbk=2,latin1=1)+2
整数/浮点数/时间类型的索引长度：
- NOT NULL=字段本身的字段长度
- NULL=字段本身的字段长度+1(因为需要有是否为空的标记，这个标记需要占用1个字节)
- datetime类型在5.6中字段长度是5个字节，datetime类型在5.5中字段长度是8个字节
总结：
- 变长字段需要额外的2个字节（varchar值保存时只保存需要的字符数，另加一个字节来记录长度，如果列声明长度超过255，则使用两个字节，所以varchar 索引长度计算的时候要加2），固定长度的字段不需要额外的字节。
- 而 null 都需要一个字节的额外空间，所以索引字段最好不要为null，因为null 让统计更加复杂并且需要额外的存储空间。
- 符合索引有最左前缀的特性，如果符合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定符合索引是否全部使用到了。
2.7 执行计划—ref
显示索引的哪一列被使用了
2.8 执行计划—rows
根据表统计信息及索引选用情况，大致估算出找到所需要的记录所需要读取得行数。
2.9 执行计划—extra
包含不适合在其他列中显示但十分重要得额外信息。
| 值 | 描述 |
| Using filesort | 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序” |
| Using temporary | 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。 |
| USING index | 是否用了覆盖索引 |
| Using where | 表明使用了where过滤 |
| Using join buffer | 使用了连接缓存： |
| Impossible where | where子句的值总是false，不能用来获取任何元组 |
覆盖索引（索引覆盖）：就是select 得数据只从索引中就能够取得，不需要读取数据行，mysql 可以利用索引返回select 列表中得字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建得索引覆盖。
九、mysql 优化
- 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则，指的是查询从索引得最左前开始并且不跳过索引中的列。
- 不在索引上做任何操作，否则会导致索引失效而转向全表扫描。
- 范围条件放最后：部分或全部索引会失效。
- 尽量使用覆盖索引：只访问索引查询，不查找数据行。
- 少用不等于：使用不等于（!=或<>）索引失效
- like 查询：以通配符开头，索引会失效。
- 字符串类型加引号：不加单引号，索引失效
- or改为union 效率更高。
- 尽量全值匹配：复合索引时，索引尽量全值匹配
- null/not 会对索引产生影响：表字段定义为非空，sql 通过条件is null 或者is not null 索引会失效。表字段未定义非空，则不影响。
十、高效批量导入
1、将某个表数据导出为txt文件：select * into outfile 'E:\\text.txt' from 表名字
2、将这个txt文件导入数据库表中：load data infile 'E:\\text.txt' into tabe 表名
十一、几个工具类
1、在系统运行时，修改表结构，而修改表结构会导致锁表，会导致大量用户阻塞无法访问。
思路：
- 创建一个表，修改表结构。
- copy原表数据到新表里面。
- 在原表上创建一个触发器，在数据copy过程中，将原表更新数据的操作全部更新到新表中来。
- copy完后，替换掉原表。
工具：pt-online-schema-change
2、慢查询分析工具
常用的慢查询日志分析工具：
mysqldumpslow，汇总除查询条件外其他完全相同的sql,并将分析结果按照参数中指定的顺序输出。这个工具必须登陆到Mysql服务器才能访问，不能显示执行计划
pt_query_digest ：是用于分析mysql慢查询的一个工具，与mysqldumpshow工具相比，py-query_digest 工具的分析结果更具体，更完善。
212
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
