MySQL数据库优化

说到数据库的优化，我在MySQL数据库引擎这篇文章当中对使用MyIsam存储引擎的表和使用InnoDB存储引擎的表之间对比的过程中发现，InnoDB存储引擎的表插入速度十分的慢，我创建了一个存储过程直接往数据库中插入一千万条数据的时候，等了大约一二十分钟的样子，才插入了一百万数据，但是MyIsam存储引擎的表只用了五分钟的时间，一直拖到晚上十一点多，还是没有完成，无奈我只能改为插入一百万的数据作为测试。后来一想到，我的InnoDB存储引擎的表在插入的时候可能默认的是自动提交，这样的话每一次插入都要打开连接一次，十分耗时，解决方案是批处理，开始的时候把自动提交关闭，然后存储过程执行结束的时候开启自动提交即可。从这里可以看出优化是多么的重要。

批处理优化

正如上面所说的那样，批量的插入数据和一次次的插入数据效率是有很大的差距的。比如说：

create table testOptimize( id int unsigned primary key auto_increment, name varchar(20) );

创建这样的一个表，默认了存储引擎是InnoDB，因为InnoDB是支持事务的。

mysql> create procedure insertOptimize()
    -> begin
    -> set @i = 1;
    -> while @i <= 1000
    -> do 
    -> insert into testOptimize(name) values(concat("wy", @i));
    -> set @i = @i + 1;
    -> end while;
    -> end//
Query OK, 0 rows affected (0.38 sec)

mysql> call insertOptimize//
Query OK, 0 rows affected (7.39 sec)

mysql> \d ;
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.15 sec)

mysql> call insertOptimize;
Query OK, 0 rows affected (0.65 sec)

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

上面是一段存储过程：插入一千条数据的时间的对比，从这里面可以直接看出，差距还是比较大的，默认的情况下自动提交，这时候的执行时间是7.39秒，而将自动提交关闭，最后开启自动提交的方式，执行时间为1s以内，这样的差距对于大批量的数据插入差距更是十分明显的。

批量插入的另一种方式：

insert into testOptimize(name) values("wy1"),("wy2"),("wy3");

打开一次连接插入多次数据。

存储过程在上面的例子中已经用到了，这里不再举例子了。

触发器

创建一个触发器，在插入testOptimize表的同时，要往testOptimize2表中插入数据。

CREATE trigger testTriger before insert on testOptimize for each row
begin
insert into testOptimize2(id, name) values(new.id, new.name);
end

这里直接调用上一步中的存储过程即可。

索引优化

数据库优化中最常见的也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的SQL性能问题。

MySQL数据库引擎这篇文章中已经介绍了MyIsam存储引擎的主要特性，这里再简要说明一下，表的数据和索引分开来存储，各自是独立的文件，innoDB是存放在一个文件当中。

MySQL不支持函数索引，但是可以对列的某一部分进行索引，可以只取出四个字符进行索引。这个特性可以缩小索引文件的大小，可以根据这个特性去设计采用谁去生成索引。

存在索引且会用到索引

1. 查看SQL语句影响的行数

对于创建了索引的列：

mysql> desc select * from testOptimize where id = 1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: testOptimize
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

对于没有进行索引的列：

mysql> desc select * from testOptimize where name = "wy1"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: testOptimize
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3242
        Extra: Using where
1 row in set (0.00 sec)

我们会看到中间的rows信息的差别:上面创建了索引的列的查询行数为1，而下面的没有创建索引的列的查询行数为3242，应该说，如果数据量比较大的时候，明显是查询的行数越少效率会越高。

对下面这句没有索引的进行优化，也就是说对他创建索引，当然索引在使用时注意，不要随便建索引，因为索引会占据很大的空间存储。一般的是对于效率很低的查询语句中没有用到索引的列进行创建索引以提升速度。

mysql> alter table testOptimize add index testOptimize(name);

2. like的查询

后面如果是常量且只有%不在第一个字符的时候，索引才可能用得上的。

比如(这里name已经创建了索引)：

desc select * from testOptimize where name like "wy1%"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: testOptimize
         type: range
possible_keys: testOptimize
          key: testOptimize
      key_len: 23
          ref: NULL
         rows: 337
        Extra: Using where; Using index
1 row in set (0.22 sec)

那么将%放在第一位的话：

mysql> desc select * from testOptimize where name like "%wy1"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: testOptimize
         type: index
possible_keys: NULL
          key: testOptimize
      key_len: 23
          ref: NULL
         rows: 3242
        Extra: Using where; Using index
1 row in set (0.25 sec)

3. 如果某一列有索引，使用column_name is null 可能会使用索引。

这里的影响行数是1，而不是全部的数据。

mysql> desc select * from testOptimize where name is null\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: testOptimize
         type: ref
possible_keys: testOptimize
          key: testOptimize
      key_len: 23
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.40 sec)

存在索引但不使用的情况

1. 如果MySQL估计使用索引比全表扫描的更慢的话，不使用索引。

2. 用or连接的条件如果or前面的条件中的列有索引，后面的没有索引，那么涉及到索引不会被用到。

<strong><span style="font-size:18px;">mysql> alter table testOptimize drop index testOptimize;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc select * from testOptimize where name = "wy1" or id = 111\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: testOptimize
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3242
        Extra: Using where
1 row in set (0.00 sec)</span></strong>