13 MYSQL 索引选择

宋涛发布于 2020-03-13 收录于存储

MySQL 如何选择索引

1. 优化器逻辑

选择索引是优化器的工作。而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。优化器会考虑扫描行数、是否使用临时表、是否排序等因素进行综合判断。对于普通索引来说，优化器需要把回表的代价算进去。

12 MySQL 问题追踪与性能优化

宋涛发布于 2020-03-12 收录于存储

MySQL 中怎么做问题追踪与性能优化

1. 问题排查的工具

mysql 中有以下几种问题排查的工具:

show processlist
show engine innodb status
information_schema.innodb_trx
show engine innodb status
optimizer_trace
慢查询日志
performance_schema 和 sys 系统库

1.1 试验环境

接下来我们以下面的实现环境看看，如何使用这些工具排查 MySQL 的性能问题

11 SQL 一些常见的错误用法

宋涛发布于 2020-03-11 收录于存储

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

试验环境

我们用下面两张表作为我们测试 SQL 用法的试验环境:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


# 交易信息表
mysql> CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 交易详情表
mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步骤*/
  `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.条件字段函数操作

类似下面的 SQL，对索引字段做了函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

10 MySQL如何显示随机消息

宋涛发布于 2020-03-10 收录于存储

random 存在哪些问题

背景

从一个单词表中随机选出三个单词。这个表的建表语句和初始数据的命令如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20



mysql> CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

1. 方法一: order by rand()

1.1 内存临时表

select word from words order by rand() limit 3; 这个语句的意思很直白，但执行流程却有点复杂的。

9 MYSQL 自增值的上限

宋涛发布于 2020-03-09 收录于存储

MySQL 里面的几种自增 id，它们的值达到上限以后，会出现什么情况。

1. 表定义自增值 id

表定义的自增值达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变。因此就会出现主键冲突错误。

8 insert 语句的锁

宋涛发布于 2020-03-08 收录于存储

insert select 为什么有这么多锁？

本节我们会介绍一些特殊的 insert 语句产生的锁:

insert … select 是很常见的在两个表之间拷贝数据的方法。你需要注意，在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁
如果 insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。
insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

1. insert … select 语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

在可重复读隔离级别下，binlog_format=statement 时, insert into t2(c,d) select c,d from t; 需要对表 t 的所有行和间隙加锁。原因还是日志和数据的一致性。