• SQL语句优化 (一) (52)


    优化SQL语句的一般步骤

      1 通过show status命令了解各种SQL的执行频率。

      格式:mysql> show [session|global]status;

       其中:session(默认)表示当前连接,

         global表示自数据库启动至今

    mysql>show status;

    mysql>show global status;

    mysql>show status like ‘Com_%’;

    mysql>show global status like ‘Com_%’;

    参数说明:

    Com_XXX表示每个XXX语句执行的次数如:

    Com_select 执行select操作的次数,一次查询只累计加1

    Com_update 执行update操作的次数

    Com_insert 执行insert操作的次数,对批量插入只算一次。

    Com_delete 执行delete操作的次数

    只针对于InnoDB存储引擎的。

    InnoDB_rows_read 执行select操作的次数

    InnoDB_rows_updated 执行update操作的次数

    InnoDB_rows_inserted 执行insert操作的次数

    InnoDB_rows_deleted 执行delete操作的次数

    其他:

    connections 连接mysql的数量

    Uptime 服务器已经工作的秒数

    Slow_queries:慢查询的次数

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

    1)explain select * from table  where id=1000;

    2)desc select * from table where id=1000;

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

    mysql> explain select count(*) from stu where name like "a%"G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: stu
             type: range
    possible_keys: name,ind_stu_name
              key: name
          key_len: 50
              ref: NULL
             rows: 8
            Extra: Using where; Using index
    1 row in set (0.00 sec)

    每一列的简单解释

    id: 1

    select_type: SIMPLE 表示select的类型,常见的取值有SIMPLE()简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SESECT)等

    table: stu   输出结果集的表

    type: range  表示表的连接类型,性能有好到差:system(表仅一行)、const(只一行匹配)、eq_ref(对于前面的每一行使用主键和唯一)、ref(同eq_ref,但没有使用主键和唯一)、ref_or_null(同前面对null查询)、index_merge(索引合并优化)、unique_subquery(主键子查询)、index_subquery(非主键子查询)、range(表单中的范围查询)、index(都通过查询索引来得到数据)、all(通过全表扫描得到的数据)

    possible_keys: name,ind_stu_name  表查询时可能使用的索引。

    key: name   表示实际使用的索引。

    key_len: 50  索引字段的长度

    ref: NULL 

    rows: 8   扫描行的数量

    Extra: Using where; Using index 执行情况的说明和描述

    索引问题

    索引是数据库优化中最常见也是最重要的手段之一,通过索引通常可以帮助用户解决大多数的SQL性能问题。

    索引的存储分类

      MyISAM存储引擎的表的数据和索引是自动分开存储的,各自是独一的一个文件;InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面,但可以有多个文件组成。

      MySQL目前不支持函数索引,但是能对列的前面某一部分进行索引,例如name字段,可以只取name的前4个字符进行索引,这个特性可以大大缩小索引文件的大小,用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。

      mysql>create index ind_company2_name on company2(name(4));
      其中company表名 ind_company2_name索引名

    MySQL如何使用索引

    索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高SELECT操作性能的最佳途径。

    1、使用索引

      (1)对于创建的多列索引,只要查询的条件中用到最左边的列,索引一般就会被使用。如下创建一个复合索引。

    mysql>create index ind_sales2_com_mon onsales2(company_id,moneys);

    然后按company_id进行查询,发现使用到了复合索引

    mysql>explain select * from sales2 where company_id=2006G

    使用下面的查询就没有使用到复合索引。

    mysql>explain select * from sales2 where moneys=1G

     (2) 使用like的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用,如下:

    mysql> explain select * from company2 where name like "%3"G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: company2
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 1000
            Extra: Using where
    1 row in set (0.00 sec)

    如下这个使用到了索引,而下面例子能够使用索引,区别就在于“%”的位置不同,上面的例子是吧“%”放在了第一位,而下面的例子则没有

    mysql> explain select * from company2 where name like “3%"G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: company2
             type: range
    possible_keys: ind_company2_name
              key: ind_company2_name
          key_len: 11
              ref: NULL
             rows: 103
            Extra: Using where
    1 row in set (0.00 sec)

    (3)如果对大的文本进行搜索,使用全文索引而不使用 like“%...%”.

    (4)如果列名是索引,使用column_name is null将使用索引。如下

    mysql> explain select * from company2 where name is nullG
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: company2
             type: ref
    possible_keys: ind_company2_name
              key: ind_company2_name
          key_len: 11
              ref: const
             rows: 1
            Extra: Using where
    1 row in set (0.00 sec)

    2、存在索引但不使用索引

     

    (1)如果MySQL估计使用索引比全表扫描更慢,则不使用索引。例如如果列key_part1均匀分布在1到100之间,查询时使用索引就不是很好

    mysql>select * from table_name where key_part1>1 and key_part<90;

      (2)如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列,那么不会用到索引。Heap表只有在“=”的条件下会使用索引。

      (3)用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。

    mysql>show index from salesG

     *************************** 1. row ***************************       
        … …
       key_name: ind_sales_year
       seq_in_index:1
       Column_name: year
        … …

     

  • 相关阅读:
    H5版俄罗斯方块(5)---需求演进和产品迭代
    vim 常用 NERDTree 快捷键
    C和C++中include 搜索路径的一般形式以及gcc搜索头文件的路径
    MySQL复制协议
    深入解析MySQL replication协议
    Install CodeBlocks in CentOS 7
    Impala 源码分析-FE
    Elasticsearch 6.x 的分页查询数据
    1、树莓派3B开箱+安装系统
    Python创建ES索引
  • 原文地址:https://www.cnblogs.com/zhenghongxin/p/4842395.html
Copyright © 2020-2023  润新知