Ways to optimize MySQL database

1. Select the most applicable field properties

Minimize the length of the defined field and set the field to not NULL, for example ' Province, gender ', preferably enum

2. Use connection (join) instead of subquery:

A, delete no orders customers

ELETE from CustomerInfo WHERE customerid Not IN (SELECT CustomerID from OrderInfo)

B, extract all customers without orders

Select from CustomerInfo WHERE CustomerID Not IN (SELECT CustomerID from OrderInfo)

c, improve the speed of B optimization

SELECT from CustomerInfo left JOIN OrderID customerinfo.customerid=orderinfo.customerid WHERE Orderinfo.customerid is NU LL

3. Use Union (Union) instead of manually created temporary table

To create a temporary table:

Select name from ' nametest ' UNION SELECT username from ' nametest2 '

4. Transaction processing:

Ensures data integrity, such as additions and modifications, both are executed and accesses than either failures fail

mysql_query ("BEGIN");

mysql_query ("INSERT into CustomerInfo (name) VALUES (' $name 1 ')";

mysql_query ("Select * from ' OrderInfo ' where customerid=". $id ");

mysql_query ("COMMIT");

5, lock the table, optimize transaction processing　　

We use a SELECT statement to remove the initial data and, with some calculations, update the new value to the table with the UPDATE statement.

The LOCK TABLE statement with the WRITE keyword guarantees that no additional access is allowed to insert, update, or delete the inventory until the UNLOCK TABLES command is executed

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, using foreign keys, optimize the locking table

Map the CustomerID in the CustomerInfo to the CustomerID in OrderInfo, and any record of no legal CustomerID will not be written in 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;

Note: ' On delete CASCADE ', this parameter guarantees that when a record in the CustomerInfo table is deleted, all records of that user in the order table are also deleted, noting that the foreign key is used to define the transaction security type of InnoDB;

7. Build the index:

A, (normal index)

Created: Create INDEX < index name > on tablename (indexed field)

Modified: Alter TABLE tablename ADD index [index name] (indexed field)

Genesis specified index: CREATE TABLE tablename ([...],index[index name] (index field))

b, (unique index)

Created: Create UNIQUE < index name > on tablename (indexed field)

Modified: Alter TABLE tablename ADD UNIQUE [index name] (indexed field)

Genesis specified index: CREATE TABLE tablename ([...],unique[index name] (index field))

C, (primary key index)

It is a unique index, generally in the creation of tables is established, in the form of:

Creata TABLE tablename ([...],primary key[index field])

8. Optimizing Query Statements

It is best to compare operations in the same field, minimizing function operations on established index fields

A, Example 1:

SELECT * FROM Order WHERE year (orderDate) <2008; (slow)

SELECT * FROM order WHERE orderdate< "2008-01-01";(fast)

B, Example 2:

SELECT * FROM Order WHERE addtime/7<24; (slow)

SELECT * FROM Order WHERE addtime<24*7; (FAST)

C, Example 3:

SELECT * from the order WHERE title like "%good%";

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

9, MySQL function summary

A, what is the function of MySQL to get the current time?

Now ()

B. What is the function of formatting the date?

Date ()

C, automatically insert the current time?

Set the Column property to "TIMESTAMP" and set its default to "Current_timestamp". But it only works for "TIMESTAMP".

What is the difference between D, Mysql_fetch_row () and mysql_fetch_array?

Mysql_fetch_row is a 1-row array taken from the result set as an enumeration

Mysql_fetch_array is to take an array of rows from the result set as an associative array, or an array of numbers, both

E, the function that gets the total number of query result sets is?

Mysql_num_rows ($result);

10. write out the names of more than three MySQL database storage engines

MyISAM, InnoDB, BDB (Berkeley DB), Merge, Memory (Heap), Example, Federated, Archive, CSV, blackhole, MaxDB, etc. more than 10 engines

11. What are the basic three optimization rules for MySQL database, in addition to increasing hardware and bandwidth?

　　(Hint: From a service configuration, application, development perspective)

　　(1) System service optimization, to increase the capacity of MySQL Key_buffer, Cache_buffer, Query_cache, etc.

(2) Add an appropriate index to all frequently queried fields

(3) Optimization of SQL statements, reduce ditinct, Group, join and other statements operations

12. How to optimize MySQL database

(1). Select the most applicable field property, you should set the field to not null as far as possible, so that in the future to execute the query, the database does not have to compare NULL values.

(2). Use connection (join) instead of subquery (sub-queries)

(3). Use Union (Union) instead of manually created temporary tables

(4). Use the LIKE keyword and wildcard characters sparingly

(5). Using Transactions and foreign keys

13, for the large flow of the site, the use of what method to solve the traffic problem?

First, verify that the server hardware is sufficient to support hold Current traffic

Second, optimize database access.

Third, prohibit the external hotlinking.

Four, control the download of large files.

V. Use different hosts to divert the main flow

VI, use traffic analysis statistics software.

14. Please briefly describe the paradigm and application of database design

The general 3rd paradigm is sufficient for the optimization of the table structure, which avoids the complexity of the application and avoids the system inefficiency caused by the SQL statement being too large.

The first paradigm: if each attribute of the relationship pattern R is non-decomposed, then it belongs to the first paradigm.

The second paradigm: if R belongs to the first paradigm, and all non-code attributes are fully functional dependent on the code attribute, then the second paradigm.

The third paradigm: if R belongs to the second paradigm, and none of the non-code attributes is a transfer function dependent on the candidate code, then it belongs to the third paradigm.

15. What are the steps to connect database operations? What data type is the return value for each step?

$coon = mysql_connect (' localhost ', ' root ', '); Return resource type

mysql_select_db (' BBS ', $coon); Boolean type

Mysq_query (' Set names UTF8 '); Boolean type

$qry = mysql_query (' select * from Bbsinfo '); Resource type

Mysql_fetch_row ($qry);//array type

How to optimize MySQL database