时间戳

首页 > 技术文章 > [数据库系列之MySQL]Mysql优化笔记

[数据库系列之MySQL]Mysql优化笔记

dream-to-pku 2017-02-10 20:34 原文

大型网站提速之MySql优化

数据库优化包括的方面

数据库优化是一个综合性的技术，并不是通过某一种方式让数据库效率提高很多，而是通过多方面的提高，从而使得数据库提高很多。

主要包括：

1、表的设计合理化(3范式)

2、Sql语句的优化

3、给表要添加合适的索引，如何使用索引

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

5、定时清除垃圾数据，定时进行碎片整理

6、多用存储过程，和触发器

7、对mysql配置进行一些优化

8、读写分离

9、MySql服务器硬件升级

数据库的设计：

良好的数据库：

 1、节省的存储空间

 2、保证数据的完整性

 糟糕的数据库：

1、数据冗余，存储空间的浪费

2、产生数据不完整

设计数据库的步骤

1、充分了解需求

2、标识实体：具体存在的对象，名词

3、标识属性

4、标识关系

 (1).一对一：两个表的主键是公共字段

 (2).一对多：主键和非主键建关系

 (3).多对一：非主键和主键建关系

 (4).多对多：非主键和非主键建关系

如何将E_R图(实体关系图)转成表

1、将实体转成表，将属性转成字段

2、如果找不到一个合适的字段做主键，我们就添加一个自动增长列作为主键

3、表之间建立正确的关系

数据规范化

表设计出来以后，并不是最合理的结构，我们需要对标进行规范化(我们通过3范式来对表进行规范化)

1、确保每列的原子性

   第一范式用来规范化所有的字段，所有的字段都不可再分。

2、非键字段必须依赖于键字段

   一个表只能描述一件事情

3、消除传递依赖

  非主键字段中，如果一个字段可以推导出另一个字段这就叫传递依赖。

数据库的分类：

关系型数据库：MySql  Oracle SqlServer db2 informix  sysbase

非关系型数据库：(面向对象)

NoSql数据库：MongoDB(面向文档)

反三范式：

范式越高，数据冗余就越少，但是有时候效率就越底下

规范化和性能的关系：

数据库性能比规范化数据库更重要

定位慢查询：

【增 删  改】(一共占10%)  查(占有90%)

MySql数据库一些关于状态的查询：

用”show status”指令

Show status    查询MySql数据库的一些状态

Show status like ‘’com_insert  显示执行了多少次插入

Show status like ‘com_update’显示执行了多少次更新

Show status like ‘com_delete’ 显示执行了多少次删除

Show status like ‘com_select’ 显示执行了多少次查询语句

Show status like ‘connections’  查看试图连接到MySql的连接数

Show status like ‘threads_cache’  查看线程缓存内的线程的数量

Show status like ‘threads_connected’ 查看当前打开的连接的数量

Show status like ‘threads_running’  查看激活的线程数

Show status like ‘slow_queries’ 查看查询时间超过long_query_time秒的查询个数

show status like ‘uptime’ 查询mysql数据库启动多长时间，如果时间很长，数据库表的存储引擎有是myisam，这时候就要注意碎片整理

Show [session|global] status like ...  如果不写，默认是会话级的(session)

 

显示慢查询：

Show variables like ‘long_query_time’;  显示慢查询的时间，默认情况下10秒是一个慢查询

Set long_query_time=0.5  设置慢查询的时间为0.5秒

Show status like ‘slow_queries’  显示慢查询的条数

 

定位慢查询(开启慢查询的日志)：

一旦开启慢查询日志后，日志文件的位置在my.ini文件中去查找，在默认情况下，我们的mysql是不会记录慢查询。

如何开启记录慢查询的步骤：

1、关闭当前的MySql服务

2、通过安全模式启动就会写日志 mysqld --safe--mode  --slow-query-log,如果低版本：mysqld -log-slow-queries=”d:/mylog.log”

3、关闭安全模式启动的MySql：mysqladmin -uroot -paa shut down

4、Set long_query_time=0.5

5、Select * from emp where empno=’12345’

6、到慢查询日志中去找到对应的SQL语句

Explain 分析的sql语句：

 

创建主键索引：

Create index 索引名 on 表(字段)

创建全文索引：

所有myisam存储引擎支持全文索引，innodb是不支持的。

Create tabke articles(

Id int unsigned auto_increment not null primary key,

Title varchar(200),

Body TEXT,

Fulltext(title ,body)

)engine = myisam charset=utf8

 

Select * from articles where match(title,body) against(‘database’)

 

总结：

1、全文索引必须是myisam存储引擎

2、MySql自己的fulltext是不支持中文的，用sphinx技术去处理中文

3、语法：match(字段,...) against

4、在50%以上都出现的关键词，是不会做全文索引的，因此做全文索引必须是海量数据

创建主键索引：

Create unique index ix_stu on student(name)

主键索引和唯一索引的区别

主键索引

主键索引只能有一个

不能重复，不能为空

唯一索引

可以有多个

不能重复

 

全文索引：

在MYSQL中使用全文索引（FULLTEXT index）

NYSQL的一个很有用的特性是使用全文索引（FULLTEXT index）查找文本的能力，目前只有MyISAM类型的表才有效，全文索引可以建立在TEXT CHAR VARCHAR类型的字段或者字段组合上。

建立表：CREATE TABLE fulltext_sample(copy TEXT,FULLTEXT(copy)) TYPE=MyISAM; 

如果表已经存在则使用ALTER TABLE fulltext_sample ADD FULLTEXT(copy)

查找：SELECT * FROM fulltext_sample WHERE MATCH(copy) AGAINST(‘love’)

在全文索引上进行搜索是不区分大小写的。

全文索引通常用来搜索自然语言文本，网页内容等等，因此MySql未这类搜索添加了很多特性，MySql不索引任何长度小于等于3的文本，也不索引有50%机会出现的单词，这意味着如果你的表记录少于2则返回空。

主要特征：

1、排除重复词语

2、排除长度小于4的词语

3、排除在多于一半记录中出现的词语

4、带连字符的词语被认为两个词语

5、结果安装关联度降序返回

6、忽略列表中的词语也内从搜索结果中排除，忽略列表基于普通的英文单词，因此如果你的数据用作不同的目的，你可能希望改变忽略列表，

7、返回匹配度：select copy ,MATCH(copy) AGAINST(‘good,alert’) AS relevance FROM fulltext_sample WHERE MATCH(copy) AGAINST(‘good,alert’)

如何使用索引：

Explain分析sql语句：

1、如果用主键去查询，自动会使用主键索引

2、如果创建的是复合索引，只有左边的可以用，右边的不能用

3、模糊查询的时候，%或者下划线写在左边没有用索引，写到右边就用了索引

4、在条件语句中使用or,or的两边的字段必须要有索引，有一个没有，索引就无法使用

5、如果一个字段是字符型的，必须用引号引起来。

在MySql数据库中，EXPLAIN指令的用法：EXPLAIN SELECT 语句

在oracle数据库中,EXPLAIN PLAN FOR SELECT 语句

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE  

        table: admin

         type: const

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 1

          ref: const

         rows: 1

        Extra:

1 row in set (0.00 sec)

 

Id

Select标识符，这是select的查询序号。

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子句的子查询)。

 

Table

输出的所引用的表

Type

联结类型

system

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

const

表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次！

eq_ref

对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。

ref

对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY(换句话说，如果联接不能基于关键字选择单个行的话)，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。

ref_or_null

该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

index_merge

该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

unique_subquery

该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)。 unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

index_subquery

该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)

range

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。 当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range。

index

该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。 当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

ALL

对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

 

Possible_keys

possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。

Key

key列显示MySQL实际决定使用的键(索引)。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

Key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

ref

ref列显示使用哪个列或常数与key一起从表中选择行。

rows

rows列显示MySQL认为它执行查询时必须检查的行数。

Extra

该列包含MySQL解决查询的详细信息。下面解释了该列可以显示的不同的文本字符串：

Distinct

MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。

Not exists

MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。 下面是一个可以这样优化的查询类型的例子：SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;假定t2.id定义为NOT NULL。在这种情况下，MySQL使用t1.id的值扫描t1并查找t2中的行。如果MySQL在t2中发现一个匹配的行，它知道t2.id绝不会为NULL，并且不再扫描t2内有相同的id值的行。换句话说，对于t1的每个行，MySQL只需要在t2中查找一次，无论t2内实际有多少匹配的行。

range checked for each record (index map: #)

MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。 这并不很快，但比执行没有索引的联接要快得多。

Using filesort

MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

Using index

从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。

Using temporary

为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。

Using where

WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。 如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。

Using sort_union(...), Using union(...), Using intersect(...)

这些函数说明如何为index_merge联接类型合并索引扫描。

Using index for group-by

类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。

 

 

如何选择MySql的存储引擎:

MyISAM

1、不支持事务处理

2、查询和添加的效率很高

3、碎片很多

论坛等

Innodb

1、支持事务

用来保存比较重要

Memory

数据频繁更改，而且不需要再数据中永久保存

Session入库

 

碎片处理：

Optimize table 表名

在MySql中，我们经常会使用varchar   text  blob等可变长度的文本数据类型，不过当我们使用这些数据类型之后，我们就不得不做一些额外的工作--MySql数据表碎片整理。
Why?

创建表并插入大量的数据，因为MySql中MyISAM表的数据是以文件的形式存储的，我们可以在MySql存储数据的文件夹中找到数据库test目录下的.MYD文件，我们可以看到其大小，然后按条件删除一部分数据这时看到其.MYD文件的大小一点都没变，这是因为在MySql中如果我们删除了表中的大量数据，或者我们队含有可变文本数据类型(varchar  text blob)的表进行了很多更改，不过被删除的数据记录仍然被保持在MySql的链接清单中，因此数据存储文件的大小并不会随着数据的删除而减小。

当我们确定数据需要被清除掉时，那么这些数据就已经成了无用的数据，但是按照MySql的处理方式，这些数据仍然会占用我们的磁盘空间，从而造成了极大的资源浪费。不仅如此，过大的数据文件还会导致MySql执行相关数据操作时需要消耗更多的性能和时间，因此对MySql的某些数据表进行碎片整理是非常有必要的。

对MySql进行碎片整理的方法非常简单，因为MySql已经给我们提供了对应的Sql指令，这个SQL指令就是OPTIMIZE TABLE，其完整语法如下：

OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE table_name [,table_name2]

我们可以看出，OPTIMIZE TABLE可以一次性对多个表进行碎片整理，只需要在OPTIMIZE TABLE后面接多个表名，并以英文逗号隔开即可。

此外，OPTIMIZE TABLE语句有两个可选的关键字：LOCAL 和NO_WRITE_BINLOG.在默认情况下，OPTIMIZE TABLE语句会被记录到二进制日志中，如果我们制定了LOCAL 或 NO_WRITE_TO_BINLOG关键字则不会记录，一般情况下我们也不需要关注这两个关键字。

使用OPTIMIZED TABLE进行整理之后我们就会发现.MYD文件的额大小发生了变化。

备注：

1、MySql官方建议不要经常进行碎片处理，一般根据实际情况，只需要每周或每月整理一次即可。

2、OPTIMIZE_TABLE只对MyISAM,bdb和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上诉可变长度的文本数据类型的表进行整理即可。

3、在OPTIMIZE TABLE运行过程中，在MySql中会锁定表

4、默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示Tanle does not support optimize,doing recreate+analyze instead的提示信息，这个时候，我们可以用mysqld --skip-new 或者mysqld --safe-mode命令来重启MySql，以便于让其他引擎支持OPTIMIZE TABLE

MySql数据库之索引：

创建索引：CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON table_name(index_col_name,...)

[UNIQUE|FULLTEXT|SPATIAL]

表示创建的索引类型，分别表示唯一索引、全文索引、空间索引三种不同的索引类型。如果不加任何关键字，则默认为普通索引。

Index_name

表示索引的名称，由用户自行定义，以便于以后对该索引进行修改等管理操作。

Index_type

表示索引的具体实现方式，在MySql中，有两种不同形式的索引-BTREE索引和HASH索引。在存储引擎为MySqlISAM和InnoDB的表只能使用BTREE;在存储引擎为MEMORY或者HEAP的表中可以使用HASH和BTREE两种类型的索引，其默认值为HASH。

Index_col_name

表示需要创建索引的字段名称。我们还可以根据多个字段创建复合索引。只需要在多个字段名称之间以英文逗号隔开即可。

Eg:CREATE INDEX idx_user_username ON user(username(6))

由于多字段的前6个字符通常不同，所以此索引不会比使用字段的全部内容创建的索引速度慢很多。另外，使用字段的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。

在MySql中，前缀长度最大值为255字节。对于存储引擎为MyISAM或InnoDB的数据表，前缀最长为1000字节。

在MySQL中，对于TEXT和BLOB这种大数据类型的字段，必须给出前缀长度(length)才能成功创建索引。

创建索引(表已创建)：

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (index_col_name,...) [USING index_type]

注意：在MySql中，只有当数据表的存储引擎为MyISAM InnoDB或BOB类型时，才能向具有NULL值的列或数据类型为TEXT或BLOB的列添加索引。

删除索引：

     ALTER TABLE user DROP INDEX idx_user_username

修改索引：

先删除再创建

--ALTER TABLE user DROP INDEX idx_user_username

--CREATE INDEX idx_user_username ON user (username(8))

查看索引：

--如果查看索引钱，没有使用use db_name等命令指定具体的数据库，则必须加上FROM db_name

  SHOW INDEX FROM table_name [FROM db_name]

--如果查看索引前，没有使用use db_name等命令指定具体的数据库，则必须加上db_name前缀

  SHOW INDEX FROM [db_name.]table_name

 

MySql之视图：

创建视图：

   CREATE [OR REPLACE] [ALGORITHM= {UNDEFINED | MERGE |TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH[CASCADED | LOCAL] CHECK OPTION]

OR REPLACE

该关键字可选，如果当前数据库中已经存在指定名称的视图时，没有该关键字，将会提示错误信息；如果使用了OR REPLACE关键字，则当前正在创建的视图会覆盖掉原来同名的视图。

[ALGORITHM= {UNDEFINED | MERGE |TEMPTABLE}]

ALGORITHM子句是可选的，他标识使用何种算法来处理视图此外它并不属于标准的SQL的一部分，而是MySql对标准SQL进行的功能扩展。ALGORITHM可以设置三个值：MERGE TEMPTABLE 或UNDEFINED,如果没有ALGORITHM字句，则默认值为UNDEFINED。对于MERGE，会将引用的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。

对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。

对于UNDEFINED,MySql将选择要使用的算法，如果可能，它倾向于MERGE而不是TEMPTABLE.这是因为MERGE通常更有效，如果使用了临时表，视图是不可更新的。

之所以提供TEMPTABLE选项，是因为TEMPTABLE在创建临时表之后、并在完成语句处理之前，能够释放基表上的锁定。与MERGE算法相比，锁定释放的速度更快，这样，使用视图的其他客户端不会被屏蔽过长时间。

此外，MERGE算法要求视图中的行和基表中的行具有一对一的关系。如果视图包含聚合函数(SUM(), MIN(), MAX(), COUNT()等)、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL、没有基表的引用文字值(例如：SELECT 'hello';)等结构中的任何一种，将失去一对一的关系，此时必须使用临时表取而代之。

 

[(column_list)]

用于自定义视图中各个字段的名称。如果没有该命令选项，那么通过视图查询创建到的哥哥字段的名称和视图所使用到的数据库表的字段名称保持一致

Eg:CREATE OR REPLACE v_user[(uid,uname)] AS SELECT id,username FROM user

Select_statement

用于指定视图的内容定义，也就是用户自定义的select语句。

[WITH[CASCADED | LOCAL] CHECK OPTION]

 

该选项中的CASCADED为默认值，LOCAL CHECK OPTION用于在可更新视图中放置插入或更新

 

删除视图：

  --DROP VIEW [IF EXISTS] view_name [,view_name2]

关键字IF EXISTS用于防止因视图不存在而提示出错，此时只有在视图存在的时候才会执行删除操作。DROP VIEW语句可以一次性删除多个视图，只需要在多个视图名称之间已英文逗号隔开即可。如果多个视图存在于不同的数据库中，还要在数据库的视图名称之前加上db_name前缀

修改视图：

   --REPLACE [ALGORITHM= {UNDEFINED | MERGE |TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH[CASCADED | LOCAL] CHECK OPTION]

查看视图：

   Show tables 命令不仅可以查看当前数据库中存在哪些数据表，同事也可以查看到当前数据库中存在哪些视图。

  Show full tanles会额外侠士出table_type可以区分出是表还是视图。

Show create view view_name查看创建视图的SQL语句。

 

推荐阅读