[大数据]MySQL调优笔记

一、数据类型优化

1. 基础知识

1. 性能监控

1） profiles

MySQL自带工具类 profiles ，但是只在老一点的版本里用。

set profiling=1;    #设置为1以后可以查看sql执行的详细情况
show profiles;    # 显示sql id，执行时间，sql 语句

show profile;    # 可以查看sql语句在执行的每个过程中消耗的时间

show profile for query 2;    # 多条语句的时候，可以通过 sql 的 ID 选择对应的语句，比如 2

2） performance schema

是一个数据库，里面有多张表
默认开启，在配置文件中修改
事件的记录不会持久化，保存在内存中，server 断开以后自动删除

show databases;

use performance_schema;

show tables;

-- 打开等待事件的采集器配置项开关，enabled 监控是否开启，timed 计时是否开启
update setup_instruments set enabled = 'yes',timed = 'yes' where name like 'wait%';

-- 打开等待事件的保存表配置开关
update setup_consumers set enabled = 'yes' where name like '%wait%';

配置完成后可以查看当前 server 在干什么，通过下表得知

select  from events_waits_current\G    -- \G 表示将表格转换为键值对形式

3）processlist

查看当前链接

show processlist;

2. 数据类型优化

1. 整型比字符操作带价更低，因为字符集和校对规则是字符比较，比整型更加复杂
2. 使用 MySQL 自建类型而不是字符串来存储日期和时间
3. 使用整型存储 IP 地址 (存储空间小，可读性差，转换可能影响查询效率)

-- 将 IP 地址转为整数存储
select INET_ATON('182.168.85.111');    -- 3232257391

-- 将整数转回 IP 地址
select INET_NTOA('3232257391');  -- 192.168.85.111

4. 尽量避免 null，可为 null 的列使得索引，索引统计和值比较都更加复杂。SQL 中 null ≠ null

1. 整数类型

TINYINT 、SMALLINT、MEDIUMINT、INT、BIGINT 分别使用8、16、24、32、64位存储空间，尽量使用满足需求的最小数据类型

2. 字符和字符串类型

3. BLOB 和 TEXT 类型

MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理，两者都是为了存储很大的数据而设计的字符串类型，分别采取二进制和字符的方式存储。

4. datetime 和 timestamp

datetime

1. 占用8个字节
2. 与时区无关，数据库底层时区配置，对datetime无效
3. 可保存到毫秒
4. 可保存时间范围大
5. 不要使用字符串存储时间类型，占用空间大，损失日期类型函数的便捷性

timestamp

1. 占用4个字节
2. 时间范围：1970-01-01到2038-01-19
3. 精确到秒
4. 采用整型存储
5. 依赖数据库时区配置
6. 自动更新 timestamp列的值

date

1. 占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节
2. 使用date类型还可以利用日期时间函数进行日期之间的计算
3. date类型用于保存 1000-01-01 到 9999-12-31 之间的日期

5. 使用枚举类型代替字符串类型

有时候可以使用枚举类型代替常用字符串类型，MySQL存储枚举类型会非常紧凑，会根据列表值的数据压缩到一个或者两个字节中，MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的 .frm 文件中保存 “数字——字符串” 映射关系的查找表

create table enum_test(
e enum('fish','apple','dog') not null
);

insert into entm_test(e) values ('fish'）,（'dog'),（'apple'）;

select * from enum_test;    -- fish apple dog
select e+0 from enum_test;    --  1 3 2

6. 特殊类型数据

人们经常使用varchar(15)类存储IP地址，然而，他的本质是32位无符号整数，不是字符串，可以使用 INET_ATON 和 INET_NTOA 函数在这两种表示之间进行转换

select inet_aton('1.1.1.1');
select inet_ntoa(16843009);

3. 合理使用范式和反范式

1. 范式

优点：

• 范式化的更新通常比反范式要快
• 当数据较好的范式化后，很少或者没有重复数据
• 范式化的数据比较小，可以放在内存中，操作比较快

缺点：

• 通常需要进行关联

2. 反范式

优点：

• 所有的数据都在同一张表中，可以避免关联
• 可以设计有效的索引

缺点：

• 表格内的冗余较多，删除数据时会造成表有些有用数据丢失

3. 注意

4. 主键的选择

5. 字符集的选择

MySQL 的utf-8 只能存储两个字符的中文，三个字符中文容易乱码
utf-8 mb4

6. 存储引擎的选择

7. 适当的数据冗余

8. 适当拆分

当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR 类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间，这样做的一个明显好处就是每个数据块中可以存储的数据条数大大增加，即减少物理 IO 次数，也能大大提高内存的缓存命中率

二、执行计划

ID

1. 如果id相同，那么执行顺序从上到下
2. 如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3. id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行

select_type

分辨查询类型。是普通查询、联合查询还是子查询

--sample:简单的查询，不包含子查询和union
explain select * from emp;

--primary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--union:若第二个select出现在union之后，则被标记为union
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--dependent union:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响
explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)

--union result:从union表获取结果的select
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--subquery:在select或者where列表中包含子查询
explain select * from emp where sal > (select avg(sal) from emp) ;

--dependent subquery:subquery的子查询要受到外部表查询的影响
explain select * from emp e where e.deptno in (select distinct deptno from dept);

--DERIVED: from子句中出现的子查询，也叫做派生类，
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存
 explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
 
--uncacheable union:表示union的查询结果不能被缓存：sql语句未验证

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集

1. 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
2. 表名是derivedN的形式，表示使用了id为N的查询产生的衍生表
3. 当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id

type

type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般情况下，得保证查询至少达到range级别，最好能达到ref

--all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
explain select * from emp;

--index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序
explain  select empno from emp;

--range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 
explain select * from emp where empno between 7000 and 7500;

--index_subquery：利用索引来关联子查询，不再扫描全表
explain select * from emp where emp.job in (select job from t_job);

--unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引
 explain select * from emp e where e.deptno in (select distinct deptno from dept);
 
--index_merge：在查询过程中需要多个索引组合使用，没有模拟出来

--ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式
explain select * from emp e where  e.mgr is null or e.mgr=7369;

--ref：使用了非唯一性索引进行数据的查找
 create index idx_3 on emp(deptno);
 explain select * from emp e,dept d where e.deptno =d.deptno;

--eq_ref ：使用唯一性索引进行数据查找
explain select * from emp,emp2 where emp.empno = emp2.empno;

--const：这个表至多有一个匹配行，
explain select * from emp where empno = 7369;
 
--system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

possible_keys

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

key

实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

key_len

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

rows

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好

explain select * from emp;

extra

包含额外的信息。

--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
explain select * from emp order by sal;

--using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除
explain select ename,count(*) from emp where deptno = 10 group by ename;

--using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;

--using where:使用where进行条件过滤
explain select * from t_user where id = 1;

--using join buffer:使用连接缓存，情况没有模拟出来

--impossible where：where语句的结果总是false
explain select * from emp where empno = 7469;

三、通过索引进行优化

索引底层是 B+树

1.索引的基本知识

1. 索引的优点

order by 是全表排序，效率比较慢，用索引以后就自动排序了

2. 索引的用处

3. 索引的分类

1）主键索引
2）唯一索引
3）普通索引
4）全文索引
5）组合索引

唯一索引

数据库会自动创建索引，索引是给唯一键创建的，不是给主键

普通索引

给一个普通列创建索引

全文索引

在 varchar char text 等类型中创建全文索引

组合索引

将两个列组合在一起创作索引

4. 面试技术名词

1. 回表
2. 覆盖索引
3. 最左匹配
4. 索引下推

回表

例如给 name 列创建索引，最后的节点里面放的是主键，按照 name 进行查询的时候，第一次根据 name 列的 B+ 树进行查找，在 name 列叶子节点里找到主键，在到主键的 B+树里找到整行的记录，这个过程就叫回表

或者按sql语句来理解，就是 select * from 表名 where id in （111,222,333）;

覆盖索引

例如 select id from 表名 where name = '....'，这个语句在执行的时候，定位到name的叶子节点，而这个节点里存着 主键 ID，最终要查的也是 ID，就不需要在通过 主键ID在主键的叶子节点里查找数据，这就是索引覆盖。也就是省去了回表的过程。

最左匹配

假如用 name 和 age 两个列创建索引，name在前age在后，select * from test where name=? and age=? 这样可以走索引，但 where age=? and name=? 这样就不能走索引。先有最左边(name)，才能有右边(age)。
假如只查 name ，不查 age，同样也可以走索引，也就是只要最左边有了，右边有没有都行。
只有组合索引才有最左匹配的概念

假如有一个需求，需要返回 name 和 age，单独 name，单独 age，创建索引的时候就可以创建成 name age 和 age 两个索引。因为索引存储也需要占用空间，显然 age 占用的空间更小。

索引下推

谓词下推：
以 select t1.name,t2.name from t1 join t2 on t1.id = t2.id 为例，有两种筛选方式
第一种：将 t1 和 t2 先组合，然后筛选
第二种：先将 name 和 id 取出来，然后筛选。
第二种方式就是谓词下推

索引下推：前提是在组合索引中
有一个 name,age 组合索引，查找的时候筛选条件 where name = ? and age = ?
老版本的时候，先把所有 name 符合的值从存储引擎中提取出来，然后在 server 层再筛选 age 。
高版本直接在存储引擎中将 name 和 age 符合的筛选出来，不在 server 层进行处理，这就是索引下推

4. 索引采用的数据结构

哈希表：Memory

B+ 树：InnoDB Myisam

5. 索引匹配方式

2. 哈希索引

1. 基于哈希表的实现，只有精确匹配索引所有列的查询才有效
2. 在 MySQL 中，只有 memory 的存储引擎显式支持哈希索引
3. 哈希索引自身只需要存储对应的 hash 值，所以索引的结构十分紧凑，这让哈希索引的查找速度非常快

哈希索引的限制：

例：
当需要存储大量的URL，并且根据URL进行搜索查找，如果使用B+树，存储的内容就会很大 select id from url where url=''
也可以利用将 url 使用 CRC32 做哈希 select id from url where url = '' and url crc=CRC32('')
此查询性能较高的原因是使用体积很小的索引来完成查找。

CRC32 ：循环冗余校验，可以把指定字符串变成整数值

3. 组合索引

当包含多个列做为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需求

4. 聚簇索引与非聚簇索引

聚簇索引

不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起

5. 覆盖索引

基本介绍

1. 如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引
2. 不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值
3. 不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory 不支持覆盖索引

explain select ....... 查完以后，Extra 是 Using inde 即为覆盖索引

优势

6. 优化小细节

尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询

查询的时候尽量带上主键查询

使用前缀索引

创建前缀索引： alter table 表名 add key(列名(数量))；

有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

使用索引扫描来排序

union all , in , or 都能够使用索引，但是推荐使用 in

范围列可以用到索引

强制类型转换会全表扫描

create table user(id int,name varchar(10),phone varchar(11));
alter table user add index idx_1(phone);

type 列，整型不会触发索引，字符串会触发索引

更新十分频繁，数据区分度不高的字段上不建议使用索引

1. 更新会变更B+树，更新频繁的字段建立索引会大大降低数据库的性能
2. 类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据
3. 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 cont(distince(列名))/congt(*) 来计算

创建索引的列，不允许为 null，可能会得到不符合预期的结果

当需要进行表链接的时候，最好不要超过三张表，因为需要 join 的字段，数据类型必须一致

left join左联接（）/返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

select from t1 left join t2 on t1.id = t2.id and t1.name = '...';
and 是在表连接前过滤A表或B表里面哪些记录符合条件，同时会兼顾是 left join 还是 right join 。即假如是左连接的话，如果左边表的某条记录不符合连接条件，那么它不进行连接，但是仍然留在结果集中（此时右边部分的连接结果为NULL）。on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录

能使用 limit 的时候尽量使用 limit

如果明确知道只有一条结果返回，limit 1 能够提高效率
limit 1 指针不会多做一次判断 .在返回结果的时候，即使只有1个返回结果，还会往下进行判断，看还有没有值

单表索引建议控制在5个以内

单索引字段数不允许超过5个（组合索引）

创建索引的时候应该避免以下错误

1. 索引越多越好
2. 过早优化，在不了解系统的情况下进行优化

7. 索引监控

监控所有索引

show status like 'Handler_rtead%'

1. Handler_read_first ：读取索引第一个条目的次数
2. Handler_read_key ：通过 index 获取数据的次数
3. Handler_read_last ：读取索引最后一个条目的次数
4. Handler_read_next：通过索引读取下一条数据的次数
5. Handler_read_prev：通过索引读取上一条数据的次数
6. Handler_read_rnd：从固定位置读取数据的次数
7. Handler_read_rnd_next ： 从数据节点读取下一条数据的次数

索引优化案例

四、查询优化

1. 查询慢的原因

1. 网络
2. CPU
3. IO
4. 上下文切换
5. 系统调用
6. 生成统计信息
7. 锁等待时间

2. 优化数据访问

查询性能低下的主要原因是访问的数据太对，某些查询不可避免的需要筛选大量的数据，我们可以通过减少访问数据量的方式进行优化

1. 确认应用程序时候在检索大量超过需要的数据
2. 确认MySQL服务器层是否在分析大量超过需要的数据行

是否向数据库请求了不需要的数据

1. 查询不需要的记录
   我们常常会误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部结果再进行计算，在日常的开发习惯中，经常是先用select语句查询大量的结果，然后获取前面的N行后关闭结果集
   优化方式：在查询后面添加 limit
2. 多表关联时返回全部列
3. 总是取出全部列
   尽量不要使用 select *
4. 重复查询相同的数据
   如果需要不断的执行重复的相同查询，且每次返回完全相同的数据，因此，基于这样的场景，我们可以将这部分数据缓存起来，这样的话能够提高查询效率

3. 执行过程优化

查询缓存

在 5.x 几的版本中，有查询缓存，如果查询缓存是打开的，那么 MySQL 会优先检查这个查询时候命中查询缓存中的数据，如果查询恰好命中缓存，那么会在返回结果之前检查用户权限，如果没问题，就跳过所有阶段，直接从缓存中拿到结果并返回给客户端

查询优化处理

MySQL查询完缓存后会经过以下几个步骤：

1. 解析 SQL
2. 预处理
3. 优化SQL执行计划
   这几个步骤出现任何错误，都有可能终止查询计划

语法解析器和预处理器

MySQL通过关键字将SQL语句进行解析，并生成一颗解析树，MySQL解析器将使用MySQL语法规则验证和解析查询，例如验证使用了错误的关键字或者顺序是否正确等等。预处理器会进一步检查解析树是否合法，例如表名和列名是否存在，是否有歧义，还会验证权限等等

查询优化器

CBO 基于成本优化
RBO 基于规则优化

select count(*) from film_actor;
show status like 'last_query_cost' -- 最后一次查询所消耗的成本

可以看到这条查询语句大概需要做 1104 个数据页才能找到对应的数据，这是经过一系列的统计信息计算来的

1. 每个表或者索引的页面个数
2. 索引的基数
3. 索引和数据行的长度
4. 索引的分布情况

很多情况下 MySQL 会执行错误的执行计划，原因如下：

1. 统计信息不准确
   InnoDB 因为其 mvcc 的架构，并不能维护一个数据表的行数的精确统计信息

2. 执行计划的成本估算不等同于实际执行的成本
   有时候某个执行计划虽然需要读取更多的界面，但是他的成本却更小，因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很小，MySQL层面并不知道哪些页面在内存中，哪些在磁盘，所以查询之际执行过程中到底需要多少次IO是无法得知的

3. MySQL 的最优可能跟你想的不一样
   MySQL的优化是基于成本的优化，但是有可能不是最快的优化

4. MySQL 不考虑其他并发执行的查询

5. MySQL 不会考虑不受其控制的操作成本
   执行存储过程或者用户自定义函数的成本

优化器的优化策略

优化器的优化类型

1. 重新定义关联表的顺序
   数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序是优化器很重要的功能

2. 将外连接转化为内连接，内连接的效率要高于外连接

3. 使用等价交换规则，MySQL 可以使用一些等价变化来简化并规划表达式

4. 优化 count() , min() , max()
   索引和列是否可以为空通常可以帮助 MySQL 优化这类表达式。例如：要找到某一列的最小值，只需要查询索引的最左端的记录即可，不需要全文扫描比较

5. 预估并转化为常数表达式，当 MySQL 检测到一个表达式可以转化为常数的时候，就会一直把该表达式做为常数进行处理
   where id = 1

6. 索引覆盖扫描，当索引中的列包含所有查询中需要使用的列的时候，可以使用覆盖索引

7. 子查询优化
   mysql 在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问。例如将经常查询的数据放入到缓存中。

8. 等值传播
   a inner join b on a.id = b.id where a.id > 500 and b.id > 500 ==> a inner join b on a.id = b.id where a.id > 500

关联查询

join 的实现方式原理

排序优化

1. 两次传输排序
   第一次数据读取是将需要排序的字段读取出来，然后进行排序。第二次是将排好序的结果按照需要去读取数据行。这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高
   两次传输的优势，在排序的时候存储尽可能少的数据，让排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作

2. 单次传输排序
   先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无需任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据

当需要排序的列的总大小加上 order by 的列大小超过 max_length_for_sort_data 定义的字节，MySQL 会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序方式

4. 优化特定类型的查询

优化 count() 查询

select count(*) / count(id) / count(1) 执行效率完全一样

count() 是特殊类型的函数，有两种不同的作用，一种是某个列值的数量，也可以统计行数

1. 总有人认为 myisam 的 count 函数比较快，这是有前提条件的，只有没有任何 where 条件的 count(*) 才是比较快的

2. 使用近似值
   在某些场景中，不需要完全精确的值，可以参考使用近似值来代替，比如可以使用 explain 来获取近似的值。
   其实在很多OLAP 的应用中，需要计算某一个列值的基数，有一个计算近似值的方法叫 hyperloglog

3. 更复杂的优化
   一般情况下，count() 需要扫描大量的行才能获得精确的数据，其实很难优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存系统

优化关联查询

确保 on 或者 using 子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序

确保任何的 group by 和 order by 中的表达式只涉及到一个表中的列，这样 MySQL 才有可能使用索引来优化这个过程

优化子查询

尽可能使用关联查询代替，子查询会有临时表，影响效率

优化 limit 分页

优化此类查询的最简单办法就是尽可能的使用覆盖索引，而不是查询所有列
select * from 表名 limit 10000,5 ==> select * from a join (select id from 表名 limit 10000,5) b on a,id = b.id;

-- 查看执行计划的扫描行数进行验证
select id,name from 表名 order by title limit 50,5  -- rows 大概 1000

explain select a.id,a.name from a inner join (select id from a order by title limit 50,5) as lim using(id);    -- rows 大概 111

优化 group by 和 distinct

在很多场景下，MySQL 使用相同的方式来优化 group by 和 distinct 的查询，使用索引是最有效的方式，当时有很多的情况下无法使用索引，可以使用临时表或者文件排序来分组

若数据里有重复项，下面的语句会造成结果不准确

优化 union 查询

union ，并集，不包含重复部分
union all ，并集，包含重复部分，也就是两个结果集交叉的部分，即重复算两次交叉部分
行转列可以用到 union

除非确实需要服务器消除重复的行，否则一定要使用 union all ，因此没有 all 关键字，MySQL 会在查询的时候给临时表加上 distinct 的关键字，这个操作代价很高

推荐使用用户自定义变量

自定义变量的使用

set @one := 1
set @min_actor :=(select min(id) from actor)
set @last_week := current_date - interval 1 week

自定义变量的限制

1. 无法使用查询缓存
2. 不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名或者limit子句
3. 用户自定义变量的生命周期在一个连接中有效，所以不能用它们来做连接件的通信
4. 不能显式地声明自定义变量的类型
5. MySQL 优化器在某些场景下可能会将这些变量优化掉，这有可能导致代码不按预想的方式运行
6. 赋值符号： = 优先级非常低，所以在使用复制表达式的时候应该明确的使用括号
7. 使用未定义变量不会产生任何语法错误

自定义变量的使用案例

优化排名语句执行结果

确定取值顺序结果：

第一个，先执行where后的语句，所以两条
第二个：

第三个，先执行where后括号里的语句，所以一 条

五、分区表

分区表的应用场景

分区表的限制

分区表的原理

分区表的底层原理

分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引知识在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

分区表的操作按照以下的操作逻辑进行：

select查询

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

insert操作

当写入一条记录的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表

delete操作

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

update操作

当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该再哪个分区，最后对底层表进行写入操作，并对源数据所在的底层表进行删除操作

有些操作时支持过滤的，例如，当删除一条记录时，MySQL需要先找到这条记录，如果where条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉，这对update同样有效。如果是insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉。mysql先确定这条记录属于哪个分区，再将记录写入对应得曾分区表，无须对任何其他分区进行操作

虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，例如innodb，则会在分区层释放对应表锁。

分区表的类型

官网地址：https://dev.mysql.com/doc/refman/8.0/en/partitioning-types.html

1. 范围分区
2. 列表分区
3. 列分区
4. hash分区
5. key分区
6. 子分区

范围分区

根据列值在给定范围内将行分配给分区

在此分区方案中，对应于在商店 1 到 5 工作的员工的所有行都存储在分区中，对应于在商店 6 到 10 工作的员工的所有行都存储在分区中，依此类推。每个分区按从低到高的顺序定义。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

也可以使用 UNIX_TIMESTAMP（） 函数根据 TIMESTAMP 列的值按 对表进行分区，如以下示例所示：RANGE

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

列表分区

类似于按 range 分区，区别在于 list 分区时基于列值匹配一个离散值集合中的某个值来进行选择（等值分区）

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

列分区

MySQL 从 5.5 开始支持 column 分区，可以认为 i 是 range 和 list 的升级版，在 5.5 之后，可以使用 column 分区代替 range 和 list，但是 column 分区只接受普通列不接受表达式

hash分区

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算，这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

key分区

类似于hash 分区，区别在于 key 分区只支持一列或多列，且 MySQL 服务器提供器自身的哈希函数，必须有一列或多列包含整数值

CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;

子分区

在分区的基础上，再进行分区后存储

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

如何使用分区表

如果需要从非常大的表中查询某一段时间的记录，而这张表中包含很多历史数据，数据按照时间排序，因数据量巨大，肯定不能每次都进行全表扫描，考虑到索引在空间和维护上的消耗，即使使用索引还会产生大量的随机IO，但当数据量超大的时候，索引也就无法起作用了，此时可以考虑使用分区来进行解决

全是扫描数据，不要任何索引

使用简单的分区方式村表，不要任何索引，根据分区规则大致定位需要的数据为止，通过使用where条件将需要的数据限制在少数分区中，这种策略适用于以正常的方式访问大量数据

索引数据，并分离热点

如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中，这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存

在使用分区表的时候需要注意的问题

六、服务器参数设置

1. general 通用的

2. character 字符

3. connection 链接

max_connections

MySQL 的最大连接数，如果数据库的并发连接请求比较大，应该调高该值

默认连接数是根据电脑配置定的

设置默认连接数：set global max_connections=1024
查看默认连接数：show variables like '%max_connection%'

max_user_connections

限制每个用户的连接个数，默认是0，表示不限制
如果一个用户连接成百上千次，肯定有异常，可以通过它来限制

back_log

MySQL 能够暂存的连接数量，当 MySQL 的线程在一个很短时间内得到非常多的连接请求时，就会起作用，如果MySQL 的连接数量达到 max_connections 时，新的请求会被存储在堆栈中，以等待某一个连接释放资源，如果等待连接的数量超过 back_log ，则不再接受连接资源

默认80，不宜过大 ，在业务中过大造成等待时间过长，还不如返回拒绝连接或者连接过多

wait_timeout

MySQL 在关闭一个非交互的连接之前需要等待的时长

使用 JDBC 请求数据就是非交互式连接

interactive_timeout

关闭一个交互的连接之前需要等待的时长

MySQL 的黑窗口就是交互连接

4. log 日志

log_bin : 多用于主从复制，默认不开启。 使用 log-bin=master-bin 开启

5. cache 缓存

key_buffer_size

索引缓存区的大小（只对 myisam 表起作用）

query cache 查询缓存

8 版本被淘汰

query_cache_size

查询缓存的大小，未来版本被删除

query_cache_limit

超出此大小的查询将不被缓存

query_cache_mine_res_unit

缓存块最小大小

query_cache_type

缓存类型，决定缓存什么样的查询

0 表示禁用
1 表示将缓存所有结果，除非 sql 语句中使用 sql_no_cache 禁用查询缓存
2 表示只缓存 select 语句中通过 sql_cache 指定需要缓存的查询

sort_buffer_size

每个需要排序的线程分派该大小的缓冲区

max_allowed_packet=32M

限制 server 接受的数据包大小

join_buffer_size=2M

表示关联缓存的大小

thread_cache_size

6. InnoDB