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

Today, I will talk to you about the advanced features of MySQL, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Overview

Have nothing to do, read the high-performance mysql this book, some of the MySQL advanced features are well written, here to summarize and share.

01. Partition table

1. Partition table restrictions

A table has up to 1024 partitions

Foreign key constraints cannot be used in partitioned tables

2. Pay attention to the partition table

There is a problem with too many partitions when writing a large amount of data by row, so for most systems, there is no problem with about 100 partitions

Note: key partitions and hash partitions do not have this problem

3. Query optimization

For access zones, it is important to have partition columns in where (partial partitions can be filtered)

Note: where must use the partition function column itself to filter partitions, such as where time='2017', and where YEAR (time) = 2017 error

02. View

1. Concept

Virtual table, no data stored, data from other tables

2. Update the view

Update columns must come from the same table and cannot be updated with GROUP BY, UNION, aggregate functions and special cases

3. Influence on performance

You can use views to reconstruct the database without having to modify the table structure, and use views to create column-based access control to reduce additional overhead, etc.

4. Restrictions on views

Materialized views (that is, views are not viewable in the table) and indexing in views are not supported.

03. Foreign key index

InnoDB is the only built-in engine in mysql that supports foreign key indexing.

Foreign key cost: each time a foreign key modifies data, it is required to perform one more lookup in another table, of course, foreign keys are more efficient in deleting and updating related data than maintaining them in the application.

Note: in many cases, it is found that the foreign key is the bottleneck in the performance analysis, and the performance is greatly improved immediately after the foreign key is deleted.

04. Character set and proofreading

Character set coding priority: column > table > database

Proofreading rules: _ cs, _ ci, _ bin correspond to case-insensitive, case-sensitive, binary values, respectively

05. Full-text index

Mysql does not support Chinese full-text indexing. Other engines such as Sphinx are used.

06. Distributed (XA) transaction

The ACID that an enterprise can still guarantee transactions under a distributed multi-database is described separately here.

07. Query cache

Concept: cache select results, skip parsing, optimization, and execution phases.

The query cache is completely stored in memory. Mysql cannot precisely allocate cache space of the right size for each query result.

How the query cache allocates memory to store result data

The reason why the query cache cannot be *: contains uncertain functions, the query has not been processed, and is expelled after running out of memory

Configure and maintain the query cache:

Query_cache_type: whether to enable query cache and set it to ON, OFF, DEMAND (this is only cached when SQL_CACHE is explicitly specified)

Query_cache_size: the total memory space used by the query cache (the value is 1024 integer times)

Query_cache_min_res_unit: the smallest unit of memory allocated in the query cache.

* * query results that can be cached by query_cache_limit:MySQL

Query_cache_wlock_invalidate: whether a table is locked and the result is still returned from the query cache. Default is OFF.

General query cache optimization:

1) use multiple small tables instead of one large table for query caching

2) you only need to do cache invalidation once for batch writes

3) the cache space is too large, and the server may be frozen by controlling the size or disabling it.

4) use SQL_CACHE and SQL_NO_CACHE to control whether a select is cached or not

5) for write-intensive applications, it is better to disable query caching directly.

Note: if you need higher cache efficiency, it is recommended to use things like memcached or redis

After reading the above, do you have any further understanding of the advanced features of MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.