MySQL的索引和查询缓存

2018-10-26  本文已影响0人  Net夜风

MySQL的索引：

EXPLAIN来分析索引有效性

用法：EXPLAIN [explain_type] SELECT select_options

    explain_type:
        EXTEBDED | PARTITIONS

示例：MariaDB [hellodb]> EXPLAIN SELECT * FROM students\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: students
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 25
            Extra: 
    1 row in set (0.00 sec)

注意：判断索引是否有效：
type类型中，all最差-->index-->range-->ref-->eq_ref-->const,system最好
一般保持在range上，避免冗余索引，移除无用索引；

示例：
    MariaDB [hellodb]> CREATE INDEX age ON students(Age);
    Query OK, 25 rows affected (0.02 sec)
    Records: 25  Duplicates: 0  Warnings: 0
    
    MariaDB [hellodb]> EXPLAIN SELECT Name FROM students WHERE Age>100;
    +------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
    | id   | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
    +------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
    |    1 | SIMPLE      | students | range | age           | age  | 1       | NULL |    1 | Using index condition |
    +------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
    1 row in set (0.00 sec)
：
        MariaDB [hellodb]> EXPLAIN SELECT Name FROM students WHERE Age=100;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
|    1 | SIMPLE      | students | ref  | age           | age  | 1       | const |    1 |       |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

MariaDB [hellodb]> EXPLAIN SELECT Name FROM students WHERE StuID=3;
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | students | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

查询缓存

mysql把整个查询缓存维持在内存中，内存空间的频繁的创建和回收，会导致内存碎片，类似于memcached一样，必须有个高效的内存分配回收算法，以尽可能降低内存碎片；
查询缓存是完全存储在内存中的，对整个内存空间的分配回收等，也会额外的产生系统资源消耗，此外，为了能够使用内存空间当做缓存，mysql服务器的查询缓存，还必须把一些内存空间拿来做缓存空间的元数据，例如整个空间中哪些分配了、哪些没分配等等，内置需要维护一个追踪表以始终得知哪些空间仍在在使用，哪些空间没被使用，一旦要有可能产生碎片时，怎么能降低碎片的产生等等；
不是所有场景中缓存查询都能提高性能的；缓存和失效都会带来额外开销，所以只有当带来的收益大于开销时，才使用查询缓存；
可通过缓存命中率来判断缓存是否足够有效；而命中率除了查询语句的命中率之外，还有结果集的命中率；虽然查询语句10个里，只有2个命中，但是这2个里结果集都很大处理起来都很慢，这个命中仍然认为收益是较大的；所以，也不能简单的以为语句命中率少就一定是命中率低；
内存空间不足，数据修改等都会造成缓存失效，如果配置了足够的缓存空间，而且对应的参数设置合理，缓存失效就只应该是数据修改导致的；所以要配置好查询缓存，避免缓存失效是因为空间过少或产生过多空间碎片导致缓存失效；

判断是否因为大部分查询都是不可缓存的导致不能命中；如果是这个原因导致的，则判断是否是因为query_cache_limit（查询结果最大上限）设置不够大导致不能缓存，如果不能缓存的原因是因为查询缓存的结果集超出了缓存所设定的上限导致，则增加query_cache_limit的值即可；如果不是因为query_cache_limit设定的值不够大导致的，则查询不能被缓存；

如果大部分查询结果都是可缓存的但是没有被缓存（缓存命中率不能被接受），判断是否发生了很多验证工作（验证缓存是否有效），如果是则判断缓存是否碎片化了，如果是碎片化了则要降低query_cache_min_res_unit参数值以减少碎片或使用FLUSH QUERY CACHE命令整理缓存；
如果缓存不是因为碎片过多导致的，则判断是否因为缓存空间过低而发生修正，就是缓存空间小，导致缓存被频繁清空，所导致缓存不能命中，如果是这个原因则增加query_cache_size参数值即可；
如果不是因为缓存空间太小导致缓存无法命中，则判断是否频繁更新语句，如果是表示为负载并不适合缓存则关闭缓存；如果不是频繁更新语句导致缓存失效，则可能是配置错误；

各种原因都不是，没有碎片、又不是不可缓存的、又没有发生验证工作、又没有被碎片化、sql语句又没有频繁更新则可认为是其它配置错误导致；

如果不是发送很多验证工作，则判断缓存是否启动，如果缓存启动，则从没见过该查询；如果缓存没启动则启动缓存即可；

注意：query_cache_size调整的话，整个缓存空间必须重新进行分配，所以整个缓存都会被失效的；因此，最好不要轻易调整此值；
如果要使用memcached来缓存数据，mysql的缓存就显得不那么重要了；可自行决定继续开启还是关闭；

上一篇 下一篇

猜你喜欢

热点阅读