优化MySQL

最新推荐文章于 2024-08-29 17:00:41 发布

DueGin

最新推荐文章于 2024-08-29 17:00:41 发布

阅读量103

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/m0_51736969/article/details/129548000

版权

文章目录

示例数据库下载
选取适合的存储引擎
选取最适合的字段属性
使用连接（JOIN）来代替子查询
避免使用全表扫描
索引优化
定期分析表
定期检查表
定期优化表
大批量导入数据
- load
- - 导入时关闭唯一性校验
  - 导入时关闭自动提交
insert
order by
limit
update
count
参考资料

经过剖析 SQL ，确定问题后，采取相应优化措施
剖析 MySQL 性能

示例数据库下载

MySQL官方示例数据库，本文用的是 sakila 示例数据库
黑马进阶篇示例数据库，本文用的是 tb_user 示例数据库

选取适合的存储引擎

MyISAM引擎设计简单，数据以紧密格式存储，所以某些读取场景下性能很好。
如果没有特别的需求，使用默认的Innodb即可。

MyISAM：

以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：

更新（删除）操作频率也高，或者要保证数据的完整性；
并发量高，支持事务和外键保证数据完整性。比如OA自动化办公系统。

选取最适合的字段属性

单表字段不宜过多，建议最多30个以内
使用小而简单的合适数据类型
尽量将列设置为NOT NULL
- 为 NULL 的列占用更多的存储空间
- 在使用索引和值比较时，MySQL 需要做特殊的处理，损耗一定的性能
尽量使用整型做主键
- 整型标识好，很快且可以使用 AUTO_INCREMENT
- 应该避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢
- 对于完全"随机"的字符串也需要多加注意。例如：MD5()，SHAI()或者UUID()产生的字符串。这些函数生成的新值也任意分布在很大空间内，这会导致 INSERT 和一些 SELECT 语句很缓慢

使用连接（JOIN）来代替子查询

使用子查询需要在MySQL中创建临时表来完成多个步骤的查询工作
超过三个表最好不要用 join

避免使用全表扫描

从此例中看出，全表扫描导致效率不理想

mysql> explain select sum(amount) from customer a, payment b
    -> where 1=1 and a.customer_id=b.customer_id and email='JANE.BENNETT@sakilacustomer.org'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 10.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 26
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

那么，对客户表 customer 的 email 字段创建索引：

create index idx_email on customer(email);

然后再次查询：

mysql> explain select sum(amount) from customer a, payment b
    -> where 1=1 and a.customer_id=b.customer_id and email='JANE.BENNETT@sakilacustomer.org'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ref
possible_keys: PRIMARY,idx_email
          key: idx_email
      key_len: 203
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 26
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.02 sec)

可以发现，建立索引后对客户表 customer 需要扫描的行数明显减少（从583行减少到1行），可见索引的使用可以大大提高数据库的访问速度，尤其在表很庞大的时候这种优势更为明显。

索引优化

索引是数据库优化中最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的SQL性能问题。本节将详细讨论MySQL中索引的分类、存储、使用方法。

索引存储分类

B-Tree 索引：最常见的索引类型，大部分引擎都支持B树索引。
HASH 索引：只有Memory引擎支持，使用场景简单。
R-Tree 索引（空间索引）：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
Full-text（全文索引）：全文索引也是 MyISAM 的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6版本开始提供对全文索引的支持。

比较常用到的索引就是 B-Tree 索引和 Hash 索引

Hash 索引不适用范围查询，例如<、>、<=、>=这类操作。
Memory/Heap 引擎只有在“=”的条件下才会使用索引。

前缀索引

MySQL 目前不支持函数索引（现在不知道支不支持），但是能对列的前面某一部分进行索引

例如：
标题 title 字段，可以只取 title 的前10个字符进行索引（前缀索引），这个特性可以大大缩小索引文件的大小
但前缀索引也有缺点，在排序 Order By 和分组 Group By 操作的时候无法使用。
用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。

create index idx_title on film(title(10));

B-Tree

B-Tree 索引是最常见的索引，构造类似平衡树，能根据键值提供一行或者一个行集的快速访问，通常只需要很少的读操作就可以找到正确的行

可以利用 B-Tree 索引进行全关键字、关键字范围和关键字前缀查询

例子：
为了避免混淆，重命名租赁表 rental 上的索引 rental_date 为 idx_rental_date：

mysql> alter table rental drop index rental_date;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table rental add index idx_rental_date(rental_date,inventory_id,customer_id);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

能够使用索引的情况

1. 匹配全值

对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件。

例如：
租赁表 rental 中通过指定出租日期 rental date + 库存编号 inventory_id + 客户编号 customer id 的组合条件进行查询，从执行计划的 key 和 extra 两字段的值看到优化器选择了复合索引 idx rental date：

mysql> explain select * from rental where rental_date='2005-05-25 17:22:10' and 
inventory_id=373 and customer_id=343\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: idx_fk_inventory_id,idx_fk_customer_id,idx_rental_date
          key: idx_rental_date
      key_len: 10
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

字段 key 的值为 idx_rental_date，表示优化器选择索引 idx_rental_date 进行扫描。

2. 匹配值的范围查询

对索引的值能够进行范围查找

例如：
检索租赁表 rental 中客户编号 customer id 在指定范围内的记录：

mysql> explain select * from rental where customer_id >= 373 and customer_id = 400\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: const
         rows: 28
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

3. 匹配最左前缀

仅仅使用索引中的最左边列进行查找
比如，在 a + b + c 字段上的联合索引能够被包含a、(a + b)、(a + b + c）的等值查询利用到，可是不能够被 b、(b + c）的等值查询利用到

例如：
以支付表 payment 为例，如果查询条件中仅包含索引的第一列支付日期 payment_date 和索引的第三列更新时间 last_update 的时候，从执行计划的 key 和 extra 看到优化器仍然能够使用复合索引 idx_payment_date 进行条件过滤：

mysql> alter table payment add index idx_payment_date(payment_date,amount,last_update);
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from payment where payment_date ='2006-02-14 15:16:03' and last_update='2006-02-15 22:12:32'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_payment_date
          key: idx_payment_date
      key_len: 5
          ref: const
         rows: 182
     filtered: 10.00
        Extra: Using index condition
1 row in set, 1 warning (0.01 sec)

但是，如果仅仅选择复合索引 idx_payment_date 的第二列支付金额 amount 和第三列更新时间 last_update 进行查询时，那么执行计划显示并不会利用到索引 idx_payment_date

mysql> explain select * from payment where amount=3.98 and last_update='2006-02-15 22:12:32'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 1.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

4. 仅对索引进行查询

当查询的列都在索引的字段中时，查询的效率更高；

对比上一个例子使用 select *，本次选择查询的字段都包含在索引 idx payment_date 中时，能够看到查询计划有了一点变动：

mysql> explain select last_update from payment where payment_date ='2006-02-14 15:16:03' and amount=3.98\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_payment_date
          key: idx_payment_date
      key_len: 8
          ref: const,const
         rows: 8
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Extra 部分变成了 Using index，也就意味着，现在直接访问索引就足够获取到所需要的数据，不需要通过索引回表，Using index 也就是平常说的覆盖索引扫描。只访问必须访问的数据，在一般情况下，减少不必要的数据访问能够提升效率。

5. 匹配列前缀

仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。

例如：
现在需要查询出标题 title 是以 AFRICAN 开头的电影信息，从执行计划能够清楚看到，idx_title_desc_part 索引被利用上了：

mysql> create index idx_title_desc_part on film_text (title(10),description(20));
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select title from film_text where title like 'AFRICAN%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_text
   partitions: NULL
         type: range
possible_keys: idx_title_desc_part,idx_title_description
          key: idx_title_desc_part
      key_len: 42
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

Extra 值为 Using where 表示优化器需要通过索引回表查询数据

6. 索引匹配部分精确而其他部分进行范围匹配

例如：
需要查询出租日期 rental_date 为指定日期且客户编号 customer_ic 为指定范围的库存：

-- idx_rental_date(rental_date,inventory_id,customer_id)
mysql> explain select inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id >300 and customer_id <=400\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id,idx_rental_date
          key: idx_rental_date
      key_len: 5
          ref: const
         rows: 182
     filtered: 16.66
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

索引 key 为 idx_rental_date 说明优化器选择索引 idx_rental_date 帮助加速查询，同时由于只查询索引字段 inventory_id 的值，所以在 Extra 部分能看到 Using index，表示查询使用了覆盖索引扫描。

7. 索引 is null

如果列名是索引，那么使用列名 is null 就会使用索引（区别于Oracle)。

例如：
查询支付表 payment 的租赁编号 rental_id 字段为空的记录就用到了索引：

mysql> explain select * from payment where rental_id is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
          ref: const
         rows: 5
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.01 sec)

8. ICP（Index Condition Pushdown）

Pushdown 表示操作下放，某些情况下的条件过滤操作下放到存储引擎。

例如：
查询租赁表rental中租赁时间rental date在某一指定时间点且客户编号customer id在指定范围内的数据：
由《深入浅出MySQL》给出的例子：

mysql> explain select * from rental where 
    -> rental_date='2006-02-14 15:16:03'and customer_id>=300 and customer_id <=400\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id,idx_rental_date
          key: idx_rental_date
      key_len: 5
          ref: const
         rows: 182
     filtered: 16.85
        Extra: Using index condition
1 row in set, 1 warning (0.01 sec)

Using index condition 就表示 MySQL 使用了 ICP 来进一步优化查询，在检索的时候，把条件 customer_id的过滤操作下推到存储引擎层来完成，这样能够降低不必要的 IO 访问

有索引但不能使用索引的情况

1. 以 % 开头的 like 查询

以 % 开头的 like 查询不能够利用 B-Tree 索引：

mysql> explain select * from actor where last_name like '%NI%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

因为 B-Tree 索引的结构，所以以 % 开头的查询很自然就没法利用索引了。
解决办法是

用全文索引（Fulltext）来解决类似的全文检索问题。
考虑利用 InnoDB 的表都是聚簇表的特点，采取一种轻量级别的解决方式：一般情况下，索引都会比表小，扫描索引要比扫描表更快（某些特殊情况下，索引比表更大，不在本例讨论范围内)

这里使用聚簇表的特点，而 InnoDB 表上二级索引 idx_last_name 实际上存储字段 last_name 还有主键 actor_id，那么理想情况应该是：
首先扫描二级索引 idx_last_name 获得满足条件 last_name like ‘%NI%’ 的主键 actor_id 列表，之后根据主键回表去检索记录，这样访问避开了全表扫描演员表 actor 产生的大量 IO 请求。验证一下：

mysql> explain select * from (select actor_id from actor where last_name like '%NI%') a,
    -> actor b where a.actor_id=b.actor_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: idx_actor_last_name
      key_len: 182
          ref: NULL
         rows: 200
     filtered: 11.11
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.01 sec)

从执行计划中能够看到，内层查询的 Using index 代表索引覆盖扫描，之后通过主键 join 操作去演员表 actor 中获取最终查询结果，理论上是能够比直接全表扫描更快一些。

2. 隐式转换导致不会使用索引

mysql> explain select * from actor where last_name=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: idx_actor_last_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 10.00
        Extra: Using where
1 row in set, 3 warnings (0.00 sec)

mysql> explain select * from actor where last_name='1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 182
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

3. 复合索引不包含索引最左边部分

假如查询条件不包含索引列最左边部分，即不满足最左原则 Leftmost，是不会使用复合索引的：

mysql> explain select * from payment where amount=3.98 and last_update='2006-02-15 22:12:32'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 1.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

4. 全表扫描比索引快的情况

如果MySQL估计使用索引比全表扫描更慢，则不使用索引。
例如，查询以“s”开头的标题的电影，需要返回的记录比例较大，MySQL就预估索引扫描还不如全表扫描更快：

mysql> update film_text set title=concat('S',title);
Query OK, 1000 rows affected (0.35 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

mysql> explain select * from film_text where title like 'S%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_text
   partitions: NULL
         type: ALL
possible_keys: idx_title_desc_part,idx_title_description
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

更换查询的值为一个选择率更高的值，就能发现优化器更倾向于选择索引扫描：

mysql> explain select * from film_text where title like 'SW%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_text
   partitions: NULL
         type: range
possible_keys: idx_title_desc_part,idx_title_description
          key: idx_title_desc_part
      key_len: 42
          ref: NULL
         rows: 43
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

5. 用 or 分割条件

如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到，例如：

mysql> explain select * from payment where customer_id = 203 or amount = 3.96\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: idx_fk_customer_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 10.15
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

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

定期分析表

本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得 SQL 能够生成正确的执行计划。
语法：

analyze [LOCAL NO_WRITE_TO_BINLOG] TABLE 表名1 [, 表名2, ..];

如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。

在分析期间，使用一个读取锁定对表进行锁定。这对于 MyISAM、BDB 和 InnoDB 表有作用。对于 MyISAM 表，本语句与使用 myisamchk -a 相当，下例中对表 sales 做了表分析：

mysql> analyze table payment;
+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| sakila.payment | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set (0.04 sec)

定期检查表

检查表

检查表的作用是检查一个或多个表是否有错误，或者检查视图是否有错误。
语法：

check table 表名1 [,表名2, ...] [option ...] option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。对于 MyISAM 表，关键字统计数据被更新，例如：

mysql> check table payment_myisam;
+-----------------------+-------+----------+----------+
| Table                 | Op    | Msg_type | Msg_text |
+-----------------------+-------+----------+----------+
| sakila.payment_myisam | check | status   | OK       |
+-----------------------+-------+----------+----------+
1 row in set (0.02 sec)

检查视图

CHECK TABLE 也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在，例如：

1. 首先创建一个视图

mysql> create view v_payment_myisam as select * from payment_myisam;
Query OK, 0 rows affected (0.03 sec)

2. check 一下该视图

发现没有问题

mysql> check table v_payment_myisam;
+-------------------------+-------+----------+----------+
| Table                   | Op    | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| sakila.v_payment_myisam | check | status   | OK       |
+-------------------------+-------+----------+----------+
1 row in set (0.00 sec)

3. 删掉视图依赖的表

mysql> drop table payment_myisam;
Query OK, 0 rows affected (0.04 sec)

4. 再次 check 一下视图

发现报错了，且提示出错原因是 Table sakila.payment_myisam 不存在了：

mysql> check table v_payment_myisam\G
*************************** 1. row ***************************
   Table: sakila.v_payment_myisam
      Op: check
Msg_type: Error
Msg_text: View 'sakila.v_payment_myisam' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
*************************** 2. row ***************************
   Table: sakila.v_payment_myisam
      Op: check
Msg_type: error
Msg_text: Corrupt
2 rows in set (0.02 sec)

定期优化表

可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但 OPTIMIZE TABLE 命令只对 MyISAM、BDB 和 InnoDB 表起作用。

optimize [LOCAL | NO_WRITE_TO_BINLOG] table 表名1 [, 表名2, ...];

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有 VARCHAR、BLOB 或 TEXT 列的表）进行了很多更改，则应使用 OPTIMIZE TABLE 命令来进行表优化。
以下例子显示了优化表 payment_myisam 的过程：
如果没有这个表，则创建：

-- 创建同样结构表
create table payment_myisam like payment;
-- 修改表引擎
alter table payment_myisam engine=myisam;
-- 插入数据
insert into payment_myisam select * from payment;

执行：

mysql> optimize table payment_myisam;
+-----------------------+----------+----------+----------+
| Table                 | Op       | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| sakila.payment_myisam | optimize | status   | OK       |
+-----------------------+----------+----------+----------+
1 row in set (0.03 sec)

对于 InnoDB 引擎的表来说，通过设置 innodb_file_per_table 参数，设置 InnoDB 为独立表空间模式，这样每个数据库的每个表都会生成一个独立的 ibd 文件，用于存储表的数据和索引，这样可以一定程度上减轻 nnoDB 表的空间回收问题。
设置方式：
在 my.cnf 中 [mysqld]下设置

innodb_file_per_table=1

另外，在删除大量数据后，InnoDB 表可以通过 alter table 但是不修改引擎的方式来回收不用的空间：

mysql> alter table payment engine=innodb;
Query OK, 0 rows affected (0.87 sec)
Records: 0  Duplicates: 0  Warnings: 0

大批量导入数据

load

如果一次性需要插入大批量数据（比如: 几百万的记录），使用 insert 语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

MyISAM

对于MyISAM存储引擎的表，可以通过以下方式快速地导入大量的数据。

ALTER TABLE 表名 disable KEYS;
loading the data
ALTER TABLE 表名 enable KEYS;

disable KEYS 和 enable KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。在导入大量的数据到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的 MyISAM 表，默认就是先导入数据然后才创建索引的，所以不用进行设置。
由于没有实测数据，所以用《深入浅出MySQL第二版》的测试结果：

InnoDB

主键顺序插入，性能要高于乱序插入
因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导人的数据按照主键的顺序排列，可以有效地提高导入数据的效率
默认是关闭本地文件导入的

-- 客户端连接服务端时，加上参数 -–local-infile 
mysql --local-infile -u root -p 

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 
set global local_infile = 1; 

-- 执行load指令将准备好的数据，加载到表结构中 
load data local infile '/root/sql_100w.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;

例如：
插入100w的数据量

mysql> CREATE TABLE `ta_user` ( 
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL, 
  `password` VARCHAR(50) NOT NULL, 
  `name` VARCHAR(20) NOT NULL, 
  `birthday` DATE DEFAULT NULL, 
  `sex` CHAR(1) DEFAULT NULL, 
  PRIMARY KEY (`id`), UNIQUE KEY `unique_user_username` (`username`) 
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> set global local_infile = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> load data local infile '/home/test/mysql/load_user_100w_sort.sql' into table ta_user fields terminated by ',' lines terminated by '\n' ;
Query OK, 1000000 rows affected (1 min 13.54 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select count(*) from ta_user;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.19 sec)

导入时关闭唯一性校验

在导入数据前执行 SET UNIQUE CHECKS=0，关闭唯一性校验，在导入结束后执行 SET UNIQUE CHECKS=1，恢复唯一性校验，可以提高导入的效率。

导入时关闭自动提交

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

insert

批量插入数据
手动控制事务
主键顺序插入

如果从不同客户插入很多行，可以通过使用 INSERT DELAYED 语句得到更高的速度。DELAYED的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快得多；LOW PRIORITY 刚好相反，在所有其他用户对表的读写完成后才进行插入。

order by

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

对于以上的两种排序方式，Using index 的性能高，而 Using filesort 的性能低，我们在优化排序
操作时，尽量要优化为 Using index。

本例用的表是：

create table tb_user(
	id int primary key auto_increment comment '主键',
	name varchar(50) not null comment '用户名',
	phone varchar(11) not null comment '手机号',
	email varchar(100) comment '邮箱',
	profession varchar(11) comment '专业',
	age tinyint unsigned comment '年龄',
	gender char(1) comment '性别 , 1: 男, 2: 女',
	status char(1) comment '状态',
	createtime datetime comment '创建时间'
) comment '系统用户表';


INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00'),('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00'),('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00'),('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00'),('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00'),('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00'),('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00'),('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00'),('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00'),('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00'),('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00'),('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00'),('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00'),('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00'),('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00'),('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00'),('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00'),('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00'),('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00'),('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00'),('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00'),('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00'),('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00'),('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');

mysql> explain select id,age,phone from tb_user order by age, phone\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 24
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.01 sec)

由于 age, phone 都没有索引，所以此时排序时，出现 Using filesort，排序性能较低。

而有索引后

mysql> create index idx_user_age_phone_aa on tb_user(age,phone);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select id,age,phone from tb_user order by age, phone\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_user
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_user_age_phone_aa
      key_len: 48
          ref: NULL
         rows: 24
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

limit

在数据量比较大时，如果进行 limit 分页查询，在查询时，越往后，分页查询效率越低。
一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头痛的分页场景是“1imit 1000, 20”，此时 MySQL 排序出前 1020 条记录后仅仅需要返回第 1001 到 1020 条记录，前 1000 条记录都会被抛弃，查询和排序的代价非常高。

在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。

例如，对电影表film根据标题title排序后取某一页数据：

优化前：

mysql> explain select film_id,description from film order by title limit 50,5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

优化后：

mysql> explain select a.film_id,a.description from film a inner join (select film_id from film order by title limit 50,5)b on a.film_id =b.film_id\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 55
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: b.film_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: film
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using filesort
3 rows in set, 1 warning (0.00 sec)

这种方式让 MySQL 扫描尽可能少的页面来提高分页效率。

update

但是当我们在执行如下SQL时。

update course set name = 'SpringBoot' where name = 'PHP' ;

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。导致该update语句的性能大大降低。
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁
升级为表锁。

count

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；但是如果是带条件的 count，MyISAM 也慢。
InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

如果说要大幅度提升 InnoDB 表的 count 效率，主要的优化思路：自己计数（可以借助于 redis 这样的数据库进行，但是如果是带条件的count又比较麻烦了）。
尝试一下100w数据量需要统计多久：

select count(*) from ta_user;

参考资料

《深入浅出MySQL++数据库开发、优化与管理维护+第2版+唐汉明》
《2022黑马MySQL进阶篇》
《黑马瑞吉外卖优化篇》

DueGin

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
优化MySQL

经过剖析 SQL ，确定问题后，采取相应优化措施。
复制链接

扫一扫