MySQL 8.0 学习笔记（与SQLServer对比区别）

最新推荐文章于 2024-04-22 16:57:43 发布

FxBilly

最新推荐文章于 2024-04-22 16:57:43 发布

阅读量939

点赞数

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

本文链接：https://blog.csdn.net/guangmo0123/article/details/106225760

版权

mysql 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

数据库

5 篇文章 0 订阅

订阅专栏

1. 创建数据库

CREATE TABLE IF NOT EXISTS person(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '学号',
    NAME VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    age INT NOT NULL DEFAULT 0 COMMENT '年龄'
)


CREATE TABLE IF NOT EXISTS `student`(
    `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `password` VARCHAR(30) NOT NULL COMMENT '密码',
    `age` INT NOT NULL DEFAULT 0 COMMENT '年龄',
    `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `brithday` DATETIME NOT NULL DEFAULT '2020-01-01' COMMENT '生日',
    PRIMARY KEY (`id`)
)
ENGINE=INNODB 
DEFAULT CHARSET=utf8

【注】

其中的``为一种修饰字符，表示所包括的字符忽略其字符的含义（比如关键字等等），只是用其纯粹的字符串内容，忽略其中的各种关键字或表达式，类似于sqlserver中的[]中括号
COMMENT ‘xxx’，描述说明，用于对于该字段进行描述，可用于表、字段等对对象上
AUTO_INCREMENT，表示字段为自增属性
建议将主键放在最后单独写
ENGINE=INNODB ，指定表的引擎结构
DEFAULT CHARSET=utf8，指定表的默认字符集

2. 查看参数和对象结构

2.1查看参数

--查看全局参数
SHOW GLOBAL VARIABLES;
--查看指定参数名，模糊查询
SHOW GLOBAL VARIABLES LIKE '%char%';

--查看会话参数
SHOW SESSION VARIABLES;
--查看指定参数名，模糊查询
SHOW SESSION VARIABLES LIKE '%char%';

2.2查看对象结构

--查看数据库创建语句
show create database [数据库名]
--查看表的创建语句
show create table [表名]
--查看表结构
desc [表名]
--查看指定表的索引信息
show index from goods;

3. 数据库引擎

	MYISAM	INNODB（默认）
事务支持	不支持	支持
数据行锁定	不支持	支持
外间约束	不支持	支持
全文索引	支持	不支持（5.7版本之后也支持）
表占用空间大小	较小	较大（约为2倍）

3.1 MYISAM

节约控件，速度较快
数据文件组成
- *.sdi，表结构文件
- *.myd，表数据存放文件（data）
- *.myi，表索引文件（index）

3.2 INNODB

安全性较高，支持事务，支持多表多用户操作
数据文件组成（只有一个文件）
- *.ibd，表结构、数据、索引公用的一个文件

4. 数据库编码集

4.1 默认编码

mysql的默认编码为Latin1，不支持中文

4.2 修改编码

在my.ini文件中添加如下设置，需要在[mysqld]、[mysql]和[client]3个参数下都添加（如果标签不存在，则手动增加）

default-character-set=utf8

另外，在创建数据库或表时，显示指定默认字符集

DEFAULT CHARSET=utf8

4.3 标准建议

建议在my.ini文件中添加后，仍然在创建数据库和表时进行显示的指定字符集以及引擎，这样可以保证同样的代码拿到其它电脑上不会出现因为配置文件的不同而导致无法使用。

5. 增删改查

5.1 与sqlserver语法基本类似

表和字段都可以设置别名

与 sqlserver用法一致

5.2 mysql中，字符串拼接

在mysql中，字符串拼接不能使用加号"+"，需要使用CONCAT(‘aaa’, ‘bbb’,…)函数来实现

SELECT CONCAT('姓名:',a.name,'密码:',a.password) `姓名+密码` FROM student a

6. join语法

与sqlserver用法基本一致

7. 分页 `LIMIT` 用法

7.1 用法种类

LIMIT 可以有2中用法，一种为一个参数，一种为2个参数。

注：参数都必须为整数。

7.2 语法

7.2.1 一个参数用法

...... LIMIT X 表示返回最大 X 条记录，若结果集没有那么多记录，则有多少条返回条记录

SELECT * FROM student LIMIT 3

7.2.2 两个参数用法

...... LIMIT X, Y 第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0（而不是 1）。为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

--上面的返回前3条记录，也可以这样写
SELECT * FROM student LIMIT 0,3

SELECT * FROM student LIMIT 5,10; --返回 6-15 行的记录

--为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
SELECT * FROM student LIMIT 95,-1; -- 返回 96-后面所有 行的记录

--如果只给定一个参数，它表示返回最大的记录行数目
SELECT * FROM student LIMIT 5; --返回前 5 条记录

--换句话说，LIMIT n 等价于 LIMIT 0,n

8. 变量

8.1 用户变量

8.1.1 方式一：会话变量

无需声明，直接使用并赋值，必须要以@开头，此种方式的变量为会话变量，在当前的整个会话（当前的连接）中都有效

需要使用set或select来直接定义变量并赋值

使用set时，有2中方式：set @a=1，或者，set @a:=1

使用select时，必须使用：select @a:=1

--无需提前声明，直接使用
set @a=1;
set @a=@a+1;
select @a;
--也可以使用select，使用select时，赋值符号必须为 【:=】，select也可以为一个查询语句
select @b:=1;
select @b;

8.2.1 方式二：过程变量（局部变量）

需要提前声明（可以在声明的同时进行初始化），此种方式的变量只在当前的过程或局部（begin-end）有效，超出了范围则无法使用。如：函数和存储过程。

declare c int default 0;
set c=c+1;
select c;

8.2 系统变量

变量详细说明见CSDN

9. 事务

9.1 ACID 特性

**原子性(Atomicity)：**是指一个事务要么全部执行，要么不执行，也就是说一个事务不可能只执行了一半就停止了。比如你从取款机取钱，这个事务可以分成两个步骤：1划卡，2出钱。不可能划了卡，而钱却没出来。这两步必须同时完成，要么就不完成。
**一致性(Consistency)：**是指事务的运行并不改变数据库中数据的一致性。例如，完整性约束了a+b=10，一个事务改变了a，那么b也应该随之改变。
**独立性(Isolation）：**事务的独立性也称作隔离性，是指两个以上的事务不会出现交错执行的状态。因为这样可能会导致数据不一致，更加具体的来讲，就是事务之间的操作是独立的。
**持久性(Durability）：**事务的持久性是指事务执行成功以后，该事务对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚。

9.2 由于ACID特性导致的问题

9.2.1 脏读

指的是一个实物读取了另外一个事务未提交的数据。

9.2.2 不可重复读

一个事务内读取某个数据，多次读取时结果不同。比如：开启实物时，数据为A，在事务内，A被其它事务修改为B，所以再次读取时，数据结果不同了。（这个不一定是错误，只是某些场合不对）

9.2.3 虚读（环读）

指的是一个事务内读取了别的事务插入的新数据，导致前后读取记录数不一致。比如：开启事务时，读取到3条记录，之后其它事务又插入一条新纪录，导致再读取时，数据变为4条记录。

9.3 事务用法

--开启事务前，先关闭mysql的事务自动提交
SET autocommit=1
--开启事务
START TRANSACTION

--事务内的sql代码块
--......

--若执行成功，则提交事务
COMMIT

--若执行失败，则回滚事务
ROLLBACK

10. 索引

10.1 索引类型

10.1.1 主键索引（PRIMARY KEY）

唯一的标识，主键不可重复，一个表只能有一个主键

10.1.2 唯一索引（UNIQUE KEY）

设定某一列不能存放重复数据，一个表可以有一个或多个唯一索引，也可以没有

10.1.3 常规索引（KEY / INDEX）

常规普通的索引，使用KEY或INDEX关键字来声明

10.1.4 全文索引（FULLTEXT）

快速定位数据中的指定字符。新版的INNODB（至少5.7版本之后都支持）已经支持全文索引。

10.2 查看表索引

SELECT INDEX FROM student

10.3 创建索引

CREATE TABLE IF NOT EXISTS color(
    colorid VARCHAR(30) NOT NULL,
    `c_no` VARCHAR(30) NOT NULL,
    `en_no` VARCHAR(30) NOT NULL,
    `color` VARCHAR(50) NOT NULL,
    memo VARCHAR(100) NOT NULL DEFAULT '',
    quantity INT NOT NULL DEFAULT 0,
    PRIMARY KEY (colorid),            --主键索引
    UNIQUE INDEX UI_c_no(c_no),       --唯一索引
    INDEX I_en_no(c_no),              --常规索引
    FULLTEXT INDEX FI_memo(memo)      --全文索引
) 
ENGINE=INNODB 
DEFAULT CHARSET=utf8

11. MySQL数据导入和导出

11.1 使用SQLYog等可视化工具实现

11.1.1 导出

右键库或表->备份/导出->***转存到SQL

11.1.2 导入

右键库或表->导入，选择sql文件即可。或者直接执行指定的sql文件。

11.2 使用MySQL命令行实现

首先打开cmd命令行

11.2.1 导出

# 语法（无需进入mysql命令行模式，直接在cmd模式下执行）
mysqldump -h{地址} -u{用户} -p{密码} {数据库} [表1] [表2] [表3] [...] > {导出的sql文件保存位置}

# 导出school数据库的所有数据，数据保存到d:/1.sql中
mysqldump -hlocalhost -uroot -pjgl school > d:/1.sql

# 导出school数据库的student表的所有数据，数据保存到d:/2.sql中
mysqldump -hlocalhost -uroot -pjgl school student > d:/2.sql

# 导出school数据库的student表和teacher表的所有数据，数据保存到d:/3.sql中
mysqldump -hlocalhost -uroot -pjgl school student teacher > d:/3.sql

11.2.2 导入

# 语法（需要先进入mysql命令行模式）
source {需要导入的sql文件位置}

# 先需要进入mysql命令行模式
mysql -uroot -pjgl

# 进入mysql命令行模式后，先切换数据库
use school

# 导入指定的sql文件，此处导入：d:/1.sql
source d:/1.sql

导入数据建议使用上面那种方法，如果指定的数据库不存在时，再使用下面这种方法

# 另一种写法（无需进入mysql命令行模式，直接在cmd模式下执行）
# 其中[数据库]是可选的，如果sql需要在指定的数据库中执行，则需要，否则不需要（如在sql中创建该数据库）
mysql -u{用户} -p{密码} [数据库] < {需要导入的sql文件位置}

# 将d:/school.sql文件导入mysql数据库中。
# 由于该sql中会创建一个school数据，并自动会切换到该数据库，所以此处无需指定数据库
mysql -u root -pjgl < d:/school.sql

# 将d:/student.sql其中的数据导入到school数据库中
mysql -u root -pjgl school < d:/student.sql

12. Java连接MySQL

12.1 使用 `Statement` 执行SQL

存在SQL注入漏洞

package com.com.billy;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author billy
 * @date 2020-05-17
 */
public class MySQLTest1 {
    //mysql 8.0版本以前的用法
    //private static final String SQL_DRIVE = "com.mysql.cj.jdbc.Driver";
    //private static final String SQL_URL = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
    /** mysql 驱动 8.0以后的写法 */
    private static final String SQL_DRIVE = "com.mysql.cj.jdbc.Driver";
    private static final String SQL_URL = "jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC";
    private static final String SQL_ROOT = "root";
    private static final String SQL_PASSWORD = "jgl";

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            //加载驱动
            Class.forName(SQL_DRIVE);
            connection = DriverManager.getConnection(SQL_URL, SQL_ROOT, SQL_PASSWORD);
            statement = connection.createStatement();
            String sql = "SELECT * FROM student";
            rs = statement.executeQuery(sql);
            while (rs.next()) {
                System.out.print("id=" + rs.getInt("id") + ", ");
                System.out.print("name=" + rs.getString("name") + ", ");
                System.out.print("age=" + rs.getInt("age") + ", ");
                System.out.print("sex=" + rs.getString("sex") + ", ");
                System.out.print("brithday=" + rs.getString("brithday") + "\n");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            CloseUtils.close(rs);
            CloseUtils.close(statement);
            CloseUtils.close(connection);
        }

    }
}

package com.com.billy;

/**
 * @author billy
 * @date 2020-05-17
 */
public class CloseUtils {
    private CloseUtils() {
    }

    /**
     * 关闭释放资源
     *
     * @param closeable
     */
    public static void close(AutoCloseable closeable) {
        if (closeable == null) {
            return;
        }
        try {
            closeable.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

12.2 使用 `PreparedStatement` 执行SQL

可以避免SQL注入的漏洞

package com.com.billy;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author billy
 * @date 2020-05-17
 */
public class MySQLTest2 {
    //mysql 8.0版本以前的用法
    //private static final String SQL_DRIVE = "com.mysql.cj.jdbc.Driver";
    //private static final String SQL_URL = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
    /** mysql 驱动 8.0以后的写法 */
    private static final String SQL_DRIVE = "com.mysql.cj.jdbc.Driver";
    private static final String SQL_URL = "jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC";
    private static final String SQL_ROOT = "root";
    private static final String SQL_PASSWORD = "jgl";

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //加载驱动
            Class.forName(SQL_DRIVE);
            connection = DriverManager.getConnection(SQL_URL, SQL_ROOT, SQL_PASSWORD);
            String sql = "UPDATE student SET age=age+1 WHERE name=?";
            ps = connection.prepareStatement(sql);
            ps.setString(1, "aaa");
            int rows = ps.executeUpdate();
            if (rows > 0) {
                System.out.println("更新成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            CloseUtils.close(rs);
            CloseUtils.close(ps);
            CloseUtils.close(connection);
        }
    }
}

13. 索引

13.1 创建索引

13.1.1 方式一

--单列索引（单列时为单列索引）或组合索引（多列时则为组合索引）
create index [索引名] on [表名] ([列名]...)
--唯一索引（单列或多列，多列时）
create unique index [索引名] on [表名] ([列名]...)

13.1.2 方式二

--单列索引（单列时为单列索引）或组合索引（多列时则为组合索引）
alter table [表名] add index [索引名] ([列名]...)
--唯一索引（单列或多列，多列时）
alter table [表名] add unique index [索引名] ([列名]...)

13.2 删除索引

drop index [索引名] on [表名]

13.3 查看索引

--如果使用控制台来执行sql语句，可以在语句结尾加上"\G"，这样查询的结果将以竖向的方式来显示
show index from [表名]

14. `explain`信息解析

14.1 id

id相同时（如多表关联），则按顺序从上往下执行，其实优先执行rows行数较少的表的查询语句；

id不同时（如子查询），则id越大越先执行，其次若存在id相同的，则按id相同的规则继续执行

14.2 select_type

14.2.1 SIMPLE

简单查询，不包含子查询或union查询

14.2.2 PRIMARY

最外层的主查询sql，该sql的where中包含子查询语句

14.2.3 SUBQUERY

子查询的sql语句

14.2.4 DERIVED

衍生查询，查询时用到了临时表，一下2种都会触发衍生查询。

1.在from子查询中只有一张表（新版貌似变为了SIMPLE），如：

EXPLAIN SELECT t.code FROM (
    SELECT * FROM goods WHERE CODE IN ('aaa')
) t

2.在from子查询中，如果table1 union table2，那么table1为衍生表，如：

EXPLAIN SELECT t.code FROM (
    SELECT * FROM goods WHERE CODE IN ('aaa') 
    UNION 
    SELECT * FROM goods WHERE CODE IN ('bbb')
) t

14.2.5 UNION

联合查询，多个表之间用union联合起来查询。如：

在from子查询中，如果table1 union table2，那么table2为union查询

EXPLAIN SELECT t.code FROM (
    SELECT * FROM goods WHERE CODE IN ('aaa') 
    UNION 
    SELECT * FROM goods WHERE CODE IN ('bbb')
) t

14.2.6 UNION RESULT

一般用于显示哪几个表之间存在union查询。

14.3 table

用于显示当前查询的是哪个表，可以是：实体表、衍生表（临时表）、union表等等。

14.4 type

表示的索引类型，在日常中能见到的一般有：

system>const>eq_ref>ref>range>index>all

从左到右性能越来越低，system性能最好，all性能最差。

其中system和const在实际开发中一般情况很难达到，所以在实际优化中，主要还是将目标优化到ref和range这2个级别上。

14.4.1 system

这种类型一般很难达到，如：只有一条数据的系统表；或者衍生表只有一条数据的主查询。如：

EXPLAIN SELECT * FROM (SELECT * FROM goods LIMIT 1) t

14.4.2 const

仅仅能查到一条数据，且必须是where使用了主键索引（primary key）或唯一索引（unique index），实际开发中也很难达到。

EXPLAIN SELECT * FROM goods WHERE id=1

14.4.3 eq_ref

唯一性索引，对于每个索引键的查询，返回匹配唯一行数据（查到的数据每个都是唯一的，不能出现重复），常见于主键索引（primary key）或唯一索引（unique index），实际开发中也很难达到。

在进行多表关联时，要求多表之间必须使用主键或唯一键来关联（假如关联列都为id），且多表之间关联的id数据记录数（行数）需要一样，不能某个id在a表中存在，但在b表中不存在。

14.4.4 ref

非唯一性索引。就是where后面使用索引列查询，此种索引级别比较常用，需要尽量达到。如：

--name列存在普通索引
EXPLAIN SELECT * FROM goods WHERE NAME='货品1';

14.4.5 range

查询指定范围内的数据，一般是在where后面使用范围查询时会使用的索引类型，如：>、<、>=、<=、between和in。

其中in有些特殊，在某些情况下不会使用range索引。

EXPLAIN SELECT * FROM goods WHERE id>1;

14.4.6 index

查询全部索引数据，即查询的数据只要索引列的全部数据。

EXPLAIN SELECT id FROM goods

14.4.7 all

全表查询，没有使用任何索引。

14.5 possible_keys

可能用到的索引，只是一种预测，不一定准确。

14.6 key

实际用到的索引。

14.7 key_len

索引的长度。一般用于判断符合索引是否被完全使用。当有使用组合索引时，若使用了多个列，则该值会是多个列索引的合计长度。在不损失精确性的情况下，长度越短越好。

索引的长度计算：索引列的字符长度*编码长度+额外长度。

如：

a char(4) not null，字符编码为utf8，则索引长度为：4*3=12；

a char(4) not null，字符编码为utf8mb4，则索引长度为：4*4=16；

a varchar(4) not null，字符编码为utf8mb4，则索引长度为：4*4+2=18；

在mysql中utf8每个字符占3个字节，utf8mb4每个字符占4个字节；

varchar类型会多2个字节用于存储字符长度；

若允许为null，则还会多占1个字节；

14.8 ref

指明当前表所引用的表的字段。如：

--则a表的ref会显示为b.id，表示引用了b表的id，即显示为dbname.b.id
EXPLAIN SELECT a.name FROM goods a , student b WHERE a.id=b.id

--ref引用也可以是常量，即显示为const
EXPLAIN SELECT a.name FROM goods a WHERE a.id=1

14.9 rows

被索引优化查询的数据行数。

14.10 Extra

用于显示执行计划的额外信息。如：排序、索引覆盖等等。

该列包含MySQL解决查询的详细信息，有如下信息：

14.10.1 Distinct

一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

14.10.2 Not exists

MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

14.10.3 Range checked for each

Record（index map:#）
没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

14.10.4 Using filesort

看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。经常出现在order by语句中。

--id列为主键，age列有索引，因为查的数据为id，但又按age排序，因此需要额外排序，所以会出现Using filesort
EXPLAIN SELECT id FROM goods  WHERE NAME='' ORDER BY age

14.10.5 Using index

列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。相当于只需要从索引中查询数据，不需要查询原表。也称为：索引覆盖。

--age列有索引，而查询的数据也只有age，所以直接查询索引即可
EXPLAIN SELECT age FROM goods  WHERE age=''

14.10.6 Using temporary

看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。经常出现在group by语句中。

--name和age列都有索引，此时where用name，排序用age，则会出现临时表，因此会出现Using temporary
EXPLAIN SELECT age FROM goods  WHERE NAME='' GROUP BY age

14.10.7 Using where

使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题

15. select语句执行顺序

15.1 select 语句写法顺序

select [distinct] [field] from [table] 
join [table] on ?=? 
where [field]=? 
group by [field] 
having [field] 
order by ? 
limit ?

15.2 select 语句执行顺序

from [table] 
join [table] on ?=? 
where [field]=? 
group by [field] 
having [field] 
select [distinct] [field] 
order by ? 
limit ?

16. 优化要点

16.1 如果建立组合索引，优先使用左匹配原则

--给book表建立组合索引
alter table book add index ix_aid_tid_name(a,b,c)

那么where 必须存在a，才能使用索引，如果存在a和b，那么就会使用a和b列索引，以此类推。如果where中没有a列，则不管b和c有没有，该索引都不会使用。

在where中，a、b和c写的顺序可以乱，因为数据库引擎会进行优化，但建议还是尽量按索引列顺序来写。

--会使用到a、b、c3列索引
select * from book where a=? and b=? and c=?
--会使用到a、b2列索引
select * from book where a=? and b=?
--会使用到a列索引，不会使用c列索引
select * from book where a=? and c=?
--以下写法都不会使用该组合索引，索引失效
select * from book where b=??
select * from book where c=?
select * from book where b=? and c=?

16.2 索引列顺序应遵循select执行顺序

在建立组合索引时，索引列的顺序应遵循select执行顺序，那个模块列先被执行，则对应模块的列在组合索引中越靠前。

--如对如下语句建立组合索引
select name from book where authorid=? and typeid=? order by typeid

--由于where后面先执行，所以应先处理where后面的列，其次是order by，最后才是select中的列，因此：
alter table book add index ix_aid_tid_name(authorid,typeid,name)

16.3 在组合索引中谨慎排序使用`in`的列

在组合索引中如果有使用in的列，如果在满足左匹配原则的情况下，应尽量将where中使用in的列放在组合索引列的后面。因为in属于范围查询，可能会导致索引失效，如果该列处于组合索引的第一列，那么就会导致整个索引都会失效。

--如下语句建立组合索引
select name from book where typeid in (?,?) and authorid=? order by typeid
--分析由于typeid使用的是in范围查询，可能会无法使用该列索引，所以因将其放在组合索引列的后面，如：
alter table book add index ix_aid_tid_name(authorid,typeid,name)
--并尽量将select语句中的where修改为如下，将authorid写在typeid前面
select name from book where authorid=? and typeid in (?,?) order by typeid

16.4 `in`与`exists`

16.4.1 in

原则：如果主查询数据集更大，则使用in

原理：将子查询中的结果放到主查询中进行校验，校验成功的则保留下来。

16.4.2 exists

原则：如果子查询数据集更大，则使用exists

原理：将主查询中的结果放到子查询中进行校验，校验成功的则保留下来。

16.5 `order by`优化

可以选择单路排序或双路排序，并且可以调整排序buffer的大小；

如果排序数据量较大，可以考虑调大排序buffer的大小。

--设置排序buffer的大小，单位：byte字节
set max_length_for_sort_data=1024

注：如果排序buffer设置的太小，则mysql会自动从单路切换为双路。

在mysql4.1之前，默认使用双路排序，此时需要进行2次IO（先对排序字段进行单独执行一次IO操作，在对其它字段进行一次单独的IO操作）；

在mysql4.1之后，默认使用单路排序，对字段只进行一次读取，且是在buffer中进行排序。

当数据量较大时，buffer一次排序无法全部完成，则单路排序可能会有一定的隐患，不一定只进行一次IO，也有可能进行多次IO；

select避免使用*

--select字段尽量不要使用*，应使用具体字段来代替
select * from book where id=33
--修改为
select a,b,c from book where id=33

避免索引失效，导致using filesort
尽量使排序字段排序一致性（如：都是升序或降序）

16.6 慢查询日志

16.6.1 慢查询日志配置

慢查询日志是mysql提供的一种文件日志记录，用于记录mysql查询响应时间超过指定阀值的sql语句。

该功能参数默认是关闭的，一般建议是在开发调优阶段打开，最终部署时关闭。

--查看慢日志查询开启状态
show variables like '%slow_query%';
--查看慢日志查询的阀值，默认10秒
show variables like '%long_query_time%';

--临时开启慢查询日志，重启mysql后无效
set global slow_query_log=1;
--临时修改慢查询日志的阀值，默认10秒，修改后重新登录mysql生效，但重启mysql后无效
set global long_query_time=10;
--未使用索引的查询是否也被记录到慢查询日志中，无论查询是否超时long_query_time。默认是关闭的
set global log_queries_not_using_indexes=1;
--将慢查询日志纪录保存的位置：文件(FILE)或表(TABLE)，
--保存到文件：日志文件的存放路径由[slow_query_log_file]参数决定
--保存到表：慢查询日志村存储在[mysql.slow_log]表中
set global log_output='FILE';
--慢查询日志的日志文件路径，以下为默认的路径，默认文件名=[计算机名]-slow.log
set global slow_query_log_file='/var/lib/mysql/xxxxx-slow.log';

若需要永久开启慢查询日志，则需要修改mysql配置文件。

window系统下，配置文件在mysql目录下，一般为my.ini文件
liunx系统下，配置文件在/etc/my.cnf

# 在[mysqld]标签下添加如下属性，并设置日志文件名称和保存位置
[mysqld]
......
slow_query_log=1
# linux路径已/开头，如保存包用户目录则为：/usr/xxx/xxx.log
slow_query_log_file=D:\slow-query.log
long_query_time=5
......

16.6.3 慢查询日志分析

慢查询日志的内容说明以及分析方法：

使用mysql自带的分析工具——mysqldumpslow
https://www.jb51.net/article/75454.htm

使用第三方分析工具——mysqlsla
https://www.jb51.net/article/75064.htm

# 例如：查询日志中select语句总耗时最长且去重后的100条记录，并将分析结果保存指定的文件中
mysqlsla -lt slow -sf "+select" -sort t_sum -top 100 20211215-versino.log >analysis_result.log;

mysqlsla-2.03下载地址（官网已经不提供下载了）：
链接: https://pan.baidu.com/s/14J9ybvgWW1FoSIdv-unERQ
提取码: uqyk

16.7 海量数据分析——`profiles`

16.7.1 `profiles`概要

profiles一般用于记录mysql执行的每一条语句，然后可以在根据执行语句的记录id（Query_Id）来分析指定语句的性能指标等数据。

一般情况下，该功能是关闭的，即：mysql默认不记录执行的sql语句。

-- 查看profiles功能是否开启，其中的profiling即为该参数
show variables like '%profiling%'

--开启profiles功能
set global profiling=1

16.7.2 `profiles`使用

-- 查询profiles中所记录的执行语句
show profiles

执行后就可以看到每条sql语句执行所需要花费的时间，其中包括（Query_ID）。

如果想知道其中某一条sql语句花费时间的具体情况（如cpu耗时、io耗时和内存耗时等等）时，则可以使用profiles功能。

-- 查看指定查询语句耗时的具体组成信息
show profile all for query [Query_ID]

-- 上面查询出的结果列太多，可能不要好看，下面则只显示cpu和io相关的耗时信息
show profile cpu, block io for query [Query_ID]

17. 索引失效

17.1 `索引列运算`导致索引失效

索引列在过滤时，如果在索引列进行任何的计算、函数、类型转换（含隐式转换）等等操作，则该列索引失效**

--假设a列有索引，那么一下所有查询都会导致a列索引失效
select * from table where a+1=7
select * from table where IFNULL(a,0)=7
select * from table where CAST(a AS VARCHAR)='7'
--...

17.2 `运算符`导致索引失效

对于组合索引使用!=、<>、>、>=、<、<=、IN、NOT NULL操作符进行过滤时，在遵循左匹配原则的情况下，一般而言，使用该操作符的列索引有效，而该列后续的索引列将会失效。

--假设a、b、c3列有组合索引（a,b,c）
--a列索引有效，b、c列索引失效
select * from table where a<>1 and b=1 and c=2
--a列索引有效，会导致b列索引失效
select * from table where a<1 and b=2
--a、b列索引有效，c列索引失效
select * from table where a=1 and b>2 and c=3
--a列索引有效，b、c列索引失效
select * from table where a IS NOT NULL and b=2 and c=3
--...

17.3 `like`导致索引失效

对于LIKE模糊查询，尽量要以"常量"开头，不要以"%"开头，否则索引失效。

--此时a列索引有效
select * from table where a like 'aaa%'

--此时a列索引失效
select * from table where a like '%aaa'

如果必须要进行a列字符串全位置查找，此时只能使用like '%aaa%'，那么此时最好在查询的字段中只包括索引列，此时仍然会使用，而且走的是还是索引覆盖，即：所需要的值在索引中已经存在，无需进行回表查询。

--此时a列索引有效，但查询结果只需要a列，那么此时仍然会使用索引，且是索引覆盖
select a from table where a like '%aaa%'

17.4 `or`导致索引失效

对于组合索引，如果使用or连接符对组合索引的多列进行连接，那么会导致组合索引失效。

--组合索引（a,b），此时由于使用了or，那么就会导致组合索引(a,b)失效
select * from table where a=1 or b=2

18. 函数和过程

18.1 函数

-- 如果一个函数存在，则删除它
DROP FUNCTION if EXISTS fun_getRandomStr ;

-- 创建一个自定义函数
-- 其中 DEFINER=`root`@`localhost` 定义该函数的访问权限，如果定位`localhost`，则只允许本地访问。
-- 如果定义为`%`，则本地无法访问，但远程可以访问
CREATE DEFINER=`root`@`localhost` FUNCTION fun_getRandomStr(strLength INT) RETURNS VARCHAR(30) 
  -- 定义函数的对数据的产生的影响类型，DETERMINISTIC:不确定性,NO SQL:没有SQl语句,READS SQL DATA:只是读取数据
  DETERMINISTIC
BEGIN
  DECLARE result VARCHAR(30) DEFAULT '';
  DECLARE constChars VARCHAR(20) DEFAULT 'ABCDEF0123456789';
  DECLARE i INT DEFAULT 1; 
  DECLARE s VARCHAR(1);
  -- 得到 constChars 字符串的长度
  DECLARE constCharsLen INT DEFAULT LENGTH(constChars);

  WHILE i<=strLength DO
    -- 从 constChars 中随意获取一个字符，RAND()返回一个0（含）-1（不含）之间的小数，FLOOR向下取整
    SET s= SUBSTRING(constChars,1 + FLOOR(RAND()*constCharsLen),1);
    SET result=CONCAT(result,s);
		
    SET i=i+1;
  END WHILE;
	
  RETURN result;

END;

FxBilly

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL 8.0 学习笔记（与SQLServer对比区别）

1. 创建数据库CREATE TABLE IF NOT EXISTS person( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '学号', NAME VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', age INT NOT NULL DEFAULT 0 COMMENT '年龄')CREATE TABLE IF NOT EXISTS `student`( `id`
复制链接

扫一扫