多维度mysql性能优化手段实践

NingKangMing

已于 2024-07-10 09:47:09 修改

阅读量1.6k

点赞数 42

分类专栏： mysql 文章标签： mysql 性能优化 adb mysql索引优化 mysql性能优化 SQL优化

于 2024-06-26 09:50:07 首次发布

本文链接：https://blog.csdn.net/u012882823/article/details/139978247

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

数据库优化维度有四个：硬件升级、系统配置、表结构设计、SQL语句及索引。

优化选择：

优化成本：硬件升级>系统配置>表结构设计>SQL语句及索引。
优化效果：硬件升级<系统配置<表结构设计<SQL语句及索引。

系统配置优化

保证从内存中读取数据

MySQL会在内存中保存一定的数据，通过LRU算法将不常访问的数据保存在硬盘文件中。尽可能的扩大内存中的数据量，将数据保存在内存中，从内存中读取数据，可以提升MySQL性能。扩大innodb_buffer_pool_size，能够全然从内存中读取数据。最大限度降低磁盘操作。

确定innodb_buffer_pool_size 足够大的方法：

mysql>  show global status like 'innodb_buffer_pool_page%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| Innodb_buffer_pool_pages_data    | 7163   |
| Innodb_buffer_pool_pages_dirty   | 0      |
| Innodb_buffer_pool_pages_flushed | 848994 |
| Innodb_buffer_pool_pages_free    | 1024   |  0则表示已用光
| Innodb_buffer_pool_pages_misc    | 4      |
| Innodb_buffer_pool_pages_total   | 8191   |
+----------------------------------+--------+

nnodb_buffer_pool_size默认为128M，理论上可以扩大到内存的3/4或4/5。

修改 my.cnf

innodb_buffer_pool_size = 750M

如果是专用的MySQL Server可以禁用SWAP

#查看swap
cat /proc/swaps
#关闭所有交换设备和文件.
swapoff -a

数据预热

默认情况，仅仅有某条数据被读取一次，才会缓存在 innodb_buffer_pool。所以，数据库刚刚启动，须要进行数据预热，将磁盘上的全部数据缓存到内存中。数据预热能够提高读取速度。

降低磁盘写入次数

增大redolog，减少落盘次数

innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size
通用查询日志、慢查询日志可以不开，bin-log开

生产中不开通用查询日志，遇到性能问题开慢查询日志
写redolog策略 innodb_flush_log_at_trx_commit设置为0或2

如果不涉及非常高的安全性 (金融系统)，或者基础架构足够安全，或者事务都非常小，都能够用 0或者 2 来减少磁盘操作。

提高磁盘读写性能

使用SSD或者内存磁盘。

表结构设计与优化

设计中间表

设计中间表，一般针对于统计分析功能，或者实时性不高的需求（OLTP、OLAP）。

设计冗余字段

为减少关联查询，创建合理的冗余字段（创建冗余字段还需要注意数据一致性问题）。

拆表

对于字段太多的大表，考虑拆表（比如一个表有100多个字段）

对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表

主键优化

每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布式系统的情况下雪花算法）。

字段的设计

数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

能用数字的用数值类型如：sex 1 0

SQL语句及索引优化

设计一个表：tbiguser

create database mytest;
use mytest;
create table tbiguser(id int primary key auto_increment,nickname varchar(255),loginname varchar(255),age int,sex char(1),status int, address varchar(255));

插入1千万的数据

CREATE PROCEDURE test_insert()
BEGIN DECLARE i INT DEFAULT 1;
WHILE i<=10000000
DO
insert into tbiguser VALUES(null,concat('zy',i),concat('zhaoyun',i),23,'1',1,'beijing');
SET i=i+1;
END WHILE;
commit;
END;

慢日志查询开启（获取慢查询）

使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句3秒-5秒

mysql> show variables like '%slow_query%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /opt/lib/mysql/dbserver0-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)

# 默认10秒
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
#命令设置开启
set global slow_query_log='ON';
#测试生成慢查询日志
select sleep(10);
#设置慢查询时间阈值
set global long_query_time=2;
flush privileges;

配置文件配置开启慢查询

[mysqld]
slow_query_log = ON
slow_query_log_file =/opt/lib/mysql/dbserver0-slow.log
long_query_time = 10

使用explain查看有问题的SQL的执行计划，重点查看索引使用情况

EXPLAIN查看索引使用情况

参考文档：

MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

使用explain查看有问题的SQL的执行计划，重点查看索引使用情况

mysql> explain select * from tbiguser where loginname='zhaoyun1' and nickname='zy1';
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | tbiguser | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10407544 |     1.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。下面连接类型按最好到最坏排序
- system
  
  表里面只有一行数据，这是const类型的特例
- const
  
  该表最多有一个匹配的行，在查询开始时读取它。因为只有一行，所以这行中列的值可以被优化器的其余部分视为常量。const表的速度非常快，因为它们只读取一次。通常是唯一索引查询，比如主键索引。
- eq_ref
  
  对于前表中的每个行组合，将从该表中读取一行。除了system和const类型之外，这是最好的连接类型。当连接使用索引的所有部分，且索引是主键或唯一的非空索引时，将使用它。
  
  eq_ref可以用于使用=操作符进行比较的索引列。比较值可以是常量，也可以是使用在此表之前读取的表中的列的表达式。在下面的例子中，mysql可以使用一个eq_ref连接来处理ref_table：
```
explain SELECT * FROM student,sc_score WHERE student.student_no=sc_score.student_no;
```
  结果
- ref
  
  对于前面表中的每个行组合，都将从此表中读取具有匹配索引值的所有行。如果联接仅使用键的最左前缀，或者键不是主键或唯一索引（换句话说，如果联接不能根据键值选择一行），则使用Ref。如果所使用的键只匹配几行，则这是一个很好的连接类型。ref可用于使用=或<=>操作符进行比较的索引列
  
  例子如下
- fulltext，全文索引
- ref_or_null
  
  跟ref类型类似，当条件添加了空值判断查询后，mysql会对包含空值的行进行额外的搜索。这种连接类型优化通常用于解析子查询。举例如下
- index_merge
  
  对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)
- unique_subquery
  
  unique_subquery只是一个索引查找函数，它完全替换子查询以提高效率。
- range，索引列进行范围查询通常会使用到此类型。
- index
  
  index连接类型与ALL类型相同，只是扫描索引树。
  - 如果该索引是查询的覆盖索引，并且可以用于满足表中所需的所有数据，则只扫描该索引树。在这种情况下，额外的列表示使用索引。仅进行索引的扫描通常比所有扫描都快，因为索引的大小通常小于表数据。
  - 使用索引读取执行完整表扫描，以索引顺序查找数据行。使用索引不出现在额外的列中
- ALL，全表扫描（整个聚簇索引扫描一次得到结果）

key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。
key_len列，索引长度。
rows列，扫描行数。该值是个预估值。
extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary 。

常见的索引：

where 字段、组合索引（最左前缀）、索引下推（非选择行不加锁）、覆盖索引（不回表）

on 两边、排序、分组统计

select_type

`select_type` Value	JSON Name	Meaning
`SIMPLE`	None	Simple SELECT (not using UNION or subqueries)
`PRIMARY`	None	Outermost SELECT
UNION	None	Second or later SELECT statement in a UNION
`DEPENDENT UNION`	`dependent` (`true`)	Second or later SELECT statement in a UNION, dependent on outer query
`UNION RESULT`	`union_result`	Result of a UNION.
SUBQUERY	None	First SELECT in subquery
`DEPENDENT SUBQUERY`	`dependent` (`true`)	First SELECT in subquery, dependent on outer query
`DERIVED`	None	Derived table
`MATERIALIZED`	`materialized_from_subquery`	Materialized subquery
`UNCACHEABLE SUBQUERY`	`cacheable` (`false`)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
`UNCACHEABLE UNION`	`cacheable` (`false`)	The second or later select in a UNION that belongs to an uncacheable subquery (see `UNCACHEABLE SUBQUERY`)

SQL语句中IN包含的值不应过多

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。

SELECT语句务必指明字段名称

SELECT * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；减少了使用覆盖索引的可能性（如果非主键查询，肯定需要回表，这样相当于走了一次普通索引，又走了一次主键索引，效率必定降低）；当表结构发生改变时，前端也需要更新。所以要求直接在select后面接上字段名。

mysql> explain select * from tbiguser;
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------+
|  1 | SIMPLE      | tbiguser | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10407544 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table tbiguser add index idx_nickname(nickname);
Query OK, 0 rows affected (27.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select id,nickname from tbiguser;
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | tbiguser | NULL       | index | NULL          | idx_nickname | 258     | NULL | 10407544 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id,nickname from tbiguser where id=1;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tbiguser | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

当只需要一条数据的时候，使用limit 1

limit 是可以停止全表扫描的,下面的结果虽然explain的type是all，但事实是结果出来是很快的。

mysql> select * from tbiguser limit 1;
+----+----------+-----------+------+------+--------+---------+
| id | nickname | loginname | age  | sex  | status | address |
+----+----------+-----------+------+------+--------+---------+
|  1 | zy1      | zhaoyun1  |   23 | 1    |      1 | beijing |
+----+----------+-----------+------+------+--------+---------+
1 row in set (0.00 sec)

mysql> explain select * from tbiguser limit 1;
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------+
|  1 | SIMPLE      | tbiguser | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10407544 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------+
1 row in set, 1 warning (0.00 sec)

排序字段加索引

mysql> explain select * from tbiguser where loginname = 'zhaoyun9999999' order by id;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | tbiguser | NULL       | index | NULL          | PRIMARY | 4       | NULL | 10407544 |    10.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from tbiguser where loginname = 'zhaoyun9999999' order by loginname;
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | tbiguser | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10407544 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，会造成该查询不走索引的情况。

下面的nickname加了索引，而loginname没加索引

mysql> explain select * from tbiguser where nickname='zy1' or loginname='zhaoyun3';
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | tbiguser | NULL       | ALL  | idx_nickname  | NULL | NULL    | NULL | 10407544 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tbiguser where nickname='zy1';
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tbiguser | NULL       | ref  | idx_nickname  | idx_nickname | 258     | const |    2 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from tbiguser where nickname='zy1';
+----------+----------+-----------+------+------+--------+---------+
| id       | nickname | loginname | age  | sex  | status | address |
+----------+----------+-----------+------+------+--------+---------+
|        1 | zy1      | zhaoyun1  |   23 | 1    |      1 | beijing |
| 10000001 | zy1      | zhaoyun1  |   23 | 1    |      1 | beijing |
+----------+----------+-----------+------+------+--------+---------+
2 rows in set (0.00 sec)

mysql> select * from tbiguser where nickname='zy1' or loginname='zhaoyun3';
+----------+----------+-----------+------+------+--------+---------+
| id       | nickname | loginname | age  | sex  | status | address |
+----------+----------+-----------+------+------+--------+---------+
|        1 | zy1      | zhaoyun1  |   23 | 1    |      1 | beijing |
|        3 | zy3      | zhaoyun3  |   23 | 1    |      1 | beijing |
| 10000001 | zy1      | zhaoyun1  |   23 | 1    |      1 | beijing |
| 10000003 | zy3      | zhaoyun3  |   23 | 1    |      1 | beijing |
+----------+----------+-----------+------+------+--------+---------+
4 rows in set (5.39 sec)

可以看出，or后面加了没有索引的查询字段，查询马上变成了全表扫描。

6.尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

7.不使用ORDER BY RAND()

ORDER BY RAND() 不走索引

mysql> select * from tbiguser order by rand() limit 10;
+---------+-----------+----------------+------+------+--------+---------+
| id      | nickname  | loginname      | age  | sex  | status | address |
+---------+-----------+----------------+------+------+--------+---------+
| 2772842 | zy2772842 | zhaoyun2772842 |   23 | 1    |      1 | beijing |
| 1506958 | zy1506958 | zhaoyun1506958 |   23 | 1    |      1 | beijing |
| 7772785 | zy7772785 | zhaoyun7772785 |   23 | 1    |      1 | beijing |
| 3376173 | zy3376173 | zhaoyun3376173 |   23 | 1    |      1 | beijing |
| 8528068 | zy8528068 | zhaoyun8528068 |   23 | 1    |      1 | beijing |
| 9404548 | zy9404548 | zhaoyun9404548 |   23 | 1    |      1 | beijing |
| 7221073 | zy7221073 | zhaoyun7221073 |   23 | 1    |      1 | beijing |
|  587473 | zy587473  | zhaoyun587473  |   23 | 1    |      1 | beijing |
| 5010876 | zy5010876 | zhaoyun5010876 |   23 | 1    |      1 | beijing |
| 5350847 | zy5350847 | zhaoyun5350847 |   23 | 1    |      1 | beijing |
+---------+-----------+----------------+------+------+--------+---------+
10 rows in set (28.33 sec)

mysql> select * from tbiguser t1 join (select rand()*(select max(id) from tbiguser) nid) t2 on t1.id>t2.nid limit 10;
+---------+-----------+----------------+------+------+--------+---------+-------------------+
| id      | nickname  | loginname      | age  | sex  | status | address | nid               |
+---------+-----------+----------------+------+------+--------+---------+-------------------+
| 5958786 | zy5958786 | zhaoyun5958786 |   23 | 1    |      1 | beijing | 5958785.582203735 |
| 5958787 | zy5958787 | zhaoyun5958787 |   23 | 1    |      1 | beijing | 5958785.582203735 |
| 5958788 | zy5958788 | zhaoyun5958788 |   23 | 1    |      1 | beijing | 5958785.582203735 |
| 5958789 | zy5958789 | zhaoyun5958789 |   23 | 1    |      1 | beijing | 5958785.582203735 |
| 5958790 | zy5958790 | zhaoyun5958790 |   23 | 1    |      1 | beijing | 5958785.582203735 |
| 5958791 | zy5958791 | zhaoyun5958791 |   23 | 1    |      1 | beijing | 5958785.582203735 |
| 5958792 | zy5958792 | zhaoyun5958792 |   23 | 1    |      1 | beijing | 5958785.582203735 |
| 5958793 | zy5958793 | zhaoyun5958793 |   23 | 1    |      1 | beijing | 5958785.582203735 |
| 5958794 | zy5958794 | zhaoyun5958794 |   23 | 1    |      1 | beijing | 5958785.582203735 |
| 5958795 | zy5958795 | zhaoyun5958795 |   23 | 1    |      1 | beijing | 5958785.582203735 |
+---------+-----------+----------------+------+------+--------+---------+-------------------+
10 rows in set (0.01 sec)

8.区分in和exists、not in和not exists

MySQL（七）｜MySQL中In与Exists的区别（1） - 云+社区 - 腾讯云

创建一个100万的表作为小表（相对于1000万的大表而言）

CREATE TABLE `tsmalluser` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` varchar(255) DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`userId`) USING BTREE
)

添加数据

CREATE PROCEDURE test_small_insert()
BEGIN DECLARE i INT DEFAULT 1;
WHILE i<=1000000
DO
insert into tsmalluser VALUES(null,concat('beijing',i),i);
SET i=i+1;
END WHILE;
commit;
END;

exists

exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时（无论记录行是的多少，只要能返回），条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false

如下：

select * from tsmalluser where exists (select 1);

对tsmalluser表的记录逐条取出，由于子条件中的select 1永远能返回记录行，那么user表的所有记录都将被加入结果集，所以与select * from tsmalluser;是一样的。

又如下：

select * from tsmalluser where exists (select * from tsmalluser where id = 0);

可以知道对tsmalluser表进行loop时，检查条件语句(select * from user where id = 0),由于id永远不为0，所以条件语句永远返回空集，条件永远为false，那么tsmalluser表的所有记录都将被丢弃。

总结：如果A表有n条记录，那么exists查询就是将这n条记录逐条取出，然后判断n遍exists条件。

in查询相当于多个or条件的叠加，这个比较好理解，比如下面的查询：

select * from user where user_id in (1, 2, 3);

等效于

select * from user where user_id = 1 or user_id = 2 or user_id = 3;

总结：in查询就是先将子查询条件的记录全都查出来，假设结果集为B，共有m条记录，然后再将子查询条件的结果集分解成m个，再进行m次查询。

使用上的区别

in查询的子条件返回结果必须只有一个字段，例如

select * from user where user_id in (select id from B);

而exists就没有这个限制。

EXISTS和IN的性能分析

in和exists查询性能分析

SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);

假设B表的所有id为(1,2,3)，查询1可以转换为：

SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
SELECT * FROM A WHERE A.id = 1 OR A.id = 2 OR A.id = 3;

这里主要是用到了A的索引，B表如何对查询影响不大。

explain select * from tsmalluser where userId in(select id from tbiguser);

explain extended select * from tsmalluser where userId in(select id from tbiguser);

exists

查询2可以转化以下伪代码：

SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);
转化为如下伪代码

for (i = 0; i < count(A); i++) {
    a = get_record(A, i); #从A表逐条获取记录
    if (B.id = a[id]) { #如果子条件成立
        result[] = a;
    }
}
return result;

这里主要用到了B表的索引，A表如何对查询的效率影响不大。

如下查询：

 explain select * from tsmalluser where exists(select id from tbiguser where id=tsmalluser.userId);

结论

Mysql中的in语句是把外表和内表作join连接，而exists语句是对外表作nest loop循环，每次loop循环再对内表进行查询。

通过以上分析，很容易得出下面的结论：

1、如果查询的两个表大小相当，那么用in和exists差别不大。

2、如果两个表中一个表大，另一个是表小，那么IN适合于外表大而子查询表小的情况。

3、如果两个表中一个表大，另一个是表小，EXISTS适合于外表小而子查询表大的情况。

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？

原SQL语句：

select colname ... from A表 where a.id not in (select b.id from B表)

高效的SQL语句：

select colname ... from A表 Left join B表 on where a.id = b.id where b.id is null