MySQL优化

爱吃糖的靓仔

已于 2023-04-13 16:08:44 修改

阅读量181

点赞数

分类专栏： # SQL使用文章标签： mysql 数据库 java

于 2019-10-10 22:53:45 首次发布

本文链接：https://blog.csdn.net/qq_42292373/article/details/102490494

版权

SQL使用专栏收录该内容

12 篇文章 0 订阅

订阅专栏

文章目录

具体的MySQL的优化方案有哪些
什么是数据库的三大范式
数据库的索引优化
使用索引需要注意的地方
- 索引所带来的代价
- 何时添加索引？
SQL优化

具体的MySQL的优化方案有哪些

1：数据库的设计- 符合三大范式（3NF）
2：数据库的索引，唯一索引，主键索引，聚合索引，复合索引，默认普通的一个索引
3：SQL调优
4：数据库的分表分库【水平分割、垂直分割】
5：读写分离
6：使用存储过程【模块化编程，可以提高速度】
7：MySQL的配置进行优化，比如MySQL的最大并发数，调整缓存大小 — my.ini 文件
8：定时清理碎片残留，定时进行碎片整理(MyISAM)
9：mysql服务器硬件升级

什么是数据库的三大范式

第一范式：1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

比如address字段属性为：上海市，但是他还可以分为浦东新区等。。

第二范式：2NF是对记录的惟一性约束，表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，主键不能包含业务逻辑。

表有要有主键ID，所以在项目中，不会用主键做业务操作的

第三范式：3NF是对字段冗余性的约束，它要求字段没有冗余。没有冗余的数据库设计可以做到。
但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

比如user表
id name address
1 william 上海市
2 jack 上海市
3 hanna 北京市
正确的做法，我们对市区创建一个表

city
id name
1 上海市
2: 北京市

数据库的索引优化

什么是索引？

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B+树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

索引的主要作用

提供查询的效率，二叉树-B+树
传统方式如果不添加索引，使用的是折半查找

索引的分类

主键索引

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( […], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。

创建表同时，新增索引
CREATE TABLE aaa (
id int PRIMARY KEY auto_increment,
name  VARCHAR(32) not null DEFAULT '')
创建表后，新增索引
alter table aaa add PRIMARY KEY (id)

查询表中的索引
desc 表名称
show index from 表名
show keys from 表名

加上索引和不加索引的区别


-- 64.217 s
SELECT * FROM emp WHERE 

-- 0.390
SELECT * FROM emp WHERE  empno = 848032

普通索引

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

在这里插入图片描述
唯一索引

这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：
创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)；
修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表)；
创建表的时候指定索引，例如CREATE TABLE tablename ( […], UNIQUE [索引的名字] (列的列表) )；

在这里插入图片描述

注意：所以约束他是可以为null
全文索引

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;

索引为什么能提升搜索速度

索引通过B+树来实现的，B+树根据折半的办法，变相的把搜索范围减少一半以上
在这里插入图片描述

使用索引需要注意的地方

以这个索引为例子
全文索引

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;

错误用法:
select * from articles where body like ‘%mysql%’; 错误用法索引不会生效
使用 EXPLAIN select * from articles where body like ‘%mysql%’; 可以查看语句的查询过程
exselect * from articles where body like ‘%mysql%’
正确用法:
select * from articles where match(title,body) against ( ‘database’)

联合索引的注意事项
新增dept 数据导入数据

create PROCEDURE insert_dept(in start int(10),in max_num int(10))
BEGIN
 declare i int DEFAULT 0;
 set autocommit=0;
 REPEAT
 set i=i+1;
 insert into dept values ((start+i),rand_string(10),rand_string(8));
 UNTIL i =max_num
 end REPEAT;
  commit;
END
执行
call insert_dept(100,10);

创建主键索引
alter table 表名 add primary key (列名);
创建一个联合索引
alter table dept add index my_ind (dname,loc); // dname 左边的列,loc就是右边的列

注意:
1.对于创建的多列索引，如果不是使用第一部分，则不会创建索引。 最左原则
explain select * from dept where loc=‘aaa’\G
就不会使用到索引
2.模糊查询在like前面有百分号开头会失效。
3. 如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用or 关键字
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’), 也就是，如果列是字符串类型，就一定要用 ‘’ 把他包括起来.
5.如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

查询索引所用使用率
show status like "handler_read%";

大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高，说明查询低效  代表节点的层数

说明:

在mysql中fulltext 索引只针对 myisam生效
mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文
使用方法是 match(字段名…) against(‘关键字’)
全文索引：停止词, 因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.比如（a，b，mysql，the）
mysql> select match(title,body) against (‘database’) from articles;（输出的是每行和database的匹配度）

索引所带来的代价

占用磁盘空间
对DML(update、delete、insert)语句的效率影响
增删改会对索引影响，因为索引要重新整理。

存储引擎允许的索引类型
myisam btree
innodb btree
memory/yeap Hash,btree

何时添加索引？

那些列上适合添加索引
① 查询作为查询条件字段应该创建索引
② 唯一性太差的字段不适合单独创建索引,即使频繁
Select * from emp where sex=’男’
③ 频繁更新字段，也不要定义索引。
④ 不会出现在where语句的字段不要创建索引

总结:满处一下条件的字段，才应该创建索引
① 肯定在where条件经常使用
② 该字段的内容不是唯一的几个值
③ 字段内容不是频繁变化

SQL优化

定位需要优化的SQL

MySQL默认10秒内没有响应SQL结果,则为慢查询,MySQL默认慢查询不会有日志进行记录
可以去修改MySQL慢查询默认时间

此时我们先来讲解下简单的status的简单语法

–mysql数据库启动了多少时间
show status like ‘uptime’;

show stauts like ‘com_select’ show stauts like ‘com_insert’ …类推 update delete（显示数据库的查询，更新，添加，删除的次数）

//显示到mysql数据库的连接数
show status like 'connections ';

//显示慢查询次数
show status like ‘slow_queries’;

修改慢查询时间
set long_query_time=1; —但是重启mysql之后，long_query_time依然是my.ini中的值

添加测试数据

导入一下脚本
创建表结构

/*部门表*/
CREATE TABLE dept( 
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,  /*编号*/
dname VARCHAR(20)  NOT NULL  DEFAULT "", /*名称*/
loc VARCHAR(13) NOT NULL DEFAULT "" /*地点*/
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
/*员工表*/
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;



/*薪水*/
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*测试数据*/

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

创建函数

create function rand_string(n INT) 
returns varchar(255) #该函数会返回一个字符串
begin 
#chars_str定义一个变量 chars_str,类型是 varchar(100),默认值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do 
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end

create FUNCTION rand_num()
RETURNS int(5)
BEGIN
 DECLARE i int default 0;
 set i =floor(10+RAND()*500);
 return i;
END

创建存储过程

delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set autocommit =0 把autocommit设置成0
 set autocommit = 0;  
 repeat
 set i = i + 1;
 insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
   commit;
 end $$

执行存储过程

call insert_emp (100001,40000000);

如何将慢查询定位到日志上

1：首先停止mysql的服务打开命令行输入services.msc 关闭mysql服务
2：在命令行进入mysql的安装目录

C:\Program Files\MySQL\MySQL Server 5.6

3：输入
bin\mysqld.exe --safe-mode --slow-query-log

具体的日志文件位置在my。ini 有记载
在这里插入图片描述
4：然后我们运行自己的select * from emp 查看data里面的日志即可

mysql 数据库引擎的选择

使用的存储引擎 myisam / innodb/ memory
myisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的发帖表，回复表.
INNODB 存储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.
MyISAM 和 INNODB的区别

事务安全（MyISAM不支持事务，INNODB支持事务）
查询和添加速度（MyISAM批量插入速度快）
支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）
锁机制（MyISAM时表锁，innodb是行锁）
外键 MyISAM 不支持外键， INNODB支持外键. (在PHP开发中，通常不设置外键，通常是在程序中保证数据的一致)
Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快. （如果mysql重启的话，数据就不存在了）

在这里插入图片描述
如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理
举例说明:
create table test100(id int unsigned ,name varchar(32))engine=myisam;
insert into test100 values(1,’aaaaa’);
insert into test100 values(2,’bbbb’);
insert into test100 values(3,’ccccc’);
insert into test100 select id,name from test100;
我们应该定义对myisam进行整理
optimize table test100;

SQL优化具体的方法

至于具体的SQL优化，已经老生杂谈区啦，分享几个厉害大佬写的博客
SQL语句常用的优化手段
 SQL优化指南

爱吃糖的靓仔

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL优化

文章目录具体的MySQL的优化方案有哪些什么是数据库的三大范式数据库的索引优化SQL优化添加数据如何将慢查询定位到日志上具体的MySQL的优化方案有哪些1：数据库的设计- 符合三大范式（3NF）2：数据库的索引，唯一索引，主键索引，聚合索引，复合索引，默认普通的一个索引3：SQL调优4：数据库的分表分库【水平分割、垂直分割】5：读写分离6：使用存储过程【模块化编程，可以提高速度】7...
复制链接

扫一扫

专栏目录