MySQL Knowledge summary notes

One. About table joins

1. The table connection mainly includes the outer connection and the inner connection, the inner connection only selects the matching records in the two tables connected, but the outer joins will select other mismatched records. There are different application scenarios for internal and external connections.

2. The left connection is based on the records in the table on the left, and if there are no records matching the left table in the right table, the records on the left table will still be displayed. 3. The right connection is based on the records in the right table, and if there are no records matching the right table in the left table, the records on the right table will still be displayed. 4. As can be known from 2 and 3, the left and right connections are interchangeable   two. As for the built-in function, you can look up the table when you need it, and use these functions as much as possible to avoid implementing 1 yourself. String functions, Concat, etc. 2. numeric function, Rand function 3. Date and Time functions, Unix_timestamp (date) and From_unixtime () 4. Process functions, such as if, and Casewhen5. Other commonly used functions, IP and long conversions, md5,password functions, etc.   three. For the storage engine, choose the appropriate storage engine 1 depending on the application type. Common storage engines include InnoDB and MyISAM, where MyISAM is primarily used for OLAP applications, InnoDB primarily for OLTP applications. 2. MyISAM table file:. frm (table definition). MYD (store data). Myi (storage index) 3. MyISAM table supported storage formats:    A. Static tables (each record is fixed length, storage is very fast, easy to cache, the disadvantage is that it takes up more space than the dynamic table, because when the storage is defined by the width of the column to complement the space)     B. Dynamic tables (contain variable-length fields, records are not fixed lengths, storage footprint is relatively small, but frequent update deletions can result in fragmentation, need to execute optimize table)     C. Compact table (occupies very small disk space, each record is compressed separately) 4. The InnoDB auto-grow column must be an index, and if it is a composite index, it must be the first column of the combined index. 5. InnoDB support foreign KEY, FOREIGN KEY constraint see: 6. InnoDB storage tables and indexes have the form of shared tablespace and multi-table space storage. In a timely manner in multi-table space storage, shared table space is still necessary, InnoDB will put the internal data dictionary and not log in this file. 7. Select the MyISAM engine if the application is based on read and insert operations with few update and delete operations, and the integrity of the transaction is not high on the concurrency requirement. If the integrity requirements for transactions are high, in the case of concurrency         Data consistency is required, and data crud operations are available, then select the InnoDB storage engine, which effectively reduces the locks caused by deletes and updates, ensuring transactional integrity.   Four. About data type 1. When a small number of strings are selected char and varchar, a large amount of text is used to save binary data using text or Blob,blob. However, the Blob and text will leave a big hole in the delete operation, and the subsequent filling of these empty records will have an effect on the performance of the insert. You can use optimize table to defragment the pieces. The trick is that you can use a composite index (a hash value based on Da Wen) to improve query performance, but apply only to exact lookups. 2. The floating-point number should pay attention to the problem of precision and rounding. In some applications where precision is more demanding, you should use fixed-point numbers or strings to store data. 3. Choose the date type that meets your app's minimum storage, depending on your actual needs. The year in which datetime types are recorded can be relatively remote, and timestamp can correspond to the actual time zone.   Five. About character Set 1. For commonly used coding knowledge, see: 2. When choosing a database character set, it needs to be based on the needs of the application to meet the needs of the language supported by the application, whether to deal with a variety of mosquitoes, or to publish to different languages of the country and region. If data already exists in the database, you also need to take into account the compatibility of the database character set with the existing data. For example Chinese character coding more, then you can choose GBK, because GBK characters occupy two bytes, UTF8 occupy three bytes, if only a small number of Chinese characters data, then choose UTF8 Good, because the UTF8 English characters only need one byte, The remaining GBK or UTF16 require two bytes to encode the western characters. 3. mysql the character set and proofing rules for saving data have 4 levels of default settings: server level, database level, table level, and field level. They are set up in different places and have different functions. 4. Also note the character set and collation rules for interaction between the client and server, and MySQL provides three different parameters to represent the client, connect and return the result of the character set, and typically, these three character sets should be the same. 5. Steps to modify the character set:  six. About index 1. The greater the range of values of the indexed columns, the better the index works. 2. If you index a string column, you should specify a prefix length, which can save a lot of index space. 3. With the leftmost prefix, when creating an N-column index, you are actually creating n indexes that MySQL can use. Multiple-column indexes can serve several indexes because the leftmost Lie match in the index is used, which is called the leftmost prefix 4. Do not over-index, the index will take up disk space, reduce the performance of write operations, in addition, MySQL in the generation of an execution plan to consider the various indexes, which also takes time. 5. InnoDB records are saved by default by a primary key or a unique index, and the most frequently accessed columns are selected as primary keys to improve query efficiency. InnThe normal index of the ODB table also holds the key value of the primary key, so the primary key chooses the shorter data type as much as possible, which reduces the disk footprint of the index. 6. Introduction of Btree Index: 7. Most MySQL indexes (Primarykey,unique,index and fulltext) are stored in btree. The index of the spatial column type uses Rtree, and the memory table also supports hash indexes.   Seven. About view   eight. About stored procedures and functions 1. The detailed syntax of the stored procedure is described in: 2. The advantage of stored procedures and functions is that the processing of data can be placed on the database server, to avoid the transfer of a large number of result sets to the client, reduce the transmission of data, but a large number of operations on the database server will also occupy the server's CPU, causing the database server pressure, so carefully consider.   Nine. About Trigger 1. A trigger is a database object that is related to a table, departs when a condition is met, and executes a collection of statements defined in the trigger. The role is to assist the application to ensure data integrity on the database side. 2. The syntax of the trigger is described in:  10. Transaction and Lock 1. MySQL supports table-level locking on MyISAM, locking the row level for InnoDB. When the connection to the server is closed, all tables locked by the current thread will be unlocked. 2. MyISAM Lock and transaction details: 3. Distributed Transaction:  11. SQL security and SQL Mode1. SQL injection: The program does not strictly filter the data entered by the user, resulting in the execution of illegal database query statements, as well as XSS injection. Preparestatement is used in Java to prevent SQL injection. 2. Sqlmode: By setting the SQL mode, the data can be verified with different rigor, and the accuracy of the data is guaranteed effectively.   12. The writing of complex SQL 1. GroupBy and have, with rollup, bit function, rand () function   13. Optimize SQL statement 1. Show status command to understand the current database, such as the scale of query updates, transaction execution, slow query number of times, connections, etc. 2. The slow query log must be turned on to locate a slow SQL statement by looking at the slow query log. The show processlist command can view the current MySQL thread in progress, including the state of the thread, whether the table is locked or not. 3. Use the explain and DESC commands to query the SQL execution plan. A detailed explanation of the explain command: 4. The index problem used by SQL execution. 5. Analyze tables and checklists regularly to optimize tables. 6. Insert the data in large batches, close the index update first, then load,And then open the index. 7. InnoDB types of tables are saved in the primary key order, so importing data in the primary key order can improve the efficiency of importing data. Turning off uniqueness checks before importing data can also improve the efficiency of importing.    14. Index 1. The data and indexes of the tables of the MyISAM storage engine are stored separately, and the data and indexes of the tables of the INNODB storage engine are stored in the same table space. 2. The most important condition for using an index in a query is that the index keyword is used in the query condition, and if it is a multicolumn index, the index can be used only if the query condition uses the leftmost prefix of the multi-column keyword, otherwise the index has a prefix attribute. 3. Detailed explanation of the Mysqlbtree index: 4. For a like query, the index can be used 5 if it is a constant and only% is not the first character. MySQL does not use indexes in the following cases: for example, using an index is slower than a full table scan, the hash index is not used in the WHERE condition =, the condition is separated by or, and the index is not used if the column in the condition before or is indexed and not in the subsequent column. 6. Use the show status like "handler_read%" to view index usage   15. Optimizes database object 1. Optimize the table's data type, use the Function procedure analyse to parse the table after the table has been running for a period of time, and refine the table's field type. 2. Split the table, including vertical splits and horizontal splits. The vertical split will require a join to query all the data, and the split of the query data will require union. Horizontal splitting can be by time or by a column of 3. When designing a table, you can add redundant fields appropriately, which reduces the need for connection operations and does not have to cling to database specifications. However, this may result in inconsistent data at the time of the update, so you need to update them regularly or use triggers.   16. Lock problem 1. Lock conflict is an important factor that affects the performance of database concurrent access. Therefore, it is important to understand whether the SQL statement is locked and what locks are added when executing. The main use of the MySQL table and InnoDB row lock. 2. mysql lock is divided into table-level lock, row-level lock, page lock. InnoDB supports row-level and table-level locks. MyISAM supports table locks. Table-level locks do not deadlock, but the locking granularity is large, the probability of a lock collision is the highest and the concurrency is the lowest. Row-level lock overhead, deadlock, lock granularity is the least, the probability of lock conflict is the lowest, the concurrency is also the highest. 3. MyISAM will automatically add read locks to all tables involved before executing the query select, and will automatically write locks to the tables involved before executing (update,delete,insert). However, an explicit lock must be manually unlocked, and after the lock tables is executed, only the explicitly lockedThese tables do not have access to the unlocked table. MyISAM always gets all the locks required by the SQL statement lock at once, so the MyISAM table does not deadlock. 4. Example of Myiasm table lock: 5. InnoDB lock problem.   17. Transaction 1. Transaction ACID Properties, Atomicity: A transaction is an atomic manipulation unit whose modifications to the data are either all executed or not executed. Consistency: The data must be in a consistent state at the beginning and completion of the transaction. Isolation: The database system provides a certain isolation mechanism to ensure that transactions are performed in an isolated environment that is not affected by external concurrency operations. Persistence: After a transaction is completed, it is permanently modified for the data and can be maintained in the event of a timely system failure. 2. Concurrent transactions increase the transaction throughput of the database system, which can support more users, but there are some problems, including: Update lost, Dirty read, non-repeatable read, Phantom read. 3. Update loss is the responsibility of the application, dirty read non-repeatable read and Phantom read, this is the database read consistency problem, must be provided by the database a certain transaction isolation mechanism to solve. The way the database realizes transaction isolation is basically divided into two kinds, one is lock, the other is MVCC (without lock, generate a consistent data snapshot of data request time point by a certain mechanism) 4. In order to deal with the contradiction between isolation and concurrency, there are 4 transaction isolation levels, Ru,rc,rr,s. The transaction isolation level corresponds to the example:  18. Backup and Recovery 1. MySQL's master-slave backup is slave MySQL is constantly doing binlog-based recovery. Master-Slave backups have multiple levels, such as row-level based, or SQL statement-level, or both 3. Backups are divided into logical and physical backups, and logical backups use the Mysqldump,myisam storage engine to lock up the data as they are backed up, and for InnoDB to get a snapshot through--single-transaction, This allows the backed up data to be consistent. Recovery is simple, directly mysqldump output as input execution. You can open the backup file to see the contents. 4. The export of the table can be select INTO Outfile,mysqldump. The import can be used with load data infile.   19. MySQL log 1. MySQL has four kinds of logs, namely error log, binary log (binlog log), query log and slow query log. 2. The error log is used to record problems with the database, and the error log should be checked first when the system fails.  3. Binary logs use Mysqlbinlog to view, Binlog is larger and need to be deleted periodically. In a master-slave backup environment, it is necessary to ensure that Binlog has been copied from the library to prevent backups from occurringProblem. 4. The query log records the client's query operations, and for frequently accessed systems, this log has a greater impact on system performance. Normally turn off 5. The slow query log records a log of SQL statements that contain all execution times that exceed the value set by the parameter long_query_time.   20. MySQL permissions 1. The permissions for MySQL are determined by the user name and IP address. MySQL database user,host,db tables      references: Books "In layman's, MySQL database development, optimization and management maintenance"  

MySQL Knowledge summary note