MySQL数据库(九):MySQL总结

1、数据库锁

1.1表锁(一般加读)

解释:偏向MyISAM存储引擎,开销小,加锁快;无死锁,锁定粒度大,发送锁冲突的概率最高,并发度低。
分类:锁也分为读锁和写锁,按照下面的语法为表增加不同的锁
lock table 表名字 read(write),表名字2 read(write);
如果表锁被锁定,在另一个客户端操作这个表的时候就会变为阻塞状态,在客户端1对表添加读锁,客户端2无法对表进行写操作,如果进行写操作那么状态为阻塞,客户端1对表添加写锁,客户端2可以对表中数据进行读操作,写操作时为阻塞状态,直到释放锁,释放锁为全表释放不能指定表名,因为已经对表进行了锁的操作
unlock tables;
总结:MyISAM在执行查询语句(select)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁

  1. 对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其他进行的写操作
  2. 对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的读写操作

1.2行锁(一般加写)

定义:偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁。锁定粒度最小,发生锁冲突的概率最低,并发度也最高
说明:InnoDB与MyISAM的最大不同点,支持事务,采用了行级锁
如果不关闭自动提交innodb中每个操作都必须commit,才能够保存进入数据库当中。

通过检查innodb_row_lock状态变量来分析系统上的行锁争夺情况

show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits       当前正在等待锁定的数量
* Innodb_row_lock_time            从系统启动到现在锁定的总时间长度
* Innodb_row_lock_time_avg        每次等待所花费平均时间
Innodb_row_lock_time_max        从系统启动到现在等待最长的一次所花费的时间
* Innodb_row_lock_waits           系统启动后到现在总共等待的次数

如果不关闭自动提交可以采取下面的方式对行进行具体的加锁操作

select * from test_innodb_lock where a = 8 for update;

1.3间隙锁

定义:当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,innodb会给符合条件的已有数据记录的索引项加锁, 对于键值在条件范围内但并不存在的记录,叫做"间隙"innodb也会对这个"间隙"加锁,这种锁机制就是所谓的间隙锁
危害:因为SQL执行过程中通过范围查找的话,他会锁定整个范围内所有的索引值,即使这个键值并不存在,间隙锁有一个比较致命的弱点,就是当锁定以为范围键值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

2、MySQL分区

如果数据量太大,每当全局搜索东西的时候性能就会比较慢,为了优化查询速度,就会给数据表进行对应的分区

2.1RANGE分区

特点:

  1. 根据分区键值的范围把数据行存储到表的不同分区中
  2. 多个分区的范围要连续,但是不能重叠
  3. 分区不包括上限,取不到上限值
create table `login_log_range`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb 
partition by range(login_id)(
partition p0 values less than(10000),   # 实际范围0-9999
partition p1 values less than(20000),   # 实际范围10000-19999
partition p2 values less than(30000),# 实际范围20000-29999
partition p3 values less than maxvalue  # 存储大于30000的数据
);

使用range分区的情景

  1. 分区键为日期或是时间类型
  2. 经常运行包含分区键的查询,MySQL可以很快的确定只有某一个或某些分区需要扫描,例如检索商品login_id小于10000的记录数,MySQL只需要扫描p0分区即可
  3. 定期按分区范围清理历史数据

2.2LIST分区

特点:

  1. 按分区键取值的列表进行分区
  2. 同范围分区一样,各分区的列表值不能重复
  3. 每一行数据必须能找到对应的分区列表,否则数据插入失败

案例:

create table `login_log_list`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip',
    login_type int(10) not null
)engine=innodb 
partition by list(login_type)(
partition p0 values in(1,3,5,7,9),    
partition p1 values in(2,4,6,8)   
);

2.3HASH分区

特点:

  1. 根据MOD(分区键,分区值)的值把数据行存储到表的不同分区内
  2. 数据可以平均的分布在各个分区中
  3. HASH分区的键值必须是一个INT类型的值,或是通过函数可以转为INT类型

案例:

create table `login_log`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb default charset=utf8 partition by hash(login_id) partitions 4;

create table `login_log`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb default charset=utf8 partition by hash(UNIX_TIMESTAMP(login_time)) partitions 4;

2.4如何选择合适的分区方式

需求:
1.用户每次登陆都会记录到日志表中
2.用户登录日志保存一年,一年后可以删除
根据需求创建range分区

create table `login_log_range`(
    login_id int(10) unsigned not null comment '登录用户id',
    login_time datetime  not null default current_timestamp,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb 
partition by range(year(login_time))(
partition p0 values less than(2015),   
partition p1 values less than(2016),   
partition p2 values less than(2017)   
);

插入数据

insert into login_log_range values
(1,'2015-01-25',1),
(2,'2015-07-25',2),
(3,'2015-06-25',3),
(4,'2016-03-25',2),
(5,'2016-02-25',1);

查询表

select table_name,partition_name,partition_description,table_rows from 
information_schema.`partitions` where table_name = 'login_log_range'

修改分区-添加分区

alter table login_log_range add partition (partition p4 values less than(2018))

分区删除

alter table login_log_range drop partition p0;

3、MySQL操作规范

3.1命名规范

  1. 表名建议使用有业务意义的英文词汇,必要时可加数字和下划线,并以英文字母开头
  2. 库、表、字段全部采用小写,MySQL 在 Linux 下默认是区分大小写的,而在 Windows 下不区分大小写。因此,防止出现问题,建议都设置为小写。
  3. 避免用 MySQL 的保留字,order等关键字需加``
  4. 命名(包括表名、列名)禁止超过 30 个字符
  5. 临时库、表名必须以 tmp 为前缀,并以日期为后缀,如:tmp_shop_info_20190404
  6. 备份库、表必须以 bak 为前缀,并以日期为后缀,如:bak_shop_info_20190404
  7. 非唯一索引必须按照"idx_字段名称"进行命名
  8. 唯一索引必须按照"uniq_字段名称"进行命名

3.2设计规范

  1. 表必须有主键
  2. 不使用更新频繁的列做主键
  3. 尽量不选择字符串列做主键
  4. 不使用 UUID MD5 HASH 做主键
  5. 默认使用非空的唯一键作为主键
  6. 如无特殊要求,建议都使用 InnoDB 引擎,MySQL5.5.5+版本之后默认是innodb引擎
  7. 默认使用 utf8mb4 字符集,数据排序规则使用 utf8mb4_general_ci
  8. utf8mb4 为万国码,无乱码风险;与 utf8 编码相比,utf8mb4 能支持 Emoji 表情
  9. 所有表、字段都需要增加 comment 来描述此表、字段所表示的含义
  10. data_status TINYINT NOT NULL DEFAULT ‘1’ COMMENT ‘1代表记录有效,0代表记录无效’
  11. 尽可能不使用 TEXT、BLOB 类型
    原因:会浪费更多的磁盘和内存空间,非必要的大量大字段查询会淘汰掉热数据,导致内存命中率急剧降低,影响数据库性能。如果实在有某个字段过长需要使用 TEXT、BLOB 类型,则建议独立出来一张表,用主键来对应,避免影响原表的查询效率。
  12. 单表列数目建议小于 30,字段小于30个,按照数据库范式进行设计

3.3SQL语句规范

  1. 避免隐式转换,varchar加引号查询,别让引擎去做这件事
  2. 尽量不使用select *,只 select 需要的字段,读取不需要的列会增加 CPU、IO、NET 消耗,并且不能有效的利用覆盖索引。使用 SELECT * 容易在增加或者删除字段后导致程序报错
  3. 建议将子查询转换为关联查询
  4. 建议应用程序捕获 SQL 异常,并有相应处理

3.4行为规范

  1. 批量导入、导出数据必须提前通知 DBA 协助观察
  2. 不在业务高峰期批量更新、查询数据库
  3. 删除表或者库要求尽量先 rename,观察几天,确定对业务没影响,再 drop
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值