MYSQL优化

优化

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> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 3     |
+---------------+-------+
1 row in set (0.01 sec) //连接mysql服务器的次数

Uptime：mysql服务器的上线时间

mysql> show status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 204   |
+---------------+-------+
1 row in set (0.00 sec)

Com_select:查询操作的次数

mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 1     |
+---------------+-------+
1 row in set (0.00 sec)

Com_insert：插入操作的次数

mysql> show status like 'com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 0     |
+---------------+-------+
1 row in set (0.00 sec)

Com_update：更新操作的次数

mysql> show status like 'com_update';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update    | 0     |
+---------------+-------+
1 row in set (0.00 sec)

Com_delete：删除操作的次数

mysql> show status like 'com_delete';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 0     |
+---------------+-------+
1 row in set (0.01 sec)

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

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

分析查询语句

创建库和表

mysql> create database fruits_sql;
Query OK, 1 row affected (0.00 sec)

mysql> use fruits_sql
Database changed

mysql> create table fruits 
  (
  f_id char(10) not null,
  s_id int not null,
  f_name char(255) not null,
  f_price decimal(8,2) not null,
  primary key(f_id)
  );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into fruits(f_id,s_id,f_name,f_price)
    values('a1',101,'apple','5.2'),
    ('b1',101,'blackberry','10.2'),
    ('bs1',102,'orange','11.2'),
    ('bs2',105,'melon','8.2'),
    ('t1',102,'banana','10.3'),
    ('t2',102,'grape','5.3'),
    ('o2',103,'coconut','9.2'),
    ('c0',101,'cherry','3.2'),
    ('a2',103,'apricot','2.2'),
    ('l2',104,'lemon','6.4'),
    ('b2',104,'berry','7.6'),
    ('m1',106,'mango','15.7'),
    ('m2',105,'xbabay','2.6'),
    ('m3',105,'xxtt','11.6'),
    ('b5',107,'xxxx','3.6');
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 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: 15
     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: 15
     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'\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: 15
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec