数据库mysql从入门到放弃
Mysql基础介绍安装mysql-server[root@localhost mysql]# cat /etc/redhat-releaseCentOS Linux release 7.9.2009 (Core)#yum install mysql#yum install mysql-server#yum install mysql-devel#CentOS 7 版本将MySQL数据库软件从默认的
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
- 销毁分区
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执行时的资源使用情况。
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)