MySQL性能优化指南

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

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

alter table user_index
# -- 重新定义字段
MODIFY id int,
drop PRIMARY KEY

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 (\',\'华山派\');

# -- 增加一个没有建立索引的字段
alter table innodb1 add sex char(1);
# -- 按sex检索时可选的索引为null
EXPLAIN SELECT * from innodb1 where sex=\'男\';

select * from user where id = 20-1;
select * from user where id 1 = 20;

# select * from article where title like \'%mysql%\';

# select * from article where title like \'mysql%\';

# alter table person add index(first_name,last_name);

# show variables like \'query_cache_type\';
# query_cache_type    DEMAND

# 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

create table article(
    id int auto_increment PRIMARY KEY,
    title varchar(64),
    content text
)PARTITION by HASH(id) PARTITIONS 10

create table article_key(
    id int auto_increment,
    title varchar(64),
    content text,
    PRIMARY KEY (id,title)    # -- 要求分区依据字段必须是主键的一部分
)PARTITION by KEY(title) PARTITIONS 10

create table article_range(
    id int auto_increment,
    title varchar(64),
    content text,
    created_time int,    # -- 发布时间到1970-1-1的毫秒数
    PRIMARY KEY (id,created_time)    # -- 要求分区依据字段必须是主键的一部分
)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),    -- 2018-9-30 23:59:59
    PARTITION p201810 VALUES less than (1541001599)    -- 2018-10-31 23:59:59
);

insert into article_range values(null,\'MySQL优化\',\'内容示例\',1535731180);
flush tables;    # -- 使操作立即刷新到磁盘文件

create table article_list(
    id int auto_increment,
    title varchar(64),
    content text,
    status TINYINT(1),    # -- 文章状态：0-草稿，1-完成但未发布，2-已发布
    PRIMARY KEY (id,status)    # -- 要求分区依据字段必须是主键的一部分
)charset=utf8
PARTITION BY list(status)(
    PARTITION writing values in(0,1),    # -- 未发布的放在一个分区
    PARTITION published values in (2)    # -- 已发布的放在一个分区
);

insert into article_list values(null,\'mysql优化\',\'内容示例\',0);
flush tables;

alter table article_range add partition(
    partition p201811 values less than (1543593599)    -- select UNIX_TIMESTAMP(\'2018-11-30 23:59:59\')
    -- more
);

# alter table article_range drop PARTITION p201808

# alter table article_key add partition partitions 4

# alter table article_key coalesce partition 6

# groupadd mysql
# useradd -r -g mysql mysql
# cd /export/server
# chown -R mysql:mysql mysql/
# chmod -R 755 mysql/

# cd /export/server/mysql
# ./bin/mysqld --basedir=/export/server/mysql --datadir=/export/data/mysql --user=mysql --pid-file=/export/data/mysql/mysql.pid --initialize

vim /etc/my.cnf
[mysqld]
basedir=/export/server/mysql
datadir=/export/data/mysql
socket=/tmp/mysql.sock
user=mysql
server-id=10 # 服务id，在集群时必须唯一，建议设置为IP的第四段
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
[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.d

# service mysqld start

# mysql env
MYSQL_HOME=/export/server/mysql
MYSQL_PATH=$MYSQL_HOME/bin
PATH=$PATH:$MYSQL_PATH
export PATH

# source /etc/profile

mysql -uroot -p
# 这里填写之前初始化服务时提供的密码

set password=password(\'root\');
flush privileges;

use mysql;
update user set host=\'%\' where user=\'root\';
flush privileges;

[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    # 开启二进制日志
expire-logs-days=7  # 设置日志过期时间，避免占满磁盘
binlog-ignore-db=mysql    # 不使用主从复制的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
binlog-do-db=test    #使用主从复制的数据库
[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.d

# service mysqld restart

mysql> show variables like \'log_bin\';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| log_bin       | ON    |
 --------------- -------

# grant replication slave on *.* to \'backup\'@\'%\' identified by \'1234\'

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 | %         |
 --------------- ------------------------------------------- -----------

CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(64) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`)
) CHARSET=utf8;

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

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)

# mysqldump -uroot -proot -hlocalhost test > /export/data/test.sql

# log-bin=mysql
# server-id=1 #192.168.10.1

# show VARIABLES like \'log_bin\';

stop slave;
change master to
    master_host=\'192.168.10.10\',    # -- master的IP
    master_user=\'backup\',            # -- 之前在master上创建的用户
    master_password=\',
    master_log_file=\'mysql-bin.000002\', #  -- master上 show master status \G 提供的信息
    master_log_pos=154;

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)

# mysql> unlock tables;
# Query OK, 0 rows affected (0.00 sec)

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

# mysql> insert into article (title,content) values (\'mysql master and slave\',\'record the cluster building succeed!:)\');
# Query OK, 1 row affected (0.00 sec)

# alter table table-name disable keys

# alter table table-name enable keys

mysql> show variables like \'profiling\';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| profiling     | OFF   |
 --------------- ------- 
1 row in set, 1 warning (0.00 sec)
mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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.15 sec)
mysql> show profiles;
 ---------- ------------ ------------------------------------------------------- 
| Query_ID | Duration   | Query                                                 |
 ---------- ------------ ------------------------------------------------------- 
|        1 | 0.00086150 | show variables like \'profiling\'                       |
|        2 | 0.15027550 | insert into article values (null,\'test profile\',\':)\') |
 ---------- ------------ -------------------------------------------------------

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

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

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

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

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: 0

        org.mybatis
        mybatis-spring
        1.3.2
        org.mybatis
        mybatis
        3.4.6
        org.springframework
        spring-core
        5.0.8.RELEASE
        org.springframework
        spring-aop
        5.0.8.RELEASE
        org.springframework
        spring-jdbc
        5.0.8.RELEASE
        com.alibaba
        druid
        1.1.6
        mysql
        mysql-connector-java
        6.0.2
        org.springframework
        spring-context
        5.0.8.RELEASE
        org.springframework
        spring-aspects
        5.0.8.RELEASE
        org.projectlombok
        lombok
        1.16.22
        org.springframework
        spring-test
        5.0.8.RELEASE
        junit
        junit
        4.12

引入依赖

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;
}

spring配置文件

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

dp.properties

mybatis-config.xml

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

ArticleMapper.java

        select * from article
        insert into article (title,content) values (#{title},#{content})
        delete from article where id=#{id}

ArticleMapper.xml

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 {
    /**
     * key为read或write
     * value为DAO方法的前缀
     * 什么前缀开头的方法使用读数据员，什么开头的方法使用写数据源
     */
    public static final Map<String, List> METHOD_TYPE_MAP = new HashMap<String, List>();
    /**
     * 由我们指定数据源的id，由Spring切换数据源
     *
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        System.out.println("数据源为：" 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(",")));
            }
        }
    }
}

RoutingDataSourceImpl

结合spring配置文件

package top.zhenganwen.mysqloptimize.dataSource;
/**
 * DataSourceHandler class
 *

DataSourceHandler

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
 *
 * 配置切面，根据方法前缀设置读、写数据源
 * 项目启动时会加载该bean，并按照配置的切面（哪些切入点、如何增强）确定动态代理逻辑
 * @author zhenganwen,blog:zhenganwen.top
 * @date 2018/12/29
 */
@Component
//声明这是一个切面，这样Spring才会做相应的配置，否则只会当做简单的bean注入
@Aspect
@EnableAspectJAutoProxy
public class DataSourceAspect {
    /**
     * 配置切入点：DAO包下的所有类的所有方法
     */
    @Pointcut("execution(* top.zhenganwen.mysqloptimize.mapper.*.*(..))")
    public void aspect() {
    }
    /**
     * 配置前置增强，对象是aspect()方法上配置的切入点
     */
    @Before("aspect()")
    public void before(JoinPoint point) {
        String className = point.getTarget().getClass().getName();
        String invokedMethod = point.getSignature().getName();
        System.out.println("对 " className "$" invokedMethod " 做了前置增强，确定了要使用的数据源类型");
        Set dataSourceType = METHOD_TYPE_MAP.keySet();
        for (String type : dataSourceType) {
            List prefixList = METHOD_TYPE_MAP.get(type);
            for (String prefix : prefixList) {
                if (invokedMethod.startsWith(prefix)) {
                    DataSourceHandler.setDataSource(type);
                    System.out.println("数据源为：" type);
                    return;
                }
            }
        }
    }
}

DataSourceAspect