开发者学堂课程【MySQL 高级应用 - 索引和锁：索引面试题分析】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/598/detail/8622
索引面试题分析
目录：
一、表的构建代码
二、索引构建
三、问题反馈
四、MySQL 逻辑构架
一、表的构建
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)
);
2. 插入数据
insert into test03(c1c2c3c4c5)values(a1'a2'a3'a4'a5');
insert into test03(c1c2c3c4c5)values(b1b2'b3b4''b5);
insert into test03(c1c2c3c4c5)values(c1c2c3c4''c5');
insert into test03(c1c2c3c4c5)values(d1'd2''d3'd4'd5')
insert into test03(c1c2c3c4c5)values(e1'e2 e3e4'.55)
3.查询表
select*from test03；
二、构建索引
复合索引：
create index idx test03_c1234 on test03(c1,c2,c3,c4); show index from test03;
排序+查找是索引两个用途
复合索引命名规则：表名_字段名
1.需要加索引的字段，要在 where 条件中。
2.数据量少的字段不需要加索引。最窄的字段放在键的左边。
3.如果 where 条件中是 OR 关系，必须所有的 or 条件都必须是独立索引，否则加索引不起作用。见：mysql 关于 or 的索引问题
4.最左匹配原则。
5.只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为 NULL。
三、问题反馈
问题一:
我们创建了复合索引 idxtest03c1234 根据以下 SQL 分析下索引使用情况?
//权值匹配
explain select*from test03 wherec1=a1:
explain select* from test03 where c1='a1'and c2=a2:
explain select*from test03 where c1=a1'and c2=a2and c3=a3",
explainselect*fromtest03wherec1=a1andc2=a2'andc3=a3'andc4=a4'
四、MySQL 逻辑架构
和其他数据库相比，MySQL 有点与众不同，它的架构可以在多种不同的场景中应用并发挥良好的作用，主要体现在存储引擎的架构上。
插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。
这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
连接层：
最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似 tcp/ip 的通信。
服务层：
第二层架构主要完成大多数的核心服务，如 SQL 接口，并完成缓存的查询，SQL 的分析进而优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的顺序，是否利用索引等，最后生成相应的执行操作。
如果是 select 语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读的操作的环境下能够很好的提升系统的性能。
引擎层：
存储引擎层，存储引擎真正的负责了 MySQL 中的数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
存储层：
数据存储层，主要是将数据存储在运行于裸机设备的文件系统之上，并完成与存储引擎的交互。
MySQL 是如何优化和执行查询的。
一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL 的优化器能够按照预想的合理方式运行而已。
当向 MySQL 发送一个请求的时候，MySQL 到底做了些什么呢？
下图展示了 MySQL 的查询过程。
//使用绝对权值匹配
1)
explain select*from test03 where c1=a1'and c2='a2' and c3=a3'and c4='a4',
2)
explain select*from test03 wherec1=a1'andc2=a2and c4=a4'and c3=a3'
3)
explain select * from test03 where c1='a1'and c2='a2' and c3>'a3' and c4='a4'
4）
explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3':
5)
explain select*from test03 where c1='a1' and c2='a2' and c4='a4' order byc3:
c3
作用在排序而不是查找
6）
explain select * from test03 where c1='a1' and c2='a2' order by c3
7)
explain select*from test03 where c1='a1' and c2=a2' order by c4,
8)
8.1
explain select * from test03 where c1='a t"and c5='a5' order by c2.c3,
只用 c1一个字段索引，但是 c2、c3 用于排序,无 filesort
8.2
explain select * from test03 where c1='a 1' and c5='a5' order by c3.c2:
出现了 filesort ，我们建的索引是 1234，它没有按照顺序来，32 颠倒了
9)
explain select * from test03 where c1='a 1' and c2='a2'order by c2.c3.
10)
explain select *from test03 where c1='a 1' and c2='a2 and c5='a5' order by
c2.c3 用 c1、c2 两个字段索引，但是 c2、c3 用于排序,无 filesort
expiain select* from test03 where c1='a1'and c2='a2'and c5='a5' order by c3,c2;
本例有常量 c2 的情况，和8.2对比
explain selec! * from test03 where c1='a !' and c5='a5' order by c3.c2, filesort
11)
explain select * from test03 where c1='a1' and c4='a4' group by c2.c3:
定值、范围还是排序，一般orderby是给个范围 groupby基本上都需要进行排序，会有临时表产生
查询优化:
经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。
优化器的作用就是找到这其中最好的执行计划。
MySQL 使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
在 MySQL 可以通过查询当前会话的 last_query_cost 的值来得到其计算当前查询的成本。
一般性建议:
1.对于单键索引，尽量选择针对当前 query 过滤性更好的索引
2.在选择组合索引的时候，当前 Query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
3.在选择组合索引的时候，尽量选择可以能够包含当前 query 中的 where 字句中更多字段的索引
4.尽可能通过分析统计信息和调整 query 的写法来达到选择合适索引的目的
5.使用索引可加快数据检索速度，但为每个列都建立索引没有必要。因为索引自身也需要维护，并占用一定的资源，可以按照以下标准选择建立索引的列
*频繁搜索、查询选择的列
*经常排序、分组的列
*经常用于连接的列（主键、外键）
