Monty says MySQL optimization (III)

This article is the third Monty speech at the o'reilly Open Source Convention 2000 conference, it involves maintenance, optimization of SQL, speed differences between different SQL servers, important MySQL startup options, and optimization tables. It is a detailed MySQL optimization document.

XI,Maintenance

If possible, run OPTIMIZE table occasionally, which is very important for changing the length of rows in a large number of updates.
Occasionally, use myisamchk-a to update the key code distribution statistics in the table. Remember to turn MySQL off before doing so.
If there are fragment files, it may be worthwhile to copy all the files to another disk, clear the original disk and copy back the files.
If any problem occurs, use myisamchk or CHECK table to CHECK the table.
Use mysqladmin-i10 precesslist extended-status to monitor the MySQL status.
With the MySQL GUI client program, you can monitor the process list and status in different windows.
Use mysqladmin debug to obtain information about locking and performance.

12,Optimize SQL

Take advantage of SQL and hand over other tasks to applications. Use the SQL Server:

Find the rows based on the WHERE clause.
JOIN table
GROUP
ORDER
DISTINCT
Do not use SQL:

Test data (such as date)
Become a calculator
TIPS:

Use the key code wisely.
The key code is suitable for searching, but not for inserting or updating index columns.
Keep data as the third paradigm of the database, but do not worry about redundant information or create a summary table if you need to speed up.
Do not use group by on a large table. Instead, create a summary table for a large table and query it.
UPDATE table set count = count + 1 where key_column = constant very fast.
For large tables, it may be better to generate a summary table occasionally instead of keeping the summary table all the time.
Make full use of the default values of INSERT.

XIII,Speed differences between different SQL servers(In seconds)

Read 2000000 lines by keycode: NT Linux
Mysql 367 249
Mysql_odbc 464
Db2_odbc 1206
Informix _odbc 121126
Ms-sql_odbc 1634
Oracle_odbc 20800
Solid_odbc 877
Sybase_odbc 17614
　
Insert row 350768: NT Linux
Mysql 381 206
Mysql_odbc 619
Db2_odbc 3460
Informix _odbc 2692
Ms-sql_odbc 4012
Oracle_odbc 11291
Solid_odbc 1801
Sybase_odbc 4802

In the above test, MySQL configures an 8 m high-speed cache to run, and other databases are installed and run by default.

14th,Important MySQL startup options

Modify back_log if a large number of new connections are required.
Thread_cache_size if a large number of new connections are required, modify it.
Key_buffer_size can be set to a large index page pool.
Bdb_cache_size the records and keys used by the BDB table are cached at high speed.
If table_cache has many tables and concurrent connections, modify it.
Delay_key_write If You Need To Cache all key code writes, set it.
Log_slow_queries.
Max_heap_table_size is used for GROUP
Sort_buffer is used for order by and GROUP
Myisam_sort_buffer_size is used for REPAIR TABLE
Join_buffer_size is used for join without keys.

15th,Optimization table

MySQL has a set of rich types. You should try to use the most effective type for each column.
The ANALYSE process helps you find the optimal table Type: SELECT * FROM table_name procedure analyse ().
Not null is used for columns that do NOT store NULL values. This is especially important for the columns you want to index.
Change the ISAM type table to MyISAM.
If possible, use a fixed table to create a table.
Don't index what you don't want to use.
The fact that MySQL can query by the prefix of an index. If you have an INDEX (a, B), you do not need an INDEX on.
Instead of creating an index on long CHAR/VARCHAR columns, you can only create a prefix of the index column to save storage space. Create table table_name (hostname CHAR (255) not null, index (hostname (10 )))
Use the most effective table style for each table.
Columns that store the same information in different tables must have the same definitions and names.