MySQL数据库的优化

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

　　批处理优化

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

　　[sql] view plain copy

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

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

　　如下所示：

　　[sql] view plain copy

　　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以内，这样的差距对于大批量的数据插入差距更是十分明显的。

　　批量插入的另一种方式，如下所示：

　　[sql] view plain copy

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

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

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

　　触发器

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

　　[sql] view plain copy

　　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语句影响的行数

　　对于创建了索引的列，如下所示：

　　[sql] view plain copy

　　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)

　　对于没有进行索引的列，如下所示：

　　[sql] view plain copy

　　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，应该说，如果数据量比较大的时候，明显是查询的行数越少效率会越高。

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

　　[sql] view plain copy

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

　　2. like的查询

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

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

　　[sql] view plain copy

　　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)

　　那么将%放在第一位的话，如下所示：

　　[sql] view plain copy

　　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，而不是全部的数据。

　　[sql] view plain copy

　　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前面的条件中的列有索引，后面的没有索引，那么涉及到索引不会被用到。

　　[sql] view plain copy

　　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)