13518219792
sql_mode 是一个容易忽视的变量，默认情况下为空，可以忍耐一些非法操作，在生产环境中，必须将其设置为严格模式，在开发测试环境中配该变量也是很有必要的，因为这样可以在生产之前发现问题。
创新互联基于成都重庆香港及美国等地区分布式IDC机房数据中心构建的电信大带宽，联通大带宽，移动大带宽，多线BGP大带宽租用,是为众多客户提供专业服务器托管报价，主机托管价格性价比高，为金融证券行业川西大数据中心，ai人工智能服务器托管提供bgp线路100M独享，G口带宽及机柜租用的专业成都idc公司。
sql_mode 常用值如下：
和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离，这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
连接层
最上层是客户端和连接服务，包含本地 socket 通信和 tcp/ip 通信，主要完成连接处理、授权认证及相关的安全方案，该层引入了线程池，为授权用户提供线程，还实现了 ssl 安全链接。
服务层
引擎层
存储引擎层，负责了数据的存储和提取，服务器通过 API 与存储引擎进行通信。
存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
开启诊断分析工具
set profiling=1;
显示最近的几条查询
show profiles;
查看 SQL 的执行步骤
show profile cpu,block io for query 1;
FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT 查看支持的存储引擎
show engines;
查看当前默认的存储引擎
show variables like '%storage_engine%';
InnoDB
InnoDB 是 MySQL 默认的事务型引擎，用来处理大量的短期事务，除非有特别的原因需要用到其他存储引擎，否则优先考虑 InnoDB。
MyISAM
MyISAM 提供了大量的特性，包括全文检索、压缩、空间函数等，但 MyISAM 不支持事务和行级锁，缺点是崩溃后无法安全恢复。
Archive
Archive 档案存储引擎只支持 INSERT 和 SELECT 操作，在 MySQL5.1 之前不支持索引；
Archive 表适合日志和数据采集类应用；
根据英文的测试结论来看，Archive 表比 MyISAM 表要小大约 75%，比支持事务处理的 InnoDB 表小大约 83%。
Blackhole
Blackhole 引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。
CSV
CSV 引擎可以将普通的 CSV 文件作为 MySQL 表来处理，但不支持索引， CSV 可以作为一种数据交换的机制，CSV 引擎存储的数据可以被文本编辑器、execl 读取。
Memory
如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用，Memory 表至少比 MyISAM 表要快一个数量级。
Federated
Federated 引擎是访问其他 MySQL 服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。
| 对比项 | InnoDB | MyISAM |
|---|---|---|
| 外键 | 支持 | 不支持 |
| 事务 | 支持 | 不支持 |
| 行表锁 | 行锁，操作时只锁定操作的那一行，不会对其他行产生影响，适合于高并发 | 表锁，即使只操作一行也会锁定整个表，不适合高并发 |
| 缓存 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 | 只缓存索引，不缓存真实数据 |
| 关注点 | 并发写、事务、更大资源 | 节省资源、消耗少、简单业务 |
| 默认安装 | Y | Y |
| 默认使用 | Y | N |
| 自带系统表使用 | N | Y |
分库分表
SQL 优化
建立索引
调整 my.cnf 优化服务器及配置参数
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引；
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上；
虽然索引提高了查询的效率，但是也降低了更新的效率，因为更新表时，不仅要插入数据，同时还要保存一下索引文件每次更新添加了的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
.jpg)
如图所示，磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3
P1 表示小于 17 的磁盘块，P2 表示介于 17 和 35 之间的磁盘块，35 表示大于 35 的磁盘块
查找过程
如果要查找数据项 29，首先将磁盘块 1 加载到内存，此时发生一次 IO，利用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 加载到内存，此时发生一次 IO，利用二分查找确定 29 在26 和 30 之间，锁定磁盘块 3 的 P2 指针，通过磁盘块 3 的 P2 指针的磁盘地址把磁盘块 8 加载到内存，此时发生一次 IO，同时利用二分查找到 29，查询结束。
B+ 树的非叶子节点只是存储 key，占用空间非常小，因此每一层的节点能索引到的数据范围更加的广，换句话说，每次 IO 操作可以观看更多的数据；
叶子节点两两相连，符合磁盘的预读特性。如图存储 5、8 、9 的叶子节点，它有个指针指向了 10、15、18 这个叶子节点，那么当我们从磁盘读取5、8、9 对应的数据的时候，由于磁盘的预读特性，会顺便把 10、15、18 对应的数据读取出来，这个时候属于顺序读取，而不是磁盘寻道了，加快了速度；
支持范围查询，而且部分范围查询非常高效，原因是数据都是存储在叶子节点这一层，并且有指针指向其他叶子节点，这样范围查询只需要遍历叶子节点这一层，无需整棵树遍历。
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，聚簇表示数据行和相邻的键值聚簇的存储在一起；
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多个数据块中提取数据，所以节省了大量的 IO 操作；
对于 MySQL 数据库目前只有 InnoDB 数据引擎支持聚簇索引，而 MyISAM 并不支持聚簇索引；
由于数据物理存储排序方式只能有一种，所以每个 MySQL 的表只能有一个聚簇索引，一般情况下就是该表的主键；
为了充分利用聚簇索引的聚簇的特性，所以 InnoDB 表的主键列尽量选用有序的顺序 ID，而不建议用无序的 ID，比如 UUID这种。
即一个索引只包含单个列，一个表可以有多个单列索引
随表一起建索引：
CREATE TABLE customer (
id INT (10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR (200),
customer_name VARCHAR (200),
PRIMARY KEY (id),
KEY (customer_name)
);
单独建单值索引：
CREATE INDEX idx_customer_name ON customer (customer_name);
删除索引：
DROP INDEX idx_customer_name ON customer;索引列的值必须唯一，但可以为空
随表一起建索引：
CREATE TABLE customer (
id INT (10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR (200),
customer_name VARCHAR (200),
PRIMARY KEY (id),
KEY (customer_name),
UNIQUE (customer_no)
);
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer (customer_no);
删除索引：
DROP INDEX idx_customer_no ON customer;设为主键后自动创建主键索引
随表一起建索引：
CREATE TABLE customer (
id INT (10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR (200),
customer_name VARCHAR (200),
PRIMARY KEY (id)
);
单独建主键索引：
ALTER TABLE customer ADD PRIMARY KEY customer (customer_no);
删除建主键索引：
ALTER TABLE customer DROP PRIMARY KEY;
修改建主键索引： 必须先删除掉 (DROP) 原索引，再新建 (ADD) 索引一个索引包含单个列
随表一起建索引：
CREATE TABLE customer (
id INT (10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR (200),
customer_name VARCHAR (200),
PRIMARY KEY (id),
KEY (customer_name),
UNIQUE (customer_name),
KEY (customer_no, customer_name)
);
单独建索引：
CREATE INDEX idx_no_name ON customer (customer_no, customer_name);
删除索引：
DROP INDEX idx_no_name ON customer;哪些情况需要创建索引？
哪些情况不需要创建索引？
使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的，分析查询语句或是表结构的性能瓶颈。
EXPLAN 的作用：
查看表的读取顺序
查看哪些索引可以被使用
数据读取操作的操作类型
哪些索引被实际使用
表之间的引用
使用方式：
Explain + SQL
Explain SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
-> UNION
-> SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;id：
SELECT 查询的序列号,包含一组数字，表示查询中执行 SELECT 子句或操作表的顺序
id 相同：执行顺序由上至下
id 不同：如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
每个 id 表示一趟独立的查询，一个 SQL 的查询趟数越少越好
select_type
查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
SIMPLE：最简单的查询，不包含 UNION 和子查询
PRIMARY：查询中若包含复杂的子部分，最外层查询被标记为 PRIMARY
DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED，MySQL 会递归执行这些子查询, 把结果放在临时表里
SUBQUERY：在 SELECT 或 WHERE 列表中包含子查询
DEPENDENT SUB：在 SELECT 或 WHERE 列表中包含子查询，子查询基于外层
UNCACHEABLE SUBQUREY：结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估
UNION：若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION
table
显示这一行的数据是关于哪张表的
type
显示连接使用的类型，按最优到最差的类型排序
system：表只有一行记录
const：表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引，因为只匹配一行数据，所以很快
如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
range：只检索给定范围的行，使用一个索引来选择行，key 列显示使用了哪个索引，一般就是在 where 语句中出现了 between、<、>、in 等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引
index：出现 index 是 SQL 使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组
all：Full Table Scan，将遍历全表以找到匹配的行
index_merge：在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的 SQL 中
ref_or_null：对于某个字段既需要关联条件，也需要 null 值的情况下，查询优化器会选择用 ref_or_null 连接查询
index_subquery：利用索引来关联子查询，不再全表扫描
一般来说，得保证查询至少达到 range 级别，最好能达到 ref
possible_keys
显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key
实际使用的索引，如果为NULL，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度， key_len 字段能够检查是否充分的利用上了索引
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
rows
rows 列显示 MySQL 认为它执行查询时必须检查的行数
Extra
包含不适合在其他列中显示但十分重要的额外信息
index(a,b,c)
| Where语句 | 索引是否被使用 |
|---|---|
| WHERE a = 3 | y，使用到 a |
| WHERE a = 3 AND b = 5 | y，使用到 a、b |
| WHERE a = 3 AND b = 5 AND c = 4 | y，使用到 a、b、c |
| WHERE b = 3、WHERE b = 3 AND c = 4、WHERE c = 4 | n |
| WHERE a = 3 AND c = 5 | y，使用到 a，b中断了 |
| WHERE a = 3 AND b > 4 AND c = 5 | y，使用到 a，b 中断了 |
| WHERE a IS NULL AND b IS NOT NULL | is null 支持索引 但是is not null 不支持，所以 a 可以使用索引，b 不可以使用索引 |
| WHERE a <> 3 | <> 不能使用索引 |
| WHERE abs(a) = 3 | abs 不能使用索引 |
| WHERE a = 3 AND b LIKE 'kk%' AND c = 4 | y，使用到 a、b、c |
| WHERE a = 3 AND b LIKE '%kk' AND c = 4 | y，使用到 a |
| WHERE a = 3 AND b LIKE '%kk%' AND c = 4 | y，使用到 a |
| WHERE a = 3 AND b LIKE 'k%kk%' AND c = 4 | y，使用到 a、b、c |
创建索引的建议：
对于单值索引，尽量选择针对当前查询过滤性更高的字段
选择组合索引，当前查询过滤性最高的字段在索引的位置越靠前越好
选择组合索引，尽量选择可以能够包含当前查询中的 where 字句中更多字段的索引
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
保证被驱动表的 join 字段已经被索引
left join 时，选择小表作为驱动表，大表作为被驱动表
inner join 时，MySQL 会自己把小结果集的表选为驱动表
子查询尽量不要放在被驱动表，有可能使用不到索引
能够直接多表关联的尽量直接关联，不用子查询
尽量不要使用not in 或者 not exists，用 left join on xxx is null 替代
ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
如果不在索引列上，filesort 有两种算法：
双路排序
单路排序：
group by 使用索引的原则几乎跟 order by 一致 ，唯一区别是 group by 即使没有过滤条件用到索引，也可以直接使用索引
什么是慢查询日志？
慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阈值的语句，具体指运行时间超过long_query_time 值的 SQL，则会被记录到慢查询日志中；
long_query_time 的默认值为10，意思是运行10秒以上的语句。
默认慢查询日志是关闭的，需要手动开启
查看慢查询日志是否开启
SHOW VARIABLES LIKE '%slow_query_log%';
开启慢查询日志
set global slow_query_log=1;查看并配置 long_query_time
查看long_query_time
SHOW VARIABLES LIKE 'long_query_time%';
set long_query_time=1日志分析工具 mysqldumpslow
.jpg)
常用参考：
hadoop100得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop100-slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hadoop100-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hadoop100-slow.log
另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop100-slow.log | more
复制的基本原理
master 将改变记录到二进制日志（binary log），这些记录过程叫做二进制日志事件，binary log events；
slave 将 master 的 binary log events 拷贝到它的中继日志（relay log）；
slave 重做中继日志中的事件，将改变应用到自己的数据库中，MySQL 复制是异步的且串行化的。
复制的基本原则
每个 slave 只有一个 master
每个 slave 只能有一个唯一的服务器 ID
每个 master 可以有多个salve
1、配置主数据库
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
binlog_format=mixed为从服务分配账号
查看主服务器 BIN 日志的信息
show master status;重启主数据库
systemctl restart mariadb2、配置从数据库
连接主数据库
CHANGE MASTER TO
-> MASTER_HOST="192.168.10.100",
-> MASTER_USER="slave",
-> MASTER_PASSWORD="123456",
-> MASTER_LOG_FILE="mysql-bin.000001",
-> MASTER_LOG_POS=388;启动从数据库
start slave;
