mysql服务器变量、缓存及索引

最新推荐文章于 2024-11-15 22:11:31 发布
最新推荐文章于 2024-11-15 22:11:31 发布
阅读量120 收藏
点赞数
文章标签： 数据库 数据结构与算法
原文链接：http://www.cnblogs.com/yaun1498078591/p/9164392.html
版权

服务器变量

注意：其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效；有些参数作用域是全局的，且不可改变；有些可以为每个用户提供单独（会话）的设置。

show variables;查看所有变量
# mysqld_safe –-skip-name-resolve=1：在启动服务时加选项参数，禁止反解析IP，提高登录效率；
同时也可以将skip_name_resolve=1参数加入到my.cnf配置文件中

服务器系统变量

服务器变量分为动态变量和非动态变量，动态变量可以在服务器不重启的情况下修改

服务器变量还分全局变量和会话变量，全局变量修改后影响后来登录的用户；会话变量只影响当前会话，退出后失效
show global variables;查看所有全局变量

show warnings 查看报警信息

修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效

mysql> SET GLOBAL system_var_name=value; 
mysql> SET @@global.system_var_name=value;

修改会话变量：

mysql> SET [SESSION] system_var_name=value; 
mysql> SET @@[session.]system_var_name=value; 　

服务器状态变量

查看状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改

MariaDB [(none)]> SHOW GLOBAL STATUS;  #全局状态变量
MariaDB [(none)]> SHOW STATUS;  #会话状态变量

参考官方文档：

SQL_MODE

sql_mode既是选项有是变量，作用对其设置可以完成一些约束检查的工作，可分别进行全局的设置或当前会话的设置。

MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_mode';  #查看sql_mode变量，默认为空
  • NO_AUTO_CREATE_USER 禁止GRANT创建密码为空的用户
  • NO_AUTO_VALUE_ON_ZERO 在自增长的列中插入0或NULL将不会是下一个自增长值
  • NO_BACKSLASH_ESCAPES 反斜杠“”作为普通字符而非转义字符
  • PAD_CHAR_TO_FULL_LENGTH 启用后，对于CHAR类型将不会截断空洞数据
  • PIPES_AS_CONCAT 将"||"视为连接操作符而非“或运算符”
  • TRADITIONAL：包括STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION，是一个参数的集合
MariaDB [(none)]> SET  sql_mode='TRADITIONAL';

参考官方文档：https://mariadb.com/kb/en/library/sql-mode

查询缓存

查看是否开启缓存

  1. SQL语句
  2. 查询缓存
  3. 解析器
  4. 解析树
  5. 预处理
  6. 查找最好的查询路径
  7. 查询优化SQL语句
  8. 执行计划
  9. API调用存储引擎

  10. 调用数据，返回结果

    缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写。

    不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果，提高查询性能

    查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低其效率；查询缓存的使用，会增加检查和清理Query Cache中记录集的开销

哪些查询可能不会被缓存：
  • 查询语句中加了SQL_NO_CACHE参数；
  • 查询语句中含有获得值的函数，包含自定义函数，如：NOW()、CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等；
  • 对系统数据库的查询：mysql、information_schema 查询语句中使用SESSION级别变量或存储过程中的局部变量；
  • 查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句，查询语句中类似SELECT …INTO 导出数据的语句；
  • 对临时表的查询操作；存在警告信息的查询语句；不涉及任何表或视图的查询语句；某用户只有列级别权限的查询语句；
  • 事务隔离级别为Serializable时，所有查询语句都不能缓存。
查询缓存相关的服务器变量：
  • query_cache_min_res_unit: 查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足；
  • query_cache_limit：单个查询结果能缓存的最大值，默认为1M，对于查询结果过大而无法缓存的语句，建议使用SQL_NO_CACHE；
  • query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报；
  • query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以从查询缓存中返回结果，默认值为OFF，表示可以在表被其它会话锁定的场景中继续从缓存返回数据；ON则表示不允许；
  • query_cache_type: 是否开启缓存功能，取值为ON, OFF, DEMAND，默认值为ON 
    值为OFF或0时，查询缓存功能关闭；
值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存；
值为DEMAND或2时，查询缓存功能按需进行，显式指定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存。
MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
优化查询缓存：

查询缓存相关的状态变量：
  • Qcache_free_blocks：处于空闲状态 Query Cache中内存 Block 数；
  • Qcache_free_memory：处于空闲状态的 Query Cache 内存总量；
  • Qcache_hits：Query Cache 命中次数；
  • Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，即没有命中的次数；
  • Qcache_lowmem_prunes：当 Query Cache 内存容量不够，需要删除老的Query Cache 以给新的 Cache 对象使用的次数；
  • Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL语句；
  • Qcache_queries_in_cache：在 Query Cache 中的 SQL 数量；
  • Qcache_total_blocks：Query Cache 中总的 Block。
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 33536824 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 4        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
命中率和内存使用率估算：
  • 查询缓存中内存块的最小分配单位query_cache_min_res_unit ：(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
  • 查询缓存命中率 ：Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%
  • 查询缓存内存使用率：(query_cache_size – qcache_free_memory) / query_cache_size * 100%

    索引

    ​ 索引是特殊数据结构：定义在查找时作为查找条件的字段，索引实现在存储引擎。

    • 索引可以降低服务需要扫描的数据量，减少了IO次数
    • 索引可以帮助服务器避免排序和使用临时表
    • 索引可以帮助将随机I/O转为顺序I/O
    • 但是占用额外空间，影响插入速度
    1、索引类型：

    • B + Tree 索引：顺序存储，每一个叶子节点到根的距离都是相同的，左前缀索引，适合查询范围类的数据；
    • 适合使用B-Tree索引的查询类型
      • 全值匹配
      • 匹配最左前缀
      • 匹配范围值
      • 精确匹配某一列并范围匹配另一列（复合索引）
      • 只访问索引的查询
    • 不适合使用B-tree索引的查询类型
      • 不从最左列开始
      • 不能跳过索引中的列
      • 如果查询中某个列是为范围查询那么右侧的列无法再使用索引优化查询

    • Hash索引：基于哈希表，构建出键值对的索引，特别适用于精确匹配索引中的索引列，只支持等值比较查询（IN，=，<>）；不适合于顺序查询，不支持模糊匹配；只有Memory存储引擎支持显式Hash索引

    • 空间索引（R - Tree）：只有MyISAM支持空间索引

    • 全文索引（FULL TEXT）：在文本中查找关键词

    2、高性能索引策略：
    • 独立使用列，尽量避免其参与运算
    • 使用左前缀索引：索引构建于字段的左侧的多少字符要通过索引选择性来评估；索引选择性：不重复的索引值和数据表的记录总数的比值
    • 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
    • 选择合适的索引列次序：无排序和分组时，将选择性最高放左侧
    3、索引的优化建议
    • 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
    • 尽量使用短索引，如果可以，应该制定一个前缀长度
    • 对于经常在where子句使用的列，最好设置索引
    • 对于有多个列where或者order by子句，应该建立复合索引
    • 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
    • 尽量不要在列上进行运算（函数操作和表达式操作）
    • 尽量不要使用not in和<>操作
    • 多表连接时，尽量小表驱动大表，即小表 join 大表
    • 在千万级分页时使用limit
    • 对于经常使用的查询，可以开启缓存
    • 大部分情况连接效率远大于子查询
    4、索引的创建与删除
    • 创建索引

    CREATE INDEX index_name ON tbl_name (index_col_name,...);

    MariaDB [hellodb]> CREATE INDEX index_name ON students(name);  #创建简单索引
    MariaDB [hellodb]> CREATE INDEX index_name_age ON students(name,age);  #创建复合索引
    • 查看索引

    SHOW INDEXES FROM [db_name.]tbl_name;

    MariaDB [hellodb]> SHOW INDEX FROM students\G
    • 删除索引

    DROP INDEX index_name ON tbl_name;

    MariaDB [hellodb]> DROP INDEX index_name ON students;
    • 优化表空间
    MariaDB [hellodb]> OPTIMIZE TABLE students;
    • 查看索引使用的情况

    启用记录索引使用情况：SET GLOBAL userstat=1;
    查看索引使用情况：SHOW INDEX_STATISTICS;

    EXPLAIN命令

    通过EXPLAIN来分析索引的有效性：EXPLAIN SELECT clause，获取查询执行计划信息，用来查看查询优化器如何执行查询

    MariaDB [hellodb]> EXPLAIN SELECT name FROM students WHERE name = 'Lin Daiyu'\G
*************************** 1. row ***************************  id: 1 select_type: SIMPLE  table: students  type: ref possible_keys: index_name_age  key: index_name_age  key_len: 152  ref: const  rows: 1  Extra: Using where; Using index
    • id：当前查询语句中，每个SELECT语句的编号；复杂类型的查询有三种：简单子查询、用于FROM子句中的子查询、联合查询（UNION，注意：UNION查询的分析结果会出现一个额外匿名临时表）
    • select_type：
    • SIMPLE ：简单查询
    • SUBQUERY: 简单子查询
    • PRIMARY:最外面的SELECT
    • DERIVED: 用于FROM中的子查询
    • UNION：UNION语句的第一个之后的SELECT语句
    • UNION RESULT: 匿名临时表
    • table：SELECT语句关联到的表
    • type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以下顺序，性能从低到高
    • ALL: 全表扫描
    • index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描
    • range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点
    • ref: 根据索引返回表中匹配某单个值的所有行
    • eq_ref：仅返回一个行，但与需要额外与某个参考值做比较
    • const, system: 直接返回单个行
    • possible_keys：查询可能会用到的索引
    • key: 查询中使用到的索引
    • key_len: 在索引使用的字节数
    • ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值
    • rows：MySQL估计为找所有的目标行而需要读取的行数
    • Extra：额外信息
    • Using index：MySQL将会使用覆盖索引，以避免访问表
    • Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤
    • Using temporary：MySQL对结果排序时会使用临时表
    • Using filesort：对结果使用一个外部索引排序

      我们可以统计不经常使用的索引从而进行优化

      EXPLAIN命令

      通过EXPLAIN来分析索引的有效性：EXPLAIN SELECT clause，获取查询执行计划信息，用来查看查询优化器如何执行查询

      MariaDB [hellodb]> EXPLAIN SELECT name FROM students WHERE name = 'Lin Daiyu'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ref
possible_keys: index_name_age
          key: index_name_age
      key_len: 152
          ref: const
         rows: 1
        Extra: Using where; Using index
      • id：当前查询语句中，每个SELECT语句的编号；复杂类型的查询有三种：简单子查询、用于FROM子句中的子查询、联合查询（UNION，注意：UNION查询的分析结果会出现一个额外匿名临时表）
      • select_type：
      • SIMPLE ：简单查询
      • SUBQUERY: 简单子查询
      • PRIMARY:最外面的SELECT
      • DERIVED: 用于FROM中的子查询
      • UNION：UNION语句的第一个之后的SELECT语句
      • UNION RESULT: 匿名临时表
      • table：SELECT语句关联到的表
      • type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以下顺序，性能从低到高
      • ALL: 全表扫描
      • index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描
      • range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点
      • ref: 根据索引返回表中匹配某单个值的所有行
      • eq_ref：仅返回一个行，但与需要额外与某个参考值做比较
      • const, system: 直接返回单个行
      • possible_keys：查询可能会用到的索引
      • key: 查询中使用到的索引
      • key_len: 在索引使用的字节数
      • ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值
      • rows：MySQL估计为找所有的目标行而需要读取的行数
      • Extra：额外信息
      • Using index：MySQL将会使用覆盖索引，以避免访问表
      • Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤
      • Using temporary：MySQL对结果排序时会使用临时表
      • Using filesort：对结果使用一个外部索引排序

     

     

     

 

转载于:https://www.cnblogs.com/yaun1498078591/p/9164392.html

weixin_33842328
关注
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL数据库基础知识6，缓存表、视图、计数器表、自定义变量
学Java，找哪吒
11-13 4331
MySQL进阶实战系列，打造精品专栏。
程序员必备手册CSS参考手册3.0.chm，HTML5 参考手册.chm，MySQL 5.1参考手册.chm，ORACLE 九阴真经
12-08
CSS参考手册3.0.chm，HTML5 参考手册.chm，MySQL 5.1参考手册.chm，ORACLE 九阴真经.chm，oracle命令速查.chm，正则表达式系统教程.CHM，织梦《正则表达式》.chm
面试：redis基础知识
qq_36198826的博客
03-25 968
主要内容出自：Java知识体系最强总结(2020版) 一、Redis数据类型及应用场景 数据类型 可以存储的值 操作 string 字符串、整数或者浮点数 对整个字符串或者字符串的其中一部分执行操作对整数和浮点数执行自增或者自减操作 list 列表 从两端压入或者弹出元素对单个或者多个元素进行修剪，只保留一个范围内的元素 set 无序集合 添加、获取、移除单个元素检查一个元素是否存在于集合中计算交集、并集、差集从集合里面随机获取元素 hash 包含键值对的无序散列表 添加、获取、
MySQL常用优化
xp_lx12的博客
03-19 2332
一、EXPLAIN 做MySQL优化，我们要善用 EXPLAIN 查看SQL执行计划。 下面来个简单的示例，标注(1,2,3,4,5)我们要重点关注的数据 type列，连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别 key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式 key_len列，索引长度 row...
【翻译】临时对象的缓存
weixin_30731287的博客
06-02 257
原文地址：http://www.sqlmag.com/Article/ArticleID/101993/sql_server_101993.html 原作者：Itzik Ben-Gan 翻 译：周强 SQL SERVER 2005 在缓存临时对象方面作了很大的增强。你能在Working with tempdb in SQL Server 2005这篇文章中找到关于这些增强的...
MYSQL创建、删除、索引和更改表
zgqtxwd的专栏
05-01 528
<!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--><script type="text/javascript"
第17章-MySQL查询缓存
Python大家一起学
09-25 1110
MySQL入门教程（保姆级）
Mysql 缓存策略
you_fathe的博客
10-31 1631
mysql 缓存方案，缓冲穿透，缓冲击穿，穿冲雪崩
MySQL的缓存机制
像豆芽一样优秀
10-27 4078
一、查询缓存 为什么MySQL8.0直接把查询缓存的功能删除了呢？ 一种说法是不建议使用查询缓存。因为查询缓存往往弊大于利。 1. 什么是查询缓存？ MySQL查询缓存即缓存查询数据的SQL文本及查询结果,用Key-Value的形式保存在服务器内存中。当查询命中缓存,MySQL会立刻返回结果,跳过了解析,优化和执行阶段。 2. 查询缓存的命中条件有哪些？ 首先你得确保开启了查询缓存。(手动狗头 MySQL将缓存存放在一个引用表(类似HashMap的数据结构)。通过一个哈希值索引,这个索引通过查询本身、当前
MySQL需要关注的参数及状态变量
热门推荐
shefin90的博客
05-09 4万+
open_files_limit 操作系统允许mysqld打开的文件数量。这个值可以设置得比较大，比如50000，最好在系统初始化安装时就设置了一个较大的值。可修改文件/etc/security/limits.conf来实现，命令如下： vi /etc/security/limits.conf * -nofile 50000 max_connect_errors 此值应设置得比较大，如大于5000，以避免因为连接出错而超过出错阈值，导致MySQL阻止该主机连接，如被阻塞，则须手动执行flush-hos
MySQL 5.5 服务器变量详解
07-16
### MySQL 5.5 服务器变量详解 #### autocommit={0|1} - **定义**: 控制MySQL事务是否在每次执行数据修改语句后自动提交。设置为1时，每条修改语句（如 INSERT、UPDATE 或 DELETE）都会立即提交；设置为0则需要...
MySQL 服务器参数说明及查看 设置方法
09-11
MySQL服务器参数是数据库管理系统的核心配置，它们决定了MySQL服务器的行为、性能和稳定性。了解并正确设置这些参数对于优化数据库性能至关重要。下面将详细解释一些重要的MySQL服务器参数及其作用： 1. `back_log`...
mysql缓冲和缓存设置详解
09-09
MySQL的缓冲和缓存设置是数据库性能优化的关键因素，尤其是在高并发和大数据量的场景下。本文将深入探讨MySQL中涉及的几个重要缓存设置，包括表缓存、线程缓存、关键字缓冲区和临时表的内存使用。 首先，我们关注的...
MySql之索引
猎户星座
11-05 3万+
Mysql 索引精讲 开门见山，直接上图，下面的思维导图即是现在要讲的内容，可以先有个印象～ 常见索引类型(实现层面) 索引种类(应用层面) 聚簇索引与非聚簇索引 覆盖索引 最佳索引使用策略 1.常见索引类型(实现层面) 首先不谈Mysql怎么实现索引的,先马后炮一下,如果让我们来设计数据库的索引，该怎么设计？ 我们首先思考一下索引到底想达到什么效果？其实就是想能够实现快速查...
redhat 7.6 oracle 19c 单机离线安装（文末附安装的虚拟机，可以直接运行）
hf191850699的专栏
11-11 764
Oracle 19c离线单机的安装，包括补丁，后期的配置和维护等常用命令记录，文末有实验的虚拟机，下载就可以打开。
Mybatis
庸不易的博客
11-15 644
1、定义不同：#{} 预处理：而 ${} 是直接替换2、使用不同：#{} 适用于所有类型的参数匹配；但 ${} 只适用于数值类型。3、安全性不同：#{} 性能高，并且没有安全问题；但 $ {} 存在 SQL 注入的安全问题。4、使用场景不同：当传递的是一个 SQL 关键字 的时候，只能 使用 ${}。当传递的是一个字段，总之，就是需要获取到参数类型 与 内容，只能使用 #{}。（PS：数字类型，#{} 和 ${} 都是可以使用的！5、 ${} 不能用于 模糊匹配查询；
MySQL 8.0特性-自增变量的持久化
myneth的博客
11-13 461
在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。下面通过案例来对比不同的版本中自增变量是否持久化。
Qt 的 QThread：多线程编程的基础
最新发布
Z1tai的博客
11-15 586
是 Qt 框架中实现多线程的基础类，它允许开发者创建新的线程，并在其中执行任务。当线程执行任务时，可能会出现阻塞，尤其是在进行 I/O 操作或等待外部资源时。在 Qt 中，线程间的通信通过信号与槽机制来实现。通过信号，子线程可以将数据或事件传递给主线程，或者主线程可以通知子线程执行某些操作。对象代表一个线程，线程之间的独立执行有助于分担主线程的压力，避免主界面的阻塞。Qt 中的多线程模型基于事件驱动机制，因此线程的管理通常通过事件循环来进行，方法会在新线程中执行，因此你可以将具体的工作逻辑放在该方法中。
MySQL日志管理：种类、功能与服务器变量解析
"MySQL日志管理详解，包括日志种类、日志功能以及MySQL中的服务器变量说明，涵盖了错误日志、一般查询日志、慢查询日志、二进制日志、中继日志和事务日志等内容。" 在MySQL数据库管理系统中，日志管理是维护系统...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值