mysql数据库优化的一些经验

最新推荐文章于 2021-08-18 22:24:03 发布

闻道☞

最新推荐文章于 2021-08-18 22:24:03 发布

阅读量513

点赞数 1

分类专栏：数据库 MySQL

本文链接：https://blog.csdn.net/u014401141/article/details/78812099

版权

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

34 篇文章 2 订阅

订阅专栏

MySQL

16 篇文章 0 订阅

订阅专栏

2.3 通过 explain 语句可以分析，mysql如何执行你的sql语句

Mysql数据库的优化技术

对mysql优化时一个综合性的技术，主要包括

1: 表的设计合理化(符合3NF)

2: 添加适当索引(index) [四种:普通索引、主键索引、唯一索引unique、全文索引]

3: 分表技术(水平分割、垂直分割)

4: 读写[写: update/delete/add]分离

5: 存储过程 [模块化编程，可以提高速度]

6: 对mysql配置优化[配置最大并发数my.ini,调整缓存大小，默认为100，max_connection=1000 ]

7: mysql服务器硬件升级

8: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

1.MySql 架构图

2. 什么样的表才是符合3NF (范式)

表的范式，一张数据表的表结构所符合的某种设计标准的级别，

是首先符合1NF, 才能满足2NF ,进一步满足3NF

1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解,只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF

☞ 数据库的分类

关系型数据库: mysql/oracle/db2/informix/sysbase/sql server

非关系型数据库: (特点:面向对象或者集合)

NoSql数据库: MongoDB(特点是面向文档)

2NF: 表中的记录是唯一的,就满足2NF,通常我们设计一个主键来实现

3NF: 即表中不要有冗余数据,就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放.比如下面的设计就是不满足3NF:

反3NF :但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。

具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

案例：

2. Sql语句优化

问题是：如何从一个大项目中，迅速的定位执行速度慢的语句. (定位慢查询)

① 首先我们了解mysql数据库的一些运行状态如何查询(比如想知道当前mysql运行的时间/一共执行了多少次select/update/delete.. / 当前连接)

2.1 show status

常用的:

-- 表级锁的争用状态变量
show status like 'table%'; 

-- 行级锁争用状态变量
show status like 'innodb_row_lock%';
对于各个状态说明如下：
Innodb_row_lock_current_waits:当前正在等待锁的数量；
Innodb_row_lock_time:从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间长度；
Innodb_row_lock_waits:系统启动到现在总共等待的次数；


-- mysql数据库启动了多长时间
show status like 'uptime';

show  status like 'com_select';
show status like 'com_insert'
-- 类推 update  delete（显示数据库的查询，更新，添加，删除的次数）

-- 如果你不写 [session|global] 默认是session会话，指取出当前窗口的执行，如果你想看所有(从mysql启动到现在，则应该 global)
show [session|global] status like .... 
show global status where Variable_name in('com_select','com_insert','com_delete','com_update'); 

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

-- 显示慢查询次数
show status like 'slow_queries';

2.2 如何去定位慢查询

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

MySQL 慢查询的相关参数解释

slow_query_log    ：是否开启慢查询日志，1表示开启，0表示关闭。
log-slow-queries  ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据<br>库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需<br>要能够获得更高的系统性能，那么建议优先记录到文件。

-- 查询是否开启慢查询日志，默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的
show variables like '%slow_query_log%';

修改my.cnf文件，增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器，如下所示:

slow_query_log =1
slow_query_log_file=/usr/local/mysql/data/localhost-slow.log

mysql> show variables like 'slow_query%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| slow_query_log      | ON                  |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+---------------------+
rows in set (0.00 sec)
 
mysql> 
#慢查询的参数slow_query_log_file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log

什么样的SQL才会记录到慢查询日志里面呢？这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。关于运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。从MySQL 5.1开始，long_query_time开始以微秒记录SQL语句运行时间，之前仅用秒为单位记录。如果记录到表里面，只会记录整数部分，不会记录微秒部分。

-- 可以显示当前慢查询时间（默认当前的session的慢查询时间）
show variables like 'long_query_time'; 
-- 设置慢查询时间
set global long_query_time=4;

修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到呢？注意：使用命令 set global long_query_time=4修改后，需要重新连接或新开一个会话才能看到修改值。你用show variables like 'long_query_time'查看是当前会话的变量值，你也可以不用重新连接会话，而是用show global variables like 'long_query_time';

log_output 参数是指定日志的存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件.

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql>

系统变量log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的sql也会被记录到慢查询日志。

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)
  
mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)
  
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)
  
mysql>

系统变量log_slow_admin_statements表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志

mysql> show variables like 'log_slow_admin_statements';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF   |
+---------------------------+-------+
1 row in set (0.00 sec)

mysql>

日志分析工具mysqldumpslow

在实际生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow

2.3 通过 explain 语句可以分析，mysql如何执行你的sql语句

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。

Explain语法：explain select … from … [where …]

EXPLAIN SELECT * FROM t_order WHERE CUSTOMER_PHONE="6767765767";

下面对各个属性进行了解：

1、id：这是SELECT的查询序列号
2、select_type：select_type就是select的类型，可以有以下几种：
SIMPLE：简单SELECT(不使用UNION或子查询等)
PRIMARY：最外面的SELECT
UNION：UNION中的第二个或后面的SELECT语句
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT：UNION的结果。
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)

3、table：显示这一行的数据是关于哪张表的
4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。
结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行
6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好
8、ref：显示使用哪个列或常数与key一起从表中选择行。
9、rows：显示MySQL认为它执行查询时必须检查的行数。
10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。
Distinct
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists
MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，
就不再搜索了
Range checked for each
Record（index map:#）
没有找到理想的索引，因此对于从前面表中来的每一 个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort
看 到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来 排序全部行
Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表 的全部的请求列都是同一个索引的部分的时候
Using temporary
看到这个的时候，查询需要优化了。这 里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index， 这就会发生，或者是查询有问题

其他一些Tip：
    当type 显示为 “index” 时，并且Extra显示为“Using Index”， 表明使用了覆盖索引。

2.4 添加索引

四种索引(主键索引/唯一索引/全文索引/普通索引)

2.4.1.添加

1.1 主键索引添加

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

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

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

举例:

create table bbb (id int , name varchar(32) not null default '');
alter table bbb add primary key (id);

1.2 普通索引

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

比如:

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

1.3 创建全文索引

全文索引，主要是针对文件，文本的检索,比如文章,全文索引针对MyISAM有用.

如何使用全文索引:

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

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 body like ‘%mysql%’; 【不会使用到全文索引】

正确的用法是:select * from articles where match(title,body) against(‘database’);【可以】

说明:

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

1.4 唯一索引

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

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

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

unique字段可以为NULL,并可以有多NULL,但是如果是具体内容，则不能重复，但是不能存有重复的空字符串’’.

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

2.在创建表后，再去创建唯一索引

create unique index 索引名 on 表名(列表..);

3. 查询索引

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

show index(es) from 表名

show keys from 表名

4. 删除

alter table 表名 drop index 索引名;

如果删除主键索引。

alter table 表名 drop primary key [这里有一个小问题]

4. 修改

先删除，再重新创建.为什么创建索引后，速度就会变快?

5. 原理示意图:

索引使用的注意事项

索引的代价:

1. 占用磁盘空间

2. 对dml操作有影响，变慢

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

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

a: 肯定在where条件经常使用

b:该字段的内容不是唯一的几个值(sex)

c:字段内容不是频繁变化.

使用索引的注意事项

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

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，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引,我们建议大家尽量避免使用or关键字

select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45

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

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

explain 可以帮助我们在不真正执行某个sql语句时，就执行mysql怎样执行，这样利用我们去分析sql指令

如何查看索引使用的情况:

show status like ‘Handler_read%’;

大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高，说明查询低效。

2.4.2 聚集索引与非聚集索引

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一
聚集索引
聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。

非聚集索引
非聚集索引制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。
例子对比两种索引
聚集索引就类似新华字典中的拼音排序索引，都是按顺序进行，例如找到字典中的“爱”，就里面顺序执行找到“癌”。而非聚集索引则类似于笔画排序，索引顺序和物理顺序并不是按顺序存放的。

2.5 sql语句优化

原则：

永远有小结果集驱动大结果集（join）

只取出自己需要的字段，用字段越多数据量越多，排序占用空间。

仅仅使用最有效的过滤条件（key length）,key越大比较就越多

尽可能避免复杂的join和子查询-锁资源

1.join

show variables  like 'join_%';

2. order by

在使用group by 分组查询是，默认分组后，还会排序，可能会降低速度.

比如:

在group by后面增加 order by null就可以防止排序.

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

select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]

select * from dept left join emp on dept.deptno=emp.deptno; [左外连接，更ok!]

3.如何选择mysql的存储引擎

innodb表锁情况

MySQL InnoDB默认行级锁。行级锁都是基于索引的

行级锁变为表级锁情况如下：

1、如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住。

2、表字段进行变更。

3、进行整表查询。(没使用索引)

4、like语句查询的时候。(没使用索引)

在开发中，我们经常使用的存储引擎 myisam / innodb/ memory

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

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

问 MyISAM 和 INNODB的区别

1. 事务安全（MyISAM不支持事务，INNODB支持事务）

2. 查询和添加速度（MyISAM批量插入速度快）

3. 支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）

4. 锁机制（MyISAM时表锁，innodb是行锁）

5. 外键 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’);

我们应该定期对myisam进行整理

重新利用未使用的空间，并整理数据文件的碎片。
optimize table test100;
mysql_query(“optimize tables $表名”);

5. 分表技术

分表技术有(水平分割和垂直分割)

5.1 水平分割

当一张越来越大时候，即使添加索引还慢的话，我们可以使用分表

以qq用户表来具体的说明一下分表的操作.

思路如图：

首先我创建三张表 user0 / user1 /user2 , 然后我再创建 uuid表，该表的作用就是提供自增的id,

走代码:

create table user0(
id int unsigned primary key ,
name varchar(32) not null default '',
pwd  varchar(32) not null default '')
engine=myisam charset utf8;

create table user1(
id int unsigned primary key ,
name varchar(32) not null default '',
pwd  varchar(32) not null default '')
engine=myisam charset utf8;

create table user2(
id int unsigned primary key ,
name varchar(32) not null default '',
pwd  varchar(32) not null default '')
engine=myisam charset utf8;

create table uuid(
id int unsigned primary key auto_increment)
engine=myisam charset utf8;

我们在提供检索时，应该根据业务的需求，找到分表的标准，并在检索页面约束用户的检索方式->要配合分页。如果有大表检索的需求也是少数的

思考: 如果我们做的是一个平安保险公司的一个订单(8999999999000000条)查询功能更.

,如何处理海量表?->按时间.

1. 分表的标准是依赖业务逻辑(时间/地区/....)

2. 安装字符不同. a-z

3. 我们给用户提供的查询界面一定是有条件，不能让用户进行大范围.(世界)，如果需要的可以根据不同的规则，对应多套分表.

4. 检索时候，带分页条件，减少返回的数据.

5. 项目中，灵活的根据需求来考虑.

5.2 垂直分割

示意图:

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

某个表的某些字段，查询时，并不是时时关心的，但是数据量很大，建议把这些字段单独的放到另外一张表，从而提高效率（当然分出的表要保留和原来表的关联关系）；

表的字段定义是保小不保大，节省磁盘空间

Tinyint< small<mediumint< int < longint

零填充：

Tinyint（1）：不是说只能存放1位数字（0到9），可以存放无符号整数（0到255），有符号整数（-128到127）

证明零填充的例子：

mysql> create table test(id1 tinyint(2) zerofill,id2 tinyint(3) zerofill);
Query OK, 0 rows affected (0.82 sec)
mysql> insert into test values(2,3);
Query OK, 1 row affected (0.10 sec)
mysql> select * from test;

+------+------+

| id1  | id2  |

+------+------+

|   02 |  003 |

+------+------+

1 row in set (0.00 sec)

关于网站的图片和视频存放：

我们的数据表中，一般只是存放图片或者视频的路径，资源是放在文件系统（往往配合独立的服务器）

如何优化mysql数据库的配置：

修改my.ini：端口号，最大连接数，关于缓存大小的设置

Max_connections=100（默认最大连接数并发）

Query_cache_size：查询缓存大小

最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大

innodb_additional_mem_pool_size = 64M

innodb_buffer_pool_size =1G

对于myisam，需要调整key_buffer_size

当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数

如果你的操作系统是64位的，，内存足够大，请选择使用64位的mysql安装版。