How to optimize the MYSQL database and optimize the mysql database

How to optimize the MYSQL database and optimize the mysql database

1. select the most suitable field attribute

Minimize the length of the defined field and set the field not null, for example, 'province, gender'. It is best to set it to ENUM.

2. Use JOIN instead of subquery:

A. Deleting customers without any orders

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 B's Speed Optimization

Select from customerinfo left join orderid customerinfo. customerid = orderinfo. customerid WHERE orderinfo. customerid IS NULL

3. Use UNION instead of creating a temporary table manually 

Create a temporary table:

SELECT name FROM 'nametest 'union select username FROM 'nametest2'

4. Transaction processing:

Ensure data integrity. For example, if both the ADD and modify operations are performed, both operations fail.

Mysql_query ("BEGIN ");

Mysql_query ("insert into mermerinfo (name) VALUES ('$ name1 ')";

Mysql_query ("SELECT * FROM 'orderinfo' where customerid =". $ id ");

Mysql_query ("COMMIT ");

5. Locking the table and optimizing Transaction Processing　　

We use a SELECT statement to retrieve the initial data. Through some calculations, we use the UPDATE statement to UPDATE the new value to the table.

The lock table statement containing the WRITE keyword ensures that no other access is allowed to insert, update, or delete the inventory before the unlock tables command is executed.

Mysql_query ("lock table customerinfo READ, orderinfo WRITE ");

Mysql_query ("SELECT customerid FROM 'mermerinfo' where id =". $ id );

Mysql_query ("UPDATE 'orderinfo' SET ordertitle = '$ title' where mermerid =". $ id );

Mysql_query ("unlock tables ");

6. Use foreign keys to optimize table locking 

Merid in customerinfo is mapped to customerid in orderinfo. Any record without valid customerid is not written to 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 ensures that when a record in the customerinfo table is deleted, all records of the user in the order table are also deleted, note that the transaction security type should be defined as INNODB when the foreign key is used;

7. Create an index:

A. (Common Index)->

CREATE: create index <INDEX Name> ON tablename (INDEX field)

Modify: alter table tablename add index [INDEX name] (INDEX field)

Create table specified INDEX: create table tablename ([...], INDEX [INDEX name] (INDEX field ))

B. (unique index)->

CREATE: create unique <index Name> ON tablename (index field)

Modify: alter table tablename add unique [index name] (index field)

Create table specified index: create table tablename ([...], UNIQUE [index name] (index field ))

C. (primary key index)->

It is a unique index. Generally, a table is created in the format:

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

8. Optimize Query statements

It is best to compare the same fields and minimize the number of function operations on the created 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 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 obtain the current time?

Now ()

B. What is the date Formatting Function?

Date ()

C. What is the current automatic insertion time?

Set the column attribute to "TIMESTAMP" and its default value to "CURRENT_TIMESTAMP ". However, it is only valid for "TIMESTAMP.

D. What is the difference between mysql_fetch_row () and mysql_fetch_array?

Mysql_fetch_row is a one-row array from the result set, which is used as an enumeration

Mysql_fetch_array is used to retrieve an array from the result set as an associated array or a number array.

E. What is the function used to obtain the total number of query result sets?

Mysql_num_rows ($ result );

10,Write the names of more than three MySQL database storage engines

Dozens of engines including MyISAM, InnoDB, BDB (Berkeley DB), Merge, Memory (Heap), Example, Federated, Archive, CSV, Blackhole, and MaxDB

11. What are the three basic optimization rules for MySQL databases, except for increasing hardware and bandwidth?

(Tip: service configuration, application, and development considerations)

(1) System Service optimization: Increase the capacity of MySQL's key_buffer, cache_buffer, and query_cache.

(2) Add an appropriate index for all frequently queried Fields

(3) Optimize SQL statements to reduce operations on Ditinct, Group, Join, and other statements

12. Methods for optimizing the MYSQL database

(1) select the most suitable field attribute and set the field as not null as much as possible. In this way, the database does NOT need to compare NULL values during future query.

(2). Use JOIN instead of subquery (Sub-Queries)

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

(4). Use the LIKE keyword and wildcard as few as possible.

(5). Use transactions and Foreign keys

13. What methods are used to solve the traffic issue for high-traffic websites?

First, check whether the server hardware is sufficient to support the current traffic.

Second, optimize database access.

Third, prohibit external leeching.

Fourth, control the download of large files.

Fifth, use different hosts to distribute major traffic

Sixth, use the traffic analysis and statistics software.

14. Briefly describe the database design paradigm and Application

Generally, the 3rd paradigm is enough to optimize the table structure. This can avoid the complexity of applications and the low system efficiency caused by too large SQL statements.

First paradigm: if every attribute of the relational model R cannot be decomposed, then it belongs to the first paradigm.

Second paradigm: If R belongs to the first paradigm, and all non-Code attributes depend entirely on the Code attributes, the second paradigm is used.

Third paradigm: If R belongs to the second paradigm, and none of all non-Code attributes depend on candidate codes, then it belongs to the third paradigm.

15. What are the database connection steps? What data type is returned for each step?

$ Coon = mysql_connect ('localhost', 'root', ''); // return the 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