1. MySQL分为三层
- 查看语句执行时间
mysql> select * from test_20221120;
Empty set (0.00 sec)
- 从上面的执行结果看不出来语句执行所需要的时间，使用以下命令
mysql> set profiling=1;
- 再次执行查看语句，然后使用以下命令查看执行时间
mysql> show profiles;
+----------+------------+-----------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------+
| 1 | 0.00043925 | select * from test_20221120 |
+----------+------------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
Navicat 中
- 查询语句各阶段的执行时间
show PROFILE;
| Status | Duration |
|---|---|
| starting | 0.000087 |
| checking permissions | 0.000008 |
| Opening tables | 0.000023 |
| init | 0.000021 |
| System lock | 0.000010 |
| optimizing | 0.000005 |
| statistics | 0.000016 |
| preparing | 0.000013 |
| executing | 0.000004 |
| Sending data | 0.000038 |
| end | 0.000004 |
| query end | 0.000008 |
| closing tables | 0.000008 |
| freeing items | 0.000029 |
| cleaning up | 0.000015 |
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type: {
ALL:显示所有信息
| BLOCK IO: 显示块IO操作的次数
| CONTEXT SWITCHES : 上下文切换次数,被动和主动;
| CPU:显示用户CPU时间、系统CPU时间
| IPC:发送和接受的消息数量
| MEMORY:暂未实现
| PAGE FAULTS:显示页错误数量
| SOURCE:显示源码中的函数名与名称
| SWAPS:显示swap的次数
}
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query |
+----------+----------+--------------------------+
| 0 | 0.000088 | SET PROFILING = 1 |
| 1 | 0.000136 | DROP TABLE IF EXISTS t1 |
| 2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)
mysql> SHOW PROFILE;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table | 0.000056 |
| After create | 0.011363 |
| query end | 0.000375 |
| freeing items | 0.000089 |
| logging slow query | 0.000019 |
| cleaning up | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| query end | 0.000107 |
| freeing items | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up | 0.000006 |
+--------------------+----------+
4 rows in set (0.00 sec)
mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 | 0.000002 |
| creating table | 0.000056 | 0.000028 | 0.000028 |
| After create | 0.011363 | 0.000217 | 0.001571 |
| query end | 0.000375 | 0.000013 | 0.000028 |
| freeing items | 0.000089 | 0.000010 | 0.000014 |
| logging slow query | 0.000019 | 0.000009 | 0.000010 |
| cleaning up | 0.000005 | 0.000003 | 0.000002 |
+----------------------+----------+----------+------------+
7 rows in set (0.00 sec)
SHOW PROFILE FOR QUERY 2;
SELECT STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 2 ORDER BY SEQ;
[注意]Profile 命令只推荐在MySQL5使用，推荐使用performance_schema
关于
performance_schema有两个基本概念
- instruments:生产者，用于采集mysql中各种各样的操作产生的事件信息，对应配置表中配置项我们可以称为监控采集配置项。
- consumers:消费者，对应的消费者表用于存储来自instruments采集的数据，对应配置表中的配置我们称为消费存储配置项。
performance_schema表的分类
-
等待时间记录表，与语句事件类型的相关记录表，类似于:
show tables like '%wait%'; -
阶段事件记录表，记录语句执行的阶段事件表
show tables like '%stage%'; -
事务事件记录表，记录事务相关的事件表
show tables like '%transaction%'; -
监控文件系统层调用的表
show tables like '%file%'; -
监视内存使用的表
show tables like '%memory%'; -
动态对
performance_schema进行配置的配置表
show tables like ''%setup%;
performance_schema的简单配置与使用
数据库刚刚初始化并启动的时候，并不是所有的
instruments信息都会被采集。
select * from setup_instruments;
- 打开等待事件的采集器配置项开关，需要修改
setup_instruments配置表中对应的采集配置项
UPDATE setup_instruments SET ENABLED ='YES' ,TIMED ='YES' WHERE NAME LIKE 'wait%';
- 打开等待事件的表配置开关，修改setup_consumers配置表中对应的配置项
UPDATE setup_consumers SET ENABLED ='YES' WHERE NAME LIKE 'wait'
- 当配置完成后可以查看当前server正在做什么，可以通过查询
events_waits_current表来得知，该表中每个线程只包含一行数据，适用于显示每个线程的最新监视事件。
select * from events_waits_current\G
performance_schema实践操作
- 哪类SQL执行最多
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM
events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
- 哪类SQL的平均响应时间最久
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM
events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
- 哪类SQL排序记录数最多
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM
events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
- 哪类SQL返回结果集最多?
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM
events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
查看数据库当前线程池
show processlist
- 数据库连接池
druid
2. schema与数据类型优化
数据类型的优化
- 更小的通常更好
应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用磁盘、内存和CPU更少缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型。
案例：
在工作中会涉及到存储状态1、0，在选择类型的时候我们一般默认选择为INT,这是不对的。不同的类型的数据空间是不一样的。Mysql中int类型有TINYINT、SMALLINT、MEDIUMINT、INT、BIGINTmysql的数据类型有哪些?
- 简单就好
简单的数据类型的操作通常需要更少的CPU周期，比如:
- 整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂。
- 使用mysql自建类型而不是字符串来存储日期和时间。
- 使用整型存储IP地址。
SELECT INET_ATON('192.189.190.101'); SELECT INET_NTOA(3233660517); 输出结果: INET_ATON('192.189.190.101') 3233660517 SELECT INET_NTOA(3233660517); 192.189.190.101
- 尽量避免null
在数据中
null不等于null;
如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂，坦白来说，通常情况下null列改为not null带来的性能提升比较小，所以没有必要将所有的表的schema进行修改，但是应该尽量避免设计成为null的列。空字符串''是可以的(使用默认值时候请谨慎)
- 实际细则
- 整数类型
可以使用的几种整数类型:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别使用8、16、24、32、64位存储空间。尽量使用满足需求的最小数据类型。
- 字符和字符串类型
- varchar根据实际内容长度保存数据
- 使用最小的符合需求的长度
- varchar(n)n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度
- varchar(5)与varchar(255) 保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小
- varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时，都会导致锁表。
- 应用场景
- 存储长度波动较大的数据，如:文章，有的会很短有的又会很长；
- 字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度。
- 适合保存多字节字符，如:汉字,特殊字符等
- char 固定长度的字符串
- 最大长度:255
- 会自动删除末尾的空格
- 检索效率、写效率会比varchar高，以空间换时间
- 应用场景
- 存储长度波动不大的数据，如:md5摘要
- 存储短字符串、经常更新的字符串
- BLOB和TEXT类型(一般不建议使用，涉及到IO问题)
MySQL把每个BLOB和TEXT值当作一个独立的对象处理。
两者都是为了存储很大数据而涉及的字符串类型，分别使用二进制和字符方式存储。
- varchar根据实际内容长度保存数据
- datetime和timestamp
- 不要使用字符串来存储日期和时间数据
- 日期时间类型通常比字符串占用的存储空间更小
- 日期时间类型在进行查找过滤时可以利用日期来进行比较
- 日期类型还有着丰富的处理函数，可以方便的对事件类型进行日期计算
- 使用int 存储日期事件不如使用timestamp类型
- datetime
- 占用8个字节
- 与时区无关，数据库底层时区配置，对datetime无效
- 可保存到毫秒
- 可保存时间范围大
- 不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性
- timestamp
- 占用4个字节
- 时间范围:1970-01-01到2038-01-19
- 精确到秒
- 采用整型存储
- 依赖数据库设置的时区
- 自动更新timestamp列的值
- date
- 占用的字节数比使用字符串、datetime、int存储更少，使用date类型存储只需要3个字节
- 使用date类型还可以利用日期事件函数进行日期之间的计算
- date类型用于保存1000-01-01到9999-12-31之间的日期
- 用枚举类型替代字符串
MySQL存储枚举类型会非常紧凑，会根据列表值的数据压缩到一个或者两个字节中，MySQL在内部会将每个值在列表中的而为之保存为整数，并且在表的.frm文件中保存"数字·字符串"映射关系的查找表
CREATE TABLE `insert_20221123_5` ( `id` char(3) DEFAULT NULL, `gender` enum('FEMAL','MALE') DEFAULT NULL, `name` varchar(3) DEFAULT NULL, `realname` char(3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 - 特殊类型数据
存IP推荐使用整型,IP的本质是32位无符号整型,
INET_ATON()和INET_NTOA()
- 整数类型
合理使用范式和反范式
数据库设计三大范式
-
第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)
-
第二范式(2NF)：满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)
-
第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）
范式
- 优点
- 范式化的更新通常比反范式更块
- 当数据较好的范式化后，很少或者没有重复的数据
- 范式化的数据比较小，可以放在内存中，操作比较快
- 缺点
- 通常需要进行关联
反范式
- 优点
- 所有数据都在一张表，可以避免关联
- 可以设计有效的索引
- 缺点
- 表格内的冗余比较多，删除数据的时候会造成有些有用的信息丢失
注意
-
在企业中很难做好严格意义上的范式或反范式，一般需要混合使用
在一个网站实例中，这个网站，允许用户发送消息，并且一些用户是付费用户。现在想看付费用户最近的10条信息。在user表和message表中都存储用户类型（account_type）而不用完全的反范式化。这避免了完全反范式化的删除和插入问题，即使没有message表的数据被删除也不会丢失用户信息。这样也不会把user_message表搞的太大，有利于高效地获取数据。
另一个从父表冗余一些数据到子表的理由是排序的需要
-
示例
- 范式设计
用户表 ->|用户ID|姓名|电话|地址|邮编|
订单表->|订单ID|用户ID|下单时间|支付类型|订单状态|
订单商品表->|订单ID|商品数量|商品价格|
商品表->|商品ID|名称|描述|过期时间| - 反范式设计
用户表 ->|用户ID|姓名|电话|地址|邮编|
订单表->|订单ID|用户ID|下单时间|支付类型|订单状态|订单价格|用户名|电话|地址|
订单商品表->|订单ID|商品数量|商品价格|
商品表->|商品ID|名称|描述|过期时间|
- 范式设计
主键的选择
-
代理主键
与业务无关，无意义的数字序列
-
自然主键
事物属性中的自然唯一标识
-
推荐使用代理主键
他们不与业务耦合，因此更容易维护；
一个大多数表，最好是全部表，通用的键策略能减少需要编写的代码源数量，减少系统的总体拥有成本。
字符集的选择
纯拉丁字符能表示的内容，没必要选择
latin1之外的其它字符编码，因为这会节省大量的存储空间。utf-8下,1字符=3字节。(uft-8也称之为utf-8mb3) utf-8mb4下,1字符=4字节;latin1 1个字符是1字节。
如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或其他UNICODE字符类型，会造成大量的存储空间浪费。
MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中粗放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减少数据库存储量，进而降低IO操作次数并提高缓存命中率。
中文使用
utf8mb4;存储引擎的选择
存储引擎:数据文件的组织形式
| MyISAM | InnoDB | |
|---|---|---|
| 索引类型 | 非聚簇索引 | 聚簇索引(数据文件和索引文件放在一起) |
| 支持事务 | 否 | 是 |
| 支持表锁 | 是 | 是 |
| 支持行锁 | 否 | 是 |
| 支持外键 | 否 | 是 |
| 支持全文检索 | 是 | 是(5.6后支持) |
| 适合操作类型 | 大量SELECT | 大量INSERT、DELETE、UPDATE |
适当的数据冗余
被频繁引用且只能通过
Jion2张(或者更多)打标的方式才能得到的独立小字段
这样的场景由于每次
Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。适当拆分
当我们的表中存在类似于TEXT或者是很大的VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都用不到这个字段，我们就该义无反顾的将其拆分到另外独立的表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理IO次数，也能大大提高内存中的缓存命中率。
数据库水平拆分和垂直拆分
3. 执行计划
EXPLAIN输出格式
- 执行计划中包含的信息
| Column | JSON Name | Meaning |
|---|---|---|
| id | select_id | The SELECT identifier |
| select_type | None The | SELECT type |
| table | table_name | The table for the output row |
| partitions | partitions | The matching partitions |
| type | access_type | The join type |
| possible_keys | possible_keys | The possible indexes to choose |
| key | key | The index actually chosen |
| key_len | key_length | The length of the chosen key |
| ref | ref | The columns compared to the index |
| rows | rows | Estimate of rows to be examined |
| filtered | filtered | Percentage of rows filtered by table condition |
| Extra | None | Additional information |
- id
SELECT查询的序列号，包含一组数字，表示查询中执行SELECT子句或者操作表的顺序;
CREATE TABLE person_20221125 ( id char(3) DEFAULT NULL, user_name varchar(3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 CREATE TABLE order_20221125 ( id char(3) DEFAULT NULL, user_id char(3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4-
如果id相同，那么执行顺序从上到下
EXPLAIN SELECT * FROM person_20221125 p left join order_20221125 o on p.id = o.user_id
-
如果id不同，如果是子查询，id的序号会递增，id值越大，优先级越高，越先被执行
-- create index person_id_idx on person_20221125(id); -- create index order_id_idx on order_20221125(id); EXPLAIN SELECT * FROM person_20221125 p where p.id in (select user_id from order_20221125 where id ='1')
-
id相同和不同的同时存在，相同的认为是一组，从上往下执行，在所有组中，id值越大，优先级越高，越先执行
-
- select_type
主要用来分辨查询的类型，是普通查询还是联合查询。
- 执行计划的 type
访问类型，SQL 查询优化中一个很重要的指标，结果值从好到坏依次是：system > const > eq_ref > ref > range > index > ALL。
- system
系统表，少量数据，往往不需要进行磁盘IO
- const
常量连接
- eq_ref
主键索引（primary key）或者非空唯一索引（unique not null）等值扫描
- ref
非主键非唯一索引等值扫描
- range
索引上的范围扫描
- index
索引上的全扫描
count(*) - ALL
全表扫描（full table scan）
- system
- extra
包含额外的信息
- using filesort
说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
- using temporary
建立临时表来保存中间结果，查询完成之后把临时表删除
- using index
这个表示当前的查询是覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现
useing where表明索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找 - using where
使用where进行条件过滤
- using join buffer
使用连接缓存
- impossible where
where语句的结果总是false;
- using filesort
4. 通过索引进行优化
[注意]：
- InnoDB是通过B+Tree结构对主键创建索引，然后叶子节点中存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6位的row_id来作为主键
- 如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再根据主键索引找到对应的记录。
B树
实例图说明:
每个节点占用一个磁盘块，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据域。以根节点为例，关键字为16和34,P1指针指向的子树的数据范围为小于16,P2指针指向的子树的数据范围为16~34,P3指针指向的子树的数据范围大于34。
B+树
3层查询支持千万级数据
索引基本知识
-
索引的优点
- 大大减少了服务器需要的扫描数量
- 帮助服务器避免排序和临时表
order by是全排序，效率很低 - 将随机IO变成顺序IO
-
索引的用处
- 快速匹配WHERE子句的行
- 从consideration中消除行，如果可以在多个索引间进行选择，mysql通常会使用找到最少行的索引
- 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
- 当有表连接的时候，从其它表检索行数据
- 查找特定索引列的min或max值
- 如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组
- 在某些情况下，可以优化查询以检索值而无需查询数据行
-
索引的分类
- 主键索引
- 唯一索引
- 普通索引
- 全文索引
- 组合索引
- 当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要。
- 如果组合索引
-
面试技术名词
-
索引采用的数据结构
- 哈希表
- B+树
-
索引匹配方式
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 '员工记录表';
alter table staffs add index idx_nap(name,age,pos)
- 全值匹配
全值匹配值得是索引中的所有列进行匹配
explain select * from staffs where name ='Mike' and age =30 and pos='dev'
-
匹配最左前缀
只匹配前面的几列
explain select * from staffs where name ='Tom' and age =30
explain select * from staffs where name ='Tom' -
匹配列前缀
可以匹配某一列的值的开头部分
explain select * from staff where name like 'T%'
explain select * from staff where name like '%om' -
匹配范围值
可以查找某一范围的数据
explain select * from staffs where age > 30 -
精确匹配某一列范围匹配另一列
查询第一列的全部和第二列的部分
explain select * from staffs where name ='Tom' and age >=30 -
字段类型不一致的时候会导致不使用索引
explain select * from staffs where name ='del' and age = 20 and pos=20;
-
只访问索引的查询
查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引
explain select name,age,pos from staffs where name ='Mike' and age =30 and pos='dev'
Extra不为空则说明使用了索引覆盖。
5. 索引进行优化
聚簇索引与非聚簇索引
-
聚簇索引
不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起
- 优点
- 可以把相关数据保存到一起。
- 数据访问更快，因为索引和数据保存在同一个树中。
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
- 缺点
- 聚簇索引最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没什么优势 。
- 插入速度严重依赖插入顺序，按照主键的顺序插入是最快的方式。
- 更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置。
- 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题。
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者是由于页分裂导致数据存储不连续的时候。
-
非聚簇索引
数据文件根索引文件分开存放
覆盖索引
- 基本介绍
- 如果一个索引包含所需要查询的字段的值，我们称之为覆盖索引。
- 不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值。
- 不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory不支持覆盖索引
- 优势
- 索引条目通常远小于数据行大小，如果只需要读取索引，那么mysql就会极大的减少数据访问量
- 因为索引是按照列值顺序存储的，对于IO密集型的范围查询会比随机从磁盘读取一行数数据的IO要少的多
- 一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题
- 由于INNODB的聚簇索引，覆盖索引对INNODB表特别有用
- 案例演示
6. 优化的小细节
当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据层。
select actor_id from actor where act_id+1 = 5
尽量使用主键查询而不是其他所有，因为主键查询不会触发回表查询。
使用前缀索引(不是最左匹配)
有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询 效率越高，因为更高的索引可以让MySQL在查找的时候过滤掉更多的行。
一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。
- 案例演示
create table citydemo(city varchar(50) not null); insert into citydemo(city) select city from city;重复执行5次下面的sql语句
insert into citydemo(city) select city from citydemo;更新城市表的名称
update citydemo set city=(select city from city order by rand() limit 1);查找最常见的城市列表,发现每个值都出现45-56次
select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;查找最频繁出现的城市前缀，先从3个前缀字母开始，发现比原来出现的次数更多，可以分别截取多个字符串查看城市出现的次数
select count(*) as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10; select count(*) as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10;
还可以通过另外一种计算完整列的选择性，可以看到当前缀长度达到7之后，再增加前缀长度，选择性提升的幅度已经很小了
select count(distinct left(city,3))/count(*) as sel3, count(distinct left(city,4))/count(*) as sel4, count(distinct left(city,5))/count(*) as sel5, count(distinct left(city,6))/count(*) as sel6, count(distinct left(city,7))/count(*) as sel7, count(distinct left(city,8))/count(*) as sel8 from citydemo;计算完成之后可以创建前缀索引
alter table citydemo add key(city(7));注意:
前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点:mysql无法使用前缀索引做order by和group by;查看索引
- 关于
Cardinality基数:该列的唯一值(近似值)。
探索HyperLogLog算法
使用索引扫描来排序
mysql有两种方式可以生成有序的结果:通过排序操作或者按索引顺序扫描，如果explain出来的type列的值为index,则说明mysql使用了索引扫描来排序;
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条记录就得回表查询一次对应的行，这基本都是随机IO，因此按索引顺序读取的速度通常要比顺序地全表扫描慢。
mysql可以使用同一个索引既满足排序，又用于查找行，如果可能的话，设计索引时是应该尽可能地满足这两种任务。
只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，MySQL才能够使用索引来对结果进行排序，如果插叙需要关联多张表，则只有当order by子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则，MySQL都需要执行顺序操作，而无法利用索引排序。
使用rental_date索引为下面的查询做排序
explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id,customer_id;
order by子句不满足索引最左前缀的要求，也可以用于查询排序，这是因为你的第一列(
rental_date='2005-05-25')被指定为一个常数。
该查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两个组合排序在一起，就形成了最左前缀。
explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id desc\G
union all,in,or都能够使用索引，但是推荐使用in
explain select * from actor where actor_id =1
union all
select * from actor where actor_id =2;
explain select * from actor where actor_id in (1,2);
explain select * from actor where actor_id ='1' or actor_id = '2';
后两条SQL比较;
注意
优先使用
in,如果条件允许的话首先使用exists
范围列可以用到索引
范围条件是:
<、<=、>、>=、boolean
范围列可以用到索引，但是范维列后面的列无法用到索引，索引最多用于一个范围列;
强制类型转换会全表扫描
create table user(
id int,
name varchar(10),
phone varchar(11)
);
alter table user add index user_idx_phone(phone);
insert into user values(1,'Tom','17787652467');
更新频繁，数据区分度不高字段不宜建立索引
- 更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能；
- 类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据;
- 一般区分度在80%以上的时候就可以建立索引，区分度可以使用
count(distinct(列名))/count(*)来计算
创建索引的列，不允许为null,可能会得到不符合预期的结果
当需要进行表连接的时候，最好不要超过三张表因为需要join的字段数据类型必须一致
join的多种方式
Simple Nested-Loop Join
r为驱动表，s为匹配表，可以看到从r中分别取出每一个记录去匹配s表的列，然后再合并数据，对s表进行r表的行数次访问，对数据的开销比较大。
Index Nested-Loop Join
这个要求非驱动表(匹配表s)上有索引，可以通过索引来减少比较，加速查询。
在查询时，驱动表®会根据关联字段的索引进行查找，当在索引上找到符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表查询。
如果非驱动表(s)的关联键是主键的话，性能就会非常高，如果不是主键，要进行多次回表查询，先关联索引，然后再根据二级索引的主键ID进行回表操作，性能上索引比主键要慢。
Block Nested-Loop Join
如果有索引，会选取第二种方式进行join,但如果join列没有索引，就会采用Block Nested-Loop join。可以看到中间有个
join buffer缓冲区，是将驱动表的所有join相关的列都先缓存到join buffer中，然后批量与匹配表进行匹配，将第一种多次比较合并为一次，降低了非驱动表(s)的访问频率。默认情况下join_buffer_size=256K,在查找的时候Mysql会将所有的需要的列缓存到join buffer当中，包括select的列，而不仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。
能使用limit的时候尽量使用limit
limit的作用应该是限制输出，而不是分页，分页只是应用场景。
单表索引建议控制在5个以内
字段值为null，也会占用存储空间。
单索引字段数不允许超过5个
组合索引
创建索引应该避免以下错误概念
- 索引越多越好
- 过早优化，在不了解系统的情况下进行优化
索引监控
show status like 'Handler_read%'
参数解释
Handler_read_first:读取索引第一个条目(根节点)的次数
Handler_read_key:通过index获取数据的次数
Handler_read_last:读取索引最后一个条目的次数
Handler_read_next:通过索引读取下一条数据的次数
Handler_read_prev:通过索引读取上一条数据的次数
Handler_read_rnd:从固定位置读取数据的次数
Handler_read_rnd_next:从数据节点读取下一条数据的次数
重点查看
Handler_read_key和Handler_read_rnd_next，这两个值越大越好
7. 查询优化
7.1 查询慢的原因
7.1.1 网络
7.1.2 CPU
时间片
7.1.3 IO
7.1.4 上下文切换
7.1.5 系统调用
7.1.6 生成统计信息
7.1.7 锁等待时间
7.2 优化数据访问
7.2.1 查询性能低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量数据。我们可以通过减少访问数据量的方式进行优化.
- 确认应用程序是否再检索大量超过需要的数据。
查看执行计划，大概扫描了多少数据。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SSfSSPcG-1677401425934)(C:\Users\Whaleson\AppData\Roaming\Typora\typora-user-images\image-20230226162717277.png)]](https://i-blog.csdnimg.cn/blog_migrate/d74444590cd5499ad5f5648477550aff.png)
我们需要查询
rental表的第10000行~第10004行explain select * from rental limit 10000,5;
从截图中可以看出为了获取5条数据而扫描了16008条数据(总共16044行，基本上是进行了全表扫描)。
- 确认mysql服务器层是否在分析大量超过需要的数据行
7.2.2 是否向数据请求了不需要的数据
- 查询不需要的记录
我们常常会误以为mysql会只返回需要的数据，实际上mysql确实先返回全部结果再进行计算，再日常开发习惯中，进场是先用select语句查询大量的结果，然后获取前面的N行后关闭结果集。
优化方式是在查询后面添加limit - 多表关联时返回全部列
select * from actor inner join file_actor using(actor_id) inner join file using(film_id) where film.title='Academy';select actor.* from actor.... - 总是取出全部列
在实际需求中，禁止使用
select *，虽然这种方式能简化开发，但是会影响查询的性能，所以尽量不要使用。 - 重复查询相同的数据
如果需要不断的重复执行相同的查询，且每次返回完全相同的数据。因此，基于这样的应用场景，我们可以将这部分数据缓存(Redis)起来，这样的话能够提高查询效率。LRU
7.3 执行过程的优化
7.3.1 查询缓存
8.0已经弃用在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据，如果查询恰好命中了查询缓存，那么会在返回结果之前检查用户权限，如果权限没问题，那么MySQL会跳过所有的阶段，就直接从内存中拿到结果并返回给客户端。
7.3.2 查询过程的优化
MySQL查询完缓存之后会经过以下几个步骤:解析SQL、预处理、优化SQL执行计划，这几个步骤出现的任何错误，都可能会终止查询。
7.3.2.1 语法解析器和预处理
MySQL通过关键字将SQL语句进行解析，并生成一棵解析树(AST-Tree)，MySQL解析器将使用MySQL语法检查规则验证和解析查询，例如验证使用了错误的关键字或者顺序是否正确等等，预处理会进一步检查解析树是否合法，例如表名和列名是否存在，是否有歧义，还会验证权限等等。
7.3.2.2 查询优化器
当语法树没有问题之后，相应的要由优化器将其转换成执行计划，一条查询语句可以使用非常多的执行方式，最后都可以得到对应的结果，但是不同的执行方式带来的效率是不同的，优化器的最主要目的就是要选择最有效的而执行计划。
mysql使用的是基于成本的优化器(RBO和CBO)，在优化的时候会尝试预测一个查询使用某种查询计划时候的成本，并选择其中成本最小的一个。
-
select count(*) from file_actor; show status like 'last_query_cost';可以看到这条查询语句大概需要1104个数据页才能查找到对应的数据，这是经过一系列的统计信息计算来的。
- 每个表或索引的页面个数
- 索引的基数
- 索引和数据行的长度
- 索引分布情况
-
在很多情况下MySQL会选择错误的执行计划，原因如下:
- 统计信息不准确
MySQL因为其
mvcc的架构，并不能维护一个数据表的行数的精确统计信息 - 执行计划的成本估算不等于实际执行的成本
有时候某个执行计划虽然读取更多的页，但是它的成本却更小，因为如果这些页面都是顺序或者这些页面都已经在内存中的话，那么它的访问成本将很小，MySQL层面并不知道哪些页面在内存中，哪些在磁盘，所以查询实际过程中到底需要多少次IO是无法得知的。
- MySQL最优可能和实际不一样
MySQL的优化是基于成本模型的优化，但是有可能不是最快的优化。
- MySQL不考虑其它并发执行的查询
- MySQL不会考虑不受其控制的操作成本
执行存储过程或者用户自定义函数的成本。
- 统计信息不准确
-
优化器的优化策略
- 静态优化
直接对解析树进行分析，并完成优化
- 动态优化
动态优化与查询的上下文有关，也可能跟取值、索引对应的行数有关。
MySQL对查询的静态化优化只需要一次，但是对动态优化在每次执行的时候都需要重新评估
- 静态优化
-
优化器的优化类型
- 重新定义关联表的顺序
数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序是优化器很重要的功能
- 将外连接转换成内连接，内连接的效率要高于外连接
- 使用等价变换规则，MySQL可以使用一些等价变化来简化并规划表达式
- 优化count(),min(),max()
索引和列是否可以为空通常可以帮助MySQL优化这类表达式:列如，要找到某一列的最小值，只需要查询所有的最左端的记录即可，不需要全文扫描比较。
- 预估并转化为常数表达式，当MySQL检测到一个表达式可以转换为常数的时候，就会一直把该表达式作为常数进行处理
- 索引覆盖扫描，当索引中的列包含所有查询中需要使用的列的时候，使用覆盖索引
- 子查询优化
MySQL在某些情况下可以将子查询转换成一种效率更高的形式，从而减少多个查询多次对数据进行访问，列如将经常查询的数据放到缓存中
- 等值传播
如果两个列的值通过等式关联，那么MySQL能够把其中一个列的where条件传递到另一个上:
explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;这里使用film_id字段进行等值关联，film_id这个列不仅适用于film表而且适用于film_actor表
explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500 and film_actor.film_id > 500;
- 重新定义关联表的顺序
-
关联查询
（1） Join Buffer会缓存所有参与查询的列而不是只有Join的列。(比如select后面的列)
（2）可以通过join_buffer_size缓存大小
（3）join_buffer_size的默认值是256K，join_buffer_size的最大值在MySQL 5.1.222版本之前是4G,而之后的版本才能在64位操作系统虾申请大于4G-1的Join Buffer空间。
（4） 使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置
block_nested_loop为on,默认为开启。show variables like '%optimizer_switch%'; -
案例演示
查看不同的顺序执行方式对查询性能的影响:
explain select film.film_id,film.title,film.release_year ,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id)
查看执行成本
按照自己预想的规定顺序执行explain select straight_join film.film_id,film.title,film.release_year ,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id);
查看两个结果集的
row列，可以看出扫描的行数不同。 -
排序优化
排序的算法-
两次传输排序
第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排好序的结果按照需要去读取数据行。
这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高。
两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作。 -
单次传输排序
先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据。
当需要排序的列总大小加上
order by的列的大小超过max_lenght_for_sort_data定义的字节，MySQL会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式。show variables like '%max_length_for_sort_data%';
1024字节 -
7.4 优化特定类型的查询
7.4.1 优化COUNT查询
count()是特殊的函数，有两种不同的作用，一种是某个列值 的数量，也可以铜价行数。
- 总有人认为MYISAM的count函数比较快，这是有前提条件的，只有没有任何where条件的count(*)才是比较快的。(MYISAM游曳哥变量用来保存整体的行数)
-
使用近似值
在某些应用场景中，不需要完全精确的值，可以参考使用近似值来代替，比如可以使用explain来获取近似的值。
其实在很多OLAP的应用中，需要计算某一个列的基数，有一个计算近似值的算法叫做hyperloglog。 -
更复杂的优化
一般情况下，count()需要扫描大量的行数据才能获取精确的数据，其实很难优化，在实际操作的过程的时候可以考虑使用索引覆盖扫描，或者添加汇总表，或者增加外部缓存系统。
7.4.2 优化关联查询
-
确保
on或者using子句的列上有索引，在创建索引的时候就要考虑到关联的顺序 -
确保任何的
group by和order by中的表达式只涉及到一个表中 的列，这样Mysql才能使用索引来优化这个过程。
7.4.3 优化子查询
子查询的优化最重要的优化建议是尽可能使用关联查询代替。
7.4.4 优化group by和distinct(忽略)
很多场景下，mysql使用相同的方法来优化group by和distinct的查询，使用索引是最有效的方式，但是有很多的情况下无法使用索引，可以使用临时表或者文件排序来分组
如果对关联查询做分组，并且是按照查找表中的某个列进行分组，那么可以采用表的标识列分组的效率比其他列更高
-
select actor.first_name,actor.last_name,count(*) fromfile_actor inner join actor using(actor_id) group by actor.first_name,actor.last_name -
select actor.first_name,actor.last_name,count(*) fromfile_actor inner join actor using(actor_id) group by actor.actor_id(未必准确)
7.4.5 优化limit分页
在很多应用场景中，我们需要将数据进行分页，一般会使用limit加上偏移量的方法实现，同时加上合适的order by子句，如果这种方式有索引的帮助，效率通常不错，否则的话需要进行大量的文件排序操作，还有一种情况，当偏移量非常大的时候，前面的大部分数据会被抛弃，这样的代价太高。
要优化这种查询的话，要么是在页面中限制分页的数量，要么优化大偏移量的性能。
select film_id,description from film order by title lmit 50,5select film.film_id,film,description from film inner join (select film_id from film oder by title limit 50,5) as lim using(film_id);- MySQL的limit优化
7.4.6 优化union查询
除非确实需要消除重复的行，否则一定要使用
union all，没有all关键字，MySQL在查询的时候会给临时表加上distinct的关键字，这个操作的代价很高。
7.4.7 推荐使用用户自定义变量
7.4.7.1 自定义变量的使用
set @one :=1;
select @one;
set @min_actor :=(select min(actor_id) from actor);
set @last_week := current_date-interval 1 week;
7.4.7.2 自定义变量的限制
1.无法使用查询缓存;
2.不能在使用常量或标识符的地方使用自定义变量，例如表名、列名或者limit子句;
3.用户自定义变量的生命周期是在一个连接中有效，所以不能使用他们来做连接间的通信;
4.不能显式地声明自定义变量的类型；
5.mysql优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想地方式运行。
6.赋值符号:= 的优先级非常低，所以在使用赋值表达式的时候应该明确使用括号;
7.使用未定义变量不会产生任何语法错误。
7.4.7.3 自定义变量的使用案例
7.4.7.3.1 优化排名语句
- 在给一个变量赋值的同时使用这个变量
select actor_id,@rownums:=@rownums+1 as rownums from actor limit 10;
- 查询获取演过最多电影的前10名演员，然后根据演出电影次数做一个排名
set @actor_num:=0;
select actor_id,cnt,@actor_num:=@actor_num+1 from (select
actor_id,count(*) as cnt from film_actor group by actor_id
order by cnt desc limit 10 )t;
7.4.7.3.2 避免重新查询刚刚更新的数据
- 当需要高效的更新一条记录的时间戳，同时希望查询当前记录中存放的时间戳是什么
create table t1(id int,lastUpdated datetime);
insert into t1 values(1,now());
update t1 set lastUpdated = now() where id =1;
select lastUpdated from t1 where id=1;
update t1 set lastUpdated = now() where id =1 and @now:=now();
select @now;
7.4.7.3.3 确定取值的顺序
- 在赋值和读取变量的时候可能是在查询的不同阶段;
set @rownum:=0;
select actor_id,@rownum:=@rownum+1 as cnt
from actor where @rownum<=1;
# 因为where和select在查询的不同阶段执行，所以查询到两条记录，这不符合预期;
set @rownum:=0;
select actor_id,@rownum:=@rownum+1 as cnt
from actor where @rownum <=1 order by first_name;
# 当引入order by之后，发现打印出了全部结果，这是因为order by引入了文件排序，而
#where条件是在文件排序操作之前取值的。
#解决这个问题的关键在于让变量的赋值和取值发生在执行的同一阶段:
set @rownum:=0;
select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1
8.分区表
8.1 分区表的应用场景
- 表非常大以至于无法全部放在内存中，或者只在最后部分有热点数据，其他均是历史数据。
- 分区表的数据更容易维护
- 批量处理大量数据可以使用清除整个分区的方式
- 对一个独立的分区进行优化、检查、修复等操作
- 分区表的数据可以分布在不同的屋里设备上，从而高效地利用多个硬件设备
- 可以使用分区表来避免某些特殊的瓶颈
- innodb的单个索引互斥访问
- ext3文件系统的inode竞争
- 可以备份和恢复独立的分区
8.2 分区表的限制
- 分区表无法使用外键约束
8.3 分区表的原理
分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样(所有底层表都必须使用相同的存储引擎)，分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和普通表没有什么不同，存储引擎也无需知道这是一个普通表还是一个分区表的一部分。
分区表的操作按照以下的操作逻辑进行:
select查询
当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否九二一过滤部分分区，然后再调用相应的存储引擎接口访问各个分区的数据。
insert操作
当写入一条记录的时候，分区层先打开并锁住所有底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表。
delete操作
当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区表，最后对相应底层表进行删除操作。
update操作
当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该再哪个分区，最后对数据底层表进行写入操作，并对数据源所在的底层表进行删除操作。
有些操作是支持过滤的，例如，当删除一条记录时，MySQL需要先找到这条记录，如果where语句恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉，这对update同样有效。如果是insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉。MySQL先确定这条记录属于哪个分区，再将记录写入对应的分区表，无需对任何其他分区进行操作。
虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，例如innodb,则会在分区层释放对应表锁。
8.4 分区表的类型
8.4.1范围分区
根据列值在给定范围内将行分配给分区;
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 MAXVALUE
);
DELETE FROM employees WHERE YEAR(separated) <= 1990;
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 RANGE ( YEAR(separated) ) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
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)
);
-- 任何其他涉及时间戳值的表达式都不是允许的。
注意: 使用采用日期或时间类型的分区列 除DATE或 DATETIME之外，不支持 。
删除分区数据:
ALTER TABLE employees DROP PARTITION p0;
-- 以下语句效率很低
DELETE FROM employees WHERE YEAR(separated) <= 1990;.
8.4.2 列表分区
类似于按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)
);
删除分区数据
ALTER TABLE employees TRUNCATE PARTITION pWest,
-- 以下语句执行效率很低
DELETE FROM employees WHERE store_id IN (4,12,13,14,18);.
关于INSERT插入
当使用单个 INSERT 语句将多行插入单个 InnoDB 表时，将该语句视为单个 事务，以便存在任何不匹配的值导致 语句完全失败，因此不插入任何行。
mysql> CREATE TABLE h2 (
-> c1 INT,
-> c2 INT
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (1, 4, 7),
-> PARTITION p1 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3
-- 尽管会发出警告，但可以使用关键字忽略此类错误 对于包含不匹配分区列值的每一行
mysql> TRUNCATE h2;
Query OK, 1 row affected (0.00 sec)
mysql> TABLE h2;
Empty set (0.00 sec)
mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 5 Duplicates: 2 Warnings: 2
mysql> SHOW WARNINGS;
+---------+------+------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------+
| Warning | 1526 | Table has no partition for value 6 |
| Warning | 1526 | Table has no partition for value 3 |
+---------+------+------------------------------------+
2 rows in set (0.00 sec)
8.4.3 列分区
MySQL从5.5开始支持column分区，可以认为是range和list的升级版，在5.5之后，可以使用column分区替代range和list，但是column分区只接受普通列不接受表达式;
mysql> CREATE TABLE rcx (
-> a INT,
-> b INT,
-> c CHAR(3),
-> d INT
-> )
-> PARTITION BY RANGE COLUMNS(a,d,c) (
-> PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
-> PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
-> PARTITION p2 VALUES LESS THAN (15,30,'sss'),
-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
-> );
Query OK, 0 rows affected (0.15 sec)
查看分区数据
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'r1';
CREATE TABLE rc1 (
a INT,
b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
PARTITION p0 VALUES LESS THAN (5, 12),
PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);
ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired) (
PARTITION p0 VALUES LESS THAN ('1970-01-01'),
PARTITION p1 VALUES LESS THAN ('1980-01-01'),
PARTITION p2 VALUES LESS THAN ('1990-01-01'),
PARTITION p3 VALUES LESS THAN ('2000-01-01'),
PARTITION p4 VALUES LESS THAN ('2010-01-01'),
PARTITION p5 VALUES LESS THAN (MAXVALUE)
);
CREATE TABLE customers_1 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);
CREATE TABLE customers_2 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(renewal) (
PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
'2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
'2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
'2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
'2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);
8.4.4 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;
8.4.5 key分区
类似于hash分区，区别在于key分区只支持一列或者多列，且MySQL服务器提供其自身的哈希函数，必须有一列或多列包含整数值
CREATE TABLE k1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;
8.4.6 子分区
8.5 如何使用分区表
如果需要从非常大的表中查询出某一段时间的记录，而这张表包含很多年的历史数据，数据是按照时间排序的，此时应如何查询数据呢？
因为数据量巨大，肯定不能在每次查询的时候扫描全表。考虑到索引在空间和维护上的消耗，也不希望使用索引，即使使用索引，会发现会产生大量的碎片，还会产生大量的随机IO，但是当数据量超大的时候，索引也就无法其作用了，此时可以考虑使用分区来解决。
8.5.1 全量扫描数据，不要任何索引
使用简单的分区方式存放表，不要任何索引，根据分区规则大致定位需要的数据为止，通过使用where条件将需要的数据限制在少数分区中，这种策略适用于以正常的方式访问大量数据。
8.5.2索引数据，并分离热点
如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以让这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中，让这个分区中的数据能够有机会都缓存在内存中，这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存。
8.6 在使用分区表的时候需要注意的问题
- null值会使分区过滤无效
- 分区列和索引列不匹配，会导致查询无法进行分区过滤
- 选择分区的成本可能很高
- 打开并锁住所有底层表的成本可能很高
- 维护分区的成本可能很高
9 服务器参数设置
9.1 通用的
-
数据文件存放的目录
datadir=/var/lib/mysql -
mysql.socket表示server和client在同一台服务器，并且使用localhost进行连接，就会使用socket进行连接
socket=/var/lib/mysql/mysql.sock -
存储mysql的PID
pid_file=/var/lib/mysql/mysql.pid -
mysql服务的端口号
port = 3306 -
mysql的存储引擎
default_storage_engine=InnoDB -
当忘记Mysql的用户名和密码的时候，可以在mysql配置文件中配置该参数，跳过权限表验证，不需要密码即可登录mysql
skip-grant-tables
9.2 字符(character)
-
客户端数据的字符集
character_set_lient -
MySQL处理客户端发来的消息时，会把这些数据转换为连接的字符集格式
character_set_connection -
mysql发送给客户端的结果集所用的字符集
character_set_results -
数据库默认字符集
character_set_database -
MySQL server的默认字符集
character_set_server
9.2 连接(connect)
show variable like '%max_connection%';
set global max_connections=1024;
-
MySQL最大连接数，如果数据库的并发连接请求比较大，应该调高这个值
max_connections -
限制每个用户的连接个数
max_user_connections -
mysql能够暂存的连接数量，当MySQL的线程在一个很短的时间内得到非常多的连接请求时，就会起作用，如果MySQL的连接数量达到max_connections时，新的请求就被会存储到堆栈中，以等待某一个连接释放资源，如果等待的连接数量超过back_log,则不再接受连接资源
back_log -
MySQL在关闭一个非交互的连接之前需要等待的时长
JDBC是非交互式的
wait_timeout -
关闭一个交互连接之前需要等待的秒数
命令行是交互式的;
interactive_timeout
9.3 日志(log)
-
指定错误日志文件名称，用于记录当MySQL启动和停止时以及服务器在运行中发生任何严重错误时的相关信息
log_error -
指定二进制日志文件名称，用于记录对数据造成更改的所有查询语句
建议开启，虽然会造成性能损失，但是可以保证数据完整性(丢失后可恢复);
log_bin -
指定将更新记录到二进制日志的数据库，其他所有没有显式指定的数据库更新将忽略，不记录在日志中
binlog_do_db -
指定不将更新记录到二进制日志的数据库
binlog_ignore_db -
指定多少次写日志后同步磁盘
sync_binlog -
是否开启查询日志记录
general_log -
指定查询日志文件名，用于记录所有的查询语句
general_log_file -
是否开启慢查询日志记录
show_query_log -
指定慢查询日志文件名称，用于记录耗时比较长的查询语句
show_query_log_file -
设置慢查询的时间，超过这个时间的查询语句才会记录日志
long_query_time -
是否将管理语句写入慢查询日志
log_slow_admin_statements
关于日志
ACID
原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）
在日志中分为
Redo Log、Undo Log、BinLog;
Redo Log、Undo Log全部归属于InnoDB存储引擎,BinLog归属于MySql Server
ACID的实现方式
原子性（atomicity):
Undo Log
隔离性（isolation):锁
持久性（durability):Redo Log
一致性（consistency):是由原子性、隔离性、持久性实现;
Redo log
- 当发生数据修改的时候，
innodb引擎会将记录写到redo log中，并更新内存，此时更新就算完成了，同时innodb引擎会在合适的时机将记录操作到磁盘中 RedoLog是固定大小的，是循环写的过程- 有了
RedoLog之后，innodb就可以保证即使数据库发生异常重启，之前的记录也不会丢失，叫做crash-safe
Undo log
Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用Undo Log来实现多版本并发控制(简称:MVCC)
在操作任何数据之前，首先将数据备份到一个地方(这个存储数据备份的地方称为
Undo Log)。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
注意:
Undo Log是逻辑日志，可以理解为:
- 当
delete一条数据时，undo log会记录一条对应的insert记录; - 当
insert一条记录时，undo log中会记录一条对应的delete记录 - 当
update一条记录时，它记录一条对应相反的update记录
BinLog——服务端的日志文件
BinLog是server层的日志，主要做MySQL功能层面的事情- 与
redo日志的区别:redo是innodb独有的，binlog是所有引擎都可以使用的redo是物理日志，记录的是在某个数据页上做了什么修改，binlog是逻辑日志，记录的是这个语句的原始逻辑redo是循环写的，空间会用完，binlog可以追加写的，不会覆盖之前的日志信息
- Binlog会记录所有的逻辑，并采作用追加写的方式
- 一般在企业中数据库会有备份系统，可以定期执行备份，备份的周期可以自己设置
- 恢复数据的过程
- 1.找到最近一次的全量备份数据
-
- 从备份的时间点开始，将备份的binlog取出来，重放到要恢复的那个时刻
数据更新的流程
执行流程
-
- 执行器先从引擎中找到数据，如果在内存中直接返回，如果不在内存中，查询后返回;
-
- 执行器拿到数据之后会先修改数据，然后调用引擎接口重新写入数据
-
- 引擎将数据更新到内存，同时写数据到
redo中，此时处于prepare阶段，并通知执行器执行完成，随时可以操作
- 引擎将数据更新到内存，同时写数据到
-
- 执行器生成或者操作的
binlog
- 执行器生成或者操作的
-
- 执行器调用引擎的事务提交接口，引擎把刚刚写完的
redo改成commit状态，更新完成
- 执行器调用引擎的事务提交接口，引擎把刚刚写完的
Redo log的两阶段提交
先写redo log后写binlog:假设在redo log写完，binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log写完之后，即使系统崩溃，仍然能够把数据恢复回来，所以恢复后这一行的值是1，但是由于binlog没写完就crash了，这时候binlog里面就没有这条语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。先写binlog后写 redo log:如果在binlog写完之后crash由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值就是0。但是binlog里面已经记录了"把c从0改成1"这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。
cache缓存
- 索引缓存区的大小(只对MyISAM有效)
query_cache_size - query cache
- 查询缓存的大小，未来版本被删除
query_cache_size - 超出此大小的查询将不被缓存
query_cache_limit - 缓存块最小大小
query_cache_min_res_unit - 缓存类型，决定缓存什么样的查询
query_cache_type
0:禁用
1:表示缓存所有结果，除非sql语句中使用sql_no_cache禁用查询缓存;
2:表示只缓存select语句中通过sql_cache指定需要缓存的查询;
- 查询缓存的大小，未来版本被删除
扫一扫
476
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
