Mysql之索引
1. 索引的设计原则
1. 索引列一般为where子句的列或者连接子句的列。
2. 尽量不对基数小的列做索引，如性别列。因为索引会影响插入更新。
3. 尽可能使用短索引：如果对字符列索引尽量指定最小长度的那一列。
最好是整型int。
4. 复合索引前缀特性，索引的顺序很重要。
创建复合索引最常用限制条件列放在最左边，依次递减。
5. 避免出现无用的索引。
INNODB：尽量指定主键，最常用较短数据类型为唯一主键。
尽量使用定长字符串类型，而不用varchar.
2. 避免过度使用索引
1. 索引的建立对提高检索能力很有用，但是数据库维护它很费资源。
2. 对性别列索引，被称为过度索引。只有两个值，建索引不仅没优势，还会影响插入更新速度。
3. 索引会占用磁盘空间，降低更新操作性能，且执行计划要考虑各个索引。
4. 索引不是越多越好。
5. 行数比较少的表可以不建索引。
3. 索引
1. 索引是一种排好序的数据结构，可以帮助mysql高效获取数据的数据结构。
2. 目的：提高查询效率
3. 一般索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上
4. 索引优势和劣势
1. 优势
提供数据检索的效率，降低数据库IO成本。
通过索引列队数据排序，降低数据排序的成本，降低CPU的消耗。
2. 劣势
索引也是一张表，该表保存了主键与索引字段，指向实体数据的记录。索引是占用空间的。
索引大大提高查询表的速度，同时降低更新表的速度，因为更新表数据，不仅更新表数据，还更新了索引。
索引只是提高效率的一个因素。
5. 索引的分类
一张表最好不超过5个索引
单值索引: 一个索引只包含一个列，一个表可以有多个单值索引
唯一索引: 索引列的值必须唯一，但允许有空值
复合索引: 一个索引包含多个列
覆盖索引: 从辅助索引中就可以得到查询的记录，而不需要查询聚焦索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息
6. 索引的基本语法
创建索引
1. 创建表时创建索引
1. 语法： create TABLE table_name [col_name data_type] [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC | DESC]
UNIQUE|FULLTEXT|SPATIAL 分别表示唯一索引，全文索引和空间索引
INDEX|KEY 同义词，用来指定索引
col_name 需要创建索引的字段列
index_name 指定索引的名称，不指定默认是字段名
length 索引的长度，只有字符串类型的字段才能指定索引长度
ASC|DESC 指定升序或降序的索引值存储
2. 创建普通索引
create table tb3(num3 int not null,name varchar(20) not null,INDEX(num3) );
3. 创建唯一索引
create table tb3(num3 int not null,name varchar(20) not null,UNIQUE INDEX weiyi_num3(num3) );
4. 创建单列索引
在某个字段上创建的索引，一个表中可以创建多个单列索引。
create table tb3(num3 int not null,name varchar(20) not null,INDEX danlie_num3(num3) );
5. 创建组合索引
create table tb3(num3 int not null,name varchar(20) not null,INDEX zuhe_num3(num3,name) );
6. 创建全文索引
只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR,VARCHAR,TEXT列创建索引
create table tb3(num3 int not null,name varchar(20) not null,FULLTEXT INDEX quanwen_name(name) ) ENGINE=myisam;
7. 创建空间索引
空间索引必须在MyISAM类型的表中创建，且空间类型的字段必须为非空
create table tb3(num3 int not null,name varchar(20) not null,g GEOMETRY not null,SPATIAL INDEX kongjian_name(g) ) ENGINE=myisam;
2. 在已经存在的表上创建索引
1. 使用ALTER TABLE语句创建索引
语法： alter TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC | DESC]
1. 创建普通索引
alter table tb3 add INDEX num3(num3);
2. 创建唯一索引
alter table tb3 add UNIQUE INDEX num3(num3);
3. 创建单列索引
alter table tb3 add INDEX danlie_num3(num3);
4. 创建组合索引
alter table tb3 add INDEX zuhe_num3(num3,name);
5. 创建全文索引
alter table tb4 add FULLTEXT INDEX quanwen_name(name);
6. 创建空间索引
alter table tb4 add SPATIAL INDEX kongjian_g(g);
2. 使用CREATE INDEX创建索引
语法： CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col_name [length]) [ASC | DESC]
1. 创建普通索引
create index num3 on tb3(num3);
2. 创建唯一索引
create unique index weiyi_num3 on tb3(num3);
3. 创建单列索引
create index danlie_num3 on tb3(num3);
4. 创建组合索引
create index zuhe_num3 on tb3(num3,name);
5. 创建全文索引
create FULLTEXT index quanwen_name on tb3(name);
6. 创建空间索引
create SPATIAL index kongjian_g on tb3(g);
删除索引
1. 使用ALTER TABLE删除索引
语法： ALTER TABLE table_name DROP INDEX index_name;
alter table tb3 drop index num3;
alter table tb3 drop index danlie_num3;
2. 使用DROP INDEX删除索引
语法： drop INDEX [indexname] on mytable;
drop index weiyi_num3 on tb3;
drop index zuhe_num3 on tb3;
查看索引
show index from mytable\G;
Table: 索引所在的表名
Non_unique: 非唯一索引
Key_name: 索引名称
Seq_in_index: 索引中该列的位置
Column_name: 索引列的名称
Collation: 列以什么方式存储在索引中。可以是A或NULL
cardinality: 索引中唯一值的数目的估计值，
sub_part: 是否是列的部分被索引。如果索引整个列，则为NULL。
packed: 关键字如何被压缩。
Null: 索引列是否含有空值
Index_type: B+都是BTREE
comment: 注释
7. 索引结构与检索原理
1. 算法
1. 二分查找法
用来查找一组有序的记录数组中的某一记录，以有序数组的中间点为比较对象，如果要找的元素值小于该中间点元素，则将待查询序列缩小为左半部分，否则为右半部分。
例子：
有5，10，19，21，31，37，42，48，50，42这10个数，要从这10个数中查找48这条记录，二分法查找过程。
如果使用顺序查找，则需要8次才能找到；而使用二分查找法，用3次就找到了。
2. 二叉查找树和平衡二叉树
1. 二叉查找树
在二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。
使用二叉查找树，查找8这个记录，需要3次；如果使用顺序方式需要查找6次
如果二叉查找树不平衡，那么效率也是低下的。如下图所示
于是就引出了新的定义：平衡二叉树
2. 平衡二叉树
平衡二叉树首先要符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度最大差为1
3. B+树
1. B+树的插入操作
例子一：
用户插入28键值，发现Leaf Page和Index Page都没有满，直接进行插入即可。
例子二：
用户插入70键值，原来的Leaf Page已满，但是Index Page还没有满，这时插入Leaf Page的情况为50,55,60.65,70，并根据中间的值60来拆分叶子节点
例子三：
插入键值95，Leaf Page和Index Page都满了，这时需要做两次拆分
2. B+树的删除操作
B+树使用填充因子(fill factor)来控制树的删除变化
例子一：
首先删除键值70的记录，删除后的结果
例子二：
删除Leaf page的25键值，还应将25的右兄弟节点的28更新到Index Page中
例子三：
删除键值60的情况，删除Leaf Page中键值为60的记录后，Fill Factor小于50%，这时需要做合并操作，同时在删除Index Page中相关记录后需要做Index Page的合并操作
4. B+树索引
1. 聚焦索引
每张表只能拥有一个聚焦索引
聚焦索引是逻辑上连续存放数据
聚焦索引对于主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。
2. 非聚焦索引
叶子节点并不包含行记录的全部数据，除了包含键值外，每个叶子节点中的索引行中还包含了一个书签（相应行数据的聚焦索引键）
Hash索引
full-text全文索引
R-Tree索引
8. 什么情况适合建索引
主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
频繁更新的字段不适合建索引
where条件里用不到的字段不创建索引
查询中排序的字段，排序字段若通过索引去访问大大提高排序速度
查询中统计或者分组字段
9. 哪些情况不适合建索引
表记录太少
经常增删改的表
数据重复的字段
选择率算法：一个字段有2000条记录，其中有1980个不同值，这个索引的选择性为：1980/2000=0.99，接近1，这个索引的效率就越高。
10. explain调优
创建两张测试表
CREATE TABLE t1 ( id INT NOT NULL AUTO_INCREMENT, key1 VARCHAR (100), key2 VARCHAR (100), key3 VARCHAR (100), NAME VARCHAR (100), PRIMARY KEY (id), KEY idx_key1 (key1), KEY idx_key2_key3 (key2, key3) ) ENGINE = INNODB CHARSET = utf8; CREATE TABLE t2 ( id INT NOT NULL AUTO_INCREMENT, key1 VARCHAR (100), key2 VARCHAR (100), key3 VARCHAR (100), NAME VARCHAR (100), PRIMARY KEY (id), KEY idx_key1 (key1), KEY idx_key2_key3 (key2, key3) ) ENGINE = INNODB CHARSET = utf8;
1. 用法
explain+sql语句
mysql> explain select * from t1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
2. 结果表的各个字段的意思
1. id 一组数字，标记执行语句的顺序
id相同，执行顺序由上至下
id不同，id值越大优先级越高，越先执行
id相同又不同，id如果相同，可以认为是一组，执行顺序由上至下，在所有组中，id越大，越优先执行，id为NULL最后执行
1. 单个表
mysql> explain select * from t1 where key1='abc'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) 只有一个select时，只会出现一个id
2. 两个表
mysql> explain select * from t1 inner join t2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ 2 rows in set, 1 warning (0.00 sec) 一个SELECT关键字后边的FROM子句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的
出现在前面的表是驱动表，出现在后面的表是被驱动表
3. 子查询
mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2) OR key3 = 'a1b6cee57a'; +----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+-------------+ | 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t2 | NULL | index_subquery | idx_key1 | idx_key1 | 303 | func | 1 | 100.00 | Using index | +----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) 从输出结果中我们可以看到，t1表在外层查询中，外层查询有一个独立的SELECT关键字，所以第一条记录的id值就是1，t2表在子查询中，子查询有一个独立的SELECT关键字，所以第二条记录的id值就是2。
4. UNION子句
mysql> EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2; +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 2 | UNION | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec) UNION子句是为了把id为1的查询和id为2的查询的结果集合并起来并去重，所以在内部创建了一个名为<union1, 2>的临时表（就是执行计划第三条记录的table列的名称），id为NULL表明这个临时表是为了合并两个查询的结果集而创建的。
5. UNION ALL子句
mysql> EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 2 | UNION | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) 跟UNION对比起来，UNION ALL就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，所以也就不需要使用临时表。所以在包含UNION ALL子句的查询的执行计划中，就没有那个id为NULL的记录
2. select_type
SIMPLE: 简单的select查询，查询中不包含子查询或者UNION。无论查询语句多么复杂，执行计划中select_type为SMIPLE的查询一定只有1个。最外侧SELECT查询的select_type通常为SIMPLE。
mysql> explain select * from t1 where key1='abc'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
PRIMARY: 对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY
执行计划中select_type为PRIMARY的查询一定只有1个
mysql> EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 2 | UNION | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec)
SUBQUERY: 在SELECT或者WHERE列表中包含了子查询
DERIVED: 在FROM列表中包含的子查询被标记为DERIVED(衍生)，Mysql会递归执行这些子查询，把结果放在临时表里。
UNION: 对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION，可以对比上一个例子的效果
UNION RESULT: MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT，同样对比上面的例子
3. table 显示哪张表
1. 如果是单表只显示一行记录
2. 如果是连接查询显示两行记录
3. 如果from子句中有子查询，table列是<derivenN>格式，表示当前查询依赖id=N的查询
4. type 访问类型(查看是否需要添加索引)
ALL index range ref eq_ref const system null
从最好到最差依次：system>const>eq_ref>ref>range>index>ALL
system: 表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略。
const: 表示通过索引一次就找到了，const用于primary key或者unique索引，因为只匹配一行数据，所以很快
eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。
ref: 非唯一性索引扫描，返回匹配某个单独值的所有行。
range: 只检索给定范围的行，使用一个索引来选择行。一般就是在你的where语句中出现了between,>,<,in,like的查询
index: 只遍历索引树。这通常比ALL快.虽然ALL和index都是读全表，但是index是从索引中读取的，而all是从硬盘中读取的。
ALL: 遍历全表以找到匹配的行
5. possible_keys 显示可能应用在这个表的索引，一个或多个
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。
6. keys key列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index
7. key_len 索引字段的最大可能长度，并非实际使用长度，数值越小，表示越快
key_len计算规则如下：字符串 char(n)：n字节长度 varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2 数值类型 tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节 时间类型 date：3字节 timestamp：4字节 datetime：8字节
mysql> explain select * from t1 where key1='a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) 由于key1列的类型是VARCHAR(100)，所以该列实际最多占用的存储空间就是300字节，又因为该列允许存储NULL值，所以key_len需要加1，又因为该列是可变长度列，所以key_len需要加2，所以最后ken_len的值就是303。
8. ref 显示索引哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
9. rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数,注意：这个不是结果集的行数
10. extra 不适合在其它列中显示，但是十分重要的额外信息
using index 查询的列被索引覆盖，并且where筛选条件是索引的前导列，是性能高的表现。一般是使用了覆盖索引(索引包含了所有查询的字段)。
using filesort 只有无法使用索引处理order by时，执行计划的Extra列才显示为using filesort。这意味着将访问的记录复制到用于排序的内存缓冲区，然后采用快速排序算法进行排序。
尽可能避免出现filesort，因为会对系统带来很重的负荷。
using temporary 使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by.
常见使用临时表的查询
1. from子句中的子查询必定创建临时表
2. 含有count(distinct column1)的查询无法使用索引时，也会创建临时表
3. 使用union或union all的查询也总是使用临时表合并结果
4. 不能使用索引的排序操作也会使用临时缓冲空间，其执行计划的Extra列显示using filesort
using where 当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。
using where ; using index 查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据
mysql> EXPLAIN SELECT id FROM t1 WHERE key3= 'a1b6cee57a'; +----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t1 | NULL | index | NULL | idx_key2_key3 | 606 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
using join buffer 使用了连接缓存
impossible where where子句的值总是false,不能用来获取任何元组
11. 索引案例
1. 单表
1. 创建表
CREATE TABLE IF NOT EXISTS article( id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, author_id INT(10) UNSIGNED NOT NULL, category_id INT(10) UNSIGNED NOT NULL, views INT(10) UNSIGNED NOT NULL, comments INT(10) UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, content TEXT NOT NULL );
2. 插入数据
INSERT INTO article(author_id,category_id,views,comments,title,content) VALUES (1,1,1,1,'1','1'), (2,2,2,2,'2','2'), (1,1,3,3,'3','3');
3. 查询案例
查询category_id为1且comments 大于1的情况下，views最多的id和author_id.
select id,author_id from article where category_id=1 and comments>1 order by views limit 1;
分析一下上面的语句 ： explain select id,author_id from article where category_id=1 and comments>1 order by views limit 1; 
结论： type是ALL,并且Extra出现了using filesort。优化是必须的。
创建索引： create INDEX index_ccv on article(category_id,comments,views);
再次分析sql语句，结果如下：
EXPLAIN select id,author_id from article where category_id=1 and comments>1 order by views limit 1;
结论：type已经是range，但是extra仍然有using filesort，所以这个索引不适合这个sql语句。因为comments是一个范围查询。MySQL 无法利用索引再对后面的views部分进行检索。
删除这个索引，再次创建索引。
drop INDEX index_ccv on article;
create index index_cv on article(category_id,views);
再次分析sql语句，结果如下：
可以看到这个效果就好很多了
2. 两表联查
1. 创建表
CREATE TABLE IF NOT EXISTS class( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(id) ); CREATE TABLE IF NOT EXISTS book( bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(bookid) );
2. 插入数据
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
3. 案例分析
实现两表的连接，连接条件是class.card=book.card
select * from class left JOIN book on class.card=book.card;
explain select * from class left JOIN book on class.card=book.card;
结果分析：因为没有任何索引，所以type都显示为ALL,对表进行了全扫描，所以需要添加索引。
1. 在右表添加索引
create index index_c on book(card);
分析一下，
结论分析：book表的查询类型为ref，效率比没有索引好很多。
2. 在左表添加索引
drop index index_c on book;
create index index_c on class(card);
分析一下，
结论：左表加了索引以后，并没有提升效率，所以使用左连接的时候，在右表添加索引，效果会更好。
3. 改成右连接以后呢？
因为right join用于从左表进行搜索，右表一定有，所以要在左表添加索引。
3. 三表联查
1. 创建表
CREATE TABLE IF NOT EXISTS phone( phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(phoneid) )ENGINE=INNODB;
2. 插入数据
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
3. 案例分析
三表连接查询
select * from class LEFT JOIN book on class.card=book.card LEFT JOIN phone on phone.card=book.card;
explain select * from class LEFT JOIN book on class.card=book.card LEFT JOIN phone on phone.card=book.card;
因为使用的是左连接，所以在右表建立索引。
create index index_c on book(card);
create index index_c on phone(card);
再执行explain分析一下，
12. 索引优化及如何避免索引失效
1. 创建表，插入数据，添加索引
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
2. 全值匹配我最爱
1. 查询条件只有name字段
explain select * from staffs where name='July';
2. 查询条件只有name和age字段
explain select * from staffs where name='July' and age=23;
3. 查询条件name和age和pos字段
explain select * from staffs where name='July' and age=23 and pos='dev';
可以看到ref列显示为const，如果匹配的越精确，key_len的值越大。
3. 最左前缀法则
1. 查询条件以name字段开头，会发现正常使用索引
explain select * from staffs where name='July';
2. 查询条件以age开头，就无法使用索引,索引失效
explain select * from staffs where age=23; 
3. 查询条件以pos开头，也无法使用索引，索引失效
explain select * from staffs where pos='dev'; 
4. 查询条件以name开头，但是顺序不是age,pos,虽然使用了索引，但是ref中没有出现两个const
explain select * from staffs where name='July' and pos='dev';
4. 不在索引列上做任何操作(计算，函数，自动或手动类型转换，会导致索引失效而转向全表扫描)
1. EXPLAIN SELECT * FROM staffs WHERE name = 'July';
正常查询可以看到使用了index。
2. 在索引列上加一个函数，EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4) = 'July';
在索引列上使用函数以后，会发现索引失效了，现在变成了全表扫描。
5. 不能使用索引中范围条件右边的列
1. explain select * from staffs where name='July' and age >20 and pos='dev';
因为age使用了范围条件>，所以导致索引无法使用pos字段，索引失效了。
6. 尽量使用覆盖索引（索引列和查询列保持一致），减少select *
1. explain select name,age,pos from staffs where name='July' and age =23 and pos='dev';
2. explain select name,age,pos from staffs where name='July' and age >23 and pos='dev';
如果用的select *，如果条件里面有范围查询，索引会失效，但是如果使用了覆盖索引，索引不会失效，效果更好。
7. mysql使用!=或者<>的时候会导致索引失效
1. explain select * from staffs where name!='July';
2. explain select * from staffs where name<>'July'; 
以上两种情况都无法使用索引
8. is null也会导致索引失效
1. explain select * from staffs where name is null; 
2. explain select * from staffs where name is not null;
9. like以通配符开头会导致索引失效
1. explain select * from staffs where name like '%July%';
会看到索引已经失效了，全表扫描。
2. explain select * from staffs where name like 'July%'; 
但是%放到右面，就不会出现索引失效的情况。
3. 解决like '%字符%'索引失效的解决方法
1. 创建表并插入数据
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2aa2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3aa3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4aa4',26,'d@163.com');
2. 模拟生产线上不得不用%%两个百分号的情况
1. 如果把%放到右边，那么我查到的结果是：
搜索不到我想要的结果。
2. 建立索引之前，查看下面语句的执行记录。
可以看到所有的查询都没有使用索引，因为没有创建索引。
3. 创建索引
create index index_name_age on tbl_user (name,age);
4. 再次执行第二步的sql
可以看到，因为我添加了覆盖索引，所以含有索引列的sql语句，都会使用覆盖索引；但是只要包含了非索引列，就不能使用覆盖索引。后面两条就是例子。
10. 字符串不加单引号会导致索引失效
1. 正常情况下的sql
2. name列为varchar类型，如果不带单引号，会导致隐性的类型转换，索引就失效了。
11. 少用or进行连接，会导致索引失效
1. 条件中加入or
13. 索引面试题分析
1. 创建表并插入数据
create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');
2. 创建索引
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
3. 问题
1. 修改条件的顺序，会不会有什么影响？
1. EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';
使用了全值匹配，可以看到ref有四个常量。
2. EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';
mysql优化器会进行自我调优，索引我们的索引都生效了。
结论：虽然mysql优化器会进行自我调优，最好还是按照索引列的顺序，输入条件列。
2. sql语句的条件中，出现范围查询。
1. EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4';
结论：c3使用了索引进行排序，并没有查找，导致c4无法使用索引进行查找。
3. sql语句的条件中，出现范围查找，并且与索引列顺序不匹配。
1. EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3';
结论：mysql优化器进行了调优，所以我们的索引都生效了，在c4进行了范围搜索。
4. sql语句中查询条件少一个索引列
1. EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3;
结论：因为条件中断了，导致只是用c1和c2，但是c3使用索引进行了排序，c4没有使用到索引。
5. sql语句中查询条件只有前两个
1. EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3;
结论：因为条件中没有c3，c4，所以c3,c4不会使用索引，但是会使用c3进行排序。
6. sql语句中使用了c4进行了排序
1. EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4;
结论：排序列要与索引列对应，才能使用索引进行排序，否则就会出现filesort。
7. sql语句中条件列中有c1和c5，但是使用c2，c3进行排序
1. EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3;
结论：使用c1进行查找，使用c2,c3进行排序
8. sql语句中条件列中有c1和c5，但是使用c3，c2进行排序
1. EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2;
结论：使用c1进行查找，但是排序的时候，order by顺序与索引顺序不匹配，出现了filesort.
9. sql语句中条件列为c1和c2，使用c2,c3进行排序
1. EXPLAIN SELECT * FROM test03 WHERE c1='a1' and c2='a2' ORDER BY c2,c3;
结论：使用c1和c2进行查找，使用c2,c3进行排序。
10. sql语句中条件列为c1,c2,c5，使用c2,c3进行排序
1. EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3;
结论：使用c1和c2进行查找，使用c2,c3进行排序。
11. sql语句中条件列为c1,c2,c5，使用c3,c2进行排序
1. EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2;
结论：使用c1和c2进行查找，使用c3直接进行排序，因为c2是个常量，都已经查找（不需要再排序了）
12. sql语句条件列为c1,c4，使用c2,c3进行分组
1. EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3;
结论：使用c1进行了查找，再使用group by之前要进行排序，所以使用c2,c3进行了排序。
2. EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2;
结论：使用c1进行了查找，但是在分组之前必须排序，结果排序顺序与索引顺序不匹配，导致出现temporary和filesort。
14. 总结表
全值匹配我最爱， 最左前缀要遵守；
带头大哥不能死， 中间兄弟不能断；
索引列上少计算， 范围之后全失效；
LIKE 百分写最右， 覆盖索引不写 *；
不等空值还有 OR， 索引影响要注意；
VAR 引号不可丢， SQL 优化有诀窍。
15. 查询优化
1. 查询优化的常用策略
1. 优化数据访问
应用程序减少对数据库的数据访问
应用程序可以访问缓存数据，就不需要从数据库中直接读取数据
数据库应减少实际扫描的记录数
如果应用程序只需要其中几列的数据，没必要把所有列的数据都读取出来
2. 重写SQL
把复杂的查询分解为多个简单的查询
3. 重新设计库表
4. 添加索引
2. 优化器加提示
1. 使用索引
SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3; 表示建议使用索引名为col1_index或col2_index的索引检索表
2. 不使用索引
SELECT * FROM table1 IGNORE INDEX (col3_index) WHERE col1=1 AND col2=2 AND col3=3;
3. 强制使用索引
SELECT * FROM table1 FORCE INDEX (col3_index) WHERE col1=1 AND col2=2 AND col3=3;
注：USE INDEX、 IGNORE INDEX和FORCE INDEX这些提示方式只会影响MySQL在表中检索记录或连接要使用的索引， 它们并不会影响ORDER BY或GROUP BY子句对于索引的选择
4. 不使用查询缓冲
5. 使用查询缓冲
6. STRAIGHT_JOIN
3. 小表驱动大表
优化原则：小表驱动大表，即小的数据集驱动大的数据集
例子：
select * from A where id in (select id from B)
等价于：
for select id from B
for select * from A where A.id=B.id
当B表的数据集必须小于A表的数据集时，用in优于exists
select * from A where exists (select 1 from B where B.id=A.id)
等价于：
for select * from A
for select * from B where B.id=A.id
当A表的数据集小于B表的数据集时，用exists优于in。
4. 各种语句优化
1. 连接的优化
1. ON,USING子句中的列确认有索引。如果优化器选择了连接的顺序为B、 A， 那么我们只需要在A表的列上创建索引即
可。 例如， 对于查询“SELECT B.*,A.*FROM B JOIN A ON B.col1=A.col2;”语句MySQL会全表扫描B表， 对B表的每一行记录探测
A表的记录（利用A表col2列上的索引） 。
2. 最好是转化为INNER JOIN，LEFT JOIN的成本比INNER JOIN高很多。
3. 使用EXPLAIN检查连接，留意EXPLAIN输出的rows列，如果rows列太高，需要考虑是否索引不佳或连接表的顺序不当。
2. GROUP BY ,DISTINCT,ORDER BY语句优化
GROUP BY默认也是要进行ORDER BY排序的
1. 常用方法
1. 尽量对较少的行进行排序
2. 如果连接了多张表，ORDER BY的列应属于连接顺序的第一张表
3. 利用索引排序
4. GROUP BY，ORDER BY参考的列应该尽量在一个表中
5. 需要保证索引列和ORDER BY的列相同，且各列按照相同的方向排序
6. 增加sort_buffer_size大小
7. 增加read_rnd_buffer_size
2. order by优化(尽量使用index排序，而不是filesort排序)
1. 创建表并插入数据
CREATE TABLE `tblA` ( age int, birth TIMESTAMP NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into tblA(age,birth) values(22,NOW()); insert into tblA(age,birth) values(23,NOW()); insert into tblA(age,birth) values(24,NOW());
2. 创建索引
create index index_ab on tblA(age,birth);
3. 例子
1. explain select * from tblA where age>20 order by age;
2. explain select * from tblA where age>20 order by age,birth;
3. explain select * from tblA where age>20 order by birth;
结论：因为order by排序时与索引顺序出现不匹配，出现了filesort
4. explain select * from tblA where age>20 order by birth,age;
结论：与上面的原因相同
5. explain select * from tblA where birth >'216-01-28 00:00' order by birth;
6. explain select * from tblA where birth >'216-01-28 00:00' order by age;
7. explain select * from tblA where birth >'216-01-28 00:00' order by age asc,birth desc;
结论：order by默认升序，如果出现两种排序方式，就会出现filesort。
4. filesort算法
1. 双路排序
mysql4.1之前都是双路排序，要对磁盘进行两次扫描。
2. 单路排序
mysql4.1之后是单路排序，从磁盘读取查询需要的所有列，按照order by列在buffer对所有列进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间。因为它把每一行都保存在内存中。
3. 优化策略
1. 使用order by的时候，不要使用select *
2. 增大sort_buffer_size参数的设置
3. 增大max_length_for_sort_data参数的设置
5. key a_b_c(a,b,c)
1. 可以使用
order by a
order by a,b
order by a,b,c
order by a desc,b desc,c desc
2. 如果where使用索引的最左前缀定义为常量，则order by能使用索引
where a=const order by b,c
where a=const and b=const order by c
where a=const and b>const order by b,c
3. 不能使用索引排序
order by a asc,b desc,c desc 排序不一致
where g=const order by b,c 带头大哥丢失
where a=const order by c 中间b丢失
where a=const order by a,d d不是索引的一部分
where a in (...) order by b,c 对于排序来说，多个相等条件也是范围查询
3. group by优化(先进行排序再进行分组)
1. 使用索引扫描处理group by
在group by的参照列创建索引，先读取已排序的索引，然后进行分组处理。
使用这种方式的查询的执行计划中，Extra列不会显示using index for group-by, using temporary,using filesort
2. 使用松散索引扫描处理group by
只对单一数据表进行group by处理时才使用松散索引扫描方式
例子：
tb_test数据表的索引为(col1+col2+col3)
1. 使用松散索引 
3. 不使用松散索引
4. 使用临时表处理group by
无论group by的参照列属于驱动表还是被驱动表，只要无法使用索引，就会使用临时表处理group by
3. 子查询优化
将子查询转换为连接查询
SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN ( SELECT column1 FROM t2); SELECT DISTINCT t1.column1 FROM t1, t2 WHERE t1.column1 = t2.column1; SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2); SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id); SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;
4. 优化limit子句
1. 限制页数
2. 要避免设置offset值
最好添加一些条件限制
SELECT id, name, address, phone FROM customers WHERE id > 990 ORDER BY id LIMIT 10;
5. 优化IN列表
IN列表不宜过长，不要超过200。
6. 优化UNION
尽可能在数据库分表的时候，就确定各个分表的数据是唯一的，这样就无须使用UNION来去除重复的记录了。
7. 优化带有BLOB,TEXT类型字段的查询
1. 可以考虑拆分表，把BLOB,TEXT字段分离到单独的表
2. 如果有许多大字段，可以考虑合并这些字段到一个字段，存储一个大的200KB比存储20个10KB更高效
3. 考虑使用COMPRESS(),或者在应用层压缩，再存储到BLOB字段中。
8. 优化临时表
内存临时表 使用MYSQL的MEMORY存储引擎
磁盘临时表 使用MYSQL的MyISAM存储引擎
一般情况下，Mysql会先创建内存临时表，当内存临时表超过配置参数指定的值后，Mysql会将内存临时表导出到磁盘临时表。
1. 创建临时表的条件
1. ORDER BY与GROUP BY子句引用的列不一样
2. 在连接查询中，ORDER BY或GROUP BY使用的列不是连接顺序中的第一个表
3. 表中有BLOB或TEXT字段
4. 使用UNION或UNION ALL时，SELECT子句中包含了大于512字节的列
如果分配的内存大于tmp_talbe_size与max_heap_table_size参数，那么内存临时表就会转换为磁盘临时表
tmp_table_size： 指定系统创建的内存临时表的最大大小
max_heap_table_size： 指定用户创建的内存表的最大大小
2. 避免临时表的方法
1. 创建索引，在ORDER BY或GROUP BY的列上创建索引
2. 分拆长的列，特别是TEXT或BLOB类型的字符串
3. 不需要使用DISTINCT就不用，能用UNION ALL就不用UNION
浙公网安备 33010602011771号