MySQL——优化1

MySQL瓶颈优化

（业务从小到大的转变） 假设一个网站从最开始访问量很小做到日PV千万，来推测一下它的mysql服务器架构演变过程

• 第一阶段：网站访问量日pv量级在1w以下。 单台机器跑web和db，不需要做架构层调优（比如，不需要增加memcached缓存）。此时，数据往往都是每日冷备份的，但是有时候如果考虑数据安全性，会搭建一个mysql主从。
• 第二阶段：网站访问量日pv达到几万。 此时单台机器已经有点负载，需要我们把web和db分开，需要搭建memcached服务作为缓存。也就是说，在这个阶段，我们还可以使用单台机器跑mysql去承担整个网站的数据存储和查询。如果做mysql主从目的也是为了数据安全性。
• 第三阶段：网站访问量日pv达到几十万。 单台机器虽然也可以支撑，但是需要的机器配置要比之前的机器多好多。如果经费允许，可以购买配置很高的机器来跑mysql服务，但是并不是说，配置翻倍，到了一定阶段配置增加已经不能带来性能的增加。所以，此阶段，会想到做mysql服务的集群，也就是说可以拿多台机器跑mysql。但mysql的集群和web集群是不一样的，需要考虑数据的一致性，所以不能简单套用做web集群的方式。可以做的架构是，mysql主从，一主多从。为了保证架构的强壮和数据完整，主只能是一个，从可以是多个。还有一个问题，需要想到，就是在前端web层，程序里面指定了mysql机器的ip，那么当mysql机器有多台时，程序里面如何去配置？可以拿多台机器跑mysql，其中一台写，其他多台是读，只需要把读写的ip分别配置到程序中，程序自动会去区分机器。
• 第四阶段：网站访问量日pv到几百万。 之前的一主多从模式已经遇到瓶颈，因为当网站访问量变大，读数据库的量也会越来越大，需要多加一些从进来，但是从的数量增加到数十台时，由于主需要把bin-log全部分到所以从上，那么这个过程本身就是一件很繁琐读取，势必会造成从上同步过来的数据有很大延迟。所以，可以做一个优化，把mysql原来的一主多从为一主一从，然后作为其他从的主，而前面的主，只负责网站业务的写入，而后面的从不负责网站任何业务，只负责给其他从同步bin-log。这样还可以继续多叠加几个从库。
• 第五阶段：网站访问量日pv到1千万的时候， 可以发现，网站的写入量非常大，之前架构中只有一个主，这里的主已经成为瓶颈了。所以，需要再进一步作出调整

优化信息查询

解决瓶颈：做索引 查询一些 MySQL 数据库的性能参数用 show status

连接mysql服务器的次数

mysql> show status like 'connections';		
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 21272 |
+---------------+-------+

Uptime：mysql服务器的上线时间

mysql> show status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 10974 |
+---------------+-------+

Com_select：查询操作的次数

mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 8     |
+---------------+-------+

Com_insert：插入操作的次数

mysql> show status like 'com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 3     |
+---------------+-------+

Com_update：更新操作的次数

mysql> show status like 'com_update';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update    | 0     |
+---------------+-------+

Com_delete：删除操作的次数

mysql> show status like 'com_delete';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 0     |
+---------------+-------+

Slow_queries：查询mysql服务器的慢查询次数

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+

分析查询语句

Explain [extended] select select_option;

mysql> explain select * from fruits\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL			使用哪个列或常数与索引一起使用来查询记录
         rows: 16
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

• Select_type：表示select语句的类型 其中simple 是简单查询(不包括连接查询和子查询) Primary 主查询 Union 连接查询
• Type：表的连接类型 System仅有系统表一行 Const 数据表中最多只有一行匹配，将在查询开始时被读取，并在余下的查询优化中，作为常量 Eq_ref 用于使用 = 操作符比较带索引的列 ref 对于来自前面的表的任意行的组合，从该表中读取所有匹配的行 ref_or_null 同上，添加可以专门搜索包含null值的行 index_merge 将连接类型表示使用了索引并优化方法 range 只检索给定范围的行 index 与all的连接类型相同，除了只扫描索引树 all 前面的表的任意行的组合，进行完整的表的扫描
• possible_keys: NULL //指出mysql使用哪个索引在表中找到行 NULL表示没有创建索引

describe select 语句和分析查询语句

mysql> desc select * from fruits\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

索引提高了查询效率

Create index 索引名 on 表名(字段)

mysql> explain select * from fruits where f_name='apple';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | fruits | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   16 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> create index index_name on fruits(f_name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from fruits where f_name='apple';
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | fruits | NULL       | ref  | index_name    | index_name | 765     | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

优化查询速度

1、做索引了之后，用 like ‘xx%’ %不在第一位查询效率最高

mysql> explain select * from fruits where f_name like '%x';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | fruits | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   16 |    11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from fruits where f_name like 'x%';
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | fruits | NULL       | range | index_name    | index_name | 765     | NULL |    4 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

2、多字段索引，除了第一字段查询最快，其余不按索引来，索引不生效

mysql> create index index_id_price on fruits(f_id,f_price);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from fruits where f_id='12'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: no matching row in const table
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from fruits where f_price='5.2'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

3、若创建索引所设置的字段，查询索引组合 or 左右边的值都是属于索引设置字段下的值

mysql> explain select * from fruits where f_name='apple' or s_id=101\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
   partitions: NULL
         type: ALL
possible_keys: index_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16
     filtered: 19.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from fruits where f_name='apple' or f_id='12'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
   partitions: NULL
         type: index_merge
possible_keys: PRIMARY,index_name,index_id_price
          key: index_name,PRIMARY
      key_len: 765,30
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using union(index_name,PRIMARY); Using where
1 row in set, 1 warning (0.00 sec)

profiling分析查询

通过慢日志查询可以知道哪些SQL语句执行效率低下，通过 explain 可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。如果觉得explain的信息不够详细，可以同通过 profiling 命令得到更准确的SQL执行消耗系统资源的信息。 profiling 默认是关闭的。可以通过以下语句查看

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |		off表示未开启
| profiling_history_size | 15    |
+------------------------+-------+

或

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |			0表示未开启
+-------------+

打开profiling功能：

mysql> set profiling=1;

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+

执行要测试的sql语句

mysql> select * from fruits where f_name='apple';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| a1   |  101 | apple  |    5.20 |
+------+------+--------+---------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+-------------------------------------------+
| Query_ID | Duration   | Query                                     |
+----------+------------+-------------------------------------------+
|        1 | 0.00021250 | select @@profiling                        |
|        2 | 0.00033250 | select * from fruits where f_name='apple' |
+----------+------------+-------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000059 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000022 |
| init                 | 0.000030 |
| System lock          | 0.000008 |
| optimizing           | 0.000007 |
| statistics           | 0.000063 |
| preparing            | 0.000009 |
| executing            | 0.000002 |
| Sending data         | 0.000084 |
| end                  | 0.000007 |
| query end            | 0.000006 |
| closing tables       | 0.000005 |
| freeing items        | 0.000012 |
| cleaning up          | 0.000013 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

• status：是profile里的状态
• duration：是status状态下的耗时。因此关注的就是那个状态最耗时，这些状态中哪些可以优化。 当然也可以查看更多的信息如CPU等等

SHOW PROFILE [type [, type] … ][FOR QUERY n] type:

• ALL：显示所有的开销信息 BLOCK
• IO：显示块IO相关开销
• CPU：显示用户CPU时间、系统CPU时间
• IPC：显示发送和接收相关开销信息
• PAGE FAULTS：显示页面错误相关开销信息
• SWAPS：显示交换次数相关开销的信息

测试完成之以后，记得要关闭调试功能，以免影响数据库的正常使用：

mysql> set profiling=0;

优化数据库表结构

将字段很多的表分解成多个表 查看表的表结构 优化表结构

1、将字段很多的表分解成多个表
2、增加中间表
3、合理增加冗余字段
4、优化插入记录的速度

（1）禁用索引(在插入数据之前禁用索引，会让创建索引不会生效，命令：alter table 表名 disable keys ，注意表的创建表后加引擎 engine=myisam，可以禁用成功)

mysql> alter table fruits disable keys;
Query OK, 0 rows affected, 1 warning (0.00 sec)

（2）禁用唯一性检查(插入记录之前禁用唯一性检查，命令：set unique_checks=0 关闭set unique_checks=1)

mysql> set unique_checks=0;
Query OK, 0 rows affected (0.00 sec)

（3）使用批量插入(多条插入命令整合成一条命令)

mysql> insert into fruits values('x1','101','mongo2','5.5');
Query OK, 1 row affected (0.01 sec)

mysql> insert into fruits values('x2','101','mongo2','5.5');
Query OK, 1 row affected (0.00 sec)

mysql> insert into fruits values('x3','101','mongo2','5.5');
Query OK, 1 row affected (0.00 sec)

mysql> insert into fruits values('x4','101','mongo2','5.5');
Query OK, 1 row affected (0.00 sec)

mysql> insert into fruits values('x8','101','mongo2','5.5'),
    -> ('x7','101','mongo2','5.5'),('x6','101','mongo2','5.5'),
    -> ('x5','101','mongo2','5.5');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

（4）使用 load data infile 批量插入 对于 innnodb 表来说

（1）禁用唯一性检查

mysql> set unique_checks=0;
Query OK, 0 rows affected (0.00 sec)

（2）禁用外键检查(命令: set foreign_key_checks=0，开启=1)

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

（3）禁用自动提交(命令: set autocommit=0，开启=1)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

5、分析表，检查表和优化表

• 分析表：分析关键字的分布
• 检查表：检查是否存在错误
• 优化表：消除删除或更新造成的空间浪费

分析表

分析表语句：analyze [local |no_wirte_to_binlog] table tb1_name[tb2_name]…… Local的关键字不写入二进制日志 后跟1个表或多个表 在分析期间只能读，不能进行插入和更新的操作。

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

• Table是表名
• op执行的操作是什么
• msg_type 信息级别（status是正常状态，info是信息，note注意，warning警告，error错误）
• msg_text 是显示信息

检查表

检查表：检查是否存在错误，关键字统计，检查视图是否有错误 Check table 表名 option ={quick |fast | medium |extended |changed} Quick 不扫描行，不检查错误连接 Fast 只检查没有被正确关闭的表 Medium 扫描行验证被删除的连接是有效的，也可以计算各行的关键字校验和。 Extended 对每行所有关键字进行全面的关键字查找 Changed 只检查上次检查后被更改的表和没有被正确关闭的表 Option只对myisam 有效 对innodb表无效 在执行时会给表加上只读锁

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

优化表

优化表：消除删除或更新造成的空间浪费 Optimize [local |no_write_to_binlog] table tb1_name …. 只能优化 myisam 的表和 innodb 的表都有效 但是只能优化表中的varchar\text\blob 执行过程中上只读锁

mysql> optimize table fruits\G
*************************** 1. row ***************************
   Table: ooo.fruits
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: ooo.fruits
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (0.04 sec)