MySQL/MariaDB基础(2)

hyperv

MariaDB查询缓存　　缓存中的数据是开源形式的，以键值对(k/v)的形式存在
　　key：查询语句的hash值；
　　value：查询语句的查询结果；
　　缓存中的数据主要是通过整个查询语句的hash值的比较，完全相同则命中；这样通过缓存响应客户端请求，可以提高检索效率；当然，也不是所有的查询数据都可以缓存，那么哪些数据不能够缓存呢？
1.要查询的数据库中可能包含敏感信息；如MySQL数据库中的各系统表；　　
2.在查询语句中包含有用户自定义的函数(UDF)
　　
3.存储函数；
　　
4.用户自定义变量；
　　
5.对于临时表发起的查询请求；
　　
6.包含列级别授权的查询；
　　
7.有着不确定结果值的mysql的内键函数；如：NOW(),CURRENT_DATE(),CURRENT_TIME(),CURRENT_USER(),...
　　与查询缓存相关的服务器参数:
MariaDB [hellodb]> 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  |
　　
+------------------------------+---------+
　　query_cache_limit：能够缓存查询结果的最大字节数上限；(单语句结果集的上限)
　　在使用SELECT查询语句时，应尽量避免"SELECT *"查询方式的应用，同时需要WHERE子句或HAVING子句对查询结果进行优化处理；以使得查询结果尽可能精确；
　　对于哪些有着较大的查询结果集的语句，应该在SELECT中明确显式的使用SQL_NO_CACHE参数，以避免查询结果先如缓存再移除缓存；
　　query_cache_min_res_unit：查询缓存中，内存块的最小分配单元；可以比较有效的避免内存碎片；
　　较大的变量值可能会带来内存空间浪费；
　　较小的变量值会减少内存空间浪费，但会导致更加频繁的内存分配和回收操作；长期下来必然产生内存碎片；
　　query_cache_size：查询缓存申请的在内存中的总可用空间；
　　单位是字节，设定的值必须是1024的整数倍；
　　query_cache_strip_comments：用于控制是否去掉SQL查询语句中的注释部分之后再作为key部分存入查询缓存；
　　默认值是"OFF"，如果启用，插入查询缓存中的查询语句是不带有注释部分信息的
　　query_cache_type：缓存功能开启与否的开关；
　　ON：启用；仅不缓存"SQL_NO_CACHE"参数的查询结果；
　　OFF：停用缓存；
　　DEMAND：按需缓存；默认不缓存，仅缓存"SQL_CACHE"参数的查询结果；
　　query_cache_wlock_invalidate：如果某个连接会话对某表施加了写锁，是否依然可以从缓存中查询并返回查询结果；
　　默认值为"OFF"，表示可以；
　　ON，表示不可以；
与查询缓存相关的服务器参数:

　　Qcache_free_blocks：表示查询缓存中目前还剩余多少个blocks；如果该值显示较大，则说明查询缓存中内存碎片过多；
　　Qcache_free_memory：查询缓存中还空闲的内存空间；
　　通过此状态参数可以评估当前系统中的查询缓存的使用情况；
　　如果剩余量太小，且剩余块很多，大量的内存空间均以碎片形式存在；
　　如果剩余量太小，且剩余块不多，分配的查询缓存的内存空间刚好或略有欠缺；
　　如果剩余量较大，分配的查询缓存的内存空间太多，应进行相应调整；一旦调整了缓存大小，则其中存放的查询结果会立即清除；
　　Qcache_hits：表示查询缓存中查询语句的命中次数；数字越大缓存效果越理想；
　　Qcache_inserts：表示未命中的而后经过处理将查询结果添加至查询请求的数量；
　　数值越大，则证明查询缓存效果越不理想；
　　可以通过规范书写查询请求的SQL语句减少此类查询请求的数量；
　　注意：如果查询缓存中确实没有对应查询语句的查询缓存，此数值的增加也是正常现象；
　　Qcache_lowmem_prunes：该参数记录了有多少条查询请求是因为内存空间不足而基于LRU算法被移除缓存的；如果该数值过大，则表示分配的内存空间太小；
　　Qcache_not_cached：取决于query_cache_type变量的设置的作用下，没有被缓存的查询请求的数量；
　　Qcache_queries_in_cache：当前查询缓存中缓存的查询请求的结果的数量；
　　Qcache_total_blocks：当前查询缓存中总计分配了多少个block；
　　查询缓存命中率的计算：Qcache_hits/( Qcache_hits + Qcache_inserts )
　　注意：如果对表进行了写操作，例如增删改等操作，则MySQL会自动将查询缓存中与该表相关的缓存项全部清除；因此与此表有关的查询请求必须重新构建缓存内容；
　　缓存的优化方式：
1.尽可能批量写入(构建自定义过程；启用事务)，尽量减少多次的单写入操作；　　
2.缓存空间不宜设置过大，如果大量缓存同时失效，会使得MySQL的执行引擎压力剧增，可能会导致服务器假死；
　　
3.必要时，需要使用SQL_CACHE和SQL_NO_CACHE等SELECT语句中的参数来手动控制缓存存入与否；
　　
4.对于密集型写操作应用场景来说，禁用缓存功能可能提升服务器性能；
　　接下来做个实例：
　　1.首先要保证query_cache_type开启：

　　2.为缓存空间设置一个大小

　　3.查询一下某个数据的数据表；

　　//在状态参数中未命中出现一次，因为没有缓存；并且这次查询完成后，缓存了一条信息；

　　4.再次进行与上次查询一样的查询；
　　//这次hits段命中了一次；你可以多次查询查看命中次数；

MySQL的索引　　那么是为了什么而提出的索引呢，即索引的作用是什么呢：当MySQL中数据过多时，要求检索的速度也应该高，所以，为了提高检索速度，提出了索引这一概念；什么是索引：也就是指定的表中的数据子集；即：将表中某个或某些字段中的数据提取出来，另存为用一个特定数据结构进行组织的数据。
　　索引有不同类型：FULLTEXT，SPACIAL，B+ TREE，HASH；由于存储引擎不同，支持的索引的类型也不同：
InnoDB存储引擎支持：B+ TREE，HASH；　　
MEMORY存储引擎：显示的支持HASH索引；
　　下面大概描述一下不同的索引：
B+ TREE索引：也就是顺序存储，所有的存储数据都存放在叶节点上，并且每个叶节点都有顺序访问指针，以此指针指向相邻的叶子节点；　　B+ TREE索引使用的场景：
1.全键值匹配：精确匹配某个值；　　
    select ... where Name='Xu Wenlong'
　　
2.左前缀匹配：值精确到数据起始位置的一部分：
　　
    select ... where Name like 'G%'
　　
3.区间数据的连续数值匹配，通常用于BETWEEN...AND...环境中：
　　
    select ... where age between 30 and 50;
　　
4.区间数据的离散值匹配；通常用于IN列表环境或OR列表环境；也是精确值匹配；
　　
    select ... where StuID IN (1,3,7)
　　
5.精确匹配左列，范围匹配右侧其他列；
　　
    select ... where StuID > 10 and name like 'a%';
　　
6.对于覆盖索引的查询请求：
　　不适用的场景：
1.如果查询条件不是精确从最左侧列开始的，索引无效；　　
    对StuID字段做索引，select ... where Name like 'A%' and StuID>10;
　　
2.如果索引了多列，若跳过索引中的某列，索引无效：
　　
    对StuID,Name,Age做索引，select ... where StuID>10 and Age>20;
　　
3.如果索引了多列，且在查询语句中对某个列做范围匹配，则其右侧列不能再使用索引优化查询；
　　
    对StuID,Name,Age做索引，select ... where StuID>10 and Name like 'a%';
HASH索引：基于HASH表实现的索引；非常适用于值的精确匹配的查询请求；　　适用的场景：只支持等值比较查询：如：=,IN(),(NULL safe equal());
　　不适用场景：所有的非精确值的比较查询：
　　索引的优点：
1.较少需要扫描的数据总量，减少IO次数；　　
2.脚面对扫描的数据进行再次排序；
　　
3.避免生成和使用临时表；
　　
4.将随机IO转换成顺序IO；
　　定义索引的一般性规则：
1.选择用于索引的数据类型；　　
    越小的数据类型越适于做索引；
　　
    越简单的数据类型越适于做索引；
　　
    尽量避免该字段中出现"NULL"值；如果必须要使用空值；建议使用"0"或一个空的字符串或者某个认可的特殊值来代替"NULL"值；
　　
2.选择主键的类型；
　　
    优先选择整型；
　　
    ×××数据可以更快速被处理；而且可以使用AUTO_INCREMENT修饰符避免重复数据；
　　
    尽量避免使用字符型；
　　
    存储字符型数据需要消耗更多的空间，处理字符型数据需要消耗更多的CPU和内存资源，处理速度较慢；
　　高性能索引的定义策略：
1.在WHERE子句中以独立使用某列做判断条件为最佳，尽量避免该列直接参数运算；　　
    select ... where Age+2>10;
　　
2.最左前缀索引：索引应该构建于字段的最左侧的一个或连续的多个字符；具体的选择数量可以通过"索引选择性"进行评估；
　　
    索引选择性：不重复的索引值和数据表的记录总数的比值；
　　
3.多列索引的选择；
　　
    WHERE条件子句中使用AND运算符连接多个查询条件；尽量避免使用OR运算符；
　　
    如果使用OR运算符，应该基于每个列做单列索引；
　　
4.选择合适的索引定义次序；选择性最高的列放在最左侧；
　　EXPLAIN语句
　　对索引进行分析，查看索引的应用信息；
　　用法：
EXPLAIN [explain_type] SELECT select_options　　

　　
explain_type: EXTENDED | PARTITIONS
　　EXTENDED：显示扩展信息；
　　PARTITIONS：用于分区表；
　　下面来上一个例子：

　　咱们对个字段来做个解释：
　　id：当前的查询语句中，各个SELECT语句的编号；
　　select_type：查询类型；
简单查询：SIMPLE  //即通过交叉连接来查询；

复杂查询：　　简单子查询(用于WHERE子句中的子查询)：SUBQUERY

　　用于FROM语句中的子查询；DERIVED

　　联合查询中的第一个查询：PRIMARY
　　联合查询中其他的查询：UNION

　　联合查询时生成的临时表查询：UNION RESULT

　　table：当前的查询语句所针对的表；
　　type：关联类型，或称为访问类型；也可以理解为MySQL是如何查询表中的行；
ALL：全表扫描；，MySQL将遍历全表以找到可以匹配的行；　　
index：全表扫描，与ALL所不同的是index类型只是遍历索引树；
　　
range：索引范围扫描，对索引的扫描从某一个点开始，返回匹配某一个值域的行；
　　
通常可以基于指定的索引，WHERE子句使用IN列表、BETWEEN...AND...、或带有"=",">","