Personal tech blog: www.zhenganwen.top
This article summary
An overview of the
Why optimize
- The throughput bottleneck of the system often appears in the database access speed
- As the application runs, more and more data is stored in the database, and processing times are correspondingly slower
- Data is stored on disk and read and write speeds are not comparable to memory
How to optimize
- Design database: database table, field design, storage engine
- Take advantage of MySQL’s own features, such as indexes
- Scale-out: MySQL cluster, load balancing, read/write separation
- SQL statement optimization (with little success)
Field design
Selection of field types, design specifications, paradigms, common design cases
Rule: Always use integers to represent strings
Store the IP
INET_ATON(STR), address to number
INET_NTOA(number), number to address
Enumeration (single) and collection (multiple) types within MySQL
However, because of the high maintenance cost, it is not often used. Associated tables are used instead of enum
Principle: choice of fixed-length and non-fixed-length data types
Decimal does not lose precision, and storage space increases as data increases. Double takes up a fixed amount of space, and larger numbers lose accuracy. There are also vARCHar and text of indefinite length
The amount of
High requirements on the accuracy of data, and there are precision problems in the operation and storage of decimals (cannot convert all decimals to binary)
Fixed-point decimal number
Price decimal(8,2) a fixed-point number with 2 decimal places, which supports large numbers (even numbers beyond the storage range of int,bigint)
Small units and large amounts to avoid decimals
Yuan – > points
String storage
Fixed size char, non-fixed size vARCHar, text (up to 65535, where varchar consumes 1 to 3 bytes of record length, while text uses extra space for record length)
Rule: Choose small data types and specify short lengths whenever possible
Rule: Use not NULL whenever possible
Handling non-NULL fields is more efficient than handling null fields! And you do not need to check whether it is null.
Null is not easy to handle in MySQL because it requires extra space for storage and special operators for operations. For example, if select NULL = NULL and SELECT NULL <> NULL (<> is not NULL) have the same result, you can determine whether a field is NULL only by using is NULL and is not NULL.
How to store it? Each record in MySQL requires additional storage space to indicate whether each field is null. Therefore, special data is usually used for placeholder, such as int not NULL default 0, string not NULL default ‘ ‘.
Principle: Field comments to complete, see the meaning
Rule: Do not have too many fields in a single table
Twenty or thirty is the limit
Principle: Fields can be reserved
Business requirements must be met before using the above principles
Design of associated tables
A foreign key can only be mapped one-to-one or one-to-many
More than a pair of
Using the foreign key
Many to many
Create a separate table to split many-to-many into two one-to-many
One to one
For example, basic information for an item (item) and details for an item (item_intro), usually use the same primary key or add a foreign key field (item_id)
Paradigm in Normal Format
Specifications for the design of data tables, a system of increasingly strict specifications (n-1 if n-normal is required). N
First Normal Form 1NF: Field atomicity
Field atomicity, the field is not separable.
Relational databases, by default, meet the first normal form
Note the error-prone use of commas to separate multiple foreign keys in a one-to-many design, which is easy to store but bad for maintenance and indexing (for example, finding tagged Java articles)
Second normal Form: Removing partial dependence on primary keys
That is, add a field to the table that has nothing to do with business logic as the primary key
Primary key: A field or set of fields that can uniquely identify a record.
| course_name | course_class | Weekday | course_teacher |
|---|---|---|---|
| MySQL | Education Building 1525 | Monday | Zhang SAN |
| Java | Education Building 1521 | Wednesday | Li si |
| MySQL | Education Building 1521 | Friday | Zhang SAN |
Dependency: Field A can determine field B, so field B depends on field A. For example, knowing that the next class is math, you can determine who the teacher will be. So the day of the week and the next class can form a composite primary key, which classroom to go to, who the teacher is, etc. But we often remove some of the dependence on the primary key by adding an ID as the primary key.
Partial dependence on primary keys: A field depends on a part of a composite primary key.
Solution: Add a separate field as the primary key.
Third normal Form: Eliminate transitive dependence on primary keys
Pass dependencies: the B field depends on A, and the C field depends on B. For example, in the previous example, who the teacher is depends on what class it is, which class it is depends on the primary key ID. Therefore, this table needs to be split into two tables: schedule and class schedule (independent data and independent table) :
| id | weekday | course_class | course_id |
|---|---|---|---|
| 1001 | Monday | Education Building 1521 | 3546 |
| course_id | course_name | course_teacher |
|---|---|---|
| 3546 | Java | Zhang SAN |
This reduces data redundancy (even though there are Java classes every day from Monday to Sunday, only course_id:3546 occurs 7 times)
Storage Engine Selection
Early questions: How to choose MyISAM and Innodb?
That’s no longer a problem, Innodb is improving and overtaking MyISAM in every way, and MySQL uses it by default.
Storage Engine: How data, indexes, and other objects are stored in MySQL is an implementation of a file system.
Functional differences
show engines
| Engine | Support | Comment |
|---|---|---|
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys |
| MyISAM | YES | MyISAM storage engine |
Store the differences
| MyISAM | Innodb | |
|---|---|---|
| The file format | Data and indexes are stored separately, data.MYDIndex,.MYI |
Data and indexes are stored centrally,.ibd |
| Can the file be moved | Yes, a list of them.frm,MYD,MYIThree files |
No, because it’s also relateddataOther files under |
| Record storage sequence | Save in record insertion order | Order insert by primary key size |
Space debris (delete records andFlush the table table nameAfter that, the table file size stays the same.) |
Is generated. Scheduled collation: Use commandsOptimize the table table nameimplementation |
Do not produce |
| The transaction | Does not support | support |
| A foreign key | Does not support | support |
| Lock support (locks are a mechanism to avoid contention, MySQL locks are almost transparent to users) | Table level lock | Row-level locking and table-level locking, with small locking force and high concurrency |
Lock extension
Table level lock: lock tables
,
… Read /write, unlock tables
,
… . Read is a shared lock. Once locked, no client can read it. Write is an exclusive/write lock. Only the locked client can read or write. Other clients cannot read or write. A table or tables are locked.
Row-level lock: Locks one or more rows of records. Select * from
where < conditional > LOCK IN SHARE MODE; To add a shared lock to the query record; Select * from
where < condition > FOR UPDATE; To add an exclusive lock to the query record. Innodb row lock is a sub-range lock, which is not mapped to a specific row, but a sub-range lock. For example, select * from stu where ID < 20 LOCK IN SHARE MODE locks the range below 20. You may not be able to insert a new record with id 18 or 22.
Selection basis
If there are no specific requirements, use the default Innodb.
MyISAM: Read-write and insert-oriented applications, such as blogging systems, news portals.
Innodb: Updates (deletes) frequently, or to ensure data integrity; High concurrency, support transactions and foreign keys to ensure data integrity. For example, OA office automation system.
The index
The mapping between keywords and data is called an index (== contains the keyword and the corresponding address of the record on disk ==). Keywords are specific content extracted from data to identify and retrieve data.
Why is index retrieval fast?
- Keyword relative to data, == Small amount of data ==
- The keyword is == ordered ==, binary search can quickly determine the position
The library uses indexes for each book (category, floor, shelf) and dictionaries to compile lists of words in alphabetical order.
Index type in MySQL
Common key, unique key, primary key, fulltext key
The three indexes are indexed in the same way, but there are different restrictions on the key of the index:
- Plain index: There are no restrictions on keywords
- Unique index: It is required that records provide keys that cannot be duplicated
- Primary key index: The key must be unique and not null
Index management syntax
View index
Show create table name:
Desc table name
Create indexes
Create indexes after the table is created
create TABLE user_index(
id int auto_increment primary key,
first_name varchar(16),
last_name VARCHAR(16),
id_card VARCHAR(18),
information text
);
Change the table structure
alter table user_index
Create a composite index of first_name and last_name and name it name
add key name (first_name,last_name),
Create a unique index for id_card using the field name by default
add UNIQUE KEY (id_card),
-- Chicken ribs, full text index does not support Chinese
add FULLTEXT KEY (information);
Copy the codeShow create table user_index:
Specify the index when creating the table
CREATE TABLE user_index2 (
id INT auto_increment PRIMARY KEY,
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
information text.KEY name (first_name, last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);
Copy the codeRemove the index
Drop a normal, unique, or full-text index by index name: ALTER TABLE table name drop KEY index name
alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;
Copy the codeAlter table table name drop primary key; It is worth noting here that this operation cannot be performed directly if the primary key grows (self-growth depends on the primary key index) :
Need to cancel self-growth and then delete:
alter table user_index
Redefine fields
MODIFY id int.drop PRIMARY KEY
Copy the codeHowever, primary keys are usually not removed because design primary keys must be independent of business logic.
Execution plan Explain
CREATE TABLE innodb1 (
id INT auto_increment PRIMARY KEY,
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
information text.KEY name (first_name, last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);
insert into innodb1 (first_name,last_name,id_card,information) values ('张'.'三'.'1001'.'Huashan School');
Copy the codeWe can analyze the execution plan before the SQL statement is executed by explaining selelct:
As you can see from the figure above, this SQL statement is retrieved by primary key index.
The execution plan is: when an SQL statement is executed, it is analyzed and optimized to form an execution plan and then executed according to the execution plan.
Index Usage scenarios (emphasis)
where
In the figure above, the record is queried by ID. Because the ID field is only the primary key index, this SQL execution can select only the primary key index. If there are more than one, it will eventually select the better one as the basis for the retrieval.
Add a field that is not indexed
alter table innodb1 add sex char(1);
-- Optional index null for sex search
EXPLAIN SELECT * from innodb1 where sex='male';
Copy the code
Alter table add index(alter table name add index(alter table name add index(alter table name add index(alter table name add index(alter table name add index(alter table name add index(alter table name add index(alter table name add index)))
order by
When we use order BY to sort the query results by a field, if the field is not indexed, then the execution plan will use external sort for all the queried data. This operation can affect performance. Because all the data involved in the query needs to be read from disk to memory (if a single data is too large or too much data will reduce efficiency), let alone the sorting after reading to memory.
However, if we create an index for this field alter table add index(field name), then because the index itself is ordered, so directly according to the order of the index and mapping relationship can be fetched one by one. And if paging, then only the index in a range of the index table corresponding to the data, rather than fetching all the data to sort and return a range of data as described above. (Fetching data from disk is the most performance critical)
join
It is efficient to index the fields involved in the join statement matching relation (ON)
Indexes cover
If the fields to be queried are all indexed, the engine will query directly in the index table without accessing the raw data (otherwise, it will do a full table scan whenever a field is not indexed), which is called index overwrite. Therefore, we need to write only the necessary query fields == after select as much as possible to increase the chance of index coverage.
It’s worth noting here that you don’t want to index every field, because the advantage of using indexes in preference is their small size.
Grammar details (key points)
Indexes are not necessarily used in cases where they are used (where/ Order BY /join on or index overrides)
Fields should appear independently
For example, the following two SQL statements are semantically identical, but the first one uses a primary key index and the second one does not.
select * from user where id = 20- 1;
select * from user where id+1 = 20;
Copy the codelikeQuery, cannot start with a wildcard character
For example, search for articles with mysql in the title:
select * from article where title like '%mysql%';
Copy the codeThis kind of SQL execution plan does not use index (like statement match expression begins with wildcard character), so it can only do full table scan, which is very inefficient and almost not used in practical projects. And generally will use the third party to support Chinese full-text index to do.
But the keyword query hot search reminder function can still be done, such as after typing mysql to remind mysql tutorial, mysql download, mysql installation steps, etc. The statement used is:
select * from article where title like 'mysql%';
Copy the codeThis type of like can be indexed (if the title field is indexed, of course).
Compound indexes are valid only for the first field
Create composite index:
alter table person add index(first_name,last_name);
Copy the codeThe index is sorted by the value of the first_name field. If the index is not sorted by the value of the first_name field, the index is sorted by the value of the first_name field.
Select * from person where first_name =? Select * from person where last_name =? Unable to use index.
So what are the application scenarios for this composite index? == Combined query ==
Select * person from first_name =? and last_name = ? A composite index is more efficient than a separate index of first_name and last_name. Well understood, the compound index first binary lookup with first_name =? A binary search of the records that match last_name involves only one index table. Select * from first_name where first_name =? Select last_name from last_name where last_name =? Records, the intersection of the two.
Or, both conditions have indexes available
No index available on one side causes a full table scan of the entire SQL statement
Status values, not easy to use indexes
Such as gender, payment status and other state value fields often have only a few possible values, and even if such fields are indexed, they are often not utilized. This is because a single state value may match a large number of records, in which case MySQL will consider using an index to be less efficient than a full table scan and deprecate the index. Indexes are random access disk, and a full table scan is a sequential access disk, it’s like there is a 20 floors of the office building, building the index card that read a company under the corresponding non-adjacent several floors, you go to the company contact, and according to the index card tips to one floor didn’t find again to see the index card upstairs again, as each find top up from the ground floor.
How to create an index
- Build the base index: in
Where, order by, joinCreate an index on the field. - Optimized, composite indexing: Based on business logic
- If conditions often come together, consider upgrading a multi-field index to a == compound index ==
- If the == index overwrites == by adding indexes to individual fields, consider indexing that field
- Delete indexes that are not commonly used when querying
The prefix index
Syntax: index(field(10)) : uses the first 10 characters of a field value to create an index. The default value is to use the entire content of a field to create an index.
Prerequisite: The identifier of the prefix is high. Passwords, for example, are good for prefix indexing because they are almost always different.
== The difficulty of the operation == : depends on the length of the prefix.
Select count(*)/count(distinct left(password,prefixLen)); By adjusting the prefixLen value (incremented from 1) to see an average match for different prefix lengths near 1 (the prefixLen characters representing a password almost determine a single record)
The storage structure of the index
BTree
Btree (multi-path balanced lookup tree) is a data structure that is widely used on == disks to achieve index function ==, and is also the implementation of most database index tables.
For example, add index(first_name,last_name) :
A node of BTree can store multiple keywords. The size of the node depends on the file system of the computer, so we can make the node store more keywords by reducing the length of the index field. If the node is full of keywords, the index can be extended by the child Pointers between each keyword without breaking the order of the structure. For example, if first_name is the first order and last_name is the second order, the newly added han xiang can be inserted after han Kang. Bai Qi < Han Fei < Han Kang < Li Shimin < Zhao She < Li Xunhuan < Wang Yuyan < Yang Bu Regrets. This is the same idea as binary search trees, except that binary search trees have a search efficiency of log(2,N) (log base 2 of N), whereas btrees have a search efficiency of log(x,N) (where x is the number of node keywords, which can reach over 1000).
According to log(1000+,N), a large amount of data can be traversed with a small number of disk reads, which is also the purpose of bTree.
B+Tree cluster structure
In a cluster structure (also upgraded on BTree), keywords and records are stored together.
In MySQL, only Innodb’s == primary key index is clustered ==, other indexes including Innodb’s non-primary key index are typical BTree structure.
The hash index
When the index is loaded into memory, the hash structure is used to store it.
The query cache
Cache the query results of the SELECT statement
Enable caching in the configuration file
Ini on Windows, my.cnf on Linux
Configure query_cache_type in the [mysqld] segment:
- 0: disabled
- 1: enable. By default, cache all. You need to add this parameter in the SQL statement
select sql-no-cachePrompt to abandon the cache - 2: enable. This parameter is not cached by default. You need to add this parameter in the SQL statement
select sql-cacheTo actively cache (== commonly used ==)
After the configuration is changed, you need to restart the system for the configuration to take effect. After the restart, run show variables like ‘query_cache_type’. To view:
show variables like 'query_cache_type';
query_cache_type DEMAND
Copy the codeSet the cache size on the client side
Set by the query_cache_size configuration item:
show variables like 'query_cache_size';
query_cache_size 0
set global query_cache_size=64*1024*1024;
show variables like 'query_cache_size';
query_cache_size 67108864
Copy the codeCache the query results
select sql_cache * from user;
Reset the cache
reset query cache;
Cache invalidation problem (Big problem)
When a table changes, any caches based on that table are deleted. (Surface management, not record level management, so high failure rate)
Matters needing attention
- Applications should not be concerned
query cacheThe use of. You can try to use it, but not byquery cacheDetermine the business logic becausequery cacheManaged by the DBA. - Caches are stored with SQL statements as keys. Therefore, even if SQL statements have the same functions, the caches cannot be matched if there is an extra space or case difference.
partition
Normally the tables we create correspond to a set of storage files, one with MyISAM storage engine. MYI and.myd files, a.ibd and.frm (table structure) file with Innodb storage engine.
When the amount of data is large (usually more than 10 million records), the performance of MySQL will start to decline. At this time, we need to spread the data into multiple storage files to ensure the execution efficiency of a single file ==.
The most common partition scheme is to partition by ID, which is modeled by the hash value of ID as follows to evenly distribute the data into 10 ibD storage files:
create table article(
id int auto_increment PRIMARY KEY,
title varchar(64),
content text
)PARTITION by HASH(id) PARTITIONS 10
Copy the codeView the data directory:
== The server table partition is transparent to the client. == The client inserts data as usual, but the server distributes the data according to the partitioning algorithm.
MySQL provides partitioning algorithms
== The field on which the partition is based must be part of the primary key ==. The partition is to quickly locate data. Therefore, the field with high search frequency should be regarded as a strong retrieval field; otherwise, it is meaningless to partition according to this field
hash(field)
The same input gives the same output. The output is independent of whether the input is regular or not. == applies only to integer fields ==
key(field)
Just like hash(field), but with key == and string ==, it takes one step more than hash() to compute an integer from the string to do the modulo operation.
create table article_key(
id int auto_increment,
title varchar(64),
content text,
PRIMARY KEY (id,title) The partition must be based on fields that are part of the primary key
)PARTITION by KEY(title) PARTITIONS 10
Copy the codeThe range algorithm
Is a == conditional partition == algorithm that partitions data by size range (dividing data into different partitions using certain conditions).
The data are stored in August, September and October of 2018 according to the release time of the article as follows:
create table article_range(
id int auto_increment,
title varchar(64),
content text,
created_time int.-- The number of milliseconds from release time to 1970-1-1
PRIMARY KEY (id,created_time) The partition must be based on fields that are part of the primary key
)charset=utf8
PARTITION BY RANGE(created_time)(
PARTITION p201808 VALUES less than (1535731199), -- select UNIX_TIMESTAMP('2018-8-31 23:59:59')
PARTITION p201809 VALUES less than (1538323199), - the 2018-9-30 23:59:59
PARTITION p201810 VALUES less than (1541001599) - the 2018-10-31 23:59:59
);
Copy the code
P201808, P201819, and P201810 are defined according to the creATED_time value range from small to large.
insert into article_range values(null.'MySQL optimizing'.'Content Sample'.1535731180);
flush tables; -- Causes operations to be flushed to disk files immediately
Copy the code
Since the publication time of the inserted article 1535731180 is less than 1535731199 (2018-8-31 23:59:59), it is stored in partition P201808. The partition to which this algorithm is stored depends on the data condition.
The list algorithm
Also a conditional partition, partitioned by list values (in (list of values)).
create table article_list(
id int auto_increment,
title varchar(64),
content text.status TINYINT(1), Article status: 0- draft, 1- Completed but not published, 2- Published
PRIMARY KEY (id.status) The partition must be based on fields that are part of the primary key
)charset=utf8
PARTITION BY list(status) (PARTITION writing values in(0.1), -- Unpublished in a partition
PARTITION published values in (2) Published files are placed in a partition
);
Copy the codeinsert into article_list values(null.'mysql optimizing'.'Content Sample'.0);
flush tables;
Copy the code
Partition management syntax
range/list
Increase the partition
In the previous article, we tried to use range to archive articles by month. As time increases, we need to add a month:
alter table article_range add partition(
partition p201811 values less than (1543593599) -- select UNIX_TIMESTAMP('2018-11-30 23:59:59')
-- more
);
Copy the code
Deleted partitions
alter table article_range drop PARTITION p201808
Copy the codeNote: == after the partition is deleted, the original data in the partition will also be deleted! = =
key/hash
The new partition
alter table article_key add partition partitions 4
Copy the code
Destruction of partition
alter table article_key coalesce partition 6
Copy the codeManagement of key/ Hash partitions does not delete data, but each adjustment (adding or destroying partitions) rewrites all data to the new partition. == Very inefficient ==, it is best to consider the partitioning strategy at the design stage.
Partition Usage
The efficiency gains from partitioning only become apparent when there is a large amount of data in the table.
Partitioning improves efficiency only when the retrieval field is a partitioned field. Therefore, the choice of the == partition field is important ==, and the == business logic should adjust accordingly to the partition field as much as possible == (use partition fields as query criteria whenever possible).
Horizontal segmentation and vertical segmentation
Horizontal partitioning: Storing data separately by creating several tables with the same structure
Vertical split: Put fields that are often used together in a separate table, and there is a one-to-one correspondence between the split table records.
Table for
- Decompress the database
- Limitations of partitioning algorithm
- Imperfect database support (
5.1aftermysqlPartition operations are supported.)
Id duplication solution
- Borrow third-party applications such as
Memcache, redistheidOf the increase - Create a separate sheet that contains only
idA table of fields that are incremented each time as data recordsid
The cluster
Horizontal expansion: Improve database performance fundamentally (the hardware processing capacity of a single machine is limited). Related technologies: == Read/write separation, load balancing ==
Install and configure master/slave replication
The environment
Red Hat Enterprise Linux Server release 7.0 (Maipo)(VIRTUAL machine)mysql5.7(Download address)
Installation and configuration
Unzip to the directory of external services (I created /export/server to store it)
Tar XZVF mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -c /export/server CD /export/server mv Mysql - 5.7.23 - Linux - glibc2.12 - x86_64 mysqlCopy the codeAdd group and owner of mysql directory:
groupadd mysql
useradd -r -g mysql mysql
cd /export/server
chown -R mysql:mysql mysql/
chmod -R 755 mysql/
Copy the codeCreate a directory for storing mysql data (where /export/data is a directory for storing data for various services)
mkdir /export/data/mysql
Copy the codeInitialize the mysql service
cd /export/server/mysql
./bin/mysqld --basedir=/export/server/mysql --datadir=/export/data/mysql --user=mysql --pid-file=/export/data/mysql/mysql.pid --initialize
Copy the codeIf successful, the initial password of the mysql root account is displayed. Note it down for subsequent login. If an error is reported, use Yum Instally to install the dependencies in sequence
Configure my CNF
vim /etc/my.cnf [mysqld] basedir=/export/server/mysql datadir=/export/data/mysql socket=/tmp/mysql.sock user=mysql Server-id =10 # The server id must be unique in the cluster. You are advised to set port=3306 in the fourth segment of the IP address# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/export/data/mysql/error.log
pid-file=/export/data/mysql/mysql.pid
#
# include all files from the config directory
#! includedir /etc/my.cnf.dCopy the codeAdd the service to boot automatically
cp /export/server/mysql/support-files/mysql.server /etc/init.d/mysqld
Copy the codeStart the service
service mysqld start
Copy the codeConfigure environment variables and add the following information to /etc/profile
# mysql env
MYSQL_HOME=/export/server/mysql
MYSQL_PATH=$MYSQL_HOME/bin
PATH=$PATH:$MYSQL_PATH
export PATH
Copy the codeThe configuration takes effect
source /etc/profile
Copy the codeLogging In as root
mysql -uroot -p
#Enter the password provided when initializing the service
Copy the codeOnce logged in, change the password of the root account (I changed it to root for convenience), otherwise the operation database will report an error
set password=password('root');
flush privileges;
Copy the codeMake the service accessible to all remote clients
use mysql;
update user set host='%' where user='root';
flush privileges;
Copy the codeThis allows you to remotely connect to mysql on Linux using NavICat on the host machine
Configure the primary and secondary nodes
Configure the master
The mysql server on Linux (192.168.10.10) is used as the master and the mysql server on the host (192.168.10.1) is used as the slave.
Modify master my.cnf as follows
[mysqld]
basedir=/export/server/mysql
datadir=/export/data/mysql
socket=/tmp/mysql.sock
user=mysql
server-id=10
port=3306
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/SystemdLog-bin =mysql-bin # enable binary log expire-logs-days=7 Binlog-ignore -db=information_schema Binlog-ignore -db=performation_schema Binlog - ignore - db = sys binlog - do - db = # test using master-slave replication database/mysqld_safe log - error = / export/data/mysql/error. The log pid-file=/export/data/mysql/mysql.pid
#
# include all files from the config directory
#! includedir /etc/my.cnf.dCopy the codeRestart the master
service mysqld restart
Copy the codeLog in to master to check whether the configuration takes effect (ON indicates that the function is enabled, and the default value is OFF) :
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
Copy the codeCreate a backup account on the master database: backup is the username, % is any remote address, and the user back can connect to the master database using password 1234 from any remote client
grant replication slave on *.* to 'backup'@'%' identified by '1234'
Copy the codeLook at the user table to see the user we just created:
mysql> use mysql
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | % |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| backup | *A4B6157319038724E3560894F7F932C8886EBFCF | % |
+---------------+-------------------------------------------+-----------+
Copy the codeCreate a new Test database and create an Article table for subsequent tests
CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(64) DEFAULT NULL.`content` text,
PRIMARY KEY (`id`))CHARSET=utf8;
Copy the codeRestart the service and flush the database state to the storage file (with read lock means that during this process, the client can only read the data to get a consistent snapshot)
[root@zhenganwen ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
[root@zhenganwen mysql]# mysql -uroot -proot
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
Copy the codeView the current binary log and offset on the master (remember File and Position)
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 154
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
Copy the code
File represents the log that implements the replication function, namely the Binary log in the figure above. Position indicates that everything after the offset of the Binary log file is synchronized to the slave, and everything before the offset must be imported manually.
Any changes made on the primary server are stored in the Binary log. An I/O thread (essentially a client process on the primary server) is started on the primary server and connected to the primary server to request the Binary log. The binary log is then written to a local Realy log. Start a SQL thread from the server to periodically check the Realy log. If any changes are detected, execute the changes on the machine.
If one master has multiple slaves, then the master library is responsible for both writing and providing binary logs for several slave libraries. In this case, you can adjust the binary log to a slave. The slave then enables the binary log and sends its binary log to another slave. Or it could simply never log and only forward binary logs to other slaves, which would probably be much better architeted, and the latency between data should be slightly better
Manual import, export data from master
mysqldump -uroot -proot -hlocalhost test > /export/data/test.sql
Copy the codeExecute the contents of test.sql on the slave.
Configuration of slave
Modify the [mysqld] part of the slave my.ini file
log-bin=mysql
server-id=1 #192.168.10.1
Copy the codeSave the changes and restart slave, WIN+R->services. MSC ->MySQL5.7-> restart
Log in to slave to check whether log_bin is enabled:
show VARIABLES like 'log_bin';
Copy the codeConfigure synchronous replication with master:
stop slave;
change master to
master_host='192.168.10.10'.- master of IP
master_user='backup'.-- User created previously on master
master_password='1234',
master_log_file='mysql-bin.000002'.-- show master status \G on master
master_log_pos=154;
Copy the codeEnable the slave node and view the status
mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.10
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: DESKTOP-KUBSPE0-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 537
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: f68774b7-0b28-11e9-a925-000c290abe05
Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\Data\master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Copy the codeCheck lines 4, 14, and 15. If they are the same as mine, the slave configuration is successful
test
Disable the master read lock
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Copy the codeInsert a piece of data into the master
mysql> use test mysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed! :) '); Query OK, 1 row affected (0.00 SEC)Copy the codeCheck whether the slave automatically synchronizes data
mysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed! :) '); Query OK, 1 row affected (0.00 SEC)Copy the codeAt this point, the configuration of master/slave replication is successful! 🙂
Use the mysqlreplicate command to quickly set up Mysql primary secondary replication
Reading and writing separation
Read/write separation depends on master/slave replication, which in turn serves read/write separation. Since master/slave replication requires that the slave cannot write and can only read (if a write operation is performed on the slave, show slave status will show Slave_SQL_Running=NO, in which case you need to manually synchronize the slave as mentioned above).
Scheme 1. Define two types of connections
Just like when we are learning JDBC DataBase definition, we can extract ReadDataBase, WriteDataBase implements the DataBase, But this approach doesn’t take advantage of good thread pooling techniques like DruidDataSource to help us manage connections, nor does Spring AOP make connections transparent to the DAO layer.
Option two, use Spring AOP
If you can use Spring AOP to solve the problem of switching data sources, then you can integrate Mybatis and Druid.
When we integrate Spring1 and Mybatis, we only need to write the DAO interface and the corresponding SQL statement, so who creates the DAO instance? Spring actually created it for us, taking the database connection from the data source we injected, executing SQL statements with the connection, and finally returning the connection to the data source.
If we can call the DAO interface according to the interface method naming conventions (addXXX/createXXX, delete deleteXX/updateXXXX removeXXX, change, check selectXX/findXXX/getXX/queryXXX) dynamically select data sources (read data The source corresponds to the master and the write source corresponds to the slave), then the read and write separation can be achieved.
The project structure
Introduction of depend on
Among them, Mybatis and Druid are introduced to facilitate database access, and the implementation of dynamic data source switching mainly relies on Spring-AOP and Spring-Aspects
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.0.8. RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.0.8. RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.8. RELEASE</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.2</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.0.8. RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>5.0.8. RELEASE</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.22</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.0.8. RELEASE</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
Copy the codeData classes
package top.zhenganwen.mysqloptimize.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Article {
private int id;
private String title;
private String content;
}
Copy the codeSpring configuration file
RoutingDataSourceImpl is the core class that implements dynamic switching, which will be described later.
<?xml version="1.0" encoding="UTF-8"? >
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<context:property-placeholder location="db.properties"></context:property-placeholder>
<context:component-scan base-package="top.zhenganwen.mysqloptimize"/>
<bean id="slaveDataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${db.driverClass}"/>
<property name="url" value="${master.db.url}"></property>
<property name="username" value="${master.db.username}"></property>
<property name="password" value="${master.db.password}"></property>
</bean>
<bean id="masterDataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${db.driverClass}"/>
<property name="url" value="${slave.db.url}"></property>
<property name="username" value="${slave.db.username}"></property>
<property name="password" value="${slave.db.password}"></property>
</bean>
<bean id="dataSourceRouting" class="top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl">
<property name="defaultTargetDataSource" ref="masterDataSource"></property>
<property name="targetDataSources">
<map key-type="java.lang.String" value-type="javax.sql.DataSource">
<entry key="read" value-ref="slaveDataSource"/>
<entry key="write" value-ref="masterDataSource"/>
</map>
</property>
<property name="methodType">
<map key-type="java.lang.String" value-type="java.lang.String">
<entry key="read" value="query,find,select,get,load,"></entry>
<entry key="write" value="update,add,create,delete,remove,modify"/>
</map>
</property>
</bean>
<! -- Mybatis file -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="configLocation" value="classpath:mybatis-config.xml" />
<property name="dataSource" ref="dataSourceRouting" />
<property name="mapperLocations" value="mapper/*.xml"/>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="top.zhenganwen.mysqloptimize.mapper" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>
</beans>
Copy the codedp.properties
master.db.url=jdbc:mysql://localhost:3306/test? useUnicode=true&characterEncoding=utf8&serverTimezone=UTC master.db.username=root master.db.password=root Slave. Db. Url = JDBC: mysql: / / 192.168.10.10:3306 / test? useUnicode=true&characterEncoding=utf8&serverTimezone=UTC slave.db.username=root slave.db.password=root db.driverClass=com.mysql.jdbc.DriverCopy the codemybatis-config.xml
<?xml version="1.0" encoding="UTF-8"? >
<configuration>
<typeAliases>
<typeAlias type="top.zhenganwen.mysqloptimize.entity.Article" alias="Article"/>
</typeAliases>
</configuration>
Copy the codeMapper interface and configuration file
ArticleMapper.java
package top.zhenganwen.mysqloptimize.mapper;
import org.springframework.stereotype.Repository;
import top.zhenganwen.mysqloptimize.entity.Article;
import java.util.List;
@Repository
public interface ArticleMapper {
List<Article> findAll(a);
void add(Article article);
void delete(int id);
}
Copy the codeArticleMapper.xml
<?xml version="1.0" encoding="UTF-8" ? >
<mapper namespace="top.zhenganwen.mysqloptimize.mapper.ArticleMapper">
<select id="findAll" resultType="Article">
select * from article
</select>
<insert id="add" parameterType="Article">
insert into article (title,content) values (#{title},#{content})
</insert>
<delete id="delete" parameterType="int">
delete from article where id=#{id}
</delete>
</mapper>
Copy the codeCore classes
RoutingDataSourceImpl
package top.zhenganwen.mysqloptimize.dataSource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import java.util.*;
/** * RoutingDataSourceImpl class * data source routing **@author zhenganwen, blog:zhenganwen.top
* @date2018/12/29 * /
public class RoutingDataSourceImpl extends AbstractRoutingDataSource {
/** * key is read or write * value is the prefix for DAO methods * What prefixes use read data clerks and what prefixes use write data sources */
public static final Map<String, List<String>> METHOD_TYPE_MAP = new HashMap<String, List<String>>();
/** * We specify the data source id, and Spring switches the data source **@return* /
@Override
protected Object determineCurrentLookupKey(a) {
System.out.println("Data source is:"+DataSourceHandler.getDataSource());
return DataSourceHandler.getDataSource();
}
public void setMethodType(Map<String, String> map) {
for (String type : map.keySet()) {
String methodPrefixList = map.get(type);
if(methodPrefixList ! =null) {
METHOD_TYPE_MAP.put(type, Arrays.asList(methodPrefixList.split(","))); }}}}Copy the codeIts main function is that we used to configure only one data source, so Spring dynamically proxy DAO interface directly use this data source. Now we have two data sources, read and write, we need to add some logic of our own to tell which interface to call which data source to use (the data reading interface uses slave, The interface for writing data uses master. This tell Spring use which data source class is AbstractRoutingDataSource, must be rewritten determineCurrentLookupKey () method returns the data source identification, combining with the Spring configuration file (code under the two row 5, 6)
<bean id="dataSourceRouting" class="top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl">
<property name="defaultTargetDataSource" ref="masterDataSource"></property>
<property name="targetDataSources">
<map key-type="java.lang.String" value-type="javax.sql.DataSource">
<entry key="read" value-ref="slaveDataSource"/>
<entry key="write" value-ref="masterDataSource"/>
</map>
</property>
<property name="methodType">
<map key-type="java.lang.String" value-type="java.lang.String">
<entry key="read" value="query,find,select,get,load,"></entry>
<entry key="write" value="update,add,create,delete,remove,modify"/>
</map>
</property>
</bean>
Copy the codeIf determineCurrentLookupKey returned to read using slaveDataSource, if use masterDataSource returned to write.
DataSourceHandler
package top.zhenganwen.mysqloptimize.dataSource;
/** * DataSourceHandler class * <p> * binds data sources to threads, fetching ** by thread if needed@author zhenganwen, blog:zhenganwen.top
* @date2018/12/29 * /
public class DataSourceHandler {
/** * binds either read or write data sources */
private static final ThreadLocal<String> holder = new ThreadLocal<String>();
public static void setDataSource(String dataSource) {
System.out.println(Thread.currentThread().getName()+"Data source type set");
holder.set(dataSource);
}
public static String getDataSource(a) {
System.out.println(Thread.currentThread().getName()+"Got the data source type");
returnholder.get(); }}Copy the codeDataSourceAspect
package top.zhenganwen.mysqloptimize.dataSource;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.stereotype.Component;
import java.util.List;
import java.util.Set;
import static top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl.METHOD_TYPE_MAP;
/** * DataSourceAspect class ** Configure the aspect, set the read and write data sources according to the method prefix ** the bean will be loaded when the project starts, and determine the dynamic proxy logic according to the configured aspect (which pointcuts, how to enhance) **@author zhenganwen,blog:zhenganwen.top
* @date2018/12/29 * /
@Component
// Declare this a facet so that Spring will configure it accordingly, otherwise it will be injected as a simple bean
@Aspect
@EnableAspectJAutoProxy
public class DataSourceAspect {
/** * Config pointcut: all methods */ for all classes in the DAO package
@Pointcut("execution(* top.zhenganwen.mysqloptimize.mapper.*.*(..) )")
public void aspect(a) {}/** * configuration prefixes, objects are pointcuts for configuration on the aspect() method */
@Before("aspect()")
public void before(JoinPoint point) {
String className = point.getTarget().getClass().getName();
String invokedMethod = point.getSignature().getName();
System.out.println("对 "+className+"$"+invokedMethod+"Pre-enhanced to determine the type of data source to use.");
Set<String> dataSourceType = METHOD_TYPE_MAP.keySet();
for (String type : dataSourceType) {
List<String> prefixList = METHOD_TYPE_MAP.get(type);
for (String prefix : prefixList) {
if (invokedMethod.startsWith(prefix)) {
DataSourceHandler.setDataSource(type);
System.out.println("Data source is:"+type);
return;
}
}
}
}
}
Copy the codeTest read/write separation
How do you test that a read is read from a slave? Data changes can be copied to a slave after a write and read again to know that it has been read from the slave. == Notice ==, once a write operation is performed on the slave, manually synchronize the slave and master again. Otherwise, the master/slave replication will fail.
package top.zhenganwen.mysqloptimize.dataSource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import top.zhenganwen.mysqloptimize.entity.Article;
import top.zhenganwen.mysqloptimize.mapper.ArticleMapper;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:spring-mybatis.xml")
public class RoutingDataSourceTest {
@Autowired
ArticleMapper articleMapper;
@Test
public void testRead(a) {
System.out.println(articleMapper.findAll());
}
@Test
public void testAdd(a) {
Article article = new Article(0."I'm a new insertion."."Test if you can write to master and copy to slave.");
articleMapper.add(article);
}
@Test
public void testDelete(a) {
articleMapper.delete(2); }}Copy the codeLoad balancing
Load balancing algorithm
- polling
- Weighted polling: Weighted by processing power
- Load allocation: According to the current idle state (but test the memory utilization, CPU utilization, etc., of each node, then compare and select the most idle one, efficiency is too low)
High availability
In the server architecture, to ensure that server 7×24 is not down and online, each single point server (server provided by one server, such as write server, database middleware) needs to provide redundant machines.
For a write server, an identical write-redundancy server needs to be provided. When the write server is healthy (write-redundancy passes heartbeat detection), write-redundancy replicates the contents of the write server as a slave and synchronizes the contents of the write server. When the write server goes down, the write-redundant server takes over to continue serving as the write server. This process is transparent to the outside world, which accesses the service through only one IP.
Typical SQL
Online DDL
Database Definition Language (DDL) is a Language used to define and maintain the structure of a Database table. Executing DDL online causes the entire table to be locked exclusively below MySQL5.6, while the table is in a maintenance, non-operable state, which causes all access to the table to be unresponsive during that time. But after MySQL5.6, Online DDL is supported, which greatly reduces the lock time.
The optimization technique used to maintain the DDL of the table structure (such as adding a column, or adding an index) is the ==copy== policy. Create a new table that meets the new structure, import (copy) the data of the old table into the new table, so that the data of the old table can be locked at one time. During the import process, all operations on the old table are recorded in the form of logs. After the import, the updated logs are executed again on the new table (to ensure consistency). Finally, the new table replaces the old table (done in the application, or in the database rename, view).
But with the MySQL upgrade, this problem almost faded away.
Database import statement
During data recovery, a large amount of data may be imported. At this point, in order to quickly import, need to master some skills:
- == Disable indexes and constraints before importing == :
alter table table-name disable keys
Copy the codeAfter data is imported, enable indexes and constraints to create indexes at a time
alter table table-name enable keys
Copy the code- Database if the engine used is
Innodb, then it will add transaction == by default to each write instruction (this will also take some time), so it is recommended to start the transaction manually, perform a certain amount of batch imports, and finally commit the transaction manually. - If the batch import SQL instruction format is the same but the data is different, then you should first
prepare== precompile ==, this can also save a lot of time for repeated compilation.
limit offset,rows
Try not to have a large offset, such as limit 1000010,10 is equivalent to discard the first 10000 rows and then select 10 rows, you can add some conditions to filter (filter), instead of using limit to skip the queried data. This is a problem where ==offset does nothing ==. In actual projects, avoid large page numbers and guide users to perform conditional filtering.
Select * should be used sparingly
Namely select as far as possible need to choose their fields, but the effect is not very big, because the network many dozens of hundreds of bytes is not much delay, and now popular ORM framework is done with a select *, just we pay attention in the design table will be large amount of data the field of separation, such as product details can be pulled out a product list separately, This will not affect the loading speed when viewing the product brief page.
Order by rand() do not use
Its logic is random sorting (generating a random number for each piece of data and then sorting according to the size of the random number). For example, select * from student order by rand() limit 5 is inefficient because it generates random numbers and sorts each entry in the table, whereas we only need the first 5 entries.
Solution: in the application program, the random primary key generation, to use the primary key retrieval database.
Single-table and multi-table queries
Multi-table query: Join and sub-query are all queries involving multiple tables. If you use Explain to analyze the execution plan, you will find that multi-table queries are also processed table by table, and the results are merged. So you can say that single-table queries place computational stress on the application, while multi-table queries place computational stress on the database.
ORM framework now helps us to solve the object mapping problem brought by single table query (query a single table, if there is found to be a foreign key automatically query the associated table, is a table by table lookup).
count(*)
In the MyISAM storage engine, the number of rows in the table is recorded automatically, so using count(*) is a quick way to return. Innodb does not have such a counter, we need to manually count the number of records, the solution is to use a separate table:
| id | table | count |
|---|---|---|
| 1 | student | 100 |
limit 1
If it is certain that only one item will be retrieved, it is recommended to add limit 1. In fact, the ORM framework does this for us (the query for a single item will automatically add limit 1).
Slow Query logs
This log is used to record SQL logs whose execution time exceeds a critical value. This log is used to quickly locate slow queries and provide reference for optimization.
Enable slow log query
Configuration item: slow_query_log
Use show variables like ‘slov_query_log’ to check whether the slov_query_log is enabled. If the status is OFF, use set GLOBAL slow_query_log = on to enable the slov_query_log function. It will generate an XXx-slow.log file under datadir.
Set critical time
Configuration item: long_query_time
Check: show VARIABLES like ‘long_query_time’, in seconds
Set: set long_query_time=0.5
The real time should be set from long time to short time, that is, the slowest SQL optimization away
See the log
Once the SQL exceeds the critical time we set, it will be logged in xxx-slow.log
Profile information
Configuration item: Profiling
Open the profile
set profiling=on
When enabled, all SQL execution details are automatically logged
mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.00 SEC) mysql > set profiling = on; Query OK, 0 rows affected (0.00 SEC)Copy the codeViewing Profile Information
show profiles
mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | ON | +---------------+-------+ 1 row in set, 1 warning (0.00 SEC) mysql> insert into article values (null,'test profile',':); Query OK, 1 row affected (0.15sec) mysql> show profiles; +----------+------------+-------------------------------------------------------+ | Query_ID | Duration | Query | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | | 0.00086150 show the variables like "Profiling" | | | | 0.15027550 2 insert into article values (null, 'the test profile', ') ') | +----------+------------+-------------------------------------------------------+Copy the codeQuery the time of all detailed steps in an SQL query with Query_ID
show profile for query Query_ID
In the result of show Profiles above, each SQL has a Query_ID that you can use to see what steps were taken to execute the SQL, and how many times each took
Copy the codeTypical server configuration
The following configurations all depend on the actual operating environment
-
Max_connections: indicates the maximum number of client connections
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ Copy the code -
Table_open_cache, table file handle cache (table data is stored on disk, cache disk file handle is convenient to open the file to read data)
mysql> show variables like 'table_open_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | table_open_cache | 2000 | +------------------+-------+ Copy the code -
Key_buffer_size, index cache size (cache indexes read from disk into memory, can be set to large for quick retrieval)
mysql> show variables like 'key_buffer_size'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | key_buffer_size | 8388608 | +-----------------+---------+ Copy the code -
Innodb_buffer_pool_size, the size of Innodb storage engine cache pool (one of the most important configurations for Innodb, if all tables use Innodb, it is even recommended to set this value to 80% of physical memory, Innodb’s many performance improvements such as indexes depend on this)
mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | innodb_buffer_pool_size | 8388608 | +-------------------------+---------+ Copy the code -
Innodb_file_per_table (innoDB, table data stored in. Ibd file, if set to ON, then one table corresponds to one IBD file, otherwise all InnoDB share table space)
Mysqlslap pressure tool
Mysqlslap comes with a stress test tool when you install MySQL (in the bin directory)
Automatically generate SQL tests
C:\Users\zaw>mysqlslap --auto-generate-sql -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 1.219 seconds
Minimum number of seconds to run all queries: 1.219 seconds
Maximum number of seconds to run all queries: 1.219 seconds
Number of clients running queries: 1
Average number of queries per client: 0
Copy the codeConcurrent test
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=100 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 3.578 seconds
Minimum number of seconds to run all queries: 3.578 seconds
Maximum number of seconds to run all queries: 3.578 seconds
Number of clients running queries: 100
Average number of queries per client: 0
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 5.718 seconds
Minimum number of seconds to run all queries: 5.718 seconds
Maximum number of seconds to run all queries: 5.718 seconds
Number of clients running queries: 150
Average number of queries per client: 0
Copy the codeSeveral rounds of testing
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=10 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 5.398 seconds
Minimum number of seconds to run all queries: 4.313 seconds
Maximum number of seconds to run all queries: 6.265 seconds
Number of clients running queries: 150
Average number of queries per client: 0
Copy the codeStorage Engine Testing
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=innodb -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 5.911 seconds
Minimum number of seconds to run all queries: 5.485 seconds
Maximum number of seconds to run all queries: 6.703 seconds
Number of clients running queries: 150
Average number of queries per client: 0
Copy the codeC:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=myisam -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 53.104 seconds
Minimum number of seconds to run all queries: 46.843 seconds
Maximum number of seconds to run all queries: 60.781 seconds
Number of clients running queries: 150
Average number of queries per client: 0
Copy the code