标签归档:mysql

mysql查询语句优化

多多利用 "explain" 查询索引使用情况, 以便找出最佳的查询语句写法和索引设置方案
慎用 "select *", 查询时只选出必须字段
查询使用索引时, 所遍历的索引条数越少, 索引字段长度越小, 查询效率越高 (可使用 "explain" 查询索引使用情况)
避免使用 函数对查询结果进行处理, 将这些处理交给客户端程序负责
使用 "limit" 时候, 尽量使 "limit" 出的部分位于整个结果集的前部, 这样的查询速度更快, 系统资源开销更低
在 "where" 子句中使用多个字段的 "and" 条件时, 各个字段出现的先后顺序要与多字段索引中的顺序相符
在 "where" 子句 中使用 "like" 时, 只有当通配符不出现在条件的最左端时才会使用索引
在 mysql 4.1 以上版本中, 避免使用子查询, 尽量使用 "内/外连接" 实现此功能
减少函数的使用, 如果可能的话, 尽量用单纯的表达式来代替
避免在 "where" 子句中, 对不同字段进行 "or" 条件查询, 将其拆分成多个单一字段的查询语句效率更高

从数据库结构做起
字段类型的定义时遵循以下规则:
选用字段长度最小
优先使用定长型
尽可能的定义 "NOT NULL"
数值型字段中避免使用 "ZEROFILL"
如果要储存的数据为字符串, 且可能值已知且有限, 优先使用 enum 或 set
索引的优化至关重要(以下如果没有特殊说明, 均指查询密集的情况)
被索引的字段的长度越小, 该索引的效率越高
被索引的字段中, 值的重复越少, 该索引的效率越高
查询语句中, 如果使用了 "group" 子句, 根据其中字段出现的先后顺序建立多字段索引
查询语句中, 如果使用了 "distinct", 根据其中字段出现的先后顺序建立多字段索引
"where" 子句中, 出现对同一表中多个不同字段的 "and" 条件时, 按照字段出现的先后顺序建立多字段索引
"where" 子句中, 出现对同一表中多个不同字段的 "or" 条件时, 对重复值最少的字段建立单字段索引
进行 "内/外连接" 查询时, 对 "连接字段" 建立索引
对 "主键" 的 "unique" 索引 毫无意义, 不要使用
被索引字段尽可能的使用 "NOT NULL" 属性
对写入密集型表, 尽量减少索引, 尤其是 "多字段索引" 和 "unique" 索引

mysql模糊查询sql语句

   $sql="select * from table_name where field_name like '%$var%'";

  ·希望通过“标题”对新闻库进行检索,关键字可能包含是中英文,如

  下 SQL 语句:

  select id,title,name from achech_com.news where title like '%a%'

  返回的结果,某些 title 字段确定带了“a”关键字,而有些则只有中文,

  但也随之返回在检索结果中。

  解决方法,使用 BINARY 属性(二进制进行检索,如:

  select id,title,name from achech_com.news where binary title like '%a%'

  返回的结果较之前正确,但英文字母区分大小写,故有时在检索如“Achech

  及“achech”的结果是不一样的。

  知道了使用 BINARY 属性可以解决前面这个问题,再看看 支持的

  UCASE 及 CONCAT 函数,其中 UCASE 是将英文全部转成大写,而 CONCAT 函

  数的作用是对字符进行连接,以下是我们完全解决后的 SQL 语句:

  select id,title,name from achech_com.news where binary ucase(title) like concat('%',ucase('a'),'%')

  检索的步骤是先将属性指定为 BINARY ,以精确检索结果,而被 like 的 title

  内容存在大小写字母的可能,故先使用 ucase 函数将字段内容全部转换成大

  写字母,然后再进行 like 操作,而 like 的操作使用模糊方法,使用 concat

  的好处是传进来的可以是直接的关键字,不需要带“%”万用符,将“'a'”直接

  换成你的变量,在任何语言下都万事无忧了。

  当然你也可以这么写:

  select id,title,name from achech_com.news where binary ucase(title) like ucase('%a%')

  检索的结果还算满意吧,不过速度可能会因此而慢N毫秒喔。

上面方法是网上找来的,可能以后要用到,这里做个记录。。。

解决MYSQL字符乱码,将字符转换为 UTF-8,gbk

要将字符写入为  UTF-8 使用下面方法

$_mylink = _connect($_host, $_user, $_pass);
mysql_query("SET NAMES 'UTF-8'");
 

数据库字符集为utf-8
mysql_query("SET NAMES 'UTF8'");
mysql_query("SET CHARACTER SET UTF8");
mysql_query("SET CHARACTER_SET_RESULTS=UTF8'");

$db->query("SET NAMES UTF8");

每天自动备份MYSQL数据库(dh空间)

Getting a Backup From the ShellYou can use the mysqldump shell command to make a backup, also. This is more difficult to do the first time than the phpMyAdmin method above, but you can automate it so that the system backs up your database for you. See Automatic Backup for instructions on how to automate this.

  1. Log into your old server with SSH or Telnet
  2. Type the following command:

mysqldump –opt -uusername -ppassword -h yourMySQLHostname dbname > output.sql

  1. Log into your account with FTP and download the newly created output.sql file.

在查找“用SSH备份mysql”时,发现的——“Dreamhost下每天自动备份mysql数据库,并发送到指定邮箱”。
觉得比wordpress的一些备份plugin好,决定启用。

1、用putty登录你的dreamhost;

2、运行

<LI class=hl-firstline>cd ~
mkdir backups
mkdir backups/archives

3、新建一个名为“.sh”的文件,并输入下列代码:

<LI class=hl-firstline>#!/bin/bash
cd /home/username/backups/ #username改为你登录ssh时用的用户名
mkdirmysql
suffix=$(date +%y%m%d)
mysqldump –opt -uUser -pPass -hmysqlA.domain.comdb_nameA > mysql/db_nameA.$suffix.sql#User为数据库用户名,pass为数据库用户密码,mysqlA.domain.com为数据库地址,db_nameA为数据库名,请根据你的情况修改。
tarzcfarchives/mysql_backup.$suffix.tar.gzmysql/*
rm -rmysql/
muttyou@domain.com -a /home/username/backups/archives/mysql_backup.$suffix.tar -s“MySQL Backup“#you@domain.com修改为你准备用于收取备份压缩包的邮箱。
保存后,上传到backups文件夹中。

4、在putty中运行

<LI class=hl-firstline>dos2unix ~/backups/mysql.sh
chmod 755 ~/backups/mysql.sh

5、在Dreamhost的panel,Goodies->Cron Jobs中新建Cron任务,选项设置为
Command to run: /home/User/backups/mysql.sh
#User改为你登录SSH时用的用户名。

时间照下图设置,将在北京时间每天的24:10左右运行备份MySQL程序,并发送备份压缩包到你指定的邮箱。

open_img('attachments/200802/3734208507.jpg')

dreamhost(DH)空间MYSQL数据库备份方法

如何利用SSH(Shell)来备份和恢复MySQL数据库的方法
例如:
数据库参数为::
MySQL地址:.dh.net
MySQL名称:mysql_dbname
MySQL用户:mysql_dbuser
MySQL密码:mysql_dbpass

我要把数据库备份为bak.sql

步骤:
同样,使用windows系统自己带的telnet或者去下载一个putty来,登陆以后,一路cd到自己觉得合适的目录下(确认当前目录可写).
输入下面的命令:
mysqldump -h mysql.dh.net -p mysql_dbname -u mysql_dbuser >bak.sql
然后回车,提示你输入数据库登陆密码,输入完密码后回车,OK! 就把数据库备份到当前目录下了

如何恢复bak.sql数据库:::
输入下面命令后回车
mysql -h mysql.dh.net -p mysql_dbname -u mysql_dbuser
提示你输入密码,输入密码后然后回车
会出现MySQL操作提示符号
然后输入下面的命令
source bak.sql
请先确认bak.sql这个文件再当前目录下
回车.OK,恢复ing…..

Sun收购数据库MySQL

 

【赛迪网讯】1月17日消息,据国外媒体报道,Sun本周三宣布,将以10亿美元并购开源软件开发商MySQL。

MySQL是一家瑞典开源数据库软件开发商,其用户遍及全球,其中包括著名搜索引擎Google,社交网站Faebook,以及全球最大手机制造商诺基亚。

在这10亿美元中,8亿以现金形式支付,另外2亿美元采取股权支付。Sun表示,此举有助于将MySQL的数据库软件普及到更多的大企业用户中。

据预计,这笔交易有望于今年第三或第四季度完成。与此同时,Sun还在另一份声明中称,第二季度利润可能无法达到华尔街的预期。

据Sun预计,第二季度净收入将达到2.3亿至2.65亿美元,相当于每股28美分至32美分,低于华尔街所预期的22-38美分。

MySQL 数据库同步镜像功能

MySQL数据同步主要有三种方式:
1.利用MySQL自身的数据库同步功能
2.利用MySQL数据库的特性(数据库存在固顶目录,并且以文件形式存储),进行数据库目录同步以达到数据同步目的
3.利用专用的MySQL数据库同步软件

1.利用MySQL自身的数据库同步功能(下面参考自网上的文章,写的非常详细了)
MySQL从3.23.15版本以后提供数据库复制功能。利用该功能可以实现两个数据库同步,主从模式,互相备份模式的功能.

数据库同步复制功能的设置都在mysql的设置文件中体现。mysql的配置文件(一般是my.cnf),在unix环境下在/etc//my.cnf或者在mysql用户的home目录下的my.cnf。

windows环境中,如果c:根目录下有my.cnf文件则取该配置文件。当运行mysql的winmysqladmin.exe工具时候,该工具会把c:根目录下的my.cnf命名为mycnf.bak。并在winnt目录下创建my.ini。mysql服务器启动时候会读该配置文件。所以可以把my.cnf中的内容拷贝到my.ini文件中,用my.ini文件作为mysql服务器的配置文件。

  设置方法:
  设置范例环境:
  操作系统:window2000professional
  mysql:4.0.4-beta-max-nt-log
  Aip:10.10.10.22
  Bip:10.10.10.53
  A:设置
  1.增加一个用户最为同步的用户帐号:
  GRANTFILEON*.*TObackup@'10.10.10.53'IDENTIFIEDBY‘1234’
  2.增加一个数据库作为同步数据库:
  createdatabasebackup
  B:设置
  1.增加一个用户最为同步的用户帐号:
  GRANTFILEON*.*TObackup@'10.10.10.22'IDENTIFIEDBY‘1234’
  2.增加一个数据库作为同步数据库:
  createdatabasebackup
  主从模式:A->B
  A为master
  修改Amysql的my.ini文件。在mysqld配置项中加入下面配置:
  server-id=1
  log-bin
  #设置需要记录log可以设置log-bin=c:mysqlbakmysqllog设置日志文件的目录,
  #其中mysqllog是日志文件的名称,mysql将建立不同扩展名,文件名为mysqllog的几个日志文件。
  binlog-do-db=backup#指定需要日志的数据库
  重起数据库服务。
  用showmasterstatus命令看日志情况。
  B为slave
  修改Bmysql的my.ini文件。在mysqld配置项中加入下面配置:
  server-id=2
  master-host=10.10.10.22
  master-user=backup#同步用户帐号
  master-password=1234
  master-port=3306
  master-connect-retry=60预设重试间隔60秒
  replicate-do-db=backup告诉slave只做backup数据库的更新
  重起数据库
  用showslavestatus看同步配置情况。
  注意:由于设置了slave的配置信息,mysql在数据库目录下生成master.info
  所以如有要修改相关slave的配置要先删除该文件。否则修改的配置不能生效。
  双机互备模式。
  如果在A加入slave设置,在B加入master设置,则可以做B->A的同步。
  在A的配置文件中mysqld配置项加入以下设置:
  master-host=10.10.10.53
  master-user=backup
  master-password=1234
  replicate-do-db=backup
  master-connect-retry=10
  在B的配置文件中mysqld配置项加入以下设置:
  log-bin=c:mysqllogmysqllog
  binlog-do-db=backup
  注意:当有错误产生时*.err日志文件。同步的线程退出,当纠正错误后要让同步机制进行工作,运行slavestart
  重起AB机器,则可以实现双向的热备。
  测试:
  向B批量插入大数据量表AA(1872000)条
  A数据库每秒钟可以更新2500条数据。

2.数据库目录同步,方法和文件同步一样,设置好需要同步的两个数据库目录就可以了!
缺点很明显,数据同步只能单向进行,可以作为备份方案

3.用专用的MySQL同步软件进行同步
这方面的软件有SQLBalance和MyReplicator,优点是方便直观,还有很多争强功能!
缺点和2一样,只能单项同步!
当然你也可以修改镜像网站的程序为提交数据到母数据库,读取则在当前镜像下的数据,不过,修改起来麻烦!普通用户修改也非常难!呵呵,大家了解一下就可以!给大家一个思路!有能力的朋友可以试试阿!

4.关于MySQL论坛的数据同步
由于数据来源的不可控制(不好表达),论坛数据是实时的,而且还要考虑来自镜像论坛的数据,如何实现镜像论坛与母论坛数据同步呢?