mysql myisam 查询缓存_mysql基础之查询缓存、存储引擎

一、查询缓存

“查询缓存”，就是将查询的结果缓存下载，如果查询语句完全相同，则直接返回缓存中的结果。

如果应用程序在某个场景中，需要经常执行大量的相同的查询，而且查询出的数据不会经常被更新，那么，使用查询缓存会有一定的性能提升。

查看当前服务是否开启了查询缓存功能：

MariaDB [ren]> show variables like '%query_cache%';+------------------------------+---------+

| Variable_name | Value |

+------------------------------+---------+

| have_query_cache | YES |

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 1048576 |

| query_cache_strip_comments | OFF |

| query_cache_type | OFF |

| query_cache_wlock_invalidate | OFF |

+------------------------------+---------+

7 rows in set (0.00 sec)

query_cache_type的值设置了OFF，表示目前没有开启查询缓存功能

query_cache_type的值可以设置为：ON、OFF、DEMAND，分别表示已启用、已禁用、按需缓存，设置在配置文件/etc/my.cnf.d/server.cnf中即可。

have_query_cache的值为YES，表示当前数据库支持缓存功能

query_cache_limit表示单条查询缓存的最大值，如果查询结果超过此值的大小，即时指定缓存当前结果，结果也不会被缓存，默认值为1M。

query_cache_min_res_unit表示缓存存储于内存的最小单元，默认为4K，也就是说，即时查询结果只有1K，也会占用4K内存，所以，如果此值设置的过大，会造成内存空间的浪费，如果此值设置的过小，则会频繁的分配内存单元或者频繁的回收内存单元。

query_cache_size表示查询缓存的总大小，也就是说，内存中用于查询缓存的空间大小，如果其值为0，即时开启了查询缓存，也无法缓存。

query_cache_wlock_invalidate表示查询语句所查询的表如果被写锁锁定，是否仍然使用缓存返回结果。也就是“查询缓存遭遇写锁时是否失效”，设置为OFF表示“不失效”；设置为ON表示“失效”。当此值设置为ON，如果表被施加了写锁，那么当写锁释放时，数据可能发生了改变，所以在表被施加写锁期间，即时此时有查询语句命中了查询缓存，也不能从缓存获取结果。(此值设置为OFF时，性能更好，并发能力更好，此值设置为ON时，更加安全，保证了数据的一致性)(写锁时独立的，排他的)

查询语句完全相同时，缓存才能够被命中，完全相同表示大小写也相同。

一般在数据变化不频繁，且又需要重复执行相同查询的场景中使用缓存。

二、使用查询缓存

使用方式：

开启缓存：query_cache_type=ON后，指定对应的查询语句不适用缓存：

select sql_no_cache name fromstu;

按需使用缓存：query_cache_type=DEMAND,指定对应的查询语句使用缓存select sql_cache name from stu;

第一种是默认符合缓存条件的都缓存，只有使用sql_no_cache指定的语句不缓存

第二种是默认所有查询语句的结果都不缓存，只有使用sql_cache指定的语句才会缓存

例子：

[root@ren7 ~]# vim /etc/my.cnf.d/server.cnf

#############################################[server]query_cache_type=DEMAND

query_cache_size=100M

#############################################[root@ren7 ~]# systemctl restart mariadb

查看查询缓存相关的参数：

MariaDB [ren]> show variables like '%query%';+------------------------------+---------------+

| Variable_name | Value |

+------------------------------+---------------+

| expensive_subquery_limit | 100 |

| ft_query_expansion_limit | 20 |

| have_query_cache | YES |

| long_query_time | 10.000000 |

| query_alloc_block_size | 16384 |

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 104857600 |

| query_cache_strip_comments | OFF |

| query_cache_type | DEMAND |

| query_cache_wlock_invalidate | OFF |

| query_prealloc_size | 24576 |

| slow_query_log | OFF |

| slow_query_log_file | ren7-slow.log |

+------------------------------+---------------+

14 rows in set (0.00 sec)

执行三次查询语句：

MariaDB [ren]> select sql_cache * from students where id=8;+----+--------------+------+------+--------+--------+-----------+

| id | name | age | high | gender | cls_id | is_delete |

+----+--------------+------+------+--------+--------+-----------+

| 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | |

+----+--------------+------+------+--------+--------+-----------+

1 row in set (0.00 sec)

查看缓存命中的情况：

MariaDB [ren]> show status like 'Qcache%';+-------------------------+-----------+

| Variable_name | Value |

+-------------------------+-----------+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 104838240 |

| Qcache_hits | 2 |

| Qcache_inserts | 1 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 0 |

| Qcache_queries_in_cache | 1 |

| Qcache_total_blocks | 4 |

+-------------------------+-----------+

8 rows in set (0.00 sec)

Qcache_free_blocks表示已分配的内存中空闲块的数量；

Qcache_free_memory表示查询缓存的空闲总量大小；

Qcache_hits表示以被缓存的条目的命中次数；

Qcache_inserts表示在未命中缓存时，将查询结果写入缓存的次数；

Qcache_lowmem_prunes表示用于查询缓存的内存区域的修剪次数(当用于缓存的内存被占满时，mysql会使用LRU算法清除命中率低的缓存项，从而空余出部分内存空间，用于缓存新的“查询缓存”)；

Qcache_not_cached表示没有被缓存的查询语句的数量；

Qcache_queries_in_cache表示已经缓存的SQL语句的数量；

Qcache_total_blocks表示当前查询缓存占用的内存的block数量。

查询缓存的碎片率 = (Qcache_free_blocks / Qcache_total_blocks)* 100%查询缓存利用率= (Qcache_cache_size - Qcache_free_memory) / query_cache_size * 100%query_cache_min_res_unit的预估值参考计算公式：(query_cache_size- Qcache_free_memory)/Qcache_queries_in_cache

查询缓存命中率= (Qcache_hits / Com_select)* 100%

Com_select表示查询语句的执行次数：

MariaDB [ren]> show status like 'Com_select%';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Com_select | 3 |

+---------------+-------+

1 row in set (0.00 sec)

flush query cache;可以清理查询缓存碎片，但并不会从缓存中移除任何缓存；

reset query cache;会从查询缓存中移除所有查询结果的缓存。

三、存储引擎

mysql中，存储引擎是插件式的，同一个数据库中的不同的表可以使用不同的存储引擎，所以，存储引擎是表级别的概念，存储引擎也被称为“表类型”，每张表可以使用不同的存储引擎类型。

mysql中最常用的存储引擎是innodb与myisam。

MYISAM：支持表级锁，不支持行级锁，不支持事务，不支持外键约束，支出全文索引，表空间文件相对小；

INNODB：支持表级锁，行级锁，支持事务，支持外键，不支持全文索引，表空间文件相对较大。

1、查看表类型，查看存储引擎

MariaDB [ren]>show engines;+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | Stores tables as CSV files | NO | NO | NO |

| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |

| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |

| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

8 rows in set (0.00 sec)