mysql 数据库优化

倾听岁月

已于 2023-11-23 15:00:32 修改

阅读量998

点赞数

分类专栏： mysql 文章标签：数据库开发

于 2022-04-20 15:44:50 首次发布

本文链接：https://blog.csdn.net/u014559227/article/details/124296917

版权

mysql 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

前引

在实际项目开发过程中，关于数据库的使用，维护一直都是项目的重点，一个优秀的程序员总是需要去了解数据库的一些底层运行机制以及如何去优化数据库的使用。

查询

针对于select语句，我们要做到客户端需要哪些数据，我们就获取哪些数据，避免造成数据赘余（将一些不需要的数据字段也一起获取出来了）。
针对于select语句我们在进行索引获取的时候要避免使用函数运算，因为使用函数运算会导致索引的失效。
使用连接（JOIN）来代替子查询（Sub-Queries）

索引

在使用where索引时，应当尽量避免mysql进行自动类型转换。举个例子

mysql 表结构
create table test(
	id int(11) UNSIGNED AUTO_INCREMENT, 
	testInfo varchar(255),
	PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

如上可知我们创建了名为test的一张表，其中id类型为int，testInfo类型为varchar
此时我们创建索引sql语句

//错误写法
select * from test where id='1' and testInfo='1';
select * from test where id=1   and testInfo=1;

//正确写法
select * from test where id=1 and testInfo='1';

因为id数值为int，我们在使用此字段为查询条件时也要注意此字段索引变更为number类型；

针对索引where，我们在使用like时如果索引前缀为“%”那么也会进行全表扫描
创建数据表时，我们应尽量避免字段可为空的设置，因为这样会让mysql在执行查询的时候多加一层的判断
一定不要在where 条件中加入函数的处理，如果加入了函数的处理，会导致索引的失效
要在区分度高的字段上建立索引。
什么叫区分度？
区分度等于count(distinct 具体的列) / count(*)，表示字段不重复的比例。
例如主键id的区分度就为1
如果一张表中标识用户性别的字段为sex(取值：0为男生，1为女生)，假设现在表中有100w人，其中男女各占50w人，那么此时的区分度就不高
当使用字符串列建立索引时，要注意其使用的大小，如果过长，那么对数据库的性能消耗就大（索引长度越小，排序速度越快，能在内存存储的数据更多），具体应根据业务合理安排。索引长度尽量小
建立联合索引需要知道的规则：最左前缀原则，举例如下

where aaa='xxxxx' and bbb='' and ccc=''

联合索引实际上是建立了三个索引： (aaa)，(aaa,bbb)，(aaa,bbb,ccc)

不同的引擎底层的搜索结构
MyIsam 引擎
非聚集索引：索引文件和数据文件是分开的，索引文件的叶子节点指向数据文件的数据地址。
不支持事务ACID

innodb 引擎
聚集索引：索引和数据是在一个文件上，即是叶子节点存储的是真实的数据
支持事务ACID
唯一索引创建的时候要注意的事项：创建联合唯一索引的字段，都不能允许为 null，否则 mysql 的唯一性约束可能会失效。

索引 - 失效的避免

哪些情况下会导致mysql 索引失效

在where 条件中使用函数（作用在了查询字段上）
在where 条件中使用不合理的like 语句如"%谭" 和 “%谭%”
在where 条件中使用表达式
对索引使用隐式转换
联合索引非最左匹配
where子句中or条件列没有使用索引

表设计

选择合适的字段属性值：不同类型的字段类型占用的空间不同，数据库中的表越小，在它上面执行的查询也就越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度舍得尽可能小。
尽量把字段设置为NOT NULL：对于某些文本字段来说，例如“省份”或者“性别”，我们可以将他们定义为ENUM(枚举)类型。因为在MySQL中，ENUM类型被当做数值型数据来处理，而数值型数据被处理起来的速度要比文本类型要快得多。这样我们又可以提高数据库的性能。如果某些场景，这个字段确实需要有填充，我们可以填充空字符串 default ‘’ 来代替default null

数据库设计优化

一致性原则：对数据来源进行统一、系统的分析与设计，协调好各种数据源，保证数据的一致性和有效性。
完整性原则：数据库的完整性是指数据的正确性和相容性。要防止合法用户使用数据库时向数据库加入不合语义的数据。对输入到数据库中的数据要有审核和约束机制。
安全性原则：数据库的安全性是指保护数据，防止非法用户使用数据库或合法用户非法使用数据库造成数据泄露、更改或破坏。要有认证和授权机制。
可伸缩性与可扩展性原则：数据库结构的设计应充分考虑发展的需要、移植的需要，具有良好的扩展性、伸缩性和适度冗余。
规范化原则：数据库的设计应遵循规范化理论。规范化的数据库设计，可以减少数据库插入、删除、修改等操作时的异常和错误，降低数据冗余度等。

分库分表

mysql 表结构

#db1
create table users(
	id int(11) UNSIGNED AUTO_INCREMENT, 
	username varchar(255),
	PRIMARY KEY (`id`),
	UNIQUE KEY `username_unique` (`username`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#db2
create table users1(
	id int(11) UNSIGNED AUTO_INCREMENT, 
	username varchar(255),
	PRIMARY KEY (`id`),
	UNIQUE KEY `username_unique` (`username`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

根据id range 区间分表
优点：
a、通过id 查询，删除，修改速度快。
b、数据新增速度快
c、排序速度快
缺点：
a、如果用户通过username 那么就需要执行全库全表（所有的表）扫描，速度低下
根据username hash 算法分表
优点：
a、通过id或是username 查询，删除，修改速度快。
b、数据新增速度快
缺点：
a、排序速度慢（不管是通过id排序还是时间排序都很慢），可搭配搜索引擎（比较熟悉的是阿里云智能开放搜索）处理。

具体可根据自己的情况选择，鱼和熊掌不可兼得。

主从数据库（读写分离）

主从数据库产生延迟了怎么办？
需要分析问题原因

如果是因为从库读比较慢，那么应考虑是否是索引导致的性能问题。如果是，优化查询索引。
如果是因为大量读取数据导致的性能瓶颈，那么应考虑升级从库数据库配置，或是新增几台从库（读-数据库），降低数据库压力
如果是因为从库和主库的服务器配置不一致导致的性能问题，那么应升级配置使主库和从库的配置一致。
如果是因为主库写数据的频率太高导致主库压力激增，那么此时应考虑分库。

数据安全

业务场景：我们要保证一个用户业务始终唯一（一个用户对同一种业务只有一条记录）

方案1

CREATE TABLE `test1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(11) NOT NULL DEFAULT '0',
  `tag` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `app_id` int(11) DEFAULT NULL,
  `member_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `value_index` (`value`) USING BTREE COMMENT 'value 索引',
  KEY `value_member_id` (`member_id`,`app_id`) USING BTREE COMMENT 'member_id' #这里很重要，因为我们需要采用悲观锁来实现
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p0 VALUES LESS THAN (3) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

假设数据当中已有数据
在这里插入图片描述

事务A
time1 : begin;
time3 : select * from test1 where member_id=1 and app_id=12; //返回结果为空
time5 : insert into test1(value,age,tag,app_id,member_id) values("谭勇",27,1,12,1); 
//运行产生阻塞，虽然表当中没有这行记录，但事务B锁住了这行记录，如果程序没有执行time4只有事务A在执行，那么这里的insert是成功的

事务B
time2 : begin;
time4 : select * from test1 where member_id=1 and app_id=12;//返回结果为空，没有阻塞住，因为查询结果为空
time6 : insert into test1(value,age,tag,app_id,member_id) values("谭勇",27,1,12,1); //此时报错（死锁冲突），事务A的insert 执行成功

基于以上原理，我们在做唯一效验并插入数据的时候特别有用。

为什么一定要给member_id和app_id 加索引?

如果不加索引，在数据库中执行

select * from test1 where member_id=1 and app_id=12;

锁的是表，不是记录。加了索引后，锁的是记录而不是表（虽然记录不存在，但对这个记录的锁依然存在）。

方案2（荐）
加联合索引并unique，示意

alter table test1 add unique key(member_id,app_id);

这样并发的情景下也能去重。成本低，性能好

mysql 事务命令辅助

//查看当前正在运行的事务及事务详情
select * from information_schema.innodb_trx\G;

select * from information_schema.innodb_trx;

select * from performance_schema.data_locks\G;

在这里插入图片描述
事务锁冲突模型。

锁空记录 - 间隙锁

间隙锁是innodb中行锁的一种，但是这种锁锁住的却不止一行数据，他锁住的是多行，是一个数据范围。间隙锁的主要作用是为了防止出现幻读，但是它会把锁定范围扩大，有时候也会给我们带来麻烦。在数据库参数中，控制间隙锁的参数是：innodb_locks_unsafe_for_binlog，这个参数默认值是OFF，也就是启用间隙锁，他是一个bool值，当值为true时表示disable间隙锁。那为了防止间隙锁是不是直接将innodb_locaks_unsafe_for_binlog设置为true就可以了呢？不一定！而且这个参数会影响到主从复制及灾难恢复，这个方法还尚待商量。

间隙锁的出现主要集中在同一个事务中先delete 后 insert的情况下，当我们通过一个参数去删除一条记录的时候，如果参数在数据库中存在，那么这个时候产生的是普通行锁，锁住这个记录，然后删除，然后释放锁。如果这条记录不存在，问题就来了，数据库会扫描索引，发现这个记录不存在，这个时候的delete语句获取到的就是一个间隙锁，然后数据库会向左扫描扫到第一个比给定参数小的值，向右扫描扫描到第一个比给定参数大的值，然后以此为界，构建一个区间，锁住整个区间内的数据，一个特别容易出现死锁的间隙锁诞生了。
test表
在这里插入图片描述