1.5.1.1.1. mysql远程连接

  • 设置防火墙禁用

systemctl staus firewalld

systemctl stop firewalld

systemctl disable firewalld

  • 设置远程用户密码权限

    登录后、use mysql

    update user set host='%' where user = 'root';

    Flush privileges;

  • 如果提示加密登录错误2058时

    ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password by '密码' ;

1.5.1.1.2. 修改默认字符集

show variables like '%character%' ;//查看字符集

image-20221030142328657

1.5.1.1.2.1. mysql5.7需要修改默认字符集

(默认是latin1)

# 编辑my.cnf文件
vim /etc/my.cnf
# 增加character_set_server=utf8
character_set_server=utf8
# 在重启mysql服务
systemctl restart mysqld
# 命令行修改数据库字符集
alter database dbtest1 character set 'utf-8';
# 命令修改表字符集
alter table emp1 convert to character set 'utf-8';
# 如果使用emoji表情需要配置utf8mb4
show charset ;查看字符集

1.5.1.1.2.2. 字符集比较规则

image-20221030144304130

image-20221030150532473

1.5.1.1.2.3. sql大小写敏感

window 默认大小写不敏感,linux系统大小写敏感

show VARIABLES LIKE '%lower_case_table_names%' ; # 0大小写敏感 1大小写不敏感

1.5.1.1.3. 用户账号和权限

1.5.1.1.3.1. 修改账号密码

# 修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'new_password';
# SET 修改当前用户密码(不建议使用)
SET PASSWORD='new_password'
# 修改其他用户密码 ALTER USER user [IDENTIFIED by '新密码']
ALTER USER 'zhangsan'@'%'IDENTIFIED by '新密码';
# SET 修改其他用户密码
SET PASSWORD for 'zhangsan'@'%'='new_password';

1.5.1.1.3.2. 密码过期

# 手动设置账号过期
ALTER USER user PASSWORD EXPIRE;
# 将用户密码设置过期
ALTER USER 'zhangsan'@'%' PASSWORD EXPIRE;
  • 密码重用策略

    Password_history:规定密码重用的数量,最近多少个密码不能重复

    password_reuse_interval: 规定密码重用的周期,多少天内需要重置密码

1.5.1.1.4. 权限管理

show privileges ;//查看权限内容
Privilege Context Comment
Alter Tables To alter the table
Alter routine Functions,Procedures To alter or drop stored functions/procedures
Create Databases,Tables,Indexes To create new databases and tables
Create routine Databases To use CREATE FUNCTION/PROCEDURE
Create role Server Admin To create new roles
Create temporary tables Databases To use CREATE TEMPORARY TABLE
Create view Tables To create new views
Create user Server Admin To create new users
Delete Tables To delete existing rows
Drop Databases,Tables To drop databases, tables, and views
Drop role Server Admin To drop roles
Event Server Admin To create, alter, drop and execute events
Execute Functions,Procedures To execute stored routines
File File access on server To read and write files on the server
Grant option Databases,Tables,Functions,Procedures To give to other users those privileges you possess
Index Tables To create or drop indexes
Insert Tables To insert data into tables
Lock tables Databases To use LOCK TABLES (together with SELECT privilege)
Process Server Admin To view the plain text of currently executing queries
Proxy Server Admin To make proxy user possible
References Databases,Tables To have references on tables
Reload Server Admin To reload or refresh tables, logs and privileges
Replication client Server Admin To ask where the slave or master servers are
Replication slave Server Admin To read binary log events from the master
Select Tables To retrieve rows from table
Show databases Server Admin To see all databases with SHOW DATABASES
Show view Tables To see views with SHOW CREATE VIEW
Shutdown Server Admin To shut down the server
Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
Trigger Tables To use triggers
Create tablespace Server Admin To create/alter/drop tablespaces
Update Tables To update existing rows
Usage Server Admin No privileges - allow connect only
XA_RECOVER_ADMIN Server Admin
SHOW_ROUTINE Server Admin
TABLE_ENCRYPTION_ADMIN Server Admin
SERVICE_CONNECTION_ADMIN Server Admin
CONNECTION_ADMIN Server Admin
CLONE_ADMIN Server Admin
REPLICATION_SLAVE_ADMIN Server Admin
APPLICATION_PASSWORD_ADMIN Server Admin
REPLICATION_APPLIER Server Admin
ROLE_ADMIN Server Admin
RESOURCE_GROUP_ADMIN Server Admin
ENCRYPTION_KEY_ADMIN Server Admin
BINLOG_ENCRYPTION_ADMIN Server Admin
PERSIST_RO_VARIABLES_ADMIN Server Admin
BINLOG_ADMIN Server Admin
INNODB_REDO_LOG_ENABLE Server Admin
AUDIT_ADMIN Server Admin
RESOURCE_GROUP_USER Server Admin
SET_USER_ID Server Admin
SYSTEM_VARIABLES_ADMIN Server Admin
SYSTEM_USER Server Admin
SESSION_VARIABLES_ADMIN Server Admin
INNODB_REDO_LOG_ARCHIVE Server Admin
GROUP_REPLICATION_ADMIN Server Admin
BACKUP_ADMIN Server Admin

1.5.1.1.4.1. 授予权限

GRANT 权限1,权限2 on 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY '密码口令'];

  • 授予角色权限
  • 授予用户权限

多次赋予权限不覆盖,是追加权限。赋所有权限时,如没有WITH GRANT OPTION时是不能创建用户和授权其他用户

GRANT 权限1,权限2 on 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY '密码口令'];

# 赋予所有权限
grant all privileges on *.* to 'zhangsan'@'%' ; //张三是没有给其他人赋予权限的能力,加上WITH GRANT OPTION 则可以

# 查看权限
    SHOW GRANTS ; 
    或
    SHOW GRANTS for CURRENT_USER;
    或
    SHOW GRANTS FOR CURRENT_USER();
# 查看某个用户权限
SHOW GRANTS FOR 'user'@'主机地址' ;
# 收回权限
REVOKE 权限1... on 数据库名称.表名称  FROM 用户名@用户地址;
# 收回全库全表权限
REVOKE ALL PRIVILEGED ON *.* FROM 'zhangsan'@'%';
# 收回mysql库下所有表的插删改权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM 'joe'@'localhost' ;

权限表

desc user;
desc db;
desc tables_priv ;
desc columns_priv ;
desc procs_priv;

1.5.1.1.4.2. 角色管理

#创建角色
CREATE ROLE 'role_name'[@'host_name'][,'ROLE_NAME'[@'host_name']] ...
#授权角色名称
GRANT select,update on 数据库.表名  to '角色名称'
#查看角色权限
show grants for '角色名称'@'%';
# 回收权限
REVOKE privileges on 数据库.表名 from '角色名称';
# 删除角色
drop role '角色名称';
# 角色赋予用户,授予后需要用户激活权限才会生效
grant 'manager'@'%' to 'wang5'@'%' ;
## 激活权限
SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';
##激活权限2 设置系统参数active_all_roles_on_login 为ON 
show variables like 'activate_all_roles_on_login';
set GLOBAL activate_all_roles_on_login=ON ;
# 收回角色
revoke 'manager'@'%' from 'wang5'@'%' ;

##设置强制角色,无法被删除或回收
方式1:服务启动时设置
mysqld配置
mandatory_roles='role1,role2@localhost'
方式2:运行时设置
SET PERSIST mandatory_roles='role1,role2@localhost'  //系统重启后仍然失效
SET GLOBAL mandatory_roles = 'role1,role2@lcoalhost' //系统重启后失效

1.5.1.1.5. 逻辑架构

image-20221030180935648

image-20221030181007600

image-20221030181956314

  • 连接层
    • tcp连接池
    • 线程池
  • 服务层
    • 解析成语法树
    • 查询优化器(选取-投影-连接),优化完生成执行计划
    • 查询缓存,不同客户端之间共享。mysql8.0中已删除
  • 引擎层(show engines;)
    • 负责mysql数据的存储和提取,对物理服务器维护底层数据执行操作

1.5.1.1.6. Sql执行流程

image-20221030183110500

大多数情况查询缓存命中率太低,只有相同的查询操作才会命中查询缓存。

  • 只有完全相同的查询语句才会缓存
  • now()函数结果每次都不一样也不会缓存
  • 缓存失效

1.5.1.1.6.1. 解析器

语法树

image-20221030211718956

image-20221030211833066

1.5.1.1.6.2. 优化器

找到最好的执行计划

image-20221030212310159

1.5.1.1.7. sql执行原理

# 查看是否开启计划
select @@profiling ;
或
show variables like 'profiling';
# 设置计划打开或者关闭
set profiling=1;
# 查看执行计划列表
show profiles;
# 查看具体某一次执行计划
show profile for query 7 ;


type:{
    ALL -- 显示所有参数的开销信息
    BLOCK IO -- 显示IO相关开销
    CONTEXT SWITCHES --上下文切换相关开销
    CPU -- 显示cpu开销信息
    IPC -- 显示发送和接收相关开销
    MEMORY -- 显示内存相关开销信息
    PAGE FAULTS -- 显示页面错误相关开下信息
    SOURCE -- 显示和source_funciton,source_file,source_line开销
    SWAPS -- 显示交换次数开销信息
}

image-20221030213039886

https://www.bilibili.com/video/BV1iq4y1u7vj?p=111&spm_id_from=pageDriver&vd_source=4943434253a78df6682ef2dc83cfb977

1.5.1.1.8. 缓冲池读取数据流程

image-20221103161243916

缓冲池更新缓存逻辑:先更新缓存在进行一定频率同步到磁盘,保证数据一致性。

1.5.1.1.8.1. 设置缓冲池大小

# 查看缓冲池大小
show variables like 'innodb_buffer_pool_size';
# 查看引擎
show variables like '%storage_engine%'
# 设置默认引擎
SET DEFAULT_STORAGE_ENGINE=innodb;

1.5.1.1.9. 索引数据结构

减少磁盘的i/o次数。索引是帮助mysql高效获取数据的数据结构。索引本质是数据结构。存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少256子节。

1.5.1.1.9.1. 优点

  • 提高数据检索的效率,降低数据库io成本
  • 创建唯一索引保证数据库每一行数据的唯一性
  • 在实现数据参考性方法,加速表与表之间的连接。
  • 使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序时间,降低cpu的消耗

1.5.1.1.9.2. 缺点

  • 创建索引和维护需要消耗时间,随着数据量增加消耗时间也会增加
  • 索引需要占磁盘空间,每个索引还占一定物理空间,存储在磁盘上如果有大量索引,索引文件就可能比数据文件更快达到最大文件尺寸
  • 索引提供查询速度,但也会降低更新表的速度.数据进行增加删除和修改时也要动态维护.降低数据维护速度。

1.5.1.1.9.3. 索引内容

record_type : 记录头信息的属性。0标识普通记录2表示最小记录3表示最大记录,

next_record: 下一条地址相对于本条记录的地址偏移量

image-20221103205004525

页分裂:下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值.基于规则记录移动的操作来保证这个状态一直成立。

给所有页建立目录项

image-20221103210847870

image-20221103211057313

目录项构成的页:目录页,单向链表逻辑链接

record_type: 0普通用户记录1目录项记录 2 最小记录 3 最大记录

image-20221104111720554

b+tree

数的层次越低io越少,b+树一般情况不会超过4层(查找3个目录页和一个用户记录页)

image-20221104110729179

image-20221104111226705

聚蔟索引

聚蔟索引不是一个单独索引类型,而是一种数据方式(所有用户记录都存储在叶子节点),索引即数据,数据即索引。

特点

1、使用记录主键值的大小进行记录和页的排序

  • 页内的记录是按照主键的大小顺序排成一个单向链表
  • 各个存放用户记录页也是根据页中用户记录的主键大小顺序排成一个双向链表
  • 存放目录项记录的页分为不同层次,在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表

2、B+树的叶子节点存储的完整的用户记录(存储了所有列的值(包含隐藏列))

回表

根据c2列的值查询一条完整的用户记录需要使用2棵B+数的过程叫回表

联合索引

b+树按照c2和c3列大小进行排序:

  • 先把各个记录和页按照c2列进行排序

  • 在记录c2列相同的情况下采用c3列进行排序

b+树索引的注意事项

1、根页面位置万年不动

image-20221104125550535

2、内节点中的目录项记录唯一性

非主键索引目录项记录由c2列 + 主键 + 页号 的值构成

3、一个页面最少存储2条记录

MyISAM索引

Myisam 索引文件仅仅保存数据记录的地址

image-20221104133803321

image-20221104133938911

索引的代价

image-20221104134501396

数据结构选择的合理性

hash结构效率高,为什么不选择

1、hash索引仅能满足(= <> in) 查询。如果进行范围查询,时间复杂度会退化为o(n);而树形'有序'特性,依然能保持o(log2N)的高效率

2、hash索引还有一个缺陷,数据存储是没有顺序,在order by 情况下,使用hash索引还需要对数据进行重新排序

3、对于联合索引情况,hash值将联合索引健合并到一起计算,无法对单独一个健或者几个索引健进行查询

4、对于等值查询,通常hash索引的效率更高。不过也存在一种情况,索引列的重复值如果很多,效率就会降低.

二叉树

性能退化成链表时查找数据时间复杂度变成o(n).为了提高查询效率减少磁盘io数,需要尽量降低树的高度,树的每层分叉越多越好。

AVL树

为了解决二叉查找树退化为链表问题,在二叉树基础上增加了约束,具有一次性质:

  • 它是一颗空树或它的左右两个子树的高度差绝对值不超过1,并且左右两个子树都是平衡二叉树o(log2n)

image-20221104142226964

image-20221104142311978

b-Tree

Balance tree 多路平衡查找树.它的高度远小于平衡二叉树的高度

image-20221104142527606

1、b数在插入和删除节点的时候如果导致树不平衡就通过自动调整节点的位置来保持树的自平衡

2、关键字集合分布在整棵树中,即叶子节点和非叶子节点都存放数据.搜索有可能在非叶子节点结束

3、 其搜索性能等价于在关键字全集内做一次二分查找

b+tree

b+树也是一种多路搜索树,基于b树做出了改进,主流DBMS都支持b+树的索引方式, b+tree 适合文件索引系统

b+ 树和b树的差异

1、有k个孩子的节点就有k个关键字。也就是孩子数量=关键字树,而b树中,孩子数量=关键字数+1

2、非叶子节点的关键字也会同时存在子节点中,并且在子节点中所有关键字最大(或最小)

3、非叶子节点仅用于索引,不保存数据记录,跟记录有关的信息都放在叶子节点中,而b树中,非叶子节点即保存索引,也保存数据记录

4、所有关键字都在叶子节点出现,叶子节点构成有序链表,而且叶子节点本身按照关键字的大小从小到大顺序链接

b+树中间节点不直接存储数据。b+树查询效率更稳定。在查询范围上,b+树效率也比b树高。

思考问题
  • 为了减少io,索引树会一次性加载吗?

1、数据库索引是存储在磁盘上,如果数据量很大,必然导致索引的大小也会很大,超过几个G

2、当我们利用索引查询时候,是不可能将全部几个G的索引都加载进内存,我们能做的是一一加载每个磁盘页,因为磁盘页对应着索引树的节点

  • b+数的存储能力如何?为何说一般查找行记录,最多只需1~3次磁盘IO

InnoDB存储引擎中页的大小为16kb,一般表的主键类型INT(占用4子节)或BIGINT(占用8子节)指针类型也一般为4或8个子节,也就是说一个页(b+tree中的一个节点)中大概存储16kb/(8b+8b)= 1k个键值( 因为是估值,为方便计算,这里k取值为10^3.也就是说一个深度为3的b+tree索引可以维护10^3 10^3 10^3) = 10亿条记录。

实际情况中每个节点可能不能填充满,因此在数据库中,b+tree的高度一般都在2~4层。mysql中的innodb存储引擎在设计时是将根节点常驻内存,也就是说查找某一键值的行记录最多只需要1~3次磁盘i/o操作。

1.5.1.1.10. Innodb 数据存储结构

数据划分若干个页,innodb中页的大小默认为16kb

show variables like '%innodb_page_size%' ;

页的上层机构,还存在区(extent)、段(segment)和表空间(tablespace)的概念

image-20221104191057576

区(Extend)会分配64个连续的页,因innodb中页大小默认16kb,所以一个区大小是64*16kb=1MB

段(segment)由一个或多个区组成,区在文件系统是一个连续分配的空间(在innodb中连续64个页)

表空间(tableSpace)是一个逻辑容器,表空间存储对象是段,在一个表空间可以有一个或多个段,但是这个段只能属于一个表空间。

系统表空间: 默认情况下会在数据目录下创建一个名为ibdata1、大小为12m的文件。

1.5.1.1.10.1. 页的内部结构

image-20221104194506204

image-20221104194556542

  • File header 38子节

image-20221105091045463

  • File Trailer (文件尾部) 8子节

  • Free Space(空闲空间)

  • User RECORDS(用户记录)

    • compact 行格式

      • 变长字段长度列表

        记录存储长度真实长度

      • null值列表

      • 记录真实数据

        image-20221105105331728

      • 记录头信息(5子节)

        • delete_mask

          删除记录的标识,默认0 ,删除1

        • min_rec_mask

          b+数每层非叶子节点最小记录添加该标记,默认是1

        • record_type

          0 标识普通记录 1 标识b+树非叶子节点记录 2表示最小记录 3 表示最大记录

        • Heap_no

          当前记录在本页的位置。也称为伪记录或者虚记录,0是最小记录 1是最大记录

        • n_owned

          页目录中每个组最后一条记录头信息会存储该组一共多少条记录

        • Next_record

image-20221105093255249

image-20221105094403167

  • Infimum Supremem(最小最大记录 13子节
  • Page Directory(页目录)

  • Page Header(页面头部)

image-20221105102856240

image-20221105103345056

1.5.1.1.10.2. 行格式

select @@innodb_default_row_format; // 查看默认行格式 7或8默认都是 dynamic

show table status like '表名';

//指定表行格式

create table emp3(id int,name varchar) row_format=compact ;

alter table emp3 row_format = compact ;

compact 行格式

image-20221105104207930

image-20221105110417493

dynamic和compressed 行格式

行溢出:

image-20221105111955648

Redundant

image-20221105112357169

1.5.1.1.10.3. 区、段、碎片区

区:一个区物理位置连续64个页.innodb中页默认是16kb,一个区大小是64*16kb=1MB,数据量大的时候,为某个索引分配空间不在按页为单位分配,而是按区为单位分配,一次分配多个连续区,从性能角度上消除了很多随机i/o,提升性能

分类4种:

1、空闲区(free) :直属表空间

2、有剩余空间的碎片区(free_frag):直属表空间

3、没有剩余空间的碎片区(full_frag) :直属表空间

4、附属某一个段的区(FSEG)

段:存放叶子节点区的集合就算是一个段,一个索引会生成2个段,一个叶子节点段,一个非叶子节点段。

除了索引的叶子节点段和非叶子节点段外,还有一些回滚段、数据段、索引段。对段的管理是引擎自身完成。

碎片区:直属于表空间,并不属于任何一个段。

比喻: 表空间比作集团军,段相当于师,区相当于团

1.5.1.1.10.4. 表空间

表空间是一个逻辑容器:表空间存储是段,在一个表空间中可以有一个或多个段。

表空间划分为:系统表空间(system tablespace、独立表空间(file-per-table tablespace)、撤销表空间(undo tablespace)和临时表空间(temporary tablespace)

查看是否独立表空间的sql: show variables like 'innodb_file_per_table' ;

一个表空间默认6个页大小;

系统表空间:

mysql进程只有一个系统表空间,系统表空间会记录一些有关整个系统信息的页面。

当插入一条数据时:先校验插入语句表存在不存在,插入列和表中列是否符合,如果没有语法问题外,还需要知道该表的聚蔟索引和所有二级索引对应根页面是哪个表空间的哪个页面,然后把记录插入对应索引b+树中,还有很多额外信息,这些数据被称为元数据,内部有一个 内部系统表(internal system table)来记录这些元数据(数据字典)。

image-20221105121933541

系统表查看在数据库information_schema中提供了一些innodb_sys开头的表:

show tables like 'innodb_sys%' ; // 这些表跟sys开头并不完全一致,只是参考而已

1.5.1.1.11. 索引创建和设计原则

1.5.1.1.11.1. 索引分类

普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等

  • 从功能逻辑上说:索引主要有4种,分别是普通索引、唯一索引、主键索引、全文索引
  • 按照物理实现方式:索引可以分为2种:聚蔟索引和非聚簇索引
  • 按照作用字段个数划分:单列索引和多列索引

普通索引

不附加任何限制条件,只是用于提高查询效率,可以创建在任何数据类型,其值是否唯一和非空要由字段本身完整性和约束性条件决定。

唯一性索引

使用UNIQUE参数设置索引为唯一性索引,限制改索引值必须唯一,允许有空值.一张表可以有多个唯一索引

主键索引

特殊的唯一索引,在唯一索引基础上增加了不为空的约束,也就是NOT NULL+UNIQUE 一张表只能有一个主键索引

单列索引

单个字段上创建索引,一个表有多个单列索引

多列索引

在表的多个字段组合上创建一个索引。只有查询条件使用这些字段第一个字段时才会被使用。使用组合索引遵循最左前缀集合。

全文索引

它利用分词技术等多种算法智能分析出文本文字中关键词频率和重要性然后按照一定算法规则智能筛选出我们想要的搜索结果。全文索引非常适合大型数据集,对于小的数据集,它的作用非常小。使用参数FULL TEXT可以设置索引为全文索引。

全文检索类型:自然语言全文索引和布尔全文索引

官方不支持中文分词,需要引入第三方分词插件。在5.7.6版本mysql内置了ngram全文解析器,用来支持亚洲语种的分词。随着大数据时代全文检索逐渐被solr \es 搜索引擎所替代

空间索引

使用参数SPATIAL 可以设置为空间索引,只有myisam索引擎支持空间索引,并且索引值不能为空值。

image-20221105124118513

1.5.1.1.11.2. 创建索引

分类:创建表时指定索引(create table ,创建表后增加索引(alter table 或者create index )

CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] ( col_name [length]) [ASC|DESC ]

aleter table ...add ...

create index .. on ..

删除,删除主键key ,如果有auto_increment不允许删

alter table ... drop index

drop index ... on ...

//创建索引
CAEATE table emp(
    emp_id int primary key auto_increment ,
  emp_name varchar(20) unique,
  dept_id int ,
  constraint emp_dept_id_fk foreign key(dept_id) references dept(dept_id),
  index mul_eid_ename(emp_id,emp_name) //多列索引
);
alter table emp add index idx_dept_id(dept_id);
alter table emp add UNIQUE index uk_dept_name(emp_name);
alter table emp add index mul_dept_id_name(dept_id,emp_name); //联合索引

create index uk_dept_name on emp(emp_name);
create UNIQUE index uk_dept_name on emp(emp_name)

//删除索引
alter table emp drop index uk_dept_name ;
drop index uk_dept_name on emp ;

查看索引

1、show create table book ;

2、show index from book;

删除索引

alter table book drop primary KEY ; //删除主键key ,如果有auto_increment不允许删

哪些情况适合创建索引

1、字段数值有唯一性的限制时

业务上具有唯一特性字段,即时是组合字段也必须建成唯一索引。

2、频繁作为where查询条件的字段

3、经常group by 和 order by 的字段

4、distinct 字段需要创建索引

5、多表join创建索引注意事项:

连接表数量不要超过3张,对where条件创建索引,对用于链接的字段创建索引,并且该字段在多表中类型必须一致

6、使用列的类型小的创建索引

7、使用字符串前缀创建索引

前缀索引

create table shop(address varcahr(120) not null);
alter table shop add index(address(12));
## 查看字段在全部数据中的选择度
select count(distinct address) /count(*) from shop ;
## 通过不同长度计算,与全表选择性对别,越接近于1说明越好 ,一般长度20区分度为90%
count(distinct left('列名',索引长度))/count(*)

8、区分度高散列性高作为索引

9、使用最频繁的列放到联合索引左侧

10、多个字段都需要创建索引时,联合索引优于单索引

11、限制索引的数目,单表不要超过6个

不建议创建索引

1、数据量小不要使用索引。少于1000条

2、有大量重复列不建议使用

3、避免对经常更新的表创建过多索引

4、不建议用无序的值作为索引

5、删除不在使用或者很少使用的索引

6、不要定义冗余或重复的索引

1.5.1.1.12. 性能分析工具

1.5.1.1.12.1. 查询优化器成本

show status like 'last_query_cost' ;

慢sql查询是否开启:

show variables like '%slow_query_log' ; long_query_time默认是10s, 大于10秒

set GLOBAL slow_query_log = on ; //开启

show variables like '%long_query_time%' ; // 慢sql默认时间配置

set global long_query_time = 1 ;

查询系统中是否有慢查询

show global status like '%slow_queries%'

1.5.1.1.12.2. 慢日志分析工具:mysqldumpslow

mysqldumpslow --help

mysqldumpslow -a -s t -t 5 /var/lib/mysql/aa.log

慢查询日志删除后重新生成慢查询日志文件:

mysqladmin -uroot -p flush-logs slow

1.5.1.1.12.3. Show profile 执行成本

show profiles ; //最近查询sql开销

show profile for query 2 ; 查询某一条sql开销

image-20221105160457784

注:show profile 命令将被弃用,我们可以从information_schema 中的profiling数据进行查看。

1.5.1.1.12.4. 分析查询语句:EXPLAIN

基本语法:

EXPLAIN SELECT  select_options;
或者
DESCRIBE SELECT select_opitons;

image-20221105161511012

1、Table

# 查询每行记录都对应一个单表
EXPLAIN select * from s1;
# s1:驱动表  s2:被驱动表
EXPLAIN select * from s1 inner join s2 ;

2、id

在一个大的查询语句中每个select关键字都对应一个唯一id

在子查询情况下,优化器可能涉及子查询的查询语句进行重写,转变为多表查询操作

Union 去重

image-20221105163052705

注:

  • id如果相同,可以认为是一组,从上往下顺序执行
  • 在所有组中,id值越大,优先级越高,越先执行
  • 关注点:id每个号码,表示一趟独立的查询,一个sql的查询趟数越少越好

3、select_type

image-20221105163633308

select关键字对应的哪个查询类型,确定小查询在整个大查询扮演什么角色。

4、partition

匹配分区情况

5、type *

访问类型:system > const> eq_ref> ref > fulltext> ref_or_null> index_merge >unique_subquery>index_subquery>range>index>all

sql性能优化目标:至少达到range级别,要求ref级别,最好是const级别

6、possible_keys 和key

possible_keys 可能用到的索引,key列表示实际用到的索引

7、key_len *

实际用到的索引长度(子节),主要针对联合索引有参考意义(值越大越好)

8、ref

索引等值查询时,与索引列进行等值匹配的对象信息

9、rows *

预估需要读取的记录条数,值越小越好

10、filtered

某个表经过搜索条件过滤后剩余记录条数的百分比。该值越大越好

11、extra *

更准确的理解mysql到底如何执行给定查询语句。

索引下推:Using index condition;

EXPLAIN select * from s1 where key1 > '2' and key1 like '%b';

1.5.1.1.12.5. Explain

explain 不考虑各种cache

explain 不能显示mysql在执行查询器所做的优化工作

explain 不会告诉关于触发器、存储过程的信息或者用户自定义函数对查询的影响情况

部分统计信息是估算,并非精确值

输出四种格式:传统格式、json格式、tree格式以及可视化输出

JSON FORMAT=JSON

EXPLAIN FORMAT = json select ...

image-20221105181049341

read_cost : io 成本 检测rows * (1 - filter) 条记录cpu成本

eval_cost : 检测rows * filter 条记录的成本

prefix_cost:单条查询s1表的成本,也就是read_cost + eval_cost

data_read_per_join : 表示在此次查询中需要读取的数据量

TREE 格式

EXPLAIN format = tree select * from a \G;

可视化显示

image-20221105182121389

Show warnings使用

在执行Explain之后,紧接着使用show warnings语句查看与这个执行计划的扩展信息

1.5.1.1.12.6. 分析优化器trace

SET optimizer_trace = "enabled=on",end_markers_in_json = on;
set optimizer_trace_max_mem_size= 100000;

// 将追踪结果记录在INFORMATION_SCHEMA.OPTIMIZER_TRACE表

// 执行sql后在
select * from information_schema.optimizer_trace \G ;

1.5.1.1.12.7. Mysql 监控分析视图-sys schema

image-20221105202601649

image-20221105202719542

1.5.1.1.13. 索引优化和查询优化

  • 索引失效、没有充分利用到索引--索引建立
  • 关联查询太多join(设计缺陷或不得已的需求) -- sql优化
  • 服务器调优即各个参数设置(缓冲、线程数等) -- 调整my.cnf
  • 数据过多 -- 分库分表

1.5.1.1.13.1. 导致索引失效

主键插入顺序

不遵守最左前缀原则

计算、函数、类型转换(自动或手动)导致索引失效

范围条件右边的列索引失效(name没有用到索引)

image-20221105210151395

不等于(!= 或者 <>) 索引失效

is null 可以使用索引,is not null 无法使用索引

Like 已通配符%开头索引失效

OR前后存在非索引列,索引失效

不同字符集进行比较前转换造成索引失效

1.5.1.1.13.2. 关联查询优化

驱动表是主表、被驱动表是从表、非驱动表。 小表驱动大表

1.5.1.1.13.3. 子查询优化

子查询会建立临时表,查询完毕后在撤销临时表,消耗过多cpu和io资源。

1.5.1.1.13.4. 排序优化

image-20221106103546931

1.5.1.1.13.5. Filesort 算法:双路排序和单路排序

双路排序慢、单路排序快.默认sort_buffer容量1mb,select 不要用*

show variables like '%max_length_for_sort_data%' ; //默认1024子节,影响是单路或者多路

1.5.1.1.13.6. GROUP BY

group by 索引规则跟order by 一致,需要先排序后分组。当无法使用索引时增大max_length_for_sort_datasort_buffer_size参数

where 效率高于having

1.5.1.1.13.7. 优化分页

limit 20000,10

//方案1
select * from student t,(select id from student order by id limit 20000,10) a where t.id = a.id
//方案2
select * from student where id > 200000 limit 10

1.5.1.1.13.8. 覆盖索引

一个索引包含了满足查询结果的数据就叫覆盖索引

索引列 + 主键 包含select 到from 之间查询的列

打破规则

index student(age,name)
#打破规则
select age,name from student where age<>20 ; //覆盖用到索引
select id ,age,name from student where name like '%abc';

好处:

  • 避免innodb表进行索引二次查询(回表)
  • 可以把随机io变成顺序io加快查询效率

1.5.1.1.13.9. 索引下推

select * from s1 where key1 > 'z' and key1 like '%abc' ; // key1是二级索引

原理:二级索引回表滞后,索引确定数据量后,先进行数据条件筛选后在进行回表。减少回表的次数

好处:icp可以减少存储引擎必须要访问基表的次数和mysql服务器必须访问存储引擎的次数。但是,icp加速效果取决于在存储引擎内通过icp筛选掉的数据的比例。

默认情况启用索引条件下推:通过系统变量optimizer_switch 控制 index_condition_pushdown

set optimizer_switch = 'index_condition_pushdown=on' ;//开启
set optimizer_switch = 'index_condition_pushdown=off' ;//关闭

image-20221106112657852

1.5.1.1.13.10. 主键需要怎么设计

自增id问题:

1、可靠性不高:存在自增id回溯问题,在8.0才修复

2、安全性不高:基础id容易被爬虫爬取

3、性能差:需要数据库服务端生成

4、交互多:额外执行last_insert_id()函数。

5、局部唯一性:局部唯一,而不是全局唯一

淘宝主键id : 订单id = 时间 + 去重字段 + 用户id后6位尾号

UUID : 全局唯一,占用36个字节,数据无序,插入性能差。select uuid() from dual ;

uuid = 时间+ uuid版本(16字节) - 时间序列(4字节) - mac地址(12字节) image-20221106115344374

mysql8.0更换时间低位和时间高位存储方式,这样uuid就有序了,存储将字符串用二进制保存,存储空间降低为16字节

1.5.1.1.13.11. 大表优化

读写分离

  • 双主双从

image-20221106150842914

垂直拆分

千万级以上,分库分表

image-20221106151026302

水平拆分

尽量控制单表数据量在1000w以内

image-20221106151306654

1.5.1.1.13.12. ER 模型

image-20221106140252620

image-20221106140418851

1.5.1.1.14. 数据库事务

show engines ;//查看引擎是否支持事务

事务: 一组逻辑操作单元,使数据从一种状态变换到另一种状态

事务处理原则:保证所有事务都作为一个工作单元来执行,即时出现故障,都不能改变这种执行方式.

事务状态:

  • 活动的(active)
  • 部分提交(partially committed)
  • 失败(failed)
  • 终止(aborted)
  • image-20221106153619512

1.5.1.1.14.1. 事务特性:ACID

原子性(atomicity)

一致性(consistency)

隔离性(isolation)

持久性(durability)

1.5.1.1.14.2. 事务隔离级别

脏读(dirty Read)

两个事务sessionA 、sessionB, SessionA读取了sessionB更新但还没被提交的字段.之后若sessionB回滚,sessionA读取的内容就是临时且无效。

不可重复读(non-repeatable Read)

两个事务sessionA、sessionB, sessionA读取了一个字段,然后sessionB更新了该字段,之后sessionA再次读取同一个字段,值就不同,那就意味着发生了不可重复读。

幻读(phantom)

两个事务sessionA、sessionB. sessionA从一个表中读取了一个字段,然后sessionB在该表中插入了一些新的行,之后sessionA再次读取同一个表,就会多出几行。就意味着放生幻读

脏写 > 脏读 > 不可重复读 > 幻读

SQL 标准4个隔离级别

READ UNCOMMITTED 读未提交,

在该隔离级别,所有事务都可以看到其他未提交事务的执行结果.不能避免脏读、不可重复读、幻读

READ COMMITTED 读已提交,

它满足隔离的简单定义:一个事务只能看见已经提交事务所做的改变。这是大多数据库系统的默认隔离级别。可以避免脏读、但不可重复读、幻读问题仍然存在

REPEATABLE READ 可重复读

事务A在读到一条数据之后,此时事务B对数据进行修改并提交,那么事务A再读改数据,读到的还是原来的内容。可以避免脏读、不可重复读,但是幻读问题还存在。这是mysql默认隔离级别。

SERIALIZABLE 可串行化

确保事务可以从一个表中读取相同的行。在这个事务持续期间,禁止其他事务对该表执行插入、更新和删除操作。所有并发问题都能避免,但性能十分低下。

image-20221106160147244

Mysql 支持的四种隔离级别

show variables like 'tx_isolation' ; //查看隔离级别

set session transaction_isolation='read-uncommitted'; //设置会话隔离级别

1.5.1.1.14.3. Mysql 日志

事务的隔离性是由 锁机制实现

而事务的原子性、一致性和持久性由事务redo日志和unod日志保证

image-20221106172749130

Redo log 重做日志

提供在写入操作,恢复提交事务修改页操作,用来保证事务的持久性.

是存储引擎(innodb)生成的日志,记录是物理级别上页修改操作,比如页号、偏移量,主要保证数据可靠性

  • 好处:
  • redo日志降低刷盘频率
  • redo日志占用空间非常小
  • 特点:
    • redo日志顺序写入磁盘
    • 事务执行过程中,redo log 不断记录

重做日志缓冲(redo log buffer)保存在内存中,容易丢失。innodb_log_buffer_size:默认16M。最大4096M;

重做日志文件(redo log file),保存在磁盘中,持久化。

文件存放路径:innodb_log_group_home_dir

show variables like 'innodb_log_files_in_group' ;//文件个数,默认48M(所有文件总和),最大512G

默认innodb_flush_log_at_trx_commint = 1

image-20221106170618078

等于2的情况会存在1秒数据丢失

image-20221106170801260

image-20221106170949444

Undo log 回滚日志

回滚记录到某个特定版本,用来保证事务原子性、一致性

是存储引擎(innodb)生成的日志,记录逻辑操作日志,比如对某一行数据进行insert语句操作,那么undo log就记录一条与之相反的delete 操作。主要用于事务回滚(undo log记录是每个修改操作的逆操作) 和一致性非锁定读(undo log 回滚行记录到某种特定版本...mvcc 多版本并发控制);

Undo log 也会产生redo log

image-20221106174220817

Bin Log

redo log是存储引擎产生,事务操作都会记录,每次重启会生成新文件。 bin log是由数据库产生,bin log 事务提交后记录。

事件形式记录并保存二进制文件,只有ddldml 没有查询语句。

应用场景:数据恢复和数据复制

show variables like 'log_bin%' ;

show binary logs ;//查看日志文件信息

--第一种查看具体日志内容  -v伪sql
mysqlbinlog -v "/var/lib/mysql/binlog/log-bin.00001" ;

--第二种 按事件查看
show binlog events [In 'log_name'] [from pos] [limit [offset,] row_count] ;

-- 数据恢复
-----第一种 start-position
/usr/bin/mysqlbinlog --start-position=886 --stop-position=1729 --database=Ivan /var/lib/mysql/log-bin.000002 | /usr/binmysql -uroot -pfafwef -v ivan

-----第二种 start-datetime
/usr/bin/mysqlbinlog --start-datetime="2022-01-05 15:39:22" --stop-datetime="2022-01-05 17:39:22" --database=Ivan /var/lib/mysql/log-bin.000002 | /usr/binmysql -uroot -pfafwef -v ivan

image-20221107092734031

二阶段提交

image-20221107093441924

image-20221107093638426

通用查询日志

show variables like 'general_log%' ;//查看通用查询日志配置

备份删除文件后生成新日志
mysqladmin -uroot -p flush-logs ;//如果flush报错需要执行install
//install
install -omysql -gmysql -m0644 /dev/null var/log/mysqld.log

错误日志

默认mysqld.log(linux系统) 或 hostaname.err(mac系统)

show variables like 'log-error%' ;

Log-error = /设置日志文件路径

1.5.1.1.14.4. 锁

事务隔离性有锁实现

锁:是计算机协调多个进程或线程并发访问某一资源的机制。当多个线程并发访问某个数据时,需要保证这个数据在任何时刻最多只有一个线程访问,保证数据的完整性一致性。锁冲突也是影响数据并发访问性能的重要因素。

锁结构:

  • trx信息:代表锁结构哪个事务生成
  • is_waiting: 代表当前事务是否在等待。

并发问题解决方法(脏读、不可重复读、幻读):

  • 读操作利用多版本并发控制(mvcc),写操作加锁
  • 读、写都加锁

锁的分类

对数据操作类型划分: 读锁/共享锁 S 、 写锁/排它锁X

锁粒度角度划分:

表级锁:表级别s锁和x锁 、意向锁、自增锁、MDL锁

行级锁:RECORD locks 、Gap locks、Next-key locks、插入意向锁

页级锁

对锁的态度划分:悲观锁、乐观锁

加锁方式: 隐式锁、显示锁

其他锁:全局锁、死锁

锁定读

//对读取记录加S锁

select ... lock in share mode ;
或
select ... for share ;#(8.0新语法)

//对读取记录加X锁, 事务阻塞

select ... for update ;

表级加锁

//查看加锁记录
show open tables where in_use > 0 ;
//加表级别S锁
lock tables mylock read ;
//解锁
unlock tables;
//加表级别X锁
lock tables mylock write;

image-20221106182505136

意向锁

innodb允许行级锁和表级锁共存。

  • 意向锁的存在时为了协调行锁和表锁的关系,支持多粒度(表锁与行锁)的锁并存。

  • 意向锁是一种不与行级锁冲突表级锁

  • 表明某个事务正在某些行持有了锁或该事物准备去持有锁。

意向共享锁:

事务有意向对表中某些行加共享锁(S锁)

意向排他锁

事务有意向对表中某些行加排它锁(X锁)

image-20221106184000525

自增锁

自增主键锁

元数据锁

MDL 操作进行加锁

行锁(record locks)

行级锁在存储引擎中实现

优点:锁定力度小,发生锁冲突概率低,可实现并发度高

缺点:对于锁的开销较大,加锁会比较慢,容易出现死锁情况

记录锁

Lock_REC_NOT_GAP

间隙锁(gap locks)

仅仅为了防止插入幻影记录。虽然有共享gap和独占gap但是他们起到的作用是相同的 。

临建锁(next-key locks)

有时候即想锁住某条记录,又想阻止其他事物在该记录前面的间隙插入新纪录。

插入意向锁(insert intention locks)

页锁

页锁的开销介于表锁和行锁之间,会出现死锁.锁定粒度介于表锁和行锁之间,并发度一般。

悲观锁:共享资源每次只给一个线程使用,其他线程阻塞,用完后在把资源转给其他线程。比如java中:synchronized和reentrantLock等独占锁就是悲观思想的实现

避免超卖:需要将要执行的sql语句放在同一个事务中,否则达不到锁定数据行的目的;

select .... for update 是mysql中悲观锁. 语句执行中会把所有扫描行都会被加锁,因此在mysql中用悲观锁必须要确定使用了索引,而不是全表扫描,否则这个表都会锁住

乐观锁:不采用数据库自身锁机制,而是通过程序实现。在程序上我们采用版本号机制或者cas机制实现。乐观锁适用于多读应用类型,这样可以提高吞吐量。

update ... set version=version+1 where version = version;

image-20221106194724974

全局锁

全局锁对整个数据库实例加锁.当加锁后整个库处于只读状态。使用场景:全库逻辑备份

-- 全局锁命令
flush tables with read lock ;

死锁

两个事务都持有对方需要的锁,并且在等待对方释放,并且双方都不会释放自己的锁。

解决:

方式1:等待,直到超时(innodb_lock_wait_timeout=50s) ,缺点:对于在线服务等待时间往往无法接受

方式2: 使用死锁检测进行死锁处理

innodb提供了wait-for graph算法主动进行死锁检测。要求数据库保存锁的信息链表和事务等待链表两部分信息。

一旦检测到死锁就会回滚undo量最小的事务,让其他继续等待。

image-20221106212618918

避免死锁:

  • 合理设计索引,是业务sql尽可能通过索引定位更少的行,减少锁竞争

  • 调整业务逻辑sql执行顺序,避免update/delete长时间持有锁的sql在事务前面

  • 避免大事务,尽量将大事务拆成多个小事务来处理,小事务缩短锁定资源时间,发生锁冲突几率更小

  • 在并发比较高的系统中,不显示加锁,特别在事务里显示加锁.如select .... for update。如果在事务里运行start transaxtion 或设置autocommit 等于0,那么就会锁定所查找到的记录。

  • 降低隔离级别,如果业务允许,将隔离级别调低也是较好的选择。比如将隔离从RR调整到RC,可以避免掉很多因为gap锁造成的死锁。

索监控

事务和锁信息记录在information_schema库中,涉及三张表:innodb_trx、innodb_locks 、innodb_lock_waits

1.5.1.1.14.5. MVCC

通过数据行多个版本管理实现数据库的并发控制。实现了一致性读操作。

实现原理:依赖于隐藏字段、undo log 、read view

Read view

事务在使用mvcc机制进行快照读操作时产生的读视图.事务启动时,会生成数据库系统当前的一个快照,innodb为每个事务构造了一个数组,用来记录并维护系统当前活跃事务的id(活跃指的是启动了但还没有提交).

在READ COMMITTED 和 REPEATABLE READ 中用到

读已提交隔离级别中:一个事务中每一次select查询都会重新获取一次read view ;

可重复读隔离级别中:只在第一次生成read view ;

image-20221106222306181

1.5.1.1.14.6. 主从复制

image-20221107093740488

image-20221107100605460

results matching ""

    No results matching ""