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_size=10485760; 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_size=10485760; 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_hits=0；
因为有一个select请求，所以将该请求列入缓存，即Qcache_inserts=1；
未缓存为1，即Qcache_not_cached=1
请求数为1,即Qcache_queries_in_cache=1
分配块数为4,即Qcache_total_blocks=4
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_hits=2
(因为之前相同的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） 索引的类型
FULLTEXT，SPACIAL，B+ TREE，HASH
B+ TREE索引: 顺序存储，所有的索引数据都放在叶节点上，并且每个叶节点都有顺序访问指针， 以此指针指向相邻的叶子节点，可提高区间数据的查询效率； 最左前缀索引：适用于高效的范围类数据查询； 适用的场景： 全键值匹配：精确匹配某个值； select .... where Name='zhangsan'; 左前缀匹配：只精确到数据起始位置的一部分； 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 StuID>10 and Age>20; 如果索引了多列，且在查询语句中某个列做范围匹配，则右侧列不再使用索引优化查询：
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 StuID<30\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: students type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 26 Extra: Using where 1.id：当前的查询语句中，各个select语句的编号； 2.select_type:查询类型 简单查询：SIMPLE 复杂查询： 简单的子查询(用于where子句中的子查询)：SUBQUERY 用于FROM语句中的子查询：DERIVED 联合查询中的第一个查询：PRIMARY； 联合查询中其它的查询：UNION 联合查询时生成的临时表查询：UNION RESULT 3.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：从可选的行中再次过滤之后选择出最终的查询结果的比值；即从多少行中过滤选择出多少行的比值；