MySQL常识

MySQL可以说是程序员应用最多的数据库，下面笔者为大家分享MySQL数据库开发当中的一些常识，存储引擎的选择，索引的设计及使用和大批量插入时SQL语句的优化。希望能对大家有帮助。

 

 

 

原文地址：http://www.cnblogs.com/zzbbs/archive/2011/02/22/1961743.html

　　存储引擎的选择

　　声明：本文所针对的数据库版本都是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.联合索引的左前缀

　　先看索引结构：

以下是代码片段：
　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)

　　

user是联合索引的名称，包含3个列，分别是username,order,email。接下来执行以下sql，使用explain命令来分析下运行结果。

 

以下是代码片段：
　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)

　　在两句sql中，我们可以发现，第一个sql虽然没用上，全部的索引列，但由于使用到了最左端的列，所以，联合索引还是启用了，第二句没有使用到最左的列，所以索引没有使用。

 

2.关于like关键字

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

以下是代码片段：
　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)

 　　3. 查看索引使用情况

　　使用以下命令：

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

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

 

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

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

　　复制内容到剪贴板代码：

以下是代码片段：
　insert　into　test　values(aa,bb) 
　　insert　into　test　values(cc,dd) 
　　insert　into　test　values　(aa),(bb),(cc),(dd)

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

　　其他杂谈

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

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

以下是代码片段：
　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