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

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

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

概括来说，尽可能保持任何东西小而简单总是好的。MySQL喜欢简单，需要使用数据库的人
应该也同样会喜欢简单的原则．

1. 尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计（很多的意思是介于有点多和非常多之间).
2. 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值。
3. 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
4. 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
5. 尽量使用整型定义标识列。
6. 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
7. 小心使用ENUM和SETO虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。最好避免使用BIT。

2.创建高性能的索引

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

• 单行访问是很慢的。特别是在机械硬盘存储中（SSD的随机I/O要快很多，不过这一点仍然成立)。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引，用以提升效率。
• 按顺序访问范围数据是很快的，这有两个原因。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘)。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUPBY查询也无须再做排序和将行按组进行聚合计算了。
• 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。

1. 在字段长度较大的字段上建立索引时，根据选择性原则，选取合适的长度建立前缀索引。
2. 在where语句后面进行and字段连接时，考虑建立多列索引，并合理安排多列索引的顺序，以便利用索引扫描来排序(既能查询行又能对结果排序)。
3. 在合适的字段上建立聚簇索引，一般的主键就是聚簇索引，数据行和索引都保存在B-Tree的叶子节点，这样查询到索引就找到了数据，不用再回表查询。
4. 使用“覆盖索引”。所谓覆盖索引就是，所有需要查询的字段都是索引字段。例如在age上建立B-Tree索引，查询SELECT age from user WHERE age = 13;由于age索引和数据行都被保存在B-Tree叶子节点，在获取索引的时候也直接获得了数据，不用回表查询，所以查询速度很快。
5. 合理对待冗余索引，对于重复索引则必须删除。
6. 在使用索引字段作为条件时，如果该索引是多列索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。（最左匹配原则）。
7. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
8. 应尽可能的避免更新聚簇索引数据列，因为聚簇索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新聚簇索引索引数据列，那么需要考虑是否应将该索引建为聚簇索引索引。

3.优化查询语句

避免向数据库请求不需要的数据

1. 避免查询不需要的记录，在查询后面加上LIMIT
2. 避免select *
3. 避免重复查询相同的数据，可以将相同的数据进行缓存

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

1. 使用索引覆盖扫描，把所有需要查询的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了。
2. 优化LIMIT，例如" LIMIT 10000,20“会扫描10020条数据只返回20条。可以使用查询和BETWEEN AND优化。
3. 改变库表结构。例如使用单独的汇总表。

避免执行耗时的大语句

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

优化关联查询：

1. 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上创建索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由。否则只需要在关联顺序中的第二个表的相应列上创建索引。原因：MySQL对任何关联都执行嵌套循环关联操作。
2. 确保任何的GROUP BY 和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才可能使用索引来优化这个过程。
3. 有时候，可以将一个复杂的关联查询拆分为多个简单查询。

 

• 简单查询的结果可以缓存起来，提高缓存的效率。
• 执行单个简单查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身效率也可能会有所提升。这个例子中，用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;

其他建议：

1. 应尽量避免在 where 子句中使用!=、<>、in、not in、between and 等范围条件查询，否则将导致存储引擎放弃使用索引而进行全表扫描。
2. 避免使用前置%查询，会导致全表扫描。
3. 尽量避免在 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';
4. 应尽量避免在where子句中对字段进行函数操作及表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：    SELECT * from user WHERE age = 3;使用索引                                                                                                                  SELECT * from user WHERE age/2 = 3; 不使用索引
5. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
6. 尽可能的使用 varchar/nvarchar （必须指定长度）代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
7. 避免频繁创建和删除临时表，以减少系统表资源的消耗。
8. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
9. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
10. 尽量避免大事务操作，提高系统并发能力。

附：MySQL怎样执行关联查询

当前MySQL关联执行的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，
然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，MySQL返回到上一层次关联表，看是否能够找到更多的匹配记录，依此类推迭代执行。按照这样的方式查找第一个表记录，再嵌套查询下一个关联表，然后回溯到上一个表，在MySQL中是通过嵌套循环的方式实现一一正如其名“嵌套循环关联"。
请看下面的例子中的简单查询：

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