1. insert优化
1.1 批量插入而不是单条插入
-- 单条插入
insert into users values(2, 'Bob', 'New York', '222-222-333');
insert into users values(1, 'Frank', 'Chicago', '111-222-222');
-- 批量插入，不要超过1000行
insert into users values(1, 'Frank', 'Chicago', '111-222-222'),(1, 'Frank', 'Chicago', '111-222-222');
1.2 手动事务提交（多次插入后统一插入）
-- start tr5ansaction
insert into users values(1, 'Frank', 'Chicago', '111-222-222');
insert into users values(2, 'Bob', 'New York', '222-222-333');
commit;
1.3 按照主键顺序插入
-- users(uid, name, address, phone), 主键为uid，按照主键先后依次插入行
insert into users values(1, 'Frank', 'Chicago', '111-222-222');
insert into users values(2, 'Bob', 'New York', '222-222-333');
1.4 load指令加载数据
-- 查看加载本地数据文件开关
select @@local_infile;
-- 开启加载本地数据文件开关
set global load_infile = 1;
-- 创建表
create table users(
uid int not null primary key,
name varchar(16) default '',
age int default 0,
create_time datetime not null,
update_time datetime default ('0000-00-00 00:00:00')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- secure_file_priv变量指定的目录，/var/lib/mysql-files
SHOW VARIABLES LIKE "secure_file_priv";
-- 需要把数据文件放到/var/lib/mysql-files目录下才能导入
load data infile '/var/lib/mysql-files/million_rows.log' into table `users` fields terminated by ',' lines terminated by '\n';
2. 主键优化
2.1 Innodb表空间结构
页（Page，16KB）：最小的磁盘IO单元，存放一行一行的数据。
分区（Extent，1MB）：存放页，一个分区有64页。
段（Segment）：存放分区或者零散的页。一个表由多个段构成。
表空间（TableSpace）：不同存储引擎有不同的表空间，文件名后缀 ibd。
2.2 页分裂
行数据挂在聚集索引的叶子结点上，一般使用主键来构建聚集索引。行数据按照主键顺序组织的，在插入数据时要保证有序性。这个存储数据的方式称为索引组织表（Index Organized Table）。页分为数据页和索引页。
页类型：数据页，索引页，空闲页
空闲页的产生：预分配空页待数据填充；数据删除留出空页；页合并产生空页；页分裂也会创建空页来放置分裂的数据。
非空闲页要求至少存放2条数据。
2.2.1 按照主键序插入
按照主键序插入，则数据逐行放置在页中，页满则申请下一个空页继续存放。
2.2.2 不按主键序插入
假设已经插入了两页，1号页满，2号页未满。当前要插入的行在按照主键序应该放在1号页的某个位置，因此这行记录无法直接追加2号页最后一行数据后。此时需要在插入位置分裂1号页，1号页分裂位置之后的行追加到新申请的空页（3号页）中。最后重新安排页的链接顺序（1 -> 3 -> 2）。分裂位置一般为50%左右的位置。
分裂前，欲插入主键值为50的行。
分裂后放置分裂数据并插入主键值为50的行。
分裂后重新安排页之间的链接关系。
2.3 页合并
删除记时可能发生页合并，删除时采取软删除（置删除标记），页中置有删除标记的行数达到MERGE_THREHOLD（一般为50%，创建表或者创建索引时指定），则出发合并页操作，参与合并的页的数据会覆盖被合并页中置删除标记的那些行。
合并前
合并后
2.4 主键优化的原则
在业务允许的条件下，尽量降低主键长度。
插入数据尽量使用顺序插入，创建主键列时设置AUTO_IINCREMENT，以保证顺序插入记录
不要使用uuid（无序，长）、身份证号（长）、社保账号等自然主键作为主键
避免对主键的修改
3. order by子句优化
排序操作可能以两种形式进行：Using index和Using filesort。对索引列进行排序，由于索引列B+tree结构上叶子结点是有序的，因而返回有序结果是高效的。filesort方式需要再排序缓冲区里面进行排序操作再返回结果，开销大。
3.1 case1：单个字段排序的情况
-- Using filesort
explain select cust_id, cust_city from Customers order by cust_city;
/*
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | Customers | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
*/
-- Using index
create index idx_cust_city on Customers(cust_city);
explain select cust_id, cust_city from Customers order by cust_city;
/*
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-------------
| 1 | SIMPLE | Customers | NULL | index | NULL | idx_cust_city | 201 | NULL | 5 | 100.00 | Using index
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-------------
*/
-- 即使为cust_city创建了索引，查询的字段没有被表上的索引覆盖，则会回表，此时仍然会使用filesort方式
explain select * from Customers order by cust_city;
/*
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | Customers | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
*/
3.2 case2: 多个字段排序的情况
-- 排序字段单独的索引无法使得排序Using index，需要建立联合索引。cust_name有索引idx_cust_nam, cust_city有索引idx_cust_city
explain select cust_id, cust_name, cust_city from Customers order by cust_city, cust_name;
/*
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | Customers | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
*/
-- 建立联合索引idx_cust_name_city
create index idx_cust_name_city on Customers(cust_name, cust_city);
explain select cust_id, cust_name, cust_city from Customers order by cust_name, cust_city; -- Using index
-- 排序列的顺序不符合最左前缀原则导致先使用idx_cust_name_city排序cust_city，再在排序缓冲区进行cust_name字段的排序：Using index; Using filesort
explain select cust_id, cust_name, cust_city from Customers order by cust_city, cust_name;
3.3 case3：索引排序方向和查询指定的排序方式不同，排序仍使Using filesort
-- 查看索引idx_cust_name_city的各个字段排序方向: cust_name和csut_city的collation都为A，即Ascend
show index from Customers;
-- Using index; Using filesort: 先使用索引idx_cust_name_city按照cust_name升序排序（符合索引中的定义），再在排序缓冲区进行降序filesort（不符合索引中的定义）
explain select cust_id, cust_name, cust_city from Customers order by cust_name, cust_city desc;
-- 创建索引时指定列的排序方向
create index idx_cust_nameA_ageD on Customers(cust_name asc, cust_city desc);
-- Using index:使用索引idx_cust_nameA_ageD
explain select cust_id, cust_name, cust_city from Customers order by cust_name, cust_city desc;
3.4 小结
- 排序字段使用索引
- 使用覆盖索引方式，排序才能使用索引
- 查询中排序列的顺序要遵循最左前缀法则，否则部分字段使用索引排序，其余字段排序仍未filesort方式
- 不可避免使用filesort时，可以把排序缓冲区大小设置大一点。默认为256KB
-- 查看filesort缓冲区大小
show variables like 'sort_buffer_size';
/*
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
*/
4. group by子句优化
对未使用索引的列进行group by，MySQL会使用临时表来操作：创建临时表，扫描要查询的表的每一行，判断临时表中是否有对应的记录，如果没有则新增临时表记录，如果有则更新临时表中记录，更新存放聚合操作结果的列。注意group by子句对于索引列使用最左前缀原则要求分组列的顺序也和联合索引中列的顺序保持一致，这和where子句应用最左前缀原则不太一样，后者不要求顺序也一致。
-- 未使用索引，Using Tmeporary
explain select cust_email, count(*) from customers group by cust_email;
-- 创建联合索引
create index idx_cust_name_city_email on Customers(cust_name, cust_city, cust_email);
-- 使用联合索引，但不按照最左前缀法则进行分组: Using index; Using temporary
explain select cust_email, count(*) from customers group by cust_email;
-- 使用联合索引，但不按照最左前缀法则进行分组（和索引顺序不一致）: Using index; Using temporary
explain select cust_name, cust_city, count(*) from customers group by cust_city, cust_name;
-- 使用联合索引，按照最左前缀法则进行分组（索引列都出现在group by子句）：Using index
explain select cust_name, cust_city, count(*) from customers group by cust_name, cust_city;
explain select cust_name, cust_city, count(*) from customers group by cust_city, cust_name;
-- 使用联合索引，按照最左前缀法则进行分组（索引列分别出现在where子句和group by子句）：Using index
explain select cust_name, cust_city, count(*) from customers where cust_name= 'Fun4All' group by cust_city;
5. limit优化
深分页：当offset值很大时，全表扫描起来比较耗时。应当考虑使用主键索引优化。
-- 深分页
select count(*) from users; -- 100 0000
select * from users order by uid limit 0, 10; -- 0.02s
select * from users order by uid limit 999990, 10; -- 0.22s
-- 索引覆盖下分页效率
select uid from users order by uid limit 999990, 10; -- 0.16s
-- 连接表查询（自连接），从而获取全部列的数据
select u.* from users u inner join (select uid from users order by uid limit 999990, 10) t on u.uid = t.uid; -- 0.12
select u.* from users u, (select uid from users order by uid limit 999990, 10) t where u.uid = t.uid; -- 0.16
6. count函数优化
count函数的使用方式：count(非主键列), count(主键), count(*), count(1)
count(非主键列)：如果列允许NULL值，则该列的NULL值不被count统计；如果列不允许NULL值，则该列的count统计值可以代表全表记录总数。服务层从引擎层返回行中读取列值，再判断是否为NULL，然后计数。
count(主键)：主键不允许NULL值，count统计值代表记录总数。服务层从引擎层返回的行中取主键值，再累加。
count(*)：服务层不从引擎层读取的行中取值，直接累加。引擎对此表达式有优化，可能直接从表的元数据中读取数据返回，推荐使用。
count(1)：参数可以是任意整数，参数值不影响技术结果。逐行检查常量表达式是否为null来计数。
性能偏序：count(*) = count(-1) > count(主键) > count(列)
7. update优化
- 行级锁和表级锁：假设事务A中update操作先执行（筛选条件中的列不是索引列）而未提交，则其余事务对此表的update操作都会阻塞，直到事务A提交后释放表级锁。如果事务A的update操作筛选条件中列是索引列，则事务A执行该操作后未提交，其他事务update该表其他行不会阻塞，只有也update这一行才阻塞，这是应为生效的是行级锁。
- 建议使用索引列筛选进行update，这样可以避免使用表级锁而影响并发事务效率。
- 表数据
7.1 筛选条件中的列是索引列，启用行锁
开启事务A，执行update操作单未提交，在事务A内查看表Customers数据，update操作结果已经出现。但是在其他客户端处，这个操作结果是不存在的。
-- clientA
start transaction;
update Customers set cust_email = '111@xxx.com' where cust_id = '1000000002';
select * from Customers; -- 1000000002记录的cust_email='111@xxx.com'
-- clientB
start transaction;
select * from Customers; -- 1000000005记录的cust_email=NULL，维持原值
update Customers set cust_email = '222@xxx.com' where cust_id = '1000000005'; -- 修改成功，应用行锁，只要不对同一行就OK。同理事务A也看不到事务B这句更新操作
-- clientA
commit;
-- clientB
commit;
7.2 筛选条件中的列不是索引列，启动表锁
-- clientA
start transaction;
update Customers set cust_email = '222New@xxx.com' where cust_city = 'Chicago';
select * from Customers; -- cust_city='Chicago'的记录的cust_email='222New@xxx.com'
-- clientB
start transaction;
select * from Customers; -- cust_city='Chicago'的记录的cust_email='222@xxx.com'，维持原值
update Customers set cust_email = '111New@xxx.com' where cust_id = '1000000002'; -- 阻塞，直到事务A提交。
-- clientA
commit;
-- clientB
commit;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
