mysql 高性能建议_根据《高性能MySQL》整理一些数据库优化建议

根据《高性能MySQL》一书，整理一些数据库优化建议。mysql

1.为字段选择合适的数据类型

归纳来讲，尽量保持任何东西小而简单老是好的。MySQL喜欢简单，须要使用数据库的人

应该也一样会喜欢简单的原则．sql

尽可能避免过分设计，例如会致使极其复杂查询的schema设计，或者有不少列的表设计(不少的意思是介于有点多和很是多之间).

使用小而简单的合适数据类型，除非真实数据模型中有确切的须要，不然应该尽量地避免使用NULL值。

尽可能使用相同的数据类型存储类似或相关的值，尤为是要在关联条件中使用的列。

注意可变长字符串，其在临时表和排序时可能致使悲观的按最大长度分配内存。

尽可能使用整型定义标识列。

避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。

当心使用ENUM和SETO虽然它们用起来很方便，可是不要滥用，不然有时候会变成陷阱。最好避免使用BIT。

2.建立高性能的索引

在选择索引和编写利用这些索引的查询时，有以下三个原则始终须要记住：数据库

单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机I/O要快不少，不过这一点仍然成立)。若是服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了不少工做。最好读取的块中能包含尽量多所须要的行。使用索引能够建立位置引，用以提高效率。

按顺序访问范围数据是很快的，这有两个缘由。第一，顺序I/O不须要屡次磁盘寻道，因此比随机I/O要快不少(特别是对机械硬盘)。第二，若是服务器可以按须要顺序读取数据，那么就再也不须要额外的排序操做，而且GROUPBY查询也无须再作排序和将行按组进行聚合计算了。

索引覆盖查询是很快的。若是一个索引包含了查询须要的全部列，那么存储引擎就不须要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。

在字段长度较大的字段上创建索引时，根据选择性原则，选取合适的长度创建前缀索引。

在where语句后面进行and字段链接时，考虑创建多列索引，并合理安排多列索引的顺序，以便利用索引扫描来排序(既能查询行又能对结果排序)。

在合适的字段上创建聚簇索引，通常的主键就是聚簇索引，数据行和索引都保存在B-Tree的叶子节点，这样查询到索引就找到了数据，不用再回表查询。

使用“覆盖索引”。所谓覆盖索引就是，全部须要查询的字段都是索引字段。例如在age上创建B-Tree索引，查询SELECT age from user WHERE age = 13;因为age索引和数据行都被保存在B-Tree叶子节点，在获取索引的时候也直接得到了数据，不用回表查询，因此查询速度很快。

合理对待冗余索引，对于重复索引则必须删除。

在使用索引字段做为条件时，若是该索引是多列索引，那么必须使用到该索引中的第一个字段做为条件时才能保证系统使用该索引，不然该索引将不会被使 用，而且应尽量的让字段顺序与索引顺序相一致。(最左匹配原则)。

索引并非越多越好，索引当然能够提升相应的 select 的效率，但同时也下降了 insert 及 update 的效率，由于 insert 或 update 时有可能会重建索引，因此怎样建索引须要慎重考虑，视具体状况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

应尽量的避免更新聚簇索引数据列，由于聚簇索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将致使整个表记录的顺序的调整，会耗费至关大的资源。若应用系统须要频繁更新聚簇索引索引数据列，那么须要考虑是否应将该索引建为聚簇索引索引。

3.优化查询语句

避免向数据库请求不须要的数据缓存

避免查询不须要的记录，在查询后面加上LIMIT

避免select *

避免重复查询相同的数据，能够将相同的数据进行缓存

避免数据库扫描过多的数据行而只返回少数的行服务器

使用索引覆盖扫描，把全部须要查询的列都放到索引中，这样存储引擎无须回表获取对应行就能够返回结果了。

优化LIMIT，例如" LIMIT 10000,20“会扫描10020条数据只返回20条。可使用查询和BETWEEN AND优化。

改变库表结构。例如使用单独的汇总表。

避免执行耗时的大语句网络

由于一个大的语句一次性完成的话，则可能须要一次锁住不少数据、占满整个事务日志、耗尽系统资源、阻塞不少小的但重要的查询。例如，一次性删除10w条记录。可使用LIMIT切分查询，一次删除1000条。并发

优化关联查询：函数

确保ON或者USING子句中的列上有索引。在建立索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，若是优化器的关联顺序是B、A，那么就不须要在B表的对应列上建立索引。没有用到的索引只会带来额外的负担。通常来讲，除非有其余理由。不然只须要在关联顺序中的第二个表的相应列上建立索引。缘由：MySQL对任何关联都执行嵌套循环关联操做。

确保任何的GROUP BY 和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才可能使用索引来优化这个过程。

有时候，能够将一个复杂的关联查询拆分为多个简单查询。

简单查询的结果能够缓存起来，提升缓存的效率。

执行单个简单查询能够减小锁的竞争。

在应用层作关联，能够更容易对数据库进行拆分，更容易作到高性能和可扩展。

查询自己效率也可能会有所提高。这个例子中，用IN()代替关联查询，可让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。

能够减小冗余记录的查询。在应用层作关联查询，意味着对于某条记录应用只须要查询一次，而在数据库中作关联查询，则可能须要重复地访问一部分数据。从这点看，这样的重构还可能会减小网络和内存的消耗。

优化子查询：性能

在MySQL 5.6及以上版本中，再也不建议使用关联查询代替子查询，高版本的MySQL对子查询进行了优化。MySQL 5.6 版本之前会建议避免使用IN+子查询，而使用下面两种方式代替。高版本对其进行了优化，可使用IN+子查询的方式。优化

mysql > SELECT  *  FROM sakila.film

->WHERE film_id IN(

->        SELECT film_id FROM sakila.filn_actor WHERE actor_id = 1);

优化一：

SELECT  *  FROM sakila.film

WHERE EXISTS(

SELECT  *  FROM  sakila.film  actor  WHERE  actorid = 1

AND film_actor.film_id  =  film.film_id);

优化二：

SELECT  film.*  FROM   sakila.film

INNER  JOIN  sakila.film_actor USING(film_id)

WHERE  actor_id = 1;

优化UNION查询：

除非确实须要服务器消除重复好行，不然必定要使用UNION ALL，这一点很重要 。 若是没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会致使对整个临时表的数据作惟一性检查。这样作的代价很是高。即便有ALL关键字，MySQL仍然会使用临时表存储结果。事实上， MySQL老是将结果放人临时表，而后再读出，再返回给客户端。虽然不少时候这样作是没有以要的(例如，MySQL能够直接把这些结果返回给客户端 )。

自定义变量：(限制比较大，谨慎使用)

用户自定义变量是一个用来存储内容的临时容器，在链接MySQL的整个过程当中都存在。

可使用下面的SET和SELECT语句来定义它们：

mysql> SET @one              ：=1

mysql> SET @min_actor    ：=or：=(SELECT MIN(actor_dd) FROM sakila.actor)；

mysql> SET @last_week    ：=CURRENT_DATE-INTERVAL 1 WEEK；

而后能够在任何可使用表达式的地方使用这些自定义变量：

mysql> SELECT ...  WHERE col <= @last_week;

其余建议：

应尽可能避免在 where 子句中使用!=、<>、in、not in、between and 等范围条件查询，不然将致使存储引擎放弃使用索引而进行全表扫描。

避免使用前置%查询，会致使全表扫描。

尽可能避免在 where 子句中使用 or 来链接条件，若是or的字段上有一个字段没有创建索引，将致使引擎放弃使用索引而进行全表扫描。如：                                                                                                                                                                  SELECT id from user WHERE age = 13 or phone = '13167001221';                                                                                    若在age和phone两个字段其中一个字段上没有索引，则不会使用索引。必须都有索引才使用索引。能够这样查询：SELECT id from user WHERE age = 13union allSELECT id from user WHERE phone = '13167001221';

应尽可能避免在where子句中对字段进行函数操做及表达式操做，这将致使引擎放弃使用索引而进行全表扫描。如：    SELECT * from user WHERE age = 3;使用索引                                                                                                                  SELECT * from user WHERE age/2 = 3; 不使用索引

尽可能使用数字型字段，若只含数值信息的字段尽可能不要设计为字符型，这会下降查询和链接的性能，并会增长存储开销。这是由于引擎在处理查询和链接时会 逐个比较字符串中每个字符，而对于数字型而言只须要比较一次就够了。

尽量的使用 varchar/nvarchar (必须指定长度)代替 char/nchar ，由于首先变长字段存储空间小，能够节省存储空间，其次对于查询来讲，在一个相对较小的字段内搜索效率显然要高些。

避免频繁建立和删除临时表，以减小系统表资源的消耗。

在新建临时表时，若是一次性插入数据量很大，那么可使用 select into 代替 create table，避免形成大量 log ，以提升速度；若是数据量不大，为了缓和系统表的资源，应先create table，而后insert。

若是使用到了临时表，在存储过程的最后务必将全部的临时表显式删除，先 truncate table ，而后 drop table ，这样能够避免系统表的较长时间锁定。

尽可能避免大事务操做，提升系统并发能力。

附：MySQL怎样执行关联查询

当前MySQL关联执行的策略很简单：MySQL对任何关联都执行嵌套循环关联操做，即MySQL先在一个表中循环取出单条数据，

而后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到全部表中匹配的行为止。而后根据各个表匹配的行，返回查询中须要的各个列。MySQL会尝试在最后一个关联表中找到全部匹配的行，若是最后一个关联表没法找到更多的行之后，MySQL返回到上一层次关联表，看是否可以找到更多的匹配记录，依此类推迭代执行。按照这样的方式查找第一个表记录，再嵌套查询下一个关联表，而后回溯到上一个表，在MySQL中是经过嵌套循环的方式实现一一正如其名“嵌套循环关联"。

请看下面的例子中的简单查询：

假设MySQL按照查询中的表顺序进行关联操做，咱们则能够用下面的伪代码表示MySQL将如何完成这个查询: