Mysql进阶学习

最新推荐文章于 2024-05-20 00:23:38 发布

云一深

最新推荐文章于 2024-05-20 00:23:38 发布

阅读量279

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_52171069/article/details/130978398

版权

MySQL

体系：连接层、服务层、引擎层、存储层

存储引擎

InnoDB

InnoDB是一种兼顾高可靠和高性能的通用存储引擎；

特点：

DML操作遵循ACID模型，支持事务；行级锁，提高并发访问性能；支持外检foreign key约束，保证数据的完整性和正确性；

MyISAM

特点：

不支持事务，不支持外键；支持表锁，不支持行锁；访问速度快；

Memory

Memory引擎的表数据存储在内存中，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或者缓存使用；

特点：

内存存放；hash索引；

索引

索引是帮助MySQL高效获取数据的数据结构（有序），数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引语法

-- 创建索引[唯一索引|全文索引]
create [UNIQUE|FULLTEXT] index index_name on table_name (idnex_col_name,...)  -- 创建索引的过程就是创建B+树的过程
-- 查看索引
show index from table_name;
-- 删除索引
drop index index_name on table_name

-- SQL执行频率
show [session|global] status like 'Com_______(7个_)' --查看服务器状态信息，可以查看当前数据库insert、update、delete、select的访问频次

-- 慢查询日志
-- 记录所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志，MySQL的慢查询日志默认没有开启，需要在Mysql的配置文件（/etc/my.cnf）中配置：如下
-- 开启慢查询日志开关   slow_query_log = 1
-- 设置慢查询的时间为2秒，sql执行时间查过2秒，就会视为慢查询，记录慢查询日志 long_query_time= 2
show variables like 'slow_query_log'	--检测是否开启慢查询
-- 查看慢查询日志记录文件   /var/lib/mysql/localhost-show

-- sql性能分析 profile
-- 查看每一条sql的耗时情况
show profiles;
-- 查看指定query_id的Sql语句各个阶段的耗时情况(query_id 是show profile查出来的id)
show profile for query_id;
-- 查看指定query_id的sql语句cpu的使用情况
show prifile cup for query_id;

-- explain执行计划 
-- explain或者desc命令获取MySQL执行select语句的信息，包括select语句执行过程中如何连接和连接顺序
-- 直接在select语句之前加上关键词explain/desc
explain select 字段 from table_name where 条件
-- explain执行计划各字段含义
id:select查询序列号，表示查询中执行select字句或者操作操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
select_type:表示查询的类型，常见有SIMPLE（简单表，即不使用连表查询或者子查询）、PRIMARY（主查询，即外层查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（select/where之后包含了子查询）
type:连接类型，性能由好到差NULL、system、const、eq_ref、ref、range、index、all
possbile_key:显示可能用到的索引，一个或多个
key:实际用到的索引
key_len:实际用到的索引的字节数
rows:必须要执行查询的行数
filtered:查询返回的行数占总读取行数的百分比，filtered的值越大越好
extra:额外信息

索引的使用原则（索引失效）

1、最左前缀法则（针对于联合索引）

如果索引了多列（联合索引），就要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳过了某一列，索引将部分失效（后面的字段索引失效）。

2、范围查询

联合索引中，出现范围查询（<,>），范围查询右侧的索引失效。（优化，范围查询改为>=,<=）

3、索引列运算

不要在索引列上进行运算操作，索引将失效。

4、字符串不加单引号

字符创类型字段使用，不加引号，索引失效。

5、模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

6、or连接的条件

用or分隔开的条件，如果or前面的条件中列有索引，而后面的列没有索引，那么涉及的索引都不会被用到。

7、数据评估

如果MySQL评估使用索引比全表更慢，则不会使用索引。

8、SQL提示

SQL提示：在SQl语句中加入一些认为的提示来达到优化操作的目的。

-- user index 建议使用哪个索引
select * frmo  table_name user index(index_name) where 条件
-- ignore index 忽略某个索引
select * from table_name ignore index(index_name) where 条件
-- force index 强制使用某个索引
select * from table_name force index(index_name) where 条件

9、覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已全部能够找到），减少select *。

10、前缀索引

当字段类型为字符串时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀建立索引，提高索引效率。

create index index_name on table_name (column(n))
-- 前缀索引长度
可以根据索引的选择性来决定，而选择性是指不重复的额索引值（基数）和数据表的记录总数的比值，索引选择性越高，查询效率越高，唯一索引的选择性是1，这个是最好的索引选择性，性能也是最好的。

11、单列索引与联合索引

尽量使用联合索引。

索引设计原则

1、针对于大数据量，且查询比较频繁的表建立索引；

2、针对与常用作查询条件、排序、分组的操作的字段建立索引；

3、尽量选择区分度高的列作为索引，区分度越高，使用索引的效率越高。

4、如果使用字符串类型的字段，字段的长度越长，可以针对于字段的特点，建立前缀索引。

5、尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6、要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7、如果索引列不能存储null值，请在建表时使用not null约束他。当优化器知道每列是否包含null时，他可以更好的确定哪个索引最有效得用于查询。

SQL优化

insert优化

-- 批量插入  一次插入不要超过1000条
insert into table values(),()...
-- 使用手动事务提交
start transaction;
insert into ...
insert into ...
insert into ...
commit;
-- 主键顺序查询
-- 大批量数据插入：如果一次性需要插入大批量数据，使用insert语句插入性能比较低，此时可以使用MySQL数据库提供的load命令插入。
#客户端连接服务端时，加参数 --local-infile
mysql --local-infile -u root -p
#设置全局参数local_infile= 1
set glocal local_infile = 1
#执行load命令将准备好的数据库，加载到表结构中
load data local infile '/root/sql.log' into table 'table_name' fileds terminated by',' lines terminated by '\n'

主键优化

页分裂

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

如：主键为1234、6789，有两个页，第一页指针指向第二页，当要插入5的使用，会新建一个页，然后原来第一页的指针就会指向新的页，新一页再指向后面。

页合并

当删除一行数据，实际上记录并没有被物理删除，知识记录被标记为删除并且他的空间允许被其他记录声明使用。当页中删除的记录达到了MERGE_THRESHOLD（默认为页的50%），Innodb开始寻找最近的也看看是否可以将两个页合并以优化空间使用。MERGE_THRESHOLD:合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键设计原则

满足业务需求的情况下，尽量减低主键的长度。

插入数据时，尽量原则顺序插入，选择使用AUTO_INCERMENT自增主键。

尽量不要使用UUID做主键或者其他自然主键，如身份证号。

业务操作时，避免对主键的修改。

order by优化

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在缓冲区域sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序豆角FileSort排序。

Using index：通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高。

设计原则

根据排序字段建立合适的索引，多个字段排序时，也遵循最左前缀法则。

尽量使用覆盖索引。

多个字段排序，一个升序一个降序，此时注意联合索引在创建时的规则（ASC/DESC）。

如果不可避免的出现filesort，大数据量排序时，可以适当增加排序缓冲区大小sort_buffer_size(默认256k)。

group by优化

在分组操作时，可以通过索引提高效率。

分组操作时，索引的使用满足最左前缀法则。

limit 优化

通过覆盖索引加子查询形式进行优化。

count优化

count用法

count（主键）

InnoDB会遍历整张表，把每一行的id都取出来，返回服务层。服务层拿到主键后，直接按行进行累加。

count（字段）

没有not null约束：会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。

又not null约束：遍历整张表把每一行的字段读取出来，返回服务层，累加计数。

count（1）

遍历整张表，但不取数。服务层对于返回的每一行，放一个数字进去，直接按行进行累加。

count（**）

不会把全部字段取出来，而是做了专门的优化，不取值，服务层直接按行进行累加。

update优化

InnoDB的行锁是针对索引加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

锁

分类：

全局锁

对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

flush tables with read lock   -- 加全局锁
mysqldump -uroot -pwd > 路径	-- 数据库备份，在win命令行执行，非sql语句
unlock tables	-- 释放锁

存在问题：

1、如果在主库上备份，在备份期间都不能执行更新操作，业务基本上就是停摆。

2、如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟。

在InnoDB引擎中，可以在备份时加上参数–single-transaction参数来完成不加锁的一致性数据备份。

表级锁

每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。

分为三类：

1、表锁加锁：locl tables 表名。。。 read/write 释放锁：unlock tables

表共享锁（read lock）:不会阻塞其他客户端的读，但会阻塞写。

表读写锁（write lock）：即会阻塞其他客户端的读，又会阻塞其他客户端的写。

2、元数据锁（mate data lock ,MDL）

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

在MSYQL5.5中引入了MDL,当对一张表进行增删改查的时候，加MDL读锁；当对表结构进行更操作时，加MDL写锁。

select object type,object schema,object_name,lock type,lock duration from performance schema.metadata locks ;	-- 查看元数据锁

3、意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，引入意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

意向共享锁（IS ）：又语句select 。。lock in share mode 添加。与表锁共享锁（read）兼容，与表锁排他锁互斥。

意向排他锁（IX）：由ISinsert、update、delete、select。。。for update添加。与共享锁以及排他锁都互斥，意向锁之间不会互斥。

行级锁

每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。行锁是通过对索引的索引项加锁实现的，而不是对记录加的锁。

1、行锁（Record Lock）:锁定单个行记录的锁，防止其他事务对此进行update和delete，在rc、rr隔离级别下都支持。

共享锁（S）：允许一个事务去读一行，组织其他事务获得相同数据集的排他锁。

排他锁（X）：允许获取排他锁的事务更新数据，组织其他事务获得相同数据集的共享锁和排他锁。

在默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，INnoDB使用Next-key锁进行搜索和索引扫描，以防止幻读。

1、针对唯一索引进行检索时，对已存在的记录进行等值匹配，将会自动优化为行锁。

2、InnoDB的行锁是针对索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

2、间隙锁（Gap Lock）：锁住索引记录的间隙（不包含该记录），确保记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在rr隔离级别下都支持。

3、临建锁（Next-Key-Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在rr隔离级别下支持。

默认情况下InnoDB在REPEATABLE READ事务隔离级别中运行，InnoDB使用Next-key锁进行搜索和索引扫描，以防止幻读

1、索引上的等值查询（唯一索引)，给不存在的记录加锁时，优化为间隙锁。

2、索引上的等值查询（普通索引)，向右遍历时最后一个值不满足查询需求时，next-key-lock是退化为间隙锁。

3、索引的范围查询（唯一索引），会访问到不满足条件的第一个值为止。

注意：间隙锁唯一目的是防止其他事务插入间隙，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一个间隙上采用间隙锁。

日志

错误日志

错误日志是MySQL中最重要的日志之一，他记录了当MySQL启动和停止时，以及服务器运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志是默认开启的，默认存放目录/var/log/，默认的日志文件名为mysql.log

show variables like '%log_error%'

二进制日志

二进制日志（BINLOG）记录了所有的DDL（数据定义语言）语句和DML（数据操纵语言）语句，但不包括数据查询（Select、Show）语句。

作用：灾难的数据恢复；Mysql的主从复制。

show variables like %log_bin%

二进制日志查看

mysqlbinglog [参数选项] logfilename	
参数选项：
	-d:指定数据库名称，只列出指定的数据库相关操作
	-o:忽略掉日志中的钱n行命令
	-v:将行事件（数据变更）重构为SQL语句
	-W:将行事件（数据变更）重构为SQL语句，并输出注释信息

日志删除

查询日志

查询日志记录了客户端的所有操作语句，而二进制日志不包含查询数据的sql语句。默认情况先，查询日志是未开启的。如果需要开启查询日志。可以设置：

show variable like '%general%'
-- 修改mysql的配置文件/etc/mu.cnf文件，添加如下内容：
#该选择用来开启查询日志，0：关闭，1：开启
general_log = 1
#设置日志的文件名，如果没有指定，默认文件名为host_name.log
gengeral_log_file=mysql_query.log

慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并扫描记录数不小于min_examined_row_limit的所有的sql语句的日志，默认未开启。long_query_time默认为10秒，精度可以到微秒。

#慢插叙日志
show_query_log = 1
#执行时间参数
long_query_time = 2

主从复制

主从复制是指将主数据库的DDL 和DML操作通过二进制日志传到从库服务器中，然后在库上对这些日志重新执行，从而使得从库和主库的数据保持同步。

mysql支持一台主库同时向多台从库进行复制，从库同时也可以作为其它从服务器的主库，实现链状复制。

MySQL复制的优点包括以下三个方面：

1、主库出现问题，可以快速切换到从库提供服务。、

2、实现读写分离，降低主库的访问压力。

3、可以在库中执行备份，以免备份期间影响主库服务。

主从复制原理：

1、Master主库在事务提交时，会把数据变更记录在二进制日志文件binlog中。

2、从库读取主库的二进制文件binlog，写入到从库的中继日志relay log。

3、slave重做中继日志中的时间，将改变反映他它自己的数据。

主从数据库搭建

主库配置

1、修改配置文件 /etc/my.cnf

#mysql服务ID，保证整个集群环境中唯一，取值范围：1-2^32-1,moren wei 1
server-id =1
#是否只读1：只读，0：读写
read-only = 0
#忽略的数据，指不需要同步的数据库
#binlog-ignore-db = mysql
#指定同步的数据库
#binlog-do-db=db01

2、mysql服务重启

systemctl restart mysql

3、登录mysql，创建远程连接的账号，并授予主从复制权限

#创建itcast用户，并设置密码，该用户可在任意主机连接该mysql服务
create user 'itcast'@'%' identified with mysql_native_passwored by 'Root@123456';
#为'itcast'@'%'用户分配注主从复制权限
grant replication slave on  *.* to 'itcast'@'%'

4、通过指令，查看二进制日志坐标

show master status;
	file:从哪个日志文件开始推送日志
	position:从哪个位置开始推送日志
	binlog_ignore_db:指定不需要同步的数据库

从库配置

1、修改配置文件/etc/my.cnf

#mysql服务ID，保证整个集群环境中唯一
server_id = 2
#是否只读1
read-only = 1

2、重启mysql服务

systemctl restart mysql

3、登录mysql，设置主库配置

change replication source to source_host= 'xxx.xxx',source_user= 'xxxx',source_password = 'xxxx',source_log_file='xxxx' ,source_log_pos = xxx;

mysql8.0.23之前版本执行一下命令

change master to master_host = 'xxx',master_user= 'xxx',master-password='xxx',master_log_file = 'xxx',master_log_pos= 'xxx'

4、开启同步操作

start replica --8.0.222之后
start slave --8.0.22之前

分库分表

中心思想：将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提高数据库性能的目的。

拆分策略

垂直拆分
- 垂直分库：以表为依据，根据业务将不同的表拆分到不同库中
  - 每个库的表结构都不一样
  - 每个库的数据也不一样
  - 所有库的并集是全量数据
- 垂直分表：是以字段为依据，根据字段属性将不同字段拆分到不同表中。
  - 每个表的就结构都不一样
  - 每个表的数据也不一样，一般通过一列关联
  - 所有表的并集是全量数据
水平拆分
- 水平分库：以字段为依据，按照一定的策略，讲一个库的数据拆分到多个库中
  - 每个库的表结构一样
  - 每个库的数据都不一样
  - 所有库的并集是全量数据
- 水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。
  - 每个表的表结构都一样
  - 每个表的数据都不一样
  - 说有表的并集是全量数据