Mysql优化（深入浅出mysql数据库开发阅读笔记）

Mysql  /  houtizong 发布于 2年前   184

《深入浅出Mysql数据库开发、优化与管理维护》笔记
1、优化sql的一般步骤
  1.1 使用 show status 命令了解各种sql的执行频率
      进入mysql控制台执行

        show session status 查询当前连接的统计结果        show global status 查询自数据库上次启动至今的统计结果

      或者在操作系统的终端执行
        mysqladmin extended-status
     
      可以通过like语句来查询一些特定的内容
      比如 show global status like "Com_%";就查看上次启动以来的每个Com_xxx语句执行的次数

     Com_select: 执行select查询的次数     Com_insert: 执行insert的次数     Com_update: 执行update的次数     Com_delete: 执行delete的次数

     这些参数会都mysql所有存储引擎的表操作都做记录
     另外还有专门针对某一些存储引擎的，比如：

     Innodb_rows_read     Innodb_rows_insert     Innodb_rows_update     Innobd_rows_delete

     通过上面这些数据的比较可以判断数据库是写为主，还是查询为主

     关于事务的信息 可以通过 Com_commit 和 Com_rollback 来了解提交和回滚的情况。如果回滚操作非常的频繁，说明应用编写存在很大的问题
    
     另外几个比较重要的，可以展示数据库基本情况的句子：

     Connections： 试图连接Mysql数据库的次数     Uptime： 服务器工作时间     Slow_queries： 慢查询的次数

   1.2 定位执行效率比较低的sql语句
      有两种方法：
      一、通过慢查询日志来定位。用--log-slow-queries[=filename]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒得sql语句的日志文件
      二、使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等，可以实时的查看sql的执行情况，同时对一些锁表操作进行优化。

   1.3 通过Explain分析低效SQL的执行计划
       再通过上面步骤，查到低效率的sql语句之后，可以通过 explain和desc命令获取mysql如何执行select语句的信息，包括select执行过程中表如何连接和连接的顺序。
       比如

explain select * from sum(money) from sales a, company b where a.company_id = b.id and a.year = 2006+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+|  1 | SIMPLE      | user  | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+1 row in set (0.00 sec)

       这个显示的结果是横向的，不够友好，在后面 追加参数“\G”可以每一项一列信息的显示

*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: user         type: systempossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 0        Extra: const row not found1 row in set (0.00 sec)ERROR:No query specified

上面每一列的含义说明

   select_type: 表示select的类型（SIMPLE=>简单表，不使用表连接或者子查询,PRIMARY=>主查询,外层的查询，UNION=>UNION中的第二个或者后面的查询语句，SUBQUERY=>子查询中的第一个select）   table; 输出结果集的表   possible_keys: 表示查询时，可能使用的索引   key: 表示实际使用的索引   key_len: 索引字段的长度   rows; 扫描的行的数量   Extra: 执行情况的说明和描述   type: 表示表的连接类型，性能有好到差的链接类型为：      system=>只有一行，也就是常量表，      const=>单表中最多有一个匹配行，例如primary key或者unique index，      eq_ref=>对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key或者unique index，       ref=>与eq_ref类似，区别在于不是使用primay key或者unique index而是使用普通的索引,      ref_or_null=>与ref类似，区别在于条件中包含对null的查询，      index_merge=>索引合并优化，      unique_subquery=>in的后面是一个查询主键字段的子查询，      index_subquery=>与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询，      range=>单表中的查询范围，      index=>对于前面的每一行，都通过查询索引来得到数据，      all=>对于前面的每一行，都通过全表扫描来得到数据

   
2 索引问题
  2.1 索引的存储分类
     MyISAM存储索引的表的数据和索引是自动分开存储的，各自是独立的文件
     InnoDB存储引擎的表的数据和索引是存储在同一个表的空间里面，但是可以有多个文件组成
     Mysql中的存储类型木匾只有两种，BTREE和HASH，具体情况和表的存储引擎有关。
     MyISAM和InnoDb存储引擎都支持BTREE索引，
     MEMORY/HEAP存储引擎可以支持HASH和BTREE索引
    
     mysql不支持函数索引，但是能对队列的前面的某一部分进行索引，例如name字段，可以只取name的前四个字符来进行索引。

2.2 使用索引
     对相关列使用索引是提高select性能的最佳途径
     使用索引的条件：a、查询条件中有索引关键字，b、多列索引只有查询条件使用了多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。
    2.2.1 使用索引
     一下情况中会使用到索引：
     （1） 多列索引，只要查询的条件中用到了最左边的列，索引一般就会被使用
     （2） 对于使用like查询，后面如果是常量，只有%号不在第一个字符时，索引才可能被用到 比如 like "%3" 不会用索引， like "3%"就会走索引
     （3） 对大文本进行搜索的时候，使用全文索引，而不是使用 like '%...%'
     （4） 如果列名是索引，使用column_name is null将使用索引，
      例如： select * from aaa where name is null（name是索引列）
    2.2.2 存在索引但不使用索引
      在下列情况下，虽然mysql存在索引，但是并不会使用到索引
     （1）如果Mysql估计使用索引比全表扫描更慢，则不使用索引。例如如果列key_1 均匀分布在1和100之间，那么查询 select * from table where key_1 > 1 and key_1 < 90;
     （2）如果使用MEMPRY/HEAP表并且，where条件中不使用“=”进行索引列，那么不会用到索引。heap表只有在使用“=”的时候，才使用索引
     （3）用or隔开的条件，如果or前面的列中有索引，而后面的列中没有索引，那么涉及的索引都不会被用到（or中有一个条件中的列没有索引就用不到索引）
     （4）如果不是索引列的第一部分（复合索引的第一部分）
     （5）如果like是%开始的
     （6）如果列类型是字符串，那么一定记得在where条件中把字符常量值用引号引起来，否则即便是这个列上有索引，也不用用到（比如name字段是字符串的，却写了name=123。要改成“123”）
    
   2.3 查看索引使用情况
    如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低得知表明增加索引得到的性能改善不高
    Handler_read_rnd_next的值高则意味着查询的效率低效，并且应该建立索引补救。它的含义是：数据文件中读下一行的请求数。如果正在进行大量的表扫描，Handle_read_rnd_next的值较高，则通常说明表索引不正确或者写入的查询没有利用索引
    查看方法：show status like 'Handler_read%'

3 简单的优化方法
   3.1 定期分析表和检查表
   分析表的语法：
   analyze [local | no_write_to_binlog] table tab1_name [, ta1_name] ...
   用于分析和存储表的关键字分布
   分析结果可以使得系统得到更准确地统计信息，使得sql能够正确的执行计划。
   如果用户感觉实际执行计划并不是与预期的执行计划，执行一次分析表可能会解决问题。
   在分析期间，使用一个读取锁对表进行锁定，这对于MyISAM，BDB和InnoDb表有作用。对于MyISAM表，与使用myisamchk -a 相当
   例如：
   analyze table user;

mysql> analyze table user;+------------+---------+----------+----------+| Table      | Op      | Msg_type | Msg_text |+------------+---------+----------+----------+| mysql.user | analyze | status   | OK       |+------------+---------+----------+----------+1 row in set (0.05 sec)

   检查表的语法如下：
   check table tab1_name [,tab1_name] ... [option] .. option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
   作用是：检查一个或者多个表中是否有错误。他对MyISAM和InnoDB表有作用，对于MyISAM表，关键字统计数据被更新。例如：

  check table user;mysql> check table user;+------------+-------+----------+----------+| Table      | Op    | Msg_type | Msg_text |+------------+-------+----------+----------+| mysql.user | check | status   | OK       |+------------+-------+----------+----------+1 row in set (0.00 sec)

  check table也可以检查视图是否有错误，例如：在视图定义中被引用的表不存在

3.2 定期优化表
    语法：
    optimize [local | no_write_to_binlog] table tab1_name [, tab1_name] ...
    适用范围：
    a、删除了表的一部分
    b、对含有可变长度行的表（varchar，blob，text列的表）进行了很多更改。
    这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费。但是这个命令只对MyISAM，BDB和InnoDb表起作用

mysql> optimize table user;+------------+----------+----------+----------+| Table      | Op       | Msg_type | Msg_text |+------------+----------+----------+----------+| mysql.user | optimize | status   | OK       |+------------+----------+----------+----------+1 row in set (0.01 sec)

4 常用SQL的优化
  4.1 大批量插入数据
    当用load命令导入数据的时候，适当的设置可以提高导入的速度
    4.1.1 对于MyISAM存储引擎的表方法如下：

    alter table tab_name disable keys;    load the data    alter table tab_name enable keys;

    上面操作是打开或者关闭MyISAM表不是唯一索引的更新
    ps：导入非空数据表的时候，上面方法很有效，但是导入空表的时候，索引是数据导入完毕之后才去创建的，所以没有影响
    4.2.2 对InnoDB表的数据导入
     （1）InnoDB类型的表是按照主键的顺序来存储的，所以导入数据按照主键的顺序排列，可以有效的提高导入的效率
     （2）在导入数据之前，关闭唯一性校验，set unique_checks=0 ,在导入结束之后设置为 1 开启唯一性校验，可以提高导入效率
     （3）如果应用使用自动提交的方式，建议在导入数据的时候执行 set autocomment=0,关闭自动提交，导入结束之后再设置为1，打开自动提交

  4.2 优化insert语句
     （1）如果是从同一客户那里插入很多航，尽量使用多个值表的insert语句，这种方式将大大的缩短客户端与数据库之间的链接，关闭等资源的消耗，使得效率比分开执行的单个insert语句快
     例如：insert to test values(1,2),(1,3),(1,4)....
     （2）如果从不同的客户中杀入多行，能通过使用insert delayed语句得到更高的速度。delayed是让insert语句立马执行，四十数据都被放在内存的队列中，并没有真正的写入磁盘，这比每一条数据分别插入快得多。low_priority 刚好想法，是所有其他用户对表的读写完成后才进行插入
     （3）将索引文件和数据文件分在不同的磁盘上存放
     （4）如果进行批量插入可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是这只能对MyIsAM表使用
     （5）当一个文本文件装载一个表时，使用 load file insert。这比通常的sql语句快20倍
4.3 优化group by语句
     如果查询包括group by但是用户想避免白须结果的小号，可以指定order by null禁止排序
   例如：

   select id, sum(money) from sale2 group by id order by null

4.4 优化order by语句
    可以使用索引来满足一个order by语句
    条件：
    where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者降序

    比如： order by key1,key2           where key1=123 order by key1 desc, key2 desc           order by key1 desc, key2 desc

    但是以下情况不可以

       order by key1 asc, key2 desc 混合使用ASC和DESC       where key2 = 1 ， order by key1 查询的关键字和排序的不一样       order by key1，key2 对不同的关键字使用排序

   
4.5 优化嵌套查询
     有些情况下，子查询可以被更加有效的join查询代替

     比如 select * from a where b_id not in(select id from b)      换成 select * from a left join b on a.b_id = b.id where a.b_id is not null

  4.6 优化OR条件
      or的每一个条件都使用索引
      但是如果or的几个条件正好是复合索引的元素，则不能起到加速的效果
   4.7 使用SQL提示
      在sql中加入一些人为的提示来达到优化操作的目的
      例如：
      select sql_buffer_results * from
      这个语句将强行MySql生成一个临时的结果集，只要临时结果集生成之后，所有表上的锁都会被释放。
      这能解决遇到表锁问题时，或者要花长时间将结果传给客户端时有所帮助，因为这样可以尽快的释放资源

      （1）use index
      在查询语句中表名的后面，添加use index来提供希望mysql去参考的索引列表，就可以让mysql不再考虑其他可用的索引

      select * from a use index(ind_a_id) where id = 1

      （2）ignore index
      如果用户只是单纯的想让mysql忽略一个多个索引，则可以使用ignore index作为hint

       select * from a ignore index(ind_a_id) where id = 1

      （3）force index
      强制MySQL使用一个特定的索引，可在查询中使用