Learn Mysql in simple terms (Collection)

Preface

The database has always been a weak point for the author. Based on my own experience (python+sqlalchemy), etc., I will make a record. I prefer to use ORM, but I have always felt that spelling out SQL is a relatively painful thing (mainly because I am not good at SQL). I have also encountered some database pitfalls in previous maintenance projects, such as encoding problems, loss of floating point precision, etc., to prevent Repeat the trap in the future.

Chapter 1: Using Help

Use the built-in help command of mysql

1. msyql> ? data types : 查看数据类型
   mysql> ? int
   mysql> ? create table

Chapter 2: Selection of table type (storage engine)

The two most commonly used engines:

1. Myisam is the default storage engine of Mysql. When create creates a new table and the storage engine of the new table is not specified, Myisam is used by default. Each MyISAM is stored as three files on disk. The file names are the same as the table names, and the extensions are .frm (storing table definition), .MYD (MYData, storing data), and .MYI (MYIndex, storing index). Data files and index files can be placed in different directories to evenly distribute IO and obtain faster speeds.

2. The InnoDB storage engine provides transaction security with commit, rollback and crash recovery capabilities. However, compared with Myisam's storage engine, InnoDB's write processing efficiency is less efficient and takes up more disk space to retain data and indexes.

Commonly used environments:

1. MyISAM: The default MySQL plug-in storage engine, which is most commonly used in Web, data warehousing and other application environments
One of the storage engines used

2. InnoDB: used for transaction processing applications, with many features, including ACID transaction support.

Chapter 3: Select the appropriate data type

First select the appropriate storage engine, based on the specified storage engine Determine the appropriate data type.

• MyISAM: It is better to use fixed-length data columns instead of variable-length data columns.

• InnoDB: It is recommended to use varchar
  

Some data types to note:

1. Char and varchar: The saving and retrieval methods are different, the maximum length and whether trailing spaces are retained are also different. char has a fixed length. If the length is not long enough, use spaces to fill it. If PAD_CHAR_TO_FULL_LENGTH is not set when obtaining, trailing spaces will be removed by default.
varchar variable lengthString, trailing spaces will be retained during retrieval. Note that the query is not case-sensitive. If you use sqlalchemy to be case-sensitive, do not use the func.binary function.

2. text and blob: Text and blob will leave a big "hole" when a large number of updates or deletions are performed. It is recommended to regularly use the OPTIMIZE TABLE function to defragment such tables. . Avoid retrieving large blob or text values. Separate text and blob columns into separate tables.

3. Floating point number float and fixed point number decimal:

Note a few points:

1. Although floating point numbers can represent larger data range, but there is an error problem.

2. For precision-sensitive issues such as currency, fixed-point storage should be used. There have been pitfalls in previous projects, and I had to use zooming in and zooming out to solve the problem, which was quite ugly.

3.ProgrammingIf you encounter floating point numbers, pay attention to the error problem and try to avoid floating point comparisons (comparing floating point numbers requires a difference less than a specific precision), in python3.5 It can be compared like this: float_eq = partial(math.isclose, rel_tol=1e-09, abs_tol=0.0)

4. Pay attention to the processing of some special values ​​​​in floating point numbers.

#Chapter 4: Character Set

To choose the appropriate character set at the beginning, Otherwise, later replacement will be very expensive. The character set in python2 is a long-standing problem, which confuses many novices. The previously maintained project used msyql's default latin1 character set, which resulted in the string having to be manually encoded into utf8 every time it was written. Recently, I used utf8 for projects using python3.5+flask, and I have never encountered encoding problems again:

• Create databaseUse utf8, CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;

• sqlalchemy connection url uses mysql://root:root@127.0.0.1:3306/my_db?charset=utf8. No need to worry about garbled characters
  

Chapter 5: Design and use of index

All mysql column types can be indexed. Using indexes on related columns is the best way to improve the performance of select operations. Principles of index design:

1. The index column searched is not necessarily the column to be selected. The most suitable columns for the index are the columns that appear in the where clause, or the columns specified in the join clause, rather than the columns that appear in the select list after the select keyword.

2. Use a unique index. For columns with unique values, the indexing effect is better, but for columns with multiple duplicate values, the indexing effect is poor.

3. Use short indexes. If you are indexing a string column, you should specify a prefix length, and you should do this whenever possible.

4. Use the leftmost prefix. When creating an n-column index, n indexes that are available to MySQL are actually created. A multicolumn index can function as several indexes because rows can be matched using the leftmost set of columns in the index, which becomes the leftmost prefix.

5. Don’t over-index. Indexes waste disk space and reduce write performance.

6. Consider the types of comparisons performed on columns.

Chapter 6: Lock Mechanism and Transaction Control

The InnoDB engine provides row-level locks and supports shared locks and Two locking modes for exclusive locks, and four different isolation levels. mysql supports local transactions through statements such as AUTOCOMIT, START TRANSACTIONS, COMMIT and ROLLBACK.

Chapter 7: Security Issues in SQL

SQL injection: exploiting the external interface of some databasesInsert user data into the actual database operation voice (sql), thereby achieving the purpose of invading the database or even the operating system. The main reason is that the data input by the program heap user is not strictly filtered, resulting in the execution of illegal database query statements. Preventive measures:

1. prepareStatement = Bind- variable, do not use spliced ​​sql

2. Use the conversion function provided by the application

3. Custom function verification (form verification etc.)
   

Chapter 8: SQL Mode and related issues

Change the default mysql When the insertion or update under strict mode is incorrect, mysql will give an error and give up the operation. set session sql_mode='STRICT_TRANS_TABLES'. Setting sql_mode requires the application personnel to weigh various pros and cons and make an appropriate choice.

Chapter 9: Common SQL Tips

1. Retrieve rows containing maximum/minimum values: MAX([DISTINCE] expr), MIN([DISTINCE] expr)

2. Smart use of rand()/rand(n)Extract random rows

3. Use group by and with rollup clauses to do statistics

4. Use bit group functionsMaking statistics
   

Chapter 10: Other issues that need attention

Database name and table name case problem: Different platforms and systems have different case sensitivity. The advice is to always use lowercase names.
Things to note when using foreign keys: InnoDB in mysql supports checking of external keyword constraints.

Chapter 11: SQL Optimization

General steps to optimize SQL:

1. Use show status and application characteristics to understand the execution frequency of various SQLs and the approximate execution ratio of various SQLs. For example, the number of rows returned by InnoDB's parameter Innode_rows_read query, the number of rows inserted by Innodb_rows_inserted and the number of rows updated by Innodb_rows_updated. There are also a few parameters: Connections attempts to connect to the mysql server, the working time of the Uptime server, and the number of Slow_queries slow queries.

2. Locate SQL statements with low execution efficiency. There are two ways: one is to locate statements with low execution efficiency through slow query logs. When starting with the --log-slow-queries[=file_name] option, mysqld writes a log file containing all SQL statements whose execution time exceeds long_query_time seconds. The other is to use show processlist to view the current MySQL threads, including the status of the thread, whether to lock the table, etc. You can view the SQL execution in real time and optimize some lock table operations.

3. Analyze the execution plan of inefficient SQL through EXPLAIN: explain can know when the table must be indexed to get a faster SELECT that uses the index to find records. The following is after EXPLAIN execution Description of the results obtained:

• select_type: select type

• table: The table that outputs the result set

• type: Represents the connection of the table type. When there is only one row in the table with the value of type being system, it is the best connection type; when an index is used for table connection in the select operation, the type value is ref; when the select table connection does not use an index, the value of type is often seen. If it is ALL, it means that a full table scan has been performed on the table. At this time, you need to consider creating an index to improve the table connection efficiency.

• possible_keys: Indicates the index columns that can be used when querying.

• key: Indicates the index used

• key_len: Index length

• rows: Scan range

• Extra: Explanation and description of the execution

• 
  

## 4. Confirm problems and take corresponding optimization measures.

Index problem

1. Index storage classification: The data files and index files of the myisam table are automatically separated, and the data and index of innodb are placed in the same place. Inside a tablespace. The index storage types of myisam and innodb are both btree

2. How Mysql uses indexes: Indexes are used to quickly find rows with specific values ​​in a column. The most important condition for using an index in a query is to use the index key in the query condition. If it is a multi-column index, the index can only be used when the leftmost prefix of the multi-column key is used in the query condition, otherwise it will not be possible. Use indexes.

3. Check the usage of the index: The value of Handler_read_key represents the number of times a row has been indexed. A low value indicates that the index is not frequently used. A high Handler_read_rnd_

   next value means that the query is running inefficiently and an index should be created to remedy it. show status like 'Handler_read%';

Two simple and practical optimization methods

• Regular analysis table: ANALYZE TABLE, CHECK TABLE, CHECKSUM TABLE

• Use OPTIMIZE table;

  
  

From the client (Code side) Angular Optimization

1. Use persistent

   connections to the database to avoid connection overhead. In the code, we generally use the connection pool

2. to check that all inserts indeed use the necessary indexes.

3. Avoid executing complex select queries on frequently updated tables to avoid problems related to table locks due to read and write conflicts.

4. Take full advantage of default values ​​and insert values ​​explicitly only if they differ from the default value. Reduce the syntax analysis that MySQL needs to do to increase the insertion speed.

5. Separation of reading and writing improves performance

6. Try not to auto-increase table fields

   Variables to prevent this field from occurring under high concurrency conditions Auto-increment affects efficiency. It is recommended to implement auto-increment of fields through applications.

Chapter 12: Optimizing the Database Object

Optimize the data type of the table :

PROCEDURE ANALYZE() Provides optimization suggestions for judging the current table type. In practice, statistical information can be used in combination with actual optimization.

Improve table access efficiency through splitting: splitting here is mainly for Myisam type tables.

• Vertical split: According to the frequency of application access, the frequently accessed fields and infrequently accessed fields in the table are split into two tables. Frequently accessed fields should be as certain as possible. long.

• Horizontal splitting: According to the application situation, the data is purposefully split horizontally into several tables or divided into multiple partitions through partitions, which can effectively avoid Myisam table reads. Locking issues caused by fetching and updating.

  
  

Denormalization: Normalized design emphasizes independence. The data should be as little redundant as possible. More redundancy means occupying more physical space. Colleagues also maintain and maintain data consistency. Sexual examination poses problems. Appropriate redundancy can reduce multi-table access and significantly improve query efficiency. In this case, appropriate redundancy can be considered to improve efficiency.

Use redundant statistical tables: use

create temporary tableDo statistical analysis

Choose a more appropriate table type: 1. If the application has serious locks If there is a conflict, please consider whether to deliberately change the storage engine to InnoDB. The row lock mechanism can effectively reduce the occurrence of lock conflicts. 2. If the application has a lot of query operations and does not have strict requirements on transaction integrity, you can consider using Myisam.

Chapter 13: Lock Problem

Get the waiting status of the lock: table_locks_waited and table_locks_immediate status variables to analyze the system Table lock contention. Check Innode_row_lock to analyze row lock contention.

Chapter 14: Optimizing Mysql Server
##View the current parameters of Mysql Server

View the default values ​​of server parameters:
1. mysqld --verbose --help

2. View the actual values ​​of the server parameters: shell> mysqladmin variables or mysql> SHOW VARIABLES

3. View the server running status values: mysqladmin extended-status or mysql>SHOW STATUS
   

##Important parameters that affect Mysql performance

1. key_buffer_size: key

   Cache

2. table_cache: The number of caches opened in the database

3. innode_buffer_pool_size: The memory buffer for caching InnoDB data and indexes Size

4. innodb_flush_log_at_trx_commit: It is recommended to set it to 1. When each transaction is committed, the log buffer is written to the log file, and the log file is refreshed by disk operations.

Chapter 15: I/O Issues

Disk search is a huge performance bottleneck.

1. Use disk arrays or virtual file volumes to distribute I/O

2. Use Symbolic Links to distribute I/O

   
   

Chapter 16: Application Optimization

1. Use connection pool: The cost of establishing a connection is relatively high. Establish a connection pool to improve access performance.

2. Reduce access to Mysql: 1. Avoid repeated retrieval of consent data. 2 Use mysql query cache

3. Add cache layer

4. Load balancing: 1. Use mysql to copy and offload query operations. 2Distributed database

   Architecture

Summary

The above is the detailed content of Learn Mysql in simple terms (Collection). For more information, please follow other related articles on the PHP Chinese website!