MySQL数据库结合项目实战SQL优化总结

建立索引的一些经验

• 建立索引的列，不允许为null。单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集，所以，请使用not null约束以及默认值。
• 更新十分频繁的字段上不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的。
• 区分度不大的字段上不宜建立索引：类似于性别这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外返回数据的比例在30%以外的情况下，优化器不会选择使用索引。
• 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引会影响insert速度，但是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发的情况下，依然有脏数据产生。
• 多表关联时，要保证关联字段上一定有索引。
• 创建索引时避免以下错误观念：索引越多越好，认为一个查询就需要建一个索引；宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度；抵制唯一索引，认为业务的唯一性一律需要在应用层通过“先查后插”方式解决；过早优化，在不了解系统的情况下就开始优化。
• 使用列的类型小的创建索引。这里说的类型小指的是该类型表示的数据范围的大小。
• 当我们要定义某一列的类型时，以整数类型为例，有TINYINT，MEDIUMINT，INT，BIGINT等，他们表示的范围依次递增，占用的空间也是依次递增。如果要对该列创建索引时，在数据范围允许的情况下，尽量选用较小的类型，也就是能用INT就不要用BIGINT，能够MEDIUMINT就不要用INT，原因如下：
  • 数据类型越小，在查询时进行的比较操作就越快。
  • 数据类型越小，索引占用的空间就越小，在一个数据页内就可以存放更多的记录，整颗B+树就更矮，从而减少IO次数。
  • 这对于表的主键更加适用，因为不仅是聚簇索引还是非聚簇索引，各节点都会存放一份主键值，如果主键选用更小的类型，也就意味着节省更多的存储空间和更高效的IO。

Explain执行后的各个字段解释

id

• SELECT 识别符。这是 SELECT 的查询序列号.

select_type

• SELECT类型,可以为以下任何一种:
  • SIMPLE: 简单select查询,查询中不包含子查询或者UNION
  • PRIMARY: 查询中若包含任何复杂的子查询,最外层查询则被标记为primary.
  • UNION: 若第二个select出现的union之后,则被标记为union；若union包含在from子句的子查询中,外层select将被标记为deriver
  • DEPENDENT UNION: UNION 中的第二个或后面的 SELECT 语句,取决于外面的查询
  • UNION RESULT: 从union表获取结果select，两个UNION合并的结果集在最后
  • SUBQUERY: 在select或where中包含了子查询.
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT,取决于外面的查询
  • DERIVED: 在from列表中包含的子查询被标记为derived(衍生),把结果放在临时表当中

table

• 输出的行所引用的表（显示这一行的数据是关于哪张表的）

partitions

• 表分区（如果查询是基于分区表的话, 会显示查询访问的分区）

type

• NULL: MySQL不访问任何表，索引，直接返回结果
• system: 表仅有一行(=系统表)。这是 const 联接类型的一个特例。
• const: 表最多有一个匹配行,const用于比较primary 或者 unique索引。直接查询主键或者唯一索引,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const 表很快,因为它们只读取一次!
• eq_ref: 唯一性索引扫描。对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描，对于每个来自于前面的表的行组合, 从该表中读取一行。这可能是最好的联接类型, 除了 const 类型。
• ref: 非唯一性索引扫描。对于每个来自于前面的表的行组合, 所有有匹配索引值的行将从这张表中读取。
• ref_or_null: 该联接类型如同 ref,但是添加了 MySQL 可以专门搜索包含 NULL 值的行。
• index_merge: 该联接类型表示使用了索引合并优化方法。
• 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: 只检索给定范围的行,使用一个索引来选择行。
• index: 该联接类型与 ALL 相同,除了只有索引树被扫描。这通常比 ALL 快,因为索引文件通常比数据文件小。
• ALL: 对于每个来自于先前的表的行组合, 进行完整的表扫描。

性能从最优到最差

• NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

要求：一般来说,保证查询至少达到range级别，最好能达到ref

possible_keys

• 显示可能应用在这张表中的索引,一个或者多个；查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用

key

• 实际使用的索引,如果为NULL,则没有使用索引 ；查询中若使用了覆盖索引 ,则该索引仅出现在key列表中；possible_keys与key关系 ：理论应该用到哪些索引，实际用到了哪些索引；覆盖索引 查询的字段和建立的字段刚好吻合,这种我们称为覆盖索引。

key_len

• 表示索引中使用的字节数,可通过该列计算查询中使用的索引长度 。如果键是 NULL, 则长度为 NULL。

ref

• 显示使用哪个列或常数与 key 一起从表中选择行。索引是否被引入到, 到底引用到了哪几个索引

rows

• 显示 MySQL 认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数，每长表有多少行被优化器查询过。

filtered

• filtered表示通过查询条件获取的最终记录行数占通过type字段指明的搜索方式搜索出来的记录行数的百分比。filtered列的值依赖统计信息，并不十分准确。

Extra

• 该列包含 MySQL 解决查询的详细信息
  • Distinct: MySQL 发现第 1 个匹配行后,停止为当前的行组合搜索更多的行。
  • Not exists: MySQL 能够对查询进行 LEFT JOIN 优化, 发现 1 个匹配 LEFT JOIN 标准的行后, 不再为前面的的行组合在该表内检查更多的行。
  • range checked for each record (index map: #): MySQL 没有发现好的可以使用的索引, 但发现如果来自前面的表的列值已知, 可能部分索引可以使用。
  • Using filesort: MySQL 需要额外的一次传递, 以找出如何按排序顺序检索行。所有不是通过索引直接返回排序结果的排序
    都叫 FileSort 排序。
  • Using index: 使用覆盖索引的时候就会出现 ,只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
  • Using temporary: 为了解决查询, MySQL 需要创建一个临时表来容纳结果。
  • Using where: 在查找使用索引的情况下，需要回表去查询所需的数据 。
  • Using sort_union(...), Using union(...), Using intersect(...): 这些函数说明如何为 index_merge 联接类型合并索引扫描。
  • Using index for group-by: 类似于访问表的 Using index 方式,Using index for group-by 表示 MySQL 发现了一个索引,可以用来查询
  • using index condition：查找使用了索引，但是需要回表查询数据
  • using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表 查询数据
• EXPLAIN命令结果中的Using Index意味着不会回表，通过索引就可以获得主要的数据。Using Where则意味着需要回表取数据。
  GROUP BY 或 DISTINCT 查询的所有列, 而不要额外搜索硬盘访问实际的表。

表关联查询时务必遵循 小表驱动大表 原则

• 注意：小表驱动大表是不严谨的，应该是小的结果集驱动大的结果集。这里评判结果集大小是符合条件的表的行数 * 每行的大小。参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表
• JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用。
• 以左连接为例，左边的表称为驱动表，右边的表称为被驱动表，我们要在被驱动表上加索引。
  • 右边是我们的关键点,一定需要建立索引 ，因为左连接，左边是一定要全表扫描的，而右边是内层循环，只需要挑出符合左边的数据即可，因此右边建立索引可以大大提高检索效率。
  • 内连接两张表的地位相同，哪张为驱动表，哪张为被驱动表由优化器决定，很多情况下数据量少的为驱动表（驱动表要全表扫描，数据量少意味着扫描规模小）。

示例

• sku表一共有 53173 条数据，sku_inventory 有 32385条数据。

explain select s.sku_code,s.product_name,s.average_amount,si.amount
        from sku_inventory as si
        left join sku as s
        on s.sku_code = si.sku_code
1
2
3
4

explain select s.sku_code,s.product_name,s.average_amount,si.amount
        from sku as s
        left join sku_inventory as si
        on s.sku_code = si.sku_code
1
2
3
4

LIKE 语句不允许使用 % 开头，否则索引会失效；

# index(section_code,shelf_code)
#索引起作用了
explain select section_code,shelf_code from location where shelf_code='E1-01-28%'
1
2
3

explain select s.product_name
        from sku as s where s.sku_code like  '%78504772808'
1
2

explain select s.product_name
        from sku as s where s.sku_code like  '78504772808%'
1
2

explain select s.product_name
        from sku as s where s.sku_code='78504772808'
1
2

尽量使用覆盖索引，避免select *

• 尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。
• 如果查询列，超出索引列，也会降低性能。

explain select * from location where code like 'A%'
1

explain select code from location where code like 'A%'
1

使用单表查询时，相同字段尽量不要用 OR

• 对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索
  引； 如果没有索引，则应该考虑增加索引。
• 同一个字段，union、in、or都能够命中索引，建议使用in。

explain select * from sku where sku_code='78166804828' or sku_code='78464859935'
1

explain select * from sku where sku_code in ('78166804828','78464859935')
1

explain select * from sku where sku_code='78166804828'
    union
        select * from sku where sku_code='78464859935'
1
2
3

# OR前后存在非索引的列，索引失效
explain select * from sku where sku_code='78166804828' or sku.product_name='耳机'
1
2

• 因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，存在全表扫描的情况下，就没有必要多一次索引扫描增加IO访问。

组合索引一定要遵循最左前缀法则

• 最左前缀法则指的是查询从索引的最左前列开始，并且不跳过索引中的列。
• 复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中符合索引。
• 建了一个联合索引 （warehouse_code,section_code,）

# 只有 warehouse_code 起作用
explain select * from location where warehouse_code='FJI' and shelf_code='A-01-01'
1
2

# 都没起作用
explain select * from location where warehouse_code='FJI' or section_code='A'
1
2

返回表中30%内的数据会走索引，返回超过30%数据就使用全表扫描。当然这个结论太绝对了，也并不是绝对的30%，只是一个大概的范围。

# warehouse_code,section_code 起了作用
explain select * from location where warehouse_code='FJI' and section_code='A' and shelf_code='A-01-01'
1
2

# 不起作用
explain select * from location where  section_code='A' and aisle_code='01'
1
2

数据类型出现隐式转换的时候不会命中索引

• 特别是当列类型是字符串，一定要将字符常量值用引号引起来。

explain select * from goods_purchase_order where code=10148131
1

explain select * from goods_purchase_order where code='10148131'
1

优化器进行权衡判断使用索引比全表更慢，则不使用索引

• 如果MySQL评估使用索引比全表更慢，则不使用索引。
• 由于查询的是所有字段，这意味着需要进行回表操作，又因为表的数据很多，优化器认为通过索引查出来后再回表 并没有直接在内存中排序更快，因此选择不使用索引。

回表：当对一个列创建索引之后，索引会包含该列的键值及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描，应该直接走全表扫描。

• index（section_code,shelf_code）

explain select section_code,shelf_code from location where section_code='E1' or shelf_code='E1-01-28'
1

explain select * from location where section_code='E1' or shelf_code='E1-01-28'
1

索引下推

• Extra中显示Using index condition，就是指使用到了索引下推。

# index(warehouse_code, section_code, aisle_code, shelf_code, code)
explain select * from location where warehouse_code='FJI' and shelf_code='A-01-01'
1
2

• 对于上面例子，warehouse_code='FJI' 使用上了索引，但是后面由于最左前缀匹配原则，所以shelf_code无法使用上索引，但是存储引擎此时会进行一种优化：索引下推。也就是通过索引查询出数据后，并不急着回表，而是先通过后面的shelf_code查询过滤掉不符合的数据，最后再回表查询所有的列数据，这就叫做索引下推。
• 如果没有索引下推，那么在通过索引查询出数据后，就立刻进行回表查出符合的数据，最后才判断是否 符合 shelf_code='A-01-01' 的条件，这样增多了回表的次数。
• 对于联合索引也是如此：虽然有些字段无法用上索引，但是存储引擎在回表之前会对该字段先进行过滤，减少回表时的数据量，提高查询效率。

EXISTS 和 IN

• 对于第一条语句使用的是IN，可以理解成会先查询B，也就是先查询子语句，然后将每一条查询结果去A表中找是否有符合条件的。因此适用于A的cc列有索引，并且B是小表。（换个角度理解，可以将 IN 看成 多个 = 号 ）
• 对于第二条语句使用的是EXISTS，可以理解成会从A表中取出每一条数据，然后去B表中找是否有符合条件的。因此适用于B表的cc列有索引，并且A表是小表。

COUNT(*) 和 COUNT(具体字段)

• count(*) = count(1) > count(主键字段) > count(字段)
• 对于NULL值是否统计：COUNT(*) 和 COUNT(1) 会计算NULL值，COUNT(具体字段)不会计算NULL值。

在索引列上进行运算操作， 索引将失效

# index(code)
explain select * from location where   substring(code,8)='A-01-01'
1
2

优化嵌套查询

• 优化前

explain select * from t_user where id in (select user_id from user_role );
1

• 优化后：

explain select * from t_user u , user_role ur where u.id = ur.user_id;
1

• 连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的
  查询工作。

explain select * from t_user u left join user_role ur on u.id = ur.user_id;
1

其它

• 不使用NOT IN和<>操作：NOT IN和<>操作都不会使用索引将进行全表扫描。
• NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。
• 负向条件查询不能使用索引，可以优化为in查询。负向条件有：!=、<>、not in、not exists、not like等。
• 范围条件右边的列索引失效

SQL执行顺序

编写顺序

SELECT DISTINCT
     <select list>
FROM
     <left_table> <join_type>
JOIN
     <right_table> ON  <join_condition>
WHERE
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>
LIMIT
    <limit_params>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

执行顺序

FROM

     <left_table>

ON <join_condition>

<join_type> JOIN <right_table>

WHERE <where_condition>

GROUP BY <group_by_list>

HAVING <having_condition>

SELECT DISTINCT <select list>

ORDER BY <order_by_condition>

LIMIT <limit_params>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

• 参考：https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html

你知道的越多，你不知道的越多。