mysql执行效率优化相关

目录

索引的一些补充

降序索引

比用按照用户名升序，然后按照最近一次修改时间降序。如果这两个字段都是升序索引，那么排序结果只会用上第一个字段，后面一个字段的顺序由字段排序算出来的，这时候就可以使用降序索引，创建索引应该按照用户名升序修改时间降序。

mysql 默认使用的升序索引，在8.0以后降序索引才真实的生效，之前只是预留了语法的支持。
降序索引使用在需要多个排序字段，并且有些字段是升序有些字段是降序的时候，

排序字段和索引排序方式不一样的时候，只有第一段索引可以用上。

语法：create index xxx(a,b desc0);

image-20250906153045884

可以考虑在需要排序的时间字段创建索引

隐藏索引(mysql8.0)

实际就是让索引失效，8.0之前只能真实的删除

image-20250906152857088

image-20250906153555602

部分索引

对于太长的变长字段可以只使用前坠部分字符做索引

不是使用整个字段，一般使用字段的钱20个字符就够了， left(字段名，20)，只用部分做索引，命中的时候会扫描同样前坠的数据，然后在做等值匹配(前坠相等，可以在比较一次全值)，但是排序结果准确性可能受到影响。

ALTER TABLE `hl`.`goods` 
DROP INDEX `name`,
ADD INDEX `name`(`name`(12), `des`(12), `des2`(16)) USING BTREE;

检查sql效率

查询mysql 状态和参数

查看状态：show STATUS

查看参数：show VARIABLES

比如Last_query_cost可以得到上次查询使用了多少个数据页
8.0的mysql可以通过max_execution_time 可以设置语句执行时间，之前执行都是会一直下去的，只有锁超有时间，现在执行语句也有超时了

#查询上次查询检索了多少个数据页，只有mysql命令行才是准确的，如果在工具上会被工具的指令打断。
select *from goods where id = 1964740863169413130;
show status like "%Last_query_cost%";

#mysql启动了多久
show status like "%Uptime%"

profile各阶段耗时

使用 profile查询sql执行过程中每个阶段的时间成本

#查询的是上次执行sql的执行阶段的数据。
show profile

#查询之前的查询耗时和编号
show profiles;

#查询指定编号的查询
show profile for query 1;

show profiles;的结果

image-20250906175913392

show profile 的结果

image-20250906180136107

可以指定cpu和读写信息

show profile cpu,block io for query 223;

image-20250906180451710

新版profile信息回写到

information_schema库的PROFILING表里面

慢查询关键参数

slow_query_log，默认off
long_query_time,默认10秒

min_examined_row_limit，默认是0，在一定时间，最少扫描了多少行记录才会算作慢查询

slow_query_log_file,慢查询日志文件地址，默认/var/lib/mysql/主机名-slow.log

show variables like "%slow_query_log_file%";

查询慢慢查询数量，这是一个状态，不是一个变量

show status like "%Slow_queries%";

慢查询日志分析工具：mysqldumpslow

#得到 按照时间排序的前10条 里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

如果需要重置慢查询日志

mysqladmin -uroot -p flush-logs slow

查询执行计划

explain select * from goods

image-20250907000446950

字段说明

EXPLAIN 语句输出的各个列的作用如下：

列名 描述
id 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type SELECT关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息
id

执行计划中有多条记录的时候执行顺序

  • id如果相同，可以认为是一组，从上往下顺序执行
  • 在所有组中，id值越大，优先级越高，越先执行
  • 关注点：id号每个号码，表示一趟独立的查询,一个sql的查询趟数越少越好
select_type

表示这段sql 的类型,一般是simple，表示普通查询，也可以是update，delete之类的，也可以是字查询，union之类的

type

检索数据的方式

结果值从最好到最坏依次是：

  • **system(有计数功能的数据库引擎，并且表里面只有一条数据)
  • ** const(唯一索引，唯一命中)
  • eq_ref (被驱动表的关联字段是唯一索引)>
  • ref(使用普通索引字段查询精确值，或者被驱动表的关联字段是普通索引 )
  • fulltext
  • ref_or_null
  • index_merge 不同字段的or查询，这时候实际使用了2条索引 比如 a =1 or b = 2，并且a，b都有索引
  • unique_subquery 相关子查询的相关字段都是唯一索引？
  • index_subquery
  • range 范围查询，大于，小于，or
  • index 查询的字段属于索引覆盖，但是使用了整个索引(where 匹配不上缩影)
  • ALL 全表扫描
possible_keys,key

可以用到的索引，和当前会默认会使用的索引，一般一条sql 只会用一条索引，但是or连接的不同字段可能会用上2条索引，这时候检索类型(type)会显示 index_merge

key_len

这个字段表示使用到的索引长度多少个字节，int是4，bigint是8，字符类型占用空间和列编码有关，ascii 是是一个char(1)占用1个字节，utf8mb3 的char(1)占用三个字节，utf8mb4 的char(1)占用4个字节，大多数情况我们字段的字符集都是来自数据库创建时候指定的字符集(如果创建表的时候没有指定，并且字段定义的时候没有指定)。

另外变长字符串类型(varchar之类的)会多占用2个字节，可以为null的字段 会多占用1个字符。

需要明确的一点char(1)不是说占用一个字节，而是这个字段可以存下指定字符集的一个字符。

在联合索引中，如果只用到了前面一部分索引，那么只计算用到索引的长度。

#比如下面字联合索引，name，des，des2都只截取前面12字符加入索引，如果查询只用到 name ，字段字符集是utf8mb4，并且字段是允许是null
ALTER TABLE `hl`.`goods` 
DROP INDEX `name`,
ADD INDEX `name`(`name`(12), `des`(12), `des2`(12)) USING BTREE;



#查询结果是12*4(utf8mb4)+2(变长)+1(可以是null)
explain select * from goods where name = 'CMl'

image-20250907185511807

ref

表示索引字段等值匹配的依赖值

  • 使用不上索引的时候是null

  • 使用上索引的时候显示和索引等值匹配的值是什么，一般是const，表示常量，

    explain select * from goods where id = 11

  • 如果依赖的别的表的字段会显示成 xxx.id(比如b表的外键字段依赖a表的id字段的时候)

    select * from goods left join goods2 on goods.id = goods2.g1_id

  • 如果是范围匹配，这时候ref字段也是不显示的

    explain select * from goods where id = 11 or id = 12
rows

预估的返回的记录数,一般等值匹配都是比较精确的，但是结果可能有偏差，比如我使用前坠字段做索引，预估是3，但是实际可能是0

filtered

这是一个百分比，表示扫描到的数据被非索引的where条件过滤以后还剩余多少

  • 正常如果是where后面的条件都都可以命中索引，那么过滤后剩余值一般是100%
  • where后面每增加一个索引字段以外的别的字段的筛选条件，过滤以后剩余量会逐渐变小，一个非索引字段减少90%，最少会显示为表记录条数分之一（如果有部分索引命中这个值会增加，大概是大于1%的一个数）
  • 全表扫描的时候，如果后面没有where条件，过滤剩余量也是100%

注意这个值估计的很不准确，一般是用于辨别是否有不是在命中索引支持的过滤字段，只要不是100，就是有这样的字段，只要低于10就是有2个以上的这样的字段。只要低于1%一般是没有用上索引，走的全表扫描。

Extra

特殊字段，里面会有一些没有列出的字段的一些信息，其实就是other

  • Using index 使用了索引覆盖 select的值都可以从索引中取，需要注意type=index的时候虽然使用了索引覆盖，但是依旧扫描了整条索引。

  • Impossible WHERE 不可能的where条件

    select name,id from goods where 1=2

  • Using where 使用了where条件，并且有where后面的字段存在不是索引字段的赛选，有了这个filtered一般就会小于100

  • Using index condition 使用了索引下推，一部分条件使用了索引，后面的条件可以在当前条件的基础上推断（ 这里前提条件是索引中断或者使用了索引的一段，并且后面筛选条件在当前索引里面得到结果）

    #索引是name，des，des2的联合索引,每个字段长255，4字节一个字符，key_len=2046，说明索引使用了2段，Using index  condition 说明使用了索引下推，但是不确定是在第二个字段下推还是第三个字段下推？如果是在第二个字段下推，fitered就应该是100,只有第三个字段下推才会是90
select *  from goods where  name = "CMI" and des < "e" and des2 != "a"

    image-20250908014935586
    对比在第二字段索引下推的结果，过滤剩余量是100%
    image-20250908015423605

  • Using join buffe 连个表关联，被驱动表的外键字段没有建立索引

    #goods2.g1_id 没有建立索引
select * from goods left join goods2 on goods.id = goods2.g1_id

    没添加索引的时候
    image-20250908020502186

    添加索引以后
    image-20250908020605037

  • Using union 使用了连接
    使用a=1 or b=1 ，并且 a，b 上独立建立索引，这时候会使用两条索引，并且是连接两次查询的结果

  • Using temporary 使用了临时表 ，比如使用 union的时候，或者 groupby ，distiinct 用不上索引的时候

  • Using filesort 使用文件排序，也就是说排序字段没有用上索引

执行计划输出格式

  • 表格模式（默认）

  • json

    explain format=json  select * from goods union select *from goods

    结果会多出cost信息

    {
    "query_block": {
        "select_id": 1,
        "cost_info": {
            "query_cost": "10.25"
        },
        "table": {
            "table_name": "goods",
            "access_type": "ALL",
            "rows_examined_per_scan": 100,
            "rows_produced_per_join": 100,
            "filtered": "100.00",
            "cost_info": {
                "read_cost": "0.25",
                "eval_cost": "10.00",
                "prefix_cost": "10.25",
                "data_read_per_join": "302K"
            },
            "used_columns": [
                "id",
                "name",
                "stock",
                "des",
                "des2",
                "data",
                "create_date"
            ]
        }
    }
}

  • tree

    #explain format=tree  select * from goods


-> Table scan on goods  (cost=10.25 rows=100)

其他的sql分析工具

得到优化以后得sql

#先执行sql，然后马上执行下一句，有些sql会被重写
select * from goods;
SHOW WARNINGS/G

查看执行计划以后，接着使用这行命令可以查看sql优化以后得真实sql

（在工具连接中无效，因为上一条已经变了）

分析优化器执行计划：trace

# 开启
SET optimizer_trace="enabled=on",end_markers_in_json=on; 

# 设置大小
set optimizer_trace_max_mem_size=1000000;
# 使用，（在工具连接中无效，因为上一条已经变了）
select * from goods;
select * from information_schema.optimizer_trace\G

mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: select *from goods
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `goods`.`id` AS `id`,`goods`.`name` AS `name`,`goods`.`stock` AS `stock`,`goods`.`des` AS `des`,`goods`.`des2` AS `des2`,`goods`.`data` AS `data`,`goods`.`create_date` AS `create_date` from `goods`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "`goods`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": "`goods`",
                "table_scan": {
                  "rows": 100,
                  "cost": 0.25
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`goods`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 100,
                      "access_type": "scan",
                      "resulting_rows": 100,
                      "cost": 10.25,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 100,
                "cost_for_plan": 10.25,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`goods`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`goods`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

MySQL监控分析视图

sys库里面的视图有很多监控信息

Sys schema视图使用场景

索引情况

#1. 查询冗余索引 
select * from sys.schema_redundant_indexes; 
#2. 查询未使用过的索引 
select * from sys.schema_unused_indexes; 
#3. 查询索引的使用情况 
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='dbname' ;

表相关

# 1. 查询表的访问量 
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc; 
# 2. 查询占用bufferpool较多的表 
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10; 
# 3. 查看表的全表扫描情况 
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

#1. 监控SQL执行的频率 
select db,exec_count,query from sys.statement_analysis order by exec_count desc; 
#2. 监控使用了排序的SQL 
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1; 
#3. 监控使用了临时表或者磁盘临时表的SQL 
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

IO相关

#1. 查看消耗磁盘IO的文件 
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb 相关

#1. 行锁阻塞情况 
select * from sys.innodb_lock_waits;

索引失效的情况

  • 在字段上使用函数索引会失效
    因为每个字段都要计算，计算后的结果是无序的，不能等值匹配

  • 隐式类型装换索引失效
    mysql的处理类型装换不是把参数转换成字段格式，而是字段转成参数格式，并且处理字符转数字只要前面任意字符可以转就转，其实是在字段使用函数的特殊情况
    image-20250908164811574

    • 时间类型除外，mysql的时间字段用字符串或者数字类型的时间戳都能正常使用索引，感觉这是mysql的一个特殊优化(把参数转成时间格式)
    • mysql的datetime(1000-9999年),timestamp(1970-2038年)没有保存毫秒数，有精确需求的时候需要标注精确到秒后面多少位比如timestamp(3)
    • 存储方面同样精度的datetime比timestamp多一占一个字节

  • 不等于，不等于用在索引字段就会导致索引失效(用在非最左会导致后面的索引部分失效)，在多列的索引时候表现为后面的索引部分中断

    #name字段有索引，stock字段没有索引

#不等与在非索引字段上，不影响别的字段索引查找，索引有效
select *from goods where name = "1"  and stock !=2

#不等于在需要使用的索引上，会让索引失效，索引失效
select *from goods where name != "1"  and stock =2

  • is not null 导致索引失效，和不等于一样
    is null 可以通过筛选吉利投空值标记得到

  • 范围查询会导致，当前这列索引后面的索引失效

    这个和不等于是不一样的，不等于是当前字段就失效，访问查询是当前字段后面的字段才失效，比如abc三字段的索引

    a = 1 and b>1 and c =1,索引可以用上 ab字段用不上c

    a = 1 and b !=1 and c =1,索引可以用上 a字段用不上bc

  • or的条件，如果连接的任意一个条件用不上索引，就会导致索引失效
    and 只要有一个条件能用上索引就能用上，
    or和and嵌套情况分段看

    #name字段有索引，stock字段没有索引
#or 后面的 stock 没有索引，导致索引失效
select *from goods where name = "1"  or stock =2

#or前后 name字段都能用上索引，索引有效
select *from goods where name = "1"  or name = "2"


#用不上索引，因为or只有有一段用不上索引索引就不生效 name != "2" 用不上
select *from goods where name = "1"  or name != "2"

#可以用上索引，and只有有一段能用上索引，索引就生效
select *from goods where name = "1"  and  name != "2"

  • 多列索引不存在最左字段
    建立索引的事按照最左边字段为排序第一优先级建立的，然后才是第二，第三字段

  • 模式查询用%开头的查不到
    这个和多列索引一定要存在最左字段才生效一样

  • 重复数据太多，或者命中的数据比例太高索引会失效

  • in 和 exists的子查询(关联字段有索引) 可以用上索引，并且not in 和 not exists 子查询可以用上索引，in 有些时候会被改成exists

我们可以想象索引的叶子节点上面的记录其实构成一个单向有序链表，然后一定数量的记录的构成一个页，这些页里面保存着这些记录的最大值最小值，并且页之间通过双向链表连接，然后如果页很多又会在页的上面生产目录页，目录页的一条记录对应下面的一页，从而形成树型结构。但是单看叶子阶段是所有记录都是通过单向链表连接，如果把索引理解为按照索引字段(多字段最左有限，默认升序)排序连接在一起的有序链表，能在索引链表上找到明确的一块连续区域，索引就会有效

多表查询优化

如下图的多表关联，goods是驱动表，goods2是被驱动表，连接字段在goods2.g1_id,执行计划第一条是全表扫描是因为where后面没有goods的筛选条件，带上以后就筛选条件，并且命中索引就行，这里这是举例子，执行计划的第二条goods2的查询type=ref是因为连接字段在goods2.g1_id上有索引，如果没有那么type会是all。

image-20250908211106364

  • 明确驱动表和被驱动表（执行计划中，驱动表在前，被驱动表在后面，和sql 写的顺序关系不大）

    • 核心思想，选择小的表作为驱动表

    • 看连接字段是够有索引，有索引倾向选择这个表作为被驱动表，两边都有或者两边都没有看where条件

    • 看where，有where 条件的倾向为驱动表，那么大概率会被认为是小表(有过滤条件，或者过滤效率高的表)

    • 可以通过 STRAIGHT_JOIN 强制指定驱动表,但是一般不要这样用

      select * from goods2 straight_join goods on goods.id = goods2.g1_id

  • 被驱动表的连接字段需要有索引，否者被驱动表会全表扫描

    如果是多对的查询比如 a ，ab ，b的三表

    • 如果是a作为驱动表，ab，和b作为被驱动表，只需要再ab表的中的a.id上创建索引
    • 如果是b作为驱动表，ab，和a作为被驱动表，只需要再ab表的中的b.id上创建索引

  • 连接字段类型要一样，不能有类型转换
    int和bigint之间

    char和varchar之间是可以用索引的

join的本质是循环查询，这个二次查询可能是等值匹配，也可能是in查询

  • 如果没有索引，那么只能等值匹配循环，这时候没有顺序，单个匹配的结果中就能得到部分最终结果，但是这样查询效率很低

    • 在8.0以前对它的优化是加载驱动表到缓存中，然后去查询被驱动表，然后组装查询结果
    • 8.0以后优化了组装结果的过程，用驱动表的连接字段做了一个hash表，然后去和被驱动表得到的结果匹配。

  • 如果有索引，那么可以得到驱动表关联字段的值集合，然后通过in去被驱动表查询，这时候对in 后面的值排序在索引里面最小的开始找，找到以后找下一个，直到找到所有的数据，需要对两个结果集在做一次连接，

  • join过程中一次能加载多少驱动表的结果然后去被驱动表查询，和最后组装结果都需要join_buffer的参与，这部分缓存越大越有利与join的进行

    #默认是256K
show VARIABLES like "%join_buffer_size%"

  • 减少驱动表不必要的字段查询，可以减少join_buffer的使用

为啥要小表驱动大表？循环等值匹配，小表在前可以减少循环次数(主要是这个原因)，索引in查询可以减少in的值的数(虽然都是查询一次索引，但是在检索过程比对次数变少)

排序分组去重优化

排序分组去重要尽量使用索引，并且和where一起用联合索引，用不上索引的排序会产生文件排序，用不上索引的分组会产生临时表

排序算法

  • 双路排序
    两次IO，第一次IO读写排序字段，排序好了再通过ID去读取其他字段

  • 单路排序
    一次就读取全部数据，然后放在内存中在排序，这样快，但是比较消耗 sort_buffer的内存(参数innodb_sort_buffer_size设置内存大小),innodb默认1M
    image-20250909004333571

  • 选择单路和双路是根据排序数据大小来决定的，高于 max_length_for_sort_data 的排序数据会选择双算法，默认是4K，所以减伤无效的select字段的数量可以增加排序效率(更大概率走单路排序)
    image-20250909004542154

mysql深分页问题

#数据很靠后，这时候用不上索引
select * from goods limit 1000000,10 


#考虑使用索引覆盖，和二次查询
select id from goods limit 1000000,10 
select * from goods where id in( xxx,xxx )


#翻案2，如果点击的下一页(一页一页向下翻的情况)，在前一次查询的基础上用ID筛选下一页(适用需要遍历完一个特别深的数据)
select id from goods id>xxx limit 10

索引条件下推

用不索引的筛选条件，存在鱼索引中，可以直接在索引中完成筛选，而不是回表以后再筛选,这样可以减少回表的次数，这就是索引条件下推，这里前提条件是使用到了部分条件使用到了索引还有一部分条件筛选可以通过索引完成过滤，使用了索引下推时执行计划中的额外字段会显示Using index condition

select *  from goods where  name = "CMI" and des < "e" and des2 != "a"

索引下推默认开启

#开关方式
set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";

因为是减少回表次数，所以索引下推只对二级索引有效，对主键索引和索引覆盖无效

in和exists的选择

  • 不相关子查询的时候用优先选择in，一般是先得出里面的结果，保持到临时表，然后查询外面
  • 相关子查询优选exists，是先执行外面查询，然后和里面的关联
  • 但是上面的情况不绝对，可以通过执行计划观察执行情况，有些in的不相关子查询(select 后面的字段可以用上索引的时候)会被改写成exists

子查询优化

  • 子查询数据多的时候会建立临时表，这种可以分多次查询，减少使用子查询

    不相关子查询，子查询关联字段没有索引的时候，是先物化子查询的结果，然后才是做的驱动表的查询和子查询筛序

    不相关子查询，如果子查询的select 后面的字段有索引，那么可以作为连接字段，子查询会被最为被驱动表
    image-20250908231204016

  • 相关子查询的情况可以考虑使用join代替,避免使用相关子查询(只有连接字段唯一才能改，不然数据会碰撞)
    相关子查询，子查询的内部使用外部的字段

索引可以用在哪些地方

下面的地方都会可以用上索引，所以出现性能问题的时候都要考虑用上

  • 增删改查的where语句的后面
  • order by 的时候
  • group by 的时候
  • join 被驱动表的连接字段
  • distinct 去重的时候
  • 相关子查询的被驱动表的连接字段

count问题

  • count(*)等价于count(主键)，count(1) ，这时候会优先选择length最短的二级索引统计
  • count(具体字段) ，会选用指定的字段统计，可能不是最优的，如果选中长索引或者非 索引效率会降低

MySQL的一些参数

  • innodb_buffer_pool_size：这个参数是Mysql数据库最重要的参数之一，表示InnoDB类型的表和索引的最大缓存。它不仅仅缓存索引数据，还会缓存表的数据。这个值越大，查询的速度就会越快。但是这个值太大会影响操作系统的性能。
  • key_buffer_size：表示索引缓冲区的大小。索引缓冲区是所有的线程共享。增加索引缓冲区可以得到更好处理的索引（对所有读和多重写）。当然，这个值不是越大越好，它的大小取决于内存的大小。如果这个值太大，就会导致操作系统频繁换页，也会降低系统性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。
  • table_cache：表示同时打开的表的个数。这个值越大，能够同时打开的表的个数越多。物理内存越大，设置就越大。默认为2402，调到512-1024最佳。这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。
  • query_cache_size：表示查询缓冲区的大小。可以通过在MySQL控制台观察，如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，就要增加Query_cache_size的值；如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓存；Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。MySQL8.0之后失效。该参数需要和query_cache_type配合使用。
  • query_cache_type的值是0时，所有的查询都不使用查询缓存区。但是query_cache_type=0并不会导致MySQL释放query_cache_size所配置的缓存区内存。
    • 当query_cache_type=1时，所有的查询都将使用查询缓存区，除非在查询语句中指定SQL_NO_CACHE，如SELECT SQL_NO_CACHE * FROM tbl_name。
    • 当query_cache_type=2时，只有在查询语句中使用SQL_CACHE关键字，查询才会使用查询缓存区。使用查询缓存区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的查询操作的情况。
  • sort_buffer_size：表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或GROUP BY操作的速度。默认数值是2 097 144字节（约2MB）。对于内存在4GB左右的服务器推荐设置为6-8M，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。
  • join_buffer_size = 8M：表示联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
  • read_buffer_size：表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节）。当线程从表中连续读取记录时需要用到这个缓冲区。SET SESSION read_buffer_size=n可以临时设置该参数的值。默认为64K，可以设置为4M。
  • innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别为0、1和2。该参数的默认值为1。
    • 值为0时，表示每秒1次的频率将数据写入日志文件并将日志文件写入磁盘。每个事务的commit并不会触发前面的任何操作。该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
    • 值为1时，表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘进行同步。该模式是最安全的，但也是最慢的一种方式。因为每次事务提交或事务外的指令都需要把日志写入（flush）硬盘。
    • 值为2时，表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘。该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
  • innodb_log_buffer_size：这是 InnoDB 存储引擎的事务日志所使用的缓冲区。为了提高性能，也是先将信息写入 Innodb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。
  • max_connections：表示 允许连接到MySQL数据库的最大数量 ，默认值是 151 。如果状态变量connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。这个连接数 不是越大 越好 ，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。
  • back_log：用于控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ， 之后的版本默认为 50 + （max_connections / 5）， 对于Linux系统推荐设置为小于512的整数，但最大不超过900。如果需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大back_log 的值。
  • thread_cache_size：线程池缓存线程数量的大小，当客户端断开连接后将当前线程缓存起来，当在接到新的连接请求时快速响应无需创建新的线程 。这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。那么为了提高性能可以增大该参数的值。默认为60，可以设置为120。
  • wait_timeout：指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
  • interactive_timeout：表示服务器在关闭连接前等待行动的秒数。

posted on 2025-09-09 15:11  zhangyukun  阅读(4)  评论(0)    收藏  举报

导航