MYSQL开发实用知识集合（暂告一段落，欢迎大家补充）

我在最近的几个项目中深刻理解到，其实项目应用的瓶颈还是在db端，在只有少量数据及极少并发的情况下，并不需要多少的技巧就可以得到我们想要的结果，但是当数据量达到一定量级的时候，程序的每一个细节，数据库的设计都会影响到系统的性能。这里就数据库开发及优化的话题和大家做个讨论和分析，也请大家完善，这里就以下几个话题，我先发表自己的见解。

1.存储引擎的选择

2.索引的设计及使用

3.大批量插入时SQL语句的优化

声明：本文所针对的数据库版本都是MYSQL 5

      这里我主要针对两种存储引擎进行简单比较分别是MyISAM和InnoDB，首先比较下区别：
      1. MyISAM不支持事务，不支持外键，优点是访问速度高，批量插入速度快。假设大量的操作是select、insert，建议采用该存储引擎。但是在我的实际应用中，出现过批量插入过于频繁的时候，当数据量到达一定级别，出现表损坏的情况。

      2. InnoDB支持事务处理，但是相对于前者，处理效率低一些，并且其索引及数据也更占用磁盘空间。在存储一些关键数据，并需要对其进行事务操作的时候，我们可以选择innodb，当然，我认为他不应该是访问量太大的。

没有索引的表是恐怖的，除非里头没多少数据，但是怎么设计索引是合理的？恐怕不是所有人都明白，这里简要分析下索引的设计及使用。

1. 索引通常是设置where字句中的列，如果你设置select后的列，这是没有任何意义的。当然你需要对某列进行排序，order by后的列也是可以建成索引的。

2. 使用唯一索引，主键就是最好的例子，假设你建的索引列，大量都是重复的，例如：性别，那么这样的索引并不会加快搜索速度。至于为什么，请大家自行了解索引的工作原理。

3. 只要有可能，就要尽量限定索引的长度，例如索引列为 char(100)，在其前10个字符大部分都是唯一的，请设置索引的长度为10，使用短索引可以加快查询速度，并节省硬盘空间。

4. 索引的左前缀特性，联合索引实质上也是建立了多个的索引，那么是建立联合索引好还是分别建多个索引好呢？显然前者更好，利用左前缀特性，只要联合索引的最左的列被用到，那么索引都会被使用。

5. 当然，最后要说的是，不要过度使用索引，索引越多，插入的速度越慢，尤其到数据量庞大时，同时，大量的索引将耗费很多硬盘空间，造成不必要的浪费。

下面举几个列子来说明索引的使用：

1.联合索引的左前缀

先看索引结构:[code]mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user  |          0 | PRIMARY  |            1 | user_id     | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| user  |          1 | user     |            1 | username    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| user  |          1 | user     |            2 | order       | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| user  |          1 | user     |            3 | email       | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)[/code]user是联合索引的名称，包含3个列，分别是username,order,email。接下来执行以下sql，使用explain命令来分析下运行结果。[code]
mysql> explain select * from user where username='leehui';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | user  | ref  | user          | user | 152     | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from user where pws='123';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
[/code]在两句sql中，我们可以发现，第一个sql虽然没用上，全部的索引列，但由于使用到了最左端的列，所以，联合索引还是启用了，第二句没有使用到最左的列，所以索引没有使用。

2.关于like关键字
对于使用like的查询，需要注意的是只有列的%不在第一个字符索引才可能被使用。以下分别展示了使用like的查询，第一个是索引被使用的，第二个是索引未被使用的。

[code]
mysql> explain select * from user where username like'lee%';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | range | user          | user | 152     | NULL |    1 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from user where username like'%lee';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
[/code]

3. 查看索引使用情况
使用以下命令
[code]mysql> show status like 'Handler_read_key';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Handler_read_key | 0     |
+------------------+-------+
1 row in set (0.00 sec)[/code]

如果索引正在工作，那么Handler_read_key 会很高，如果查询中出现Handler_read_rnd_next的值很高，则表明查询低效，索引的应用并不合理。

在大量插入时，尤其是并发插入时，mysql往往要承受更高的负载，使用mysql administortar的健康检查就可以发现，其avg的值相当高，在这种情况下，首先要做的是sql语句的优化，比较下面两个句子，后者的速度比前者要快得多。因为减少大量的连接。

[code]
insert into test values(aa,bb)
insert into test values(cc,dd)


insert into test values (aa),(bb),(cc),(dd)
[/code]

在我的一个实际应用中，由于需要经常有数百个并发的插入，我还采用了insert delayed into来取代insert into，前者与后者的区别是在执行插入语句时，数据保存在内存队列中，待数据库空闲时执行，但是会立即返回一个插入成功的信息。使用insert delayed into时需要注意：此时不能使用mysql_insert_id()，因为此时并没有真正插入。对特别重要的数据不宜采用该语句，避免数据以外丢失。

1.mysql myisam 表超过4G无法访问的解决

myisam引擎默认是支持4GB，innodb理论上可以到6TB，假设单张表容量超过4GB，可能导致表都无法访问了。可以通过以下命令增加表最大数据量：

[code]mysql> alter table user MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000;
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0[/code]

这样修改后数据文件可以支持到208TB左右。

ibbackup是针对innodb的备份工具，没备份mysql是很难恢复，至于易损坏，我还没有见过

看来得转移到原创区，新手们貌似不太感兴趣

曾经有几个人告诉我，说mysql数据库在大数量的时候容易损坏，而且无法恢复，我也不知道这是什么原因，也不知道是否属实。

辉老大多搞点实例，数据库优化是相当重要的一门技术！
非常感谢辉老大的文章！

我也是总是在MyISAM和inno间徘徊.....

听我们以前老大说:MySQL一个表超过4G,性能就会几何级下降,到现在也没见过.....

只接触过整个库6G的...没接触个单个表这么大的.....

下降是必然的，毕竟mysql针对的是中小型应用，且mysql的索引机制不是那么完善，大型应用还是采用oracle比较好

myisam引擎默认是支持4GB，innodb理论上可以到6TB，假设单张表容量超过4GB，可能导致表都无法访问了。可以通过以下命令增加表最大数据量：

[code]mysql> alter table user MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000;
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0[/code]

这样修改后数据文件可以支持到208TB左右。

多谢 辉老大，以后改用ibbackup 。 那次数据丢失,真是太惨了。

其实还可以试一下PostgreSQL
刚接触PHP那会，我用的就是
后来因为AMP平台容易搭建所有就习惯了MySQL
其实PostgreSQL相当不错

  支持辉老大，跟着学习一下

那玩意收费的，不过可以免费使用1个月。

不是不感兴趣，看了没敢回。冏。。。

请大家都能补充自己的经验来完善这个帖子，我会把大家的见解也编辑进去。顺便说下，为了占楼，删了部分网友的回帖，包括15的，不好意思哈~~~

http://blog.csdn.net/gprime/archive/2007/07/13/1687930.aspx
人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中（如联机事务处理OLTP或决策支持系统DSS）中表现得尤为明显。
笔者在工作实践中发现，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。
在对它们进行适当的优化后，其运行速度有了明显地提高！
下面我将从这三个方面分别进行总结：
为了更直观地说明问题，所有实例中的SQL运行时间均经过测试，不超过１秒的均表示为（< 1秒）。----
测试环境: 主机：HP LH II---- 主频：330MHZ---- 内存：128兆----
操作系统：Operserver5.0.4----
数据库：Sybase11.0.3

一、不合理的索引设计----
例：表record有620000行，试看在不同的索引下，下面几个 SQL的运行情况：
---- 1.在date上建有一非个群集索引
select count(*) from record where date >'19991201' and date < '19991214'and amount >2000 (25秒)
select date ,sum(amount) from record group by date(55秒)
select count(*) from record where date >'19990901' and place in ('BJ','SH') (27秒)
---- 分析：----
date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，在范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。
---- 2.在date上的一个群集索引
select count(*) from record where date >'19991201' and date < '19991214' and amount >2000 （14秒）
select date,sum(amount) from record group by date（28秒）
select count(*) from record where date >'19990901' and place in ('BJ','SH')（14秒）
---- 分析：---- 在群集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询速度。
---- 3.在place，date，amount上的组合索引
select count(*) from record where date >'19991201' and date < '19991214' and amount >2000 （26秒）
select date,sum(amount) from record group by date（27秒）
select count(*) from record where date >'19990901' and place in ('BJ, 'SH')（< 1秒）
---- 分析：---- 这是一个不很合理的组合索引，因为它的前导列是place，第一和第二条SQL没有引用place，因此也没有利用上索引；第三个SQL使用了place，且引用的所有列都包含在组合索引中，形成了索引覆盖，所以它的速度是非常快的。
---- 4.在date，place，amount上的组合索引
select count(*) from record where date >'19991201' and date < '19991214' and amount >2000(< 1秒)
select date,sum(amount) from record group by date（11秒）
select count(*) from record where date >'19990901' and place in ('BJ','SH')（< 1秒）
---- 分析：---- 这是一个合理的组合索引。它将date作为前导列，使每个SQL都可以利用索引，并且在第一和第三个SQL中形成了索引覆盖，因而性能达到了最优。
---- 5.总结：----
缺省情况下建立的索引是非群集索引，但有时它并不是最佳的；合理的索引设计要建立在对各种查询的分析和预测上。
一般来说：
①.有大量重复值、且经常有范围查询（between, >,< ，>=,< =）和order by、group by发生的列，可考虑建立群集索引；
②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；
③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。

二、不充份的连接条件：
例：表card有7896行，在card_no上有一个非聚集索引，表account有191122行，在account_no上有一个非聚集索引，试看在不同的表连接条件下，两个SQL的执行情况：
select sum(a.amount) from account a,card b where a.card_no = b.card_no（20秒）
select sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.account_no（< 1秒）
---- 分析：---- 在第一个连接条件下，最佳查询方案是将account作外层表，card作内层表，利用card上的索引，其I/O次数可由以下公式估算为：
外层表account上的22541页+（外层表account的191122行*内层表card上对应外层表第一行所要查找的3页）=595907次I/O
在第二个连接条件下，最佳查询方案是将card作外层表，account作内层表，利用account上的索引，其I/O次数可由以下公式估算为：外层表card上的1944页+（外层表card的7896行*内层表account上对应外层表每一行所要查找的4页）= 33528次I/O
可见，只有充份的连接条件，真正的最佳方案才会被执行。
总结：
1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。
2.查看执行方案的方法-- 用set showplanon，打开showplan选项，就可以看到连接顺序、使用何种索引的信息；想看更详细的信息，需用sa角色执行dbcc(3604,310,302)。

三、不可优化的where子句
1.例：下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢：
select * from record wheresubstring(card_no,1,4)='5378'(13秒)
select * from record whereamount/30< 1000（11秒）
select * from record whereconvert(char(10),date,112)='19991201'（10秒）
分析：
where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；
如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样：
select * from record where card_no like'5378%'（< 1秒）
select * from record where amount< 1000*30（< 1秒）
select * from record where date= '1999/12/01'（< 1秒）
你会发现SQL明显快起来！
2.例：表stuff有200000行，id_no上有非群集索引，请看下面这个SQL：
select count(*) from stuff where id_no in('0','1')（23秒）
分析：---- where条件中的'in'在逻辑上相当于'or'，所以语法分析器会将in ('0','1')转化为id_no ='0' or id_no='1'来执行。
我们期望它会根据每个or子句分别查找，再将结果相加，这样可以利用id_no上的索引；
但实际上（根据showplan）,它却采用了"OR策略"，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。因此，实际过程没有利用id_no上索引，并且完成时间还要受tempdb数据库性能的影响。
实践证明，表的行数越多，工作表的性能就越差，当stuff有620000行时，执行时间竟达到220秒！还不如将or子句分开：
select count(*) from stuff where id_no='0'select count(*) from stuff where id_no='1'
得到两个结果，再作一次加法合算。因为每句都使用了索引，执行时间只有3秒，在620000行下，时间也只有4秒。
或者，用更好的方法，写一个简单的存储过程：
create proc count_stuff asdeclare @a intdeclare @b intdeclare @c intdeclare @d char(10)beginselect @a=count(*) from stuff where id_no='0'select @b=count(*) from stuff where id_no='1'endselect @c=@a+@bselect @d=convert(char(10),@c)print @d
直接算出结果，执行时间同上面一样快！

---- 总结：---- 可见，所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销。
1.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
2.in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。
3.要善于使用存储过程，它使SQL变得更加灵活和高效。
从以上这些例子可以看出，SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

好文，学习，谢谢

顶了.  小辉同志讲的基本就是那么优化了. 我比较喜欢的就是 innodb 引擎拉.行锁定并发情况下会很不错.当然不能直接热备份有点郁闷需要通过工具来实现. 还有对于一个SQL 是否使用了索引.我们也应该使用explain 来看下.而不能单靠直接经验来加索引....

up

这么好的帖子不顶不行啊！

web开发中 数据库设计 数据库操作 数据库维护 是整个开发过程中占据最大部分的！

大量的逻辑操作 被 数据库代替执行。所以研究数据库执行的效率就成了现实问题！

而一个大项目中model的开发 都是围绕着数据库的设计

可以说一个项目数据库设计的成功应该就成功一大半了！

如果设计的再牛点 那就给系统维护升级提供大大的方便

发布....中...

真不错,学习了

学习了

还可以在插入数据前删除索引，插入后再重建索引（插入大量数据时效果很明显）

看完了~~~初学，还真没试过多数据同时插入~~~~~

顶   索引确实很重要。。。

索引那段能不能用图形做得形象一点,看得有点糊涂~

顶起来

收藏了

不错，顶一个

不错，学习了，留下脚印

好

很好的帖子

好文章，支持

其实mysql和oracle的根本区别只是在应用的领域上不同,mysql主要应用于非敏感性数据(互联网应用居多),oracle应用于大型敏感数据(比如银行,证券,交通运输).

综合比较,不论多少数据量,mysql在性能上时完全可以和oracle相抗衡的,目前我们公司大量的oracle服务器已经转为mysql,总数据量6个多TB,通过服务器分布处理,性能上非常好.目前来说,这个不是问题

最重要的是:企业看重的是运维成本,oracle除了贵还是贵,mysql平民专用

本帖最后由 la19850302 于 2009-10-12 16:48 编辑

总体来说,mysql调优是个系统级的问题,主要从下面三个层次全面的考虑

1 硬件层次:CPU,存储系统,内存,网络

2 服务器层次:操作系统,mysql软件的配置置

3 应用层次:应用程序的数据库构架和设计

由于涉及面非常广泛,并且已有众多前辈的金口留言,我就偷懒少写一些,下面这篇文档比较完整的介绍mysql优化,极为实用

另外,优化是一个逐步深入的过程,由于不同应用领域的业务规则不同,优化往往需要长期的测试,调整,才可能达到最佳的状态,所以优化最重要的是要理解优化的原则,不能按照别人的案例照搬.

真是好贴啊