MySQL优化技巧总结

2018-06-18 01:24:04来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

MySQL优化的几个大方向

① 硬件优化
② 对MySQL配置参数进行优化(my.cnf)此优化需要进行压力测试来进行参数调整
③ SQL语句方面的优化
④ 表方面的优化
 

硬件优化

cpu,内存,硬盘等硬件的优化

 

MySQL参数优化

设置最大连接数
set globle max_connections = 5000;

long_query_time = 2 超过两秒的查询记录下来

log_slow_queries = /data/mysql/slowlog.log

query_cache_size = 64M

 

SQL语句方面的优化

●修改mysql的慢查询

show variables like ‘long_query_time’ ; //可以显示当前慢查询时间

set long_query_time=1 ;//可以修改慢查询时间

详情:https://www.cnblogs.com/luyucheng/p/6265594.html

●使用查询缓存优化查询

大多数的MySQL服务器都开启了查询缓存。这是提高性能最有效的方法之一,而且这是被MySQL引擎处理的。当有很多相同的查询被执行了多次的时候,这些查询结果会被放入一个缓存中,这样后续的相同查询就不用操作而直接访问缓存结果了。
这里最主要的问题是,对于我们程序员来说,这个事情是很容易被忽略的。因为我们某些查询语句会让MySQL不使用缓存,示例如下:
1:SELECT username FROM user WHERE    signup_date >= CURDATE()
2:SELECT username FROM user WHERE    signup_date >= '2014-06-24‘
上面两条SQL语句的差别就是 CURDATE() ,MySQL的查询缓存对这个函数不起作用。所以,像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存,因为这些函数的返回是会不定的易变的。所以,你所需要的就是用一个变量来代替MySQL的函数,从而开启缓存。

●使用EXPLAIN关键字检测查询

●当只要一行数据时使用LIMIT 1

●只去自己需要的column,避免使用SELECT *

●添加索引(主键索引/唯一索引/全文索引/普通索引)

1.添加

1.1主键索引添加

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

1 create table aaa
2 (id int unsigned primary key auto_increment ,
3 name varchar(32) not null defaul '');

这是id 列就是主键索引

如果你创建表时,没有指定主键索引,也可以在创建表后,在添加, 指令:

1 alter table 表名 add primary key (列名);

举例:

1 create table bbb (id int , name varchar(32) not null default ‘’);
2 alter table bbb add primary key (id);

 

1.2普通索引

一般来说,普通索引的创建,是先创建表,然后在创建普通索引

比如:

1 create table ccc(
2 id int unsigned,
3 name varchar(32)
4
1 create index 索引名 on 表 (列1,列名2);

 

1.3创建全文索引

全文索引,主要是针对对文件,文本的检索, 比如文章(5.5版本以后,innodb 支持全文索引)

创建 :

 1 CREATE TABLE articles (
 2 
 3        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
 4 
 5        title VARCHAR(200),
 6 
 7        body TEXT,
 8 
 9        FULLTEXT (title,body)
10 
11      )engine=myisam charset utf8;

 

 1 INSERT INTO articles (title,body) VALUES
 2 
 3      ('MySQL Tutorial','DBMS stands for DataBase ...'),
 4 
 5      ('How To Use MySQL Well','After you went through a ...'),
 6 
 7      ('Optimizing MySQL','In this tutorial we will show ...'),
 8 
 9      ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
10 
11      ('MySQL vs. YourSQL','In the following database comparison ...'),
12 
13      ('MySQL Security','When configured properly, MySQL ...'); 

如何使用全文索引:

错误用法:

select * from articles where body like ‘%mysql%’; 【不会使用到全文索引】

证明:

1 explain  select * from articles where body like%mysql%’ 

正确的用法是:

1 select * from articles where match(title,body) against(‘database’);  

测速匹配度

1 mysql> select id, match(title,body) against('dbms') from articles;

 

常用的词语,匹配度反而很低或者为0(假如常用的单词都要匹配的话,每条数据基本上都会匹配上,那搜索效率就很慢了) 如:

 

MySQL 使用一个非常简单的剖析器来将文本分隔成词。一个“词”是由文字、数据、“'” 和 “_” 组成的任何字符序列。任何在 stopword 列表上出现的,或太短的(3 个字符或更少的)的 “word” 将被忽略。

在集和查询中的每个合适的词根据其在集与查询中的重要性衡量。这样,一个出现在多个文档中的词将有较低的权重(可能甚至有一个零权重),因为在这个特定的集中,它有较低的语义值。否则,如果词是较少的,它将得到一个较高的权重。然后,词的权重将被结合用于计算记录行的相似性。

这样一个技术工作可很好地工作与大的集(实际上,它会小心地与之谐调)。 对于非常小的表,词分类不足以充份地反应它们的语义值,有时这个模式可能产生奇怪的结果。

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

在上面的例子中,搜索词 MySQL 却没有得到任何结果,因为这个词在超过一半的记录行中出现。同样的,它被有效地处理为一个 stopword (即,一个零语义值的词)。这是最理想的行为 -- 一个自然语言的查询不应该从一个 1GB 的表中返回每个次行(second row)。

匹配表中一半记录行的词很少可能找到相关文档。实际上,它可能会发现许多不相关的文档。我们都知道,当我们在互联网上通过搜索引擎试图搜索某些东西时,这会经常发生。因为这个原因,在这个特殊的数据集中,这样的行被设置一个低的语义值。

 详情:http://blog.sina.com.cn/s/blog_63426ff901014l2d.html

全文说明:

  1. 在mysql中fulltext 索引只针对 myisam生效(5.5版本以后,innodb 支持全文索引)
  2. mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文
  3. 使用方法是 match(字段名..) against(‘关键字’)
  4. 全文索引一个 叫 停止词,  因为在一个文本中,创建索引是一个无穷大的数,因此,对一些常用词和字符,就不会创建,这些词,称为停止词.

1.4唯一索引

①当表的某列被指定为unique约束时,这列就是一个唯一索引

1 create table ddd(id int primary key auto_increment , name varchar(32) unique);

 

这时, name 列就是一个唯一索引.

unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容,则不能重复.

主键字段,不能为NULL,也不能重复.

②在创建表后,再去创建唯一索引

1 create table eee(id int primary key auto_increment, name varchar(32));
2 create unique index 索引名  on 表名 (列表..);

 

 

2.查询索引

desc 表名 【该方法的缺点是: 不能够显示索引名.】

show index(es) from 表名

show keys from 表名

 

3.删除

alter table 表名 drop index 索引名;

 

如果删除主键索引。

alter table 表名 drop primary key

 

4.修改

先删除,再重新创建.

 

二叉树算法的索引实现原理:

 

索引的代价:

  1. 占用磁盘空间
  2. 对dml操作有影响,变慢

在哪些列上适合添加索引?

总结: 满足以下条件的字段,才应该创建索引.

a: 肯定在where条经常使用 b: 该字段的内容不是唯一的几个值(sex) c: 字段内容不是频繁变化.

 

使用索引的注意事项

把dept表中,我增加几个部门:

1 alter table dept add index my_ind (dname,loc); //  dname 左边的列,loc就是右边的列

说明,如果我们的表中有复合索引(索引作用在多列上), 此时我们注意:

1,  对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用。 explain select * from dept where loc='aaa'\G   就不会使用到索引

2,对于使用like的查询,查询如果是  ‘%aaa’ 不会使用到索引,‘aaa%’ 会使用到索引。

比如: explain select * from dept where dname like '%aaa'\G

不能使用索引,即,在like查询时,关键的 ‘关键字’ , 最前面,不能使用 % 或者 _这样的字符., 如果一定要前面有变化的值,则考虑使用 全文索引->sphinx.

3.如果条件中有or,即使其中有条件带索引也不会使用(select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45)。换言之,就是要求使用的所有字段,都必须建立索引, 我们建议大家尽量避免使用or 关键字

4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’), 也就是,如果列是字符串类型,就一定要用 ‘’ 把他包括起来.

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

 

表方面的优化

●永远为每张表设置一个ID主键

●越小的列会越快

对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。所以,把我们的数据变得紧凑会对这种情况非常有帮助,因为这减少了对硬盘的访问。 参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。 如果一个表只会有几列罢了(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果我们不需要记录时间,使用 DATE 要比 DATETIME 好得多。 
“性别”,“国家”,“民族”,“状态”或“部门”等字段,我们知道这些字段的取值是有限而且固定的,那么,我们应该使用 ENUM 而不是 VARCHAR。

●建表符合三范式

●选择合适的存储引擎

myisam 存储: 如果表对事务要求不高,同时是以查询和添加为主的,我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表,回复表.

INNODB 存储: 对事务要求高,保存的数据都是重要数据,我们建议使用INNODB,比如订单表,账号表.

MyISAM 和 INNODB的区别

1. 事务安全

2. 查询和添加速度

3. 支持全文索引(5.5版本以后,innodb 支持全文索引

4. 锁机制

5. 外键 MyISAM 不支持外键, INNODB支持外键

 

 

 

●mysql读写分离

●对表进行水平分割

当一张越来越大时候,即使添加索引还慢的话,我们可以使用分表(即存在不同的表里)

●对表进行垂直分割

如果一张表某个字段,信息量大,但是我们很少查询,则可以考虑把这些字段,单独的放入到一张表中,这种方式称为垂直分割

● 如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理

optimize table 表名;

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:mysql-8.0 安装教程(自定义配置文件,密码方式已修改)

下一篇:mysql5.7.22 下载过程