mysql 优化

最新推荐文章于 2021-01-21 14:07:58 发布
最新推荐文章于 2021-01-21 14:07:58 发布
阅读量618 收藏
点赞数
分类专栏： mysql 文章标签： mysql
本文深入探讨了MySQL优化的关键步骤，包括表设计遵循规范、SQL语句优化、索引创建与配置、存储过程与触发器的运用，以及硬件升级策略。通过实例分析，展示了如何在不同场景下实施优化措施，显著提升数据库性能。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

mysql 优化
从以下几个方面来进行优化

  1. 表的设计要合理(3NF)第三范式->第六范式
  2. SQL优化
  3. 创建适当的索引(普通索引，主键索引，唯一索引，全文索引->sphinx(coreseek),空间索引)
  4. 对my.ini配置优化[最大连接数，各级缓存]
  5. 创建适当存储过程，触发器，视图
  6. 硬件升级

1)
设计表要符合3NF

3NF:我们设计表要符合一定的规范，分为1NF-6NF，我们PHP网站满足3NF，范式规范是递进的，首先要满足1NF,才能满足2NF,一次类推

1NF:即表的属性(列)具有原子性，不可再分割，这里说的不可分割指的是列名的含义是准确的，不可分割。（只要是关系型数据库就自然的满足1NF）
关系型数据库：mysql/oracle/sqlserver/sybase/postgresql/informix/DB2
非关系型数据库：面向对象或者集合
面向文档的数据库：MongDB

2NF:保证表的数据具有唯一性，通常情况下是设计主键，这个主键一般是自增长的
3NF:表中没有冗余数据，我们表中的数据如果可以显式的推导或者隐式的推导，就不要直接写记录

二）优化sql语句

看几个指令

show shatus [查看当前mysql的配置信息，和运行状态]

必须知道这几个常用的信息含义

show [global|session] status like ‘%COM_xx’;【xx可以是select,update,delete,insert,这些值作为你判断数据库负载和执行各种sql频率的标准】

global:表示从数据库启动到现在
session:表示当前控制台，默认就是session

Connections:视图连接mysql服务器的次数
uptime:服务器工作的事件(单位秒)
Slow_queries:慢查询的次数(默认是慢查询时间10s)

如何定位慢查询

什么是一个慢查询：就是mysql管理员，定了一个时间，比如10秒，如果一个查询时间超过10s,则这就是一个慢查询，这个10秒，是可以自定义，默认就是10s

show status like ‘slow_queries’;”

我们构建一个大表 400w

一个使用蠕虫复制->测试效果比较差
insert into user values(1,’aaa’);插入一条后
insert into user select id,name from user;插入全部。然后插入。翻倍写入
(#模拟一个雇员管理系统
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 ;

创建表EMP雇员

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);

定义一个新的命令结束符合,为了创建存储过程不冲突

delimiter $$

这里创建一个函数

rand_string(n INT) 函数名 (n INT)参数

create function rand_string(n INT)
return varchar(255)#该函数会返回一个字符串
begin #开始

声明一个变量 ，类型是varchar 名字是char_str ,默认值是’abcdefghijkl,nopqstruvwxyzABCDEFJHKNMOPQRSTUVWXYZ’;

declare chars_str varchar(100) default
‘abcdefghijklknopqstruvwxyzABCDEFJHKNMOPQRSTUVWXYxcsd’;
declare return_str varchar(255) default ”;
declare i int default 0;
while i

这里又自定义了一个函数，返回一个随机的部门号

create function rand_num()
returns int(5)
begin
declare i int default 0;
set i=floor(10+rand()*500);
return i;
end$$

向emp表中插入记录(海量的数据)

随即添加雇员[光标] 400w,Mysql开发中，可以在存储过程中调用你自己

编写的函数

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 $$

delimiter ;

调用刚刚写好的函数, 1800000条记录,从100001号开始

call insert_emp(100001,4000000);

查看默认慢查询时间

show variables like ‘long_query_time’$$询时间
set long_query_time=0.8;//修改为0.8秒

这时慢查询的次数可以记录。但是，日志没有记录，在默认情况下,mysql不记录慢查询

③在启动mysql的时候，就指定记录慢查询
cmd>bin\mysql.exe –safe-mode –slow-query-log
还有一种方式my.ini配置
[mysqld]
slow-query-log

④看看是否能把慢查询记录下来
慢查询日志，存放的地方，取决my.ini文件中指定的一个配置
datadir=”D:/phpStudy/MySQL/data/”

当有慢查询时，就会把慢查询记录到日志中，然后分析并解决

select * from emp where empno=456788;
4百万条记录，没有主键索引，查询要0.84秒
添加主键索引后。。查询为0.0秒，速度上升不是一点点

⑤分析问题
看看表是否创建了索引
desc 表名；
show index from 表名；
show indexes from 表名
show keys from 表名

⑥通过创建索引
创建一个主键索引
alter table 表名 add primary key(列名)

这时通过创建索引，速度提高了
小建议：如果时间紧急，但没有什么好方案。那就添加索引。查那个表就添加那个。

那些列上适合添加索引，

较频繁的作为查询条件字段应该创建索引
select * from emp where empno=1
select * from emp where sex=’男’
更新非常频繁的字段不适合创建索引
select * from emp where logincount=1
不会出现在where字句中字段不该创建索引

索引的创建，修改，删除，查询->如何使用索引

索引注意四类(primary,unique,index,fulltext)

主键索引

创建主键索引
1.在创建表的时候，指定某列为主键，那么这列就是一个主键索引
create table aaa(id int unsigned primary key auto_increment,name varchar(255));
2.在创建表后，然后指定某列或者某几列为主键索引
create table bbb
(id int unsigned,name varchar(255) not null default ”);
ALTER TABLE bbb ADD PRIMARY KEY(id);

主键索引最多只能有一个
主键索引快是因为它不可能重复，找到一个就行了
普通索引就因为可能重复

创建唯一索引

1.在创建表的时候，直接指定某列，或者某几列为唯一索引，当你把某个字段设置为unique的时候，该列就自然是唯一索引
create table ccc
(id int unsigned primary key,
emial varchar(64) unique,
name varchar(255) not null default ”);

2.创建表后，在指定某列唯一索引
create table ddd
(id int unsigned primary key,
emial varchar(64)
);
//两个方法
1.create unique index uni_email ON ddd (emial);
2.alter table ddd add unique index uni_email(emial)

创建普通索引

1.一般是创建表后，再使用指令来创建索引
create table eee
(id int unsigned primary key,
email varchar(64),
name varchar(255) not null default ”);

//两个方法
1.create index ind_email ON eee (email);
2.alter table eee add index ind_email(email)

说明一下主键索引和唯一索引的区别
1.主键索引不能为空，也不能重复
2.唯一索引可以为空，多个NULL也可以，但是如果有值,就不能重复

全文索引

一句话：主要用处是快速检索文章的关键字，在默认情况下，mysql提供的全文索引支持对英文文章的检索->如果文章中含有中文，则需要学习sphinx(coreseek)

我们先体验一下mysql的自带的fulltext

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

INSERT INTO articles (title,body) VALUES
(‘MySQL Tutorial’,’DBMS stands for DataBase …’),
(‘How To Use MySQL Well’,’After you went through a …’),
(‘Optimizing MySQL’,’In this tutorial we will show …’),
(‘1001 MySQL Tricks’,’1. Never run mysqld as root. 2. …’),
(‘MySQL vs. YourSQL’,’In the following database comparison …’),
(‘MySQL Security’,’When configured properly, MySQL …’);

—-如何使用
SELECT * FROM articles WHERE title like ‘%MySQL%’；X错的
–这样使用
SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘database’);

提出一个关于全文索引的匹配度的概念，
select MATCH (title,body) AGAINST (‘database’) from articles;
这里写图片描述

看出，全文索引是以匹配度的高低来决定是否把这条记录检索出来，全文索引对常用词是不会建立索引，我们把这些常用词称为停止词

explain 检测索引

explain 加查询语句。能分析出有没有使用索引

explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘database’);

删除索引
drop index uni_email on ddd;

小技巧

技巧1：对于创建多列索引，只要查询条件使用了最左的列，索引一般就会被使用
技巧2：对于使用Like的查询，查询如果是 ‘%aaa’不会使用索引’aaa%’hu会使用到索引，查询内容的左边，使用%就不会使用到索引
技巧3：如果我们在使用group by时候，我们只希望分租，不希望排序，这可以使用order by null 进行控制
explain select * from dept group by dname order by null\G
技巧4：有些情况下，可以使用连接来替代子查询,因为使用join，MySQL不需要在内存中创建临时表
技巧5：使用OR语句，请保证个哥条件都是索引->自连接，sql语句，

如何选择合适的储存引擎？

  • Myisam:默认是mysql存储引擎，如果应用在以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性要求不是很高，其优势是访问速度快
  • InnoDB：提供了具有提交，回滚和奔溃恢复能力的事务安全，但是对比myISAM，写的处理效率差一些并且会占用更多的磁盘空间
  • Memory:[一些访问频繁，变化频繁，又没有必要写入库的数据：比如在线状态]，存储引擎是memory的话，重启mysql数据没有了

定时对myisam的数据表，进行碎片整理

  1. 使用指令： optimize table 表名;

下列的表将不使用索引：

  • 1，如果条件中有or,即使其中有条件带索引也不会使用索引，只有全部条件都带索引，才会使用索引
  • 2.对于所列索引，不是使用的第一部分，则不会使用索引
  • 3.Like查询是以%开头的，
  • 4.如果字段类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引，(添加时，字符串必须”)
  • 5.如果mysql估计使用全表扫毛要比使用索引快，则不使用索引(当表只有一条记录的时候)

如何衡量你的索引的效率是否高？

  • show status like ‘Handler_read%’;
  • Handler_read_key 高，证明好
  • Handler_read_rnd_next 高，证明效率低

对mysql管理员的一些建议

  • 大批量插入数据(Mysql管理员)了解
  • 对于MyiSAM:
  • alter table table_name disable keys;
  • loading data//insert语句
  • alter table table_name enable keys;
  • 对于Innodb:
  • 1,将要导入的数据安装主键排序
  • 2,set unique_checks=0,关闭唯一性效验
  • 3,set autocommit=0,关闭自动提交

请考虑如果我们存放的日期返回超过了2038年怎么办？

  • 1.php int范围是由运行决定，所以在高位的系统上，表示的范围超过2038
  • 2.mysql中存放日期类型
 safe-mode（安全模式）
    在php中，有一些函数，当启动safe_mode模式后，会去检查执行者，是否有响应的权限。
    那些函数(创建文件，删除文件，创建目录，删除目录，修改文件的所有者，所在组等)
    mkdir rmdir  chgrp chown 在linux或unix下会受影响
    提示：将来我们创建session，一定把创建session目录，约束到网站的某个目录下
    #session保存路径
    $sessSavePath=DEDEATA."/sessions";
    if(is_writeable($sessSavePath)&&is_readable($sessSavePath))
    {
        session_save_path($sessSavePath);
    }

适当的调整my.ini 的配置参数:

最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大
　　innodb_additional_mem_pool_size = 64M
　　innodb_buffer_pool_size =1G
对于myisam，需要调整key_buffer_size
当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数
在my.ini修改端口3306，默认存储引擎和最大连接数

最大连接数: max_connections=100 调整为1000

slowdown instead of a performance improvement.

query_cache_size=100M

怎么实现:

<?php
    $sql=”select * from ....”;

//连接mem
if($arr=mem->get(md5($sql))){

//我直接从mem中来处理结果，不到数据库.
}else{

$res=mysql_query();==>$arry=>memcache [key 可以使用$key=md5($sql)  
$mem->add($key,$arr);
] 
}



<javascrip>

// ajax md5(url)->拷贝过来.

</javacrpt>
mysql自定义函数默认值_Mysql自定义函数（function）
weixin_39999222的博客
01-27 1007
语法自定义函数也需要相应的要求，语法如下:CREATE FUNCTION(参数列表)RETURNS返回值类型函数体删除:DROPFUNCTION调用自定义函数语法:SELECT(parameter_value,...)语法实例create function rand_string(n int) returns varchar(255)begindeclare chars_str varchar...
mysql for_MySQL中的For循环示例
weixin_42297846的博客
01-18 3123
MySQL中的while循环语法示例：delimiter //CREATE procedure yourdatabase.while_example()wholeblock:BEGINdeclare str VARCHAR(255) default '';declare x INT default 0;SET x = 1;WHILE x <= 5 DOSET str = CONCAT(str...
sqlserver存储过程调试方法
KnuthZ的博客
06-23 1万+
declare @xx varchar(max);declare @where varchar(100);declare @order varchar(100);declare @page int;declare @row int;set @page = 1;set @row =10;set @where='1=1';set @order='order by id';exec dbo.p_yw_g...
mysql自定义函数random_str,产生随机字符串
06-03 6408
delimiter $$ CREATE FUNCTION random_str(n int) RETURNS varchar(255) begin declare chars_str varchar(100) default "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"; declare return
mysql优化笔记+资料
11-18
以下是一份详细的MySQL优化笔记，涵盖了多个方面： 一、查询优化 1. 使用索引：为经常用于搜索的列创建索引可以显著加快查询速度。B树和哈希索引是最常见的类型，适用于不同的查询场景。 2. 避免全表扫描：尽量使用...
MySQL优化方案参考
12-15
MySQL优化是一个涵盖多个层面的复杂任务，涉及到硬件配置、操作系统设置以及数据库本身的调整。这篇文章将主要探讨硬件层、系统层和MySQL层的优化方案。 在硬件层，优化首先从CPU开始。为了最大化CPU性能，应调整...
MYSQL.rar_MYSQL_mysql 优化_mysql优化_mysql备份_sql优化
09-14
一、MySQL优化 1. 结构优化：这涉及到数据库设计，如合理创建索引、避免全表扫描、选择合适的数据类型，以及规范化与反规范化设计等。正确地设置主键和外键，可以提高数据一致性并优化查询速度。 2. 查询优化：编写...
mysql优化笔记
08-13 362
Mysql数据库的优化技术 对mysql优化是一个综合性的技术，主要包括 a: 表的设计合理化(符合3NF) b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引] c: 分表技术(水平分割、垂直分割) d: 读写[写: update/delete/add]分离 e: 存储过程 [模块化编程，可以提高速度] f: 对mysql配置...
mysql快速生成百万条测试数据的方法
热门推荐
oahz4699092zhao的专栏
11-25 2万+
转自：mysql快速生成百万条测试数据的方法 — 没那么简单的博客 有时候我们需要对大数据进行测试，本地一般没有那么多数据，就需要我们自己生成一些。下面会借助内存表的特点进行生成百万条测试数据。创建一个临时内存表, 做数据插入的时候会比较快些 -- 创建一个临时内存表 DROP TABLE IF EXISTS `vote_record_memory`; CREATE TABLE `vote_re
mysql自动推最新数据_mysql自动生成大量数据
weixin_33211213的博客
01-21 140
目录mysql自动生成大量数据为了学习验证高性能mysql，自动生成大量的数据做测试。内容来源于网络。创建随机数字生成delimiter $$create definer=`root`@`%` function `random_num`( ) returns int(5)begindeclare i int default 0;set i = floor(100+rand()*10);return...
给数据库建索引的规则
殇沫流年的专栏
11-24 1981
给数据库建索引的规则 建立索引常用的规则如下： 1、表的主键、外键必须有索引； 2、数据量超过300的表应该有索引； 3、经常与其他表进行连接的表，在连接字段上应该建立索引； 4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 5、索引应该建在选择性高的字段上； 6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不
[MySQL优化案例]系列 -- DISABLE/ENABLE KEYS的作用
weixin_33816946的博客
08-16 333
作/译者：叶金荣（Email: ），来源：http://imysql.cn，转载请注明作/译者和出处，并且不能用于商业用途，违者必究。 有一个表 tbl1 的结构如下:CREATE TABLE `tbl1` ( `id` int(10) unsigned NOT NULL auto_increment, `name` char(20) NOT NULL default '', ...
MySQL必知必会——第24章 使用游标 读书笔记
weixin_43854189的博客
01-20 401
本章讲授了什么是游标，如何使用游标。 1 什么是游标 游标只用于MySQL5.0及以后的版本。 MySQL检索操作返回一组称为结果集的行。这组返回的行都是与SQL语句相匹配的行（零行或者多行）。 使用简单的select语句没有办法得到第一行、下一行或前10行，也不存在每次一行地处理所有行的简单方法（相对于成批地处理它们）。 有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。 游...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值