MySQL优化
程序员文章站
2024-01-26 12:17:46
...
MySQL优化
数据库优化包括的方面:
- 表的设计合理化(3范式)
- SQL语句的优化
- 给表要添加合适的索引(四种:普通索引、主键索引、唯一索引unique、全文索引),如何使用索引
- 分表技术(水平分割、垂直分割)
- 定时清除垃圾数据,定时进行碎片整理(尤其存储引擎是myisam的)
- UML序列图和流程图
- 多用存储过程[执行sql:编译->执行->缓存,存储过程(已编译过):执行->缓存。模块化编程,但是移植性不好]和触发器
- 对mysql的配置[1、配置最大并发数max_connections ,默认值为100,一般网站调整到1000左右。2、调整缓存大小。my.ini文件中设置]进行一些优化
- 读写分离
- MySQL服务器硬件升级
数据库设计
-
良好的数据库
节省存储空间
保证数据完整性 -
糟糕的数据库
数据冗余,存储空间浪费
数据产生不完整
设计数据库步骤
- 充分了解数据
- 标识实体:具体存在的对象、名词
- 标识属性
- 标识关系
一个栗子:
1、需求
2、实体
- 用户
- 发帖
- 回帖
- 板块
3、属性
4、关系
- 一对一
- 一对多
- 多对一
- 多对多
如何将E_R图(实体关系图)转成表:
数据规范化
通过三范式对表进行规范化。
满足顺序:第一范式——第二范式(所有非键字段依赖主键字段)——第三范式(非主键字段消除传递依赖)
-
确保每列的原子性
第一范式用来规范所有字段,所有字段都不可再分。
注意:比如地址这个字段,如果不分类汇总、不排序,仅仅起一个字符串的作用,这时候我们不拆分(反三范式)。 非键字段必须依赖于键字段(一个表只能描述一个事情)
-
消除传递依赖
在非主键字段中,如果一个字段可以推导出另一个字段,这就叫做传递依赖。
数据库分类
- 关系型数据库:MySQL、Oracle、SWL Server、db2、informix、sysbase
- 非关系型数据库(面向对象)
- NoSql数据库:MongoDB(面向文档)
定位慢查询
【增删改】(一共占有10%)查(90%)
MySQL数据库一些关于状态的查询
show status指令:
指令 | 说明 |
---|---|
show status | 查询mysql数据库的一些运行状态 |
show status like ‘com_insert’ | 显示了执行了多少次插入 |
show status like ‘com_update’ | 显示了执行了多少次更新 |
show status like ‘com_delete’ | 显示了执行了多少次删除 |
show status like ‘com_select’ | 显示了执行了多少次查询语句 |
show status like ‘uptime’ | 显示了Mysql数据库起用了多长时间,如果时间长了,数据库表的存储引擎有的是myisam的,这时候要注意碎片整理 |
show[session | global ] status like ……如果不写,默认是会话级的(session),如果mysql一直启动到现在,用global。
显示慢查询
指令 | 说明 |
---|---|
show variables like ‘long_query_time’ | 显示慢查询的时间,默认是10s是一个慢查询 |
set long_query_time = 0.5 | 设置long_query_time为0.5 |
show status like ‘slow_queries’ | 显示慢查询条数 |
定位慢查询(开启慢查询的日志)
开启慢查询日志后,日志文件的位置在my.ini文件中查找 ‘datadir’ ,默认情况下,mysql是不会记录慢查询。
开启慢查询步骤:
- 关闭当前的MySQL服务 net stop mysql(myswl.exe:客户端, mysqld.exe :服务器端)
- 安全模式启动就会写日志,考虑MySQL版本相应的命令不同,进入mysql/bin目录下, mysqld –safe–mode –slow–query–log [–safe–mode:安全模式,–slow–query–log:开启慢查询日志]
注:安全模式下的关闭mysql服务,需要通过mysqladmin -uroot -pXXX shut down。 - 设置慢查询时间
- 执行查询语句
- 分析慢查询日志
explain分析sql语句
索引(建立适当的索引 ——create index)
索引分类:
- 主键索引
- 唯一索引
- 全文索引
- 普通索引
操作索引
1. 添加
1.1 主键索引添加——把表的某个列设置为主键,则该列就是主键索引
create table test(id int unsigned primary key auto_increment, name varchar(32) not null default ‘’);
如果没有指定主键索引,可以创建表后,再次添加指令:
alter table 表名 add primary key(列名)
1.2 普通索引——一般情况下,先创建表,再创建普通索引
cretate index 索引名 on 表 (列)
1.3 全文索引——FULLTEXT,应用于对文字、文本的检索,比如文章。全文索只针对MyISAM有用
create table articles(
id int unsigned auto_increment not null primary key,
title varchar(200),
body text,
FULLTEXT(title, body)
)engin=myisam charset utf8
用法:
select * from articles where match(title, body) against('database')
2. 查询
desc 表名 【缺点:无法显示索引名】
show index(es) from 表名
show keys from 表名
【拓展】
.frm 表的结构
.MYD 表的数据本身
.MYI 表的索引
3. 删除
4. 修改
【为什么创建索引后,速度会变快?】
索引是以二叉树(BTREE)的算法索引文件
上一篇: React和Vue比较
下一篇: 解决浮动导致父元素高度坍塌的几种方法