Store the IP

INET_ATON(STR), address to number

INET_NTOA(number), number to address

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)

Small units and large amounts to avoid decimals

Yuan – > points

View index

Show create table name:

Desc table name

Remove 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 code

Alter 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 -- MODIFY id int, drop PRIMARY KEYCopy the code

However, 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 code

We 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)

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)

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. So we need to do as much as possible after select

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)

A like query 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 code

This 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 code

This type of like can be indexed (if the title field is indexed, of course).

Or, both conditions have indexes available

No index available on one side causes a full table scan of the entire SQL statement

BTree

Btree (multi-way balanced search tree) is a widely used in

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.

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

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 statementselect 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 statementselect sql-cacheTo actively cache (
  The 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 code

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 code

select sql_cache * from user;

reset query cache;

When a table changes, any caches based on that table are deleted. (Surface management, not record level management, so high failure rate)

1. Applications should not be concernedquery cacheThe use of. You can try to use it, but not byquery cacheDetermine the business logic becausequery cacheManaged by the DBA.
2. 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.

The performance of MySQL starts to deteriorate when the data volume is large (usually above the 10 million records level), and we need to split the data into multiple storage files.

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 code

View the data directory:

Server side table partitions are transparent to clients

, the client inserts data as usual, but the server distributes the data according to the partitioning algorithm.

The field on which the partition is based must be part of the primary key

, partition is to quickly locate data, so this field with high search frequency should be regarded as a strong retrieval field, otherwise it is meaningless to partition according to this field

key(field)

It has the same properties as hash(field), except that key is

hash()

create table article_key( id int auto_increment, title varchar(64), content text, PRIMARY KEY(ID,title) -- requires PARTITIONS by KEY(title) PARTITIONS 10Copy the code

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), -- 0- draft, 1- Completed but not released, Charset =utf8 PARTITION BY list(status)(PARTITION writing valuesin(0,1), -- place the unpublished values on a PARTITION published valuesin(2) -- published in a partition);Copy the code

insert into article_list values(null,'mysql optimizing'.'Content Sample', 0); flush tables;Copy the code

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 code

Management of key/ Hash partitions does not delete data, but each adjustment (adding or destroying partitions) rewrites all data to the new partition.

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. As a result,

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.

• Decompress the database
• Limitations of partitioning algorithm
• Imperfect database support (5.1aftermysqlPartition operations are supported.)

• Borrow third-party applications such asMemcache, redistheidOf the increase
• Create a separate sheet that contains onlyidA 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 from this:

Read/write separation and load balancing

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/Systemd

log-bin=mysql-bin	Enable binary logging
expire-logs-days=7  Set log expiration time to avoid disk usage
binlog-ignore-db=mysql	# Do not use master-slave replication database
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
binlog-do-db=test	Use a master-slave database

[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 code

Restart the master

service mysqld restart
Copy the code

Log 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 code

Create 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'@The '%' identified by '1234'
Copy the code

Look 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 code

Create 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 code

Restart 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 code

View 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 code

Execute 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 code

Save 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 code

Configure synchronous replication with master:

stop slave; 
change master to
    master_host='192.168.10.10', -- master's IP address master_user='backup'-- create user master_password= on master'1234',
    master_log_file='mysql-bin.000002', -- master on show master status \GCopy the code

Enable the slave node and view the status

mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting forMaster 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 code

Check lines 4, 14, and 15. If they are the same as mine, the slave configuration is successful

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).

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> < version > 3.4.6 < / version > < / dependency > < the dependency > < groupId > org. Springframework < / groupId > < artifactId > spring - the core < / artifactId > < version > 5.0.8. RELEASE < / version > < / dependency > < the dependency > < the 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 > < the dependency > < groupId > com. Alibaba < / groupId > <artifactId>druid</artifactId> <version>1.1.6</version> </dependency> <dependency> <groupId> < artifactId > mysql connector - Java < / artifactId > < version > 6.0.2 < / version > < / dependency > < the dependency > < the groupId > org. Springframework < / groupId > < artifactId > spring - the 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 > < the dependency > < groupId > org. Projectlombok < / groupId > < artifactId > lombok < / artifactId > < version > 1.16.22 < / version > < / dependency > < the dependency > < the groupId > org. Springframework < / groupId > < artifactId > spring - test < / artifactId > < version > 5.0.8. RELEASE < / version > </dependency> <groupId> <groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency>  </dependencies>Copy the code

Data 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 code

Spring 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 code

dp.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.Driver
Copy the code

mybatis-config.xml

<? xml version="1.0" encoding="UTF-8"? > <! DOCTYPE configuration PUBLIC"- / / mybatis.org//DTD Config / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <typeAlias type="top.zhenganwen.mysqloptimize.entity.Article" alias="Article"/>
    </typeAliases>
</configuration>
Copy the code

Mapper 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();

    void add(Article article);

    void delete(int id);

}
Copy the code

ArticleMapper.xml

<? xml version="1.0" encoding="UTF-8"? > <! DOCTYPE mapper PUBLIC"- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<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 code

Core classes

RoutingDataSourceImpl

package top.zhenganwen.mysqloptimize.dataSource; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; import java.util.*; /** * RoutingDataSourceImpl class ** @author zhenganwen, blog:zhenganwen.top * @date 2018/12/29 */ public class RoutingDataSourceImpl extends AbstractRoutingDataSource { /** * The key forreadOr write * value is the prefix of DAO methods * what prefix is used for methods that use reader, Public static final Map<String, List<String>> METHOD_TYPE_MAP = new HashMap<String, List<String>>(); /** * We specify the id of the data source, and Spring switches the data source ** @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        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 code

Its 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 code

If determineCurrentLookupKey returned to read using slaveDataSource, if use masterDataSource returned to write.

DataSourceHandler

package top.zhenganwen.mysqloptimize.dataSource; /** * DataSourceHandler class * <p> * ** @author zhenganwen, blog:zhenganwen.top * @date 2018/12/29 */ public class DataSourceHandler {** *readPrivate static final ThreadLocal<String> holder = new ThreadLocal<String>(); public static voidsetDataSource(String dataSource) {
        System.out.println(Thread.currentThread().getName()+"Data source type set");
        holder.set(dataSource);
    }

    public static String getDataSource() {
        System.out.println(Thread.currentThread().getName()+"Got the data source type");
        returnholder.get(); }}Copy the code

DataSourceAspect

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 ** Set the section to read and write data sources according to the method prefix * this bean will be loaded when the project starts, And according to the configuration section (which the breakthrough point, how to enhance) to determine the dynamic proxy logic * @ author zhenganwen, blog: zhenganwen. Top * @ date 2018/12/29 * / @ Component / / declare this is a plane, This is how Spring will do the configuration, otherwise it will inject @aspect @enableAspectJAutoProxy public Class DataSourceAspect as a simple bean. All methods of all classes in the DAO package */ @pointcut ("execution(* top.zhenganwen.mysqloptimize.mapper.*.*(..) )")
    public void aspect() {} /** * configuration pre-enhanced, the object is the pointcut configured 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 code

Test 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.

Pay attention to

, but for
slaveYou have to redo the write operation manually
slavewith
masterSynchronize, or 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() {
        System.out.println(articleMapper.findAll());
    }

    @Test
    public void testAdd() {
        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() { articleMapper.delete(2); }}Copy the code

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)

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

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.

Optimization techniques are used to maintain the DDL of the table structure (such as adding a column, or adding an index), is

But with the MySQL upgrade, this problem almost faded away.

During data recovery, a large amount of data may be imported. At this point, in order to quickly import, need to master some skills:

1. When the import
   Start by disabling indexes and constraints

   :

alter table table-name disable keys
Copy the code

After data is imported, enable indexes and constraints to create indexes at a time

alter table table-name enable keys
Copy the code

1. Database if the engine used isInnodb, then it
   By default, transactions are added to each write instruction

   (This can also take some time), so it is recommended to start the transaction manually, perform a certain amount of batch imports, and commit the transaction manually.

2. If the batch import SQL instruction format is the same but the data is different, then you should firstprepare
   precompiled

   This also saves a lot of time on repeated compilation.

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

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.

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.

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).

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:

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.

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.

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

Once the SQL exceeds the critical time we set, it will be logged in xxx-slow.log

Profile information

Configuration item: Profiling

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 set, 1 warning (0.00 sec)

mysql> setprofiling=on; Query OK, 0 rows affected (0.00 SEC)Copy the code

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'                       |
|        2 | 0.15027550 | insert into article values (null,'test profile'.':)') | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

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 code

Typical 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)

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 code

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 code

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 code

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 forEngine 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: 0Copy the code

C:\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: 0Copy the code

Ok, I will write here first, it is not easy to organize, like to click a like oh

Summary: The essence of Mysql optimization – Programmer internal matters – Blog garden