Mysql基础介绍

安装mysql-server

[root@localhost mysql]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

#yum install mysql
#yum install mysql-server
#yum install mysql-devel
#CentOS 7 版本将MySQL数据库软件从默认的程序列表中移除，用mariadb代替了

#官网下载
# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
# rpm -ivh mysql-community-release-el7-5.noarch.rpm
# yum install mysql-community-server

安装成功后重启mysql服务。

# service mysqld restart   或者：systemctl restart mysqld.service
# service mysqld status    或者：systemctl status mysqld.service

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-92x6JG4D-1647763550960)(C:\Users\d\AppData\Roaming\Typora\typora-user-images\image-20211030203243678.png)]

初次安装mysql，root账户没有密码。

[root@localhost mysql]#  mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

设置密码

mysql> set password for 'root'@'localhost' =password('123456');
Query OK, 0 rows affected (0.00 sec)

不需要重启数据库即可生效。

在mysql安装过程中如下内容：

Installed:
  mysql-community-client.x86_64 0:5.6.26-2.el7                mysql-community-devel.x86_64 0:5.6.26-2.el7                
  mysql-community-libs.x86_64 0:5.6.26-2.el7                  mysql-community-server.x86_64 0:5.6.26-2.el7               

Dependency Installed:
  mysql-community-common.x86_64 0:5.6.26-2.el7                                                                            

Replaced:
  mariadb.x86_64 1:5.5.41-2.el7_0          mariadb-devel.x86_64 1:5.5.41-2.el7_0   mariadb-libs.x86_64 1:5.5.41-2.el7_0  
  mariadb-server.x86_64 1:5.5.41-2.el7_0  

所以安装完以后mariadb自动就被替换了，将不再生效。()

[root@yl-web yl]# rpm -qa |grep mariadb
[root@yl-web yl]# 

配置mysql

1、编码

mysql配置文件为/etc/my.cnf

最后加上编码配置

[mysql]
default-character-set =utf8

2、远程连接设置

把在所有数据库的所有表的所有权限赋值给位于所有IP地址的root用户。

mysql> grant all privileges on *.* to root@'%'identified by '123456';

如果是新用户而不是root，则要先新建用户

mysql>create user 'username'@'%' identified by 'password';  

此时就可以进行远程连接了。

MySQL分区

一般情况下我们创建的表对应一组存储文件，
使用MyISAM存储引擎时是一个.MYI和.MYD文件，
使用Innodb存储引擎时是一个.ibd和.frm（表结构）文件。
当数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率。
最常见的分区方案是按id分区，如下将id的哈希值对10取模将数据均匀分散到10个.ibd存储文件中：

CREATE TABLE article(
	id INT AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(64),
	content TEXT
)PARTITION BY HASH(id) PARTITIONS 10

-- /var/lib/mysql/mydb/
-rw-r----- 1 mysql mysql   8624 Feb  1 06:04 article.frm
-rw-r----- 1 mysql mysql  98304 Feb  1 06:04 article#P#p0.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 06:04 article#P#p1.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 06:04 article#P#p2.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 06:04 article#P#p3.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 06:04 article#P#p4.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 06:04 article#P#p5.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 06:04 article#P#p6.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 06:04 article#P#p7.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 06:04 article#P#p8.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 06:04 article#P#p9.ibd

– 服务端的表分区对于客户端是透明的==，客户端还是照常插入数据，但服务端会按照分区算法分散存储数据。

MySQL分区算法

#分区依据的字段必须是主键的一部分==，分区是为了快速定位数据，因此该字段的搜索频次较高应作为强检索字段，否则依照该字段分区毫无意义
1: hash(field)  --仅适用于整型字段
2: key(field)	==处理字符串==的
3: range算法	==条件分区==
4: list算法	==条件分区==

1 :hash(field)
– 相同的输入得到相同的输出。输出的结果跟输入是否具有规律无关。仅适用于整型字段

CREATE TABLE article(
	id INT AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(64),
	content TEXT
)PARTITION BY HASH(id) PARTITIONS 10

2: key(field)
– 和hash(field)的性质一样，只不过key是处理字符串的，比hash()多了一步从字符串中计算出一个整型在做取模操作。

CREATE TABLE article_key(
	id INT AUTO_INCREMENT,
	title VARCHAR(64),
	content TEXT,
	PRIMARY KEY (id,title)	-- 要求分区依据字段必须是主键的一部分
)PARTITION BY KEY(title) PARTITIONS 10

3 range算法
– 条件分区算法，按照数据大小范围分区（将数据使用某种条件，分散到不同的分区中）。
– 如下，按文章的发布时间将数据按照2018年8月、9月、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
);

-rw-r----- 1 mysql mysql   8670 Feb  1 07:01 article_range.frm
-rw-r----- 1 mysql mysql  98304 Feb  1 07:01 article_range#P#p201808.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 07:01 article_range#P#p201809.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 07:01 article_range#P#p201810.ibd

-- 注意：条件运算符只能使用==less than==，这以为着较小的范围要放在前面，
-- 比如上述p201808,p201819,p201810分区的定义顺序依照created_time数值范围从小到大，不能颠倒。
INSERT INTO article_range VALUES(NULL,'MySQL优化','内容示例',1535731180);
FLUSH TABLES;	-- 使操作立即刷新到磁盘文件

-rw-r----- 1 mysql mysql  98304 Feb  1 07:04 article_range#P#p201808.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 07:01 article_range#P#p201809.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 07:01 article_range#P#p201810.ibd

4 list算法

-- 也是一种条件分区，按照列表值分区（in (值列表)）
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;

-rw-r----- 1 mysql mysql   8658 Feb  1 07:21 article_list.frm
-rw-r----- 1 mysql mysql  98304 Feb  1 07:21 article_list#P#published.ibd
-rw-r----- 1 mysql mysql  98304 Feb  1 07:22 article_list#P#writing.ibd

SELECT * FROM article_list;

Mysql分区管理语法

range/list
– 使用range对文章按照月份归档，随着时间的增加，我们需要增加一个月份：

1.增加分区

ALTER TABLE article_range ADD PARTITION(
	PARTITION p201811 VALUES LESS THAN (1543593599)	-- select UNIX_TIMESTAMP('2018-11-30 23:59:59')
	-- more
);

2.删除分区

ALTER TABLE article_range DROP PARTITION p201808;
-- 注意：==删除分区后，分区中原有的数据也会随之删除！==

key/hash

1.新增分区

ALTER TABLE article_key ADD PARTITION PARTITIONS 4

2. 销毁分区

ALTER TABLE article_key COALESCE PARTITION 6

– 注意: key/hash分区的管理不会删除数据，但是每一次调整（新增或销毁分区）都会将所有的数据重写分配到新的分区上。效率极低，
– 最好在设计阶段就考虑好分区策略。

分区的使用
– 当数据表中的数据量很大时，分区带来的效率提升才会显现出来。
– 只有检索字段为分区字段时，分区带来的效率提升才会比较明显。因此，分区字段的选择很重要，并且业务逻辑要尽可能地根据分区字段做相应调整（尽量使用分区字段作为查询条件）。

水平分割和垂直分割

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

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

分表原因

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

id重复的解决方案

– 借用第三方应用如memcache、redis的id自增器

– 单独建一张只包含id一个字段的表，每次自增该字段作为数据记录的id

mysql主从复制

配置主从节点

配置master

#1: 配置master
以linux（192.168.10.10）上的mysql为master，宿主机（192.168.10.1）上的mysql为slave配置主从复制。

-- 修改master的my.cnf如下
vi /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
basedir=/export/SERVER/mysql
datadir=/export/DATA/mysql
SOCKET=/tmp/mysql.sock
USER=mysql
SERVER-id=1
PORT=3306
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

重启master

root@wangcheng-soakmaster:~# systemctl  restart mysql

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

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | VALUE |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

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

root@wangcheng-soakmaster:~# mysql -u root -p
Enter PASSWORD: 123456
mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' IDENTIFIED BY '1234';
QUERY OK, 0 ROWS affected, 1 warning (0.02 sec)

-- 查看user表可以看到我们刚创建的用户:
mysql> USE mysql
mysql> SELECT USER,authentication_string,HOST FROM USER;

USER              authentication_string                      HOST       

----------------  -----------------------------------------  -----------

root              *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9  localhost  
mysql.session     *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE  localhost  
mysql.sys         *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE  localhost  
debian-sys-maint  *5F1FDBACBFCF615E9755E5376BB987313A3682B2  localhost  
root              *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9  %          
BACKUP            *A4B6157319038724E3560894F7F932C8886EBFCF  %         

-- 新建test数据库，创建一个article表以备后续测试
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

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@wangcheng-soakmaster:~# systemctl  restart mysql.service
root@wangcheng-soakmaster:~# mysql -u root -p 
Enter PASSWORD: 123456
mysql> 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,sys
Executed_Gtid_Set: 
1 ROW IN SET (0.00 sec)

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

主库导出sql

mysqldump -uroot -p123456 -hlocalhost test > /home/DATA/test.sql
-- 导入从库
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

mysql> USE test
DATABASE CHANGED
mysql> source /home/DATA/test.sql

配置slave

-- 修改slave的my.ini文件中的[mysqld]部分
vi /etc/mysql/mysql.conf.d/mysqld.cnf

SERVER-id               = 2
log_bin                 = /var/LOG/mysql/mysql-bin.log

-- 保存修改后重启slave
root@wangcheng-soakmaster:~# systemctl  restart mysql.service

-- 登录slave检查log_bin是否以被开启 (在从库 上面执行)
SHOW VARIABLES LIKE 'log_bin';
Variable_name  VALUE   

-------------  --------

log_bin        ON      

-- 配置与master的同步复制：(在从库 上面执行)
STOP SLAVE; 
CHANGE MASTER TO
    MASTER_HOST='192.168.111.138',	-- master的IP
    MASTER_USER='backup',		    -- 之前在master上创建的用户
    MASTER_PASSWORD='1234',
    MASTER_LOG_FILE='mysql-bin.000003', -- master上 show master status \G 提供的信息
    MASTER_LOG_POS=154;

-- 启用slave节点并查看状态 (在从库 上面执行)
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G

-- Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
-- 1. 检查主从server_id 
 SHOW VARIABLES LIKE 'server_id';

2 .检查主从状态：

主库:
mysql> SHOW MASTER STATUS;

从库：
mysql> SHOW MASTER STATUS;

-- 3. auto.cnf中的server-uuid  主从不能一样
root@wangcheng-soakmaster:/var/lib/mysql# cat auto.cnf 
[auto]
SERVER-UUID=8939c40f-4371-11ea-8a6b-000c29b6995e
SERVER-UUID=8939c40f-4371-11ea-8a6b-000c29b6995e

4 . 问题解决

-- 停止从库的mysqld服务，删除他的auto.cnf文件，再启动数据库服务即可：
root@wangcheng-soaksalve:/var/lib/mysql# systemctl stop mysql.service
root@wangcheng-soaksalve:/var/lib/mysql# mv auto.cnf  auto.cnf.bak
root@wangcheng-soaksalve:/var/lib/mysql# systemctl start mysql.service

校验
mysql>  SHOW SLAVE STATUS \G
*************************** 1. ROW ***************************
               Slave_IO_State: Waiting FOR MASTER TO send EVENT
                  MASTER_HOST: 192.168.111.138
                  MASTER_USER: BACKUP
                  MASTER_PORT: 3306
                Connect_Retry: 60
              MASTER_LOG_FILE: mysql-bin.000003
          Read_Master_Log_Pos: 154
               RELAY_LOG_FILE: wangcheng-soaksalve-relay-bin.000006
                RELAY_LOG_POS: 367
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试主从复制

-- 1.关闭master的读取锁定

mysql> UNLOCK TABLES;
QUERY OK, 0 ROWS affected (0.00 sec)

-- 2. 向master中插入一条数据
mysql> USE test
mysql> 
INSERT INTO article (title,content) VALUES ('mysql master and slave','record the cluster building succeed!:)'); 
INSERT INTO article (title,content) VALUES ('mysql master and slave2','record the cluster building succeed2!:)');
INSERT INTO article (title,content) VALUES ('mysql master and slave2','record the cluster building succeed2!:)');
INSERT INTO article (title,content) VALUES ('mysql master and slave2','record the cluster building succeed2!:)');
INSERT INTO article (title,content) VALUES ('mysql master and slave2','record the cluster building succeed2!:)');
INSERT INTO article (title,content) VALUES ('mysql master and slave2','record the cluster building succeed2!:)');
INSERT INTO article (title,content) VALUES ('mysql master and slave2','record the cluster building succeed2!:)');
INSERT INTO article (title,content) VALUES ('mysql master and slave2','record the cluster building succeed2!:)');
INSERT INTO article (title,content) VALUES ('mysql master and slave2','record the cluster building succeed2!:)');
INSERT INTO article (title,content) VALUES ('mysql master and slave2','record the cluster building succeed2!:)');

SELECT * FROM article;

mysql读写分离

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

典型的服务器配置

max_connections

#最大客户端连接数
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | VALUE |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+

table_open_cache

#表文件句柄缓存（表数据是存储在磁盘上的，缓存磁盘文件的句柄方便打开文件读取数据）
mysql> SHOW VARIABLES LIKE 'table_open_cache';
+------------------+-------+
| Variable_name    | VALUE |
+------------------+-------+
| table_open_cache | 2000  |
+------------------+-------+

key_buffer_size

#索引缓存大小（将从磁盘上读取的索引缓存到内存，可以设置大一些，有利于快速检索）
mysql> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+---------+
| Variable_name   | VALUE   |
+-----------------+---------+
| key_buffer_size | 16777216 |  16M*1024*1024 B
+-----------------+---------+
SELECT 16*1024*1024 = 16777216

innodb_buffer_pool_size

Innodb存储引擎缓存池大小（对于Innodb来说最重要的一个配置，如果所有的表用的都是Innodb，
-- 那么甚至建议将该值设置到物理内存的80%，Innodb的很多性能提升如索引都是依靠这个）
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+---------+
| Variable_name           | VALUE   |
+-------------------------+---------+
| innodb_buffer_pool_size | 134217728 |  B
+-------------------------+---------+
SELECT 134217728/1024/1024 = 128M

innodb_file_per_table

#innodb中，表数据存放在.ibd文件中，如果将该配置项设置为ON，
-- 那么一个表对应一个ibd文件，否则所有innodb共享表空间）
SHOW VARIABLES LIKE 'innodb_file_per_table';
Variable_name          VALUE   
---------------------  --------
innodb_file_per_table  ON      

压测工具 mysqlslap

C:\Users\zaw>mysqlslap --auto-generate-SQL -uroot -p123456
mysqlslap --auto-generate-SQL -uroot -p123456

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

1 . sql 执行顺序

SELECT DISTINCT
	<select_list>
FROM
	<left_table> 
<join_type> JOIN <right_table> ON 
	<join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT <OFFSET>,<ROWS>

/*
distinct，用于对查询出的结果集去重（若查出各列值相同的多条结果则只算一条）
join，关联表查询，若将两个表看成两个集合，则能有7种不同的查询效果（将在下节介绍）。
group by，通常与合计函数结合使用，将结果集按一个或多个列值分组后再合计
having，通常与合计函数结合使用，弥补where条件中无法使用函数
order by，按某个标准排序，结合asc/desc实现升序降序
limit，如果跟一个整数n则表示返回前n条结果；如果跟两个整数m,n则表示返回第m条结果之后的n条结果（不包括第m条结果）
*/

七种Join方式

-- tbl_dept 部门表：主键id、部门名称deptName，部门楼层locAdd
-- tbl_emp  员工表：主键id，姓名name、所属部门deptId
USE test;
CREATE TABLE `tbl_dept` (
  `id` INT (11) NOT NULL AUTO_INCREMENT,
  `deptName` VARCHAR (30) DEFAULT NULL,
  `locAdd` VARCHAR (40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 ;

CREATE TABLE `tbl_emp` (
  `id` INT (11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR (20) DEFAULT NULL,
  `deptId` INT (11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  #KEY `fk_dept_id` (`deptId`)
  CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `tbl_dept` (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 ;

-- 删除表
DROP TABLE tbl_emp;

INSERT INTO tbl_dept(deptName,locAdd) VALUES('技术部',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('美工部',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('总裁办',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('人力资源',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('后勤组',15);

INSERT INTO tbl_emp(NAME,deptId) VALUES('jack',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('tom',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('alice',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('john',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('faker',4);
INSERT INTO tbl_emp(NAME) VALUES('mlxg');
INSERT INTO tbl_emp(NAME) VALUES('ning');

SELECT * FROM tbl_dept;
SELECT * FROM tbl_emp;

1、左连接（A独有+AB共有）

-- 查询所有部门以及各部门的员工数：

SELECT @@sql_mode;
-- 去除 ONLY_FULL_GROUP_BY

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'  


SELECT t1.id,t1.deptName,COUNT(t2.name) AS emps 
FROM tbl_dept t1 LEFT JOIN tbl_emp t2 
ON t2.deptId=t1.id GROUP BY deptName 
ORDER BY t1.id;

+----+----------+------+
| id | deptName | emps |
+----+----------+------+
|  1 | 技术部   |    2 |
|  2 | 美工部   |    1 |
|  3 | 总裁办   |    1 |
|  4 | 人力资源 |    1 |
|  5 | 后勤组   |    0 |
+----+----------+------+

2、右连接（B独有+AB共有）

– 查询所有员工及其所属部门：

SELECT t2.id,t2.name, t1.deptname
FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 
ON t2.deptId=t1.id

3、内连接（AB共有）

– 查询两表共有的数据：

SELECT t2.id,t2.name, t1.deptname
FROM tbl_dept t1 INNER JOIN tbl_emp t2 
ON t2.deptId=t1.id

4、A独有

– 即在（A独有+AB共有）的基础之上排除B即可（通过b.id is null即可实现）：

SELECT t1.id,t1.deptName,t2.name,t2.id, t2.deptid
FROM tbl_dept t1 LEFT JOIN tbl_emp t2 
ON t2.deptId=t1.id
WHERE t2.id IS NULL

SELECT * FROM tbl_dept;
SELECT * FROM tbl_emp

5、B独有

– 与（A独有）同理：

SELECT t1.id,t1.deptName,t2.name,t2.id, t2.deptid
FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 
ON t2.deptId=t1.id
WHERE t1.id IS NULL

### 

6、A独有+B独有

– 使用union将（A独有）和（B独有）联合在一起：

SELECT a.deptName,b.name empName FROM tbl_dept a LEFT JOIN tbl_emp b 
ON a.id=b.deptId 
WHERE b.id IS NULL 
UNION 
SELECT b.deptName,a.name emptName FROM tbl_emp a LEFT JOIN tbl_dept b 
ON a.deptId=b.id 
WHERE b.id IS NULL;
+----------+---------+
| deptName | empName |
+----------+---------+
| 后勤组   | NULL    |
| NULL     | ning    |
| NULL     | mlxg    |
+----------+---------+

7、A独有+AB公共+B独有

-- 使用union（可去重）联合（A独有+AB公共）和（B独有+AB公共）
SELECT a.deptName,b.name empName  FROM tbl_dept a LEFT JOIN tbl_emp b 
ON a.id=b.deptId 
UNION 
SELECT a.deptName,b.name empName FROM tbl_dept a RIGHT JOIN tbl_emp b 
ON a.id=b.deptId;
+----------+---------+
| deptName | empName |
+----------+---------+
| 技术部   | jack    |
| 技术部   | tom     |
| 美工部   | alice   |
| 总裁办   | john    |
| 人力资源 | faker   |
| 后勤组   | NULL    |
| NULL     | ning    |
| NULL     | mlxg    |
+----------+---------+

mysql架构介绍

MySQL逻辑系统架构分为3层:

• 应用层
• MySQL服务层
• 存储引擎层

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xpqAF9Sa-1647763550964)(C:\Users\d\AppData\Roaming\Typora\typora-user-images\image-20211030170428683.png)]

mysql存储引擎

存储引擎 : 其实就是指定 表 如何存储数据，如何为存储的数据 建立索引 以及 如何更新，查询数据等技术实现的方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

MySQL存储引擎比较

特性	MyISAM	InnoDB	Memory	Archive	NDB
存储限制	没有	64TB	有	没有	没有
事务		支持			支持
锁粒度	表	行	表	行	页
B树索引	支持	支持	支持		支持
哈希索引		支持	支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持
数据压缩	支持			支持
批量插入	高	相对低	高	非常高	高
内存消耗	低	高	中	低	低
外键支持		支持
复制支持	支持	支持	支持	支持	支持
查询缓存	支持	支持	支持	支持	支持
备份恢复	支持	支持	支持	支持	支持
集群支持					支持

innodb与MyIASM存储引擎的区别:
　　1.innodb 是mysql5.5版本以后的默认存储引擎, 而MyISAM是5.5版本以前的默认存储引擎.
　　2.innodb 支持事务, 而MyISAM不支持事务
　　3.innodb 支持行级锁. 而MyIASM 它支持的是并发的表级锁.
　　4.innodb 支持外键, 而MyIASM 不支持外键
　　5.innodb与MyIASM存储引擎都采用B+TREE存储数据,

​ innodb的索引与数据存储在一个文件中,这种方式我们称之为聚合索引.
　　　　而MyIASM则会单独创建一个索引文件,也就是说,数据与索引是分离开的
　　6.在效率方面MyISAM比innodb高,但是在性能方面innodb要好一点.

物理文件

日志文件

错误日志（error log）

默认是开启的，记录运行过程中遇到的严重的错误信息，以及mysql每次启动和关闭的详细信息

二进制日志（bin log）

默认是关闭的，binlog记录数据库所有操作的ddl语句和dml语句，但不包括select语句，还包括了每个更新语句的执行时间信息，

如果是DDL语句，则直接记录到binlog日志中，而DML语句，则通过事务提交才能记录到binlog日志中

binlog主要用于实现mysql主从复制，数据备份，数据恢复。

通用查询日志（general query log）

默认是关闭的

慢查询日志（slow query log）

默认是关闭的，需要配置 slow query log

重做日志（redo log）

作用： 确保 事务的持久性；放置在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log 进行重做，从而达到事务的持久性 这一特性

回滚日志（undo log）

作用：保存事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多个版本并发控制下的读(MVCC)，也即锁定读

中继日志（relay log）

是在主从复制环境中产生的日志。

主要作用是为了从机可以从中继日志中获取到主机同步过来的SQL语句，然后执行到从机中。

数据文件

InnoDB数据文件：

.frm文件：主要存放于表相关的数据信息，主要看看表结构的定义信息。

.ibd文件：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件。

ibdata文件：使用共享表空间存储表数据和索引信息，所有表共享使用一个或者多个ibdata文件

#cd /var/lib/mysql/mysql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8lnBHR74-1647763550966)(C:\Users\d\AppData\Roaming\Typora\typora-user-images\image-20211030211250011.png)]

MyIsam数据文件：

.frm文件：主要存放与表相关的数据信息，主要包括表结构的定义信息

.myd文件：主要用来存储表数据信息。

.myi文件：主要用来存储表数据文件中任务索引的数据树。

mysql性能分析

性能分析思路

1：首先使用【慢查询日志】功能，去获取所有查询时间比较长的sql语句

2：【查看执行计划】查看有问题的sql执行计划

3：使用【show profiles】(show profile for query 2) 查看有问题的sql性能使用情况

慢日志查询

# vi  /etc/my.ini 
slow_query_log = on
slow-query-log-file = /var/log/mysql/slow-query.log
long_query_time = 1

#备注：slow_query_log_file 对应目录需要设置成mysql用户权限，或者777，无需自己创建日志文件

# 查询配置
show variables like "%slow%"; 
# 查询慢查询时间
show variables like "long_query_time";
# 查询慢查询数量
show status like "%slow_queries%"; 

profile分析语句

Query Profiler 需要自己手动启动

Query Profiler 可以分析出一条SQL语句的硬件性能瓶颈在什么地方；通常 使用explain 和slow query log无法做到精确分析，但是Query Profiler 可以定位出一条sql语句执行的各种资源的消耗情况，比如cpu,io等，以及sql执行所耗费的时间等

#show variables like '%profiling%';
profiling参数值为OFF，说明没有打开该功能。
profiling_history_size参数值为15表示，记录最近15次的查询历史。该值可以修改。

mysql> set profiling=1;

#使用
#show profiles;
#show profile cpu,block io for query 111;

mysql索引

索引介绍

索引的特点：

1：高效获取数据的数据结构

2：使用 B+树结构

3：索引是存储在磁盘文件中（可能是单独的索引文件中，也可能和数据一起存储在数据文件中）

索引的优势：

1：可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录

2：通过索引列数据进行排序，降低数据排序的成本，降低CPU的消耗

索引的劣势：

1：索引会占据磁盘空间

2：磁盘虽然会提高查询效率，但是会降低更新表的效率。

创建索引

创建表之后建立索引

CREATE TABLE user_index(
    id INT  AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(16),
    last_name VARCHAR(16),
    id_card VARCHAR(18),
    information TEXT
);

SELECT * FROM user_index;

-- 更改表结构
ALTER TABLE user_index
-- 创建一个first_name和last_name的复合索引，并命名为name
ADD KEY NAME (first_name,last_name),
-- 创建一个id_card的唯一索引，默认以字段名作为索引名
ADD UNIQUE KEY (id_card),
-- 鸡肋，全文索引不支持中文
ADD FULLTEXT KEY (information);
DESC user_index;
-- 查看表的索引
SHOW CREATE TABLE user_index;

-- 创表时指定索引
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 表名 drop KEY 索引名
ALTER TABLE user_index DROP KEY NAME;
ALTER TABLE user_index DROP KEY id_card;
ALTER TABLE user_index DROP KEY information;

/*
删除主键索引：alter table 表名 drop primary key（因为主键只有一个）。这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）：
*/
ALTER TABLE user_index DROP PRIMARY KEY   -- [Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

-- 需要取消自增长再行删除：
ALTER TABLE user_index
-- 重新定义字段
MODIFY id INT,
DROP PRIMARY KEY

---

常见索引分类

单列索引

**1：普通索引：**MySQL中基本索引类型，没有什么限制，允许在 定义索引的列中插入重复值和空值，存粹为了查询数据更快一点

**2：唯一索引：**索引列中的值必须的唯一值，但是允许为空值。

3：主键索引： 是一种特殊的唯一索引，不允许有空值。

组合索引

1：在表中的多个字段组合上创建的一个索引

2：组合索引的使用，需要遵循最左前缀原则（最左匹配原则）

3：一般情况下：建议使用组合索引代替单列索引（主键索引除外）

索引的存储结构

1：索引是在存储引擎中实现的。

2：MyISAM和InnoDB存储引擎：只支持BTree索引，也就是说默认使用BTEE,不能够更换；实际上很多存储引擎使用的是B+Tree

数据结构示例网站：

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B树和B+树

B树: (多叉平衡查找树)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ntz7D0w3-1647763550967)(C:\Users\d\AppData\Roaming\Typora\typora-user-images\image-20211031201244950.png)]

B+Tree：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4TVj55ty-1647763550968)(C:\Users\d\AppData\Roaming\Typora\typora-user-images\image-20211031201602451.png)]

说明：

B树的高度一般都是2~4高度，树的高度直接决定IO读写的次数以及查询的时间复杂度（log(n)）.

B树三层可以存储bigint类型的主键10亿条

如果是三层树结构–支撑的数据可以达到20G, 如果是四层树结构–支撑的数据可以达到几十TB

B树和B+树的区别：

最大区别在于 非叶子节点是否存储数据的问题

1：B树：非叶子节点和叶子节点都会存储数据

2：B+树：只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的是有顺链表

聚集索引（InnoDB）

1：聚集索引

InnoDB存储引擎表是索引组织表，即按照主键的顺序存储数据。
聚集索引（clustered index）就是按照每张表的主键构造一棵 B+树，树中的叶子节点存放着表中的行记录数据，因此，也将聚集索引的叶子节点称为数据页；非叶子节点中存放着仅仅是键值和指向叶子节点的偏移量。每个叶子节点（数据页）都通过一个双向链表进行连接。
由于实际的数据页只能按照一棵B+树进行排序，因此数据库中每张表只能有一个聚集索引。
聚集索引能过特别快的访问针对范围值的查询。

注意：聚集索引并不是在物理存储上是连续的，其只是在逻辑上连续，这有两点：
一、数据页是按照主键的顺序并通过双向链表链接的，因此物理存储上可以不按主键顺序存储。
二、数据页中的记录也是通过双向链表进行维护的，物理存储上同样可以不按主键顺序存储。

聚集索引的好处：
一、对于主键的排序查找非常的快（因为其叶子节点是用双向链表链接的）
二、对于主键的范围查找非常的快（因为通过叶子节点的上层中间节点，就可以得到叶结点的范围值）

2：辅助索引

辅助索引（Secondary index）也是B+树结构，但其在叶子节点中并不包含行记录的全部数据。除了包含键的值（建立辅助索引的列中的值）外，还包含了一个书签，这个书签用来告诉InnoDB引擎从哪里可以找到与索引相对应的行数据。由于InnoDB引擎是索引组织表，因此，这个书签就是相应的行数据的聚集索引键。
因为辅助索引不会对影响数据在聚集索引中的组织，所以可以有多个。

组合索引使用

节省mysql索引存储空间以及提升索引性能，可以建立组合索引

alter table 'table_name' add index index_name('col1','col2','col3')

如何选择哪些列创建组合索引：

1：常出现在where条件中的列，建议用来创建组合索引，顺序很重要：，因为组合索引会使用最左前缀原则，但是因为MySQL中存在查询优化器，所以写sql条件的顺序不一定是执行时候的where条件顺序。

2：常出现在order by和group by 语句中的列。最后按照顺序去创建组合索引。

3：常出现在select 语句中的列，建议按照顺序 创建组合索引。

最左前缀原则

就是最左优先：最左是针对于组合索引和前缀索引。

1：最左前缀匹配原则：MySQL会一直向右匹配直到遇到范围查询（> ,< , between, like ）就停止匹配，

比如：a =1 and b=2 or c>3 and d =4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的。

如果建立(a,b,d,c)顺序的索引 则都可以用到；a b d的顺序可以任意调整。

2：= 和 in 可以乱序： 比如：a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

那些情况需要创建索引

1：主键自动建立索引

2：频繁作为查询条件的字段应该创建索引（业务）

3：多表关联查询中，关联字段应该建立索引

4：查询中统计或者分组字段，应该建立索引

5：查询中排序的字段，应该建立索引

哪些情况不需要创建索引

1：表记录较少

2：经常进行增删改查的表

3：品牌更新的字段

4：where条件里使用频繁不高的字段

SQL执行计划——Explain

通过EXPLAIN分析某条SQL语句执行时的如下特征：

表的读取顺序（涉及到多张表时）
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

EXPLAIN SELECT * FROM tbl_dept

---

 id  select_type  TABLE     PARTITIONS  TYPE    possible_keys  KEY     key_len  ref       ROWS  filtered  Extra   
1  SIMPLE       tbl_dept  (NULL)      ALL     (NULL)         (NULL)  (NULL)   (NULL)       5    100.00  (NULL)  

EXPLAIN SELECT t1.id,t1.deptName,COUNT(t2.name) AS emps 
FROM tbl_dept t1 LEFT JOIN tbl_emp t2 
ON t2.deptId=t1.id GROUP BY deptName 
ORDER BY t1.id;       

​

---

id  select_type  TABLE   PARTITIONS  TYPE    possible_keys  KEY     key_len  ref       ROWS  filtered  Extra        
1  SIMPLE       t1      (NULL)      ALL     (NULL)         (NULL)  (NULL)   (NULL)       5    100.00  USING TEMPORARY; USING filesort   

参数说明：

1.id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。根据id是否相同可以分为下列三种情况
所有表项的id不同：嵌套查询，id的序号会递增，id值越大优先级越高，越先被执行

2.select_type

SIMPLE，表示此SQL是简单的select查询，查询中不包含子查询或者union
PRIMARY，查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
SUBQUERY，在select或where列表中包含的子查询
DERIVED，在from子句中的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询, 把结果放在临时表里
UNION，union右侧的select
UNION RESULT，union的结果

EXPLAIN SELECT * FROM tbl_dept a WHERE a.id = (SELECT id FROM tbl_emp WHERE id =1)

id  select_type  TABLE    PARTITIONS  TYPE    possible_keys  KEY      key_len  ref       ROWS  filtered  Extra        

---

 1  PRIMARY      a        (NULL)      const   PRIMARY        PRIMARY  4        const        1    100.00  (NULL)       
 2  SUBQUERY     tbl_emp  (NULL)      const   PRIMARY        PRIMARY  4        const        1    100.00  USING INDEX  

3 .table

表名，表示该表项是关于哪张表的，也可以是如形式：

，表示该表是表项id为N的衍生表
<unionM,N>，表示该表是表项id为M和N两者union之后的结果

4.partition

如果启用了表分区策略，则该字段显示可能匹配查询的记录所在的分区

5. type

#type显示的是访问类型，是较为重要的一个指标，
结果值 从最好到最坏依次是： 
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 。

/*
1:system，表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
2:const，表示通过索引一次就找到了，const用于比较primary key或者unique key。因为只匹配一行数据，
所以很快。若将主键置于where列表中，MySQL就能将该查询转换为一个常量
desc student;
Field Type Null Key Default Extra

---

​ id int(10) NO PRI -主键索引 (NULL) auto_increment
​ stuId int(32) YES UNI -唯一索引 (NULL)
​ name varchar(100) YES MUL (NULL)
​

explain select * from student where id =1;
id  select_type  table    partitions  type    possible_keys  key      key_len  ref       rows  filtered  Extra   

---

 1  SIMPLE       student  (NULL)      const   PRIMARY        PRIMARY  4        const        1    100.00  (NULL)                                                                                                  
    explain select * from student where stuId =123456789; 
id  select_type  table    partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra   

---

 1  SIMPLE       student  (NULL)      const   stuId          stuId   5        const        1    100.00  (NULL)  

3: eq_ref，唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
EXPLAIN SELECT t1.id,t1.deptName
FROM tbl_dept t1 LEFT JOIN tbl_emp t2
ON t2.deptId=t1.id
explain select * from tbl_dept a ,tbl_emp b where a.id =b.id;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra

---

 1  SIMPLE       a       (NULL)      ALL     PRIMARY        (NULL)   (NULL)   (NULL)          5    100.00  (NULL)  
 1  SIMPLE       b       (NULL)      eq_ref  PRIMARY        PRIMARY  4        test.a.id       1    100.00  (NULL)  

4: ref，非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，
然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。（查找是基于有序性的能利用二分，而扫描则是线性的）

explain SELECT * FROM person where firstname ='张'
id  select_type  table   partitions  type    possible_keys  key       key_len  ref       rows  filtered  Extra        
 1  SIMPLE       person  (NULL)      ref     idx_name       idx_name  93       const        1    100.00  Using index             

5: range，根据索引的有序性检索特定范围内的行，通常出现在between、<、>、in等范围检索中
explain select * from tbl_emp where id between 2 and 5;
explain select * from tbl_emp where id in(3,4,5);
explain select * from tbl_emp where id >1;

---

  id  select_type  table    partitions  type    possible_keys  key      key_len  ref       rows  filtered  Extra        
 1  SIMPLE       tbl_emp  (NULL)      range   PRIMARY        PRIMARY  4        (NULL)       4    100.00  Using where 

6: index，在索引中扫描，只需读取索引数据。
7: all，全表扫描，需要从磁盘上读取表数据。

CREATE TABLE `person` (
  `id` INT (32) NOT NULL AUTO_INCREMENT,
  `firstName` VARCHAR (30) DEFAULT NULL,
  `lastName` VARCHAR (30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX idx_name (firstName, lastName)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 ;

6: possible_keys

MySQL可以利用以快速检索行的索引。

7:key

MySQL执行时实际使用的索引

8: key_len

表示索引中每个元素最大字节数，可通过该列计算查询中使用的索引的长度（如何计算稍后详细结束）
在不损失精确性的情况下，长度越短越好。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

9: ref

显示哪一列或常量被拿来与索引列进行比较以从表中检索行

10: rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

11: Extra (特别、额外的)

包含不适合在其他列中显示但十分重要的额外信息
1：Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
	            mySQL中无法利用索引完成的排序操作称为“文件排序”

2: Using temporary：使用了临时表保存中间结果。MySQL在对查询结果聚合时使用临时表。常见于排序 order by 和分组查询 group by

3: Using index：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行（需要读磁盘），
		效率不错！如果同时出现Using where，表明索引被用来执行索引键值的查找；
		如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作

  索引覆盖：
  就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，
  而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。

  如果要使用覆盖索引:
  一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降

4:Using where：查询使用到了where语句
5:Using join buffer：使用了连接缓存
6:Impossible where：where子句的值总是false

索引失效

1、全值匹配我最爱

– 根据常量在索引字段上检索时一定能够利用到索引。
EXPLAIN SELECT * FROM person WHERE id =4;

2、最佳左前缀法则

– 对于复合索引检索时一定要遵循左前缀列在前的原则。
ALTER TABLE person ADD c5 VARCHAR(10) DEFAULT NULL, ADD c6 VARCHAR(10) DEFAULT NULL, ADD c7 VARCHAR(10) DEFAULT NULL;
CREATE INDEX idx_c5_c6_c7 ON person(c5,c6,c7);

EXPLAIN SELECT * FROM person WHERE c6=’’;
EXPLAIN SELECT * FROM person WHERE c5=’’;
EXPLAIN SELECT * FROM person WHERE c5=’’ AND c7=’’;

– 最优的做法是：
EXPLAIN SELECT * FROM person WHERE c5=’’;
EXPLAIN SELECT * FROM person WHERE c5=’’ AND c6=’’;
EXPLAIN SELECT * FROM person WHERE c5=’’ AND c6=’’ AND c7=’’;

3、不在列名上添加任何操作

– 有时我们会在列名上进行计算、函数运算、自动/手动类型转换，这会直接导致索引失效。
CREATE INDEX firstname ON person (firstname);
– 索引会失效 ,index 全表扫描
EXPLAIN SELECT * FROM person WHERE LEFT(firstName,1)=‘张’;

EXPLAIN SELECT * FROM person WHERE firstName=‘张’;

4、存储引擎无法使用索引中范围条件右边的列

EXPLAIN SELECT * FROM person WHERE c5=’’ AND c6=’’ AND c7=’’;
EXPLAIN SELECT * FROM person WHERE c5=’’ AND c6 >‘a’ AND c7=’’;

5、尽量使用索引覆盖

– 尽量使查询列和索引列保持一致，这样就能避免访问数据行而直接返回索引数据。
– 避免使用select *除非表数据很少，因为select *很大概率访问数据行
EXPLAIN SELECT * FROM person WHERE c5=’’ AND c6=’’ AND c7=’’;
EXPLAIN SELECT c5,c6,c7 FROM person WHERE c5=’’ AND c6=’’ AND c7=’’;

– Extra: Using index 表示发生了索引覆盖

6、使用 != 或 <> 时可能会导致索引失效

EXPLAIN SELECT * FROM student WHERE stuId != 123456; – 索引失效
EXPLAIN SELECT * FROM student WHERE stuId = 123456789; – 查询有返回结果，则使用了索引
EXPLAIN SELECT * FROM student WHERE stuId <> 123456789; – 索引失效

7、not null对索引也有影响

CREATE TABLE staff ( – 职员表
id INT (32) NOT NULL AUTO_INCREMENT,
Name VARCHAR (30) NOT NULL,
age INT (3) DEFAULT 0,
addr VARCHAR (100) DEFAULT NULL,
PRIMARY KEY (id),
INDEX idx_name_age_addr (NAME, age, addr)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 ;

EXPLAIN SELECT * FROM staff WHERE NAME IS NULL; – 索引未利用
EXPLAIN SELECT * FROM staff WHERE NAME IS NOT NULL;

– 若name的定义不是not null则不会有索引未利用的情况

8、like以通配符开头会导致索引失效

– like语句以通配符%开头无法利用索引会导致全索引扫描，而只以通配符结尾则不会

EXPLAIN SELECT * FROM person WHERE firstName LIKE ‘%张’; – 索引失效
EXPLAIN SELECT * FROM person WHERE firstName LIKE ‘张%’; – 使用索引

9、join on的列只要有一个没索引则全表扫描

EXPLAIN SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.id;
– a.deptId 无索引
– b.id 主键索引

---

 id  select_type  TABLE   PARTITIONS  TYPE    possible_keys  KEY     key_len  ref       ROWS  filtered  Extra  
 1  SIMPLE       a       (NULL)      ALL     (NULL)         (NULL)  (NULL)   (NULL)       7    100.00  (NULL)                                              
 1  SIMPLE       b       (NULL)      ALL     PRIMARY        (NULL)  (NULL)   (NULL)       5    100.00  USING WHERE; USING JOIN buffer (Block Nested LOOP)  

– type : All 造成全表 扫描

10、or两侧的列只要有一个没索引则全表扫描

EXPLAIN SELECT * FROM tbl_emp WHERE id=1 OR deptID=2;
EXPLAIN SELECT * FROM tbl_dept WHERE id=1 OR deptname=2;

11、字符串不加单引号索引失效
EXPLAIN SELECT * FROM person WHERE lastname=123;

mysql性能优化

服务器层面优化

1：将数据保存在内存中，保证从内存读取数据

建议 innodb_buffer_pool_size 设置总内存大小的 3/4 或者4/5。

怎样确定 innodb_buffer_pool_size 足够大，数据是从内存读取而不是硬盘？

show GLOBAL status like ‘%innodb_buffer_pool_pages_%’;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Kxg0YWJe-1647763550969)(C:\Users\d\AppData\Roaming\Typora\typora-user-images\image-20211120165328941.png)]

2：内存预热

将磁盘数据写在MySql Server启动的时候，读取到内存中

3：降低磁盘写入次数

对于生产环境来说，很多日志是不需要开启的，比如：通用查询日志，慢查询日志，错误日志。

使用足够大的写入缓存 innodb_log_file_size，推荐 innodb_log_file_size 设置为：0.25*innodb_buffer_pool_size 。

设置适合的 innodb_flush_log_at_trx_commit，和日志落盘有关系

SQL设计层面优化

1:设置中间表，一般针对统计分析功能，或者实时性不高的需求

2：为减少关联查询，创建合理的冗余字段（考虑数据库的三范式查询性能的取舍，创建冗余字段还需要注意数据一致性问题）

3：对于字段太多的大表，考虑垂直拆表（比如一个表有100多个字段）

4：对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表（比如商品表中会存储商品介绍，此时可以将商品介绍字段单独拆解到另一个表中，使 用商品id关联）

5：每张表都建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布式系统的情况下）

SQL语句优化

索引优化

1：为搜索字段（where中的条件）排序字段，select查询列，创建适合的索引，不过要考虑数据的业务场景：查询多还是增删多

2：尽量使用组合索引并注意组合索引的创建顺序，安装顺序组织查询条件，尽量将筛选粒度大的查询条件放到最左边

3：尽量使用索引覆盖，select语句中尽量不要使用*。

4：ordery by,group by 语句尽量使用到索引。

其他优化

1：尽量不要使用count(*), 尽量使用count(主键)

count(*)：查询行数，会遍历所有的行，所有的列。

count(列)：查询指定列不为null的行数(过滤null),如果列可以为空，则count(*)不等于count(列)，除非指定的列是非空的列 才会让

​ count(*) = count(列)。

count(伪列)：比如count(1)。

2：join两张表的关联字段最好都建立索引，而且最好字段类型是一样的。

3： where条件中尽量不要使用 1=1，not in语句（建议使用not exist）

4：不用mysql 内置的函数，因为内置函数不会建立查询缓存。

​ sql查询语句和查询结果都会 在第一次查询只会存储到mysql的查询缓存中，如果需要获取到查询缓存中的查询结果，结果的sql语句必须和第一次的查询sql语句一致。

5：合理利用慢查询日志，explain执行计划查询，show profile 查询sql执行时的资源使用情况。

11、字符串不加单引号索引失效
EXPLAIN SELECT * FROM person WHERE lastname=123;

mysql性能优化

服务器层面优化

1：将数据保存在内存中，保证从内存读取数据

建议 innodb_buffer_pool_size 设置总内存大小的 3/4 或者4/5。

怎样确定 innodb_buffer_pool_size 足够大，数据是从内存读取而不是硬盘？

show GLOBAL status like ‘%innodb_buffer_pool_pages_%’;

[外链图片转存中…(img-Kxg0YWJe-1647763550969)]

2：内存预热

将磁盘数据写在MySql Server启动的时候，读取到内存中

3：降低磁盘写入次数

对于生产环境来说，很多日志是不需要开启的，比如：通用查询日志，慢查询日志，错误日志。

使用足够大的写入缓存 innodb_log_file_size，推荐 innodb_log_file_size 设置为：0.25*innodb_buffer_pool_size 。

设置适合的 innodb_flush_log_at_trx_commit，和日志落盘有关系

SQL设计层面优化

1:设置中间表，一般针对统计分析功能，或者实时性不高的需求

2：为减少关联查询，创建合理的冗余字段（考虑数据库的三范式查询性能的取舍，创建冗余字段还需要注意数据一致性问题）

3：对于字段太多的大表，考虑垂直拆表（比如一个表有100多个字段）

4：对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表（比如商品表中会存储商品介绍，此时可以将商品介绍字段单独拆解到另一个表中，使 用商品id关联）

5：每张表都建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布式系统的情况下）

SQL语句优化

索引优化

1：为搜索字段（where中的条件）排序字段，select查询列，创建适合的索引，不过要考虑数据的业务场景：查询多还是增删多

2：尽量使用组合索引并注意组合索引的创建顺序，安装顺序组织查询条件，尽量将筛选粒度大的查询条件放到最左边

3：尽量使用索引覆盖，select语句中尽量不要使用*。

4：ordery by,group by 语句尽量使用到索引。

其他优化

1：尽量不要使用count(*), 尽量使用count(主键)

count(*)：查询行数，会遍历所有的行，所有的列。

count(列)：查询指定列不为null的行数(过滤null),如果列可以为空，则count(*)不等于count(列)，除非指定的列是非空的列 才会让

​ count(*) = count(列)。

count(伪列)：比如count(1)。

2：join两张表的关联字段最好都建立索引，而且最好字段类型是一样的。

3： where条件中尽量不要使用 1=1，not in语句（建议使用not exist）

4：不用mysql 内置的函数，因为内置函数不会建立查询缓存。

​ sql查询语句和查询结果都会 在第一次查询只会存储到mysql的查询缓存中，如果需要获取到查询缓存中的查询结果，结果的sql语句必须和第一次的查询sql语句一致。

5：合理利用慢查询日志，explain执行计划查询，show profile 查询sql执行时的资源使用情况。