Shulou(Shulou.com)06/01 Report--

This article mainly introduces some basic knowledge about MySQL, the content of the article is carefully selected and edited by the author, has a certain pertinence, and is of great significance to everyone's reference, so let's learn some basic knowledge about MySQL with the author.

one。 Char and varchar

1) char is stored in fixed space. If the actual data is not enough, it will be truncated. If the length is exceeded, it will be truncated. For varchar, the length information is stored with an additional 1-2 bytes.

2) char may lead to space waste, but it may also effectively reduce space debris, with an upper limit of 255; in the case of varchar, on the contrary, the upper limit is 65535 bytes (the specified length is a character and coding should be considered)

two。 Int (x)

X represents the maximum display length of int integers. Int occupies 4 bytes, and the maximum unsigned value is 4294729693, that is, the length is 10. 5%. So there should be no difference between int (11) and int (12) in nature.

three。 Log types of mysql

Error log:-log-err (record information including: service startup or shutdown, run process errors or warnings, event scheduler)

Query log:-log

Slow log:-log-slow-queries

Update log: log-update (usually not used)

Binary log:-log-bin (mysqlbinlog view binaries)

four。 Show profile

It is used to analyze the performance of sql execution, and profile can also view the CPU/Memory usage of execution compared to explain.

five。 About buffer size

For myisam, set the appropriate key_buffer_size size; because myisam needs to use the system's cache to cache data, it needs to set aside some space for the index.

For innodb, set the appropriate innodb_buffer_pool_size size; because innodb caches both the data and the index, the value should be relatively large.

six。 Delete and optimize

If a table has frequent insert and delete operations, regular optimize operations should be performed to effectively reclaim space and defragment.

seven。 About locks

Myisam only supports table locks; innodb supports row-level locks, which are implemented by adding locks on indexes, so row locks are used only when indexes are used to retrieve data, otherwise table locks are still used.

eight。 Indexes

The index types supported by myisam include: general index (index), primary key (primary key), unique primary key (unique key), and full-text index (full text).

Innodb has two indexes: B + tree index and hash index. Innodb organizes tables for indexes.

After reading the above about some basic knowledge about MySQL, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.