[导入]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.联合索引的左前缀

先看索引结构:

复制内容到剪贴板

代码:


        
          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

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

bestmost 2009-02-23 13:33 发表评论

文章来源: http://www.phpweblog.net/fuyongjie/archive/2009/02/23/6344.html

[导入]MYSQL开发实用知识集合

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义