wordpress建立多站点,贵阳网站建设端觉,集团网站建设需求,做网站市场分析MariaDB基础(二)介绍关于MariaDB的如下知识点#xff1a;1. 查询缓存2. 索引3. EXPLAIN1.查询缓存#xff1a;1#xff09;什么是缓存#xff1f;缓存就是数据交换的缓冲区#xff0c;即Cache#xff0c;存放在内存中#xff1b;2#xff09;查询缓存的数据以何种形式存… MariaDB基础(二) 介绍关于MariaDB的如下知识点 1. 查询缓存 2. 索引 3. EXPLAIN 1.查询缓存 1什么是缓存 缓存就是数据交换的缓冲区即Cache存放在内存中 2查询缓存的数据以何种形式存在 查询缓存的数据以键值对(key/value)的形式存在; key 查询语句的哈希值哈希值可理解为数据的×××用于验证数据来源的真实性 value 查询语句的查询结果 3缓存命中的标准 将查询语句的哈希值与数据的源哈希值做比较相同则命中反之则未命中 4什么样的查询结果不可缓存 1. 要查询的数据库中包含敏感信息如MySQL数据库中的各系统表 2. 查询语句中包含用户自定义的函数或变量 3. 存储函数 4. 对于临时表发起的查询请求 5. 包含列级别授权的查询 6. 有着不确定值得MySQL内建函数如NOW(),CURRENT_DATE()等 5) 与查询缓存相关得服务器变量 MariaDB [(none)] show global variables like query_cache%;---------------------------------------| Variable_name | Value |---------------------------------------| query_cache_limit | 1048576|| query_cache_min_res_unit | 4096 || query_cache_size | 0 || query_cache_strip_comments | OFF || query_cache_type | ON || query_cache_wlock_invalidate| OFF |---------------------------------------1.query_cache_limit:查询缓存限制能够缓存的最大结果的字节数上限(单语句结果集的上限)注意在使用select查询语句时尽量避免避免“select * ”尽可能使用where或having子句是结果尽可能精确 2.query_cache_min_res_unit查询缓存的最小分配单元功能可有效避免内存碎片注意较大的变量值可能会造成内存空间的浪费较小的变量值可能减少内存空间浪费但会导致频繁的内存分配和回收操作长期会造成内存碎片3.query_cache_size查询缓存大小查询缓存申请的在内存空间的空间大小单位为字节大小需是1024的整数倍示例MariaDB [(none)] select 10*1024*1024;--------------| 10*1024*1024|--------------| 10485760 |--------------MariaDB [(none)] set global.query_cache_size10485760;4.query_cache_strip_comments用于控制是否去掉SQL查询语句中的注释部分之后再作为key存入查询缓存默认值为“OFF”如果启用插入查询缓存的查询语句是不带有注释信息的5.query_cache_type缓存功能开启与否的开关ON启用仅不缓存SQL_NO_CACHE参数的查询结果OFF停用DEMAND按需缓存默认不缓存仅缓存SQL_CACHE参数的查询结果6.query_cache_wlock_invalidate查询缓存写锁无效开关功能如果某会话对某表施加了写锁是否可以从缓存中查询并返回查询结果OFF 可以ON 不可以 6与查询缓存相关的服务器状态参数 MariaDB [(none)] show global status like Qcache%;-----------------------------------| Variable_name | Value |-----------------------------------| Qcache_free_blocks | 1 || Qcache_free_memory | 10468296| | Qcache_hits | 0 || Qcache_inserts | 0 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 0 || Qcache_queries_in_cache| 0 || Qcache_total_blocks | 1 |----------------------------------- 1.Qcache_free_blocks: 查询缓存空闲块查看目前缓存中有多少个剩余空闲块若该数值过大说明内存碎片过多2.Qcache_free_memory: 查询缓存空闲内存空间如果该值太小且剩余块太多则内存碎片多如果该值太小且剩余块不多则说明分配查询缓存的内存空间刚好或偏小如果该值较大分配的查询缓存的内存空间太多应进行相应调整一旦调整了缓存大小则其中存放的查询结果会立即被清除3.Qcache_hits: 查询缓存命中数4.Qcache_inserts:表示未命中的而后经过处理将查询结果添加至查询缓存的查询请求的数量数值越大则证明查询缓存效果越不理想可以通过规范书写查询请求的SQL语句减少此类查询请求的数量注意如果查询缓存中确实没有对应查询语句的查询缓存此数值的增加也是正常现象5.Qcache_lowmem_prunes:查询缓存低内存修剪该参数记录了有多少条查询请求是因为内存空间不足而基于LRU算法移出缓存的如果该数值多大则表示为查询缓存分配的内存空间太小6.Qcache_not_cached:查询请求未被缓存取决于query_cache_type变量的设置的作用下没有被缓存的查询请求的数量7.Qcache_queries_in_cache:当前查询缓存中缓存的查询请求的结果的数量8.Qcache_total_blocks:当前查询缓存中总计分配了多少个block 示例修改查询缓存相关服务器变量查询数据后查看服务器状态参数变化 设置前状态参数 MariaDB [(none)] show global status like Qcache%;--------------------------------| Variable_name | Value|--------------------------------| Qcache_free_blocks | 0 || Qcache_free_memory | 0 || Qcache_hits | 0 || Qcache_inserts | 0 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 0 || Qcache_queries_in_cache| 0 || Qcache_total_blocks | 0 |-------------------------------- 设置query_cache_size为10485760该值需为1024的整数倍 MariaDB [(none)] select 10*1024*1024;--------------| 10*1024*1024|--------------| 10485760 |--------------MariaDB [(none)] set global.query_cache_size10485760;MariaDB [(none)] show global variables like query_cache%;----------------------------------------| Variable_name | Value |----------------------------------------| query_cache_limit | 1048576 || query_cache_min_res_unit | 4096 || query_cache_size | 10485760|| query_cache_strip_comments | OFF || query_cache_type | ON || query_cache_wlock_invalidate| OFF |---------------------------------------- 查看此时状态参数开辟了一个空闲的块缓存剩余为10468296 MariaDB [(none)] show global status like Qcache%;-----------------------------------| Variable_name | Value |-----------------------------------| Qcache_free_blocks | 1 || Qcache_free_memory | 10468296|| Qcache_hits | 0 || Qcache_inserts | 0 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 0 || Qcache_queries_in_cache| 0 || Qcache_total_blocks | 1 |----------------------------------- 使用名为hellodb的数据库用select语句查看库中students表的信息查看状态参数变化 如下缓存剩余空间变为10466128 因为第一次请求所以没有命中缓存即Qcache_hits0 因为有一个select请求所以将该请求列入缓存即Qcache_inserts1 未缓存为1即Qcache_not_cached1 请求数为1,即Qcache_queries_in_cache1 分配块数为4,即Qcache_total_blocks4 MariaDB [(none)] use hellodb;MariaDB [hellodb] select * from students;MariaDB [hellodb] show global status like Qcache%;-----------------------------------| Variable_name | Value |-----------------------------------| Qcache_free_blocks | 1 || Qcache_free_memory | 10466128|| Qcache_hits | 0 || Qcache_inserts | 1 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 1 || Qcache_queries_in_cache| 1 || Qcache_total_blocks | 4 |----------------------------------- 再次请求查看 缓存空间变小 命中数为2 即Qcache_hits2 (因为之前相同的select请求被列入缓存所以接下来两次请求都命中) MariaDB [hellodb] select * from students;MariaDB [hellodb] select * from students;MariaDB [hellodb] show global status like Qcache%;-----------------------------------| Variable_name | Value |-----------------------------------| Qcache_free_blocks | 1 || Qcache_free_memory | 10466128|| Qcache_hits | 2 || Qcache_inserts | 1 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 1 || Qcache_queries_in_cache| 1 || Qcache_total_blocks | 4 |----------------------------------- 7) 如何优化缓存 1尽可能的批量写入构建自定义过程启用事务尽量减少多次的单写入操作 2缓存空间不宜设置过大如果大量缓存同时失效会使MySQL的执行引擎压力过大可能导致服务器假死 3. 必要时必须使用SQL_CACHE和SQL_NO_CACHE等SELECT语句中的参数手动控制缓存存入与否 4. 对于密集型写操作应用场景来说禁用缓存功能可能提升服务器性能 2. 索引 1什么是索引 指的是表中的数据子集 即将表中的某个或某些字段中的数据提取出来另存为一个用一个特定数据结构进行组织的数据 2索引的功能 加速查询操作 3 索引的类型 FULLTEXTSPACIALB TREEHASH B TREE索引: 顺序存储所有的索引数据都放在叶节点上并且每个叶节点都有顺序访问指针 以此指针指向相邻的叶子节点可提高区间数据的查询效率 最左前缀索引适用于高效的范围类数据查询 适用的场景 全键值匹配精确匹配某个值 select .... where Namezhangsan; 左前缀匹配只精确到数据起始位置的一部分 select ... where Name like zhangsan%; 区间数据的连续数值匹配通常用于BETWEEN...AND...环境中 select ... where Age between 20 and 30; 区间数据的离散值匹配通常用于IN列表环境或OR列表环境也是精确匹配 select ... where StuID in (1,2,4); 精确匹配左列范围匹配右侧其它列 select ... where StuID 10 and Name like a%; 对于覆盖索引的查询请求 不适用的场景 如果查询条件不是精确从最左侧列开始的索引无效 如对StuID字段做了索引select ... where Name like a% and StuID 10; 如果索引了多列若跳过了索引中的某列则索引无效 如对StuID,Name,Age做索引select ... where StuID10 and Age20; 如果索引了多列且在查询语句中某个列做范围匹配则右侧列不再使用索引优化查询 HASH索引基于HASH表实现的索引 非常适用于值的精确匹配的查询请求 适用场景 只支持等值比较查询 如,IN(),(NULL safe equal) ; 不适用的场景 所有的非精确值的比较查询 注意 1.在InnoDB存储引擎中创建索引时只能显式使用“BTREE”索引 2.索引中的数据来源于数据表但数据结构与源数据可能有很大差异 4索引的查询优点 1. 减少需要扫描的数据总量减少IO次数 2. 避免对扫描的数据进行再次排序 3. 避免生成和使用临时表 4. 将随机IO转换为顺序IO 5 定义索引的一般性规则 1.选择用于索引的数据类型 越小的数据类型越适于做索引例如int和char 越简单的数据类型越适于做索引例如 char 和varchar 尽量避免该字段中出现“NULL”值如果必须要使用空值建议使用“0”或一个空的字符串 或某个认可的特殊值来代替“NULL”值 2.选择主键的类型 优先选择整型 整型数据可以更快速被处理且可以使用AUTO_INCREMENT修饰符避免重复数据 尽量避免使用字符型 存储字符型数据需要消耗更多空间处理字符型数据需要消耗更多的CPU和内存资源处理速度较慢 可能导致页面分裂随机IO等问题 3. EXPLAIN 查看帮助文档 MariaDB [hellodb] help explain; 格式explain_type: EXTENDED| PARTITIONS Or: EXPLAIN tbl_name 示例 MariaDB [hellodb] explain select * from students where StuID30\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentstype: rangepossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: NULLrows: 26Extra: Using where1.id当前的查询语句中各个select语句的编号2.select_type:查询类型简单查询SIMPLE复杂查询简单的子查询(用于where子句中的子查询)SUBQUERY用于FROM语句中的子查询DERIVED联合查询中的第一个查询PRIMARY联合查询中其它的查询UNION联合查询时生成的临时表查询UNION RESULT3.table: 当前的查询语句所针对的表4.type: 关联类型或称访问类型也可理解为MySQL是如何查询表中的行1 ALL全表扫描MySQL将遍历全表找到可以匹配的行2 index全表扫描与ALL不同的是index类型只是遍历索引树3 range索引范围扫描对索引的扫描从某一个点开始返回匹配值域的行通常可以基于指定的索引where子句中使用IN列表BETWEEN...AND....或带有“”“,的查询4 ref: 使用非唯一索引扫描或使用唯一索引的做左前缀扫描返回匹配某个单独值得行5 eq_ref: 类似ref,区别是使用唯一索引对于每一个索引键值表中都只有一条记录匹配无论是单表查询还是多表查询都使用主键或唯一键索引作为关联条件6 const,system: 当MySQL对查询部分进行优化并转换为一个常量使用const类型system类型是一个const类型得特例当要查询得表是一行时使用system类型;7 NULL : MySQL在优化过程中分解查询语句执行时不用访问表或索引5.possible_keys: 为了执行查询语句MySQL可能使用哪个索引在表中查找到记录如果查询所涉及到的字段上如果存在索引则该索引被列出但不一定被查询使用6. key: 显示MySQL数据库在查询过程中实际使用到的索引如果查询过程没有用到任何索引则此处显示“NULL”7. key_len: 表示索引中可以被引用的最大字节数可通过该列计算查询中使用的索引的长度注意key_len显示的值通常为索引字段的最大可能长度并非实际使用长度因此key_len是根据表定义时指定的字段长度计算得到的并不是在表中通过通过检索数据得到的8. ref: 在利用key字段所显示的索引完成查询操作时所引用的列或常量值若果都没有则显示为NULL 9. rows: 表示MySQL根据表统计信息及索引选用的情况估算的本次检索所需要查找所有记录的过程中需要读取表的行数10.Extra额外信息或称扩展信息Using where 表示MySQL服务器将在存储引擎检索后再次进行条件过滤在许多的where条件里涉及到索引中的列并且当MySQL读取该索引时就可被存储引擎检索Using index 使用了覆盖索引进行检索Using temporary在查询过程中使用了临时表存放查询结果集Using filesort MySQL中无法利用索引完成的排序操作就称为“文件排序”Using join buffer 强调了在获取连接条件时没有使用到索引且需要连接缓冲区来存储中间结果如果出现了该值需要根据查询的具体情况适当的添加索引以提示查询性能Impossible where若该值出现则意味着在查询时没有发现符合条件的行Select tables optimized away 该值意味着仅通过使用索引来进行查询但是优化器可能从聚合函数的结果中给出一个可行优化方案11.filtered从可选的行中再次过滤之后选择出最终的查询结果的比值即从多少行中过滤选择出多少行的比值 转载于:https://blog.51cto.com/yuantianchi/2151128
