Mysql优化

置顶 zhoupenghui168

于 2023-03-29 10:44:04 发布

阅读量467

点赞数 1

分类专栏： # MySQL 数据库文章标签： mysql 数据库 Powered by 金山文档

本文链接：https://blog.csdn.net/zhoupenghui168/article/details/129821303

版权

数据库同时被 2 个专栏收录

34 篇文章 3 订阅

订阅专栏

MySQL

28 篇文章 1 订阅

订阅专栏

一.表的设计满足3NF

目前我们的表的设计，最高级别的范式是"6NF",对程序员而言，我们的表满足3NF即可

所谓1NF,就是指表的属性(列)具有原子性，即表的列的不能再分了，比如：学生表

所谓2NF,就是指我们的表中不能有完全重复的一条记录（行），一般情况下通过设置一个主键来搞定，而且该主键是自增的

所谓3NF就是指，如果列的内容可以推导(显示推导，隐私推导)出，那么我们就不要单独的用一列存放，比如：下面的就是满足3NF的

反3NF

在通常情况下，我们的表的设计要严格的遵守3NF,但也有例外，反而会提高查询的效率，比如：

二.索引优化

1.为什么要创建索引

这是因为，创建索引可以大大提高系统的性能
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

2.索引的特征

索引有两个特征: 唯一性索引和复合索引

2.1唯一性索引

保证在索引列中的全部数据是唯一的，不会包含冗余数据。如果表中已经有一个主键约束或者唯一性键约束，那么当创建表或者修改表时，SQL Server自动创建一个唯一性索引。然而，如果必须保证唯一性，那么应该创建主键约束或者唯一性键约束，而不是创建一个唯一性索引。当创建唯一性索引时，应该认真考虑这些规则：当在表中创建主键约束或者唯一性键约束时，SQL Server自动创建一个唯一性索引；如果表中已经包含有数据，那么当创建索引时，SQL Server检查表中已有数据的冗余性；每当使用插入语句插入数据或者使用修改语句修改数据时，SQL Server检查数据的冗余性：如果有冗余值，那么SQL Server取消该语句的执行，并且返回一个错误消息；确保表中的每一行数据都有一个唯一值，这样可以确保每一个实体都可以唯一确认；只能在可以保证实体完整性的列上创建唯一性索引，例如，不能在人事表中的姓名列上创建唯一性索引，因为人们可以有相同的姓名。

2.2复合索引

就是一个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合索引。当创建复合索引时，应该考虑这些规则：最多可以把16个列合并成一个单独的复合索引，构成复合索引的列的总长度不能超过900字节，也就是说复合列的长度不能太长；在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列；在复合索引中，列的排列顺序是非常重要的，因此要认真排列列的顺序，原则上，应该首先定义最唯一的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；为了使查询优化器使用复合索引，查询语句中的WHERE子句必须参考复合索引中第一个列；当表中有多个关键列时，复合索引是非常有用的；使用复合索引可以提高查询性能，减少在一个表中所创建的索引数量

3. 索引的功能

3.1 分解功能

把文献中的资料单元(如篇名、机构、短语、概念、物名、地名、书名、人名、字词、符号等)一一分解，这就是索引的分解功能。它是索引工作的起跑线和索引编纂的基础，没有对文献内容的这种分解功能，就没有索引。

过去有些反对索引的人说，索引是把古人的著书“凌迟碎割”。他们对索引法的反对，实出于对流传已久的那种落后的皓首穷经的陋习的偏爱和对新的治学方法的无知，洪业曾鄙视他们为卧于涸辙的鲋鱼，以升斗之水济命，而不知西江水之可羡。虽然如此，但他们所谓的索引是把古人著书“凌迟碎割”的形象说法，却从反面十分正确地道破了索引的分解功能。

分解功能是索引作用于文献的特殊功能，是它和其他检索工作不同之处。

3.2 梳理功能

每种文献都包容着许多不同性质的资料单元，它们在文献中基本呈无序的状态。把这些无序状态的资料单元按外表特征或内容性质进行各归其类的整理，这就是索引的梳理功能。章学诚早就发现了这种功能，他在给《族孙守一论史表》信中要求其在治二十四史年表时一并把廿二史列传中的人名编成索引，两者互为经纬，这样便可使考古之士，于纷如乱丝之资料中，忽得梳通栉理。

梳理功能是索引分解的后继。如果只有分解功能而没有梳理的功能，那么分解功能就没有价值。

梳理是对资料单元的初分。如是字序，只要按笔划或音序归类即可；如是类序只要按大类归纳即可。就像小姑娘梳头，先把长发梳顺，而编什么辫子或梳什么发型则是下一步的要求了。

3.3 组合功能

把梳理后的资料单元按照分类的要求，严密地组织它们的类别层次以及类目下的专题和同类目下款目的序列关系；或按字序的要求，严密地把标目的结构正装或倒装、考虑限定词对标目的限定和修饰的级数、或考虑字序和类序相结合的可能。此外，不论是类序或字序都要考虑参照系统的建立方案，使相关款目形成网络，使用户检索的眼界得以拓宽。这些，都是索引的组合功能。

过去，国外的同行曾把圣经的页边索引以“串珠”命名；我国有人曾把本草的方剂编成索引，以“针线”命名，“串珠”和“针线”是索引组合功能很形象的描绘。它使文献资料单元成为一串串的明珠，成为被针线贯穿起来的资料单元的珍品。

3.4 结网功能

对某个领域的文献进行有计划的索引编纂，利用类型的结构从各种不同的角度和层次对这些文献的内容进行纵横交错和多维的揭示和组合，使之形成一个检索这些文献中的各种不同性质的资料单元的网络。这就是索引的结网功能。

由“主表”和“词族索引”、“范畴索引”、“英汉对照索引”等所组成的《汉语主题词表》是由几种不同性质的索引构建的一个主题词间的联系、辨析主题词词义和被标引的文献主题概念是否精确的一个隐含的语义网络，它对文献中的资料单元产生族性检索和扩大检索途径的作用。这个网络的结构和作用就是运用索引结网功能的一个范例。

《古今图书集成》囊括了清初以前绝大部分的文献，是我国现存最大的一部类书，广西大学林仲湘等根据它的特点，分别编了经线索引、纬线索引、图表索引、人物传记索引、职方典汇考引和禽虫草木三典释名索引，从该书的各级类目和内容等不同角度交叉编结了一个严密的揭示网络，这又是索引结网功能的具体运用的例证。

3.5 揭示功能

分解功能只是客观地对文献进行解剖，而揭示功能有较强的目的性，它最常出现的是按专业的需要挑选某些资料单元。

在主题索引的编制中，主题概念的分析和主题词的选用，包括对隐性主题的析出是这种功能的体现。

各种专题索引或各种专业的文摘索引也是索引揭示功能的运用。清朝徐克祺称赞蔡烈先的《本草万方针线》为镜考、为烛照，这是对索引揭示功能很生动的形容。

3.6 鉴别功能

文献是作者思维的物化，包含了作者用以施展其聪明才智的特有的言语轨迹及其思想气质。在文献的出版史中，出于各种不同的动机和原因，真伪之作叠出，为杜伪辨真，不少人引经据典，注疏考证，一字之微，动千万言，甚至引发了校勘学的出现。近年来又产生了利用索引进行鉴别的方法，通过逐字索引分解被鉴别的文献，即同时从两种版本中分析作者用字(词)的规律和频率以甄别其真伪。

英国的女研究学家凯洛莱因·斯彼琴曾对莎士比亚的剧作进行过有趣的用词统计和分析，她发现，莎氏使用的许多诗意的语言都和大自然以及乡村生活有关。又有人在研究司马迁的《史记·货殖列传》时，发现这篇不到5000字的文章竟使用49次“富”字，由此得出，致“富”是这篇论文的主题。这些事例证明通过对文献中用词的解剖可以发现作者的特有气质和语言习惯，索引可利用这种潜在的规律辨析文献的真伪。

3.7 追踪功能

索引像只嗅觉灵敏的警犬一样能追踪各种领域的事物在文献中留下来的痕迹。

一部旧时代报纸的索引，能追踪那个时代的重大历史事件，并清晰地再现这些重大的历史事件脉络；一部类书的索引，能追踪许多名人的各方面的成就和言行；一种专业期刊的刊后索引，能追踪当年该专业学科研究的动态；一部跨多年度的报刊回溯索引，就是一部多功能的追踪记录。

追踪，是索引的功能之一。

3.8 导航功能

导航就是指引，它带有较强的评价取向，索引具有这种功能。

过去，我国有一些所谓新产品，在技术人员的积极努力下问市了，但到国际市场上却被打回票，因为这种新产品在别的国家已成老产品。技术人员的情报鼻子不灵，其中原因之一是不查阅有关的索引，不依靠这个情报导航工具，以致迷失了生产的方向。

在哲学社会科学的领域中，索引经常能提供在某个时期某个专业的理论动向和水平的第一手材料，许多科研工作者就依靠索引的导航，找到自己出发的港湾和要达到的彼岸。

洪业在《引得说》中指出：“引得者，予学者以游翔于载籍之舟车也。舟车愈善，则其所游愈广，所入愈深。”这就是索引的导航功能。

3.9 执简功能

您感到在信息爆炸的文献环境中很难伸展自己的科研手脚吧?那么，请您先为自己的研究对象编编索引，那就可以执简御繁了。我国的史学家顾颉刚曾说：“我常想暂不办学术研究所而先办材料整理所。”“索引，也是研究的基础的一种，它能给您一个钥匙，使您在许多头绪不清的材料中找出头绪，而得到你所需要的东西。”

国外盛行的那种快速反映科研情报的KWIC、KWOC，就是发挥索引执简功能的工具，把众多科研期刊中的关键词以最简要的格式显现，使用户能享受“索引在手，千刊掌握”的好处，而避免花去一半的科研时间在众多的资料堆中苦苦求索。因而，不少科研工作者赞誉索引有“天增岁月人增寿”的妙用。

3.10 检索功能

索引诞生在文献检索困难的历史背景里，它的检索功能随索引的诞生同步而生。在近二千年的索引发展史中，检索功能的变化和发展就是它的主要内容。所以，检索功能是索引最基本的功能。

任何文献记录或工作记录一旦和索引方法挂上钩，它就会从山穷水尽疑无路的困境中解脱出来，很快得到检索方便的好处，机关或企业中人事档案和工资单的人员的索引就是如此。又如，最令旅游者头疼的是，到了一个旅游点，买了地图，但要寻找一个地区、一条街道或里弄，那真像大海捞针一样的困难。当这幅地图加上了地名索引后，所要之地名便一检即得了。

3.11 预测功能

索引中有不少数学现象，如从引文索引中统计到被引作者的论文频率，便基本上能预测到这个专业的学术带头人的出现；从题录索引中统计的课题内容，便可预测到学术界未来研究的动向；从统计索引中出现的各种产品名称的频率，便能预测到市场将出现的热门商品。这些都是索引的预测功能

4.优化引入

为了讨论这个优化，需要构建一个海量表(8000000),而且每条数据不一样，这时需要使用存储过程完成该任务创建海量数据表

4.1数据表如下

#创建表DEPT 
#模拟一个雇员管理系统
#部门表
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);

4.2存储过程如下

#随机产生字符串
#定义一个新的命令结束符合,防止创建存储过程冲突
delimiter $$ 

#删除自定的函数
drop  function  IF EXISTS  rand_string $$
 
#这里创建了一个函数.
#rand_string(n INT) rand_string 是函数名 (n INT) //该函数接收一个整数
#随机返回指定n长度的一个字符串
#把语句结束符改成 $$
delimiter $$

 
#创建一个函数，名字 rand_string，可以随机返回指定的个数字符串
create function rand_string(n INT)
returns varchar(255) #该函数会返回一个字符串
begin
#定义了一个变量 chars_str， 类型  varchar(100)
#默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do
   #concat 函数 : 连接函数mysql函数
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
 return return_str;
end $$
 
 
# 随机产生部门编号
delimiter $$
drop  function IF EXISTS rand_num $$
 
#这里我们又自定了一个函数,返回一个随机的部门号
create function rand_num( )
returns int(5)
begin
 declare i int default 0;
 set i = floor(10+rand()*500);
return i;
end $$ 
 
delimiter ;
select rand_num();

#插入海量数据
#******************************************
#向emp表中插入记录(海量的数据)
 
delimiter $$
drop procedure if exists insert_emp $$
 
#随机添加雇员  max_num条 ,雇员的编号从  start
#start是雇员的开始编号，max_num准备添加多少雇员
 
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit设置成0
#autocommit = 0 含义: 不要自动提交
 set autocommit = 0; #默认不提交sql语句
 repeat
 set i = i + 1;
 #通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
 insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
 until i = max_num
 end repeat;
 #commit整体提交所有sql语句，提高效率
   commit;
end $$
 
delimiter ;
#调用刚刚写好的函数, 4000000条记录,从100001号开始
call insert_emp(100001,8000000);

5.添加索引进行优化

5.1创建索引前，查看data相关数据

一个表(存储引擎是MyISAM),对应三个文件 xx.frm 表结构 xx.MYD 数据文件 xx.MYI 索引文件

给empno段添加主键索引

alter table emp add primary key (empno);

5.2 创建索引后，查看data相关数据

发现xx.MYI 索引文件发生变化

通过测试看效果

5.3 使用慢查询日志定位问题语句

默认慢查询记录功能是关闭的，开启方式如下：在启动时添加参数

查看慢查询

默认慢查询是10秒钟，改为1秒

执行sql语句

获得慢查询日志文件的路径： SHOW VARIABLES LIKE '%slow%'

得知，日志文件记录在itsource-PC-slow.log

5.4 查询和慢查询有关的配置

SHOW VARIABLES LIKE '%slow%'

SHOW VARIABLES LIKE '%query%'

开启慢查询： SET GLOBAL slow_query_log=ON
此方法只对当前连接有效

5.5慢查询也可以在配置文件中的mysqld区段中配置

log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=2

5.6 使用explain工具分析查询语句问题

explain可以使用desc代替

如果rows很长，possible_keys和key都为空就需要优化

三.索引详解

索引分为几大类：
单列索引
一个索引只包含单个列，但一个表中可以有多个单列索引，这里不要搞混淆了
普通索引：
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点
唯一索引：
索引列中的值必须是唯一的，但是允许为空值
主键索引：
是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。
组合索引
在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合
全文索引
全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行

1.主键索引

主键索引的创建有两种形式:在创建表的时候，直接指定某列或者某几列为主键,这时就有主键索引; 添加表后，再指定主键索引

1.1 直接创建主键索引

1.2 先创建表，再指定主键

增加主键

ALTER TABLE 表名 ADD PRIMARY KEY (列名1, 列名2..)

1.3 主键索引的特点

1）一个表最多只能有一个主键

2）一个主键可以指向多列(复合主键)

3）主键索引的效率是最高，因此我们应该给id,一般id是自增

4）主键索引列是不能重复，也不能为null

2.唯一索引

2.1 直接在创建表的时候，指定某列或某几列为唯一索引

2.2 把表创建好后，再指定某列或者某几列为唯一索引

说明: 使用 create unique index 指令，必须指定索引名

 alter table ddd add unique (dd);

说明: 使用alter table 指令，可以指定索引名，也可以不指定

2.3 唯一索引的特点

1) 一张表可以有多个唯一索引

2) 唯一索引不能重复，但是如果你没有指定not null ，唯一索引列可以为null,而且可以有多个

3) 什么时候使用唯一索引，当某列数据不会重复，才能使用

4) 唯一索引效率也很高，可以考虑优先使用

3. 普通索引

3.1把表创建好后，再指定某列或者某几列为索引

3.2 添加普通索引(2种方式)

create index ind ddd (ddd); alter table ddd add index (ddd)

3.3 特点

1) 一张表中可以有多个普通索引，一个普通索引页可以指向多列

2) 普通索引列的数据可以重复

3) 效率相对而言低

4.全文索引

全文索引是针对对文章，汉字，英文的检索，可以快速的检索到文章中的某个关键字

4.1案例

先创建一张表

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

使用全文索引查询数据

说明：要使用mysql默认的全文索引，需要使用 match(字段名..) against('关键字')

4.2 特点

1）mysql默认的全文索引，只对MyISAM存储引擎

2）mysql默认的全文索引，只支持英文

3）停止词: 对于特别普通的字母，不会建立索引

4）匹配度: 全文索引是按一定概率来匹配

4.3 如何解决mysql的全文索引不支持中文的问题

1) 使用mysql的一个中文检索插件 mysqlcft, 具体的看文档.

2) 可以使用专门的中文检索引擎 sphinx 中文版(coreseek)

5.查看索引

ldesc 表名
lshow keys from 表名\G
lshow index from 表名\G
lshow indexes from 表名\G

key的属性说明

(1). 如果Key是空的, 那么该列值的可以重复, 表示该列没有索引, 或者是一个非唯一的复合索引的非前导列

(2). 如果Key是PRI, 那么该列是主键的组成部分

(3). 如果Key是UNI, 那么该列是一个唯一值索引的第一列(前导列),并别不能含有空值(NULL)

(4). 如果Key是MUL, 那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL

如果对于一个列的定义，同时满足上述4种情况的多种，比如一个列既是PRI,又是UNI
那么"desc 表名"的时候，显示的Key值按照优先级来显示 PRI->UNI->MUL
那么此时，显示PRI
一个唯一性索引列可以显示为PRI,并且该列不能含有空值，同时该表没有主键
一个唯一性索引列可以显示为MUL, 如果多列构成了一个唯一性复合索引
因为虽然索引的多列组合是唯一的，比如ID+NAME是唯一的，但是没一个单独的列依然可以有重复的值,只要ID+NAME是唯一的即可

6.索引的修改

6.1 修改

//ALTER  TABLE 表名 DROP INDEX 索引名; 
alter table ddd drop index ddd

6.2 删除

//DROP  INDEX  索引名  ON  表;  
drop index ddd on biaoming

7.添加索引后效率为什么会那么快

查看索引原理

8.索引的创建经验

8.1 较频繁的作为查询条件的字段应该创建索引

select * from emp where empno = 1

8.2 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

select * from emp where sex = '男'

8.3 更新非常频繁的字段不适合创建索引

select * from emp where logincount = 1

8.4 不会出现在where子句中的字段不应该创建索引

8.5 order by group by这种经常用到的字段，也可以使用索引

8.6 test(a,b)复合索引

where a=1
where a=1 and b=2
where b=2用不到

8.7 test(a,b,c)复合索引

对于创建的多列(复合)索引，只有查询条件使用了最左边的列，索引才可能会被使用

test(a,b,c)
where a=1
where a=1 and b=2
where a=1 and b=2 and c=3
where a=1 and c=3
where b=2
where b=2 and c=3

8.8 前置模糊无法使用索引

where name like ‘%san’;前置模糊无法使用索引
where name like ‘zhang%’;可以命中索引

四.sql语句的优化和正确使用索引

为了演示效果，创建一个测试表

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
 
DELIMITER $$
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(10,10);

1.对于创建的多列(复合)索引，只有查询条件使用了最左边的列，索引才可能会被使用

dname是左边的列，因此发现使用到dname,就使用到索引，而下面的sql语句，没有使用到索引

2.前置模糊无法使用索引

于使用like的查询，查询如果是‘%aaa’,‘_啊啊' 不会使用到索引,‘aaa%’会使用到索引

3.如果条件中有or，则要求or的所有字段都必须有索引，否则不能用到索引

因为 deptno 没有索引，所以整个sql语句就没有使用到索引

如果在 deptno上也创建索引，就可以使用到索引了

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

5.如果mysql估计使用全表扫描要比使用索引快，则不使用索引

6.优化group by 语句

默认情况，MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序

举例说明

7.有些情况下，可以使用连接来替代子查询,因为使用join，MySQL不需要在内存中创建临时表

8.索引的使用情况查询

使用下面命令可查看创建的索引使用的好坏

show status like ‘Handler_read%’

注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
handler_read_rnd_next:这个值越高，说明查询越低效

9.管理员在导入大量数据，可以这样提高速度

9.1 对于MyISAM

alter table table_name disable keys;
loading data //insert语句; 
alter table table_name enable keys;

9.2 对于Innodb：

(1).将要导入的数据按照主键排序

(2).set unique_checks=0,关闭唯一性校验

(3).set autocommit=0,关闭自动提交

10.选择合适存储引擎

10.1 MyISAM

默认的MySQL存储引擎,如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性要求不是很高。其优势是访问的速度快。(尤其适合论坛的帖子/信息表/新闻/商品表表)

10.2 InnoDB

提供了具有提交、回滚和崩溃恢复能力的事务安全,但是对比MyISAM，写的处理效率差一些并且会占用更多的磁盘空间(如果对安全要求高，则使用innodb).[账户，积分,余额]

10.3 Memory/heap

一些访问频繁，变化频繁，又没有必要入库的数据：比如用户在线状态

11.数据类型的选择

在精度要求高的应用中，建议使用定点数来存储数值，以保证结果的准确性,可以用decimal 就不要用float；比如：float(10,2) 和 decimal(10,2), decimal 更精准,所以对精度高的列，要使用decimal 类型

12.myisam碎片整理

比如：在users表中有大量数据时，在delete 数据后，发现磁盘空间没有回收，因此需要定时的进行碎片整理

delete from users where id = 2$$
optmize tabel user$$

五.MySQL的分表分区技术

当一个表很大很大时，可以考虑添加索引，当索引都解决不了这个问题时，可以使用分表技术或者分区技术搞定

1.分表

使用QQ用户登录来讲解：有很多分表，用户表前缀qq_memner_xx,当用户注册一个qq号时，系统会通过算法生成一个唯一的uuid，这个uuid就是用户的id，使用用户id % 3(该值可以根据实际情况设定),可以得出表后缀，如：qq_memner_1,然后就在这个表里面新增/查询用户

1.1水平分表

1.2 垂直分表

垂直分表，就是说把表的某个大字段(而且很少查询)，单独的取出，放入到另外一个表，并通过id关联

1.3分表的缺点

sql语句复杂,不好扩展

2.分区

2.1 先创建两张表

-- 创建两张表
-- 分区表
CREATE TABLE part_balance
(
    id int default NULL,
    name varchar(30) default NULL,
    savetime date default NULL
) engine=myisam

PARTITION BY RANGE (year(savetime)) 
(
PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) , 
PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) , 
PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) , 
PARTITION p6 VALUES LESS THAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) , 
PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) , 
PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN MAXVALUE );

-- 普通表
create table no_part_balance
(
    id int default NULL,
    name varchar(30) default NULL,
    savetime date default NULL
) engine=myisam;

2.2 创建存储过程生成数据

- 创建存储过程，添加测试数据
-- 创建一个函数,可以返回随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255) #该函数会返回一个字符串
begin
#定义了一个变量 chars_str， 类型  varchar(100)
#默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do
    # concat 函数 : 连接函数
   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 PROCEDURE add_tab()
begin
    declare v int default 0;
    while v < 800000
    do
       insert into part_balance values (v,rand_string(10),adddate('1995-01-01',(rand(v)*36520) mod 3652));
      set v = v + 1;
    end while;
end$$

-- 执行
delimiter ;

call add_tab();
-- 复制数据到无分区技术的表中
insert into no_part_balance select * from part_balance;

2.3 测试结果

2.4 查询数据本身

2.4.1 使用分区

2.4.2 不使用分区

2.5 优缺点

优点

使用了分区技术，性能有显著提高！

限制

只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列
最大分区数目不能超过1024
如果含有唯一索引或者主键，则分区列必须包含所有的唯一索引或者主键在内
不支持外键
不支持全文索引（fulltext）
按日期进行分区非常适合，因为很多日期函数可以用，但是对于字符串来说合适的分区函数不太多

3.配置文件优化

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

六.MySQL的定时备份

1.定时完成的任务说明

先看一个实际的项目需要：需要完成定时备份某个数据库，或者定时备份数据库的某些表的操作

windows 下每隔1小时，备份一次数据newsdb

windows 每天晚上2:00 备份 testdb 下 dept某一张表

2.Windows下备份

(1)使用mysqldump 手动备份表（备份testdb下的dept表）

恢复数据库或者表命令

(2)可以直接写一个mytask.bat 文件，双击即可备份

在该文件中写入指令

"C:\myenv\mysql5.5.27\bin\mysqldump.exe" -u root -proot testdb dept > c:/dept.bak

(3)可以使用系统计划任务/crontab来自动完成

1).开始->输入“计划任务”-> 点击进入

2).通过配置后

(4).目前可以完成定时备份，但是如果数据备份文件总是被覆盖，所以要修改

方法一

(1).计划任务的配置和前面一样

(2).mytask.bat

"C:\myenv\php5.3.5\php.exe"  -f C:\myenv\apache\htdocs\mytask.php

(3).mytask.php

<?php
date_default_timezone_set('PRC');

$filename = date('YmdHis', time()).'.bak';

$command = "C:\myenv\mysql5.5.27\bin\mysqldump.exe -u root -proot testdb dept > D:/backup/{$filename}";

exec($command);

方法2

cmd下可以通过：

％变量：～指定偏移量，指定截取字符长度％

比如：

%date:~0,4%取得年份

将以下代码存放到一个批处理文件中，然后再计划任务中新建，程序设置为对应的批处理

mysqldump -u root -pusbw testdb dept > D:/backup/testdb-%date:~0,4%-%date:~5,2%-%date:~8,2%.sql

3.linux下备份

3.1 直接执行PHP脚本, 需要在同一个服务器上执行

# crontab -e   
0 * * * * /usr/local/bin/php -f /home/htdocs/phptimer.php

3.2 通过HTTP请求来触发脚本, PHP文件允许不在同一服务器上

# crontab -e
0 * * * * /usr/bin/wget -q -O temp.txt http://www.xxx.com/phptimer.php

上面是通过wget来请求PHP文件, PHP输出会保存在临时文件temp.txt中

3.3 通过curl -o来请求PHP文件

# crontab -e
0 * * * * /usr/bin/curl -o temp.txt http://www.xxx.com/phptimer.php

上面是通过curl -o来请求PHP文件, PHP输出会保存在临时文件temp.txt中

3.4 通过Lynx文本浏览器来请求PHP文件

# crontab -e
0 * * * * lynx -dump http://www.xxx.com/phptimer.php

上面是通过Lynx文本浏览器来请求PHP文件

3.5 通过mysqldump直接备份

#获取日期：
date +%Y-%m-%d
mysqldump -u 用户 --password=用户密码 数据库名 > 数据库名-$(date +%Y%m%d).sql

zhoupenghui168

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql优化

Mysql优化，索引优化，sql语句的优化和正确使用，分区分表基数，mysql的定时备份
复制链接

扫一扫

专栏目录