【免费分享】mysql笔记，涵盖查询、缓存、存储过程、索引，优化。
概括本篇笔记涵盖根本查询、视图、存储过程、函数、索引、优化、分库分表。适合在学完mysql后进行时常观看。下面展示部分内容。如果需要可以在文章底部的链接进行下载查看。
简介
数据库
数据库：DataBase，简称 DB，存储和管理数据的仓库
数据库的优势：
[*]可以持久化存储数据
[*]方便存储和管理数据
[*]使用了同一的方式操作数据库 SQL
数据库、数据表、数据的关系介绍：
[*] 数据库
[*]用于存储和管理数据的仓库
[*]一个库中可以包含多个数据表
[*] 数据表
[*]数据库最紧张的组成部分之一
[*]由纵向的列和横向的行组成（雷同 excel 表格）
[*]可以指定列名、数据范例、约束等
[*]一个表中可以存储多条数据
[*] 数据：想要永世化存储的数据
参考视频：https://www.bilibili.com/video/BV1zJ411M7TB
参考专栏：https://time.geekbang.org/column/intro/139
参考册本：https://book.douban.com/subject/35231266/
MySQL
MySQL 数据库是一个最盛行的关系型数据库管理系统之一，关系型数据库是将数据生存在不同的数据表中，而且表与表之间可以有关联关系，提高了机动性
缺点：数据存储在磁盘中，导致读写性能差，而且数据关系复杂，扩展性差
MySQL 所使用的 SQL 语句是用于访问数据库最常用的标准化语言
MySQL 配置：
[*] MySQL 安装：https://www.jianshu.com/p/ba48f1e386f0
[*] MySQL 配置：
[*] 修改 MySQL 默认字符集：安装 MySQL 之后第一件事就是修改字符集编码
vim /etc/mysql/my.cnf
添加如下内容：
character-set-server=utf8
collation-server=utf8_general_ci
default-character-set=utf8
[*] 启动 MySQL 服务：
systemctl start/restart mysql
[*] 登录 MySQL：
mysql -u root -p敲回车，输入密码
初始密码查看：cat /var/log/mysqld.log
在root@localhost: 后面的就是初始密码
[*] 查看默认字符集下令：
SHOW VARIABLES LIKE 'char%';
[*] 修改MySQL登录密码：
set global validate_password_policy=0;
set global validate_password_length=1;
set password=password('密码');
[*] 授予远程连接权限（MySQL 内输入）：
-- 授权
grant all privileges on *.* to 'root' @'%' identified by '密码';
-- 刷新
flush privileges;
[*] 修改 MySQL 绑定 IP：
cd /etc/mysql/mysql.conf.d
sudo chmod 666 mysqld.cnf
vim mysqld.cnf
# bind-address = 127.0.0.1注释该行
[*] 关闭 Linux 防火墙
systemctl stop firewalld.service
# 放行3306端口
体系架构
整体架构
体系结构详解：
[*]第一层：网络连接层
[*]一些客户端和链接服务，包含本地 Socket 通讯和大多数基于客户端/服务端工具实现的 TCP/IP 通讯，紧张完成一些雷同于连接处理、授权认证、及相关的安全方案
[*]在该层上引入了连接池 Connection Pool 的概念，管理缓冲用户连接，线程处理等需要缓存的需求
[*]在该层上实现基于 SSL 的安全链接，服务器也会为安全接入的每个客户端验证它所具有的操作权限
[*]第二层：核心服务层
[*]查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，全部的内置函数（日期、数学、加密函数等）
[*]Management Serveices & Utilities：系统管理和控制工具，备份、安全、复制、集群等
[*]SQL Interface：接受用户的 SQL 下令，而且返回用户需要查询的效果
[*]Parser：SQL 语句分析器
[*]Optimizer：查询优化器
[*]Caches & Buffers：查询缓存，服务器会查询内部的缓存，如果缓存空间充足大，可以在大量读操作的环境中提升系统性能
[*]全部跨存储引擎的功能在这一层实现，如存储过程、触发器、视图等
[*]在该层服务器会剖析查询并创建相应的内部剖析树，并对其完成相应的优化如确定表的查询顺序，是否使用索引等， 末了生成相应的执行操作
[*]MySQL 中服务器层不管理事务，事务是由存储引擎实现的
[*]第三层：存储引擎层
[*]Pluggable Storage Engines：存储引擎接口，MySQL 区别于其他数据库的紧张特点就是其存储引擎的架构模式是插件式的（存储引擎是基于表的，而不是数据库）
[*]存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 和存储引擎进行通讯
[*]不同的存储引擎具有不同的功能，共用一个 Server 层，可以根据开发的需要，来选取符合的存储引擎
[*]第四层：系统文件层
[*]数据存储层，紧张是将数据存储在文件系统之上，并完成与存储引擎的交互
[*]File System：文件系统，生存配置文件、数据文件、日志文件、错误文件、二进制文件等
https://i-blog.csdnimg.cn/img_convert/cf4f8bff488d588ae24dd208badfb690.png
创建连接
连接器
池化技术：对于访问数据库来说，创建连接的代价是比力昂贵的，由于每个连接对应一个用来交互的线程，频繁的创建关闭连接比力泯灭资源，有必要创建数据库连接池，以提高访问的性能
连接创建 TCP 以后需要做权限验证，验证乐成后可以进行执行 SQL。如果这时管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限，只有再新建的连接才会使用新的权限设置
MySQL 服务器可以同时和多个客户端进行交互，所以要包管每个连接会话的隔离性（事务机制部分详解）
整体的执行流程：
https://i-blog.csdnimg.cn/img_convert/90f3a49daa99bfcbda69b41615fa4919.png *** #### 权限信息 grant 语句会同时修改数据表和内存，判定权限的时候使用的是内存数据
flush privileges 语句本身会用数据表（磁盘）的数据重修一份内存权限数据，所以在权限数据可能存在不一致的情况下使用，这种不一致每每是由于直接用 DML 语句操作系统权限表导致的，所以尽量不要使用这类语句
https://i-blog.csdnimg.cn/img_convert/f370c266064798b2cc2a21e8ecef6e8a.png
连接状态
客户端如果长时间没有操作，连接器就会自动断开，时间是由参数 wait_timeout 控制的，默认值是 8 小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query
数据库里面，长连接是指连接乐成后，如果客户端持续有请求，则一直使用同一个连接；短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新创建一个
为了减少连接的创建，推荐使用长连接，但是过多的长连接会造成 OOM，办理方案：
[*] 定期断开长连接，使用一段时间，大概步调里面判定执行过一个占用内存的大查询后，断开连接，之后要查询再重连
KILL CONNECTION id
[*] MySQL 5.7 版本，可以在每次执行一个比力大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源，这个过程不需要重连和重新做权限验证，但是会将连接规复到刚刚创建完时的状态
SHOW PROCESSLIST：查看当前 MySQL 在进行的线程，可以实时地查看 SQL 的执行情况，此中的 Command 列显示为 Sleep 的这一行，就体现如今系统里面有一个空闲连接
https://i-blog.csdnimg.cn/img_convert/3b4f3d1e4a0f1b348679a58db6ff58e9.png
参数寄义ID用户登录 mysql 时系统分配的 connection_id，可以使用函数 connection_id() 查看User显示当前用户，如果不是 root，这个下令就只显示用户权限范围的 sql 语句Host显示这个语句是从哪个 ip 的哪个端口上发的，可以用来跟踪出现标题语句的用户db显示这个进程目前连接的是哪个数据库Command显示当前连接的执行的下令，一样平常取值为休眠 Sleep、查询 Query、连接 Connect 等Time显示这个状态持续的时间，单位是秒State显示使用当前连接的 sql 语句的状态，以查询为例，需要颠末 copying to tmp table、sorting result、sending data等状态才可以完成Info显示执行的 sql 语句，是判定标题语句的一个紧张依据 Sending data 状态体现 MySQL 线程开始访问数据行并把效果返回给客户端，而不但仅只是返回给客户端，是处于执行器过程中的任意阶段。由于在 Sending data 状态下，MySQL 线程需要做大量磁盘读取操作，所以是整个查询中耗时最长的状态。
执行流程
查询缓存
工作流程
当执行完全相同的 SQL 语句的时候，服务器就会直接从缓存中读取效果，当数据被修改，之前的缓存会失效，修改比力频繁的表不适合做查询缓存
查询过程：
[*]客户端发送一条查询给服务器
[*]服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的效果（一样平常是 K-V 键值对），否则进入下一阶段
[*]分析器进行 SQL 分析，再由优化器生成对应的执行计划
[*]MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询
[*]将效果返回给客户端
大多数情况下不发起使用查询缓存，由于查询缓存每每弊大于利
[*]查询缓存的失效非常频繁，只要有对一个表的更新，这个表上全部的查询缓存都会被清空。因此很可能费力地把效果存起来，还没使用就被一个更新全清空了，对于更新压力大的数据库来说，查询缓存的命中率会非常低
[*]除非业务就是有一张静态表，很长时间才会更新一次，比如一个系统配置表，那这张表上的查询才适合使用查询缓存
缓存配置
[*] 查看当前的 MySQL 数据库是否支持查询缓存：
SHOW VARIABLES LIKE 'have_query_cache'; -- YES
[*] 查看当前 MySQL 是否开启了查询缓存：
SHOW VARIABLES LIKE 'query_cache_type'; -- OFF
参数说明：
[*] OFF 或 0：查询缓存功能关闭
[*] ON 或 1：查询缓存功能打开，查询效果符合缓存条件即会缓存，否则不予缓存；可以显式指定 SQL_NO_CACHE 不予缓存
[*] DEMAND 或 2：查询缓存功能按需进行，显式指定 SQL_CACHE 的 SELECT 语句才缓存，别的不予缓存
SELECT SQL_CACHE id, name FROM customer; -- SQL_CACHE:查询结果可缓存
SELECT SQL_NO_CACHE id, name FROM customer;-- SQL_NO_CACHE:不使用查询缓存
[*] 查看查询缓存的占用大小：
SHOW VARIABLES LIKE 'query_cache_size';-- 单位是字节 1048576 / 1024 = 1024 = 1KB
[*] 查看查询缓存的状态变量：
SHOW STATUS LIKE 'Qcache%';
https://i-blog.csdnimg.cn/img_convert/59010f9a72c5497abf7e18d16616f4f3.png 参数寄义Qcache_free_blocks查询缓存中的可用内存块数Qcache_free_memory查询缓存的可用内存量Qcache_hits查询缓存命中数Qcache_inserts添加到查询缓存的查询数Qcache_lowmen_prunes由于内存不足而从查询缓存中删除的查询数Qcache_not_cached非缓存查询的数量（由于 query_cache_type 设置而无法缓存或未缓存）Qcache_queries_in_cache查询缓存中注册的查询数Qcache_total_blocks查询缓存中的块总数
[*] 配置 my.cnf：
sudo chmod 666 /etc/mysql/my.cnf
vim my.cnf
# mysqld中配置缓存
query_cache_type=1
重启服务既可见效，执行 SQL 语句进行验证 ，执行一条比力耗时的 SQL 语句，然后再多执行几次，查看反面几次的执行时间；获取通过查看查询缓存的缓存命中数，来判定是否走查询缓存
缓存失效
查询缓存失效的情况：
[*] SQL 语句不一致，要想命中查询缓存，查询的 SQL 语句必须一致，由于缓存中 key 是查询的语句，value 是查询结构
select count(*) from tb_item;
Select count(*) from tb_item; -- 不走缓存，首字母不一致
[*] 当查询语句中有一些不确定查询时，则不会缓存，比如：now()、current_date()、curdate()、curtime()、rand()、uuid()、user()、database()
SELECT * FROM tb_item WHERE updatetime < NOW() LIMIT 1;
SELECT USER();
SELECT DATABASE();
[*] 不使用任何表查询语句：
SELECT 'A';
[*] 查询 mysql、information_schema、performance_schema 等系统表时，不走查询缓存：
SELECT * FROM information_schema.engines;
[*] 在跨存储引擎的存储过程、触发器或存储函数的主体内执行的查询，缓存失效
[*] 如果表更改，则使用该表的全部高速缓存查询都将变为无效并从高速缓存中删除，包罗使用 MERGE 映射到已更改表的表的查询，比如：INSERT、UPDATE、DELETE、ALTER TABLE、DROP TABLE、DROP DATABASE
分析器
没有命中查询缓存，就开始了 SQL 的真正执行，分析器会对 SQL 语句做剖析
SELECT * FROM t WHERE id = 1;
剖析器：处理语法息争析查询，生成一课对应的剖析树
[*]先做词法分析，输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么代表什么。从输入的 select 这个关键字识别出来这是一个查询语句；把字符串 t 识别成 表名 t，把字符串 id 识别成列 id
[*]然后做语法分析，根据词法分析的效果，语法分析器会根据语法规则，判定你输入的这个 SQL 语句是否满意 MySQL 语法。如果你的语句不对，就会收到 You have an error in your SQL syntax 的错误提醒
预处理器：进一步检查剖析树的合法性，比如数据表和数据列是否存在、别名是否有歧义等
优化器
成天职析
优化器是在表里面有多个索引的时候，决定使用哪个索引；大概在一个语句有多表关联（join）的时候，决定各个表的连接顺序
[*]根据搜索条件找出全部可能的使用的索引
[*]成天职析，执行本钱由 I/O 本钱和 CPU 本钱组成，计算全表扫描和使用不同索引执行 SQL 的代价
[*]找到一个最优的执行方案，用最小的代价去执行语句
在数据库里面，扫描行数是影响执行代价的因素之一，扫描的行数越少意味着访问磁盘的次数越少，消耗的 CPU 资源越少，优化器还会结合是否使用临时表、是否排序等因素进行综合判定
统计数据
MySQL 中生存着两种统计数据：
[*]innodb_table_stats 存储了表的统计数据，每一条记录对应着一个表的统计数据
[*]innodb_index_stats 存储了索引的统计数据，每一条记录对应着一个索引的一个统计项的数据
MySQL 在真正执行语句之前，并不能精确地知道满意条件的记录有多少条，只能根据统计信息来估算记录，统计信息就是索引的区分度,一个索引上不同的值的个数（比如性别只能是男女，就是 2 ），称之为基数（cardinality），基数越大说明区分度越好
通过采样统计来获取基数，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数
在 MySQL 中，有两种存储统计数据的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：
[*]ON：体现统计信息会持久化存储（默认），采样页数 N 默认为 20，可以通过 innodb_stats_persistent_sample_pages 指定，页数越多统计的数据越精确，但消耗的资源更大
[*]OFF：体现统计信息只存储在内存，采样页数 N 默认为 8，也可以通过系统变量设置（不推荐，每次重新计算浪费资源）
数据表是会持续更新的，两种统计信息的更新方式：
[*]设置 innodb_stats_auto_recalc 为 1，当发生变动的记录数量凌驾表大小的 10% 时，自动触发重新计算，不过是异步进行
[*]调用 ANALYZE TABLE t 手动更新统计信息，只对信息做重新统计（不是重修表），没有修改数据，这个过程中加了 MDL 读锁而且是同步进行，所以会临时壅闭系统
EXPLAIN 执行计划在优化器阶段生成，如果 explain 的效果预估的 rows 值跟实际情况差距比力大，可以执行 analyze 下令重新修正信息
错选索引
采样统计本身是估算数据，大概 SQL 语句中的字段选择有标题时，可能导致 MySQL 没有选择精确的执行索引
办理方法：
[*] 接纳 force index 强行选择一个索引
SELECT * FROM user FORCE INDEX(name) WHERE NAME='seazean';
[*] 可以考虑修改 SQL 语句，引导 MySQL 使用期望的索引
[*] 新建一个更符合的索引，来提供给优化器做选择，或删掉误用的索引
执行器
开始执行的时候，要先判定一下当前连接对表有没有执行查询的权限，如果没有就会返回没有权限的错误，在工程实现上，如果命中查询缓存，会在查询缓存返回效果的时候，做权限验证。如果有权限，就打开表继续执行，执行器就会根据表的引擎定义，去使用这个引擎提供的接口
引擎层
Server 层和存储引擎层的交互是以记录为单位的，存储引擎会将单条记录返回给 Server 层做进一步处理，并不是直接返回全部的记录
工作流程：
[*]首先根据二级索引选择扫描范围，获取第一条符合二级索引条件的记录，进行回表查询，将聚簇索引的记录返回 Server 层，由 Server 判定记录是否符合要求
[*]然后在二级索引上继续扫描下一个符合条件的记录
推荐阅读：https://mp.weixin.qq.com/s/YZ-LckObephrP1f15mzHpA
终止流程
终止语句
终止线程中正在执行的语句：
KILL QUERY thread_id
KILL 不是马上终止的意思，而是告诉执行线程这条语句已经不需要继续执行，可以开始执行停止的逻辑（雷同于打断）。由于对表做增编削查操作，会在表上加 MDL 读锁，如果线程被 KILL 时就直接终止，那这个 MDL 读锁就没机会被开释了
下令 KILL QUERYthread_id_A 的执行流程：
[*]把 session A 的运行状态改成 THD::KILL_QUERY（将变量 killed 赋值为 THD::KILL_QUERY）
[*]给 session A 的执行线程发一个信号，让 session A 来处理这个 THD::KILL_QUERY 状态
会话处于等待状态（锁壅闭），必须满意是一个可以被唤醒的等待，必须有机会去判定线程的状态，如果不满意就会造成 KILL 失败
典范场景：innodb_thread_concurrency 为 2，代表并发线程上限数设置为 2
[*]session A 执行事务，session B 执行事务，达到线程上限；此时 session C 执行事务会壅闭等待，session D 执行 kill query C 无效
[*]C 的逻辑是每 10 毫秒判定是否可以进入 InnoDB 执行，如果不可就调用 nanosleep 函数进入 sleep 状态，没有去判定线程状态
增补：执行 Ctrl+C 的时候，是 MySQL 客户端另外启动一个连接，然后发送一个 KILL QUERY 下令
终止连接
断开线程的连接：
KILL CONNECTION id
断开连接后执行 SHOW PROCESSLIST 下令，如果这条语句的 Command 列显示 Killed，代表线程的状态是 KILL_CONNECTION，说明这个线程有语句正在执行，当前状态是停止语句执行中，终止逻辑耗时较长
[*]超大事务执行期间被 KILL，这时回滚操作需要对事务执行期间生成的全部新数据版本做回收操作，耗时很长
[*]大查询回滚，如果查询过程中生成了比力大的临时文件，删除临时文件可能需要等待 IO 资源，导致耗时较长
[*]DDL 下令执行到末了阶段被 KILL，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久
总结：KILL CONNECTION 本质上只是把客户端的 SQL 连接断开，反面的终止流程还是要走 KILL QUERY
一个事务被 KILL 之后，持续处于回滚状态，不应该强行重启整个 MySQL 进程，应该等待事务本身执行完成，由于重启后依然继续做回滚操作的逻辑
获取方式。
由于字数较多无法一次性全部上传，可以点击链接进行生存下载。
夸克网盘
百度网盘
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]