Shulou(Shulou.com)05/31 Report--

This article to share with you is about MYSQL optimization is how to carry out, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some harvest, not much to say, follow Xiaobian to see it.

1. database design

Try to design the database to take up less disk space.

1). Use smaller integer types whenever possible. (Mediumint is better than int).

2). The definition field is not null whenever possible, unless null is required for this field.

3). If you don't use variable-length fields like varchar, you use fixed-size records like char.

4). The primary index of a table should be as short as possible. That way each record has a name tag and is more efficient.

5). Create only the indexes you really need. Indexing is good for retrieving records, but not good for saving them quickly. If you always want to search on composite fields of a table, create indexes on those fields. The first part of the index must be the most frequently used field. If you always need to use a lot of fields, you should first copy more of these fields to make the index better compressed.

6). All data must be processed before it can be saved to the database.

7). All fields must have default values.

8). In some cases, splitting a frequently scanned table into two can be much faster. This is especially true when dynamic format tables are scanned for relevant records, which may use smaller static format tables.

2. The purpose of the system

1). Try to use long links.

2). explain complex SQL statements.

3). if two associate tables are to be compare, that fields to be compared must be of the same type and length.

4).LIMIT statement should follow order by or distinct as much as possible. This can avoid doing a full table scan.

5). If you want to clear all records of a table, it is recommended to truncate table tablename rather than delete from tablename.

6). When STORE PROCEDURE or USER FUNCTION can be used.

7). Use multiple record insertion format in an insert statement. And use load data file to import large amounts of data, which is much faster than simple inert.

8). OPTIMIZE TABLE is often used to defragment.

9). There is also the date type of data, if you want to do frequent comparison, try to save in unsigned int type is faster.

3. the bottleneck

1). Disk Search.

Parallel search, where data is stored separately on multiple disks to speed up search time.

2). Disk Read/Write (IO)

Data can be read from multiple media in parallel.

3) CPU cycle

Data is stored in main memory. This increases the number of CPUs required to process the data.

4). memory bandwidth

Memory bandwidth becomes a bottleneck as CPU stores more data into CPU cache.

The above is how MYSQL optimization is carried out. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.