MySql

MySql关联查询中的on和where

要回答这个问题，需要知道SQL语句的执行顺序。SQL并不是按照书写的顺序来依次执行，每一次的执行都会形成一个虚拟的表，但是这个虚拟的表对用户是不可见的。

• FROM语句，对FROM前后的表做笛卡尔积。形成虚拟表1。
• ON语句，对虚拟表1 进行筛选，筛选条件用的是join_coddition,然后形成虚拟表2。
• JOIN语句，如果是外连接如left join和right join 则on 筛选条件对关联表无效，仅会用来过滤被关联表，关联表中的所有数据都会显示，而被关联表中的数据如果没有不满足过滤条件则会置NULL，如果是inner join 则会同时过滤两张表，最终形成虚拟表3。
• WHERE语句 对虚拟表3进行筛选符合条件的形成虚拟表4.
• GROUP BY 对虚拟表4中的记录按照列进行分组，形成虚拟表5
• HAVING 对虚拟表5中的数据进行筛选，形成虚拟表6
• SELECT 选出虚拟表6中指定的列，插入到虚拟表7
• DISTINCT 对虚拟表7中的列进行去重，产生虚拟表8
• ORDER BY ：对虚拟表8进行排序，形成虚拟表9
• LIMIT 取出虚拟表9中指定行的数据，返回给用户

由此可以看出on 语句 总是先于where执行的，另外在inner join中 on语句对关联表和被关联表都会进行过滤，而在外连接中仅会过滤被关联表，而where条件是对on条件关联之后形成的表进行过滤。

char(N)和varchar(N)

CHAR(N) 用来保存固定长度的字符，N 的范围是 0 ~ 255，牢记，N 表示的是字符，而不是字节。VARCHAR(N) 用来保存变长字符，N 的范围为 0 ~ 65536， N 表示字符。

Mysql 中的DateTime和TimeStamp

DATETIME 最终展现的形式为：YYYY-MM-DD HH：MM：SS，固定占用 8 个字节。
MySQL 5.6 版本开始 DATETIME 和 TIMESTAMP 精度支持到毫秒；
TIMESTAMP 日期存储的上限为 2038-01-19 03:14:07，业务用 TIMESTAMP 存在风险
表结构设计时，每个核心业务表，推荐设计一个 last_modify_date 的字段，用以记录每条记录的最后修改时间。
推荐使用dateTime ，并且对于更新时间设置自动更新。

Mysql 主键自增锁

自增值是由mySql服务端产生的，多线程操作这个变量会存在线程安全问题，需要加锁。由参数innodb_autoinc_lock_mode 来控制加锁时长。

如 :

INSERT INTO ... VALUES (NULL,...),(NULL,...),(NULL,...);

在第三种情况下，并发能力最强，但是连续插入的三条数据，可能并不连续，需要注意。

数据库在文件系统中的表示

每当创建一个数据库的时候，会在数据库的数据目录下生成一个新的目录，这个目录的名称和数据库的名称是一样的，并且在这个目录下会生成一个名为db.opt的文件，这个文件包含了数据库的各种属性，如字符集和校验规则等。

表在文件系统中的表示

一个表分为两个部分：1、表的定义信息，2、表的数据信息。
对于表的定义信息，不论是innodb还是myisam存储引擎都会在该表所在的数据库目录下创建一个.frm文件，文件名称和表名一样。
对于表的数据信息，innodb会生成一个.ibd文件，文件名称和表名一样，存储索引和数据信息，也就是所谓的索引组织表。myisam而言没有表空间的说法，表数据都存放到对应的数据库子目录下，.MYD结尾的是数据文件，.MYI结尾的是索引文件。

慢sql的排查

数据库CPU报警很可能是慢sql导致的，但是会有这种现象，本来这个sql是不慢的，平时执行也是很快的，但是由于其他慢sql导致数据库CPU暴涨之后，这些平常正常的sql也变成了慢sql，对于排查问题造成困扰。因此看执行时间有时候并不一定准确。还需要看扫描行数和返回的记录数，从某种经验上来看，这两个信息其实比执行时间更重要，因为这才是比较根本的原因，这才是导致响应时间慢的原因。特别要注意扫描行数。

分页查询优化

select * from saf_client limit 100000,10

对于这种sql ，msyql会先查出 100010 然后取最后的10条，如果翻页特别严重，接口就会卡顿，影响用户体验。

可以进行如下改写，因为innodb是索引组织表，因此先查询id ，这一步无需回表，然后根据id关联查询其它数据，相当于索引直接匹配，查询很快。
select sc.* from saf_client sc inner join (select client_id from saf_client order by client_id limit 100000,10) t on sc.client_id = t.client_id

关联查询优化

mysql存在两种关联查询算法：

• 嵌套循环连接：从驱动表（关联表）中一次取出一行数据，然后逐行去被关联表中查询，最终返回满足条件的结果集。多用于查询被关联表的时候可以走索引这种场景。
• 基于块的嵌套循环链接：把驱动表的数据加入到join buffer中，然后从被驱动表中取出每一条数据进行匹配，返回满足条件的结果集。因为join buffer是有限制的，因此如果驱动表的数据多的话，会进行分段匹配。

基于这两个算法，因此对于关联查询的优化，主要包含两个原则：

• 小表驱动大表：不论是大表能不能利用到索引，都是比较好的，如果可以利用到索引，小表的数据集小，然后利用大表的索引去匹配。相当于小次数的索引查询。否则的话。如果没有索引，使用join buf，join buf中也是少量的数据，不会分段。
• 尽量利用索引，也就是尽量使用第一种算法。