Mysql High Performance

1. the length of a string is defined not as the number of bytes but as the number of characters (char, varchar) 2. the binary string stores the bytecode rather than the character (binary, varbinary fill, the fill value will not be removed during retrieval) 3. the string type is not ideal for identifying columns because it consumes space and is generally slower than the numeric type. myisam uses a compressed index on the string to export

1. the length of a string is defined not as the number of bytes but as the number of characters (char, varchar) 2. the binary string stores bytecode rather than characters (binary, varbinary \ 0 fill, the fill value will not be removed during retrieval) 3. the string type is not ideal for identifying columns because it consumes space and is generally slower than the numeric type. myisam uses a compressed index on the string to export

1. The length of a string is defined not as the number of bytes but as the number of characters (char, varchar)

2. binary strings store bytecode rather than characters (binary, varbinary \ 0 fill, the fill value will not be removed during retrieval)

3. the string type is not ideal for identifying columns because it consumes space and is generally slower than the numeric type.

4. If myisam uses a compressed index on the string, the query will be slow.

5. Proper Redundancy

6. innodb uses B + tree (each leaf node contains a pointer pointing to the next leaf node to facilitate the range traversal of the leaf node)

7. The index is valid only when the index helps the storage engine quickly find the record, and the benefit is greater than the extra work it brings.

8. prefix index. The ratio is (0.0312 ).

9. innodb does not have a primary key index and will have a unique non-null index instead. Otherwise, a primary key will be implicitly defined as a clustered index,

10. Secondary indexes (non-clustered indexes) may be larger than expected, because the leaf node of the secondary index contains the primary key column of the referenced row.

11. secondary index access requires two index searches, instead of one (the leaf node of the secondary index saves not the pointer to the physical location of the flight, but the primary key value of the row) (innodb adaptive hash index can reduce repetitive work)

12. innodb uses the share (read, s) lock on the secondary index, but the exclusive (write) Lock is required to access the primary key index.

13. check table

14. Optimized whether unnecessary data is requested. The same data is queried multiple times.

15. query overhead measurement: response time (service time + queue time), number of scanned rows, and number of returned rows (mysql slow log query)

16. When explaining, Extra = using index indicates that the covered index is used (the index containing all the data meeting the query needs is called the covering index)

17. decomposition association query

18. the communication protocol between the mysql client and the server is half-duplex, meaning that at any time, either the server sends data like the client, or the client sends data to the server, these two actions cannot occur simultaneously.

19. When the query statement is long: max_allowed_packet

20. mysql_query () of php caches the result set to the memory, and mysql_unbuffered_query () does not cache the result set to the memory.

21. view the current status: show full processlist;

The sleep thread is waiting for the client to send a new request. The query thread is executing the query or sending the result to the client locked. The thread is waiting for the table lock. the analyzing and statistics threads are collecting statistics on the storage engine. information, and generate the query execution plan copying to tmp table [on disk] thread is executing the query, copy the result set to the temporary table sorting result thread to sort the result set sending data: the thread may transmit data between multiple states, generate a result set, or send data to the client.

22. in mysql is not exactly the same as or. mysql sorts the data in the In list first, and then determines whether the values in the list meet the conditions by means of binary search, this is an o (log N) Complexity operation, equivalent to the complexity of converting to or query>

23. explain extended select ...; Show warnings; try it by yourself,

24. Partition Table:
When performing insert, update, delete, and select queries, the underlying table of the residence will be opened and locked first, and operations will be performed in the corresponding underlying table ..
25. Partition Table type:
The partition clause can use various functions, but there is a requirement that the return value of an expression is a definite integer and cannot be a constant.
Key-value partition, hash partition, list Partition

Note: When the data volume is too large, the B-tree index will not work. Unless the index overwrites the query, the database server needs to return to the table based on the index scan results, query all qualified records.

26. Partition policy:
Full scan data, without any index: roughly locate the desired data location according to the Partition Rules, as long as the where condition can be used, limit the required data to a few partitions, the efficiency is very high.
Index the data and separate the hotspot: The data has obvious hot spots. In addition to the few other hot spots, you can put the hotspot data in a separate partition so that it has the opportunity to cache the data to the memory.

27. important assumptions about partition policies:
The query can filter out many additional partitions.
The partition itself does not bring a lot of extra costs.
Problems:
1. the null value will invalidate partition filtering.
2. The partition column and index column do not match
3. The cost of selecting a partition may be high.
4. The cost of opening and locking all underlying tables may be high.
5. High partition maintenance costs

28. Partition restrictions:

1. all partitions must use the same storage engine. there are limits on functions and expressions that can be used in partition functions. 3. some storage engines do not support partitioning. myisam partition, cannot be operated using load index into cache 5. myisam. When using a partition table, you need to open more file descriptors.

Dht: distributed hash table

29. Merge tables

30. View

After 5.0, the view merge algorithm (to be used as much as possible) is introduced. The temporary table algorithm view contains group by, distinct, any aggregate function, union, subquery, etc, as long as one-to-one ing cannot be established between the original table record and view simplification, mysql uses the temporary table algorithm to try to view: select * from view_name; select_type deriver indicates that views implemented using the temporary table algorithm cannot be updated because the temporary table algorithm is used. mysql does not allow creation of any trigger innodb foreign key constraints on The View.

31. mysql allows you to store code in the form of triggers, stored procedures, and functions. stored procedures and functions are called stored procedures.

Stored Procedures, triggers, and cursors keep comments in the stored program to bind variables to user-defined functions

32. Plug-ins
Stored Procedure plug-ins, background plug-ins, information_schema, full-text parsing plug-ins, audit plug-ins, authentication plug-ins,

33. Character Set and Verification

The default setting is used during creation. When the Server communicates with the client, the client is set to convert data transmitted by character_set_client and received by the SQL server. character_set_connection server returns the error message character_set_result.

34. The load data infile database always parses the character set in the file according to character_set_database. You can use the character set clause to set the character set.

35. The result set of select info outfield is not written to any transcoded file.

36. Conversion between different character sets and verification rules may incur additional system overhead

37. Full-text index: supports searching for various characters (char, carchar, and test), as well as searching by natural language and by NOL.

38. distributed (xa) transactions; internal xa transactions, external xa transactions

39. query Cache
Mysql hit cache: the cache is stored in a reference table and referenced by a hash value. This hash value contains the query itself, the database to be queried, and the client Protocol version.

If the query contains an uncertain function, mysql does not check the query cache.

40. The query cache is stored in the memory:
When a query result needs to be cached, mysql will apply for a data block from a large space block for storing the results. This data block must be larger than the query_cache_min_res_unit configuration, even if the query result is smaller than this, at least the space for this parameter is required.

Cache fragmentation, insufficient memory, data modification will cause cache failure

41. Configure and maintain the query cache:
Query_cache_type, query_cache_size, query_cache_min_res_unit, query_cache_limit,
Query_cache_wlock_invalidate,

Original article address: mysql high performance. Thank you for sharing it with me.