你的位置:首页 > 数据库

[数据库]优化MYSQL数据库的方法


1、选取最适用的字段属性

  尽可能减少定义字段长度,尽量把字段设置NOT NULL,例如'省份,性别',最好设置为ENUM 

2、使用连接(JOIN)来代替子查询: 

   a、删除没有任何订单客户  

  ELETE FROM customerinfo WHERE customerid NOT in(SELECT customerid FROM orderinfo)

   b、提取所有没有订单客户  

  SELECT FROM customerinfo WHERE customerid NOT in(SELECT customerid FROM orderinfo) 

   c、提高b的速度优化      

  SELECT FROM customerinfo LEFT JOIN orderid customerinfo.customerid=orderinfo.customerid  WHERE orderinfo.customerid IS NULL 

3、使用联合(UNION)来代替手动创建的临时表 

  创建临时表:  

  SELECT name FROM `nametest` UNION SELECT username FROM `nametest2` 

4、事务处理: 

  保证数据完整性,例如添加和修改同时,两者成立则都执行,一者失败都失败 

  mysql_query("BEGIN"); 

  mysql_query("INSERT INTO customerinfo (name) VALUES ('$name1')"; 

  mysql_query("SELECT * FROM `orderinfo` where customerid=".$id"); 

  mysql_query("COMMIT"); 

5、锁定表,优化事务处理  

  我们用一个 SELECT 语句取出初始数据,通过一些计算,用 UPDATE 语句将新值更新到表中。

  包含有 WRITE 关键字的 LOCK TABLE 语句可以保证在 UNLOCK TABLES 命令被执行之前,不会有其它的访问来对 inventory 进行插入、更新或者删除的操作 

  mysql_query("LOCK TABLE customerinfo READ, orderinfo WRITE"); 

  mysql_query("SELECT customerid FROM `customerinfo` where id=".$id); 

  mysql_query("UPDATE `orderinfo` SET ordertitle='$title' where customerid=".$id); 

  mysql_query("UNLOCK TABLES"); 

6、使用外键,优化锁定表 

  把customerinfo里的customerid映射到orderinfo里的customerid, 任何一条没有合法的customerid的记录不会写到orderinfo里 

  CREATE TABLE customerinfo   ( 

       customerid INT NOT NULL, 

       PRIMARY KEY(customerid)   

  )TYPE = INNODB; 

  CREATE TABLE orderinfo 
   ( 
    orderid INT NOT NULL, 

    customerid INT NOT NULL, 

    PRIMARY KEY(customerid,orderid), 

    FOREIGN KEY (customerid) REFERENCES customerinfo 

    (customerid) ON DELETE CASCADE    

  )TYPE = INNODB; 

  注意:'ON DELETE CASCADE',该参数保证当customerinfo表中的一条记录删除的话同时也会删除order 表中的该用户的所有记录,注意使用外键要定义事务安全类型为INNODB; 

7、建立索引: 

  a、(普通索引)-> 

  创建:CREATE INDEX <索引名> ON tablename (索引字段) 

  修改:ALTER TABLE tablename ADD INDEX [索引名] (索引字段) 

  创表指定索引:CREATE TABLE tablename([...],INDEX[索引名](索引字段))  

  b、(唯一索引)-> 

  创建:CREATE UNIQUE <索引名> ON tablename (索引字段) 

  修改:ALTER TABLE tablename ADD UNIQUE [索引名] (索引字段) 

  创表指定索引:CREATE TABLE tablename([...],UNIQUE[索引名](索引字段))  

  c、(主键索引)-> 

  它是唯一索引,一般在创建表是建立,格式为: 

  CREATA TABLE tablename ([...],PRIMARY KEY[索引字段]) 

8、优化查询语句 

  最好在相同字段进行比较操作,在建立好的索引字段上尽量减少函数操作 

  a、例子1: 

  SELECT * FROM order WHERE YEAR(orderDate)<2008;(慢) 

  SELECT * FROM order WHERE orderDate<"2008-01-01";(快) 

  b、例子2: 

  SELECT * FROM order WHERE addtime/7<24;(慢) 

  SELECT * FROM order WHERE addtime<24*7;(快) 

  c、例子3: 

  SELECT * FROM order WHERE title like "%good%"; 

  SELECT * FROM order WHERE title>="good" and name<"good"; 

9、MySQL函数小结

  a、MYSQL取得当前时间的函数是?

  now()

  b、格式化日期的函数是?

  date() 

  c、自动 插入当前时间 ?

  将列属性设置为 “TIMESTAMP”,设置其 default 为 “CURRENT_TIMESTAMP”。但是其只对“TIMESTAMP"有效。

  d、mysql_fetch_row() 和mysql_fetch_array之间有什么区别?

  mysql_fetch_row是从结果集取出1行数组,作为枚举 

  mysql_fetch_array是从结果集取出一行数组作为关联数组,或数字数组,两者兼得 

  e、取得查询结果集总数的函数是?

  mysql_num_rows($result);

10、写出三种以上MySQL数据库存储引擎的名称

  MyISAM、InnoDB、BDB(Berkeley DB)、Merge、Memory(Heap)、Example、Federated、Archive、CSV、Blackhole、MaxDB 等等十几个引擎

11、MySQL数据库基本的三个优化法则是什么,除了增加硬件和带宽?

  (提示:从服务配置、应用、开发角度考虑)

  (1)系统服务优化,把MySQL的key_buffer、cache_buffer、query_cache等增加容量

  (2)给所有经常查询的字段增加适当的索引

  (3)优化SQL语句,减少Ditinct、Group、Join等等语句的操作

12、优化MYSQL数据库的方法

  (1).选取最适用的字段属性,应该尽量把字段设置为NOT NULL,这样在将来执行查询的时候,数据库不用去比较NULL值。

  (2).使用连接(JOIN)来代替子查询(Sub-Queries)

  (3).使用联合(UNION)来代替手动创建的临时表

  (4).尽量少使用 LIKE 关键字和通配符

  (5).使用事务和外键

13、对于大流量的网站,采用什么样的方法来解决访问量问题?

  首先,确认服务器硬件是否足够支持当前的流量

  其次,优化数据库访问。

  第三,禁止外部的盗链。

  第四,控制大文件的下载。

  第五,使用不同主机分流主要流量

  第六,使用流量分析统计软件。

14、请简述数据库设计的范式及应用

  一般第3范式就足以,用于表结构的优化,这样做既可以避免应用程序过于复杂同时也避免了SQL语句过于庞大所造成系统效率低下。

  第一范式:若关系模式R的每一个属性是不可再分解的,再属于第一范式。

  第二范式:若R属于第一范式,且所有的非码属性都完全函数依赖于码属性,则为第二范式。

  第三范式:若R属于第二范式,且所有的非码属性没有一个是传递函数依赖于候选码,则属于第三范式。

15、连接数据库操作的步骤是什么?每一步的返回值是什么数据类型?

  $coon = mysql_connect('localhost', 'root' ,''); //返回资源类型

  mysql_select_db('bbs', $coon);        // 布尔类型

  mysq_query('set names utf8');  //布尔类型

  $qry = mysql_query('select * from bbsinfo');  // 资源类型

  mysql_fetch_row($qry);        // 数组类型