点击这里给我发消息 
  设为首页 | 加入收藏 | 网站地图
联系我们
北京总部调度中心
具体地址:北京市海淀区知春路23号量子银座(863软件园)8层802
电话:010-51530012/13
传真:010-51530013
24小时服务热线:13501016950
长途客户:4006-355-119
数据恢复项目
监控录像数据恢复
监控录像数据恢复,大华监控录像恢复,海康监控录像恢复,汉邦高科监控录像恢复
数据库修复
ACCESS、FOXPRO、SQL SERVER、 ORACLE、MYSQL、DB2、NFORMIX、 SYBASE等数据库丢失的恢复
硬盘数据恢复
希捷、日立、IBM、迈拓、三星、 西部数据、昆腾、富士通、东芝、易拓等各种硬盘的数据恢复
RAID数据恢复
RAID0、RAID1、RAID5、RAID6、RAID1+0、RAID0+1.硬盘掉线导致RAID信息丢失、误删除数据、RAID信息丢失、服务器硬盘损坏导致无法读取数据
开盘数据恢复
硬盘磁头卡死、硬盘电机问题、硬盘读取无反应、硬盘内部组件问题、硬盘磁头偏移、硬盘磁头等等相关问题。
存储介质恢复
U盘、光盘、软盘、MP3、MP4、CF、SD、xD、MMC、SM、SMC、记忆棒、录音笔、磁带、微硬盘、硬盘、等。
当前位置:首页 > 技术交流

程序员基础 MySQL数据库开发必备常识

来源: 发布时间:2012-2-22 点击量:   【返回

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

MySQL可以说是程序员应用最多的数据库,下面笔者为大家分享MySQL数据库开发当中的一些常识,存储引擎的选择,索引的设计及使用和大批量插入时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
 


上一条: 数据库故障oracle与防火墙 | 下一条: SQL Server 2000数据库管理中的维护计划
最新文章 
· 服务器虚拟化带来的六大存储瓶颈 6-5
· 服务器虚拟化的经验未必适合桌面… 6-5
· 服务器虚拟化怎样选择具体实施方… 6-5
· 选购虚拟化服务器的五大注意事项 6-5
· 如何创建虚拟机备份并避免一些常… 6-5
· 嵌入云服务器:如何控制服务器升… 6-5
· 云时代:政府CIO应该“未雨绸缪”… 6-5
· 存储虚拟化和服务器虚拟化紧密相… 5-31
相关文章
· 西数的微博开通了! 2-20
· 关注微博,有好礼相送! 2-20
· 西数科技培训 2-22
· 西数科技的培训课程表 2-22
· R-Studio5.0 2-22
· 聚焦MySQL性能优化 空间数据库开… 2-22
· SQL Server 2000数据库管理中的维… 2-22
· 程序员基础 MySQL数据库开发必备… 2-22
 
友情链接: 北京服务器数据恢复 | 数据安全论坛 | 重庆数据恢复 | 天津数据恢复 | 监控录像数据恢复 | 数据恢复实验室 | 保定分类信息网 | 南京数据恢复 | 南京硬盘恢复 | 数据恢复 | 电子取证 | U盘数据恢复 | 西数数据恢复博客 | 昆明数据恢复 |
版权所有:北京西数科技有限公司 Copyright @ 2003-2012 All rights reserved 京ICP备10217323号
公司地址:北京市海淀区知春路23号量子银座(863软件园)8层802 | 邮编:230031 1024*768显示最佳
公司电话:010-51530012 /13 13501016950