提高mysql性能的方法!
提高mysql性能的方法! 来自：chinaunix 作者：uunguymadman007风云使者 一、问题的提出在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。二、SQL语句编写注意问题下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。1. IS NULL 与 IS NOT NULL不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。2. 联接列对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有一个职工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个叫比尔.克林顿（Bill Cliton）的职工。下面是一个采用联接查询的SQL语句，select * from employsswherefirst_name||”||last_name =’Beill Cliton’ 上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没有使用。当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。Select * from employeewherefirst_name =’Beill’ and last_name =’Cliton’ 遇到下面这种情况又如何处理呢？如果一个变量（name）中存放着Bill Cliton这个员工的姓名，对于这种情况我们又如何避免全程遍历，使用索引呢？可以使用一个函数，将变量name中的姓和名分开就可以了，但是有一点需要注意，这个函数是不能作用在索引列上。下面是SQL查询脚本：select * from employeewherefirst_name = SUBSTR(‘&&name’,1,INSTR(‘&&name’,’ ‘)-1)andlast_name = SUBSTR(‘&&name’,INSTR(‘&&name’,’ ‘)+1) 3. 带通配符（%）的like语句同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句：select * from employee where last_name like ‘%cliton%’ 这里由于通配符（%）在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：select * from employee where last_name like […]
MySQL-Cluster集群研究
MySQL-Cluster集群研究 from: discuz wiki一、介绍这篇文档旨在介绍如何安装配置基于2台服务器的MySQL集群。并且实现任意一台服务器出现问题或宕机时MySQL依然能够继续运行。 注意！ 虽然这是基于2台服务器的MySQL集群，但也必须有额外的第三台服务器作为管理节点，但这台服务器可以在集群启动完成后关闭。同时需要注意的是并不推荐在集群启动完成后关闭作为管理节点的服务器。尽管理论上可以建立基于只有2台服务器的MySQL集群，但是这样的架构，一旦一台服务器宕机之后集群就无法继续正常工作了，这样也就失去了集群的意义了。出于这个原因，就需要有第三台服务器作为管理节点运行。 另外，可能很多朋友都没有3台服务器的实际环境，可以考虑在VMWare或其他虚拟机中进行实验。 下面假设这3台服务的情况： Server1: mysql1.vmtest.net 192.168.0.1Server2: mysql2.vmtest.net 192.168.0.2Server3: mysql3.vmtest.net 192.168.0.3 Servers1和Server2作为实际配置MySQL集群的服务器。对于作为管理节点的Server3则要求较低，只需对Server3的系统进行很小的调整并且无需安装MySQL，Server3可以使用一台配置较低的计算机并且可以在Server3同时运行其他服务。二、在Server1和Server2上安装MySQL从http://www.mysql.com上下载mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz 注意：必须是max版本的MySQL，Standard版本不支持集群部署！ 以下步骤需要在Server1和Server2上各做一次 # mv mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz /usr/local/# cd /usr/local/# groupadd mysql# useradd -g mysql mysql# tar -zxvf mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz# rm -f mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz# mv mysql-max-4.1.9-pc-linux-gnu-i686 mysql# cd mysql# scripts/mysql_install_db –user=mysql# chown -R root .# chown -R mysql data# chgrp -R mysql .# cp […]
Your First Groovy
Your First Groovy //hello.groovy println “hello, world” for (arg in this.args ) { println “Argument:” + arg; } // this is a comment /* a block comment, commenting out an alternative to above: this.args.each{ arg -> println “hello, ${arg}”} */ To run it from command line groovy hello.groovy MyName yourName HisName Overview Groovy classes compile […]
install Groovy in RedHat as4
官方网是这样写的：1,first, Download a binary distribution of Groovy and unpack it into some file on your local file system 2,set your GROOVY_HOME environment variable to the directory you unpacked the distribution 3,add GROOVY_HOME/bin to your PATH environment variable 4,set your JAVA_HOME environment variable to point to your JDK. On OS X this is /Library/Java/Home, on other […]
mysqlbinlog 資料庫處理二進制日誌檔案的實用工具
當然，系統剛弄好是沒有開啟 mysqlbinlog 的功能，至少在 CentOS4.4 上面我還要去開啟，不過 ubuntu 系統預設就已經開啟了，不過沒關係，只要利用下面方法就可以達到了 修改 my.cnf [ CentOS: /etc/my.cnf Ubuntu: /etc/mysql/my.cnf ]，加入下面語法 # Replication Master Server (default)# binary logging is required for replicationlog-BIN=mysql-BIN 不過話說當你開啟這個功能之後，你會發現在 /var/lib/mysql/ 底下多出很多檔案 -rw-rw—- 1 mysql mysql 33164904 1月 17 15:44 mysql-bin.000001-rw-rw—- 1 mysql mysql 4007 1月 17 15:50 mysql-bin.000002-rw-rw—- 1 mysql mysql 70288989 1月 29 22:38 […]
Timer Server
import java.io.InterruptedIOException;import javax.microedition.io.Connector;import javax.microedition.io.Datagram;import javax.microedition.io.DatagramConnection;import javax.microedition.lcdui.Alert;import javax.microedition.lcdui.AlertType;import javax.microedition.lcdui.Command;import javax.microedition.lcdui.CommandListener;import javax.microedition.lcdui.Display;import javax.microedition.lcdui.Displayable;import javax.microedition.lcdui.Form;import javax.microedition.lcdui.StringItem;import javax.microedition.lcdui.TextField;import javax.microedition.midlet.MIDlet;import javax.microedition.midlet.MIDletStateChangeException;public class TimeMIDlet extends MIDlet implements CommandListener, Runnable { private Display display; private Form addressForm; private Form connectForm; private Form displayForm; private Command backCommand; private Command exitCommand; private Command okCommand; private StringItem messageLabel; private TextField serverName; protected void startApp() throws MIDletStateChangeException { if (display == null) { initialize(); display.setCurrent(addressForm); } } protected void pauseApp() { } protected void destroyApp(boolean unconditional) throws MIDletStateChangeException { } public void commandAction(Command cmd, Displayable d) { if (cmd == okCommand) { Thread t = new Thread(this); t.start(); } else if (cmd == backCommand) { display.setCurrent(addressForm); } else if (cmd == exitCommand) { try { destroyApp(true); } catch (MIDletStateChangeException ex) { } notifyDestroyed(); } } public void run() { DatagramConnection conn = null; display.setCurrent(connectForm); try { // Build the name string for the Connector open method String server = serverName.getString(); String name = “datagram://” + server + “:” + 13; conn = (DatagramConnection)Connector.open(name, Connector.READ_WRITE, false); // Build and send an empty datagram Datagram dg = conn.newDatagram(10); dg.setData(“Hello”.getBytes(), 0, 5); conn.send(dg); // Receive the reply Datagram rdg = conn.newDatagram(512); conn.receive(rdg); messageLabel.setText(new String(rdg.getData(), 0, rdg.getLength())); display.setCurrent(displayForm); } catch (InterruptedIOException iex) { display.callSerially(new Runnable() { public void run() { Alert alert = new Alert(“No Reply”, “No reply was received.\n” + “Please check the server address and try again.”, null, AlertType.ERROR); alert.setTimeout(Alert.FOREVER); display.setCurrent(alert, addressForm); } }); return; } catch (Exception ex) { display.callSerially(new Runnable() { public void run() { Alert alert = new Alert(“Invalid Address”, “The supplied address is invalid\n” + “Please correct it and try again.”, null, AlertType.ERROR); alert.setTimeout(Alert.FOREVER); display.setCurrent(alert, addressForm); } }); return; } catch (Error err) { System.out.println(err); err.printStackTrace(); } } private void initialize() { display = Display.getDisplay(this); // Commands exitCommand = new Command(“Exit”, Command.EXIT, 0); okCommand = new Command(“OK”, Command.OK, 0); backCommand = new Command(“Back”, Command.BACK, 0); // The address form addressForm = new Form(“Network Time”); serverName = new TextField(“Time Server name:”, “tock.usno.navy.mil”, 256, TextField.ANY); addressForm.append(serverName); addressForm.addCommand(okCommand); addressForm.addCommand(exitCommand); addressForm.setCommandListener(this); // The connect form connectForm = new Form(“Sending”); messageLabel = new StringItem(null, “Sending the datagram…\nPlease wait.”); connectForm.append(messageLabel); connectForm.addCommand(backCommand); connectForm.setCommandListener(this); // The display form displayForm = new Form(“Server Reply”); messageLabel = new StringItem(null, null); displayForm.append(messageLabel); displayForm.addCommand(backCommand); displayForm.setCommandListener(this); }}
J2ME Datagram Receiver
import java.io.IOException;import javax.microedition.io.Connector;import javax.microedition.io.Datagram;import javax.microedition.io.DatagramConnection;public class DatagramReceiver { public static void main(String[] args) { if (args.length != 1) { System.out.println(“Usage: DatagramReceiver port”); System.exit(1); } try { DatagramConnection receiver = (DatagramConnection)Connector.open(“datagram://:” + args[0]); byte[] buffer = new byte[256]; Datagram dgram = receiver.newDatagram(buffer, buffer.length); for ($$) { dgram.setLength(buffer.length); receiver.receive(dgram); int length = dgram.getLength(); System.out.println(“Datagram received. Length is ” + length); // Show the content of the datagram. for (int i = 0; i < length; i++) { System.out.print(buffer[i] + ” “); } System.out.println(); // Send it back… receiver.send(dgram); } } catch (IOException ex) { System.out.println(“IOException: ” + ex); } }}
J2ME DatagramSender
import java.io.IOException;import javax.microedition.io.Connector;import javax.microedition.io.Datagram;import javax.microedition.io.DatagramConnection;public class DatagramSender { public static void main(String[] args) { if (args.length != 2) { System.out.println(“Usage: DatagramSender port length”); System.exit(1); } try { DatagramConnection sender = (DatagramConnection)Connector.open(“datagram://localhost:” + args[0]); int length = Integer.parseInt(args[1]); byte[] buffer = new byte[length]; for (int i = 0; i < length; i++) { buffer[i] = (byte)(‘0’ + (i % 10)); } Datagram dgram = sender.newDatagram(buffer, buffer.length); sender.send(dgram); // Wait for the packet to be returned for (int i = 0; i < length; i++) { buffer[i] = (byte)0; } sender.receive(dgram); length = dgram.getLength(); System.out.println(“Received return packet, length is ” + length); // Show the content of the datagram. for (int i = 0; i < length; i++) { System.out.print(buffer[i] + ” “); } System.out.println(); } catch (IOException ex) { System.out.println(“IOException: ” + ex); } }}
Demonstrates the functionality of DatagramConnection framework.
/*** Chapter 5 Sample Code for Datagram functionality ***/import javax.microedition.midlet.*;import javax.microedition.lcdui.*;import javax.microedition.io.*;import java.util.*;public class DatagramTest extends MIDlet { // Port 9001 is used for datagram communication static final int receiveport = 9001; Receive receiveThread = new Receive(); public DatagramTest() { } public void startApp() { // Start the listening thread receiveThread.start(); // Send message Hello World! sendMessage(“Hello World!”); } public void pauseApp() { } public void destroyApp(boolean unconditional) { } // This function sends a datagram message on port 9001. void sendMessage(String msg) { String destAddr = “datagram://localhost:” + receiveport; DatagramConnection dc = null; Datagram dgram; byte[] bMsg = msg.getBytes(); try { dc = (DatagramConnection)Connector.open(destAddr); // Create a datagram socket and send dgram= dc.newDatagram(bMsg,bMsg.length,destAddr); dc.send(dgram); System.out.println(“Sending Packet:” + msg); dc.close(); } catch (Exception e) { System.out.println(“Exception Connecting: ” + e.getMessage()); } finally { if (dc != null) { try { dc.close(); } catch (Exception e) { System.out.println(“Exception Closing: ” + e.getMessage()); } } } } // This function is a listener. It waits to receive datagram packets on 9001 port class Receive extends Thread { public void run() { doReceive(); } void doReceive() { DatagramConnection dc = null; Datagram dgram; try { // Open Server side datagram connection dc = (DatagramConnection)Connector.open(“datagram://:”+receiveport); String receivedMsg; while (true) { dgram = dc.newDatagram(dc.getMaximumLength()); try { dc.receive(dgram); } catch (Exception e) { System.out.println(“Exception in receiving message:” + e.getMessage()); } receivedMsg = new String(dgram.getData(), 0,dgram.getLength()); System.out.println(“Received Message: ” + receivedMsg); try { Thread.sleep(500); } catch (Exception e) { System.out.println(“Exception doReceive(): ” + e.getMessage()); } } } catch (Exception e) { System.out.println(“Exception doReceive(): ” + e.getMessage()); } finally { if (dc != null) { try { dc.close(); } catch (Exception e) { System.out.println(“Exception Closing: ” + e.getMessage()); } } } } } }
j2me:Socket connection
/*J2ME: The Complete ReferenceJames KeoghPublisher: McGraw-HillISBN 0072227109*/// jad file (Please verify the jar size first)/*MIDlet-Name: socketconnectionMIDlet-Version: 1.0MIDlet-Vendor: MyCompanyMIDlet-Jar-URL: socketconnection.jarMIDlet-1: socketconnection, , socketconnectionMicroEdition-Configuration: CLDC-1.0MicroEdition-Profile: MIDP-1.0MIDlet-JAR-SIZE: 100*/import javax.microedition.midlet.*;import javax.microedition.lcdui.*;import java.io.*;import javax.microedition.io.*;public class socketconnection extends MIDlet implements CommandListener { private Command exit, start; private Display display; private Form form; public socketconnection () { display = Display.getDisplay(this); exit = new Command(“Exit”, Command.EXIT, 1); start = new Command(“Start”, Command.EXIT, 1); form = new Form(“Read Write Socket”); form.addCommand(exit); form.addCommand(start); form.setCommandListener(this); } public void startApp() throws MIDletStateChangeException { display.setCurrent(form); } public void pauseApp() { } public void destroyApp(boolean unconditional) { } public void commandAction(Command command, Displayable displayable) { if (command == exit) { destroyApp(false); notifyDestroyed(); } else if (command == start) { try { StreamConnection connection = (StreamConnection) Connector.open(“socket://www.myserver.com:80”); PrintStream output = new PrintStream(connection.openOutputStream() ); output.println( “GET /my.html HTTP/0.9\n\n” ); output.flush(); InputStream in = connection.openInputStream(); int ch; while( ( ch = in.read() ) != –1 ) { System.out.print( (char) ch ); } in.close(); output.close(); connection.close(); } catch( ConnectionNotFoundException error ) { Alert alert = new Alert( “Error”, “Cannot access socket.”, null, null); alert.setTimeout(Alert.FOREVER); alert.setType(AlertType.ERROR); display.setCurrent(alert); } catch( IOException error ) { Alert alert = new Alert(“Error”, error.toString(), null, null); alert.setTimeout(Alert.FOREVER); alert.setType(AlertType.ERROR); display.setCurrent(alert); } } }}