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

《深入浅出Mysql数据库开发、优化与管理维护》笔记
[b]1、优化sql的一般步骤[/b]
[b]1.1 使用 show status 命令了解各种sql的执行频率[/b]
进入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： 慢查询的次数

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

[b]1.3 通过Explain分析低效SQL的执行计划[/b]
再通过上面步骤，查到低效率的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: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 0
        Extra: const row not found
1 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=>对于前面的每一行，都通过全表扫描来得到数据

[b]2 索引问题[/b]
[b]2.1 索引的存储分类[/b]
MyISAM存储索引的表的数据和索引是自动分开存储的，各自是独立的文件
InnoDB存储引擎的表的数据和索引是存储在同一个表的空间里面，但是可以有多个文件组成
Mysql中的存储类型木匾只有两种，BTREE和HASH，具体情况和表的存储引擎有关。
MyISAM和InnoDb存储引擎都支持BTREE索引，
MEMORY/HEAP存储引擎可以支持HASH和BTREE索引

mysql不支持函数索引，但是能对队列的前面的某一部分进行索引，例如name字段，可以只取name的前四个字符来进行索引。

[b] 2.2 使用索引[/b]
对相关列使用索引是提高select性能的最佳途径
使用索引的条件：a、查询条件中有索引关键字，b、多列索引只有查询条件使用了多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。
[b]2.2.1 使用索引[/b]
一下情况中会使用到索引：
（1） 多列索引，只要查询的条件中用到了最左边的列，索引一般就会被使用
（2） 对于使用like查询，后面如果是常量，只有%号不在第一个字符时，索引才可能被用到 比如 like "%3" 不会用索引， like "3%"就会走索引
（3） 对大文本进行搜索的时候，使用全文索引，而不是使用 like '%...%'
（4） 如果列名是索引，使用column_name is null将使用索引，
例如： select * from aaa where name is null（name是索引列）
[b]2.2.2 存在索引但不使用索引[/b]
在下列情况下，虽然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”）

[b] 2.3 查看索引使用情况[/b]
如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低得知表明增加索引得到的性能改善不高
Handler_read_rnd_next的值高则意味着查询的效率低效，并且应该建立索引补救。它的含义是：数据文件中读下一行的请求数。如果正在进行大量的表扫描，Handle_read_rnd_next的值较高，则通常说明表索引不正确或者写入的查询没有利用索引
查看方法：show status like 'Handler_read%'

[b]3 简单的优化方法[/b]
[b]3.1 定期分析表和检查表[/b]
分析表的语法：
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也可以检查视图是否有错误，例如：在视图定义中被引用的表不存在

[b] 3.2 定期优化表[/b]
语法：
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)

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

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

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

[b] 4.2 优化insert语句[/b]
（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倍
[b] 4.3 优化group by语句[/b]
如果查询包括group by但是用户想避免白须结果的小号，可以指定order by null禁止排序
例如：

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

[b]4.4 优化order by语句[/b]
可以使用索引来满足一个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 对不同的关键字使用排序

[b] 4.5 优化嵌套查询[/b]
有些情况下，子查询可以被更加有效的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

[b] 4.6 优化OR条件[/b]
or的每一个条件都使用索引
但是如果or的几个条件正好是复合索引的元素，则不能起到加速的效果
[b]4.7 使用SQL提示[/b]
在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使用一个特定的索引，可在查询中使用