对于mysql机能优化题目的整理

A、表的是设计合理化(相符 3范式)

B、增加恰当的索引(index)[四种：一般索引，主键索引，独一索引，unique，全文索引]

C、分表技术(水平分割，垂直分割)

D、读写[写：update/delete/add]别离

E、储备历程[模块化编程，可以提高速度]

F、对mysql配置优化[配置最大并发数，my.ini调整缓存大小]

G、Mysql效劳器举荐升级

H、按时的去分明不需要的数据，按时停止碎片整理

引荐Mysql相关视频教程：https://www.php.cn/course/list/51/type/2.html

1、数据库表的设计

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

第二范式：2NF是对记载的唯一性束缚，要求记载有唯一标识，即实体的唯一性；

第三范式：3NF是对字段冗余性的束缚，它要求字段没有冗余。没有冗余的数据库设计可以做到。

2、sql优化的一样步骤

操纵步骤:

1、通过show status命令理解各种SQL的施行频率。

2、 定位施行效力较低的SQL语句-（重点select）

3、 通过explain剖析低效力的SQL语句的施行状况

4、肯定问题并采取响应的优化办法

MySQL通过使用show [session|global] status 命令可以供给效劳器状态信息。

session来表示当前的连接的统计结果，global来表示自数据库上次启动至今的统计结果。默许是session级别的。

show status like ‘Com_%’;

其中Com_XXX表示XXX语句所施行的次数。Eg:Com_insert,Com_Select…
重点留意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数，可以容易地理解到当前数据库的利用是以插入更新为主还是以查询操纵为主，乃至各类的SQL大致的施行比例是多少。
Connections：试图连接MySQL效劳器的次数
Uptime：效劳器工作的时间（单位秒）
Slow_queries：慢查询的次数 (默许是慢查询时间10s)

Show status like‘Handler_read%’使用查询的次数

定位慢查询：

在默许的状况下mysql是不记载满查询日志的，需要在启动的时候指定

\bin\mysqld.exe- -safe-mode – slow-query-log[mysql5.5可以在my.ini中指定]

\bin\mysqld.exe- -log-slow-queries=d:bac.log

详细操纵如下：

假如启用了慢查询，默许储备在mysql.ini文件的此处

1、重新启动mysql,寻到datadir的路劲，使用cmd进入到data的上级名目

2、运转命令\bin\mysqld.exe –safe-mode –slow-query-log(留意施行前先关闭mysql效劳)

3、生成的日志文件记载着所有的记载信息

显示慢查询的时间：Show variables like ‘long_query_time’;

从新设定满查询的时间：Set long_query_time=2;

修改命令完毕符：(为了储备历程能够正常施行，我们需要把命令完毕符号停止修修改)

Delimiter $$

怎样把慢查询的sql语句记载到我们的日志中(默许状况下mysql是不会记载的，需要在启动mysql的时候，指定慢查询的)。

3、索引

?索引的类型：

★四种索引①主键索引②独一索引③一般索引④全文索引

一、增加

1.1主键索引增加

当把一张表的某列设定为主键的时候，则该列就是主键索引。

Createtable aaa(id int unsigned primary key auto_increment,

name varchar(32) not null default);

1.2一般索引

一样来说，一般索引是先创立表，然后创立一般索引。

比方：

Createindex索引名 from表名

1.3创立全文索引

全文索引，主如果针对文件，比方文章的索引全文索引针对MyISAM有用，针对innodb没有用

Create table articles(

Id int unsignedauto_increment not null primary key,

Title varchar(20),

Body text,

Fulltext (title,body)

)engine=myisam charsetutf8;

错误用途：

Select * from articles where body like ‘%mysql%’[不会使用到全文索引]

证明：

Explain select * from articles body like ‘%mysql%’;

准确的用途：

Select * from article wherematch(title,body)against(‘database’);[可以]

说明：

1、在mysql中fulltest索引值针对myisam生效

2、针对英文生效，àsphinx(coreseek)技术处置中文

3、使用的办法，match(字段名，…)against(‘关键词’)

4、全文索引一个叫休止词。由于在一个文本中，创立索引的是一个无限大的书，因此，对一些常用词和字符就不会创立，这些词，称之为休止词

1.4创立独一索引

当表的某列被指定为unique束缚时，这列就是独一索引

第一种、Create table ddd(id int primary keyauto_increment,name varchar(32) unique);

这时，name默许就是独一索引

第二种、create table eee(id int primary keyauto_increment,name varchar(32));

Createunique index索引名 on表名(列名)

简便的说：PRIMARY KEY=UNIQUE+NOT NULL

Unique字段可认为null，并可以有多个null,但是假如是详细内容，则不克不及反复

主键字段，不克不及为null,也不克不及反复

二、查询

1．Desc表名[该办法的缺陷，不克不及够实际索引名]

2．Show index from表名;

select index from表名\G

3．show keys from表名

三、删除

Altertable 表名 drop index 索引名，

Altertable 表名 drop primary key。(删除主键索引名)

四、修改

先删除，在全部

二、针对SQL编写致使的慢 SQL，优化起来还是相对照较利便的。正如上一节提到的准确的使用索引能加快查询速度，那么我们在编写 SQL 时就需要留意与索引相关的规则：

1.字段类型转换致使不消索引，如字符串类型的不消引号，数字类型的用引号等，这有大概会用不到索指导致全表扫描；

2.mysql 不支撑函数转换，所以字段前面不克不及加函数，不然这将用不到索引；

3.不要在字段前面加减运算；

4.字符串比力长的可以思考索引一部份减少索引文件大小，提高写入效力；

5.like % 在前面用不到索引；

6.按照结合索引的第二个及今后的字段独自查询用不到索引；

7.不要使用 select *；

8.排序请尽量使用升序 ;

9.or 的查询尽量用 union 代替（Innodb）；

10.复合索引高选中性的字段排在前面；

11.order by / groupby 字段包罗在索引傍边减少排序，效力会更高。

除了上述索引使用规则外，SQL 编写时还需要特殊留意一下几点：

1.尽量躲避大事务的 SQL，大事务的 SQL 会影响数据库的并发机能及主从同步；

2.分页语句 limit 的问题；

3.删除表所有记载请用 truncate，不要用 delete；

4.不让 mysql 干余外的事情，如运算；

5.输写 SQL 带字段，以防止后面表变动带来的问题，机能也是比力优的 ( 触及到数据字典解析，请自行查询材料)；

6.在 Innodb上用 select count(*)，由于 Innodb 会储备统计信息；

7.慎用 Oder by rand()。

三、显示慢查询的次数：show status like 'slow_queries';

HEAP是较早的mysql版本

四、Explain剖析低效力的SQL语句：

会发生如下信息：

select_type:表示查询的类型。

table:输出结果集的表

type:表示表的连接类型

possible_keys:表示查询时，大概使用的索引

key:表示实际使用的索引

key_len:索引字段的长度

rows:扫描出的行数(预算的行数)

Extra:施行状况的描写和说明

Select_type类型：

primary : 子查询中最外层查询

subquery : 子查询内层第一个select，结果不依靠于外部查询

dependent subquery : 子查询内层第一个select，依靠于外部查询

union:union语句中第二个select开端后面所有select

simple: 简便模式

union result: union中合并结果

type 类型:

all: 完全的表扫描 平常不好

system : 表仅有一行(=系统表) 这是const联接类型的一个特例

const : 表最多有一个匹配行

extra 类型:

no table: query语句中使用 from dual 或不含任何from子句

Using filesort : 当query中包括 order by 操纵，并且没法利用索引完成排序

impossible WHERE noticed after readingconst tables:Mysql query optimizer

通过收集统计信息不成能存在结果

Using temporary : 某些操纵必需使用暂时表，常见 group by ,order by

Using where: 不消读取表中所有信息，仅通过索引就可以猎取所需数据

4、为什么使用了索引后查询速度会变快

一般的查询假如没有索引，他会不断去施行，及时匹配到了还要连续查询，不克不及包管后面有没有要查询的。要全文索引。

■索引使用的留意事项

索引的代价：

1、占用磁盘空间

2、对DML(insert,update,create)操纵有影响，变慢

■总结：知足以下前提，才应当创立索引

A、必定在where经常使用

B、该字段的内容不是独一的几个值(sex)

C、字段内容不是频繁转变

■使用索引的留意事项：

alter table dept add index myind (dname,loc); // dname就是左边的列，loc是右侧的列

以下状况有大概使用到索引

a.关于创立的多列索引，只要查询前提使用了最左边的列，索引一样就会被使用 explain select * from dept where dname='aaa';

b.关于使用like的查询，查询前提假如是'%aaa'则不会使用到索引，'aaa%'会使用到索引

以下状况不会使用索引 ：

a.假如前提中有or，即便其中有前提带索引也不会使用换言之，就是要求使用的所有字段都创立索引，倡议：尽量幸免使用or关键字

b.关于多列索引，不是使用的第一部分，则不会使用索引

explain select * from dept where loc='aaa';// 多列索引时，loc为右侧列，索引不会使用到

c.like查询是以%开头假如必然要使用，则使用全文索引去查询

d.假如列类型是字符串，那必然要在前提中将数据使用引号引发来，不然不使用索引

e.假如MySQL估量使用全表扫描要比使用索引块，则不使用索引

怎样选中mysql的储备引擎
1：myISAM

假如表对事务的要求不高，同事一查询和增加为主的，

比方BBS中的发帖，回帖。

2：InnoDB

对事务的要求高，留存的数据都是重要数据，

比方订单，账户表

3：Memory:

数据转变频繁，不需要入库同时又进场查询和修改。

myISAM和InnoDB的不同：

1、myISAM大量插入快，InnoDB插入慢，myISAM插入时候不排序。

2、InnoDB支撑事务，myISAM不支撑事务。

3、MyISAM支撑全文索引，

4、锁机制，myISAM是表锁，InnoDB是行锁

5、myISAM不支撑外键，InnoDB支撑外健

① 在进度要求高的利用中，倡议使用定点数据来储备数值，组U一包管数据的准确性，deciaml进度比float高，尽量使用

② 关于储备引擎的myISAM的数据库，假如进场要走删除和修改的操纵，要按时施行optimize_table_name功效对表停止碎片整理。

③ 日期类型要按照实际需要选中援用的最小储备的早期类型，

手动备份数据库：

1、进入cmd

2、Mysqldump –uroot –proot数据库【表名1,表名2…】 > 文件途径

Eg: mysqldump -uroot -proot temp > d:/temp.bak

复原备份文件数据:

Source d:/temp.bak（在mysql操纵台）

合理的硬件资源和操纵系统

Master

Slave1

Slave2

Slave3

主库master用来写入，slave1—slave3都用来做select，每个数据库

分担的压力小了许多。

要实现这种方式，需要程序特殊设计，写都操纵master，读都操纵

slave，给程序开发带来了额外肩负。当然当前已经有中心件来实现这个

代理，对程序来读写哪些数据库是透亮的。官方有个mysql-proxy，但是

还是alpha版本的。新浪有个amobe for mysql，也可到达这个目的，构造

如下：

5、表的分割

水平分割：

大数据量的表,我们在供给检索的时候，应当按照业务的需求，寻到表的标准，并在检索页面束缚会员的检索方式，并且要配合分页，

案例：大数据量的会员表

三张表：qqlogin0,qqlogin1,qqlogin2

将会员id%3,按结果放入不一样的表傍边

create tableqqlogin0(

id int unsigned not null primary key,/* 这个id不克不及设定自增长 */

name varchar（32）not null default''，

pwd varchar（32）not null default''

）engine = myisam default charset = utf8;

创立表qqlogin1（

id int unsigned not null主键，/ *这个id不克不及设定自增长* /

name varchar（32）not null default''，

pwd varchar（32）not null default''

）engine = myisam default charset = utf8;

创立表qqlogin2（

id int unsigned not null主键，/ *这个id不克不及设定自增长* /

name varchar（32）not null default''，

pwd varchar（32）not null default''

）engine = myisam default charset = utf8;

垂直分割：

把某个表的某些字段，这些字段，在查询时候并不关系，但是数据量很大，我们倡议将这些字段放到一个表中，从而提高效力

6、优化的mysql的配置

MY.INI

port = 3306默许端口是3306，

假如想修改端口port = 3309，在mysql_connect（'localhost：3309'，'root'，'root'）;要留意

query_cache_size = 15M这个是查询缓存的大小

InnoDB的参数也可以调大以下两个参数

innodb_additional_mem_pool_size = 64M

innodb_buffer_pool_size = 1G

myisam需要调整key_buffer_size

调整参数还要看状态，用show status可以看到当前状态，以决议该调整哪些参数

7、增量备份

实际案例：

怎样停止增量备份，和复原

步骤：

如图1所示，配置的my.ini文件或者是my.cof，启用二进制备份

2，从新启动的MySQL

启动之后会发明mylog名目下生成了一下文件

其中：E：\二进制日志\ mylog.index索引文件，是什么备份文件

E：\二进制日志\ mylog.000001存置会员对象数据库操纵的文件

3，当我们停止操纵的时候（选中）

查看需要进入到MySQL的的安置名目下的bin中，然后施行mysqlbinlog可以文件，后面追加文件途径

如图4所示，复原到某个语句的时间点

4,1依照时间点回复

Mysqlbinlog -stop-datetime =“2013-01-17 12:00:23”d：/binlog/mylog.000001 | mysq -uroot -p

（复原到休止时间此前的所有数据）

Mysqlbinlog-start-datetime =“2013-01-17 12:00:23”d：/binlog/mylog.000001 | mysq -uroot -p

（复原开端时间到之后的所有数据）

4,2依照位置复原

Mysqlbinlog-stop-position =“234”d：/binlog/mylog.000001 | mysq -uroot -p

（复原到休止时间此前的所有数据）

Mysqlbinlog-start-position =“234”d：/binlog/mylog.000001 | mysq -uroot -p

（复原开端时间到之后的所有数据）

更多相关问题，请拜访PHP中文网：https://www.php.cn/

以上就是关于mysql机能优化问题的整理的具体内容，更多请关注百分百源码网其它相关文章！