mysql 优化_MySQL优化面试

概述

为什么要优化

• 系统的吞吐量瓶颈往往出现在数据库的访问速度上
• 随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
• 数据是存放在磁盘上的，读写速度无法和内存相比

如何优化

• 设计数据库时：数据库表、字段的设计，存储引擎
• 利用好MySQL自身提供的功能，如索引等
• 横向扩展：MySQL集群、负载均衡、读写分离
• SQL语句的优化(收效甚微)

索引

关键字与数据的映射关系称为索引(==包含关键字和对应的记录在磁盘中的地址==)。关键字是从数据当中提取的用于标识、检索数据的特定内容。

索引检索为什么快？

• 关键字相对于数据本身，==数据量小==
• 关键字是==有序==的，二分查找可快速确定位置

图书馆为每本书都加了索引号(类别-楼层-书架)、字典为词语解释按字母顺序编写目录等都用到了索引。

MySQL中索引类型

普通索引(key)，唯一索引(unique key)，主键索引(primary key)，全文索引(fulltext key)

三种索引的索引方式是一样的，只不过对索引的关键字有不同的限制：

• 普通索引：对关键字没有限制
• 唯一索引：要求记录提供的关键字不能重复
• 主键索引：要求关键字唯一且不为null

索引管理语法

查看索引

show create table 表名：

索引覆盖

如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据(否则只要有一个字段没有建立索引就会做全表扫描)，这叫索引覆盖。因此我们需要尽可能的在select后==只写必要的查询字段==，以增加索引覆盖的几率。

这里值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。

语法细节(要点)

在满足索引使用的场景下(where/order by/join on或索引覆盖)，索引也不一定被使用

字段要独立出现

比如下面两条SQL语句在语义上相同，但是第一条会使用主键索引而第二条不会。

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

like查询，不能以通配符开头

比如搜索标题包含mysql的文章：

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

这种SQL的执行计划用不了索引(like语句匹配表达式以通配符开头)，因此只能做全表扫描，效率极低，在实际工程中几乎不被采用。而一般会使用第三方提供的支持中文的全文索引来做。

但是 关键字查询 热搜提醒功能还是可以做的，比如键入mysql之后提醒mysql 教程、mysql 下载、mysql 安装步骤等。用到的语句是：

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

这种like是可以利用索引的(当然前提是title字段建立过索引)。

复合索引只对第一个字段有效

建立复合索引：

alter table person add index(first_name,last_name);

其原理就是将索引先按照从first_name中提取的关键字排序，如果无法确定先后再按照从last_name提取的关键字排序，也就是说该索引表只是按照记录的first_name字段值有序。

因此select * from person where first_name = ?是可以利用索引的，而select * from person where last_name = ?无法利用索引。

那么该复合索引的应用场景是什么？==组合查询==

比如对于select * person from first_name = ? and last_name = ?，复合索引就比对first_name和last_name单独建立索引要高效些。很好理解，复合索引首先二分查找与first_name = ?匹配的记录，再在这些记录中二分查找与last_name匹配的记录，只涉及到一张索引表。而分别单独建立索引则是在first_name索引表中二分找出与first_name = ?匹配的记录，再在last_name索引表中二分找出与last_name = ?的记录，两者取交集。

or，两边条件都有索引可用

一但有一边无索引可用就会导致整个SQL语句的全表扫描

状态值，不容易使用到索引

如性别、支付状态等状态值字段往往只有极少的几种取值可能，这种字段即使建立索引，也往往利用不上。这是因为，一个状态值可能匹配大量的记录，这种情况MySQL会认为利用索引比全表扫描的效率低，从而弃用索引。索引是随机访问磁盘，而全表扫描是顺序访问磁盘，这就好比有一栋20层楼的写字楼，楼底下的索引牌上写着某个公司对应不相邻的几层楼，你去公司找人，与其按照索引牌的提示去其中一层楼没找到再下来看索引牌再上楼，不如从1楼挨个往上找到顶楼。

如何创建索引

• 建立基础索引：在where、order by、join字段上建立索引。
• 优化，组合索引：基于业务逻辑
• 如果条件经常性出现在一起，那么可以考虑将多字段索引升级为==复合索引==
• 如果通过增加个别字段的索引，就可以出现==索引覆盖==，那么可以考虑为该字段建立索引
• 查询时，不常用到的索引，应该删除掉

索引的存储结构

BTree

btree(多路平衡查找树)是一种广泛应用于==磁盘上实现索引功能==的一种数据结构，也是大多数数据库索引表的实现。

以add index(first_name,last_name)为例：

BTree的一个node可以存储多个关键字，node的大小取决于计算机的文件系统，因此我们可以通过减小索引字段的长度使结点存储更多的关键字。如果node中的关键字已满，那么可以通过每个关键字之间的子节点指针来拓展索引表，但是不能破坏结构的有序性，比如按照first_name第一有序、last_name第二有序的规则，新添加的韩香就可以插到韩康之后。白起 < 韩飞 < 韩康 < 李世民 < 赵奢 < 李寻欢 < 王语嫣 < 杨不悔。这与二叉搜索树的思想是一样的，只不过二叉搜索树的查找效率是log(2,N)(以2为底N的对数)，而BTree的查找效率是log(x,N)(其中x为node的关键字数量，可以达到1000以上)。

从log(1000+,N)可以看出，少量的磁盘读取即可做到大量数据的遍历，这也是btree的设计目的。

B+Tree聚簇结构

聚簇结构(也是在BTree上升级改造的)中，关键字和记录是存放在一起的。

在MySQL中，仅仅只有Innodb的==主键索引为聚簇结构==，其它的索引包括Innodb的非主键索引都是典型的BTree结构。

哈希索引

在索引被载入内存时，使用哈希结构来存储。

一般情况下我们创建的表对应一组存储文件，使用MyISAM存储引擎时是一个.MYI和.MYD文件，使用Innodb存储引擎时是一个.ibd和.frm(表结构)文件。

当数据量较大时(一般千万条记录级别以上)，MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，==保证其单个文件的执行效率==。

水平分割和垂直分割

水平分割：通过建立结构相同的几张表分别存储数据

垂直分割：将经常一起使用的字段放在一个单独的表中，分割后的表记录之间是一一对应关系。

分表原因

• 为数据库减压
• 分区算法局限
• 数据库支持不完善(5.1之后mysql才支持分区操作)

id重复的解决方案

• 借用第三方应用如memcache、redis的id自增器
• 单独建一张只包含id一个字段的表，每次自增该字段作为数据记录的id

集群

横向扩展：从根本上(单机的硬件处理能力有限)提升数据库性能 。由此而生的相关技术：==读写分离、负载均衡==

安装和配置主从复制

环境

• Red Hat Enterprise Linux Server release 7.0 (Maipo)(虚拟机)
• mysql5.7(下载地址)

安装和配置

解压到对外提供的服务的目录(我自己专门创建了一个/export/server来存放)

tar xzvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /export/servercd /export/servermv mysql-5.7.23-linux-glibc2.12-x86_64 mysql

添加mysql目录的所属组和所属者：

groupadd mysqluseradd -r -g mysql mysqlcd /export/serverchown -R mysql:mysql mysql/chmod -R 755 mysql/

创建mysql数据存放目录(其中/export/data是我创建专门用来为各种服务存放数据的目录)

mkdir /export/data/mysql

初始化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

如果成功会显示mysql的root账户的初始密码，记下来以备后续登录。如果报错缺少依赖，则使用yum instally依次安装即可

配置my.cnf

vim /etc/my.cnf[mysqld]basedir=/export/server/mysqldatadir=/export/data/mysqlsocket=/tmp/mysql.sockuser=mysqlserver-id=10 # 服务id，在集群时必须唯一，建议设置为IP的第四段port=3306# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-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.logpid-file=/export/data/mysql/mysql.pid## include all files from the config directory#!includedir /etc/my.cnf.d

将服务添加到开机自动启动

cp /export/server/mysql/support-files/mysql.server /etc/init.d/mysqld

启动服务

service mysqld start

配置环境变量，在/etc/profile中添加如下内容

# mysql envMYSQL_HOME=/export/server/mysqlMYSQL_PATH=$MYSQL_HOME/binPATH=$PATH:$MYSQL_PATHexport PATH

使配置即可生效

source /etc/profile

使用root登录

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

登录上去之后，更改root账户密码(我为了方便将密码改为root)，否则操作数据库会报错

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

设置服务可被所有远程客户端访问

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

这样就可以在宿主机使用navicat远程连接虚拟机linux上的mysql了

配置主从节点

配置master

以linux(192.168.10.10)上的mysql为master，宿主机(192.168.10.1)上的mysql为slave配置主从复制。

修改master的my.cnf如下

[mysqld]basedir=/export/server/mysqldatadir=/export/data/mysqlsocket=/tmp/mysql.sockuser=mysqlserver-id=10port=3306# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-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# 开启二进制日志expire-logs-days=7 # 设置日志过期时间，避免占满磁盘binlog-ignore-db=mysql# 不使用主从复制的数据库binlog-ignore-db=information_schemabinlog-ignore-db=performation_schemabinlog-ignore-db=sysbinlog-do-db=test#使用主从复制的数据库[mysqld_safe]log-error=/export/data/mysql/error.logpid-file=/export/data/mysql/mysql.pid## include all files from the config directory#!includedir /etc/my.cnf.d

重启master

service mysqld restart

登录master查看配置是否生效(ON即为开启，默认为OFF)：

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

在master的数据库中建立备份账号：backup为用户名，%表示任何远程地址，用户back可以使用密码1234通过任何远程客户端连接master

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

查看user表可以看到我们刚创建的用户：

mysql> use mysqlmysql> select user,authentication_string,host from user;+---------------+-------------------------------------------+-----------+| user | authentication_string | host |+---------------+-------------------------------------------+-----------+| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | % || mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || backup | *A4B6157319038724E3560894F7F932C8886EBFCF | % |+---------------+-------------------------------------------+-----------+

新建test数据库，创建一个article表以备后续测试

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

重启服务并刷新数据库状态到存储文件中(with read lock表示在此过程中，客户端只能读数据，以便获得一个一致性的快照)

[root@zhenganwen ~]# service mysqld restartShutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS! [root@zhenganwen mysql]# mysql -uroot -prootmysql> flush tables with read lock;Query OK, 0 rows affected (0.00 sec)

查看master上当前的二进制日志和偏移量(记一下其中的File和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,sysExecuted_Gtid_Set: 1 row in set (0.00 sec)

File表示实现复制功能的日志，即上图中的Binary log；Position则表示Binary log日志文件的偏移量之后的都会同步到slave中，那么在偏移量之前的则需要我们手动导入。

主服务器上面的任何修改都会保存在二进制日志Binary log里面，从服务器上面启动一个I/O thread(实际上就是一个主服务器的客户端进程)，连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log里面。从服务器上面开启一个SQL thread定时检查Realy log，如果发现有更改立即把更改的内容在本机上面执行一遍。

如果一主多从的话，这时主库既要负责写又要负责为几个从库提供二进制日志。此时可以稍做调整，将二进制日志只给某一从，这一从再开启二进制日志并将自己的二进制日志再发给其它从。或者是干脆这个从不记录只负责将二进制日志转发给其它从，这样架构起来性能可能要好得多，而且数据之间的延时应该也稍微要好一些

手动导入，从master中导出数据

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

将test.sql中的内容在slave上执行一遍。

配置slave

修改slave的my.ini文件中的[mysqld]部分

log-bin=mysqlserver-id=1 #192.168.10.1

保存修改后重启slave，WIN+R->services.msc->MySQL5.7->重新启动

登录slave检查log_bin是否以被开启：

show VARIABLES like 'log_bin';

配置与master的同步复制：

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

启用slave节点并查看状态

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: 0Master_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:ProgramDataMySQLMySQL Server 5.7Datamaster.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)

注意查看第4、14、15三行，若与我一致，表示slave配置成功

测试

关闭master的读取锁定

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

向master中插入一条数据

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

查看slave是否自动同步了数据

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

至此，主从复制的配置成功！：)

使用mysqlreplicate命令快速搭建 Mysql 主从复制

读写分离

读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。因为主从复制要求slave不能写只能读(如果对slave执行写操作，那么show slave status将会呈现Slave_SQL_Running=NO，此时你需要按照前面提到的手动同步一下slave)。

方案一、定义两种连接

就像我们在学JDBC时定义的DataBase一样，我们可以抽取出ReadDataBase,WriteDataBase implements DataBase，但是这种方式无法利用优秀的线程池技术如DruidDataSource帮我们管理连接，也无法利用Spring AOP让连接对DAO层透明。

方案二、使用Spring AOP

如果能够使用Spring AOP解决数据源切换的问题，那么就可以和Mybatis、Druid整合到一起了。

我们在整合Spring1和Mybatis时，我们只需写DAO接口和对应的SQL语句，那么DAO实例是由谁创建的呢？实际上就是Spring帮我们创建的，它通过我们注入的数据源，帮我们完成从中获取数据库连接、使用连接执行 SQL 语句的过程以及最后归还连接给数据源的过程。

如果我们能在调用DAO接口时根据接口方法命名规范(增addXXX/createXXX、删deleteXX/removeXXX、改updateXXXX、查selectXX/findXXX/getXX/queryXXX)动态地选择数据源(读数据源对应连接master而写数据源对应连接slave)，那么就可以做到读写分离了。

项目结构

数据类

package top.zhenganwen.mysqloptimize.entity;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data@AllArgsConstructor@NoArgsConstructorpublic class Article { private int id; private String title; private String content;}

mapper接口和配置文件

ArticleMapper.java

package top.zhenganwen.mysqloptimize.mapper;import org.springframework.stereotype.Repository;import top.zhenganwen.mysqloptimize.entity.Article;import java.util.List;@Repositorypublic interface ArticleMapper { List findAll(); void add(Article article); void delete(int id);}

ArticleMapper.xml

<?xml version="1.0" encoding="UTF-8" ?> select * from article  insert into article (title,content) values (#{title},#{content})  delete from article where id=#{id} 

核心类

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 { /** * key为read或write * value为DAO方法的前缀 * 什么前缀开头的方法使用读数据员，什么开头的方法使用写数据源 */ public static final Map> METHOD_TYPE_MAP = new HashMap>(); /** * 由我们指定数据源的id，由Spring切换数据源 * * @return */ @Override protected Object determineCurrentLookupKey() { System.out.println("数据源为："+DataSourceHandler.getDataSource()); return DataSourceHandler.getDataSource(); } public void setMethodType(Map map) { for (String type : map.keySet()) { String methodPrefixList = map.get(type); if (methodPrefixList != null) { METHOD_TYPE_MAP.put(type, Arrays.asList(methodPrefixList.split(