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

1. Introduction

The history of MySQL can be traced back to 1979, when a programmer named Monty Widenius worked for a small company of TcX and designed a reporting tool with BASIC so that it could run on computers with 4MHz host frequency and 16KB memory. At that time, it was just a very low-level and report-only storage engine called Unireg. [MySQL was called Unireg in the early days] in the early days, it developed into a Big Mac (Taobao, faceboock) MySQL AB is a company founded by the founder and main developer of MySQL. MySQL AB was originally founded by David Axmark, Allan Larsson and Michael "Monty" Widenius in Sweden.

SQL standard: ANSI SQL

SQL-86, SQL-89, SQL-92, SQL-99, SQL-03

SQL four languages: DDL,DML,DCL,TCL

DDL (Data Definition Language) data definition language

DML (Data Manipulation Language) data manipulation language

DCL (Data Control Language) database control language authorization, role control, etc.

TCL (Transaction Control Language) transaction control language

SAVEPOINT set SavePoint

ROLLBACK rollback

SET TRANSACTION

SQL is mainly divided into four parts:

(1) data definition. (SQL DDL) is used to define the creation and undo operations of SQL schemas, basic tables, views, and indexes.

(2) data manipulation. (SQL DML) data manipulation is divided into two categories: data query and data update. Data update is divided into three operations: insert, delete, and modify.

(3) data control. It includes authorization of basic tables and views, description of integrity rules, transaction control, and so on.

(4) the rules for the use of embedded SQL. Involves rules that SQL statements are embedded in the host language program.

Start port 3306 (connection pool authentication enters successfully, but does not exit successfully; (Check Memory-Caches) query cache to find the execution result of the sql statement directly, otherwise continue to generate query plan and parse sql statement; after optimization, find the statement in buffers cache, try to load buffers cache statement, and interact with io device through specific engine if there is no statement in buffers cache. )

The execution path of the query:

In my daily study and work, I am sure that I deeply feel the importance of data structures and algorithms. If you are willing to dig a little deeper, then all kinds of data structures and algorithms must be coming.

The classic BTREE index data structure is shown in the following figure:

B-Tree index is the most frequently used index type in MySQL database, and all storage engines except Archive storage engine support B-Tree index. Not only in MySQL, but also in many other database management systems, B-Tree index is also the most important index type. This is mainly because the storage structure of B-Tree index has excellent performance in database data retrieval.

Generally speaking, most of the physical files of the B-Tree index in MySQL are stored in the structure of Balance Tree, that is, all the actual data are stored in the Leaf Node of Tree, and the length of the shortest path to any Leaf Node is exactly the same, so we all call it the B-Tree index. It is possible that various databases (or MySQL's various storage engines) will slightly modify the storage structure when storing their own B-Tree indexes. For example, the actual storage structure used by the B-Tree index of the Innodb storage engine is actually B+Tree, that is, a small modification has been made on the basis of the B-Tree data structure. In addition to storing the relevant information of the index key on each LeafNode, it also stores the pointer information to the next LeafNode adjacent to the LeafNode, which is mainly to speed up the efficiency consideration of retrieving multiple adjacent LeafNode.

The B + tree is a balanced multi-tree, the height difference from the root node to each leaf node is less than 1, and the pointers of the nodes at the same level are linked to each other.

In the conventional retrieval on the B+ tree, the search efficiency from the root node to the leaf node is basically the same, and there is no significant fluctuation, and when scanning sequentially based on the index, the bi-directional pointer can also be used to move quickly from left to right, which is very efficient.

Therefore, B+ tree index is widely used in database, file system and other scenarios. By the way, one of the reasons why xfs file system is much more efficient than ext3/ext4 is that its file and directory index structure all uses B+ tree index, while ext3/ext4 's file directory structure uses Linked list, hashed B-tree, Extents/Bitmap and other index data structures, so under high Imax O pressure, its IOPS capability is not as good as xfs.

Lex & Yacc is a tool for generating lexical parsers and parsers, which simplify the writing of compilers. Lex & Yacc were released by Mike Lesk and Stephen C. Johnson of Bell Laboratories in 1975.

MySQL transactions:

Transaction: a set of atomic SQL queries, or a separate unit of work. Transaction log: ACID test: A:atomicity, atomicity; all operations in the entire transaction are either executed successfully or rolled back after failure; C:consistency, consistency; database always transitions from one consistency state to another; I:Isolation, isolation; operations made by one transaction cannot be seen by others until committed; isolation has multiple isolation levels D:durability: persistent; once a transaction commits, its changes are permanently saved in the database; transaction: start transaction: START TRANSACTION end transaction: (1) COMMIT: commit (2) ROLLBACK: rollback Note: only the transactional storage engine can support such operations; recommendation: explicitly request and commit transactions instead of using the "autocommit" feature Autocommit= {1 | 0} transactions support savepoint SAVEPOINT identifier ROLLBACK [WORK] TO [SAVEPOINT] identifier RELEASE SAVEPOINT identifier transaction isolation level: READ UNCOMMITTED (read uncommitted) [dirty, non-repeatable, phantom] READ COMMITTED (read commit) [non-repeatable, Phantom] REPEATABLE READ (rereadable) [Phantom] [default [InnoDB] isolation level used by the MySQL storage engine "re-readable"] SERIALIZABLE (serializable) [locked read] there may be a problem: dirty read Unrepeatable reading; phantom reading; locked reading

MySQL user and Rights Management:

Permission category: library level, table level, field level, management class, program class management class: CREATE TEMPORARY TABLES CREATE USER FILE SUPER SHOW DATABASES RELOAD SHUTDOWN REPLICATION SLAVE REPLICATION CLIENT LOCK TABLES PROCESS program class: FUNCTION PROCEDURE TRIGGER CREATE, ALTER, DROP EXCUTE library and table level: TABLE or DATABASE ALTER CREATE CREATE VIEW DROP INDEX SHOW VIEW GRANT OPTION: you can give other users a copy of your permissions. Data manipulation: SELECT INSERT DELETE UPDATE field level: SELECT (col1,col2,...) UPDATE (col1,col2,...) INSERT (col1,col2,...) All limited: ALL PRIVILEGES, ALL metadata database: mysql authorization table: db, host, user columns_priv, tables_priv, procs_priv, proxies_priv user account: 'USERNAME'@'HOST': @' HOST': hostname IP address or Network; wildcard:%, _: 172.16%.% create user: CREATE USER CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY' password'] View the user's authorization: SHOW GRANTS FOR SHOW GRANTS FOR 'USERNAME'@'HOST' user rename: RENAME USER RENAME USER old_user_name TO new_user_name delete user: DROP USER' USERNAME'@'HOST' change password: (1) SET PASSWORD FOR (2) UPDATE mysql.user SET password=PASSWORD ('your_password') WHERE clause (3) mysqladmin password mysqladmin [OPTIONS] command command.... -u,-h,-p solution for forgetting the administrator password: (1) when starting the mysqld process, use-- skip-grant-tables-- skip-networking (2) use the UPDATE command to modify the administrator password (3) close the mysqld process, remove the above two options, and restart the mysqld; authorization: GRANT GRANT priv_type [,...] ON [{table | function | procedure}] db. {table | routine} TO 'USERNAME'@'HOST' [IDENTIFIED BY' password'] [REQUIRE SSL] [WITH with_option] with_option: GRANT OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count revoke authorization: REVOKE REVOKE priv_type [(column_list)] [ Priv_type [(column_list)]]... ON [object_type] priv_level FROM user [, user].. .2, characteristics of each storage engine InnoDB: transaction: transaction log foreign key: MVCC: clustered index: index other than clustered index Commonly referred to as secondary index row-level locks: gap locks support secondary indexes support adaptive hash indexes support hot backup MyISAM: full-text index compression: for data warehouse implementation Can save storage space and improve performance space indexing table-level locks delayed update indexes do not support transactions, foreign keys, and row-level locks cannot safely recover data after crashes: read-only data, smaller tables, Ability to tolerate post-crash modification and data loss ARCHIVE: only INSERT and SELECT are supported Support good compression function Suitable for storing log information or other data collection applications implemented according to time series; does not support transactions, does not support indexes; CSV: stores data in CSV format; does not support indexes; applies only to data exchange scenarios; BLACKHOLE: there is no storage mechanism, any data sent to this engine will be discarded It records binary logs, so it is often used as a transit server in multi-level replication architecture; MEMORY: keep data in memory, memory tables; often used to save intermediate data, such as periodic aggregate data, etc. It is also used to implement temporary tables that support hash indexes, use table-level locks, and do not support BLOB and TEXT data types MRG_MYISAM: a variant of MYISAM that can merge multiple MyISAM tables into a single virtual table; NDB: a third-party storage engine dedicated to MySQL CLUSTER: OLTP class: XtraDB: enhanced InnoDB, provided by Percona When compiling and installing, download the source code of XtraDB to replace the source code of InnoDB in MySQL storage engine: MariaDB comes with this storage engine to support engine-level replication and foreign key constraints, and provide appropriate support for SSD disks; support transactions, MVCC TokuDB: use Fractal Trees index, suitable for storage big data, with a good compression ratio Has been introduced into MariaDB; determinant storage engine: Infobright: a well-known determinant engine, suitable for mass data storage scenarios, such as PB level, designed for data analysis and data warehouse InfiniDB MonetDB LucidDB open source community storage engine: Aria: formerly known as Maria, can be understood as an enhanced version of MyISAM (supports secure recovery after a crash, supports data caching) Groona: full-text indexing engine, Mroonga is a secondary development version of Groona-based OQGraph: developed by Open Query Storage engine SphinxSE that supports graph structure: provides SQL interface for Sphinx full-text search server Spider: can split data into different slices, implement shared efficiently and transparently, and support parallel queries on shards 3. MySQL data type

Defining the type of data field in MySQL is very important for optimizing your database. MySQL supports a variety of types, which can be roughly divided into character type, numeric type, date-time type and built-in type. It can also be divided into three categories: numeric, date / time, and string (character) types.

Character type: CHAR, BINARY: fixed length data type; [CHAR is case insensitive, BINARY is case sensitive] VARCHAR, VARBINARY: variable length data type; requires Terminator; TEXT:TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT BLOB: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB ENUM, SET

The ENUM type is somewhat similar to a single option because only one value is allowed in the collection. The SET type is similar to but not the same as the ENUM type. The SET type can get any number of values from a predefined collection.

Numerical: exact numerical: integer: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT decimal: DECIMAL [usually used in the financial field is decimal] approximate numerical floating point: FLOAT DOUBLE BIT [generally not recommended]

Date and time type: DATETIME DATETIME TIMESTAMP YEAR (2), YEAR (4)

Character type modifier: NOT NULL: non-empty constraint; NULL: DEFAULT 'STRING': indicates the default value; CHARACTER SET'': the character set used COLLATION: collation used integer data modifier: NOT NULL NULL DEFAULT NUMBER AUTO_INCREMENT: UNSIGNED PRIMARY KEY | UNIQUE KEY NOT NULL mysql > SELECT LAST_INSERT_ID () Date and time modifiers: NOT NULL NULL DEFAULT built-in types SET and ENUM modifiers: NOT NULL NULL DEFAULT4, SQL MODE

Commonly used SQL MODE are: TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES. SQL_MODE may be an easy variable for developers and DBA to ignore, and it is empty by default. The setting of SQL_MODE is a risky one, because it allows illegal operations, such as inserting NULL into NOT NULL fields or illegal dates, such as "2019-12-33". Therefore, it is strongly recommended that developers set this value to strict mode in a production environment, so that some problems can be found during the design and development phase of the database, and if such problems are found after running the database in a production environment, then the cost of modification will become huge.

The common values for sql_mode are as follows: ONLY_FULL_GROUP_BY: for GROUP BY aggregation operations, if the column in SELECT does not appear in GROUP BY, then the SQL is illegal because the column is not in the GROUP BY clause NO_AUTO_VALUE_ON_ZERO: this value affects the insertion of self-growing columns. By default, inserting 0 or NULL means the next self-growth value is generated. This option is useful if the user wants to insert a value of 0 and the column is self-growing. STRICT_TRANS_TABLES: in this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted and no restrictions are imposed on the non-transaction table NO_ZERO_IN_DATE: in strict mode, zero dates and months are not allowed to be zero no _ ZERO_DATE: set this value, the mysql database does not allow the insertion of zero dates, and inserting zero dates throws errors rather than warnings. ERROR_FOR_DIVISION_BY_ZERO: in the INSERT or UPDATE process, if the data is divided by zero, an error is generated instead of a warning. If the mode is not given, MySQL returns NULLNO_AUTO_CREATE_USER when the data is divided by zero: forbids GRANT to create a user NO_ENGINE_SUBSTITUTION with an empty password: throws an error if the required storage engine is disabled or uncompiled. When this value is not set, replace it with the default storage engine and throw an exception PIPES_AS_CONCAT: treat "| |" as the concatenation operator of the string rather than the OR operator, which is the same as the Oracle database and similar to the string concatenation function Concat: when ANSI_QUOTES is enabled, the string cannot be referenced in double quotation marks Because it is interpreted as the sql_mode setting of the identifier ORACLE is equivalent: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.5, SQL syntax

Create a database

Use the create database statement to create the database. The format of the creation command is as follows: create database database name [other options]

Create a database table

Use the create table statement to complete the creation of the table, a common form of create table: create table table name (column declaration)

Insert data into the table

Insert statements can be used to insert one or more rows of data into a database table in the following general form: insert [into] table name [(column name 1, column name 2, column name 3,...)] Values (value 1, value 2, value 3,...)

Query the data in the table

Select statement is often used to get data from the database according to certain query rules. Its basic usage is: select column name from table name [query condition]

Query by specific criteria:

The where keyword is used to specify query conditions in the form of select column name from table name where condition

Update the data in the table

The update statement can be used to modify the data in a table. The basic form of use is: update table name set column name = new value where update condition

Delete data from a table

The delete statement is used to delete data from a table. The basic usage is: delete from table name where delete condition

Modification of the table after creation

Add column basic form: alter table table name add column name column data type [after insert location]; modify column basic form: alter table table name change column name new data type; delete column basic form: alter table table name drop column name; rename table basic form: alter table table name rename new table name; delete the whole table basic form: drop table table name; delete the whole database basic form: drop database database name

The execution flow of the SELECT statement:

FROM Clause-- > WHERE Clause-- > GROUP BY-- > HAVING Clause-- > ORDER BY-- > SELECT-- > LIMIT

Single table query:

SELECT

[ALL | DISTINCT | DISTINCTROW] [SQL_CACHE | SQL_NO_CACHE] select_expr [, select_expr...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC],... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC],...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [FOR UPDATE | LOCK IN SHARE MODE] DISTINCT: data deduplication; SQL_CACHE: explicitly specify that query results are stored in cache; SQL_NO_CACHE: explicit query results are not cached; query cache is turned on when the value of query_cache_type is' ON' The results of SELECT will be cached if they meet the caching conditions, otherwise, they will not be cached; if SQL_NO_CACHE is explicitly specified, no caching will be done; when the value of query_cache_type is' DEMAND', the query caching function will be carried out as needed; only the SELECT statements of explicitly specified SQL_CACHE will be cached; others will not be cached Field display can use aliases: col1 AS alias1, col2 AS alias2,... WHERE clause: indicates the filter condition to achieve the "select" function: filter condition: Boolean expression; arithmetic operator: +, -, *, /,% comparison operator: =,! =, >, > =, 30; federated query: UNION

Design specifications for SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;6 and MySQ

Database design follows the requirements of the third normal form (3NF) as far as possible. That is, a table contains only its own basic attributes, which need to be decomposed when they are not their own attributes. The relationships between tables are connected by foreign keys. It has the following characteristics: there is a set of tables dedicated to storing associated data connected by keys.

For those who cannot follow the third paradigm due to efficiency and other reasons, the reason should be clearly stated in the design document and how to maintain the consistency of the data.

1 naming convention (1) Database names, table names, and field names must be in lowercase letters and separated by an underscore "_". (2) Database name, table name and field name are prohibited to exceed 32 characters. It is recommended that nouns are not verbs. (3) MySQL reserved words are prohibited for database names, table names and field names. (4) the temporary database name and table name must be prefixed with tmp and suffixed with date. (5) the backup database name and table name must be prefixed with bak and suffixed with date. 2 basic specification (1) use INNODB storage engine (2) table character set to use UTF8 (3) all tables need to be annotated (4) the amount of data in a single table is recommended to be less than 5000W (5) database tables are recommended not to store drawings, files, etc. Big data (6) prohibit database stress test operations online (7) all fields must be defined as not null The default value 3 database table design can be specified according to the business. (1) the library name format is organization _ xxx [_ xxx]. For example, take the toon foundation as an example, where "toon_" is prefixed and xxx is the actual database name, using the project name field design of each module. (2) the words contained in the table name are all in singular form, and "_" is used to divide multiple words. (3) the same business module table. It is recommended to add module abbreviations before the table name. Example: customer table: cust_customer customer contact information: cust_contact (4) sub-table naming rules: originally indicated _ number, example: cust_customer _ 01 (5) all tables add comments. (6) all tables must explicitly specify a primary key. (7) A single table controls the number of fields and the upper limit of 30 fields. (8) it is recommended to limit the amount of data in a single table to less than 5000W for simple field types, and 2000W for complex field types such as int,tinyint,bigint, such as varchar (n > 2048), text, etc. (9) naming rules for associated tables: table a _ table b, if there are module abbreviations According to the actual business needs to retain a module abbreviation example: tenant relations table: prt_property_customer4 field design type byte signed / unsigned minimum TINYINT 1 have-128 127 no 0 255SMALLINT 2 have-32768 32767 no 0 65535MEDIUMINT 3 have-8388608 8388607 no 0 16777215INT 4 have-2147483648 2147483647 Zero 4294967295BIGINT 8 has the principle of-9223372036854775808 9223372036854775807 without 018446744073709551615 (1) Select the appropriate data type. The smaller the number of bytes occupied by the data type, the better. For the values of numeric types, please refer to the following table: if the upper limit of the primary key value does not exceed 4.2 billion, it is recommended that you do not use BIGINT (2) as simple as possible. Fields can be numeric instead of characters. (3) avoid using the text/blob data type as much as possible, and if necessary, update it according to the access frequency to see if it is necessary to split it from the main table. (4) use TINYINT instead of ENUM and SET. (5) string type, relatively small and fixed, such as MD5 value, select CHAR, otherwise select VARCHAR. Using the smallest possible VARCHAR field, the N in VARCHAR (N) represents the number of characters rather than bytes, with a Chinese character occupying three bytes and a letter occupying one byte. (6) the field must specify NOT NULL. (7) id primary keys are recommended for common field types: bigint or int, which is self-increasing depending on the data growth range; phone:varchar (15); email:varchar (254); ZIP code: varchar (11); enumerated data: tinyint;url:varchar (2083); img:varchar (2083); IP:varchar (45) or transformed storage; Money:DECIMAL (1910); Longitude:DECIMAL (9); Latitude:DECIMAL (8). 5 Index design (1) non-unique indexes must be named according to "idx_ field name _ field name [_ field name]". (2) the unique index must be named according to "uniq_ field name _ field name [_ field name]". (3) to control the number of indexes, no more than 5 indexes per table and no more than 5 index fields per table. (4) the index is as strong as possible on highly differentiated columns and does not build indexes on low differentiated columns, such as gender. (5) do not build indexes on columns that are updated frequently, and do not perform mathematical and functional operations on index columns. (6) create a joint index reasonably (avoid redundancy), (a) (a), (b), (7) important SQL must be indexed, WHERE conditional columns of UPDATE and DELETE statements, ORDER BY, GROUP BY, DISTINCT fields, multi-table JOIN fields (8) queries that do not use% prefixes, such as like "% ab" Do not use negative queries, such as not in/like (it is recommended to consider full-text search sphinx). 6 SQL design (1) the sql statement is as simple as possible, and the large sql is split into small sql statements according to the business (make full use of QUERY CACHE and make full use of multicore CPU). (2) limit pagination should pay attention to efficiency. The larger the limit, the lower the efficiency. You can rewrite limit, such as example rewriting: select id from t limit 10000, 10; = > select id from t where id > 10000 limit10. (3) reduce the number of interactions with the database and use batch sql statements as much as possible. (4) pay attention to the use of the performance analysis tool Sql explain / showprofile / mysqlsla (5) it is recommended that all SQL keywords are capitalized, and only one space is allowed for each word. (6) SQL statements cannot be implicitly converted, for example, the amount of data in the select id from table where id='1' (7) IN condition is less, and using exist instead of in,exist in some scenarios will be faster than in when both can be used. It is suggested that the trade-off should be made by implementing the plan. (8) it is forbidden to run large queries in the database. (9) if you don't need NOT IN, you don't need NOT IN. (10) Fuzzy prefix matching operations are not recommended in SQL statements, such as like (11) for paging queries: programs recommend reasonable use of paging to improve efficiency limit,offset should match subqueries (12) use precompiled statements, passing only parameters, which is more efficient than passing SQL statements; parsing multiple times Reduce SQL injection probability 7 Code of Conduct (1) Database design must involve DBA, table structure changes must notify DBA; (2) batch import, export or batch update data, must be reviewed and executed by DBA; (3) it is recommended that multiple alter operations on the same table be merged into one operation; (4) do not store business logic in the MySQL database (5) it is recommended to prohibit the use of stored procedures, functions and triggers. 7. Frequently used command 1. Delete all anonymous users mysql > DROP USER'@ 'localhost'; mysql > DROP USER' @ 'www.xxx.com' The user account consists of two parts: username@host host can also use wildcards:%: any character of any length _: match any single character 2, set a password for all root users: the first way: mysql > SET PASSWORD FOR username@host = PASSWORD ('your_passwrod') The second way: mysql > UPDATE user SET password = PASSWORD ('your_password') WHERE user =' root'; mysql > FLUSH PRIVILEGES;. The third way: # mysqladmin-uUserName-hHost password' new_password'-p # mysqladmin-uUserName-hHost-p flush-privileges

Create a new user # create a user and set a password

CREATE USER 'New user' @ 'localhost' IDENTIFIED BY' New password'

Authorize users

GRANT ALL PRIVILEGES ON. TO 'New user' @ 'localhost' WITH GRANT OPTION

Connect to the MySQL server mysql client mysqld mysqld to receive connection requests: local communication: the client and server are on the same host and communicate based on 127.0.0.1 (localhost) address or lo interface Linux OR Unix: Unix Sock, / tmp/mysql.sock, / var/lib/mysql/mysql.sock Windows: memory, pipe remote communication: client and server are located on different hosts Or use non-loopback address communication TCP socket client tools on the same host: mysql, mysqladmin, mysqldump, mysqlcheck [client] options:-u,-- user=-h,-- host=-p -- password=-- protocol= {tcp | socket | memory | pipe}-- port=-- socket= for example: / tmp/mysql.sock [note that no spaces are concatenated after the format-u and-p. If the password does not want to be displayed, it can be as follows: mysql-uroot-p enter password] Port mysql listens: 3306/tcp non-client class management tool: myisamchk, myisampack mysql working mode: interactive mode mysql > script mode mysql

Help mysql >\?\ c\ g\ G\ Q\!\ s\. / path/to/mysql_script.sql server-side command: command Terminator is required, default is semicolon ( ) mysql > help contents mysql > help Keryword mysql command line options:-- compress-- database=,-D-H,-- html: output document in html format-X,-- xml: output format is xml-- sate-updates: reject update or delete commands without where clause Mysql command prompt: mysql > wait for commands->'> "> `> / * > shortcut for mysql: Ctrl+w: delete the word before the cursor Ctrl+u: delete everything before the cursor to the beginning of the command line Ctrl+ y: paste inside deleted using Ctrl+w or Ctrl+u Allow Ctrl + a: move the cursor to the beginning of the line Ctrl + e: move the cursor to the end of the line 8, MySQL optimization

MySQL optimization needs to be coordinated at three different levels: MySQL level, OS level, and hardware level. MySQL-level optimization includes table optimization, query optimization and MySQL server configuration optimization, and various data structures of MySQL ultimately act on OS and hardware devices, so it is also necessary to understand the needs of each structure for OS-level resources and ultimately lead to CPU and Icano operations, etc., and on this basis, reduce the need for CPU and Icano operations as much as possible to improve their efficiency.

The optimization focus at the database level:

1. Whether the relevant properties of the table structure are set correctly, especially whether the field type of each field is the best. 2. Whether an appropriate index is created for efficient query. 3. Whether the appropriate storage engine is selected for each table, and make effective use of the advantages and characteristics of the selected storage engine. 4. Whether the appropriate row format (row format) is selected for the table based on the storage engine. 5. Whether the appropriate locking strategy is used, such as using shared locks in concurrent operation scenarios and using exclusive locks for higher priority requirements. 6. Whether the appropriate memory space is set for InnoDB buffer pool, MyISAM key cache and MySQL query cache so that frequently accessed data can be stored without causing page swapping out. Because MySQL cannot run efficiently on multi-CPU, and its support for the number of CPU is also limited. In general, newer versions can support 16 to 24 CPU or more. 2. Whether you have the right size of physical memory, and balance the memory and disk resources through reasonable configuration, so as to reduce or even avoid disk Ibank O. Caching can effectively delay and optimize writes, but it can also eliminate writes and comprehensively consider the scalability of storage space. It is also very important to select reasonable external storage devices for business. 3. Whether the appropriate network equipment is selected and the network is configured correctly also has a great impact on the overall system. Delay and bandwidth are the limiting factors of network connection, while common network problems, such as packet loss, even a small packet loss rate will agree to a significant decline in performance. What is more important is to adjust the network settings in the system as needed to efficiently handle a large number of connections and small queries. 4. Whether the appropriate file system is selected based on the operating system. At the same time, turning off certain features of the file system, such as access time and read-ahead behavior, and choosing a reasonable disk scheduler will often help improve performance. 5. MySQL uses a separate thread in response to each user connection, plus internally used threads, special purpose threads and any other threads created by the storage engine. MySQL needs to manage a large number of threads effectively. The NPTL thread library on Linux systems is lighter and more efficient. Thread pool plug-ins have been introduced in MySQL 5.5, but its utility is unclear.

Communicate with Aliyun database boss over the phone and ask the and Google solution and about the boss in the group. The summary is as follows (all essence):

1. Performance should be considered when designing databases and creating tables

The compilation of 2.sql needs to be optimized.

3. Partition [the partition introduced by MySQL in version 5.1 is a simple horizontal split. Users need to add partition parameters when creating the table, which is transparent to the application without modifying the code]

4. Sub-table [sub-table is a large table, optimized according to the above process, or the query is stuck, then divide the table into multiple tables, divide a query into multiple queries, and then return the results to the user. ]

5. Sub-library [divide a database into multiple, it is recommended to do a read-write separation on the line, the real sub-library will also bring a lot of development costs, the loss outweighs the gain! It is not recommended. ]

First, optimize sql and index; second, add cache, after memcached,redis; has done more than the third, or slow, make master-slave replication or master-master replication, read-write separation, can be done at the application layer, high efficiency, you can also use three-party tools, the third-party tools recommend atlas, the rest is either inefficient or unmaintained Fourth, if it is still slow to do all the above, do not think about sharding. Mysql comes with its own partition table. Try this first. It is transparent to your application and does not need to change the code, but the sql statement needs to be optimized for the partition table. The sql condition should include columns with partition conditions, so that the query is located to a small number of partitions, otherwise all partitions will be scanned. Fifth, if all the above is done, then do the vertical split first, which is to divide a large system into several small systems, that is, distributed systems, according to the coupling of your modules. The sixth is horizontal segmentation. For tables with a large amount of data, this step is the most troublesome and can best test the technical level. It is necessary to choose a reasonable sharding key. In order to have good query efficiency, the table structure should also be changed, some redundancy should be made, and the application should also be changed. Sql should take sharding key as far as possible, locate the data to a limited table, instead of scanning all tables.

Optimize the relevant parameters:

Query which cpu mysql is running on. A process can only run on one CPU.

Query cache flowchart:

Turn on caching

Mysql > show variables like'% query%'; query cache

Set global query_cache_size = 16777216; change the query cache

Slow query log

Open table action cache

Number of connections adjustment

Cache innidb tablespace [default 8m theory, the larger the better, the better to set 80% of the overall memory]

Connection timeout [when the network communication is bad or the load on the back-end is heavy, this value will be adjusted higher, otherwise many front-end computers will not be connected]

Check the innodb transaction log mode [this value has a great impact on IO. The value that determines how the innodb transaction log is written to disk is only 0 1 / 2 (it is recommended to change it to 2)]

Query related status variables

Evaluation of cache hit ratio: Qcache_hits/ (Qcache_hits+Com_select)

9. Summary

Data analysis is inseparable from SQL tools. The ability to use SQL is a basic ability of data analysis, which is the core reason for learning SQL. As long as you have the idea of data analysis, you must learn it in a down-to-earth manner. The era of big data is the first book I read about the trend of the Internet. it grasps the era from a macro level, starts with the characteristics of big data, and tells about big data's changes in our life, work, and even thinking. Specifically include: big data's concept, its value, the transformation it brings and the way it changes, its negative impact, a series of norms of this era, and so on. In today's era when big data is popular, NoSQL:redis, mongodb, hbase, NewSQL:TiDB and relational database MySQL all play an important role in each business level. Whether it is big data or small data, it is necessary to master a set of data management system, the current MySQL is still quite popular in the open source world, and even some state-owned enterprises are beginning to go to IOE, so it is still necessary to learn deeply, in line with the purpose of reviewing the past and knowing the new, sort out the relevant knowledge of MySQL into a new chapter step by step.

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.