Summary of MySQL basic knowledge

This article introduces some basic knowledge about mysql to you. I hope it will be helpful to you in the future.

1. char and varchar

1)Char is a fixed space storage, if the actual data is not enough, it will be filled. If the length is exceeded, it will be truncated; varchar is not a fixed-length storage and uses an additional 1-2 bytes to store the length information.

2) char may cause a waste of space, but it may also effectively reduce space fragmentation, with an upper limit of 255; the opposite is true for varchar, with an upper limit of 65535 bytes (the specified length represents characters, encoding must be considered)

2. int(x)

x represents the maximum display length of the integer type. int occupies 4 bytes, and the unsigned maximum value is 4294729693, that is, the length is 10. Therefore, there should be essentially no difference between int(11) and int(12).

3. Mysql log types

Error log: -log-err (recorded information includes : Service startup or shutdown, errors or warnings in the running process, event scheduler)

Query log: -log        

Slow query log: -log-slow-queries

Update log: log-update (generally not used)

Binary log: -log-bin (mysqlbinlog to view binary files)

4. show profile

is used to analyze the performance of SQL execution. Compared with explain, profile can also view the CPU/Memory usage of execution.

5. About buffer size

For myisam, set the appropriate key_buffer_size size; because myisam The system cache is needed to cache data, so some space needs to be reserved for the index.

For innodb, set the appropriate innodb_buffer_pool_size size; because innodb will cache both the data and the index, this value should Relatively larger.

6. Delete and optimize

If a table often has inserts and deletes Operations should be carried out regularly to optimize operations, which can effectively reclaim space and defragment.

7. About locks

Myisam only supports table locks; innodb supports row-level locks, which are implemented by locking the index. Therefore, row locks are only used when the index is used to retrieve data, otherwise table locks are still used. .

8. Index

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

Innodb has two kinds of indexes: B-tree index, hash index. Innodb is an index-organized table.

The above are some basic knowledge about mysql that I have compiled for you. I hope it will be helpful to you in the future.

Related articles:

Introduction to common MySQL statements

##Some advanced usage of mysql

Mysql basic syntax

The above is the detailed content of Summary of MySQL basic knowledge. For more information, please follow other related articles on the PHP Chinese website!