Chinaunix首页 | 论坛 | 博客
  • 博客访问: 954517
  • 博文数量: 128
  • 博客积分: 10012
  • 博客等级: 上将
  • 技术积分: 2050
  • 用 户 组: 普通用户
  • 注册时间: 2008-10-15 17:49
文章分类

全部博文(128)

文章存档

2011年(16)

2009年(57)

2008年(55)

分类: 数据库开发技术

2011-07-01 21:44:50

我在最近的几个项目中深刻理解到,其实项目应用的瓶颈还是在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
阅读(3782) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~