优化出发点

大部分情况下一般是考虑索引，但是其他方面也值得考虑。

数据库级别

  • 表的结构：列是否具有正确的数据类型？表的结构是否设计正确，例如，频繁更新的应用的表结构应该是少列多表，分析大量数据的应用应该是少表多列
  • 是否创建索引？索引是否有用？
  • 是否选择了合适的存储引擎，这个现在一般是InnoDB
  • 表是否使用正确的行格式(ROW_FORMAT)？特别是ROW_FORMAT为COMPRESSED 的表使用较少的磁盘空间，因此需要较少的磁盘I/O来读写数据
  • 应用程序是否使用了适当的锁策略？例如在某种情况下允许共享读(shared access)，以便数据库操作能够同时进行
  • 缓存大小是否设置正确？足够大的缓存能够容纳经常访问的数据，但是又不能过大，过大导致使用虚拟内存I/O性能下降

硬件级别

磁盘种类、速度、CPU、内存等因素

数据类型优化

几个简单原则：

  1. 更小的通常更好

    应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型占用更少磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少。（确保没有低估，如果无法确定哪个数据类型最好，就选择不会超过范围的最小类型）

  2. 简单就好

    简单类型操作通常需要更少的CPU周期。例如整型比字符操作代价更低，因为字符集和排序规则时字符比较比整型比较更复杂。比如使用MySQL的内置类型（date,time,datatime）而不是字符串来存储时间和日期

  3. 尽量避免NULL

    可为NULL的列被索引时使用更多的存储空间，在MySQL里需要被特殊处理。当可为NULL的列被索引时，每个索引记录需要额外的字节记录。（注意，通常把可NULL的列改为NOT NULL带来的性能提升比较小，但是如果计划在列上建索引，就应该尽量避免设计成可为NULL的列）

索引建立策略

一般加索引就能解决了很多慢查询问题

下面默认讲的索引都是btree索引

独立列

索引列不能是表达式一部分，也不能是函数参数，否则MySQL不会使用索引

1
2
select ... where id+1=5;
select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)<=10;

前缀索引和后缀索引

  • 索引很长的字符串，会让索引变得大且慢，通常可以索引开始的部分字符（前缀），可以大大节约索引空间，提高索引效率

    1
    create index table_index on atdata_casedataon(user(3));

    选择的前缀尽量让建立的索引区分度更高。可以用下面的方法选择合适的前缀，如果选择性能够接近0.031，基本上就可用。如果全是0，就没有必要用这个了。

    1
    2
    3
    4
    5
    select
        COUNT(DISTINCT LEFT(user, 1))/COUNT(*) as user1,
        COUNT(DISTINCT LEFT(user, 2))/COUNT(*) as user2,
        COUNT(DISTINCT LEFT(user, 3))/COUNT(*) as user3
    from atdata_casedata

    缺点是无法使用前缀索引做Group by和Order by，也无法使用前缀索引做覆盖索引

  • 后缀索引在某些情况下会有用途，比如找到某个域名所有电子邮箱地址，但是MySQL原生不支持反向索引，但是可用把字符串反转后存储

多列索引

在多个列上建立独立的单列索引大部分情况下不能提高查询性能，旧版本MySQL中只会使用其中一个索引。

1
SELECT...WHERE name = x AND age = t
1
2
CREATE INDEX name_index ON table (name);
CREATE INDEX age_index ON table (age);
1
CREATE INDEX name_age_index ON table (name, age);

虽然新版本有优化，但是在有多个查询条件情况下最好使用多列索引，使其索引到所有条件列。

选择合适的索引列顺序

索引列的顺序意味着首先按照最左列进行排序，其次是第二列，那么如何选择列的顺序，有一个经验法则是将选择性（值不同的个数）最高的列放到索引最前列（更多适用于不考虑order和groupby）

1
SELECT * FROM payment WHERE staff_id = 2 and customer_id = 58
1
2
3
4
5
6
7
SELECT 
   COUNT(DISTINCT staff_id) AS count_staff_id, 
   COUNT(DISTINCT customer_id) AS count_customer_id
FROM payment

count_staff_id: 1001
count_customer_id: 1241334

那么此时选择customer_id会更好。

或者有个更通用的做法是按照最左前缀匹配原则。MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式

使用索引优化order by

条件：

  • 当索引列的顺序和order by子句的顺序完全一致，并且所有列排序方向一致（有一特殊情况，前面列为常量）
  • 关联多张表，order by子句引用的字段全部在第一个表

比如有一索引KEY a_b_c_index(a,b,c) 

1
2
3
4
5
6
WHERE a = 1 ORDER BY b  #前面列为常量,true
WHERE a = 1 ORDER BY b,c #前面列为常量,true
WHERE a > 1 ORDER BY b,c #前面列为范围查询,false
WHERE a > 1 ORDER BY a,b,c #索引列顺序一样,true
WHERE a = 1 ORDER BY b DESC,c ASC #所有列排序方式不一样,false
WHERE a = 1 ORDER BY c #没有符合最左前缀,false

优化group by

group by操作在没有合适的索引可用时，通常先扫描整个表提取数据并创建一个临时表，然后按照group by指定的列进行排序，需要通过建立索引避免创建临时表，一般会选择多列索引

优化聚合函数

将所有使用的列包括在索引中（“覆盖索引”），MySQL不需要访问表本身，并且可以仅读取索引来继续进行操作。

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

重复索引

避免创建重复索引（相同的列上按照相同的顺序创建相同类型的索引），MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑

尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

查询性能优化

避免访问所有列

  1. 只返回需要的列
  2. 使用覆盖索引来减少查询扫描的行数

分解关联查询

在很多场景下通过重构查询将关联放到应用程序中将会更加高效

1
2
3
4
SELECT * FROM tag
	JOIN tag_post ON tag_post_id=tag.id
	JOIN post ON post.post_id=post.id
WHERE tag.tag='mysql';

可以分解成下面这些查询来代替：

1
2
3
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post_id in (1,2,3,4);
  1. 让缓存效率更高，许多应用程序可以方便地缓存单表查询对应的结果集
  2. 将查询分解后，执行单个查询可以减少锁的竞争
  3. 查询本身效率也可能会有所提升：比如使用IN() 代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效
  4. 减少冗余记录查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。

原理分析

原理推荐看这篇

MySQL索引原理及慢查询优化