Monty says MySQL optimization (5)

This article is Monty's speech at the o'reilly Open Source Convention 2000 conference, it involves the working principle of MySQL table high-speed cache, MySQL expansion/Optimization-providing faster speed, MySQL index usage, learning to use EXPLAIN, and using show processlist, is a detailed MySQL optimization document

21,Working principle of MySQL table high-speed cache

Each MyISAM table's open instance uses an index file and a data file. If the table is used by two threads or twice in the same query, MyIASM will share the index file but open another instance of the data file.
If all the tables in the cache are in use, the cache will be temporarily increased to a greater size than the table cache. In this case, the next released table will be closed.
You can check whether the cache is too small by checking the Opened_tables variable of mysqld. If the value is too high, you should increase the table cache speed.
　
22,MySQL extension/Optimization-faster speed

Use the optimized table type (HEAP, MyIASM, or BDB table ).
Use optimized columns for data.
Use fixed-length rows if possible.
Use different lock types (SELECT HIGH_PRIORITY, INSERT LOW_PRIORITY)
Auto_increment
REPLACE (replace into table_name VALUES (...))
INSERT DELAYED
Load data infile/LOAD_FILE ()
INSERT multiple rows at a time using multiple rows.
SELECT INTO OUTFILE
Left join, STRAIGHT JOIN
Left join, combined with IS NULL
Order by can use the key code in some cases.
If you only query the columns in one index, you can use only the index tree to solve the query.
Joins are generally faster than subqueries (for most SQL servers ).
LIMIT
SELECT * from table1 WHERE a> 10 LIMIT 10, 20
DELETE * from table1 WHERE a> 10 LIMIT 10
Foo IN (Constant list) is highly optimized.
GET_LOCK ()/RELEASE_LOCK ()
LOCK TABLES
INSERT and SELECT can run simultaneously.
UDF functions can be loaded into a running server.
Compress read-only tables.
CREATE TEMPORARY TABLE
Create table... SELECT
The MyIASM table with the RAID option splits files into many files to break through the 2G limitation of some file systems.
Delay_keys
Copy Function

22,When does MySQL use indexes?

Use>, >=, =, 1 and key_part1 for a key code <90
If you use the HEAP table and do not use = to search for all key code parts.
Use order by on the HEAP table.
If you do not use the first part of the key code

SELECT * FROM table_name WHERE key_part2 = 1
If you use LIKE, which starts with a wildcard

SELECT * FROM table_name WHERE key_part1 LIKE '% jani %'
Search for one index and create order by on another index

SELECT * from table_name WHERE key_part1 = # order by key2

24,Learn to use EXPLAIN

Use EXPLAIN for each query that you think is too slow!

Mysql> explain select t3.DateOfAction, t1.TransactionID
-> From t1 join t2 join t3
-> Where t2.ID = t1.TransactionID and t3.ID = t2.GroupID
-> Order by t3.DateOfAction, t1.TransactionID;
+ ------- + -------- + --------------- + --------- + ------------------ + ------ + --------------------------------- +
| Table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ------- + -------- + --------------- + --------- + ------------------ + ------ + --------------------------------- +
| T1 | ALL | NULL | 11 | Using temporary; Using filesort |
| T2 | ref | ID | 4 | t1.TransactionID | 13 |
| T3 | eq_ref | PRIMARY | 4 | t2.GroupID | 1 |
+ ------- + -------- + --------------- + --------- + ------------------ + ------ + --------------------------------- +
The ALL and range types indicate a potential problem.
　
25,Learn to use SHOW PROCESSLIST

Use SHOW processlist to find out what is being done:
+ ---- + ------- + ----------- + ---- + --------- + ------ + -------------- + --------------------------------------- +
| Id | User | Host | db | Command | Time | State | Info |
+ ---- + ------- + ----------- + ---- + --------- + ------ + -------------- + --------------------------------------- +
| 6 | monty | localhost | bp | Query | 15 | Sending data | select * from station, station as s1 |
| 8 | monty | localhost | Query | 0 | show processlist |
+ ---- + ------- + ----------- + ---- + --------- + ------ + -------------- + --------------------------------------- +
KILL is used in mysql or mysqladmin to KILL the thread.