Mysql优化

为什么要有mysql优化，具体是什么东西，我们通过一个案例来大概了解一下：

提个需求： 我们需要做一张5000个记录的一张表。
1.我们先用引擎是innodb;来做。
我们先要写一个存储过程：存储过程是怎么写的，请参考
存储过程

drop procedure if exists pro;delimiter $$create procedure pro()begindeclare add_name varchar(50);declare add_age int(11);declare i int(11) default 1;while(i<100) doset i:= i+1;set add_name := concat("hourse",i);set add_age := floor(rand()*100);insert into dept(dept_name,age) values(add_name,add_age);end while;end$$delimiter ;

运行上面的代码，我们设置存储过程成果，利用call调用存储过程pro，实现在innodb引擎下插入100条数据。

mysql> drop procedure if exists pro;Query OK, 0 rows affected (0.11 sec)mysql> delimiter $$mysql> create procedure pro()    -> begin    ->  declare add_name varchar(50);    ->  declare add_age int(11);    ->  declare i int(11) default 1;    ->  while(i<100) do    ->  set i:= i+1;    ->  set add_name := concat("hourse",i);    ->  set add_age := floor(rand()*100);    ->  insert into dept(dept_name,age) values(add_name,add_age);    ->  end while;    -> end$$Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql>mysql>mysql> call pro;Query OK, 1 row affected (6.36 sec)####这里我们发现利用了6smysql> select * from dept;+---------+-----------+------+| dept_id | dept_name | age  |+---------+-----------+------+|       1 | hourse2   |   53 ||       2 | hourse2   |   27 ||       3 | hourse3   |   56 ||       4 | hourse4   |   98 ||       5 | hourse5   |   25 ||       6 | hourse6   |   31 ||       7 | hourse7   |   80 ||       8 | hourse8   |    9 ||       9 | hourse9   |    6 ||      10 | hourse10  |    2 ||      11 | hourse11  |   94 ||      12 | hourse12  |   62 ||      13 | hourse13  |   30 ||      14 | hourse14  |   65 ||      15 | hourse15  |   34 ||      16 | hourse16  |   76 ||      17 | hourse17  |   80 ||      18 | hourse18  |   72 ||      19 | hourse19  |   19 ||      20 | hourse20  |   80 ||      21 | hourse21  |   45 ||      22 | hourse22  |   86 ||      23 | hourse23  |   95 ||      24 | hourse24  |   18 ||      25 | hourse25  |    4 ||      26 | hourse26  |   69 ||      27 | hourse27  |   33 ||      28 | hourse28  |   59 ||      29 | hourse29  |   96 ||      30 | hourse30  |    1 ||      31 | hourse31  |   21 ||      32 | hourse32  |    2 ||      33 | hourse33  |   46 ||      34 | hourse34  |   23 ||      35 | hourse35  |   81 ||      36 | hourse36  |   35 ||      37 | hourse37  |   31 ||      38 | hourse38  |   51 ||      39 | hourse39  |   61 ||      40 | hourse40  |   54 ||      41 | hourse41  |   87 ||      42 | hourse42  |   75 ||      43 | hourse43  |   13 ||      44 | hourse44  |   39 ||      45 | hourse45  |   57 ||      46 | hourse46  |   70 ||      47 | hourse47  |   80 ||      48 | hourse48  |   91 ||      49 | hourse49  |   16 ||      50 | hourse50  |    6 ||      51 | hourse51  |   81 ||      52 | hourse52  |   90 ||      53 | hourse53  |    6 ||      54 | hourse54  |   63 ||      55 | hourse55  |   94 ||      56 | hourse56  |   85 ||      57 | hourse57  |   42 ||      58 | hourse58  |   56 ||      59 | hourse59  |   53 ||      60 | hourse60  |   97 ||      61 | hourse61  |   26 ||      62 | hourse62  |   41 ||      63 | hourse63  |   29 ||      64 | hourse64  |   20 ||      65 | hourse65  |   16 ||      66 | hourse66  |   19 ||      67 | hourse67  |   47 ||      68 | hourse68  |   79 ||      69 | hourse69  |   54 ||      70 | hourse70  |   34 ||      71 | hourse71  |    7 ||      72 | hourse72  |   36 ||      73 | hourse73  |   60 ||      74 | hourse74  |   90 ||      75 | hourse75  |   71 ||      76 | hourse76  |   88 ||      77 | hourse77  |   26 ||      78 | hourse78  |   64 ||      79 | hourse79  |   46 ||      80 | hourse80  |   39 ||      81 | hourse81  |   55 ||      82 | hourse82  |   61 ||      83 | hourse83  |   40 ||      84 | hourse84  |   19 ||      85 | hourse85  |   74 ||      86 | hourse86  |   13 ||      87 | hourse87  |   46 ||      88 | hourse88  |   88 ||      89 | hourse89  |    6 ||      90 | hourse90  |   64 ||      91 | hourse91  |    4 ||      92 | hourse92  |   27 ||      93 | hourse93  |   26 ||      94 | hourse94  |   49 ||      95 | hourse95  |   69 ||      96 | hourse96  |   97 ||      97 | hourse97  |   77 ||      98 | hourse98  |   94 ||      99 | hourse99  |   39 ||     100 | hourse100 |   16 |+---------+-----------+------+100 rows in set (0.00 sec)

我们先会发现innodb用时6秒多插入了100条数据。
我们把数据删除掉。

mysql> truncate dept;Query OK, 0 rows affected (0.10 sec)mysql> select * from dept;Empty set (0.00 sec)

更换表的引擎

alter table dept engine myisam;

mysql> alter table dept engine myisam;Query OK, 0 rows affected (0.29 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table dept;+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                      |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| dept  | CREATE TABLE `dept` (  `dept_id` int(11) NOT NULL AUTO_INCREMENT,  `dept_name` varchar(100) DEFAULT NULL COMMENT '部门名字',  `age` int(11) DEFAULT NULL,  PRIMARY KEY (`dept_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

我们在把pro的存储过程设置一下：

mysql> call pro;Query OK, 1 row affected (0.01 sec)mysql> select * from dept;+---------+-----------+------+| dept_id | dept_name | age  |+---------+-----------+------+|       1 | hourse2   |   64 ||       2 | hourse3   |   70 ||       3 | hourse4   |   61 ||       4 | hourse5   |   93 |

在myisam下我们发现使用时间为0.01s，比innodb的6s要小了很多。这就是大致的一个mysql的优化实例，详细的优化文章，请听下回分解。