逻辑架构

 

MySQL逻辑架构图

 

• MySQL逻辑架构整体分为三层，最上层为客户层，
• 并非MySQL所独有，诸如，连接处理、授权认证、
• 安全等功能均在这一层处理。
• MySQL大多数核心服务均在中间这一层，包括查
• 询解析、分析、优化、缓存、内置函数（时间、数学、
• 加密等），所有的跨存储引擎的功能也在这一层实现：
• 存储过程、触发器、视图等。
• 最下层为存储引擎，其负责MySQL中的数据存储
• 和提取，中间的服务层通过API与存储引擎通信，这
• 些API接口屏蔽了不同存储引擎的差异。

查询过程

查询过程时序图

存储引擎

 

支持的9种存储引擎

从上图我们可以查看出当前版本的MySQL 默认存储引擎是InnoDB, 其实在5.5版本之前，MyISAM是它的默认引擎，之后是InnoDB。我们项目用的是8.0.17 。

主流的引擎就两种：innodb和myisam

• MyISAM和InnoDB区别

• MyISAM不支持事务，而InnoDB支持。
• Myisam可以没有主键，InnoDB表必须有主键（用户没有指定的话会自己找或生产一个主键）
• 而 MyISAM表不支持外键，InnoDB支持
• MyISAM锁的粒度是表级，而InnoDB支持行（默认），表级锁。
• （InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁）
• MyISAM支持全文类型索引，而InnoDB不支持全文索引。(mysql 5.7后innodb支持全文索引)
• InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）
• 清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。
• Innodb存储文件有frm、ibd，而Myisam是frm、myd(my data)、myi(my index)
• Innodb：frm是表定义文件，ibd是数据文件
• Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
• MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择innodb表。当你的数据库主要以查询为主，相比较而言更新和写 入比较少，并且业务方面数据完整性要求不那么严格，就选择myisam表。

《MySQL高性能》上面有一句话这样写到:

不要轻易相信“MyISAM比InnoDB快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB的速度都可以让MyISAM望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用。

一般情况下我们选择 InnoDB 都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题的话，选择MyISAM也是一个不错的选择。但是一般情况下，我们都是需要考虑到这些问题的。

MYSQL数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型

• 数值类型

 

数值类型

注：

• M 的值跟 int(M) 所占多少存储空间并无任何关系
• TINYINT(M), M默认为4;
• SMALLINT(M), M默认为6;
• MEDIUMINT(M), M默认为9;
• INT(M),M默认为11,其实如果我们明确了无符号，那么可以设置为10，因为少了符号位;
• BIGINT(M),M默认为20.
• M表示最大显示宽度,不是用来限制INT列内保存值的范围的。建表若设置了zerofill(0填充), 会在数字前面补充0. int(M)的最大值和最小值与UNSIGNED有关

 

---

 

• 日期类型

 

日期类型

注：

• 每种日期时间类型都有一个有效值范围，如果超出这个范围，在默认的SQLMode下，系统会进行错误示，并将以“零值”来进行存储。
• datetime : 0000-00-00 00:00:00
• date :0000-00-00
• timestamp:00000000000000

• 字符/字符串类型

 

字符/字符串类型

注：

• char(N)用于存放固定长度的字符串，长度最大为255，比指定长度大的值将被截断，而比指定长度小的值将会用空格进行填补；
• varchar(N)用于保存可变长度的字符串，长度最大为65535，只存储字符串实际需要的长度，它会增加一个额外字节来保存字符串本身的长度，varchar使用额外的1~2字节来存储值得长度，如果列的最大长度小于或等于255，则使用1字节，否则就是使用2字节；（1个字节占8位，2的8次方是256（-128~127）；2个字节占16位，2的16次方为65536）
• char和varchar跟字符编码也有密切联系，lantin1占用1个字节，gbk占用2个字节，utf8占用3个字节。

索引失效场景

• 当查询条件存在隐式转换（字符串没加单引号，数字加了单引号）
• 索引失效使用like时通配符在前
• 在查询条件（只要有一个条件列没索引）中使用OR
• 对索引列进行函数或者数学运算
• 联合索引未遵循最左前缀原则
• 范围条件右边的列索引失效（< ,> between and）
• 例如INDEX（a,b,c）,where a=1 and b>2 and c=3,c是不走索引的
• 使用不等于（<>,!=）
• is null或者is not null

a.单列索引无法储null值，复合索引无法储全为null的值。

b.查询时，采用is null条件时，不能利用到索引，只能全表扫描。

为什么索引列无法存储Null值？

①.索引是有序的。NULL值进入索引时，无法确定其应该放在哪里。（将索引列值进行建树，其中必然涉及到诸多的比较操作，null 值是不确定值无法比较，无法确定null出现在索引树的叶子节点位置。）　

②.如果需要把空值存入索引，方法有二：其一，把NULL值转为一个特定的值，在WHERE中检索时，用该特定值查找。其二，建立一个复合索引。例如　create index IDX on table(col1,1); 通过在复合索引中指定一个非空常量值，而使构成索引的列的组合中，不可能出现全空值。

• 左连接查询或者右连接查询,查询关联的字段编码格式不一样
• 如果mysql觉得全表扫描更快时（数据少）

• Explain查看执行计划后主要字段详解：

• id: SQL执行的顺序的标识,SQL从大到小的执行,先执行的语句编号大

1、id相同：按从上到下的顺序执行

2、id不同：按id从大到小执行

3、id部分不同：先执行id大的，id相同的，按从上到下的顺序执行

• select_type:就是select的类型

SIMPLE简单的查询，没有子查询，也没有union

PRIMAR主查询，有子查询的外边的查询

UNIONUNION中的第二个或者后边的select

DEPENDENT UNION 指子查询中的第二个或者后边的select

UNION RESULTUNION 的整个查询

SUBQUERY子查询中的第一个SELECT

DEPENDENT SUBQUERY子查询中的第一个SELECT，取决于外面的查询

DERIVED派生表的SELECT, FROM子句的子查询

• table：显示这一行的数据是关于哪张表的
• partitions:分区
• possible_keys：显示可能应用在这张表中的索引
• type:

mysql在表中找到需要的行的方式，也叫访问类型（好的sql至少达到range级别，最好能达到ref）

all全表扫描

index全索引扫描

range给定索引范围进行扫描

ref表示连接，即扫描条件是某个常量，或者列

eq_ref类似于ref，区别是所使用的索引是唯一索引不存在相同的值

const,system 在查询时mysql的优化，where语句后被优化为一个常量进行查询

null MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

• key: 显示Mysql实际决定使用的索引
• key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
• ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
• rows: 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
• filtered：返回结果的行占需要读到的行(rows列的值)的百分比(只对index和all的扫描有效)
• extra:不适合在其他字段中显示，但是十分重要的额外信息

 

extra子项

MYSQL优化方案

• 索引

• 单表索引不超过6个；
• 单个索引不超过5列；
• innodb主键推荐自增列； 主键不该被修改； 字符串不应该做主键； 若不指定主键，innodb会使用唯一且非空值索引代替；
• 若是复合索引，区分度大的前置；
• 核心sql优先考虑索引；
• 区分度高的字段前置；
• 避免冗余或重复索引：合理创建联合索引：index(a,b,c)相当于：index(a),index(a,b),index(a,b,c)
• 不在低基数列建索引，如性别；
• 不在索引列进行数学、函数运算；
• 尽量不要使用外键；
• 不使用%前导查询，如like "%xxx"，无法使用索引；
• 不使用反响查询，如not in、not like，无法使用索引；
• 尽量要有主键。

• 字段

• 避免使用TEXT、BLOG类型；
• MySQL 内存临时表不支持 TEXT、BLOB 这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。而且对于这种数据，MySQL 还是要进行二次查询，会使 sql 性能变得很差，但不是说一定不能使用这样的数据类型。
• 将字符转化为数字；
• 使用TINYINT代替ENUM类型；
• 字段长度尽量按照实际进行分配，不要随意给一个大容量；
• 所有字段尽量not null;
• 索引 NULL 列需要额外的空间来保存，所以要占用更多的空间；进行比较和计算时要对 NULL 值做特别的处理
• 使用unsigned存储非负整数；
• INT类型固定占用4个字节存储；
• 小数类型为 decimal，禁止使用 float 和 double。
• 使用TIMESTAMP存储时间；因为timestamp使用4字节，datetime使用8字节，同时timestamp具有自动复制以及自动更新的特性。
• 禁止在数据库中存储明文密码

• SQL

• 禁止使用存储过程、触发器、视图等；
• 让数据库做最擅长的事，降低业务耦合度；
• 用小表驱动大表，尽可能减少JOIN中Nested Loop（两个表读一行数据进行两两对比）的循环次数，避免使用大表的join；
• 避免数据库中进行数学运算，数学运算和逻辑判断，无法使用索引；
• 减少与数据库的交互次数；
• 拒绝大sql，拆分成小sql，充分使用query cache，充分利用多核CPU.
• 使用in 代替 or,in 的值不超过1000个；
• 禁止使用order by rand(); 因为使用order by rand() 会将数据从磁盘读取进行排序，耗费大量IO和CPU，可以再程序中获取一个rand值，然后通过数据库中获取对应的值。
• 使用union all 而不是 union.
• 禁止单条sql语句同时更新多个表(跨表更新)；
• Update 中禁止使用left join
• 不使用select *;
• 不要使用count(常量)或者count(列名)来替代count(*),count(*)是SQL92定义的标准统计行数的语法，和数据库无关，和NULL和非 NULL也无关，而 count(列名)不会统计此列为 NULL 值的行
• 禁止使用test库;
• 默认情况下，mysql.db表中包含的行表示任意用户可以访问test数据库和test_开头的数据库。这些行的User字段的值为空，表示匹配任意用户。这意味着这些数据库（test数据库和test_开头的数据库）默认可以被任意用户使用（即使没有权限的用户）

• 利用延迟关联或者子查询优化超多分页场景

• 行为规范

• 禁止super权限应用账号存在；
• 对单表的多次order必须合并为一次操作；
• 不在业务高峰期批量更新、查询数据库