mysql-sql优化

一、SQL执行流程
MySQL是客户端-服务器的模式。

在执行过程中，主要有三类角色：客户端、服务器、存储引擎。

大致可以分为三层：

第一层：客户端连接到服务器，构造SQL并发送给服务器。

第二层：服务器收到SQL进行解析及优化，最终生成执行计划并执行

第三层：服务器调用存储引擎的API，进行数据的查询和存储。

二、执行计划分析
可以通过explain提前知道当前MySQL是如何处理SQL语句的。

在我们要执行的SQL前加上explain即可。

explain select * from user;

explain insert into user values(user_name, password, email) values(‘1’, ‘1’, ‘1’);

通过分析执行计划，我们可以得出以下几点：

  1. 1.表的读取顺序 -> id

  2. 2.表读取操作的类型 -> select_type

  3. 3.可以被使用的索引 -> possible_keys

  4. 4.实际使用的索引 -> key

  5. 5.表之间的引用 -> ref

  6. 6.每张表有多少行被优化器查询 -> rows

上述查询结果字段的含义：

1.id：表示查询中执行select子句或操作表的顺序，id越大，执行优先级越高，id相同，则从上至下

2.select_type：表示查询的类型，用来区分普通查询、联合查询、子查询等。一共有9种类型。

3.table: 输出行所引用的表名，如果使用了别名则显示别名

4.partitions：使用的哪个分区

5.type：查询使用了那种类型。描述的是当前如何去找数据的，如：all 表示扫描全表。

6.possible_keys：可能有助于查询的索引

7.key：实际使用的索引

8.key_len: 使用的索引的长度

9.ref：显示索引的哪一列被使用了

10.rows：请求数据大概返回的行数

11.filtered：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例

12.extra: 其他信息，出现Using filesort、Using temporary 意味着不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

其中比较重要的字段：

1.type：可以看出是如何查询数据的方式。一般需要达到 ref、eq_ref 级别，范围查找需要达到 range。

2.key：是否使用索引，如果为NULL表示没有使用索引，需要优化调整。

3.rows：表示返回的行数，可以直观观察到结果。

4.extra：有Using filesort、Using temporary 的一定需要优化。

三、表结构优化
数据库效率的影响主要是因为数据量太大，进行一次查找需要扫描很多数据(硬盘上的磁头需要越过很多数据来找到目标数据)，通过表结构优化的方式可以减轻当前访问的数据量。

3.1 数据类型优化
主旨就是能用小字段类型就不用大字段类型～

使用简单的数据类型。int 要比 varchar 类型在mysql处理简单
尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int
尽可能使用 not null 定义字段，因为 null 占用4字节空间。数字可以默认0，字符串默认“”
尽量少用 text 类型，非用不可时最好考虑分表
尽量使用 timestamp而非 datetime
单表不要有太多字段，建议在 20 以内
3.2 分库分表优化
当数据太多的时候，即使走索引啥的也不能解决效率问题，根本就在于要扫描的数据太多了，并且存储也是比较难的。

这时候就可以采用分表的方式，将一张表拆分成多张，然后通过编号等手段进行查询。

拆分大致也有两种方式：

垂直拆分：

    按照列的维度，将表中的列拆分开来，分别放在多张表中。例如：某些字段在一张表中可能更加平凡的查询，可以将这些字段放到一张表中，不常用的放在另一张表中。

    但需要注意的是，这种方式需要保证原子性！可以在进行插入的时候使用事务～

水平拆分：

    按照行的维度，将一张表的数据切分。如0-100的数据放在这张表中，101-200的数据放在另一张表中。

3.3 读写分离优化
由于一台数据库服务器的性能肯定是有瓶颈的，可以进行部署一个数据库集群。并采用主从的方式。设置一些主库，一些从库，主库用来负责写入数据，从库用来负责读取数据，当一个新的数据写入的时候，主库需要将数据同步到从库中，以保证数据的完整性。

四、查询语句优化
4.1避免使用 select *
sql在解析过程中，还需要把*依次转换为所有的列名，这个工作需要查询数据字典完成。额外开销！因此建议将需要的列写出来。

4.2多表联查时，小表在前，大表在后
from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表。

4.3调整where子句中的连接顺序
where子句是从左往右，自上而下的顺序执行的（Oracle相反），根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

4.4调整group by和order by子句中的顺序
group by和order by子句是从左往右的顺序执行的，根据这个原理，应将排序影响数据多的条件往前放，最快速度缩小结果集。

4.5用exists、not exists和in、not in相互替代
exists以外层表为驱动表，先被访问，适合于外表小而内表大的情况。

in则是先执行子查询，适合外表大而内表小的情况，一般情况是不推荐使用not in，因为效率非常低。

原则是哪个的子查询产生的结果集小，就选哪个

4.6用where子句替换having子句
where子句搜索条件在进行分组操作之前应用；而having子句条件在进行分组操作之后应用。

尽可能让where来缩小结果集！

4.7分段和分页查询
使用合理的分页方式，在数据表量级逐渐增加的时候，limit分页查询的效率会降低。

可以根据字段索引进行快速定位，直接找到偏移量。

huang2024-6-3-2024-6-9周报

本周学习大纲

1.MySQL学习

2.Java笔记整理

3.日周笔记

本周学习日历

日期 学习内容 其他
星期一 sql优化 \
星期二 html常用代码 \
星期三 html设计 \
星期四 c题目 \
星期五 \ 日周笔记
星期六 \ 离散期末复习
星期日 \ \

本周学习内容及收获

1.sql优化

https://snail0220.github.io/2024/06/09/mysql-sql%E4%BC%98%E5%8C%96/

2.html常用代码

https://snail0220.github.io/2024/06/04/HTML%E5%B8%B8%E7%94%A8%E4%BB%A3%E7%A0%81/

3.日周笔记

image-20240421122925872

4.C题目

image-20240609223511371

总结

临近期末，复习的任务加重，再加上这周是端午假期，学习时间大大减少，下周的学习应该也会重点回到期末复习上面。

下周学习

1.期末复习：高数、离散

2.java笔记整理

HTML常用代码

网页制作基础学习

一、介绍

1.HTML5的优势：
  1. 化繁为简
  2. 跨平台
  3. 跨浏览器
  4. 功能强大，新增特性
2.HTML基本结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<!DOCTYPE html>
<html lang="en">
	<head>
	<meat charset="UTF-8">
	<title>网页标题</title>
	</head>
	<body>
	我的第一个网页 
	</body>
</html>

<!DOCTYPE html>文档类型声明
<html lang="en">语言类型声明
<head><head>头部
<body><body>主体

二、标签

  1. 标题标签
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    标题标签，用于定义网页的标题
    <h1></h1>
    <h2></h2>
    <h3></h3>
    <h4></h4>
    <h5></h5>
    <h6></h6>
    h1最大，h6最小

    特点：
    1.会将文字自动加粗及放大
    2.独占一行，属于块级标签
  2. 段落标签
    1
    2
    3
    4
    <p></p>
    特点：
    1.对字段对照网页大小进行自适应分段
    2.属于块级标签，独占一行
  3. 换行标签
    1
    2
    <br/>
    用于手动将页面内容换行
  4. 水平线标签
    1
    2
    <hr/>
    在界面中添加水平线
  5. 字体样式标签
    1
    2
    3
    4
    <strong>加粗</strong>
    <b>加粗</b>
    <em>倾斜</em>
    <i>倾斜</i>
  6. 注释标签
    1
    <!--注释-->
  7. 字符实体
    1
    2
    3
    4
    5
    &nbsp; 空格
    > 大于>
    < 小于<
    " "引号
    &copy; 版权符号
  8. 图像标签
    1
    <img scr="路径(绝对路径/相对路径)">
  9. 超链接标签
    1
    2
    3
    4
    <a href="连接地址" taret="">  </a>
    taret属性常用值
    _self:在当前窗口打开目标页面
    _blank:在新窗口打开目标页面

java3

4.1 容器概述

案例分析

现在需要统计某公司员工的工资情况，例如计算平均工资、找到最高工资等。假设该公司有50名员工，用前面所学的知识，程序首先需要声明50个变量来分别记住每位员工的工资，然后在进行操作，这样做会显得很麻烦，而且错误率也会很高。因此我们可以使用容器进行操作。将所有的数据全部存储到一个容器中，统一操作。

容器概念

容器：是将多个数据存储到一起，每个数据称为该容器的元素。
生活中的容器：水杯，衣柜，教室

4.2 数组的概念

数组概念：

数组就是用于存储数据的长度固定的容器，保证多个数据的数据类型要一致。

百度百科中对数组的定义：

所谓数组(array)，就是相同数据类型的元素按一定顺序排列的集合，就是把有限个类型相同的变量用一个名字命名，以便统一管理他们，然后用编号区分他们，这个名字称为数组名，编号称为下标或索引(index)。组成数组的各个变量称为数组的元素(element)。数组中元素的个数称为数组的长度(length)。

数组的特点：

1、数组的长度一旦确定就不能修改

2、创建数组时会在内存中开辟一整块连续的空间。

3、存取元素的速度快，因为可以通过[下标]，直接定位到任意一个元素。

4.3 数组的定义

方式一：静态初始化

格式：

1
数据类型[] 数组名 = {元素1,元素2,元素3...};//必须在一个语句中完成，不能分开两个语句写

举例：
定义存储1，2，3，4，5整数的数组容器

1
2
3
4
int[] arr = {1,2,3,4,5};//正确

int[] arr;
arr = {1,2,3,4,5};//错误
方式二：静态初始化

格式：

1
2
3
4
数据类型[] 数组名 = new 数据类型[]{元素1,元素2,元素3...};
或
数据类型[] 数组名;
数组名 = new 数据类型[]{元素1,元素2,元素3...};

举例：
定义存储1，2，3，4，5整数的数组容器。

1
2
3
4
int[] arr = new int[]{1,2,3,4,5};//正确

int[] arr;
arr = new int[]{1,2,3,4,5};//正确
方式三：动态初始化

格式：

1
2
3
4
5
6
数组存储的元素的数据类型[] 数组名字 = new 数组存储的元素的数据类型[长度];

  或

 数组存储的数据类型[] 数组名字;
 数组名字 = new 数组存储的数据类型[长度];

数组定义格式详解：
数组存储的元素的数据类型： 创建的数组容器可以存储什么数据类型的数据。
元素的类型可以是任意的Java的数据类型。例如：int, String, Student等
[] : 表示数组。
数组名字：为定义的数组起个变量名，满足标识符规范，可以使用名字操作数组。
new：关键字，创建数组使用的关键字。因为数组本身是引用数据类型，所以要用new创建数组对象。
[长度]：数组的长度，表示数组容器中可以存储多少个元素。
注意：数组有定长特性，长度一旦指定，不可更改。
和水杯道理相同，买了一个2升的水杯，总容量就是2升，不能多也不能少。
举例：
定义可以存储5个整数的数组容器，代码如下：

1
2
3
4
int[] arr = new int[5];

int[] arr;
arr = new int[5];

4.4 数组元素的访问

索引： 每一个存储到数组的元素，都会自动的拥有一个编号，从0开始，这个自动编号称为数组索引(index)，可以通过数组的索引访问到数组中的元素。
索引范围：[0, 数组的长度-1]
格式：

1
数组名[索引]

索引访问数组中的元素：
数组名[索引]=数值，为数组中的元素赋值
变量=数组名[索引]，获取出数组中的元素

1
2
3
4
5
6
7
8
9
10
11
public static void main(String[] args) {
    //定义存储int类型数组，赋值元素1，2，3，4，5
    int[] arr = {1,2,3,4,5};
    //为0索引元素赋值为6
    arr[0] = 6;
    //获取数组0索引上的元素
    int i = arr[0];
    System.out.println(i);
    //直接输出数组0索引元素
    System.out.println(arr[0]);
}

huang2024-5-26-2024-6-2周报

本周学习大纲

1.mysql学习

2.Java笔记

3.日周笔记

本周学习日历

日期 内容 其他
星期一 mysql总结 \
星期二 Java笔记 \
星期三 C程序 \
星期四 Java笔记 \
星期五 \ 日周笔记
星期六 mysql查看思维导图 \
星期日 mysql整理 \

内容

1.mysql笔记

https://snail0220.github.io/2024/06/02/mysql%E6%80%BB%E7%BB%93/

2.Java笔记

https://snail0220.github.io/2024/06/02/java3/

3.日周笔记

img

总结

普普通通的一周，按照计划学习了该学习的内容，Java的笔记整理稳步进行。Mysql的学习也在渐进尾声。日周笔记的整理在慢慢进行。

下周学习大纲

1.mysql

2.Java笔记

3.日周笔记

huang2024-5-13-2024-5-26周报

本周学习大纲

1.mysql学习

2.Java笔记

3.日周笔记

本周学习日历

日期 内容 其他
星期一 mysql–null \
星期二 Java笔记 \
星期三 web网页美化 \
星期四 Java笔记 \
星期五 \ 日周笔记
星期六 mysql–null not误差理解 \
星期日 mysql–数据 \

内容

1.mysql笔记

mysql笔记（13） | Hexo (snail0220.github.io)

2.Java笔记

Hexo (snail0220.github.io)

3.日周笔记

image-20240526132501816

4.web设计

image-20240526132619790

总结

普普通通的一周，按照计划学习了该学习的内容，Java的笔记整理的还算OK。Mysql的学习也在稳步向下学习着。日周笔记的整理在慢慢进行。web网页的设计还是需要看着书上的方法去进行，还没能形成自己的思维。

下周学习大纲

1.mysql

2.Java笔记

3.日周笔记

mysql笔记（13）

创建表时应当设置not null，添加一个默认值0或’’去替代null。

sum(‘field’)的坑

若一列的所有值都是null，那么sum函数的结果不是0，而是null，所以可能会因为值的类型兼容问题，出现意料之外的情况。

null值会有NPE问题。

count(‘field’)的坑

有null的列，count不会+1。

where(‘field’)的坑

null是没有设置值的一种状态，并不是某个值，若用where field = null, where field = '', where field = false, where field = true，都无法匹配。

需要使用where field is null，where field <=> null来匹配。

group by(‘field’)的坑

null值会被自动归类为一类，某些场景下，可能不适用。

order by(‘field’)的坑

null值会被视为最小值，放在0的前方，某些场景下，可能不适用。

distinct(‘field’)的坑

有null的值，也会被去重，某些场景下，可能不适用。

null相关运算的坑

null值与任何数据进行四则运算或者大于小于等于不等于的运算，都返回null，某些场景下，可能不适用。

导入数据的坑

若两个MySQL环境或配置不一样，A设备导出的数据，B设备导入，可能会因为null值所在的字段缺少值从而报错。

外键的坑

在 MySQL 中，如果使用了外键约束，并且外键列允许 NULL 值，那么当引用的列中出现 NULL 值时，可能会导致一些意想不到的行为，如无法正确匹配行或者导致级联更新或删除的问题。

结果兼容性的坑

对于静态强类型语言，可能类型上会不兼容。

对于动态若类型的语言，null，0，false，‘’，都是false，可能会影响一些逻辑。

对唯一索引的影响

null值会导致数据有多个相同的null值，破坏了唯一性约束。

对索引的影响

有的说null值就不走索引，经过实测，就算全是null值，也显示的Using index。

mysql笔记（12）

查看字符集

字符集是自然语言字符集合及其在计算机中的编码和字符串排序规则。每个字符集至少对应一个排序规则。

常见字符集

计算机系统存在大量字符集，常见的有：ASCII字符集、GB2312字符集、BIG5字符集、GB18030字符集、Unicode字符集、UTF-8字符集等。其中：

Unicode支持现今世界各种不同语言的书面文本的交换、处理以及显示，以满足跨语言、跨平台进行文本交换、处理的请求。

UTF-8（8-bit Unicode Transformation Format）是Unicode的其中一个编码格式，是把Unicode字符集按某种格式存储，采用可变长度字节来储存Unicode字符。UTF-8是一种针对Unicode的可变长度字符编码，又称万国码。

MySQL支持多种字符集，默认使用是UTF-8，可以设置不同级别的字符集：服务器级、数据库级、数据表级、字段级。

查看当前MySQL系统支持的字符集：

show character set

查看当前服务器级字符集：

show variables [like 'character_set_server']

查看当前数据库级字符集

show variables like 'character_set_database'

查看字符集排序规则

show collation [like 'charset_name% ]

中括号【】包含内容是可选项。不写的时候是查看全部排序规则，写上的时候可以按指定的字符集名称匹配的方式查找字符集排序规则。‘%’是模式匹配符号，只要字符集是%前指定字符串开头的，都会显示比如:show collation like’utf8%’

启动服务

用命令行的方式启动 net start mysql57

打开windows+r，输入services.msc 找到MySQL的服务，右键启动。

1、创建数据库\na)语句创建数据库

语法：

create {database|schema} [if not exists] db_name

[default] character set [=] charset_name

| [default] collate [=] collation_name

语法说明：

MySQL系统会自动在安装时的数据库默认目录下生成一个与新建数据库名相同的空文件夹

数据库名不能使用MySQL关键字，也不能与其他数据库重名

名称可以有任意字母、阿拉伯数字、下划线“_”和“$”组成，可以使用上述任意字符开头，但不能使用单独的数字命名，否则会造成它与数字混淆

名称最长为64个字符，别名最长可达256个字符

建议数据库名、表名用小写，不同平台度大小写敏感度不同，Windows不敏感二Linux敏感，统一小写方便平台移植

character set用来设置数据库字符集，若不指定字符集，系统会使用my.ini指定的character-set-server参数设置的字符集

collate表示字符集排序规则

在MySQL中 create database和create schema是同义词，功能一致，这是与其他DBMS不同之处。\ncreate database db_test default char

DML语句

DML语句是数据操作语句，主要负责对数据的增删改操作，下面对这三种数据操作进行介绍

1 添加数据（插入数据）

语法：

插入单条指定字段数据：insert into 表名(name,id，字段1，字段2,......) values ('张三丰',6,.......);

插入单条所有字段数据：insert into 表名 values (所有字段数据);

插入多条数据：insert into 表名 values (字段数据1)，（字段数据2），（字段数据3）......;

需要插入多条数据的话直接在values关键字后用逗号继续添加括号即可。

代码：

1
2
3
insert into student (name,id) values ('张三丰',6);
insert into student values ('lisi',7,19,90,'男');
insert into student values ('lisi',7,19,90,'男'),('wangwu',8,20,91,'女');

2 删除数据

语法：delete from 表名 where 条件;

代码：

1
delete from student where id = 1;

3 修改数据

修改数据可也可以理解为更新某一条数据，常与where条件进行配合使用

语法：update 表名 set 字段1 = 数据1,字段2 = 数据2 where 条件;

代码：

1
update student\nset name = '灭绝师太',sex = '男'\nwhere id = 2;

huang2024-5-13-2024-5-19周报

本周学习大纲

1.mysql学习

2.Java笔记

3.日周笔记

本周学习日历

日期 内容 其他
星期一 mysql–查看字符集 \
星期二 Java笔记 \
星期三 web网页美化 \
星期四 Java笔记 \
星期五 \ 日周笔记
星期六 mysql–启动 \
星期日 mysql–DML \

内容

1.mysql笔记

https://snail0220.github.io/2024/05/19/mysql%E7%AC%94%E8%AE%B0%EF%BC%8812%EF%BC%89/

2.Java笔记

https://snail0220.github.io/2024/05/16/Java%E5%A4%9A%E7%BA%BF%E7%A8%8B%E5%9F%BA%E7%A1%80%20-%20%E5%89%AF%E6%9C%AC/

3.日周笔记

image-20240519211930735

4.web设计

img

总结

普普通通的一周，按照计划学习了该学习的内容，web网页的设计还是需要看着书上的方法去进行，还没能形成自己的思维。Java的笔记整理的还算OK。日周笔记的整理在慢慢进行。

下周学习大纲

1.mysql

2.Java笔记

3.日周笔记