`

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

阅读更多

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

原文地址:http://hakering.iteye.com/blog/918833

               http://www.cnblogs.com/zzbbs/archive/2011/02/22/1961743.html

分享到:
评论
1 楼 褚晓娜(0511) 2011-02-24  
不错 谢谢收藏啦

相关推荐

    mysql面试题-后端数据库程序员必备-前端框架-大厂面试准备.docx

    内容概要:是一款后端程序员mysql面试题,word格式,直接打开便可以学习使用,里面有大约54题的面试题,每个题都是问题加回答,可以方便大家进行学习。虽说不可能全面,但也可以作为一个学习索引,大家可以根据自己...

    mysql数据库必备技能,对于新手来说最合适不过了

    数据库的操作是每个程序员必须学会的东西,不论是新手面试还是实际开发中都不可避免的问题。这个资源中包含了很多种的例子,有助于更加便捷的学习。还包含一个sql导入文件,可使用SQLyogE或者navicat等数据库工具来...

    高薪程序员必备刷题软件-mysql_doc:mysql_doc

    第2章:mysql数据库面试必备入门基础知识 简介:主讲面试时,面试官必问的知识点,主要关于版本号,服务进程,有哪些sql操作语句等 2.1 mysql各个版本的重要性介绍 **简介:各个版本的区别** 官网:...

    Mac必备免费好用之 sql数据库

    Sequel Pro是一个快速,易于使用的Mac数据库管理应用程序,用于处理MySQL数据库。 完美的WEB开发伴侣 无论您是Mac Web开发人员,程序员还是软件开发人员,您的工作流程都将与本机Mac OS X应用程序简化! 灵活的...

    MySql学习建议以及安装全详解,初学数据库及安装小白必备!

    初学数据库及安装小白,数据库安装需要的程序员

    必会神器之MySQL数据库

    MySQL是开放源代码的 由于开源免费强大等特点 深受公司的喜爱 所以也是作为程序员必须所掌握的数据库之一 也是面试必备的技能 本视频lucky老师会带领小伙伴从数据库的认识到库、表的创建以及增删改查到和权限、...

    初级程序员必备2020最新MYSQL面试题1

    MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,...

    简单修改32位、16位数据库对应密码表

    程序员,新手必备,数据库32位、16位对应密码表,修改代码即可修改密码

    Java程序员面试题全.zip

    Java程序员面试题全(包含23种设计模式知识要点整理、Dubbo、Dubbo服务框架面试专题及答案整理文档、java筑基(基础)面试专题系列(二):并发+Netty+JVM、java筑基(基础)面试专题系列(一):Tomcat+Mysql+设计...

    HeimaCodeUtil_V2.4 黑马程序员代码生成器

    非常好用的代码生成器,最新版,传智播客出品,支持多种代码...ssm+dubbox+angularjs等多种模板的代码自动生成,可以极大提高开发效率,秒杀一切单表操作,支持oracle+mysql多数据库,开发人员必备工具,现提供给广大开发人员

    程序员必备手册CSS参考手册3.0.chm,HTML5 参考手册.chm,MySQL 5.1参考手册.chm,ORACLE 九阴真经

    CSS参考手册3.0.chm,HTML5 参考手册.chm,MySQL 5.1参考手册.chm,ORACLE 九阴真经.chm,oracle命令速查.chm,正则表达式系统教程.CHM,织梦《正则表达式》.chm

    跟兄弟连学PHP.part2.rar

    内容涵盖了动态网站开发的前端技术(HTML5)、企业现在主流应用版本PHP 5.4为主的语法、PHP的常用功能模块和实用技巧、MySQL数据库的设计与应用、PHP 面向对象的程序设计思想、数据库抽象层PDO、Smarty模板技术、Web...

    最全的Java程序员知识(专业知识+面试+视频教程)学习资料合集.zip

    最全的Java程序员知识学习资料合集,共20+专题。 开源框架面试题系列:Spring+SpringMVC+MyBatis 分布式限流面试专题系列:Nginx+zookeeper 分布式通讯面试专题系列:ActiveMQ+RabbitMQ+Kafka 分布式数据库面试专题...

    细说PHP(第2版)

    内容涵盖了PHP的运行环境搭建、Web服务器Apache的配置与应用、动态网站开发的前台技术、PHP编程语言的语法、PHP的常用功能模块和实用技巧、MySQL数据库的设计与应用、PHP 5面向对象的程序设计思想、Web开发的设计...

    跟兄弟连学PHP.pdf下载地址

    内容涵盖了动态网站开发的前端技术(HTML5)、企业现在主流应用版本PHP 5.4为主的语法、PHP的常用功能模块和实用技巧、MySQL数据库的设计与应用、PHP 面向对象的程序设计思想、数据库抽象层PDO、Smarty模板技术、Web...

    学通PHP的24堂课

    主要包括php概述、php基础、php函数、php流程控制语句、php数组应用、php与web页面交互、mysql数据库技术、php数据库编程技术、cookie与会话控制、字符串高级处理技术、日期和时间的处理技术、图形图像处理技术、...

    高性能MySQL实战课

    使用MySQL解决大量数据以及高并发请求已经是程序员的必备技能,也是衡量一个程序员能力和薪资的标准之一。 为了让大家快速系统了解高性能MySQL核心知识全貌,我为你总结了「高性能 MySQL 知识框架图」,帮你梳理学习...

Global site tag (gtag.js) - Google Analytics