MySQL 调优攻略

最新推荐文章于 2024-07-12 13:27:28 发布

程序员Jie

最新推荐文章于 2024-07-12 13:27:28 发布

阅读量2.1k

点赞数 3

分类专栏：数据库文章标签： mysql java php linux

本文链接：https://blog.csdn.net/zhj52666/article/details/108180565

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL 高级 (调优攻略)

1. Linux 环境下安装MySQL

MySQL 版本 5.x 5.0-5.1:早期产品延续 5.4-5.x:MySQL整合了三方公司的新存储引擎（推荐5.5之后）

1.1 清空环境已有的MySQL

1、检查是否已经安装过mysql，执行命令

[root@localhost /]# rpm -qa | grep mysql

2、删除命令

[root@localhost /]# rpm -e --nodeps mysql-libs-5.1.73-5.el6_6.x86_64

再次执行查询命令，查看是否删除

[root@localhost /]# rpm -qa | grep mysql

3、查询所有Mysql对应的文件夹

[root@localhost /]# whereis mysql
mysql: /usr/bin/mysql /usr/include/mysql
[root@localhost lib]# find / -name mysql
/data/mysql
/data/mysql/mysql

4、删除相关目录或文件

[root@localhost /]#  rm -rf /usr/bin/mysql /usr/include/mysql /data/mysql /data/mysql/mysql

5、验证是否删除完毕

[root@localhost /]# whereis mysql
mysql:
[root@localhost /]# find / -name mysql
[root@localhost /]#

1.2 安装MySQL

1、检查mysql用户组和用户是否存在，如果没有，则创建

[root@localhost /]# cat /etc/group | grep mysql
[root@localhost /]# cat /etc/passwd |grep mysql
[root@localhost /]# groupadd mysql
[root@localhost /]# useradd -r -g mysql mysql
[root@localhost /]#

2、从官网下载是用于Linux的Mysql安装包

下载命令：

[root@localhost /]#  wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

也可以直接到 mysql官网选择对应版本进行下载。

3、在执行wget命令的目录下或你的上传目录下找到Mysql安装包：mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
执行解压命令：

[root@localhost /]#  tar xzvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
[root@localhost /]# ls
mysql-5.7.24-linux-glibc2.12-x86_64
mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

解压完成后，可以看到当前目录下多了一个解压文件，移动该文件到**/usr/local/下，并将文件夹名称修改为mysql**。

如果**/usr/local/下已经存在mysql**，请将已存在mysql文件修改为其他名称，否则后续步骤可能无法正确进行。

执行命令如下：

[root@localhost /]# mv mysql-5.7.24-linux-glibc2.12-x86_64 /usr/local/
[root@localhost /]# cd /usr/local/
[root@localhost /]# mv mysql-5.7.24-linux-glibc2.12-x86_64 mysql

如果**/usr/local/下不存在mysql**文件夹，直接执行如下命令，也可达到上述效果。

[root@localhost /]# mv mysql-5.7.24-linux-glibc2.12-x86_64 /usr/local/mysql

4、在**/usr/local/mysql**目录下创建data目录

[root@localhost /]# mkdir /usr/local/mysql/data

5、更改mysql目录下所有的目录及文件夹所属的用户组和用户，以及权限

[root@localhost /]# chown -R mysql:mysql /usr/local/mysql
[root@localhost /]# chmod -R 755 /usr/local/mysql

6、编译安装并初始化mysql,务必记住初始化输出日志末尾的密码（数据库管理员临时密码）

[root@localhost /]# cd /usr/local/mysql/bin
[root@localhost bin]# ./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql

补充说明：

第6步时，可能会出现错误：

出现该问题首先检查该链接库文件有没有安装使用命令进行核查
[root@localhost bin]# rpm -qa|grep libaio   
[root@localhost bin]# 
运行命令后发现系统中无该链接库文件
[root@localhost bin]#  yum install  libaio-devel.x86_64
安装成功后，继续运行数据库的初始化命令，此时可能会出现如下错误：

执行如下命令后：
[root@localhost bin]#  yum -y install numactl
执行无误之后，再重新执行第4步初始化命令，无误之后再进行第5步操作！

7、运行初始化命令成功后，输出日志如下：

记录日志最末尾位置**root@localhost:**后的字符串，此字符串为mysql管理员临时登录密码。

8、编辑配置文件my.cnf，添加配置如下

[root@localhost bin]#  vi /etc/my.cnf

[mysqld]
datadir=/usr/local/mysql/data
port=3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0
max_connections=600
innodb_file_per_table=1
lower_case_table_names=1

character_set_server=utf8
character_set_client=utf8
collation_server=utf8_general_ci

9、测试启动mysql服务器

[root@localhost /]# /usr/local/mysql/support-files/mysql.server start

显示如下结果，说明数据库安装并可以正常启动

异常情况

如果出现如下提示信息
Starting MySQL... ERROR! The server quit without updating PID file
查看是否存在mysql和mysqld的服务，如果存在，则结束进程，再重新执行启动命令
#查询服务
ps -ef|grep mysql | grep -v grep
ps -ef|grep mysqld | grep -v grep

#结束进程
kill -9 PID

#启动服务
/usr/local/mysql/support-files/mysql.server start

10、添加软连接，并重启mysql服务

[root@localhost /]#  ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql 
[root@localhost /]#  ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
[root@localhost /]#  service mysql restart

11、登录mysql，修改密码(密码为步骤5生成的临时密码)

[root@localhost /]#  mysql -u root -p
Enter password:
mysql>set password for root@localhost = password('your password');

12、开放远程连接

mysql>use mysql;
msyql>update user set user.Host='%' where user.User='root';
mysql>flush privileges;

13、设置开机自动启动

1、将服务文件拷贝到init.d下，并重命名为mysql
[root@localhost /]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
2、赋予可执行权限
[root@localhost /]# chmod +x /etc/init.d/mysqld
3、添加服务
[root@localhost /]# chkconfig --add mysqld
4、显示服务列表
[root@localhost /]# chkconfig --list

开机自启：开启 chkconfig mysql on 、关闭 chkconfig mysql on 、查看 ntsysv

至此，mysql5.7.24版本的数据库安装，已经完成。

2. 索引

索引(index)是帮助MySQL高效获取数据的数据结构（有序）

2.1 索引的优劣

优势：

类似于书籍的目录，提高数据检索，降低IO成本
通过索引对数列排序，降低数据排序成本，降低CPU消耗

劣势：

实际索引也是一张表，该表保存了主键与索引字段、并指向实体类的记录，所以索引列也是占用空间的
虽然索引大大提高了查询效率，但同时降低了更新的速度，因为更新还得保存索引信息

2.2 索引常见的数据结构

BTREE （ InnoDB支持、MyISAM支持、Memory支持）最常见的索引类型
HASH （ InnoDB不支持、MyISAM不支持、Memory支持）使用场景简单
R-tree （ InnoDB不支持、MyISAM支持、Memory不支持）空间索引，主要处理地理空间数据、通常用的少
Full-text （ InnoDB 5.6+支持、MyISAM支持、Memory不支持）全文索引，主要用于全文检索

我们平常说的索引，没有特别指定只的都是B+树（多路平衡搜索树、并不一定是二叉树）。其中聚集索引、复合索引、前缀索引、唯一索引默认都是B+ Tree 索引

2.3 BTREE 数据结构

BTree 又叫多路平衡搜索树

一棵m叉特性：

树中每个阶段最多包涵m个子节点
除根节点与叶子节点外，每个节点至少包含【ceil(m/2)】个孩子节点 ceil向上取整
若根节点不是叶子节点，则至少包含两个孩子节点
所有叶子节点都在同一层
每个非叶子节点由n个key与n+1个指针组成，其中【ceil(m/2)-1】<=n <=m-1

以5叉BTree 为例，key 的数量：公式推导【ceil(m/2)-1】<=n <=m-1 所以 2<= n <=4 ,当n>4时，中间节点分裂到父节点，两边节点分裂成两个节点

最大为四个数据块，五个叉所以到4的时候做处理、持续向上分裂

2.4 B+ TREE 数据结构

B+ Tree 是B树的变种

B+树与B树的区别：

n叉树B+树最多有n个key,而B树最多含n-1个key
B+树的叶子节点保存所有的key的信息，依key大小顺序排列
所有非叶子节点都可以看做key的索引部分

2.5 MySQL中的 B+ TREE

MySQL索引对B+Tree进行了优化，在相邻的叶子节点加了一个链表指针，就形成了带有顺序指针的B+Tree，提高了访问性能

方便范围搜索，提高区间访问性能

2.6 索引分类

单值索引 : 即一个索引只包含一个列，一个表可以有多个单列索引
唯一索引 : 索引列的值必须唯一，但允许有空值（主键索引，不能为NULL)
复合索引 : 即一个索引包含多个列（有点像多及目录）| （name , age) (a,b,c,d …)

2.7 索引语法

2.7.1 环境准备

CREATE DATABASE demo_01 DEFAULT CHARSET = utf8mb4;

USE demo_01;

CREATE TABLE city (
    city_id INT(11) NOT NULL AUTO_INCREMENT,
    city_name VARCHAR(50) NOT NULL,
    country_id INT(11) NOT NULL,
    PRIMARY KEY (city_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE country (
    country_id INT(11) NOT NULL AUTO_INCREMENT,
    country_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (country_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO city VALUES (1,'北京',1);
INSERT INTO city VALUES (2,'NewYork',2);
INSERT INTO city VALUES (3,'上海',1);
INSERT INTO city VALUES (4,'天津',1);

INSERT INTO country VALUES (1,'China');
INSERT INTO country VALUES (2,'America');
INSERT INTO country VALUES (3,'Japan');
INSERT INTO country VALUES (4,'UK');

2.7.2 创建索引

# UNIQUE 唯一  FULLTEXT 全文索引 SPATIAL 空间索引  INDEX 后边多个列是复合索引
CREATE 	[UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tal_name(index_col_name,...)

index_col_name : column_name[(length)][ASC|DESC]

eg:
CREATE INDEX idx_city_name ON city(city_name);
# 查看索引
SHOW INDEX FROM city;   # SHOW INDEX FROM city \G; 变成列的形式
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY       |            1 | city_id     | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_city_name |            1 | city_name   | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2.7.3 删除索引

DROP INDEX index_name ON tbl_name;

2.7.4 ALTER命令

# 单值
ALTER TABLE tbl_name ADD INDEX dept_index(dept);
# 唯一索引
ALTER TABLE tbl_name ADD UNIQUE INDEX name_index(name);
# 复合索引
ALTER TABLE tbl_name ADD INDEX name_index(dept,name);

3. 数据库逻辑分层

1 连接层（提供与客户端连接的服务）

2 服务层（提供用户使用的接口【select、】、提供SQL优化器【MySQL QUery Optimizer】

3 引擎层（【InnoDB一：事物有限行锁、MyISAM：性能有先表锁】）

4 存储层（存储数据））

3.1 数据库引擎

# 查询所有支持的引擎
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

# 查询默认引擎
mysql> SHOW variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

# 创建表会设置引擎
CREATE TABLE country (
    country_id INT(11) NOT NULL AUTO_INCREMENT,
    country_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (country_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

4. SQL优化

性能低、执行时间长、等待时间长、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理

4.1 SQL解析

编写过程：

select dinstinct … from … join … on … where … group by … having … order by … limit …

解析过程：

from … on … join … where … group by … having … select dinstinct … order by … limit …

4.2 SQL优化

主要是优化索引索引相当于目录

索引： index 帮助MySQL高效查询数据的数据结构

索引的弊端：

	1. 索引本身很大，可以存放在内存、硬盘（通常为硬盘）

 		2. 索引不是所有情况都适用： a.少量数据 b.频繁更新的字段 c.很少使用的字段
              		3. 索引可以提高查询的效率（增删改会降低）

优势：

提高查询效率（降低IO使用率）
排好序、不需要排序（降低CPU使用率）

5. SQL 性能问题

分析SQL的执行计划：explain , 模拟SQL优化器执行SQL语句，可以让开发人员自己知道自己SQL的执行计划
MySQL查询优化器会干扰我们的优化

优化方法：官网

查询执行计划：explain + SQL语句

explain select * from tbl_name;

属性	表示的含义
select_type	查询类型
table	表
type	类型
possible_keys	预测用到的索引
key	实际使用的索引
key_len	实际使用索引的长度
ref	表之间的引用
rows	通过索引查询到的数据量
Extra	额外的信息

# 测试
mysql> EXPLAIN SELECT * FROM city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# 测试 条件查询
mysql> EXPLAIN SELECT * FROM city WHERE city_name = '北京';
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | idx_city_name | idx_city_name | 152     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# 测试 多表关联
mysql> EXPLAIN SELECT * FROM city,country WHERE city.country_id = country.country_id AND city_name = '北京';
+----+-------------+---------+------------+--------+---------------+---------------+---------+-------------------------+------+----------+-------+
| id | select_type | table   | partitions | type   | possible_keys | key           | key_len | ref                     | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------+---------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | city    | NULL       | ref    | idx_city_name | idx_city_name | 152     | const                   |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | country | NULL       | eq_ref | PRIMARY       | PRIMARY       | 4       | demo_01.city.country_id |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+--------+---------------+---------------+---------+-------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
# 测试 子查询
mysql> EXPLAIN SELECT * FROM city WHERE city.country_id = (SELECT country_id FROM country);
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | city    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    4 |    25.00 | Using where |
|  2 | SUBQUERY    | country | NULL       | index | NULL          | PRIMARY | 4       | NULL |    4 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

表的执行顺序

因数量的个数改变而改变的原因 : 笛卡尔积数据量小会优先查询
id值不同 id值越大越优先查询（本质：在嵌套子查询时，先查内层，再查外层）
id值有相同又有不同，id值越大越优先；id值相同顺序执行

5.1 select_type

primary 主查询
subquery 子查询
simple 简单查询
derived 衍生查询

（查询遇到零时表，

a.from子查询只有一张表 from(select * from course where tid in (1 ,2))

b.在from子查询中，如果有 from(select * from course where tid = 1 union select * from course where tid = 2) ）

5.2 type 索引类型、类型

system>const>eq_ref>ref>range>index>all ，要优化前提有索引

其中 system>const只是理想级别，实际达到ref>range

system: 只有一条数据的系统表；衍生表只有一条数据的主查询
const: 仅仅能查到一条数据的SQL,用于Primary key 或者 unique 索引（类型与索引类型有关）
eq_ref: 唯一索引：对于每个索引键的查询，会匹配唯一的一行数据（有且只有一个，不能多、不能0）常见主键、唯一
ref: 非唯一性索引：每个索引键的查询，会返回匹配的所有行数据（0，多）
range：检索指定范围的行，where 后面是一个范围查询（between ,in 有时候会失效【转为无索引all】,> < >=)
index：查询全部数据查询全部索引的数据
all：查询全部数据

5.3 possible_keys 预测用到的索引

一种预测，不准确

5.4 key 实际用到的索引

5.5 key_len 索引的长度

用于判断复合索引是否完全被使用（utf8一个字符占3个字节、gbk 一个字符占2个字节、latin一个字符占1个字节)

5.6 ref

注意与type中ref的区分

作用：指明当前表所参照的字段 const指常量

5.7 rows

被索引优化查询的数据个数(实际通过索引查询到的)

5.8 Extra

using filesort （常见order by) using temporary（常见group by)

using filesort ：性能消耗大，需要“额外”的一次排序（查询）

对于单索引，如果排序和查找的是一个字段，则不会出现using filesort;

复合索引：不能跨列（最佳左前缀） a1,a2,a3 如果是a1,a3 或者a2,a3则会出现using filesort;

单索引WHERE哪些字段就按哪些字段排序，复合索引要按顺序使用，不要跨列或无序使用
using temporary：性能消耗大，用到了零时表

查询哪些列就用哪些列分组
using index：性能提升，索引覆盖原因，不读取源文件，只从索引就能获得值只要使用到的列都在索引中，就是覆盖索引

会对 possible key 和 key 产生影响

a.如果没有where 只在key

b.如果有where,则出现在 possible key 和 key
using where: 如果需要回表查询就有
impossible where: where 条件不可能 i

6. 优化示例

6.1 优化示例

CREATE TABLE test
(
a1 INT(4) NOT NULL,
a2 INT(4) NOT NULL,
a3 INT(4) NOT NULL,
a4 INT(4) NOT NULL
);
ALTER TABLE test ADD INDEX idx_a1_a2_a3_a4(a1,a2,a3,a4);

# 推荐写法：where后边顺序和索引顺序一致
mysql> EXPLAIN SELECT a1,a2,a3,a4 FROM test WHERE a1 = 1 AND a2 =2 AND a3 = 3 AND a4 = 4;
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_a1_a2_a3_a4 | idx_a1_a2_a3_a4 | 16      | const,const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 虽然编写顺序不一致，但是sql优化器优化，结果与上条一致
mysql> EXPLAIN SELECT a1,a2,a3,a4 FROM test WHERE a4 = 4 AND a2 =2 AND a3 = 3 AND a1 = 1;
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_a1_a2_a3_a4 | idx_a1_a2_a3_a4 | 16      | const,const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 由于a4跨列使用 失效 a1,a2,a3连续
mysql> EXPLAIN SELECT a1,a2,a3,a4 FROM test WHERE a1 = 1 AND a2 =2 AND a4 = 4 ORDER BY a3;
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_a1_a2_a3_a4 | idx_a1_a2_a3_a4 | 8       | const,const |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
# a4 失效 a1,a3 跨列使用，出现Using filesort 多了一次额外的排序/查找
mysql> EXPLAIN SELECT a1,a2,a3,a4 FROM test WHERE a1 = 1 AND a4 = 4 ORDER BY a3;
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra                                    |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+------------------------------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_a1_a2_a3_a4 | idx_a1_a2_a3_a4 | 4       | const |    1 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

总结：如果（a,b,c,d）复合索引顺序全部一致（且不跨列使用），则复合索引全部使用，如果顺序部分一致（且不跨列使用），使用部分索引

6.2 单表优化案例

CREATE TABLE book
(
bid INT(4) PRIMARY KEY,
bname VARCHAR(4) NOT NULL,
authorid INT(4) NOT NULL,
publicid INT(4) NOT NULL,
typeid INT(4) NOT NULL
);
INSERT INTO book VALUES (1,'Java',1,1,2);
INSERT INTO book VALUES (2,'Jvm',2,1,2);
INSERT INTO book VALUES (3,'C',3,2,1);
INSERT INTO book VALUES (4,'C++',4,2,3);

# 原始效果
mysql> EXPLAIN SELECT bid FROM book WHERE typeid IN (2,3) AND authorid = 1 ORDER BY typeid DESC;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
# 优化 加索引
ALTER TABLE book ADD INDEX idx_bta (bid,typeid,authorid);  #索引顺序
ALTER TABLE book ADD INDEX idx_tab (typeid,authorid,bid);
# 删除索引
DROP INDEX idx_bta ON book;
mysql> EXPLAIN SELECT bid FROM book WHERE typeid IN (2,3) AND authorid = 1 ORDER BY typeid DESC;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | book  | NULL       | range | idx_tab       | idx_tab | 8       | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
ALTER TABLE book ADD INDEX idx_atb (authorid,typeid,bid);
DROP INDEX idx_tab ON book;
# a.索引不能跨列使用（最佳左前缀），保持索引的定义和使用顺序一致性
# b.索引需要逐步优化
# c.将含in的范围查询放到 where 条件的最后防止失效
# useing index(不需要回原表) useing where(需要回原表)
mysql> EXPLAIN SELECT bid FROM book WHERE authorid = 1 AND typeid =2 ORDER BY typeid DESC;
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | book  | NULL       | ref  | idx_atb       | idx_atb | 8       | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

6.3 多表优化案例

CREATE TABLE teacher
(
bid INT(4) PRIMARY KEY,
cid INT(4) NOT NULL
);

INSERT INTO teacher VALUES (1,2);
INSERT INTO teacher VALUES (2,1);
INSERT INTO teacher VALUES (3,3);

CREATE TABLE course
(
cid INT(4) PRIMARY KEY,
cname VARCHAR(20)
);

INSERT INTO course VALUES (1,'Java');
INSERT INTO course VALUES (2,'Python');
INSERT INTO course VALUES (3,'Kotlin');
# 左连接
SELECT * FROM teacher t LEFT OUTER JOIN course c ON t.cid = c.cid WHERE c.cname = 'Java';
mysql> EXPLAIN SELECT * FROM teacher t LEFT OUTER JOIN course c ON t.cid = c.cid WHERE c.cname = 'Java';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 |    33.33 | Using where                                        |
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
# Using join buffer 是 Mysql自动优化加缓存
# 如何加索引？ 小表驱动大表 where 小表.x = 大表.x
# 一般情况左外连接给左表加索引，右连接给右表加
ALTER TABLE teacher ADD INDEX index_teacher_cid(cid);
ALTER TABLE course ADD INDEX index_course_cname(cname);
mysql> EXPLAIN SELECT * FROM teacher t LEFT OUTER JOIN course c ON t.cid = c.cid WHERE c.cname = 'Java';
+----+-------------+-------+------------+------+----------------------------+--------------------+---------+---------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys              | key                | key_len | ref           | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+----------------------------+--------------------+---------+---------------+------+----------+--------------------------+
|  1 | SIMPLE      | c     | NULL       | ref  | PRIMARY,index_course_cname | index_course_cname | 83      | const         |    1 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | t     | NULL       | ref  | index_teacher_cid          | index_teacher_cid  | 4       | demo_01.c.cid |    1 |   100.00 | Using index              |
+----+-------------+-------+------------+------+----------------------------+--------------------+---------+---------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

# 三张表优化A,B,C 	a.小表驱动大表 b.索引建在经常查询的字段上

7 避免索引失效的原则

复合索引，不要跨列或者无序使用（最佳左前缀）
复合索引，尽量使用全索引匹配
不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效【对于复合索引，如果左边失效，右边也全部失效。】
复合索引，不能使用不等于（!= <>）或 is null (is not null) ，否则索引失效，右侧也全部失效

SQL优化，是一种概率层的优化，至于是否真的进行优化需要通过 EXPLAIN 进行推测

体验概率情况（> < =）受服务层的SQL优化器影响

一般情况范围查询（> < in）,之后的索引失效补救：尽量使用索引覆盖
like 尽量以常量开头，不要以“%”开头，否则失效比如： name like ’%s%‘; 如果必须使用，索引覆盖会一定程度解决这个问题
尽量不要使用类型转换（显式，隐式），否则索引失效
尽量不要使用or，否则索引失效甚至可以将左侧失效

8. 一些其它优化方法

exist 和 in

如果主查询的数据集大，则使用 In

如果子查询的数据集大，则使用 exist

exist 语法：将主查询的结果，放到子查询结果中进行条件校验（看子查询是否有数据，如果有数据，则校验成功），如果符合校验，则保留数据

select … from table where exist/in (子查询)
order by 优化

using filesort 有两种算法：双路排序、单路排序（根据IO的次数）

MySql 4.1 之前默认使用双路排序：扫描两次磁盘（1.从磁盘读取排序字段，对字段排序（在 buffer 缓冲区排序） 2.扫描其他字段）IO较消耗性能

MySql 4.1 之后默认使用单路排序：只读取一次（全部字段），在buffer中排序，但此种单路排序会有一定隐患，不一定真的是一次IO,可能多次IO 原因数据量特别大无法将字段的数据一次性读取完毕，会进行分片读取

注意：单路排序比双路排序占用更多的buffer，如果数据量过大可以调大buffer大小 set max_length_for_sort_data = 1024; 单位byte

如果buffer设置值过低会自动由单路转双路（太低：需要排序列的总大小超过了 set max_length_for_sort_data ）

策略：
- 选择使用单路、双路；调整buffer 的容量大小；
- 避免使用 select * … 效率低，不容易索引覆盖
- 复合索引不要跨列使用 using filesort
- 保证全部排序字段，排序的一致性（都是升或者降）

9. 慢查询日志

SQL排查 – 慢查询日志 MySQL提供的一种日志记录，用于记录MySQL中响应时间超过阀值的SQL语句（long_query_time ，默认十秒）

默认是关闭的：开发调优打开，部署关闭：

# 查看
SHOW VARIABLES LIKE '%slow_query_log%';
# 临时开启 退出并且重启服务自动关闭
SET GLOBAL slow_query_log = 1;
# 慢查询 阀值 重新登录不需要重启服务
SET GLOBAL long_query_time = 5;
# 可以查看有几条属于慢SQL 结果记录在日志里
SELECT SLEEP(10);
SHOW GLOBAL STATUS LIKE '%slow_queries%';

查看方式
- cat 日志位置
- mysqldumpslow 工具查看慢SQL，可以通过一些过滤条件快速查找出需要定位的慢SQL
  
  mysqldumpslow -help
  
  s:排序方式 r:逆序 l:锁定时间g:正则匹配模式
  
  —获取返回记录最多的三个
  
  mysqldumpslow -s r -t 3 /文件位置
  
  —获取访问次数最多的三个
  
  mysqldumpslow -s c -t 3 /文件位置
  
  —按照时间排序，前十条包含left join 查询语句的SQL
  
  mysqldumpslow -s t -t 10 -g “left join” /文件位置

10. 分析海量数据

10.1 模拟海量数据环境

CREATE DATABASE testdata;

USE testdata

CREATE TABLE dept
(
dno INT(5) PRIMARY KEY DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT '',
loc VARCHAR(20) DEFAULT ''
)ENGINE=INNODB DEFAULT CHARSET = utf8;

CREATE TABLE emp
(
eid INT(5) PRIMARY KEY,
ename VARCHAR(20) NOT NULL DEFAULT '',
job VARCHAR(20) DEFAULT '',
deptno INT(5) NOT NULL DEFAULT 0
)ENGINE=INNODB DEFAULT CHARSET = utf8;

# 通过存储过程（无return）/ 存储函数 添加数据
# 产生随机字符串
DELIMITER $
CREATE FUNCTION randstring(n INT) RETURNS VARCHAR(255)
BEGIN
	DECLARE all_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;   # 循环
	
	WHILE i<n
	DO
		SET return_str = CONCAT(return_str, SUBSTRING(all_str, FLOOR(RAND()*52)+1, 1));  #(0-1)* 52 0-52
		SET i = i + 1;
	END WHILE;
	RETURN return_str;
END $
# 如果报错 this function has none of SETERMINISTIC ... 是因为与之前慢查询冲突了
# 解决冲突： SHOW VARIABLES LIKE "%log_bin_trust_function_creators%";        SET GLOBAL log_bin_trust_function_creators = 1;

SELECT randstring(8);
# 产生随机整数
DELIMITER $
CREATE FUNCTION randnum() RETURNS INT(5)
BEGIN
	DECLARE i INT DEFAULT 0;   # 循环
	
	SET i = FLOOR(RAND()*100);
	RETURN i;
END $
SELECT randnum();

# 插入海量数据
DELIMITER $
CREATE PROCEDURE insert_emp(IN eid_start INT(10),IN data_times INT(10))
BEGIN
	DECLARE i INT DEFAULT 0;  
	SET autocommit = 0; 
	REPEAT
		INSERT INTO emp VALUES(eid_start + i, randstring(5), 'other', randnum());
		SET i = i + 1;
		UNTIL i = data_times
	END REPEAT;
	COMMIT;
END $

DELIMITER $
CREATE PROCEDURE insert_dept(IN eid_start INT(10),IN data_times INT(10))
BEGIN
	DECLARE i INT DEFAULT 0;  
	SET autocommit = 0; 
	REPEAT
		INSERT INTO dept VALUES(eid_start + i, randstring(6), randstring(10));
		SET i = i + 1;
		UNTIL i = data_times
	END REPEAT;
	COMMIT;
END $

# 插入数据
DELIMITER ;
CALL insert_emp(1000,800000);
CALL insert_dept(0,99);

10.2 分析海量数据

# 打开 SET profiling = ON; SHOW VARIABLES LIKE '%profiling%';
# 1.profiles
SHOW profiles; #打开后 会记录SQL花费的时间  缺点不够精确 不能知道cpu io
# 2.精确分析profile
SHOW profile ALL FOR QUERY 30;
SHOW profile cpu,block io FOR QUERY 30;
# 3.全局查询日志：记录开启之后的 全部SQL (仅在开发中使用，它会占用大量性能，生产环境关闭)
# 默认是关闭的
SHOW VARIABLES LIKE 'general_log';
SET GLOBAL general_log = 1;  # 开启后记录在 mysql.general_log 表中
SET GLOBAL log_output = 'table'; # 设置记录在表中 还可以设置记录在文件 
SET GLOBAL log_output = 'file'; SET GLOBAL general_log_file = '/temp/general.log'; 
# 查询全局日志
SELECT * FROM mysql.general_log;

11. 锁机制

解决资源共享，而造成并发问题

乐观锁：不加锁，通过版本号控制悲观锁：加锁

11.1 锁分类

操作类型
- 读锁（共享锁）：对同一个数据，多个读操作同时进行，互不干扰（大家一起看衣服）
- 写锁（互斥锁）：如果当时写操作没有完毕，则无法进行其它的读操作，写操作（购买衣服，带到试衣间）
操作范围
- 表锁：一次性对一张表加锁。如MyISAM存储引擎使用表锁，开销小，加锁快；无死锁；但锁的范围大，容易出现锁冲突，并发度低
  
  通过加锁释放锁控制
- 行锁：一次性对一条数据加锁。如InnoDB存储引擎使用行锁，开销大，加锁慢；容易死锁；锁的范围小，不容易出现锁冲突，并发度高（很小概率发生高并发问题：脏读、幻读、不可重复读…）
  
  通过事物控制
- 页锁：页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

11.2 锁操作

# 读写锁 LOCK TABLE 表1 READ/WRITE , 表2  READ/WRITE , ...
# 查看加锁的表
SHOW OPEN TABLES;
# 测试表  表锁
CREATE TABLE tablelock
(
id INT(4) PRIMARY KEY DEFAULT 0,
NAME VARCHAR(20) NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET = utf8; 
# 加读锁 当前会话只能对该表读，不能对该表写，也不能读写其他表  其他会话可以进行读操作，如果进行写操作将会一直等待锁被释放，操作其他表无影响
LOCK TABLE tablelock READ;
# 释放锁
UNLOCK TABLES;
# 加写锁 当前会话 可以对加了写锁的表 进行任何增删改查操作；但是不能操作其他表 其他会话中多加锁的表操作，可以对该表增删改查，但是得等拿到锁的人释放锁
LOCK TABLE tablelock WRITE;
# 测试表  行锁
CREATE TABLE linelock
(
id INT(4) PRIMARY KEY DEFAULT 0,
NAME VARCHAR(20) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET = utf8; 
# 研究行锁 关闭自动提交
SET autocommit = 0;
# 会话0 写操作
INSERT INTO linelock VALUES(6,'a6');
# 会话1 写操作 同样的数据
UPDATE linelock SET name = 'ax' where id = 6;
# 对于行锁情况：
#  1.如果会话x对某条数据进行DML操作（关闭自动提交），则其他会话等待会话结束事物，才可以操作
#  2.表锁通过unlock table; 行锁 是通过事物解锁的
# 行锁操作不同数据，互不影响

# 行锁注意事项
#  a.如果没有索引，则行锁转为表锁
#   --索引类发生类型转换，索引失效，行锁也会转为表锁
#  b.行锁的一种特殊情况：间隙锁：值在范围内，但却不存在
UPDATE linelock SET NAME = 'x' WHERE id > 1 AND id < 9;  # 如果中间没有 7 就会产生间隙锁 在此期间对7操作会等待
# 行锁：如果有where 就是where 后边的范围 不是实际的值
#  缺点：性能损耗大
#  优点：并发能力强，效率高
# 建议：高并发建议InnoDB

# 如果仅仅是查询数据 对别人增删改没影响
#  FOR UPDATE 对query 语句加锁
SELECT * FROM linelock WHERE id = 2 FOR UPDATE; # 测试一定要关闭自动提交  FOR UPDATE d

11.3 分析表锁定

# 查看加锁的表  0 没加锁 1加锁
SHOW OPEN TABLES; 
# 分析表锁定的严重程度
SHOW STATUS LIKE 'table%';
Table_locks_immediate	982   #  即可能获得锁的数
Table_locks_waited	0         # 等待 越大锁的竞争越大
# 一般建议 看 Table_locks_immediate/Table_locks_waited > 5000 建议InnoDB引擎, 否则MyISAM引擎

# 行锁分析
SHOW STATUS LIKE '%innodb_row_lock%';
Innodb_row_lock_current_waits	0 # 当前正在等待的锁的数量
Innodb_row_lock_time	0         # 等待总时长
Innodb_row_lock_time_avg	0     # 平均等待时长
Innodb_row_lock_time_max	0     # 最大等待时长
Innodb_row_lock_waits	0         # 等待次数

程序员Jie

关注

3
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
MySQL 调优攻略

MySQL 对于一个Java开发者来说，几乎是必不可少的技能，那么我们在学完MySQL的基础操作后，我们就需要在性能上做出要求，需要学习掌握MySQL的优化技巧，本篇文章就是一篇MySQL调优攻略，将带给你对MySQL数据库更深入的理解。
复制链接

扫一扫

专栏目录