文章目录
经过剖析 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进阶篇》
- 《黑马瑞吉外卖优化篇》