基于微信公众号开发网站开发,怎样做推广,企业网站的推广方式和手段有哪些,做网站一年赚80亿一条 SQL 语句过来的流程是什么样的#xff1f;
①当客户端连接到 MySQL 服务器时#xff0c;服务器对其进行认证。可以通过用户名与密码认证#xff0c;也可以通过 SSL 证书进行认证。登录认证后#xff0c;服务器还会验证客户端是否有执行某个查询的操作权限。 ②在正式…一条 SQL 语句过来的流程是什么样的
①当客户端连接到 MySQL 服务器时服务器对其进行认证。可以通过用户名与密码认证也可以通过 SSL 证书进行认证。登录认证后服务器还会验证客户端是否有执行某个查询的操作权限。 ②在正式查询之前服务器会检查查询缓存如果能找到对应的查询则不必进行查询解析优化执行等过程直接返回缓存中的结果集。
③MySQL 的解析器会根据查询语句构造出一个解析树主要用于根据语法规则来验证语句是否正确比如 SQL 的关键字是否正确关键字的顺序是否正确。而预处理器主要是进一步校验比如表名字段名是否正确等。
④查询优化器将解析树转化为查询计划一般情况下一条查询可以有很多种执行方式最终返回相同的结果优化器就是根据成本找到这其中最优的执行计划。
⑤执行计划调用查询执行引擎而查询引擎通过一系列 API 接口查询到数据。
⑥得到数据之后在返回给客户端的同时会将数据存在查询缓存中。
查询缓存
我们先通过 show variables like %query_cache% 来看一下默认的数据库配置此为本地数据库的配置。
概念
①have_query_cache当前的 MySQL 版本是否支持“查询缓存”功能。
②query_cache_limitMySQL 能够缓存的最大查询结果查询结果大于该值时不会被缓存。默认值是 1048576(1MB)。
③query_cache_min_res_unit查询缓存分配的最小块字节。默认值是 40964KB。
当查询进行时MySQL 把查询结果保存在 query cache但是如果保存的结果比较大超过了 query_cache_min_res_unit 的值这时候 MySQL 将一边检索结果一边进行保存结果。
他保存结果也是按默认大小先分配一块空间如果不够又要申请新的空间给他。
如果查询结果比较小默认的 query_cache_min_res_unit 可能造成大量的内存碎片如果查询结果比较大默认的 query_cache_min_res_unit 又不够导致一直分配块空间。
所以可以根据实际需求调节 query_cache_min_res_unit 的大小。
注如果上面说的内容有点弯弯绕那举个现实生活中的例子比如咱现在要给运动员送水默认的是 500ml 的瓶子如果过来的是少年运动员可能 500ml 太大了他们喝不完造成了浪费。
那我们就可以选择 300ml 的瓶子如果过来的是成年运动员可能 500ml 不够那他们一瓶喝完了又开一瓶直接不渴为止。那么那样开瓶子也要时间我们就可以选择 1000ml 的瓶子。
④query_cache_size为缓存查询结果分配的总内存。
⑤query_cache_type默认为 on可以缓存除了以 select sql_no_cache 开头的所有查询结果。
⑥query_cache_wlock_invalidate如果该表被锁住是否返回缓存中的数据默认是关闭的。
原理
MySQL 的查询缓存实质上是缓存 SQL 的 Hash 值和该 SQL 的查询结果如果运行相同的 SQL服务器直接从缓存中去掉结果而不再去解析优化寻找最低成本的执行计划等一系列操作大大提升了查询速度。
但是万事有利也有弊
第一个弊端就是如果表的数据有一条发生变化那么缓存好的结果将全部不再有效。这对于频繁更新的表查询缓存是不适合的。
比如一张表里面只有两个字段分别是 id 和 name数据有一条为 1张三。
我使用 select * from 表名 where name“张三”来进行查询MySQL 发现查询缓存中没有此数据会进行一系列的解析优化等操作进行数据的查询。
查询结束之后将该 SQL 的 Hash 和查询结果缓存起来并将查询结果返回给客户端。
但是这个时候我又新增了一条数据 2张三。如果我还用相同的 SQL 来执行他会根据该 SQL 的 Hash 值去查询缓存中那么结果就错了。
所以 MySQL 对于数据有变化的表来说会直接清空关于该表的所有缓存。这样其实效率是很差的。
第二个弊端就是缓存机制是通过对 SQL 的 Hash得出的值为 Key查询结果为 Value 来存放的那么就意味着 SQL 必须完完全全一模一样否则就命不中缓存。
我们都知道 Hash 值的规则就算很小的查询哈希出来的结果差距是很多的所以 select * from 表名 where name“张三”和SELECT * FROM 表名 WHERE NAME“张三”和select * from 表名 where name “张三”三个SQL 哈希出来的值是不一样的。
大小写和空格影响了他们所以并不能命中缓存但其实他们搜索结果是完全一样的。
生产如何设置 MySQL Query Cache
先来看线上参数
我们发现将 query_cache_type 设置为 OFF其实网上资料和各大云厂商提供的云服务器都是将这个功能关闭的从上面的原理来看在一般情况下他的弊端大于优点。
索引 例子创建一个名为 user 的表其包括 idnameagesex 等字段信息。此外id 为主键聚簇索引idx_name 为非聚簇索引 CREATE TABLE user ( id varchar(10) NOT NULL DEFAULT , name varchar(10) DEFAULT NULL, age int(11) DEFAULT NULL, sex varchar(10) DEFAULT NULL, PRIMARY KEY (id), KEY idx_name (name) USING BTREE ) ENGINEInnoDB DEFAULT CHARSETutf8; 我们将其设置 10 条数据便于下面的索引的理解 INSERT INTO user VALUES (1, andy, 20, 女); INSERT INTO user VALUES (10, baby, 12, 女); INSERT INTO user VALUES (2, kat, 12, 女); INSERT INTO user VALUES (3, lili, 20, 男); INSERT INTO user VALUES (4, lucy, 22, 女); INSERT INTO user VALUES (5, bill, 20, 男); INSERT INTO user VALUES (6, zoe, 20, 男); INSERT INTO user VALUES (7, hay, 20, 女); INSERT INTO user VALUES (8, tony, 20, 男); INSERT INTO user VALUES (9, rose, 21, 男); 聚簇索引主键索引
他包含两个特点 使用记录主键值的大小来进行记录和页的排序。页内的记录是按照主键的大小顺序排成一个单项链表。各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。 叶子节点存储的是完整的用户记录。
注聚簇索引不需要我们显示的创建他是由 InnoDB 存储引擎自动为我们创建的。如果没有主键其也会默认创建一个
非聚簇索引二级索引
上面的聚簇索引只能在搜索条件是主键时才能发挥作用因为聚簇索引可以根据主键进行排序的。
如果搜索条件是 name在刚才的聚簇索引上我们可能遍历挨个找到符合条件的记录但是这样真的是太蠢了MySQL 不会这样做的。
如果我们想让搜索条件是 name 的时候也能使用索引那可以多创建一个基于 name 的二叉树如下图
他与聚簇索引的不同 叶子节点内部使用 name 字段排序叶子节点之间也是使用 name 字段排序。 叶子节点不再是完整的数据记录而是 name 和主键值。
为什么不再是完整信息MySQL 只让聚簇索引的叶子节点存放完整的记录信息因为如果有好几个非聚簇索引他们的叶子节点也存放完整的记录绩效那就不浪费空间啦。
如果我搜索条件是基于 name需要查询所有字段的信息那查询过程是啥 根据查询条件采用 name 的非聚簇索引先定位到该非聚簇索引某些记录行。 根据记录行找到相应的 id再根据 id 到聚簇索引中找到相关记录。这个过程叫做回表。
联合索引
图就不画了简单来说如果 name 和 age 组成一个联合索引那么先按 name 排序如果 name 一样就按 age 排序。
一些原则
①最左前缀原则。一个联合索引a,b,c,如果有一个查询条件有 a有 b那么他则走索引如果有一个查询条件没有 a那么他则不走索引。
②使用唯一索引。具有多个重复值的列其索引效果最差。例如存放姓名的列具有不同值很容易区分每行。
而用来记录性别的列只含有“男”“女”不管搜索哪个值都会得出大约一半的行这样的索引对性能的提升不够高。
③不要过度索引。每个额外的索引都要占用额外的磁盘空间并降低写操作的性能。
在修改表的内容时索引必须进行更新有时可能需要重构因此索引越多所花的时间越长。
④索引列不能参与计算保持列“干净”比如 from_unixtime(create_time) ’2014-05-29’就不能使用到索引。
原因很简单B 树中存的都是数据表中的字段值但进行检索时需要把所有元素都应用函数才能比较显然成本太大。
所以语句应该写成
create_time unix_timestamp(’2014-05-29’);⑤一定要设置一个主键。前面聚簇索引说到如果不指定主键InnoDB 会自动为其指定主键这个我们是看不见的。
反正都要生成一个主键的还不如我们设置以后在某些搜索条件时还能用到主键的聚簇索引。
⑥主键推荐用自增 id而不是 uuid。上面的聚簇索引说到每页数据都是排序的并且页之间也是排序的如果是 uuid那么其肯定是随机的其可能从中间插入导致页的分裂产生很多表碎片。
如果是自增的那么其有从小到大自增的有顺序那么在插入的时候就添加到当前索引的后续位置。当一页写满就会自动开辟一个新的页。
注如果自增 id 用完了那将字段类型改为 bigint就算每秒 1 万条数据跑 100 年也没达到 bigint 的最大值。
万年面试题为什么索引用 B 树
①B 树的磁盘读写代价更低B 树的内部节点并没有指向关键字具体信息的指针因此其内部节点相对 B 树更小。
如果把所有同一内部节点的关键字存放在同一盘块中那么盘块所能容纳的关键字数量也越多一次性读入内存的需要查找的关键字也就越多相对 IO 读写次数就降低了。
②由于 B 树的数据都存储在叶子结点中分支结点均为索引方便扫库只需要扫一遍叶子结点即可。
但是 B 树因为其分支结点同样存储着数据我们要找到具体的数据需要进行一次中序遍历按序来扫所以 B 树更加适合在区间查询的情况所以通常 B 树用于数据库索引。
优化器
在开篇的图里面我们知道了 SQL 语句从客户端经由网络协议到查询缓存如果没有命中缓存再经过解析工作得到准确的 SQL现在就来到了我们这模块说的优化器。
首先我们知道每一条 SQL 都有不同的执行方法要不通过索引要不通过全表扫描的方式。
那么问题就来了MySQL 是如何选择时间最短占用内存最小的执行方法呢
什么是成本 I/O 成本。数据存储在硬盘上我们想要进行某个操作需要将其加载到内存中这个过程的时间被称为 I/O 成本。默认是 1。 CPU 成本。在内存对结果集进行排序的时间被称为 CPU 成本。默认是 0.2。
单表查询的成本
先来建一个用户表 dev_user里面包括主键 id用户名 username密码 password外键 user_info_id状态 status外键 main_station_id是否外网访问 visit这七个字段。
索引有两个一个是主键的聚簇索引另一个是显式添加的以 username 为字段的唯一索引 uname_unique。
如果搜索条件是 select * from dev_user where usernameXXX那么 MySQL 是如何选择相关索引呢
①使用所有可能用到的索引
我们可以看到搜索条件 username所以可能走 uname_unique 索引。也可以做聚簇索引也就是全表扫描。
②计算全表扫描代价
我们通过 show table status like ‘dev_user’命令知道 rows 和 data_length 字段如下图
rows表示表中的记录条数但是这个数据不准确是个估计值。
data_length表示表占用的存储空间字节数。data_length聚簇索引的页面数量 X 每个页面的大小。
反推出页面数量1589248÷16÷102497 I/O 成本97X197 CPU 成本6141X0.21228 总成本9712281325
③计算使用不同索引执行查询的代价
因为要查询出满足条件的所有字段信息所以要考虑回表成本 I/O 成本11X12范围区间的数量预计二级记录索引条数 CPU 成本1X0.21X0.20.4读取二级索引的成本回表聚簇索引的成本 总成本 I/O 成本CPU 成本2.4
④对比各种执行方案的代价找出成本最低的那个
上面两个数字一对比成本是采用 uname_unique 索引成本最低。
多表查询的成本
对于两表连接查询来说他的查询成本由下面两个部分构成 单次查询驱动表的成本 多次查询被驱动表的成本具体查询多次取决于对驱动表查询的结果集有多少个记录
index dive
如果前面的搜索条件不是等值而是区间如 select * from dev_user where usernameadmin and usernametest 这个时候我们是无法看出需要回表的数量。
步骤 1先根据 usernameadmin 这个条件找到第一条记录称为区间最左记录。
步骤 2再根据 usernametest 这个条件找到最后一条记录称为区间最右记录。
步骤 3如果区间最左记录和区间最右记录相差不是很远可以准确统计出需要回表的数量。
如果相差很远就先计算 10 页有多少条记录再乘以页面数量最终模糊统计出来。
Explain
产品来索命 产品为什么这个页面出来这么慢 开发因为你查的数据多呗他就是这么慢 产品我不管我要这个页面快点你这样客户怎么用啊 开发......你行你来
哈哈哈哈不瞎 BB 啦如果有些 SQL 贼慢我们需要知道他有没有走索引走了哪个索引这个时候我就需要通过 explain 关键字来深入了解 MySQL 内部是如何执行的。
id一般来说一个 select 一个唯一 id如果是子查询就有两个 selectid 是不一样的但是凡事有例外有些子查询的他们 id 是一样的。
这是为什么呢那是因为 MySQL 在进行优化的时候已经将子查询改成了连接查询而连接查询的 id 是一样的。
select_type simple不包括 union 和子查询的查询都算 simple 类型。 primary包括 unionunion all其中最左边的查询即为 primary。 union包括 unionunion all除了最左边的查询其他的查询类型都为 union。
table显示这一行是关于哪张表的。
type 访问方法 ref普通二级索引与常量进行等值匹配 ref_or_null普通二级索引与常量进行等值匹配该索引可能是 null const主键或唯一二级索引列与常量进行等值匹配 range范围区间的查询 all全表扫描
possible_keys对某表进行单表查询时可能用到的索引。
key经过查询优化器计算不同索引的成本最终选择成本最低的索引。
rows 如果使用全表扫描那么 rows 就代表需要扫描的行数 如果使用索引那么 rows 就代表预计扫描的行数
filtered 如果全表扫描那么 filtered 就代表满足搜索条件的记录的满分比 如果是索引那么 filtered 就代表除去索引对应的搜索其他搜索条件的百分比 结语 本次写的稍微杂但是都是mysql可能遇到的一些问题主要是讲优化器。
