mysql 那些事

已于 2023-02-02 10:36:01 修改
阅读量141 收藏 1
点赞数
文章标签： mysql 数据库
于 2022-08-24 15:52:46 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_41395435/article/details/126504352
版权

文章目录

mysql体系结构：大致分为4层

1.连接层 : 客户端和链接通信服务，主要完成连接处理，授权认证等。

最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2.服务层：完成SQL接口，SQL分析和优化，部分内置函数执行等核心服务功能

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3.引擎层：负责数据存储和提取

存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通
信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库
中的索引是在存储引擎层实现的。

4.存储层：存储数据

数据存储层， 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询
日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。

在这里插入图片描述

各个存储引擎

1.InnoDB

  • 介绍：InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的MySQL 存储引擎，工作中常用的也是innoDB。
  • 特点：
    1.DML操作遵循ACID模型，支持事务；
    2.行级锁，提高并发访问性能；
    3.支持外键FOREIGN KEY约束，保证数据的完整性和正确性；
  • 逻辑存储结构：
    在这里插入图片描述
    1.表空间（Tablespace） : 存储一张表的数据，InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。
    2.段（Segment） : 存储各个区的数据，表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
    3.区（Extant） : 存储各个页的数据，区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。
    4.页（page） : 存储各行的数据，页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
    5.行 (row) : 存储具体的数据，InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，

2.MyISAM

  • 介绍：MyISAM是MySQL早期的默认存储引擎。
  • 特点：
    1.不支持事务，不支持外键;
    2.支持表锁，不支持行锁；
    3.访问速度快；

3.Memory

  • 介绍：Memory引擎的表数据时存储在内存中，作为缓存使用。
  • 特点：
    1.内存存放;
    2.hash索引（默认）；

在这里插入图片描述

SQl分类

1.DDL

Data Definition Language : 数据定义语言，用来定义数据库对象(数据库，表，字段)

2.DML

Data Manipulation Language : 数据操作语言，用来对数据库表中的数据进行增删改

3.DQL

Data Query Language : 数据查询语言，用来查询数据库中表的记录

4.DCL

Data Control Language : 数据控制语言，用来创建数据库用户、控制数据库的访问权限

数据类型

1.数值类型:

类型大小有符号范围无符号范围适合存储
TINYINT1byte(-128，127)(0，255)小整数值
SMALLINT2byte(-32768，32767)(0，65535)大整数值
MEDIUMINT3byte(-8388608，8388607)(0，16777215)大整数值
INT/INTEGER4byte(-2147483648，2147483647)(0，4294967295)大整数值
BIGINT8byte(-2^63， 2 ^63-1)(0，4294967295)超大整数值
FLOAT4byte(-3.402823466 E+38，3.402823466351 E+38)0 和 (1.175494351 E38，3.402823466 E+38)单精度浮点数值
DOUBLE8byte(-1.7976931348623157E+308，1.7976931348623157E+308)0 和(2.2250738585072014E-308，1.7976931348623157E+308)双精度浮值
DECIMAL依赖于M(精度)和D(标度)的值依赖于M(精度)和D(标度)的值精度准确的小数
  • int(1) 和 int(5) 的区别 :
    1.int(1) 和 int(5) 都是int类型，占用的空间都是一样的。
    2.区别在于显示上 :
    int(1) 如果插入 1，查询显示 1，因为指定了显示1个长度。
    int(5) 如果插入 1，查询显示 00001, 因为指定了显示5个长度，不够的用0来填充。

2.字符串类型

类型大小描述
CHAR0-255 bytes定长字符串(需要指定长度)
VARCHAR0-65535 bytes变长字符串(需要指定长度)
TINYBLOB0-255 bytes不超过255个字符的二进制数据
TINYTEXT0-255 bytes短文本字符串
BLOB0-65 535 bytes二进制形式的长文本数据
TEXT0-65 535 bytes长文本数据
MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT0-4 294 967 295 bytes极大文本数据

  • char :
    1.需要指定长度，长度是固定的。
    2.如 phone char(11), 那么这个字段无论存储多大内容，都会使用11个字符的空间。
    即使插入的内容是 ‘abcd’ 3 个字符，它依然会占用 11 个字节，其中包含了 7 个空字节。
    3.效率比varchar高。
    4.内存占用有可能比varchar多，除非业务能固定这个字段的存储长度。

  • varchar
    1.需要指定长度，但是长度不固定。
    2.例如 name varchar(128), 虽然指定了128个字符的长度，但实际存储内容是多大，这个字段就占用多大的字符空间。
    3.效率比char低。

  • vachar和char的选择 :
    1.如确定了字符长度，就用char，例如 : sex char(1), 不是男就是女。
    2.如不确定字符长度，就用varchar，例如 ：address varchar(128), 地址的长度是不确定的

索引

索引简介

  1. 啥是索引？指向数据所在的标记。
  2. 索引优势和劣势：
    在这里插入图片描述
  3. 索引是基于存储引擎实现的，不同引擎有不同的索引结构：
    在这里插入图片描述
  4. 不同引擎对索引的支持情况：
    在这里插入图片描述

B+树

  1. mysql InnoDB引擎使用B+树实现，我们主要研究B+树
    mysql对B+树进行了优化，使底层的叶子节点变成双向循环链表。
    提高区间访问的性能，利于排序。
    在这里插入图片描述
  2. 为什么InnoDB选择B+树？
    • 比二叉树层级更少，搜索效率高。
    • 在分支节点保存指针和索引键值，只起到索引数据的作用，可以保存更多的索引。
    • 具体数据都存储在叶子节点，并且形成双向循环链表，提高查询效率和排序效率。
    • 相对hash索引，b+树虽然搜索效率更低，但是B+树支持范围匹配和排序操作。

索引分类

  1. 索引主要分为以下几类：主键索引，唯一索引，常规索引，全文索引。
    在这里插入图片描述

  2. 主键索引选取规则：

    • 如果存在主键，主键索引就是聚集索引。
    • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
    • 没有主键，也没有唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

  3. 聚集索引和二级索引
    在这里插入图片描述

  4. 聚集索引和二级索引的机构

    • 聚集索引的叶子节点下挂的是这一行的数据。
    • 二级索引的叶子节点下挂的是该字段值对应的主键值(主键ID)。
      在这里插入图片描述

回表查询

  1. 先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取
    数据的方式，就称之为回表查询。例如下面这个查找。
    • 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
    • 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
    • 拿到这一行的数据，返回。
      在这里插入图片描述

树的高度计算可以存储的索引key

一个节点存储在一页中。
一页可以存储16KB，1kB=1024B(字节) 16KB = 161024 = 16384字节。
设可以存储的索引数量为n，设索引bigint类型-8字节。
根据B+树定义，指针总比索引多一个。所以索引个数为 n8, 指针个数为(n+1)6
又因指针大小为6字节，索引大小为8字节(bigint)。
所以一页中可以存储的索引数量为：(n * 8) + ((n+1) * 6) = 161024
算出n约 1170 个, 所以每个节点最多可以存储的索引个数为 1170 个，指针个数为 1171
(1170 * 8字节 + 1171 * 6字节 = 11386字节，约等于上面的 16*1024 = 16384字节)
总结：一个节点最多存储 1170 个索引，1171 个指针

假设一行数据大小为1K，一页中可以存储( 16/1) = 16 行这样的数据
而指针有1171个，

假设高度为2（只有第一个根节点，下面都是叶子节点）
可存储总行数 = 1171 * 16 = 18736 行。

假设高度为3 ：根节点有1171个子节点，每个子节点有1171个子节点
可存储总行数 = 1171 * 1171 * 16 = 2200万
在这里插入图片描述

所以得出公式，一个B+树能存储的数据总行数为N
N = [ 1171(树的高度 - 1)次方 * (16/每一行的大小) ]

SQL性能分析

总结：

  1. 先看慢日志和profile，看看到底是哪条语句在耗时，对其进行优化。
  2. 通过explain执行计划，看看select语句有没有走索引，走了什么类型的索引，连接类型是什么，然后对其进行优化。

查看SQL执行类型频率

show global status like ‘Com_____’
可以查出下面这样的:
在这里插入图片描述
Com_delete: 删除次数
Com_insert: 插入次数
Com_select: 查询次数
Com_update: 更新次数

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据
库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以
查询为主，那么就要考虑对数据库的索引进行优化了。

慢查询日志

  • 慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

  • MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。
    show variables like ‘slow_query_log’
    在这里插入图片描述

  • 如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

    # 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

  • 配置完之后，通过以下指令重启MySQL进行测试，查看慢日志文件中记录的信息.
    systemctl restart mysqld

  • 慢日志文件一般在：/var/lib/mysql/localhost-slow.log

  • 再次通过 show variables like ‘slow_query_log’ 查看慢日志是否开启, 下图就是开启了
    在这里插入图片描述

  • 执行一条查询比较慢的 sql
    select count(*) from tb_sku; – 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时
    13.35sec
    然后检查慢日志. tail -f localhost-show.log, 就能看到这条查询语句了
    在这里插入图片描述

profile详情

  • show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了.
  • 执行 SELECT @@have_profiling; 看下当前mysql是否支持profile操作。
    SELECT @@have_profiling;
    在这里插入图片描述
  • 可以看到，当前MySQL是支持 profile操作的，但是开关是关闭的。可以通过set语句在
    session/global级别开启profiling :
    SET profiling = 1;
  • 然后通过如下指令查看指令的执行耗时：
    -- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
  • 开关已经打开了，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。 我们直接执行如下的SQL语句：
    select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
select count(*) from tb_sku;
  • 查看每一条SQL的耗时情况:
    在这里插入图片描述
  • 查看指定SQL各个阶段的耗时情况 :
    在这里插入图片描述

explain 查询情况

  • EXPLAIN 命令可以获取mysql执行 select语句时候的详细信息。
  • 语法 :
    -- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
    在这里插入图片描述
  • explain 各个字段的含义：
    在这里插入图片描述
    在这里插入图片描述

索引使用规则

1. 最左前缀法则:

  • 如果某个表有联合索引，要遵循最左前缀法则，查询从索引的最左列开始，
    并且不跳过索引中的列。如果跳跃某一列，后面的字段索引失效。

  • 例如在 tb_user 表中，有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession，age，status。
    在这里插入图片描述
    所以查询时候，最左边的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。

    1.来看这条查询语句的执行计划

    explain select * from tb_user where profession = '软件工程' and age = 31 and status= '0';

    在这里插入图片描述
    可以看到
    type = ref，
    可能用到的索引 possible_keys = idx_usr…,
    实际用到的索引 key = idx_usr…
    所以当联合索引的3列都存在时候，索引生效。
    2. 再看这两条

    explain select * from tb_user where profession = '软件工程' and age = 31;

    在这里插入图片描述

    	explain select * from tb_user where profession = '软件工程';

    在这里插入图片描述
    我们发现只要联合索引最左边的字段 profession存在，索引就会生效，只不
    过索引的长度不同。 而且由以上三组测试，我们也可以推测出profession字段索引长度为47、age字段索引长度为2、status字段索引长度为5。

  • 接下来看看没有最左索引的情况：

    	explain select * from tb_user where age = 31 and status = '0';

    在这里插入图片描述
    我们也可以看到索引并未生效，原因是因为不满足最左前缀法则，联合索引
    最左边的列profession不存在。

2. 范围查询法则

  1. 联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。
  2. 出现范围查询使用 >= 或 <= 时, 索引有效，所以尽量使用 >= 和 <=。

3. 索引失效的情况

  1. 联合索引没有遵守最左前缀法则, 例如联合索引顺序为：profession，age，status, 下面语句where条件没有profession字段

    explain select * from tb_user where age = 31 and status = '0';

  2. 联合索引中，出现范围查询(>,<), 例如：

    explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';

  3. 在索引列上进行计算, 例如 phone字段的单列索引：

    explain select * from tb_user where substring(phone,10,2) = '15';

  4. 查询字符串时候，字符串字段不加 引号，例如 status是索引，而且是字符串类型:

    explain select * from tb_user where profession = '软件工程' and age = 31 and status = 0;

  5. 模糊查询会使索引失效，但是尾部模糊匹配除外，例如 profession 字段是索引:

    # 尾部模糊匹配，索引生效
explain select * from tb_user where profession like '软件%';
# 下面两种情况索引失效
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';

  6. or连接使索引失效, 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
    例如 id有索引，但是 age没有，那么下面语句 id 的索引也会失效，所以要给 age 字段也加上
    索引

    explain select * from tb_user where id = 10 or age = 23;

  7. mysql数据分布影响，自行判断是否走索引
    因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。

4.覆盖索引

啥是覆盖索引？：覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到，不需要回表查询，效率高。
例如：
1.select * from tb_user where id = 2;
  根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
在这里插入图片描述

2.selet id,name from tb_user where name = ‘Arm’;
  虽然是根据name字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在name的二级索引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高。
在这里插入图片描述

3.selet id,name,gender from tb_user where name = ‘Arm’;
  由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询 gender字段，性能相对较差一点。
在这里插入图片描述

5.前缀索引 ：把前缀当成一个单列索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让
索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法：

create index idx_xxxx on table_name(column(n)) ;

例如：

create index idx_email_5 on tb_user(email(5));

在这里插入图片描述

6.联合索引B+树结构

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
联合索引中包含 phone、name的信息，在叶子节点下挂的是对应的主键id
在这里插入图片描述

索引设计原则

  1. 针对于数据量较大，且查询比较频繁的表建立索引。
  2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

SQL提示

是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优
化操作的目的。

  1. use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。例如：
    explain select * from tb_user use index(idx_user_pro) where profession = 'CS';
  2. ignore index ： 忽略指定的索引。例：
    explain select * from tb_user ignore index(idx_user_pro) where profession = 'CS';
  3. force index ： 强制使用索引。例如：
    explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

数据的组织方式

InnoDB数据存储

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。
在这里插入图片描述
行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：
在这里插入图片描述
在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不下，将会存储到下一个页中，页与页之间会通过指针连接。

页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行
溢出)，根据主键排列。

主键顺序插入

从磁盘中申请空间，按顺序插入, 不会页分裂
在这里插入图片描述

主键乱序插入

可能会对已经存在的页进行分裂，会发生页分裂，耗费性能。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

页合并

在这里插入图片描述
在这里插入图片描述
合并页的阈值，可以自行在创建表活着创建索引时指定。

SQL优化

1. Insert 优化

当需要批量插入数据时，选择以下几种方式插入 :

  1. 一条insert语句批量插入数据：
    Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
  2. 手动控制事务，统一提交
    start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;
  3. 主键顺序插入，性能高于乱序插入 (原理看数据的组织方式)
    主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
  4. 当需要 insert 的数据过多时，使用 load 指令进行插入
    (1). 先把要插入的数据放到脚本中，例如: /root/load_user_100w_sort.sql 这个sql脚本，前提是数据的字段要和表的一致。
    (2). 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 : set global local_infile = 1;
    (3). 执行sql语句
    	load data local infile '/root/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;
    可以看到，100W条数据，插入的也不慢
    在这里插入图片描述

2. 主键优化

  1. 满足业务需求的情况下，尽量降低主键的长度。
  2. 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
  3. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
  4. 业务操作时，避免对主键的修改。

3. order by 优化

MySQL的排序，有两种方式(可在执行计划中explain查看)：

  • Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
  • Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
  • 对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

4. group by 优化

  1. 在分组操作时，可以通过索引来提高效率。
  2. 分组操作时，索引的使用也是满足最左前缀法则的。

5. limit 优化

场景：假设用户表 t_user 表有 10000000 条数据，我们现在需要查性别是男性的 1000000 - 1000010，这 10 条数据，
正常使用limit就是这样写（假设sex字段=1是男性, sex字段最好加上索引）：

select * from t_user where sex=1 limit 1000000,10;

这样mysql会扫描前1000010行，扔掉前面的1000000行，返回后面的10行，所以offset越大的时候，扫描的行就越多，效率也就越慢了。

优化方法：

1.ID主键有序自增, 直接判断 id >=
select * from t_user
where id >= 1000000 limit 10;
2.ID主键有序自增, 使用子查询

特点 :
1.id需要是主键，这样即使查询 limit 1000000 也不会太慢。
2.子查询里面只能查 id，加上其他字段就会拖慢子查询。
3.id主键必须是有序自增的，这样才能使 id >= 子结果 有效
4.子查询查到的是第100万条的id，然后我们的条件就是id >= 第100万条的id，向后查10条。
5.where后面的查询条件字段一定要有索引。
6.主查询语句where后面除了id >= , 不能有其他条件。
6.需要用子查询查到符合条件的起时id，不能直接 让 id >= 1000000。

select * from t_user
where id >= (select id from tb_test where sex = 1 limit 1000000, 1) 
limit 10;
2.ID主键无序, 使用子查询

特点 :
1.id需要是主键，这样即使查询 limit 1000000 也不会太慢。
2.子查询里面只能查 id，加上其他字段就会拖慢子查询。
3.子查询查到的是 100000 - 1000010 内的id，子查询结果作为一个临时表，使用 inner join 连接使两个表，用两个表的 id 作为条件匹配。
4.where后面的查询条件字段一定要有索引。

select t.* from t_user t
inner join
(select id from t_user where sex=1 limit 1000000,1000010) tmp
on t.id = tmp.id;

6. count 优化

InnoDB 执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出
来，然后累积计数。如果数据量很大，在执行count操作时，是非常耗时的。

优化方法：
1.可以先把总数查出来，放redis里面，但这样的话数据不是最新的。

count的用法：
在这里插入图片描述

多表对应关系

一对一：

例如：用户 与 用户详情的关系。
关系：一对一关系，当某张表字段过多，可以进行单表拆分，字段分开放在多个表中
操作： 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
但是推荐不要加外键，用一个字段标明这个数据属于哪个ID就行。<//font>

在这里插入图片描述

一对多 或 多对一：

例如：部门 与 员工的关系。
关系：一个部门对应多个员工，一个员工对应一个部门。
操作： 在多的一方建立外键，指向一的一方的主键
但是推荐不要加外键，用一个字段标明这个数据属于哪个ID就行。<//font>
在这里插入图片描述

多对多

例如：学生 与 课程的关系。
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择。
操作： 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。
但是推荐不要加外键，用一个字段标明这个数据属于哪个ID就行。<//font>
在这里插入图片描述

where和having的区别 TODO

主键自增ID用完了会怎么样？

  • 设置了自增主键ID，达到最大值之后再插入就报错主键冲突了。我们可以把自增ID设置为8个字节的bigint unsigned。
    用完了就分库分表，或者用雪花算法算出的ID作为主键(id不连续，插入数据时候性能有影响)。
  • 没有指定主键ID，mysql会默认设置一个长度为 6 个字节的 row_id, 达到上限之后，归0重新递增，后写的数据会覆盖之前id相同的数据。
    所以建表的时候一定要指定主键。

事务四大特性 简称ACID

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

事务并发问题

赃读：一个事务读到另外一个事务还没有提交的数据。

比如B读取到了A未提交的数据。
在这里插入图片描述

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

事务A两次读取同一条记录，但是读取到的数据却是不一样的。
在这里插入图片描述

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。

事务A插入数据时候，事务B已经提前插入了，导致出错
在这里插入图片描述

事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：
事务隔离级别越高，数据越安全，但是性能越低。

隔离级别脏读不可重复度幻读
Read uncommitted(读未提交)未解决未解决未解决
Read committed(不可重复度)解决未解决未解决
Repeatable Read(可重复的-默认)解决解决未解决
Serializable(串行化)解决解决解决

int(3) 和 int(6) 的区别

先来总结 : int(3) 和 int(6) 都可以显示数字，数字长度超过3和6的也可以展示。设置3和6的目的在于，数字长度没有3位或6位，会自动补0。
我们来创建个表：

CREATE TABLE `test1` (
`id`int(10) unsigned NOT NULL AUTO_INCREMENT,
`i1`int(3) unsigned zerofill DEFAULT NULL,
`i2`int(6) unsigned zerofill DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

接着插入一些数据，然后看结果，一图胜千言
在这里插入图片描述

char 和 varchar 的区别

  • 相同点：char和varchar都是用来存储字符串.
  • 不同点：
    1.char是固定长度，varchar是可变长度
    2.char适合存储长度不会变化的数据，例如性别。
    3.varchar适合存储长度经常变化的数据，例如地址。

注意：例如某个字段是 varchar(10) ，如果存储数据超过的定义的长度，会出现二进制被截断的错误，数据保存将失败。

MySQL预处理

  • 普通SQL语句执行过程：
    1.客户端对SQL语句进行占位符替换得到完整的SQL语句。
    2.客户端发送完整SQL语句到MySQL服务端
    3.MySQL服务端执行完整的SQL语句并将结果返回给客户端。

  • 预处理执行过程：
    1.把SQL语句分成两部分，命令部分与数据部分。
    2.先把命令部分发送给MySQL服务端，MySQL服务端进行SQL预处理。
    3.然后把数据部分发送给MySQL服务端，MySQL服务端对SQL语句进行占位符替换。
    4.MySQL服务端执行完整的SQL语句并将结果返回给客户端。

  • 为啥要预处理
    1.优化MySQL服务器重复执行SQL的方法，可以提升服务器性能，提前让服务器编译，一次编译多次执行，节省后续编译的成本。
    2.避免SQL注入问题。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值