首页 > 数据库 >mysql优化

mysql优化

时间：2023-03-12 16:56:25浏览次数：62  
标签：07 ref 索引 2021 mysql NULL 优化 id

mysql使用总结

针对字段判断

case when 字段判断 then

使用示例

#建表语句
CREATE TABLE `user` (
  `id` int(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


#第一种用法

SELECT id ,
(CASE when name is null then '暂无1' else name end),age 
from `user` 


#第二种用法
SELECT id ,
(case when (name is null or '') then '暂无' else name end),
(case when (age is null or '') then '暂无' else name end)
FROM `user`;


#第三种写法
select 
case  
when budget_type='预算' then  '0' 
when budget_type='预算内调剂' then  '0' 
when budget_type='预算内调剂' then  '0' 
 end 
from budget_process where process_id=15


#第四种语法(针对一个字段多种情况判断)
select 
case  
when budget_type='预算' then  (
SELECT no_reallocation_apply from function_settings WHERE id =1
) 
when budget_type='预算内调剂' then (
SELECT business_department_adjustment_apply from function_settings WHERE id =1
) 
when budget_type='总公司调剂' then   (
SELECT head_office_transfer_apply from function_settings WHERE id =1
) 
end 
from budget_process where process_id=17

update中使用case when then

建表语句

CREATE TABLE `budget` (
  `process_id` int(11) NOT NULL AUTO_INCREMENT,
  `budget_id` int(11) NOT NULL DEFAULT '-1' COMMENT '预算编号',
  `org_id` int(11) NOT NULL COMMENT '机构代码',
  `budget_type` enum('预算内','营业部内调剂','总公司调剂') NOT NULL DEFAULT '预算内' COMMENT '调剂类型 0预算 1预算内调剂 2总公司调剂',
  `flow_status` enum('未申请','已申请') NOT NULL DEFAULT '未申请' COMMENT '状态:已申请0,未申请1',
  `applicat_id` char(11) NOT NULL COMMENT '申请人id',
  `applicat_name` varchar(255) DEFAULT NULL COMMENT '申请人姓名',
  `branch_office` varchar(255) DEFAULT '' COMMENT '分公司',
  `name_of_business_department` varchar(255) DEFAULT '' COMMENT '营业部名称',
  `budget_classification` enum('固定资产','信息工程','软件购置','软件维护','外包维护','耗材费用','线路采购') NOT NULL COMMENT '预算分类',
  `equipment_type` varchar(255) DEFAULT '' COMMENT '设备类型',
  `asset_classification` varchar(255) DEFAULT '' COMMENT '资产分类',
  `asset_subclass` varchar(255) DEFAULT '' COMMENT '资产子类',
  `fixed_assets_no` varchar(255) DEFAULT '' COMMENT '固定资产编号',
  `use_people` varchar(45) DEFAULT NULL COMMENT '使用人',
  `number_of_cameras` varchar(255) DEFAULT '' COMMENT '摄像头数量',
  `software_classification` varchar(255) DEFAULT '' COMMENT '软件分类',
  `software_name` varchar(255) DEFAULT '' COMMENT '软件名称',
  `version_number` varchar(255) DEFAULT '' COMMENT '版本号',
  `enable_time` varchar(255) DEFAULT '' COMMENT '启用时间',
  `purpose` varchar(255) DEFAULT '' COMMENT '用途',
  `user_department` varchar(255) DEFAULT '' COMMENT '使用部门',
  `current_executable_amount` decimal(11,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '当前可执行金额',
  `executed_amount` decimal(11,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '已执行金额',
  `apply_quantity` int(11) DEFAULT NULL COMMENT '审批数量',
  `apply_unit_price` decimal(11,2) unsigned DEFAULT NULL COMMENT '审批单价',
  `apply_amount` decimal(11,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '审批金额',
  `description` varchar(255) DEFAULT '' COMMENT '说明',
  `usage_time` date DEFAULT NULL COMMENT '使用时间',
  `year` int(11) NOT NULL,
  `created_time` datetime NOT NULL,
  `updated_time` datetime NOT NULL,
  PRIMARY KEY (`process_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12346 DEFAULT CHARSET=utf8mb4 COMMENT='预算表';

使用


UPDATE budget
SET current_executable_amount = current_executable_amount + cast(0.01 AS DECIMAL(11, 2)),
flow_status= case  when (current_executable_amount>0.00  )THEN  
'未申请'
else 
'已申请'
end
WHERE
	process_id = 1

有其他例子:

https://blog.csdn.net/qq_36850813/article/details/80449860?utm_medium=distribute.pc_relevant_t0.none-task-blog-2~default~CTRLIST~default-1.no_search_link&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2~default~CTRLIST~default-1.no_search_link

mysql复杂查询

#查询以user_id为分组多条记录的平均值
SELECT user_id,SUM(TIMESTAMPDIFF(SECOND ,created_time,updated_time))/COUNT(user_id)   FROM `card` where user_id in(1,2,3,4) GROUP BY user_id 
DROP TABLE IF EXISTS `card`;
CREATE TABLE `card` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `bank_card` varchar(255) DEFAULT NULL,
  `created_time` datetime DEFAULT NULL,
  `updated_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of card
-- ----------------------------
INSERT INTO `card` VALUES ('1', '1', '152544589', '2021-10-21 07:52:22', '2021-10-21 07:53:22');
INSERT INTO `card` VALUES ('2', '1', '152444589', '2021-10-21 07:52:22', '2021-10-21 07:52:22');
INSERT INTO `card` VALUES ('3', '1', '152547889', '2021-10-21 07:52:22', '2021-10-21 07:52:22');
INSERT INTO `card` VALUES ('4', '1', '152544589', '2021-10-21 07:52:22', '2021-10-21 07:52:22');
INSERT INTO `card` VALUES ('5', '2', '152547589', '2021-10-21 07:52:22', '2021-10-21 07:53:22');
INSERT INTO `card` VALUES ('6', '2', '152547589', '2021-10-21 07:52:22', '2021-10-21 07:54:22');
INSERT INTO `card` VALUES ('7', '2', '152556589', '2021-10-21 07:52:22', '2021-10-21 07:55:22');
INSERT INTO `card` VALUES ('8', '2', '152544639', '2021-10-21 07:52:22', '2021-10-21 07:56:22');
INSERT INTO `card` VALUES ('9', '3', '152544589', '2021-10-21 07:52:22', '2021-10-21 07:55:22');
INSERT INTO `card` VALUES ('10', '3', '152444589', '2021-10-21 07:52:22', '2021-10-21 07:55:22');
INSERT INTO `card` VALUES ('11', '3', '152547889', '2021-10-21 07:52:22', '2021-10-21 07:56:22');
INSERT INTO `card` VALUES ('12', '3', '152544589', '2021-10-21 07:52:22', '2021-10-21 07:55:22');
INSERT INTO `card` VALUES ('13', '4', '152547589', '2021-10-21 07:52:22', '2021-10-21 07:56:22');
INSERT INTO `card` VALUES ('14', '4', '152547589', '2021-10-21 07:52:22', '2021-10-21 07:56:22');
INSERT INTO `card` VALUES ('15', '4', '152556589', '2021-10-21 07:52:22', '2021-10-21 07:56:22');
INSERT INTO `card` VALUES ('16', '4', '152544639', '2021-10-21 07:52:22', '2021-10-21 07:56:22');

mysql优化

主键索引

1616837474860

辅助索引

1616832290759

范围

limit优化

1616833006607

利用索引

1616833021883

SELECT
                                                dti.pk,
            dti.mobile 'custPhone',
            dti.custname 'custName',
            dti.channel_name 'channelName',
            dti.progress_time 'bandDingTime',
            dti.progress 'taskProgress',
            SEC_TO_TIME(UNIX_TIMESTAMP(SYSDATE()) - UNIX_TIMESTAMP( dti.progress_time )) AS 'breakPointTime',
            dci.progress 'custProgress',
            dci.progress_time 'stageTime',
            dti.staff_name 'staff',
            dti.task_status 'taskStatus',
            dti.task_state,
            dti.task_cust_desire,
            dti.task_is_continue
        FROM
--                                 ddyx_task_info dti
             (SELECT
        * 
FROM
        ddyx_task_info 
WHERE
        is_delete = '0' 
        AND task_mode = '1'
  AND channel_name = '互联网同花顺'
--   AND dti.progress_time between '' and ''
--   AND dti.progress = ''
--   AND dti.staff_name = ''
--   AND dti.task_status = ''
  AND pk >= (select pk from ddyx_task_info limit 100000,1)
        order by progress_time
        LIMIT 10) dti
LEFT JOIN
    ddyx_custom_info dci 
        ON dti.mobile = dci.cust_mobile

索引下推

索引下推是用于没有覆盖索引情况下的查询优化

索引下推的标志:explain的extra列中显示了Using index condition。

使用条件

  1. 需要整表扫描的情况。比如：range, ref, eq_ref, ref_or_null 。适用于InnoDB 引擎和 MyISAM 引擎的查询。（5.6版本不适用分区表查询，5.7版本后可以用于分区表查询）。

  2. 对于InnDB引擎只适用于二级索引，因为InnDB的聚簇索引会将整行数据读到InnDB的缓冲区，这样一来索引条件下推的主要目的减少IO次数就失去了意义。因为数据已经在内存中了，不再需要去读取了。

  3. 引用子查询的条件不能下推。

  4. 调用存储过程的条件不能下推，存储引擎无法调用位于MySQL服务器中的存储过程。

  5. 触发条件不能下推。

    工作过程:

    1616837076969

实战sql语句

添加组合索引建表

#查看表的索引结构
show INDEX from abc_innodb
#组合索引建表
CREATE TABLE `abc_innodb`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11)     DEFAULT NULL,
  `b`  int(11)     DEFAULT NULL,
  `c`  varchar(10) DEFAULT NULL,
  `d`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;

#建表后添加组合索引
CREATE INDEX idx_cd ON abc_innodb(c,d)

 SELECT * from abc_innodb WHERE a=1

1616835784374

SELECT * from abc_innodb WHERE id>=1

1616835792103

进行范围查询: > <可以走索引

SELECT * from abc_innodb WHERE a>=5

1616837895144

最左前缀匹配原则

条件

1.需要查询的列和组合索引的列顺序一致

select a,b,c form

2.查询不要跨列

注意:
  1. 对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。
  2. 如果建立的索引顺序是（a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）这种顺序的索引，那么 d 是用不到索引的，但是如果建立的是 （a,b,d,c）这种顺序的索引的话，那么就没问题，而且 a，b，d 的顺序可以随意调换
  3. = 和 in 可以乱序，比如 a = 3 and b = 4 and c = 5 建立 （a，b，c）索引可以任意顺序

为什么会有最左前缀原则?

  1. 首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推。第一个字段是绝对有序的，从第二个字段开始是无序的，这就解释了为什么直接使用第二字段进行条件判断用不到索引了（从第二个字段开始，无序，无法走 B+ Tree 索引）
  2. 避免回表
  3. select id,name,sex from user where name ='zhangsan';这个语句在业务上频繁使用到，而user表的其他字段使用频率远低于它，在这种情况下，如果我们在建立 name 字段的索引的时候，不是使用单一索引，而是使用联合索引（name，sex）这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。这样就可以有效地避免了回表再获取sex的数据。
  4. 频繁查询的字段且不是主键,应该不使用单一索引,使用联合索引(前提创建联合索引)

回表

​ 磁盘IO数：辅助索引3次+获取记录回表3次
​ select * from t_user_innodb where age=19;
​ 1616838345536

explain 字段解释

从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

1616846314865

1. select_type

就是select类型,可以有以下几种

(1) SIMPLE
简单SELECT(不使用UNION或子查询等)

2.table:

*table表示查询的表名，如果使用了别名，那么这儿显示的就是别名，如果为null则代表不涉及表操作，如果为<>括起来，比如则代表该表是id为N的查询产生的临时表，<unionM,N>表示该表为id为M和N union之后产生的临时表。

3.type

这列很重要,显示了连接使用了哪种类别,有无使用索引.
从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

(1).system

这是const联接类型的一个特例。表仅有一行满足条件.如下(t3表上的id是 primary key)

mysql> explain select * from (select * from t3 where id=3952602) a ;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

(2).const

表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次！

const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。在下面的查询中，tbl_name可以用于const表：

SELECT * from tbl_name WHERE primary_key=1；
SELECT * from tbl_name WHERE primary_key_part1=1和 primary_key_part2=2；

例如:

mysql> explain select * from t3 where id=3952602;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

(3). eq_ref

对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。

eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。

在下面的例子中，MySQL可以使用eq_ref联接来处理ref_tables：

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
​ AND ref_table.key_column_part2=1;

例如

mysql> create unique index idx_t3_id on t3(id) ;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0

mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 1000 | |
| 1 | SIMPLE | t3 | eq_ref | PRIMARY,idx_t3_id | idx_t3_id | 4 | dbatest.t4.accountid | 1 | |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+

(4).ref

对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。

ref可以用于使用=或<=>操作符的带索引的列。

在下面的例子中，MySQL可以使用ref联接来处理ref_tables：

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
​ AND ref_table.key_column_part2=1;

例如:

mysql> drop index idx_t3_id on t3;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0

mysql> create index idx_t3_id on t3(id) ;
Query OK, 1000 rows affected (0.04 sec)
Records: 1000 Duplicates: 0 Warnings: 0

mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+-----------+---------+----------------------+------+-------+
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 1000 | |
| 1 | SIMPLE | t3 | ref | PRIMARY,idx_t3_id | idx_t3_id | 4 | dbatest.t4.accountid | 1 | |
+----+-------------+-------+------+-------------------+-----------+---------+----------------------+------+-------+
2 rows in set (0.00 sec)

(5). ref_or_null

该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

在下面的例子中，MySQL可以使用ref_or_null联接来处理ref_tables：

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

(6). index_merge

该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

例如:

mysql> explain select * from t4 where id=3952602 or accountid=31754306 ;
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
| 1 | SIMPLE | t4 | index_merge | idx_t4_id,idx_t4_accountid | idx_t4_id,idx_t4_accountid | 4,4 | NULL | 2 | Using union(idx_t4_id,idx_t4_accountid); Using where |
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
1 row in set (0.00 sec)

(7). unique_subquery

该类型替换了下面形式的IN子查询的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

(8).index_subquery

该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

(9).range

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。

当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range

mysql> explain select * from t3 where id=3952602 or id=3952603 ;
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | t3 | range | PRIMARY,idx_t3_id | idx_t3_id | 4 | NULL | 2 | Using where |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
1 row in set (0.02 sec)

(10).index

该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。

当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

(11). ALL

对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

4.possible_keys(可能会使用的索引，但不一定会使用)

   指出mysql在查询中可能会使用的索引，但不一定会使用，mysql会根据统计信息选出代价最小的索引。代价一般指找到所需的行记录所需要查询的页数量（并不是行数量，因为mysql以页为基本单位，可能会出现行数较少，但是需要I/O的页却较多的情况），页的数量越多代价越大，性能也就越差。

5.key(实际使用的索引)**

   *在查询中实际使用的索引，若没有使用则显示NULL。根据官方文档，key中实际使用的索引可能并不会在possible_keys中出现，当某个索引是需要查找的列的覆盖索引，且mysql找不到更好的索引去查询时，会使用该索引进行索引全表扫描，虽然比较慢，但总比普通的全表扫描，且需要随机磁盘I/O好得多。

6.key_len(索引在表定义中的长度)

   *表示使用的索引在表定义中的长度，比如film表中主键为smallint，则为2，
   language_id为tinyint则为1，若没有使用索引，则为NULL。
   通过key_len可以知道复合索引中的那几列在查询中使用了。

7.ref

   *根据官方文档，ref是指用来与key中所选索引列比较的常量(const)或者连接查询列（显示为该列名字）。
     ref的值为 func 时，表示索引比较的值来自函数或者算数式。
     ref为 NULL 时表示没有使用索引。
   explainselect * from film where film_id in(select film_id from film_actor where actor_id=1);
    由于mysql优化器将外部查询压入in中的子查询，因而ref有两行。

8.rows(需要扫描多少行才能得到目标结果)

   *表示优化器认为需要扫描多少行才能得到目标结果。对于innodb来说这是一个估计值，innodb并不知道一页中有多少行数据，因而优化器会通过随机读取一些页求平均值来估计行数。

9.filtered(过滤)

   *表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例。即实际剩下的行数为rows xfiltered/100。

10Extra(额外的):

using index ：使用覆盖索引的时候就会出现

using where：在查找使用索引的情况下，需要回表去查询所需的数据

using index condition：查找使用了索引，但是需要回表查询数据(索引下推)

using index & using where:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

理解MySQL中的page页

在介绍InnoDB中的页的时候，很有必要先让大家了解一下InnoDB中的存储结构

img

从InnoDB存储引擎的逻辑结构看，所有数据都被逻辑地存放在一个空间内，称为表空间(tablespace)，而表空间由段(sengment)、区(extent)、页(page)组成。 在一些文档中extend又称块(block)。

一、表空间(table space)

表空间(Tablespace)是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

在 InnoDB 中存在两种表空间的类型：共享表空间和独立表空间。如果是共享表空间就意味着多张表共用一个表空间。如果是独立表空间，就意味着每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间可以在不同的数据库之间进行迁移。可通过命令

mysql > show variables like 'innodb_file_per_table';

查看当前系统启用的表空间类型。目前最新版本已经默认启用独立表空间。

InnoDB把数据保存在表空间内，表空间可以看作是InnoDB存储引擎逻辑结构的最高层。本质上是一个由一个或多个磁盘文件组成的虚拟文件系统。InnoDB用表空间并不只是存储表和索引，还保存了回滚段、双写缓冲区等。

二、段(segment)

段(Segment)由一个或多个区组成，区在文件系统是一个连续分配的空间(在 InnoDB 中是连续的 64 个页)，不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。

三、区(extent)

在 InnoDB 存储引擎中，一个区会分配 64 个连续的页。因为 InnoDB 中的页大小默认是 16KB，所以一个区的大小是 64*16KB=1MB。在任何情况下每个区大小都为1MB，为了保证页的连续性，InnoDB存储引擎每次从磁盘一次申请4-5个区。默认情况下，InnoDB存储引擎的页大小为16KB，即一个区中有64个连续的页。

四、页(Page)

页是InnoDB存储引擎磁盘管理的最小单位，每个页默认16KB；InnoDB存储引擎从1.2.x版本碍事，可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。若设置完成，则所有表中页的大小都为innodb_page_size，不可以再次对其进行修改，除非通过mysqldump导入和导出操作来产生新的库。

innoDB存储引擎中，常见的页类型有：

  1. 数据页(B-tree Node)

  2. undo页(undo Log Page)

  3. 系统页 (System Page)

  4. 事物数据页 (Transaction System Page)

  5. 插入缓冲位图页(Insert Buffer Bitmap)

  6. 插入缓冲空闲列表页(Insert Buffer Free List)

  7. 未压缩的二进制大对象页(Uncompressed BLOB Page)

  8. 压缩的二进制大对象页 (compressed BLOB Page)

五、行(row)

InnoDB存储引擎是按行进行存放的，每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200，即7992行记录。

了解了整体架构，下面我们开始详细对Page来做一些介绍。

先贴一张Page完整的结构图

img

整结构图(注意箭头)

上较的概念实在太多了，为了方便理解，可以按下面的分解一下Page的结构

Page结构示意图1

img

每部分的意义img

img

Page结构示意图2

页结构整体上可以分为三大部分，分别为通用部分(文件头、文件尾)、存储记录空间、索引部分。

第一部分通用部分，主要指文件头和文件尾，将页的内容进行封装，通过文件头和文件尾校验的CheckSum方式来确保页的传输是完整的。

在文件头中有两个字段，分别是 FIL_PAGE_PREV 和 FIL_PAGE_NEXT，它们的作用相当于指针，分别指向上一个数据页和下一个数据页。连接起来的页相当于一个双向的链表，如下图所示：

img

需要说明的是采用链表的结构让数据页之间不需要是物理上的连续，而是逻辑上的连续。

第二个部分是记录部分，页的主要作用是存储记录，所以“最小和最大记录”和“用户记录”部分占了页结构的主要空间。另外空闲空间是个灵活的部分，当有新的记录插入时，会从空闲空间中进行分配用于存储新记录，如下图所示：

img

Page结构示意图3

一个页内必须存储2行记录，否则就不是B+tree，而是链表了。

第三部分是索引部分，这部分重点指的是页目录(示意图2中的s0-sn)，它起到了记录的索引作用，因为在页中，记录是以单向链表的形式进行存储的。单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索，因此在页目录中提供了二分查找的方式，用来提高记录的检索效率。这个过程就好比是给记录创建了一个目录：

将所有的记录分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录。

第 1 组，也就是最小记录所在的分组只有 1 个记录；

最后一组，就是最大记录所在的分组，会有 1-8 条记录；

其余的组记录数量在 4-8 条之间。

这样做的好处是，除了第 1 组(最小记录所在组)以外，其余组的记录数会尽量平分。

在每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段。

页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽(slot)，每个槽相当于指针指向了不同组的最后一个记录。如下图所示：

img

页目录存储的是槽，槽相当于分组记录的索引。我们通过槽查找记录，实际上就是在做二分查找。这里我以上面的图示进行举例，5 个槽的编号分别为 0，1，2，3，4，我想查找主键为 9 的用户记录，我们初始化查找的槽的下限编号，设置为 low=0，然后设置查找的槽的上限编号 high=4，然后采用二分查找法进行查找。

首先找到槽的中间位置 p=(low+high)/2=(0+4)/2=2，这时我们取编号为 2 的槽对应的分组记录中最大的记录，取出关键字为 8。因为 9 大于 8，所以应该会在槽编号为 (p,high] 的范围进行查找

接着重新计算中间位置 p’=(p+high)/2=(2+4)/2=3，我们查找编号为 3 的槽对应的分组记录中最大的记录，取出关键字为 12。因为 9 小于 12，所以应该在槽 3 中进行查找。

遍历槽 3 中的所有记录，找到关键字为 9 的记录，取出该条记录的信息即为我们想要查找的内容。

B+ 树是如何进行记录检索的？

如果通过 B+ 树的索引查询行记录，首先是从 B+ 树的根开始，逐层检索，直到找到叶子节点，也就是找到对应的数据页为止，将数据页加载到内存中，页目录中的槽(slot)采用二分查找的方式先找到一个粗略的记录分组，然后再在分组中通过链表遍历的方式查找

5 个槽的编号分别为 0，1，2，3，4，我想查找主键为39 的用户记录，我们初始化查找的槽的下限编号，设置为 low=0，然后设置查找的槽的上限编号 high=4，然后采用二分查找法进行查找。

首先找到槽的中间位置 p=(low+high)/2=(0+4)/2=2，这时我们取编号为 2 的槽对应的分组记录中最大的记录，取出关键字为 42。因为 42大于39，所以应该会在槽编号为 (p,high] 的范围进行查找

接着重新计算中间位置 p’=(p+high)/2=(0+2)/2=1，我们查找编号为 1 的槽对应的分组记录中最大的记录，取出关键字为 40。因为40大于 39，所以应该在槽 3 中进行查找。

接着重新计算中间位置 p’=(p+high)/2=(0+0)/2=0，我们查找编号为 0 的槽对应的分组记录中最大的记录，取出关键字为38。因为38小于 39，所以应该在槽 1 中进行查找。

遍历槽 1中的所有记录，找到关键字为 39 的记录，取出该条记录的信息即为我们想要查找的内容。

存在的问题

槽只存储索引值吗?

确定了槽的编号后需要根据索引去在页记录部分进行遍历查找数据吗?还是直接在槽中进行遍历?

exsit和in的使用

in

select * from A where id in(select id from B)

确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

结论:in()适合B表比A表数据小的情况

exists

select a.* from A a where exists(select 1 from B b where a.id=b.id)

　指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

执行流程完全不一样：

　　　　使用exists关键字进行查询的时候，首先，我们先查询的不是子查询的内容，而是查我们的主查询的表

然后，根据表的每一条记录，执行以下语句，依次去判断where后面的条件是否成立,如果成立则返回true不成立则返回false。如果返回的是true的话，则该行结果保留，如果返回的是false的话，则删除该行，最后将得到的结果返回。

结论:exists()适合B表比A表数据大的情况

当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.

mysql批量插入

1.mysql 的 表锁

lock tables 感觉就像一个 封闭的空间

mysql发现 lock tables 命令的时候,会将带有锁标记的表(table) 带入封闭空间,直到 出现 unlock tables 命令 或 线程结束, 才关闭封闭空间。

进入封闭空间时 , 仅仅只有锁标记的表(table) 可以在里面使用,其他表无法使用。

锁标记 分为 read 和 write 下面是 两种 锁的区别

--------------------------------------------------------------------

//如 将 table1 设为read锁, table2 设为write锁, table3 设为read锁

lock tables [table1] read,[table2] write,[table3] read;
----------------------------------------------------------------------

//执行到这里时,进入封闭空间。

1. table1 仅允许[所有人]读,[空间外]如需写、更新要等待[空间退出],[空间内]如需写、更新会引发mysql报错。
2. table2 仅允许[空间内]读写更新,[空间外]如需写、更新要等待[空间退出]。
3. table3 仅允许[所有人]读,[空间外]如需写、更新要等待[空间退出],[空间内]如需写、更新会引发mysql报错。

----------------------------------------------------------------------
//执行到这里时,退出封闭空间,释放所有表锁

unlock tables
----------------------------------------------------------------------

当前线程关闭时,自动退出封闭空间,释放所有表锁,无论有没有执行 unlock tables

lock 和unlock用法
lock tables  `budget`  READ; //执行后 不能修改,
update budget set description ='搬迁VIP会议室75寸会议平板设备11122' where budget_id=1 and budget_classification='信息工程';


lock tables  `budget`  write;//只允许本窗口修改,不允许其他窗口修改,允许所有人读
UNLOCK tables ;

注意: lock 和unlock是事务不安全的,会隐式提交

BEGIN
delete  from  budget  where process_id =3265
lock tables  `budget`  READ;//这里会隐式提交
ROLLBACK//回滚
select * from budget //1条数据被删除
UNLOCK tables

2.使用事务

效率和锁表差不多

begin ;
commit;

mysql存储过程

-- 1. 准备表
USE ssm;
CREATE TABLE `user`(
	id INT,
	username VARCHAR(32),
	`password` VARCHAR(32),
	sex VARCHAR(6),
	email VARCHAR(50)
);

-- 2. 创建存储过程，实现批量插入记录
DELIMITER $$ -- 声明存储过程的结束符号为$$
CREATE PROCEDURE auto_insert()
BEGIN
    DECLARE i INT DEFAULT 1;
	START TRANSACTION; -- 开启事务
    WHILE(i<=10000000)DO
        INSERT INTO `user` VALUES(i,CONCAT('jack',i),MD5(i),'male',CONCAT('jack',i,'@itcast.cn')); -- 分号不能少
        SET i=i+1;
    END WHILE;
	COMMIT; -- 提交
END$$ -- 声明结束
DELIMITER ; -- 重新声明分号为结束符号

-- 3. 调用存储过程
CALL auto_insert();

MySQL函数：

1.isnull(exper) 判断exper是否为空，是则返回1，否则返回0

2.ifnull(exper1,exper2)判断exper1是否为空，是则用exper2代替

3.nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为 expr1。

  1. LENGTH(trim(o.document_id))>0去掉空串儿
SELECT
	a.process_id,
	a.document_id
FROM
	(
		SELECT
			o.process_id,
			o.document_id,
			TIMESTAMPDIFF(
				SECOND,
				o.updated_time,
				'2021-12-20 21:17:16'
			) AS updated_time
		FROM
			`oa_content` AS o
		LEFT JOIN budget_process b ON o.process_id = b.process_id
		WHERE
			b.flow_status = '申请中'
		AND o.flow_status != '正在起草'
		AND o.flow_status != '流程结束'
		AND o.budget_classification = '固定资产'
		AND  ISNULL(o.document_id)=0
	AND  LENGTH(trim(o.document_id))!=0
	) a
WHERE
	a.updated_time > '600'

where子句、group by 子句 、having子句

需要注意说明：当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：
--执行where子句查找符合条件的数据；
--使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组。
--having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle.
--having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。
--having子句限制的是组，而不是行。where子句中不能使用聚集函数，而having子句中可以

示例

SELECT budget_classification,COUNT(*) as count FROM `oa_content` where flow_status='已作废' group by budget_classification HAVING  count >3

mysql配置优化

innodb_file_per_table参数

将一个表空间分为多个文件存储,提高并发

show variables like 'innodb_data_file_path'\G;
show variables like 'innodb_file_per_table'\G;

如果设置了这个参数(配置为:on)，则用户可以将每个基于InnoDB存储引擎的表产生一个独立表空间。通过这样的方式，用户不用将所有数据都存放于默认的表空间中。该参数默认关闭，不使用独立表空间文件

若这两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能

问题

mysql datetime类型字段存入时间多了1秒导致的问题

一、问题描述

商城系统今天上线了一个功能，用户在商城中点击商品详情页的去购买按钮时，如果商品是有代金券优惠活动的商品，则会先领券后计算商品金额。 这里面涉及到两步操作：调用领券接口，领券成功后，调用计算接口（计算商品价格接口），计算接口内部会调用代金券查券接口。遇到问题是，发券后，立即调用代金券查询代金券，查询不到刚发的代金券，停几秒中再去查，就可以查到，这种情况大概率会复现。

很奇怪的问题，代金券系统已经很久没有修改过了，线上也未爆出过发了代金券而查询不到的情况。

二、问题排查

一开始以为是数据库主从延迟导致的，把代金券查询接口日志打开，发现dao从库中查询时是可以查到刚发送的券，但是进行规则过滤后，代金券被过滤了，添加详细的日志，发现代金券是被有效期校验给过滤了。

代金券的有效期校验是：当前时间 >= 代金券创建时间 and 当前时间 <= 代金券结束时间

代金券有效期一般都是从发券时间开始，到发券时间+7天，因此当前时间必然<代金券结束时间，那么就是当前时间小于创建时间导致过滤，这就更奇怪了，发券在前，查询在后，查询时，系统时间应该是大于代金券创建时间的，为什么会被过滤呢。

查看数据库记录，发现一个奇怪的问题,代金券createtime居然比updatetime多了1s：

createTime updateTime
2021-07-01 14:18:42 2021-07-01 14:18:41

心理隐约感觉找到了方向。继续查看代金系统的日志，发现创建日志打印时间为：2021-07-01 14:18:41.529， 而查询代金券的时间为2021-07-01 14:18:41.611。
这大概就找到问题了，createTime入库时间比系统时间多了1秒，导致2021-07-01 14:18:41.611 < 2021-07-01 14:18:42。那么为什么入库时间会多1秒呢？

查看代金券createTime设值的代码:

setCreateTime(new Date());

取的是当前系统时间，而updateTime其值不是由java代码设置的，而是设置了默认值，也就时入库时，会取mysql current time。
看下createtime和updatetime的sql定义：

`create_time` datetime(0)  DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
  `last_update_time` timestamp(0)  DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '最后更新时间',

发现两个字段的类型居然不一致，createTime是datetime(0)类型,表示时间会精确到秒，而updateTime是timestamp(0)。

百度一番，发现这居然是mysql的一个bug， 当时间字段类型为datetime时，如果时间毫秒数小于500时，向下舍，如果大于等于500时向上加1秒，也就是：

2021-07-01 14:18:41.400 会存为2021-07-01 14:18:41
2021-07-01 14:18:41.591 会存为2021-07-01 14:18:42

线上之所以一直未爆出这个问题，主要是没有这种领券后，在极短时间(500ms内)就去查券的场景，而商城这个场景是自动发券自动查，间隔时间基本上100ms以内，才爆出这个问题。

三、问题解决

解决办法有3个：

  1. 查询判断时，取当前时间时，加1s
  2. 存datetime类型的字段时，对Date类型的值进行处理，将毫秒置为000，比如2021-07-01 14:18:41.400 设置为2021-07-01 14:18:41.000
  3. 将mysql时间类型改为datetime(3),或者timestamp类型。

推荐第二种，因为第1种并没有解决根本问题，存入的数据本身就是错的，保不齐其他地方也会有日期判断的地方，如果数据存入的就是错的，其他地方判断也会是错的。而第3种需要修改数据库字段类型，公司内对数据量大的表进行修改比较麻烦，而且时间精确到毫秒意义并不大，还会占用更多的空间，不如修改两行代码来的快捷。

标签：07,ref,索引,2021,mysql,NULL,优化,id
From： https://www.cnblogs.com/cicc/p/17208474.html

相关文章

  • 【项目实战】基于Python+Django+MySQL的音乐网站系统（附完整源码）
    1、项目说明基于python+Django+Mysql的音乐网站系统项目实战项目需要安装pycharm专业版以及MySQL环境（环境搭建和破解可以看我的B站里《python环境配置》的视频里有讲解）......
  • 【项目实战】基于Python+Flask+MySQL的在线笔记管理系统
    1、项目说明基于python+Flask+mysql的在线笔记管理系统项目实战项目需要安装pycharm专业版，mysql数据库以及项目所需的所有模块创建数据库名称db_online_notes，然后执行sq......
  • MySQL中的函数
    1.函数函数与存储过程一样，也是一组预先编译好的SQL语句的集合，理解为批处理语句。将实现某个功能的一段代码封装起来，只暴露名字，隐藏具体实现过程。函数与存储过程的......
  • MySQL8中如何估算redo日志的大小
    先说公式：mysql>pagergrepsequence;showengineinnodbstatus\Gselectsleep(60);showengineinnodbstatus\Gnopager;mysql>select(<second_value>-<firs......
  • MySQL报Got timeout writing communication packets错误
    应用日志显示连接数据库超时，查看MySQL的错误日志，发现大量的如下错误:2023-02-14T14:19:48.330743+08:00696688[Note][MY-010914][Server]Abortedconnection696688......
  • mysql-connector-java版本大于5，项目启动连接数据库报错
    项目启动连接数据库报错如下2023-02-0822:44:23.491ERROR23596---[reate-951000054]com.alibaba.druid.pool.DruidDataSource:createconnectionSQLException,......
  • mysql数据库binlog日志太大解决办法
    解决办法一：1.在mysql中修改查看binlog过期时间，这个值默认是0天，也就是说不自动清理，可以根据生产情况修改，目前环境是设置的60天。showvariableslike‘expire_logs_days......
  • mac安装mysqlclient
    参考：https://www.php1.cn/detail/Mac_AnZhuang_mys_6826da49.htmlbrewinstallmysql-connector-c    pip3installmysqlclient ......
  • K8S 性能优化 - OS sysctl 调优
    前言K8S性能优化系列文章，本文为第一篇：OSsysctl性能优化参数最佳实践。参数一览sysctl调优参数一览#KubernetesSettingsvm.max_map_count=262144kernel.softl......
  • Python-pymysql如何向SQL语句中传参
    方法一：不传递参数##方式一、不传递参数id="01"name="语文"sql="select*fromcoursewherecourse_id='%s'andcourse_name='%s';"%(id,name)##......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99