人若无名,便可专心练剑

mysql优化学习小计(一)

  |   0 评论   |   3,276 浏览

1,优化sql一般步奏

     1.1 通过show status 和应用特点了解sql执行频率
        通过 SHOW STATUS 可以提供服务器状态信息,也可以使用 mysqladmin extended-status 命令获得。SHOW 
        STATUS 可以根据需要显示 session 级别的统计结果和 global级别的统计结果。
        以下几个参数对 Myisam 和 Innodb 存储引擎都计数:
            1. Com_select 执行 select 操作的次数,一次查询只累加 1;
            2. Com_insert 执行 insert 操作的次数,对于批量插入的 insert 操作,只累加一次;
            3. Com_update 执行 update 操作的次数;
            4. Com_delete 执行 delete 操作的次数;
        以下几个参数是针对 Innodb 存储引擎计数的,累加的算法也略有不同:
            1. Innodb_rows_read select 查询返回的行数;
            2. Innodb_rows_inserted 执行 Insert 操作插入的行数;
            3. Innodb_rows_updated 执行 update 操作更新的行数;
            4. Innodb_rows_deleted 执行 delete 操作删除的行数;
        通过以上几个参数,可以很容易的了解当前数据库的应用是以插入更新为主还是
        以查询操作为主,以及各种类型的 SQL 大致的执行比例是多少。对于更新操作的计数 ,
        是对执行次数的计数,不论提交还是回滚都会累加。
        对于事务型的应用,通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚
        的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题。
        此外,以下几个参数便于我们了解数据库的基本情况:
            1. Connections 试图连接 Mysql 服务器的次数
            2. Uptime 服务器工作时间
            3. Slow_queries 慢查询的次数
     1.2 定位执行效率较低的 SQL 语句:
            可以通过以下两种方式定位执行效率较低的 SQL 语句:
        1. 可以通过慢查询日志定位那些执行效率较低的 sql 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 写一个包含所有执行时间超过
              long_query_time 秒的 SQL 语句的日志文件。可以链接到管理维护中的相关章节。
        2. 慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询
            慢查询日志并不能定位问题,可以使用 show processlist 命令查看当前 MySQL 在
            进行的线程,包括线程的状态,是否锁表等等,可以实时的查看 SQL 执行情况, 同
                时对一些锁表操作进行优化。
     1.3 通过 EXPLAIN 分析低效 SQL 的执行计划
            通过以上步骤查询到效率低的 SQL 后,我们可以通过 explain 或者 desc 获取
            MySQL 如何执行 SELECT 语句的信息,包括 select 语句执行过程表如何连接和连接的
            次序。
            explain 可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的 SELECT。
mysql> explain select sum(moneys) from sales a,companys b where a.company_id =
b.id and a.year = 2006;
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| select_type | table | type | possible_keys| key | key_len | rows
| Extra |
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| SIMPLE | b | index | PRIMARY | PRIMARY | 4 | 1 | Using index
|
| SIMPLE | a | ALL | NULL | NULL | NULL | 12 | Using where
|
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
2 rows in set (0.02 sec)

 select_type: select 类型
 table: 输出结果集的表
type: 表示表的连接类型
当表中仅有一行是type的值为system是最佳的连接类型;
当select操作中使用索引进行表连接时type的值为ref;
当select的表连接没有使用索引时,经常会看到type的值为ALL,表示对该表
进行了全表扫描,这时需要考虑通过创建索引来提高表连接的效率。
possible_keys: 表示查询时,可以使用的索引列.
key: 表示使用的索引
key_len: 索引长度
rows: 扫描范围
    Extra: 执行情况的说明和描述
   1.4 确定问题,并采取相应的优化措施:
    经过以上步骤,基本可以确认问题出现的原因,可以根据情况采取相应的措施,进行优化提高执行的效率。
        例如上面的例子,我们确认是对 a 表的全表扫描导致效率的不理想,我们对 a
        表的 year 字段创建了索引,查询需要扫描的行数明显较少。
mysql> explain select sum(moneys) from sales a,companys b where a.company_id =
b.id and a.year = 2006;
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| select_type | table | type | possible_keys| key | key_len | rows
| Extra |
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| SIMPLE | b | index | PRIMARY | PRIMARY | 4 | 1 | Using index
|
| SIMPLE | a | ref | year | year | 4 | 3 | Using
where |
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
    2 rows in set (0.02 sec)

2.索引问题

      2.1 索引的存储分类
        myisam 表的数据文件和索引文件是自动分开的;innodb 的数据和索引是存储在
        同一个表空间里面,但可以有多个文件组成。
        创建索引语法如下:
            CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
            [USING index_type]
            ON tbl_name (index_col_name,...)
            index_col_name:
            col_name [(length)] [ASC | DESC]
        索引的存储类型目前只有两种(btree 和 hash),具体和表的模式相关:
            myisam btree
            innodb btree
            memory/heap hash,btree
        mysql 目前不支持函数索引,只能对列的前一部分(length)进行索引,例:
        create index ind_test on table1(name(5)),
            对于 char 和 varchar 列,使用前缀索引将大大节省空间。
      2.2 MySQL 如何使用索引
            索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高
            SELECT 操作性能的最佳途径。
            查询要使用索引最主要的条件是查询条件中需要使用索引关键字,如果是多列索
            引,那么只有查询条件使用了多列关键字最左边的前缀时,才可以使用索引,否则将
            不能使用索引。
            下列情况下,Mysql 不会使用已有的索引:
            1. 如果 mysql 估计使用索引比全表扫描更慢,则不使用索引。例如:如果 key_part1
                  均匀分布在 1 和 100 之间,下列查询中使用索引就不是很好:
                  SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
            2. 如果使用 heap 表并且 where 条件中不用=索引列,其他> 、<、 >=、 <=均不使用
          索引;
            3. 如果不是索引列的第一部分;
            4. 如果 like 是以%开始;
            5. 对 where 后边条件为字符串的一定要加引号,字符串如果为数字 mysql 会自动转为
              字符串,但是不使用索引
     2.3 查看索引使用情况
        如果索引正在工作,Handler_read_key 的值将很高,这个值代表了一个行被索
        引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用 。
        Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补
         救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描,
        该值较高。通常说明表索引不正确或写入的查询没有利用索引。
    语法:  
mysql> show status like 'Handler_read%';

3,常用优化

    3.1 大批量插入数据:
         1. 对于 Myisam 类型的表,可以通过以下方式快速的导入大量的数据。
            ALTER TABLE tblname DISABLE KEYS;
            loading the data
           ALTER TABLE tblname ENABLE KEYS;
            这两个命令用来打开或者关闭 Myisam 表非唯一索引的更新。在导入大量的数据到一
            个非空的 Myisam 表时,通过设置这两个命令,可以提高导入的效率。对于导入大量
            数据到一个空的 Myisam 表,默认就是先导入数据然后才创建索引的,所以不用进行
            设置。     
        2. 而对于 Innodb 类型的表,这种方式并不能提高导入数据的效率。对于 Innodb 类型
              的表,我们有以下几种方式可以提高导入的效率:
            a. 因为 Innodb 类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺
                序排列,可以有效的提高导入数据的效率。如果 Innodb 表没有主键,那么系统会默认
                创建一个内部列作为主键,所以如果可以给表创建一个主键,将可以利用这个优势提高
                导入数据的效率。
            b. 在导入数据前执行 SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行 SET
                UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率。
            c. 如果应用使用自动提交的方式,建议在导入前执行 SET AUTOCOMMIT=0,关闭自动
                 提交,导入结束后再执行 SET AUTOCOMMIT=1,打开自动提交,也可以提高导入的效率。
    3.2 优化 insert 语句:
        3. 如果你同时从同一客户插入很多行,使用多个值表的 INSERT 语句。这比使用分开
            INSERT 语句快(在一些情况中几倍)。
            Insert into test values(1,2),(1,3),(1,4)…
        4. 如果你从不同客户插入很多行,能通过使用 INSERT DELAYED 语句得到更高的速度。
          Delayed 的含义是让 insert 语句马上执行,其实数据都被放在内存的队列中,并
             没有真正写入磁盘;这比每条语句分别插入要快的多;LOW_PRIORITY 刚好相反,
          在所有其他用户对表的读写完后才进行插入;
        5. 将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项);
        6. 如果进行批量插入,可以增加 bulk_insert_buffer_size 变量值的方法来提高速
              度,但是,这只能对 myisam 表使用;
        7. 当从一个文本文件装载一个表时,使用 LOAD DATA INFILE。这通常比使用很多
          INSERT 语句快 20 倍;
        8. 根据应用情况使用 replace 语句代替 insert;
        9. 根据应用情况使用 ignore 关键字忽略重复记录。
   3.3 优化 group by 语句
       默认情况下,MySQL 排序所有 GROUP BY col1,col2,....。查询的方法如同在查询
      中指定 ORDER BY col1,col2,...。如果显式包括一个包含相同的列的 ORDER BY
      子句,MySQL 可以毫不减速地对它进行优化,尽管仍然进行排序。
      如果查询包括 GROUP BY 但你想要避免排序结果的消耗,你可以指定 ORDER BY NULL
        禁止排序。
        例如:
            INSERT INTO foo  SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
   3.4 优化 order by 语句:
        在某些情况中,MySQL 可以使用一个索引来满足 ORDER BY 子句,而不需要额外的排
        序。 where 条件和 order by 使用相同的索引,并且 order by 的顺序和索引顺序相同 ,
        并且 order by 的字段都是升序或者都是降序。
       例如:下列 sql 可以使用索引。
        SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
        SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
        SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
    但是以下情况不使用索引:
        SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
    --order by 的字段混合 ASC 和 DESC
        SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
    --用于查询行的关键字与 ORDER BY 中所使用的不相同
        SELECT * FROM t1 ORDER BY key1, key2;
      --对不同的关键字使用 ORDER BY :
  3.5 优化 join 语句:
     查询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性
    的完成很多逻辑上需要多个步骤才能完成的 SQL 操作,同时也可以避免事务或者表锁
    死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)替代。
    假设我们要将所有没有订单记录的用户取出来,可以用下面这个查询完成:
    SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
    如果使用连接(JOIN).. 来完成这个查询工作,速度将会快很多。尤其是当 salesinfo
    表中对 CustomerID 建有索引的话,性能将会更好,查询如下:
    SELECT * FROM customerinfo
    LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID
    WHERE salesinfo.CustomerID IS NULL
    连接(JOIN).. 之所以更有效率一些,是因为 MySQL 不需要在内存中创建临时表来完
     成这个逻辑上的需要两个步骤的查询工作。
  3.6 mysql 如何优化 or 条件:
        对于 or 子句,如果要利用索引,则 or 之间的每个条件列都必须用到索引;如果
       没有索引,则应该考虑增加索引。
  3.7 查询优先还是更新(insert 、update 、delete)优先 :
    MySQL 还允许改变语句调度的优先级,它可以使来自多个客户端的查询更好地协
    作,这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类
    型的查询被处理得更快。
    我们首先应该确定应用的类型,判断应用是以查询为主还是以更新为主的,是确
    保查询效率还是确保更新的效率,决定是查询优先还是更新优先。
    下面我们提到的改变调度策略的方法主要是针对 Myisam 存储引擎的,对于
    Innodb 存储引擎,语句的执行是由获得行锁的顺序决定的。
      MySQL 的默认的调度策略可用总结如下:
        1. 写入操作优先于读取操作。
        2. 对某张数据表的写入操作某一时刻只能发生一次,写入请求按照它们到达的次序来处理。
        3. 对某张数据表的多个读取操作可以同时地进行。
    MySQL 提供了几个语句调节符,允许你修改它的调度策略:
        1. LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。
        2. HIGH_PRIORITY关键字应用于SELECT和INSERT语句。
        3. DELAYED关键字应用于INSERT和REPLACE语句。
    如果写入操作是一个 LOW_PRIORITY (低优先级)请求,那么系统就不会认为它的优
    先级高于读取操作。在这种情况下,如果写入者在等待的时候,第二个读取者到达了,那么
    就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候,才允许写入者开始
    操作。这种调度修改可能存在 LOW_PRIORITY 写入操作永远被阻塞的情况。
    SELECT 查询的 HIGH_PRIORITY (高优先级)关键字也类似。它允许 SELECT 插入正
    在等待的写入操作之前,即使在正常情况下写入操作的优先级更高。另外一种影响是,高优
    先级的 SELECT 在正常的 SELECT 语句之前执行,因为这些语句会被写入操作阻塞。
    如果你希望所有支持 LOW_PRIORITY 选项的语句都默认地按照低优先级来处理,那么
    请使用--low-priority-updates 选项来启动服务器。通过使用 INSERT HIGH_PRIORITY 来把
    INSERT 语句提高到正常的写入优先级,可以消除该选项对单个 INSERT 语句的影响。
  3.8 使用 SQL 提示:
        SELECT SQL_BUFFER_RESULTS ...
        将强制 MySQL 生成一个临时结果集。只要所有临时结果集生成后,所有表上的锁定均
        被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助。
        当处 理一个会 让客户端 耗费点时 间才能 处理的大 结果集时 ,可以考 虑使用
        SQL_BUFFER_RESULT 提示字。这样可以告诉 MySQL 将结果集保存在一个临时表中,这
        样可以尽早的释放各种锁。
   USE INDEX
        在你查询语句中表名的后面,添加 USE INDEX 来提供你希望 MySQ 去参考的索引列
        表,就可以让 MySQL 不再考虑其他可用的索引。
        Eg:SELECT * FROM mytable USE INDEX (mod_time, name) ...
   IGNORE INDEX
        如果你只是单纯的想让 MySQL 忽略一个或者多个索引,可以使用 IGNORE INDEX 作
        为 Hint。
        Eg:SELECT * FROM mytale IGNORE INDEX (priority) ...
   FORCE INDEX
        为强制 MySQL 使用一个特定的索引,可在查询中使用 FORCE INDEX 作为 Hint 。
        Eg:SELECT * FROM mytable FORCE INDEX (mod_time) ...

其他优化方法:
     定期分析表:
    ANALYZE TABLE
        语法:
        ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
        本语句用于分析和存储表的关键字分布。在分析期间,使用一个读取锁定对表进行锁
        定。这对于 MyISAM, BDB 和 InnoDB 表有作用。对于 MyISAM 表,本语句与使用 myisamchk -a 相当。
    CHECK TABLE
        语法:
        CHECK TABLE tbl_name [, tbl_name] ... [option] ...
        option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
        检查一个或多个表是否有错误。CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。对于
    MyISAM 表,关键字统计数据被更新。
        CHECK TABLE 也可以检查视图是否有错误,比如在视图定义中被引用的表已不存在。
      CHECKSUM TABLE
        语法:
        CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]
    报告一个表校验和。
     使用 optimize table :
   OPTIMIZE TABLE
        语法:
        OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
        如果已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有
        VARCHAR, BLOB 或 TEXT 列的表)进行了很多更改,则应使用 OPTIMIZE TABLE。被删
        除的记录被保持在链接清单中,后续的 INSERT 操作会重新使用旧的记录位置。您可
        以使用 OPTIMIZE TABLE 来重新利用未使用的空间,并整理数据文件的碎片。
         OPTIMIZE TABLE 只对 MyISAM, BDB 和 InnoDB 表起作用。


dropbox:邀请您的加入,让你随时随地同步您的文件:https://db.tt/wCU4HQq4

人生格言: 如果说人生是自我编写的程序, 那么青春就是其中意味深长的代码。 或简单,分解成彼此独立的字符: 或复杂, 拼凑成一连串神秘的语句。 我们尝试着不断调试, 不断优化,无论编译结果如何, 过程才是最美。 如果说这段代码不能复制, 那么我愿意用一生去续写.

评论

发表评论

validate