阅读 183

MySQL 调优攻略

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步时,可能会出现错误:

img

出现该问题首先检查该链接库文件有没有安装使用 命令进行核查

[root@localhost bin]# rpm -qa|grep libaio   
[root@localhost bin]# 
复制代码

运行命令后发现系统中无该链接库文件

[root@localhost bin]#  yum install  libaio-devel.x86_64
复制代码

安装成功后,继续运行数据库的初始化命令,此时可能会出现如下错误:

img

执行如下命令后:

[root@localhost bin]#  yum -y install numactl
复制代码

执行无误之后,再重新执行第4步初始化命令,无误之后再进行第5步操作!

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

img

记录日志最末尾位置**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
复制代码

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

img

异常情况

如果出现如下提示信息

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
复制代码

img

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');
复制代码

img

12、开放远程连接

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

img

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 索引的优劣

优势:

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

劣势:

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

2.2 索引常见的数据结构

  1. BTREE ( InnoDB支持、MyISAM支持、Memory支持) 最常见的索引类型
  2. HASH ( InnoDB不支持、MyISAM不支持、Memory支持) 使用场景简单
  3. R-tree ( InnoDB不支持、MyISAM支持、Memory不支持)空间索引,主要处理地理空间数据、通常用的少
  4. 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树的区别:

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

2.5 MySQL中的 B+ TREE

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

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

2.6 索引分类

  1. 单值索引 : 即一个索引只包含一个列,一个表可以有多个单列索引
  2. 唯一索引 : 索引列的值必须唯一,但允许有空值 (主键索引,不能为NULL)
  3. 复合索引 : 即一个索引包含多个列 (有点像多及目录)| (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. 索引可以提高查询的效率(增删改会降低)
复制代码

优势:

  1. 提高查询效率 (降低IO使用率)
  2. 排好序、不需要排序(降低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

  1. primary 主查询

  2. subquery 子查询

  3. simple 简单查询

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

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

  1. using filesort :性能消耗大,需要“额外”的一次排序(查询)

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

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

    单索引WHERE哪些字段就按哪些字段排序,复合索引要按顺序使用,不要跨列或无序使用

  2. using temporary:性能消耗大,用到了零时表

    查询哪些列就用哪些列分组

  3. using index:性能提升,索引覆盖 原因,不读取源文件,只从索引就能获得值 只要使用到的列都在索引中,就是覆盖索引

    会对 possible key 和 key 产生影响

    a.如果没有where 只在key

    b.如果有where,则出现在 possible key 和 key

  4. using where: 如果需要回表查询就有

  5. 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 避免索引失效的原则

  1. 复合索引,不要跨列或者无序使用(最佳左前缀)

  2. 复合索引,尽量使用全索引匹配

  3. 不要在索引上进行任何操作(计算、函数、类型转换),否则索引失效 【对于复合索引,如果左边失效,右边也全部失效。】

  4. 复合索引,不能使用不等于(!= <>) 或 is null (is not null) ,否则索引失效,右侧也全部失效

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

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

    一般情况范围查询(> < in),之后的索引失效 补救: 尽量使用索引覆盖

  5. like 尽量以常量开头,不要以“%”开头,否则失效 比如: name like ’%s%‘; 如果必须使用, 索引覆盖会一定程度解决这个问题

  6. 尽量不要使用类型转换(显式,隐式),否则索引失效

  7. 尽量不要使用or,否则索引失效 甚至可以将左侧失效

8. 一些其它优化方法

  1. exist 和 in

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

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

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

    select .. from table where exist/in (子查询)

  2. 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. 慢查询日志

  1. 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%';
    复制代码
  2. 查看方式

    • 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 锁分类

  1. 操作类型

    • 读锁(共享锁):对同一个数据,多个读操作同时进行,互不干扰 (大家一起看衣服)
    • 写锁(互斥锁):如果当时写操作没有完毕,则无法进行其它的读操作,写操作 (购买衣服,带到试衣间)
  2. 操作范围

    • 表锁:一次性对一张表加锁。 如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         # 等待次数
复制代码
文章分类
后端
文章标签