当前位置： 首页 > news >正文

购买营销型网站,做动画的网站有哪些,廊坊网络推广公司,网站文章批量上传工具概括 本篇笔记涵盖基础查询、视图、存储过程、函数、索引、优化、分库分表。适合在学完mysql后进行时常观看。下面展示部分内容。如果需要可以在文章底部的链接进行下载查看。 简介 数据库 数据库#xff1a;DataBase#xff0c;简称 DB#xff0c;存储和管理数据的仓库…概括 本篇笔记涵盖基础查询、视图、存储过程、函数、索引、优化、分库分表。适合在学完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添加如下内容 [mysqld] character-set-serverutf8 collation-serverutf8_general_ci[client] default-character-setutf8启动 MySQL 服务 systemctl start/restart mysql登录 MySQL mysql -u root -p 敲回车输入密码 初始密码查看cat /var/log/mysqld.log 在rootlocalhost: 后面的就是初始密码查看默认字符集命令 SHOW VARIABLES LIKE char%;修改MySQL登录密码 set global validate_password_policy0; set global validate_password_length1;set passwordpassword(密码);授予远程连接权限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 命令并且返回用户需要查询的结果ParserSQL 语句分析器Optimizer查询优化器Caches Buffers查询缓存服务器会查询内部的缓存如果缓存空间足够大可以在大量读操作的环境中提升系统性能 所有跨存储引擎的功能在这一层实现如存储过程、触发器、视图等在该层服务器会解析查询并创建相应的内部解析树并对其完成相应的优化如确定表的查询顺序是否利用索引等 最后生成相应的执行操作MySQL 中服务器层不管理事务事务是由存储引擎实现的 第三层存储引擎层 Pluggable Storage Engines存储引擎接口MySQL 区别于其他数据库的重要特点就是其存储引擎的架构模式是插件式的存储引擎是基于表的而不是数据库存储引擎真正的负责了 MySQL 中数据的存储和提取服务器通过 API 和存储引擎进行通信不同的存储引擎具有不同的功能共用一个 Server 层可以根据开发的需要来选取合适的存储引擎 第四层系统文件层 数据存储层主要是将数据存储在文件系统之上并完成与存储引擎的交互File System文件系统保存配置文件、数据文件、日志文件、错误文件、二进制文件等 建立连接 连接器 池化技术对于访问数据库来说建立连接的代价是比较昂贵的因为每个连接对应一个用来交互的线程频繁的创建关闭连接比较耗费资源有必要建立数据库连接池以提高访问的性能 连接建立 TCP 以后需要做权限验证验证成功后可以进行执行 SQL。如果这时管理员账号对这个用户的权限做了修改也不会影响已经存在连接的权限只有再新建的连接才会使用新的权限设置 MySQL 服务器可以同时和多个客户端进行交互所以要保证每个连接会话的隔离性事务机制部分详解 整体的执行流程 *** #### 权限信息 grant 语句会同时修改数据表和内存判断权限的时候使用的是内存数据 flush privileges 语句本身会用数据表磁盘的数据重建一份内存权限数据所以在权限数据可能存在不一致的情况下使用这种不一致往往是由于直接用 DML 语句操作系统权限表导致的所以尽量不要使用这类语句 连接状态 客户端如果长时间没有操作连接器就会自动断开时间是由参数 wait_timeout 控制的默认值是 8 小时。如果在连接被断开之后客户端再次发送请求的话就会收到一个错误提醒Lost connection to MySQL server during query 数据库里面长连接是指连接成功后如果客户端持续有请求则一直使用同一个连接短连接则是指每次执行完很少的几次查询就断开连接下次查询再重新建立一个 为了减少连接的创建推荐使用长连接但是过多的长连接会造成 OOM解决方案 定期断开长连接使用一段时间或者程序里面判断执行过一个占用内存的大查询后断开连接之后要查询再重连 KILL CONNECTION idMySQL 5.7 版本可以在每次执行一个比较大的操作后通过执行 mysql_reset_connection 来重新初始化连接资源这个过程不需要重连和重新做权限验证但是会将连接恢复到刚刚创建完时的状态
SHOW PROCESSLIST查看当前 MySQL 在进行的线程可以实时地查看 SQL 的执行情况其中的 Command 列显示为 Sleep 的这一行就表示现在系统里面有一个空闲连接 参数含义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%;参数含义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_type1重启服务既可生效执行 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 NAMEseazean;可以考虑修改 SQL 语句引导 MySQL 使用期望的索引 新建一个更合适的索引来提供给优化器做选择或删掉误用的索引 执行器 开始执行的时候要先判断一下当前连接对表有没有执行查询的权限如果没有就会返回没有权限的错误在工程实现上如果命中查询缓存会在查询缓存返回结果的时候做权限验证。如果有权限就打开表继续执行执行器就会根据表的引擎定义去使用这个引擎提供的接口 引擎层 Server 层和存储引擎层的交互是以记录为单位的存储引擎会将单条记录返回给 Server 层做进一步处理并不是直接返回所有的记录 工作流程 首先根据二级索引选择扫描范围获取第一条符合二级索引条件的记录进行回表查询将聚簇索引的记录返回 Server 层由 Server 判断记录是否符合要求然后在二级索引上继续扫描下一个符合条件的记录 推荐阅读https://mp.weixin.qq.com/s/YZ-LckObephrP1f15mzHpA 终止流程 终止语句 终止线程中正在执行的语句 KILL QUERY thread_idKILL 不是马上终止的意思而是告诉执行线程这条语句已经不需要继续执行可以开始执行停止的逻辑类似于打断。因为对表做增删改查操作会在表上加 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 状态没有去判断线程状态 补充执行 CtrlC 的时候是 MySQL 客户端另外启动一个连接然后发送一个 KILL QUERY 命令 终止连接 断开线程的连接 KILL CONNECTION id断开连接后执行 SHOW PROCESSLIST 命令如果这条语句的 Command 列显示 Killed代表线程的状态是 KILL_CONNECTION说明这个线程有语句正在执行当前状态是停止语句执行中终止逻辑耗时较长 超大事务执行期间被 KILL这时回滚操作需要对事务执行期间生成的所有新数据版本做回收操作耗时很长大查询回滚如果查询过程中生成了比较大的临时文件删除临时文件可能需要等待 IO 资源导致耗时较长DDL 命令执行到最后阶段被 KILL需要删除中间过程的临时文件也可能受 IO 资源影响耗时较久 总结KILL CONNECTION 本质上只是把客户端的 SQL 连接断开后面的终止流程还是要走 KILL QUERY 一个事务被 KILL 之后持续处于回滚状态不应该强行重启整个 MySQL 进程应该等待事务自己执行完成因为重启后依然继续做回滚操作的逻辑 获取方式。 因为字数较多无法一次性全部上传可以点击链接进行保存下载。 夸克网盘 百度网盘