• MySQL优化

     
    目录

    MySQL优化步骤

    首先学会如何定位到SQL语句

    1.1查看SQL语句的执行次数

    在MySQL中可以通过命令查看服务器该表状态信息

    show status like 'Com_______';

    image-20201207215917015

    如果想查看整个数据库信息

    show global status like 'Com_______';

    下面这些对于所有存储引擎的表操作都会进行累计

    • Com_select：执行 select 操作的次数，一次查询只累加 1。
    • Com_insert：执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
    • Com_update：执行 UPDATE 操作的次数。
    • Com_delete：执行 DELETE 操作的次数。

    有专门针对Innodb统计的，其中 rows_read代表的是读取的行数。

    show status like 'Innodb_rows_%';

    image-20201207221031143

    对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况， 对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

    1.2 定位执行效率较低的SQL语句

    • 通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选 项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志 文件。具体可以查看本书第 26 章中日志管理的相关部分。
    • 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查 询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程， 包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操 作进行优化。

    通过下面命令可以查看MySQL进程

    image-20201207222623991

    • Id：数据库连接id
    • User：显示当前用户
    • Host：从哪个ip的哪个端口上发的
    • db:数据库
    • Command：连接的状态，休眠（sleep），查询（query），连接（connect）
    • Time：秒
    • State：SQL语句执行状态，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
    • Info：SQL语句

    1.3 通过 EXPLAIN 分析低效SQL的执行计划

    找到相应的SQL语句之后，可以EXPLALIN获取MySQL的执行信息。

    image-20201209212623162

    其中每个列的解释：

    id：id相同表示加载表的执行顺序从上到下，id越大加载的优先级越高

    select_type:表示 SELECT 的类型，常见的取值有

    • SIMPLE（简单表，即不使用表连接 或者子查询）
    • PRIMARY（主查询，即外层的查询）
    • UNION（UNION 中的第二个或 者后面的查询语句）
    • SUBQUERY（子查询中的第一个 SELECT）

    table:输出结果集的表

    type：表示表的连接类型，性能好到坏的结果

    • system(表中仅有一行，即常量表)
    • const（单表中最多有一个匹配行，只能查询出来一条）
    • eq_ref（对于前面的每一行，在此表中只有一条查询数据，类似于主键和唯一索引）
    • ref（与eq_ref类式，区别是不使用主键和唯一索引）
    • ref_ir_null（与ref类似，区别在于对NULL的查询）
    • index_merge（索引合并优化）
    • unique_subquery（in 的后面是一个查询主键字段的子查询）
    • index_subquery（与 unique_subquery 类似， 区别在于 in 的后面是查询非唯一索引字段的子查询）
    • range（单表中的范围查询）、
    • index（对于前面的每一行，都通过查询索引来得到数据）
    • all（对于前面的每一行， 207 都通过全表扫描来得到数据）

    possible_keys：表示查询时，可能使用的索引。

    key：表示实际使用的索引

    key_len：索引字段的长度

    rows：扫描行的数量

    Extra：执行情况的说明和描述

    根据以上内容创建 Teacher、 Student表，通过ClassID关联

    create table Teacher
(
   teacherId int not NULL AUTO_INCREMENT,
	 teacherName VARCHAR(50),
	 ClassID int,
	 primary key (teacherId)
) ENGINE =innodb DEFAULT charset=utf8;

create table Student
(
   StudentID int not NULL AUTO_INCREMENT,
	 ClassId int,
	 StudentName varchar(50),
	 primary key (StudentID)
) ENGINE = INNODB DEFAULT charset=utf8;

INSERT into Teacher(teacherName,ClassID) values("小李",204),("小刘",205),("小杨",206);

INSERT into Student(ClassId,StudentName) VALUES(204,"张三"),(205,"李四"),(206,"王五");

    explain-id

    （1）、Id相同表示执行顺序从上到下

    EXPLAIN select * from Teacher t,Student s where t.ClassID=s.ClassID;

    image-20201209223229190

    （2）、Id不同表示，Id越大越先执行

    explain  select *from Teacher where ClassId =( select ClassId from Student where StudentName='张三');

    image-20201209223750003

    （3）、Id有相同的也有不同的，先执行Id大的，再从上到下执行。

    explain select_type

    （1）、SIMLPLE简单的select查询，不包含子查询或者UNION

    explain select * from Teacher;

    image-20201210220352988

    （2）、PRIMARY查询当中包含了子查询，最外层就是改查询的标记

    （3）、SUBQUERY在select或者Where中包含了子查询

    explain select *from Teacher where ClassId=(select ClassId from Student where StudentId=1);

    image-20201210220847393

    （4）、DERIVED在form列表包含子查询

    explain  select * from (select * from Student where Student.StudentID>2  )   a where a.ClassID=204;

    image-20201215214322476

    如果查询显示都是SIMLPLE是因为mysql5.7对 derived_merge 参数默认设置为on，也就是开启状态，我们在mysql5.7中把它关闭 shut downn 使用如下命令就可以了

    set session optimizer_switch=`derived_merge=off`;
set global optimizer_switch=`derived_merge=off`;

    （5）、UNION 、UNION RESULT

    explain select * from Student where StudentID=1  union select * from Student where StudentID=2;

    image-20201215223848880

    UNION指的是后面那个Select，UNION RESULT 将前面的select语句和后面的select联合起来。

    explain-type

    （1）、NULL直接返回结果，不访问任何表索引

    select NOW();

    image-20201215224329518

    （2）、system查询结果只有一条的数据,const类型的特例

    explain select * from (select * from Student where StudentID=1) a;

    image-20201215224802706

    （3）、const根据主键或者唯一索引进行查询，表示一次就找到了

    EXPLAIN select * from Student where StudentID=1;

    image-20201215225620366

    （4）、eq_ref 索引是主键或者唯一索引，使用多表关联查询查询出来的数据只有一条

    explain select * from Student s,Teacher t where  s.StudentID=t.teacherId

    image-20201216222926210

    （5）、ref 根据非唯一性的索引查询，返回的记录有多条，比如给某个字段添加索引

    explain select * from Student s WHERE  StudentName='张三1';

    image-20201216224858127

    （6）、range 范围查询 between <> in等操作，前提是用索引，要自己设定索引字段；

    explain select * from Student where StudentID in (2,3);

    image-20201216225335048

    （7）、index 遍历整个索引树,相当于查询了整张表的索引

    explain select  StudentID from Student;

    image-20201216225755186

    （8）、ALL 遍历所有数据文件

    explain select  * from Student;

    image-20201216225633881

    通过这个Type就可以判断当前查询返回了多少行，有没有走索引还是走全表扫描

    结果从最好到最坏

    NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL


system > const > eq_ref > ref > range > index > ALL

    explain-key

    image-20201217221447301

    （1）possible_keys：可能用到的索引

    （2）key：实际用到的索引

    （3）key_len：key的长度，越短越好

    explain-rows

    sql语句执行扫描的行数

    explain-extra

    （1）using filesort ：会对进行文件排序即内容，而不是按索引排序，效率慢

    EXPLAIN select *from Student order by StudentName;

    image-20201217222406780

    如果要优化的话可以对该字段建索引

    （2）using index 根据根据索引直接查，避免访问表的数据行

    explain select StudentID from Student order by StudentID ;

    image-20201217223530114

    （3）using temporary 使用临时表保存结果，在没有索引的情况下，需要进行优化

    EXPLAIN select * from Teacher t GROUP BY teacherName;

    image-20201217230812749

    报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'demo_01.Teacher.teacherName' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

    解决办法：
    1、找到mysql的配置文件 my.ini (一般在mysql根目录)

    2、在my.cn中将以下内容添加到 [mysqld]下

    我的是：etc/my.cnf

    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

    1.4 show profile分析SQL

    show profile可以分析sql运行的时间，通过 have_profiling可以查看MySQL是否支持profile

    image-20201218203651567

    默认profiling是关闭的，可以通过语句打开

    set profiling=1;//打开

    image-20201218203922282

    执行SQL语句之后乐意通过show profiles指令，来查看语句的耗时

     show profiles;

    image-20201218204516161

    可以通过Show profile for query Query_id查看每个阶段的耗时

    Show  profile for query 2;

    image-20201218205555339

    其中Sending data表示来说访问数据库并把结果返回给数据库的过程，MySQL需要做大量的磁盘读取操作，因此是最耗时的。

    在知道最消耗时间的状态后，可以选择all、cpu、block to、context switch、page fault等明细查看在什么资源上浪费了时间

    show profile cpu for query 2;

    image-20201218210149487

    1.5 trace分析优化器执行计划

    Mysql有一个优化器按照规则对SQL进行优化处理，trace就是用来分析优化器的执行计划

    首先开启trace开关，然后设置trace文件占用的内存空间

    set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

    执行SQL语句之后检查系统表就可以知道如何执行的SQL

    select * from information_schema.optimizer_traceG;

    *************************** 1. row ***************************
                            QUERY: select * from Student where StudentId<1
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `Student`.`StudentID` AS `StudentID`,`Student`.`ClassId` AS `ClassId`,`Student`.`StudentName` AS `StudentName` from `Student` where (`Student`.`StudentID` < 1)" //把*查询的都解析出来了
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`Student`.`StudentID` < 1)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`Student`.`StudentID` < 1)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`Student`.`StudentID` < 1)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`Student`.`StudentID` < 1)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`Student`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`Student`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 4,
                    "cost": 3.9
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "StudentID"
                      ] /* key_parts */
                    },
                    {
                      "index": "index_id_Student",
                      "usable": true,
                      "key_parts": [
                        "StudentID"
                      ] /* key_parts */
                    },
                    {
                      "index": "index_Name_Student",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "StudentID < 1"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 1,
                        "cost": 1.21,
                        "chosen": true
                      },
                      {
                        "index": "index_id_Student",
                        "ranges": [
                          "StudentID < 1"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 1,
                        "cost": 2.21,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 1,
                      "ranges": [
                        "StudentID < 1"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 1,
                    "cost_for_plan": 1.21,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`Student`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "PRIMARY"
                      } /* range_details */,
                      "resulting_rows": 1,
                      "cost": 1.41,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 1.41,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`Student`.`StudentID` < 1)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`Student`",
                  "attached": "(`Student`.`StudentID` < 1)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`Student`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

    1.6 索引使用

    （1）索引对查询效率的提升

    根据有索引的ID和名字查询结果，数据量不是很大只有两万可能不是很明显，有索引的快一些

    image-20201218221940368

    如果查询的条件值没有索引，可以通过创建索引来达到快速查询的目的

    （2）全值匹配，先创建联合索引，所有列都指定具体值

    create index idx_Stuname_id on Student(ClassId,StudentName);
explain select * from Student where StudentName='货物9000号' and ClassId=9000;

    image-20201218224023944

    （3）最左前缀法则，从最左边一个 索引开始匹配，顺序位置不受where影响，法则是查询的结果包含索引的最左列，且后面没有跳过其他列。

    explain select * from Student where StudentName='货物9000号' and ClassId=9000;

    image-20201219144252170

    如果将where后面最左列匹配的索引ClassId增加一个其他字段就无法用到idx_Stuname_id索引

    explain select * from Student where   ClassId=9000  and  StudentID=20771   AND StudentName=20771;

    image-20201219145054957

    走索引就相当于爬楼梯，从一层一层开始爬，一层爬完爬二层，不能直接从二层开始爬，也不能爬了二层开始爬第三层

    （3）在范围查询的字段后面索引失效

    explain select *from Student where 索引1= and 字段>2 and 索引2=

    因此索引2将会失效，用不到该索引

    （4）如果对某一个列进行了计算操作，索引失效

    explain select * from Student where ClassId  BETWEEN 20771 and 20111

    image-20201219151532920

    （5）、如果字符串不加单引号，索引会失效。

    （6）、使用覆盖索引（只访问索引的查询），避免使用select *

    在查询的时候将*号改成需要查询的字段或者索引，减少不必要的开销，使用索引查询，using index condition 会将需要的字段查询出来

    using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

    （7）、如果有 or后面的字段没有索引，则整个索引失效

    explain select * from Teacher where  teacherId=2;

    原本主键索引

    image-20201219153246707

    加上or之后，索引失效

    explain select * from Teacher where   ClassId=204  or teacherId=2;

    image-20201219153357659

    （8）、以like '%XX'开头不走索引

    正常走索引

    explain select * from Student where StudentName LIKE '货物9000号%';

    image-20201219154341290

    在like前加上%号

    explain select * from Student where StudentName LIKE '%货物9000号%' ;

    image-20201219154554779

    不走索引解决办法：使用覆盖索引，将*号改成有索引的列，再通过索引查询

    explain select StudentID from Student where StudentName LIKE '%货物9000号%'

    image-20201219154757088

    （8）如果再一张表中，一个字段数据基本全是1，只有为2。这时候给该字段建立索引，查询1的时候mysql认为走全表速度更快就不会走索引，如果查询2就会走索引。

    （9）IS NUL、IS NOT NULL有时走索引

    如果一个字段中所有数据都不为空，那么查询该字段时会走索引，是少量的就会走索引，大多数不会走索引。

    EXPLAIN  select * from Student  where StudentName is NULL;
EXPLAIN  select * from Student  where StudentName is NOT NULL;

    image-20201219160409169

    （10）in走索引、not in 不走索引，但也不是绝对的，按照第八条

    （11）单列索引和复合索引

    create index idx_Stuname_id on Student(ClassId,StudentName);
就相当于创建了三个索引 ： 
	ClassId
	StudentName
	ClassId + StudentName

    如果创建单个索引，数据库不会全部使用，而是选择一个最优的。一般选择辨识度最高的。

    （12）查看所有使用情况

    show status like 'Handler_read%';	
show global status like 'Handler_read%';//全局

    image-20201219162100069

    Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。

Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。

Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。

Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。

Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。

Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

    1.7 SQL优化

    优化批量插入

    （1）大批量插入数据时，需要将主键按顺序插入会快很多

    （2）如果插入过程中有唯一索引，可以先关闭索引检查，防止每插入一条时对索引进行筛查

    set unique_checks=1;//1为打开 0为关闭

    （3）手动提交事务，关闭自动提交事务

    set autocommit=1;//1为打开 0为关闭

    优化insert语句

    （1）将多条insert语句改为一条

    （2）手动开启事务，全部插入之后，再提交

    （3）尽量按主键顺序插入

    优化Order by语句

    （1）如果按照多字段排序，要么统一升序要么统一降序

    （2）order 不用后面的字段需要和索引的顺序保持一致

    （3）如果Extra列还出现Using filesort，表示进行了额外的一次排序，考虑使用联合索引

    优化Group by语句

    （1）使用Group by如果Extra列出现Using filesort，表示Group by语句默认进行了排序，可以使用Order by null取消排序

    （2）使用Group by如果Extra列出现Using Temporary，可以给字段建立索引提高效率

    优化嵌套查询

    （1）把多表连接查询替换为子查询

    优化OR查询

    （1）如果需要用到索引，则每个列需要单独创建索引，不能用复合索引

    （2）使用Union替换Or

    优化分页查询

    （1）根据主键进行排序分页操作，得到主键再回原表进行查询

    （2）主键自增时，可以直接根据ID查询，数据没删除的情况下

    SQL提示

    （1）USE index，在有多个索引的情况下，希望Mysql使用该索引，但不是一定会用。

    explain select * from sales2 use index (ind_sales2_id) where id = 3

    （2）ignore index可以忽略使用该索引，使用其他索引

    （3）在数据量很多的情况下，查询数据占很大比重，即使使用了索引，数据库也不会用，这时候使用force index强制指定索引。
  • 相关阅读:
    mysql 性能监控
    拼接字符
    mysql 存储 2
    mysql 存储过程
    flock
    readfile() file_get_content f
    url_encode和base64
    jsdetox反混淆js内容，解密前端加密参数
    前端加密之使用firefox来解密
    v to ray做渗透测试
  • 原文地址：https://www.cnblogs.com/wjxzs/p/14244477.html
Copyright © 2020-2023  润新知