推荐最新

请问一般数据库的表的字段是预先创建的，还是在运行项目的时候创建的？

比如我们在开发一个后端项目，里面使用mysql数据库，请问常规做项目的情况下对数据库的表的初始化操作，里面的字段是在项目第一次启动之前就预设好了，还是指的是，在第一次启动项目的时候，看到表不存在，然后再进行创建表呢？

浏览量373

网站热门搜索实现思路，不知道这样对不对，大佬指点一下？

数据库有一个搜记录表 "t_searche", 字段："id", "user_id", "title", "label", "created_time", "ip", "deleting" 说明： "id"：mysql 自增ID "user_id"：如果用户登录，添加用户ID，如果没登录，就是0 "title"：搜索的关键词 "label"：记录 "title" 的第一个字母，如果是中文，就是第一个汉字拼音第一个字母，归类用 "created_time"：搜索的时间 "ip"：搜索的用户IP "deleting"：是否删除每次搜索都是都记录一次，不管是不是同一个人还是同一个IP，这样对不对这样就可以统计 "title" 的条数来排序热门还是说单独再建一个表来记录 "title" 的数量，如果数据不是很大的时候没必要吧，后期数量大了，再增加一个统计表感觉也简单。

浏览量496

我头像最美

Mysql 是用分区表，还是直接分表？

我们现在有个 Oracle 的数据库，有一张实验数据相关的表字段不多大约 3000W 的数据吧打算迁移到 Mysql8 中，我在想这张表是直接分区呢还是分表好一些？

浏览量360

D_Y_大师

大家做多级代理项目的时候怎么设计sql表？

要不要在每个表里面都加一个parentId字段，这样可以根据这个字段查到所有下级对应表的情况。但是感觉每个表都多一个字段比较麻烦，还有别的方案吗？

浏览量351

无事小神仙

【图形工具】MySQL Workbench 图形化界面工具

Workbench 介绍MySQL官方提供了一款免费的图形工具——MySQL Workbench，它是一款功能强大且易于使用的数据库设计、管理和开发工具，总之，MySQL Workbench是一款非常好用的MySQL图形工具，可以满足大多数MySQL用户的需求。Workbench 下载MySQL :: MySQL Workbenchwww.mysql.com/products/wo…点击 Download 下载点击不用登录开始下载Workbench 安装点击安装包，双击运行程序，弹出软件安装向导，点击“Next”开始安装；自定义软件安装路径，点击“Next”进行安装；选择安装类型：默认选择第一项“Complete”完整安装；进行安装，等待安装进度完成；Workbench 使用MySQL Workbench 的初始界面如下图所示。首先，打开MySQL Workbench，双击打开即可。打开后的界面如下所示，然后选择数据库实例，双击进行登录。图中数据库的实例是Local instance MySQL 5.7输入用户名和密码进行登录，如下图所示：其一：显示的是数据库服务器中已经创建的数据库列表其二：关于数据库的操作列表其三：sql的编辑器和执行环境Workbench 创建数据库点击创建数据库按钮，输入数据库名称，选择编码方式，点击ApplyWorkbench会自动生成SQL语句，再次点击Apply就可以成功创建数据库创建成功，点击 finishWorkbench 建表右键单击 Tables --> Create table之后填写表名，列名，数据类型，约束，点击 ApplyWorkbench 会自动生成建表语句，之后点击 ApplyWorkbench 汉化首先打开github.com/JiuBanA1/my…点击 download 下载，下载后在目录找到 main_menu.xml 复制复制 main_menu.xml 替换 mysql workbench 安装目录的 data/main_menu.xml 文件。重新启动后，就是汉化版总结设计后的效果

浏览量2045

无事小神仙

PHP处理库存超卖的几种处理方法

第一种方法：使用mysql数据库的锁机制。在事务中使用 for update 语句，在事务处理完成之后释放这一条数据。代码使用tp5的框架：public function mysqlLock(){ $goods_id = 26545; $sku_id = 26545; $price = 300; $user = ''; StoreOrderModel::startTrans(); $nums = StoreOrderModel::where(['id'=>1])->field('number')->lock(true)->find(); $nums = $nums['number']; if($nums > 0){ $item['goods_id'] = $goods_id; $item['sku_id'] = $sku_id; $item['number'] = $nums; $item['price'] = $price; $item['user'] = $user; $id = StoreModel::insertGetId($item); if($id){ StoreOrderModel::where(['id'=>1])->setDec('number'); StoreOrderModel::commit(); }else{ StoreOrderModel::rollback(); } }else{ echo "没有库存了"; } } 第二种方法：redis 事务。public function start_reids_tran(){ $goods_id = 26545; $sku_id = 26545; //$number = 1; $price = 300; $user = ''; $redis = ResRedisModel::getinstance(); $redis->watch('store'); $nums = intval($redis->get('store')); if($nums > 0){ $item['goods_id'] = $goods_id; $item['sku_id'] = $sku_id; $item['number'] = $nums; $item['price'] = $price; $item['user'] = $user; $redis->lPush('success', json_encode($item)); $redis->multi(); $redis->decr('store'); $replies = $redis->exec(); // 执行以上 redis 事务 if(!$replies){ echo "订单 {$nums} 回滚".PHP_EOL; } $redis->unwatch(); echo "抢购成功！".PHP_EOL; }else{ echo "没有库存了"; } } 第三种方法：redis 队列，预先把库存信息存入队列当中，抢购时判断队列的数量，然后出队。队列为空时库存为0。public function eq_start(){ $redis = ResRedisModel::getinstance(); $nums = $redis->lSize('store'); $goods_id = 26545; $sku_id = 26545; $number = 1; $price = 300; $user = ''; if($nums > 0){ $user = $redis->rPop('store'); if($user){ $item['goods_id'] = $goods_id; $item['sku_id'] = $sku_id; $item['number'] = $number; $item['price'] = $price; $item['user'] = $user; StoreModel::insertGetId($item); echo '抢购成功！'; }else{ echo '抢购失败！'; } }else{ echo '抢购失败！'; } } 第四种：文件排他锁方式public function file_star(){ $fp = fopen('D:/phpStudy/PHPTutorial/www/public/lock.txt', "r"); if(flock($fp, LOCK_EX)) { //排他型锁定阻塞模式， flock($fp,LOCK_EX | LOCK_NB) 非阻塞模式 $nums = StoreOrderModel::where(['id'=>1])->field('number')->find(); $nums = $nums['number']; if($nums > 0){ $goods_id = 26545; $sku_id = 26545; $number = 1; $price = 300; $user = '213'; $item['goods_id'] = $goods_id; $item['sku_id'] = $sku_id; $item['number'] = $number; $item['price'] = $price; $item['user'] = $user; StoreModel::insertGetId($item); StoreOrderModel::where(['id'=>1])->setDec('number'); flock($fp, LOCK_UN); //释放锁定 echo '抢购成功！'; }else{ echo '没有库存了！'; } }else{ echo '抢购失败！'; } fclose($fp); }

浏览量2086

刘传疯子

问一个多表查询的简单问题？

有两张表，分别储存产品信息与检测信息检测信息表procheck中pro_id关联到产品信息表pro中的id，每个产品对应有多个检测信息记录我现在需要把某个公司（产品表中company_id）生产的所有产品的最新一次检测报告调出来？ SELECT `pro`.`id`,`pro`.`pm`,`pro`.`company_id`,procheck.id AS procheck__id,procheck.pro_id AS procheck__pro_id,procheck.checkdate AS procheck__checkdate FROM `da_pro` `pro` LEFT JOIN `da_procheck` `procheck` ON `pro`.`id`=`procheck`.`pro_id` WHERE `pro`.`company_id` = 487 但是得出的结果却是： "image.png" (https://wmlx-new-image.oss-cn-shanghai.aliyuncs.com/images/20250121/1fc0154e7d3cecbee677766287c8508b.png) 全部给我找了出来，而我真正需要的是箭头上的三条即可。求SQL语句.....

浏览量505

无事小神仙

【MySQL】MySQL Explain性能调优详解

在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。 -- 实际SQL，查找用户名为Jefabc的员工 select * from emp where name = 'Jefabc'; -- 查看SQL是否使用索引，前面加上explain即可 explain select * from emp where name = 'Jefabc'; expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra概要描述id选择标识符select_type表示查询的类型。table输出结果集的表partitions匹配的分区:------------:------------type表示表的连接类型possible_keys表示查询时，可能使用的索引key表示实际使用的索引:------:--------key_len索引字段的长度ref列与索引的比较rows扫描出的行数(估算的行数):-------:------------filtered按表条件过滤的行百分比Extra执行情况的描述和说明下面对这些字段出现的可能进行解释：一、 idSELECT识别符。这是SELECT的查询序列号我的理解是SQL执行的顺序的标识，SQL从大到小的执行id相同时，执行顺序由上至下如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行 -- 查看在研发部并且名字以Jef开头的员工，经典查询 explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研发部'; 二、select_type示查询中每个select子句的类型SIMPLE(简单SELECT，不使用UNION或子查询等)PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)UNION(UNION中的第二个或后面的SELECT语句)DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)DERIVED(派生表的SELECT, FROM子句的子查询)UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)三、table显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称四、type对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行index: Full Index Scan，index与ALL区别为index类型只遍历索引树range:只检索给定范围的行，使用一个索引来选择行ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用systemNULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。五、possible_keys指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询六、Keykey列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。七、key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）不损失精确性的情况下，长度越短越好八、ref列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值九、rows估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数十、Extra该列包含MySQL解决查询的详细信息,有以下几种情况：Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order byUsing filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”测试Extra的filesort explain select * from emp order by name; Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行No tables used：Query语句中使用from dual 或不含任何from子句-- explain select now() from dual; 总结EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况EXPLAIN不考虑各种CacheEXPLAIN不能显示MySQL在执行查询时所作的优化工作部分统计信息是估算的，并非精确值EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

浏览量2070

无事小神仙

【MySql】MySQL索引15连问（相信大家看完肯定会有帮助）

索引是什么？索引是一种能提高数据库查询效率的数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。索引一般存储在磁盘的文件中，它是占用物理空间的。正所谓水能载舟，也能覆舟。适当的索引能提高查询效率，过多的索引会影响数据库表的插入和更新功能。2. MySQL索引有哪些类型数据结构维度B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询。哈希索引: 适合等值查询，检索效率高，一次到位。全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本类型char,text,varchar类型上创建。R-Tree索引: 用来对GIS数据类型创建SPATIAL索引物理存储维度聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。（Innodb存储引擎）非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。（Innodb存储引擎）逻辑维度主键索引：一种特殊的唯一索引，不允许有空值。普通索引：MySQL中基本索引类型，允许空值和重复值。联合索引：多个字段创建的索引，使用时遵循最左前缀原则。唯一索引：索引列中的值必须是唯一的，但是允许为空值。空间索引：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则。3. 索引什么时候会失效？查询条件包含or，可能导致索引失效如果字段类型是字符串，where时一定用引号括起来，否则索引失效like通配符可能导致索引失效。联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。在索引列上使用 mysql 的内置函数，索引失效。对索引列运算（如，+、-、*、/），索引失效。索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。索引字段上使用is null， is not null，可能导致索引失效。左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。mysql 估计使用全表扫描要比使用索引快,则不使用索引。4. 哪些场景不适合建立索引？数据量少的表，不适合加索引更新比较频繁的也不适合加索引区分度低的字段不适合加索引（如性别）where、group by、order by等后面没有使用到的字段，不需要建立索引已经有冗余的索引的情况（比如已经有a,b的联合索引，不需要再单独建立a索引）5. 为什么要用 B+树，为什么不用二叉树？可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数，为什么不是二叉树，为什么不是平衡二叉树，为什么不是 B 树，而偏偏是 B+树呢？为什么不是一般二叉树？如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。为什么不是平衡二叉树呢？我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是 B 树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。那为什么不是 B 树而是 B+树呢？B+树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。innodb 中页的默认大小是 16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数有会再次减少，数据查询的效率也会更快。B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么 B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。6. 一次B+树索引树查找过程假设有以下表结构，并且初始化了这几条数据CREATE TABLE `employee` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, `date` datetime DEFAULT NULL, `sex` int(1) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_age` (`age`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into employee values(100,'小伦',43,'2021-01-20','0'); insert into employee values(200,'俊杰',48,'2021-01-21','0'); insert into employee values(300,'紫琪',36,'2020-01-21','1'); insert into employee values(400,'立红',32,'2020-01-21','0'); insert into employee values(500,'易迅',37,'2020-01-21','1'); insert into employee values(600,'小军',49,'2021-01-21','0'); insert into employee values(700,'小燕',28,'2021-01-21','1'); 执行这条查询SQL，需要执行几次的树搜索操作？可以画下对应的索引树结构图~select * from Temployee where age=32; 其实这个，这个大家可以先画出idx_age普通索引的索引结构图，大概如下：再画出id主键索引，我们先画出聚族索引结构图，如下：这条 SQL 查询语句执行大概流程是这样的：搜索idx_age 索引树，将磁盘块1加载到内存，由于32<43,搜索左路分支，到磁盘寻址磁盘块2。将磁盘块2加载到内存中，由于32<36,搜索左路分支，到磁盘寻址磁盘块4。将磁盘块4加载到内存中，在内存继续遍历，找到age=32的记录，取得id = 400.拿到id=400后，回到id主键索引树。搜索id主键索引树，将磁盘块1加载到内存，因为300<400<500,所以在选择中间分支，到磁盘寻址磁盘块3。虽然在磁盘块3，找到了id=400，但是它不是叶子节点，所以会继续往下找。到磁盘寻址磁盘块8。将磁盘块8加载内存，在内存遍历，找到id=400的记录，拿到R4这一行的数据，好的，大功告成。7. 什么是回表？如何减少回表？当查询的数据在索引树中，找不到的时候，需要回到主键索引树中去获取，这个过程叫做回表。比如在第6小节中，使用的查询SQLselect * from employee where age=32; 需要查询所有列的数据，idx_age普通索引不能满足，需要拿到主键id的值后，再回到id主键索引查找获取，这个过程就是回表。8. 什么是覆盖索引？如果我们查询SQL的select * 修改为 select id, age的话，其实是不需要回表的。因为id和age的值，都在idx_age索引树的叶子节点上，这就涉及到覆盖索引的只是点了。覆盖索引是select的数据列只用从索引中就能够取得，不必回表，换句话说，查询列要被所建的索引覆盖。9. 聊聊索引的最左前缀原则索引的最左前缀原则，可以是联合索引的最左N个字段。比如你建立一个组合索引（a,b,c），其实可以相当于建了（a），（a,b）,(a,b,c)三个索引，大大提高了索引复用能力。当然，最左前缀也可以是字符串索引的最左M个字符。。比如，你的普通索引树是酱紫：这个SQL: select * from employee where name like '小%' order by age desc; 也是命中索引的。10. 索引下推了解过吗？什么事索引下推给你这个SQL：select * from employee where name like '小%' and age=28 and sex='0'; 其中，name和age为联合索引（idx_name_age）。如果是Mysql5.6之前，在idx_name_age索引树，找出所有名字第一个字是“小”的人，拿到它们的主键id，然后回表找出数据行，再去对比年龄和性别等其他字段。如图：有些朋友可能觉得奇怪，idx_name_age（name,age)不是联合索引嘛？为什么选出包含“小”字后，不再顺便看下年龄age再回表呢，不是更高效嘛？所以呀，MySQL 5.6就引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。因此，MySQL5.6版本之后，选出包含“小”字后，顺表过滤age=2811. 大表如何添加索引如果一张表数据量级是千万级别以上的，那么，如何给这张表添加索引？我们需要知道一点，给表添加索引的时候，是会对表加锁的。如果不谨慎操作，有可能出现生产事故的。可以参考以下方法：先创建一张跟原表A数据结构相同的新表B。在新表B添加需要加上的新索引。把原表A数据导到新表Brename新表B为原表的表名A，原表A换别的表名；12. 如何知道语句是否走索引查询？explain查看SQL的执行计划，这样就知道是否命中索引了。当explain与SQL一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。一般来说，我们需要重点关注type、rows、filtered、extra、key。1.2.1 typetype表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALLsystem：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询ref : 常用于非主键和唯一索引扫描。ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。unique_subquery：类似于eq_ref，条件用了in子查询index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。range：常用于范围查询，比如：between … and 或 In 等操作index：全索引扫描ALL：全表扫描1.2.2 rows该列表示MySQL估算要找到我们所需的记录，需要读取的行数。对于InnoDB表，此数字是估计值，并非一定是个准确值。1.2.3 filtered该列是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。1.2.4 extra该字段包含有关MySQL如何解析查询的其他信息，它一般会出现这几个值：Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句Using index ：表示是否用了覆盖索引。Using temporary: 表示是否使用了临时表,性能特别差，需要重点优化。一般多见于group by语句，或者union语句。Using where : 表示使用了where条件过滤.Using index condition：MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。1.2.5 key该列表示实际用到的索引。一般配合possible_keys列一起看。13.Hash 索引和 B+树区别是什么？你在设计索引是怎么抉择的？B+树可以进行范围查询，Hash 索引不能。B+树支持联合索引的最左侧原则，Hash 索引不支持。B+树支持 order by 排序，Hash 索引不支持。Hash 索引在等值查询上比 B+树效率更高。（但是索引列的重复值很多的话，Hash冲突，效率降低）。B+树使用 like 进行模糊查询的时候，like 后面（比如%开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。14. 索引有哪些优缺点？优点：索引可以加快数据查询速度，减少查询时间唯一索引可以保证数据库表中每一行的数据的唯一性缺点：创建索引和维护索引要耗费时间索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间以表中的数据进行增、删、改的时候，索引也要动态的维护。15. 聚簇索引与非聚簇索引的区别聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。它表示索引结构和数据一起存放的索引。非聚集索引是索引结构和数据分开存放的索引。接下来，我们分不同存存储引擎去聊哈~在MySQL的InnoDB存储引擎中，聚簇索引与非聚簇索引最大的区别，在于叶节点是否存放一整行记录。聚簇索引叶子节点存储了一整行记录，而非聚簇索引叶子节点存储的是主键信息，因此，一般非聚簇索引还需要回表查询。一个表中只能拥有一个聚集索引（因为一般聚簇索引就是主键索引），而非聚集索引一个表则可以存在多个。一般来说，相对于非聚簇索引，聚簇索引查询效率更高，因为不用回表。而在MyISM存储引擎中，它的主键索引，普通索引都是非聚簇索引，因为数据和索引是分开的，叶子节点都使用一个地址指向真正的表数据。

浏览量2073

无事小神仙

【性能优化】MySql查询性能优化必知必会

本文内容主要包括以下几个方面：分析查询SQL，MySQL查询优化器、数据库存储结构、索引，索引维护，索引设计，SQL优化，表结构设计，分库分表，查询功能架构设计。分析查询SQLMySQL提供了一个性能分析工具 EXPLAIN ，它可以帮助我们了解SQL语句的执行计划，分析查询效率低下的原因（eg:是否使用索引，是否做全表扫描…），进而有针对性地对SQL进行优化。EXPLAIN 使用起来很简单，在你的SQL查询语句前加上它就可以了。示例如下图所示：EXPLAIN 命令执行之后，显示的结果一共有12列，这里我简单说一下各个参数的意思：id：是一个查询序列号。select_type：查询类型。table：表示与查询结果相关的表的名称。partition：表示查询访问的分区。key：表示优化器最终决定使用的索引是什么。key_len：表示优化器选择的索引字段按字节计算的长度。如果没有使用索引，这个值就是空。ref：列与索引的比较。rows：表示为了得到查询结果，必须扫描多少行记录。filtered：表示查询筛选出的记录占全部表记录数的百分比。possible_key：表示查询时可能使用的索引。如果这里的值是空，就说明没有合适的索引可用。Extra：表示MySQL执行查询中的附加信息。type：表的连接类型。我们还可以通过命令 EXPLAIN的FORMAT=json 和 FORMAT=tree 来查看SQL 执行成本。EXPLAIN FORMAT=json SELECT * FROM USER_TASK_STATUS_LOG WHERE userId = '1' and createTime = '2023-01-13 20:46:27'; { "query_block": { "select_id": 1, "cost_info": { "query_cost": "409960.06" }, "table": { "table_name": "USER_TASK_STATUS_LOG", "access_type": "ALL", "rows_examined_per_scan": 3990545, "rows_produced_per_join": 39905, "filtered": "1.00", "cost_info": { "read_cost": "405969.52", "eval_cost": "3990.55", "prefix_cost": "409960.06", "data_read_per_join": "11M" }, "used_columns": [ "id", "userId", "taskStatus", "createTime", "updateTime" ], "attached_condition": "((`ds`.`user_task_status_log`.`createTime` = TIMESTAMP'2023-01-13 20:46:27') and (`ds`.`user_task_status_log`.`userId` = '1'))" } } 从第 14 行开始，其中：read_cost 表示就是从 InnoDB 存储引擎读取的开销；eval_cost 表示 Server 层的 CPU 成本；prefix_cost 表示这条 SQL 的总成本；data_read_per_join 表示总的读取记录的字节数。如果你不了解这些参数的意义，或者说不清楚MySQL为什么要计算这些执行开销，那么接着往下看MySQL查询优化器。查询优化器先上一张MySQL执行过程图，我们来看看查询优化器在MySQL执行过程中的位置。从上图可以看出，MySQL 数据库由 Server层和 Engine层两部分Server 层负责“逻辑处理”，包括连接器、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。而存储引擎层负责数据的存储和提取。可插拔式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。查询优化器的两种优化方式查询优化器的目标是生成最佳的执行计划，而生成最佳执行计划的策略通常有以下两种方式。第一种是基于规则的优化器（RBO，Rule-Based Optimizer），规则就是人们以往的经验，或者是采用已经被证明是有效的方式。通过在优化器里面嵌入规则，对查询SQL进行重写。例如，对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。第二种是基于代价的优化器（CBO，Cost-Based Optimizer，基于成本的优化器），SQL 优化器会分析所有可能的执行计划，选择成本最低的执行，在 MySQL中，一条 SQL 的计算成本计算如下所示：Cost = Server Cost + Engine Cost = CPU Cost + IO CostCPU Cost 表示计算的开销，比如索引键值的比较、记录值的比较、结果集的排序等等。IO Cost 表示引擎层 IO 的开销，MySQL 8.0 可以通过区分一张表的数据是否在内存中，分别计算读取内存 IO 开销以及读取磁盘 IO 的开销。Server Cost 和 Engine Cost 则记录了对于各种成本的计算。Server Cost 记录了 Server 层优化器各种操作的成本。Engine Cost 记录了存储引擎层各种操作的成本。如果想深入了解这里有篇论文可以帮到你。《Access Path Selection-in a Relational Database Management System》数据库存储结构数据库中的存储结构是怎样的记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取（也就是一次I/O操作）只能处理一行数据，效率会非常低。因此InnoDB将数据划分为若干个页面，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页（Page）。一个页中可以存储多个行记录（Row），同时在数据库中，还存在着区（Extent）、段（Segment）和表空间（Tablespace）。行、页、区、段、表空间的关系如下图所示：区（Extent）是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64个连续的页。因为InnoDB中的页大小默认是16KB，所以一个区的大小是64*16KB=1MB。段（Segment）由一个或多个区组成，区在文件系统是一个连续分配的空间（在InnoDB中是连续的64个页），不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。数据页内的结构是怎样的页（Page）如果按类型划分的话，常见的有数据页（保存B+树节点）、系统页、Undo页和事务数据页等。数据页是我们最常使用的页。数据页包括七个部分，分别是文件头（File Header）、页头（Page Header）、最大最小记录（Infimum+supremum）、用户记录（User Records）、空闲空间（Free Space）、页目录（Page Directory）和文件尾（File Tailer）。页结构的示意图如下所示：实际上，我们可以把这7个数据页分成3个部分。首先是文件通用部分，也就是文件头和文件尾。它们类似集装箱，将页的内容进行封装，通过文件头和文件尾校验的方式来确保页的传输是完整的。在文件头中有两个字段，分别是FIL_PAGE_PREV和FIL_PAGE_NEXT，它们的作用相当于指针，分别指向上一个数据页和下一个数据页。连接起来的页相当于一个双向的链表，如下图所示：需要说明的是采用链表的结构让数据页之间不需要是物理上的连续，而是逻辑上的连续。第二个部分是记录部分，页的主要作用是存储记录，所以“最小和最大记录”和“用户记录”部分占了页结构的主要空间。另外空闲空间是个灵活的部分，当有新的记录插入时，会从空闲空间中进行分配用于存储新记录，如下图所示：第三部分是索引部分，这部分重点指的是页目录，它起到了记录的索引作用。因为在页中，记录是以单向链表的形式进行存储的。单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索，因此在页目录中提供了二分查找的方式，用来提高记录的检索效率。索引索引是什么索引的出现是为了提高数据查询的效率，就像书的目录一样。没有索引，我们就只能一页一页去找。而加上索引之后，我们可以根据目录来快速查找我们所需要的内容。对于数据库的表而言，索引就是它的“目录”，它是帮助MySQL系统快速检索数据的一种数据结构。索引在插入时会对数据进行排序，我们可以在索引中按照查询条件，检索索引字段的值，然后快速定位数据记录的位置，这样就不需要遍历整个数据表了。索引好坏的评价标准可以用于提高读写效率的数据结构很多，那么如何评价一个索引数据结构的好坏呢？数据库服务器有两种存储介质，分别为硬盘和内存。硬盘相当于永久存储介质。内存属于临时存储，容量有限，当发生意外时（比如断电或者发生故障重启）会造成数据丢失。所以尽管内存的读取速度很快，但我们还是需要将索引存放到硬盘上。这样的话，当我们在硬盘上进行查询时，也就产生了硬盘的I/O操作，而硬盘I/O耗时是比较高的。所以好的索引数据结构应该尽量减少硬盘的I/O操作，降低耗时。索引的数据结构MySQL默认存储引擎是InnoDB存储引擎,InnoDB存储引擎使用的是B+树索引。B树B树的英文是Balance Tree，也就是平衡的多路搜索树。在文件系统和数据库系统中的索引结构经常采用B树来实现。B树的结构如下图所示（一棵3阶的B树）：B+树相较于B树而言，B+树在两个方面做出了改进和提升，一方面是查询的稳定性，另一方面是查询的效率更高。下图是一棵3阶的B+树B+树和B树有个根本的差异在于，B+树的中间节点并不直接存储数据。这样的好处都有什么呢？首先，B+树查询效率更稳定。因为B+树每次只有访问到叶子节点才能找到对应的数据，而在B树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字。其次，B+树的查询效率更高，这是因为通常B+树比B树更矮胖（阶数更大，深度更低），查询所需要的磁盘I/O也会更少。同样的磁盘页大小，B+树可以存储更多的节点关键字。不仅是对单个关键字的查询上，在查询范围上，B+树的效率也比B树高。这是因为所有关键字都出现在B+树的叶子节点中，并通过有序链表进行了链接。而在B树中则需要通过中序遍历才能完成查询范围的查找，效率要低很多。B+树是如何进行记录检索的？如果通过B+树的索引查询行记录，首先是从B+树的根开始，逐层检索，直到找到叶子节点，也就是找到对应的数据页为止，将数据页加载到内存中，页目录中的槽（slot）采用二分查找的方式先找到一个粗略的记录分组，然后再在分组中通过链表遍历的方式查找记录。索引维护B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。这里存在两种不同数据类型的插入情况。数据顺序（或逆序）插入： B+ 树索引的维护代价非常小，叶子节点都是从左往右进行插入，比较典型的是自增 ID 的插入、时间的插入（若在自增 ID 上创建索引，时间列上创建索引，则 B+ 树插入通常是比较快的）。数据无序插入： B+ 树为了维护排序，需要对页进行分裂、旋转等开销较大的操作，另外，即便对于固态硬盘，随机写的性能也不如顺序写，所以磁盘性能也会收到较大影响。比较典型的是用户昵称，每个用户注册时，昵称是随意取的，若在昵称上创建索引，插入是无序的，索引维护需要的开销会比较大。所以对于 B+ 树索引，在 MySQL 数据库设计中，建议主键的索引设计为顺序，比如使用自增，或使用函数 UUID_TO_BIN 排序的 UUID，而不用无序值做主键。索引组织表MySQL InnoDB 存储引擎是索引组织表的存储方式。在索引组织表中，数据即索引，索引即数据，数据根据主键排序存放在索引中。索引组织表示例：表 User 的主键是 id，所以表中的数据根据 id 排序存储，叶子节点存放了表中完整的记录，可以看到表中的数据存放在索引中，即表就是索引，索引就是表。二级索引InnoDB 存储引擎的数据是根据主键索引排序存储的，除了主键索引（聚集索引）外，其他的索引都称之为二级索引，或非聚集索引。二级索引也是一颗 B+ 树索引，但它和主键索引不同的是叶子节点存放的是索引键值、主键值。通过二级索引 idx_name 只能定位主键值，需要额外再通过主键索引进行查询，才能得到最终的结果。这种“二级索引通过主键索引进行再一次查询”的操作叫作“回表”，你可以通过下图理解二级索引的查询：索引组织表这样的二级索引设计有一个非常大的好处：若记录发生了修改，则其他索引无须进行维护，除非记录的主键发生了修改。与堆表的索引实现对比着看，你会发现索引组织表在存在大量变更的场景下，性能优势会非常明显，因为大部分情况下都不需要维护其他二级索引。索引设计覆盖索引覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。函数索引函数索引即索引键是一个函数表达式。举个例子，假设User表中创建了register_date索引。SELECT * FROM User WHERE DATE_FORMAT(register_date,'%Y-%m') = '2021-01' 这条 SQL 因为索引 register_date 只对 register_date 的数据排序，没有对DATE_FORMAT(register_date) 排序，因此上述 SQL 无法使用二级索引register_date。此时我们可以创建一个DATE_FORMAT(register_date) 索引，来提升SQL的查询性能。ALTER TABLE User ADD INDEX func_register_date((DATE_FORMAT(register_date,'%Y-%m'))); 前缀索引MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。从而创建占用空间更小、查询效率相同的字段。示例代码：alter table user add index user_uuid_index(user_uuid(10)); 组合索引最左前缀原则组合索引的多个字段是有序的，遵循左对齐的原则。假设我们创建一个组合索引，排序的方式是sex、age和height。此时，筛选的条件也要遵循从左向右的原则。如果中断，那么，断点后面的条件就没有办法利用索引了。假设查询条件为 “WHERE sex = ‘男’ AND age = 25 AND height = 180”，包含了从左到右的所有字段，所以可以最大限度使用全部组合索引。假如把条件换成“WHERE age = 25 AND height = 180”，最左边的字段 sex 没有包含到条件创建索引的规律我之前讲了索引的使用和它的底层原理，今天我来讲一讲索引的使用原则。既然我们的目标是提升SQL的查询效率，那么该如何通过索引让效率最大化呢？1.字段的数值有唯一性的限制，比如用户名索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。2.频繁作为WHERE查询条件的字段，尤其在数据表大的情况下在数据量大的情况下，某个字段在SQL查询的WHERE条件中经常被使用到，那么就需要给这个字段创建索引了。创建普通索引就可以大幅提升数据查询的效率。3.需要经常GROUP BY和ORDER BY的列索引就是让数据按照某种顺序进行存储或检索，因此当我们使用GROUP BY对数据进行分组查询，或者使用ORDER BY对数据进行排序的时候，就需要对分组或者排序的字段进行索引。4.UPDATE、DELETE的WHERE条件列，一般也需要创建索引先根据WHERE条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。不过在实际工作中，我们也需要注意平衡，如果索引太多了，在更新数据的时候，如果涉及到索引更新，就会造成负担。5.DISTINCT字段需要创建索引有时候我们需要对某个字段进行去重，使用DISTINCT，那么对这个字段创建索引，也会提升查询效率。6.做多表JOIN连接操作时，创建索引需要注意以下的原则首先，连接表的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。其次，对WHERE条件创建索引，因为WHERE才是对数据条件的过滤。如果在数据量非常大的情况下，没有WHERE条件过滤是非常可怕的。最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。从表结构入手设计提高性能数据类型优化尽量使用占用存储空间更少的数据类型，例如字段既可以用文本类型，也可以用整数类型时，尽量使用整数类型。需要注意的是：修改数据类型，节省存储空间的同时，你要考虑到数据不能超过取值范围；合理增加冗余字段以提高效率在数据量大，而且需要频繁进行连接的时候，为了提升效率，可以考虑增加冗余字段来减少连接。需要注意的是：增加冗余字段的时候，不要忘了确保数据一致性；拆分表把1个包含很多字段的表拆分成2个或者多个相对较小的表。这样做的原因是，把这个大表拆分开，把使用频率高的字段放在一起形成一个表，把剩下的使用频率低的字段放在一起形成一个表，这样查询操作每次读取的记录比较小，查询效率自然也就提高了。需要注意的是：把大表拆分，也意味着你的查询会增加新的连接，从而增加额外的开销和运维的成本。分库分表分库分表的目的就是为了解决由于数据量过而导致数据库性能降低的问题，将原来独立的数据库拆分为若干数据库组成，将数据大表拆分成若干数据表，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。一般来说，在系统设计阶段就应该根据业务耦合程度来确定用哪种分库分表的方式(方案)，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且连续增长，再考虑水平分库水平分表的方案。分库分表的方式分库分表的方式在生产中通常包括：垂直分库、垂直分表、水平分库和水平分表四种。垂直分表定义：将一个表按字段分成多表，每个表存储其中一部分字段。带来的提升是：为了避免IO争抢并减少锁表的几率。充分发挥热门数据的操作效率。需要注意的是：拆分后，尽量从业务角度避免联查，否则性能方面将得不偿失。为什么大字段表的IO效率低：第一由于数据量本身大，需要更长的读取时间；第二是跨页，页是数据存储单位，很多查找及定位操作都是以页为单位，单页内的数据行越来越多数据库整体性能越好，而大字段占用空间大，单页内存储行数少，因此IO效率低。第三，数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率高，减少了磁盘IO，从而提升了数据库性能。垂直分库垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，从而使访问压力被分摊在多个服务器，大大提高性能，同时能提高整体架构的业务清晰度，不同的业务库可根据自身情况定制优化方案，它的核心理念是专库专用。带来的提升是：解决业务层面的耦合，业务清晰能对不同业务的数据进行分级管理、维护、监控和扩展等高并发场景下，垂直分库一定程度上提升IO、数据库连接和降低单机硬件资源的瓶颈水平分库水平分库就是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。带来的提升是：解决了单库大数据，高并发的性能瓶颈。提高了系统的稳定性和可用性。需要注意的是：使用水平分库不仅需要解决跨库带来的问题，还需要解决数据路由的问题。水平分表水平分表就是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中（对数据的拆分，不影响表结构）。它带来的提升是：优化单一表数据量过大而产生的性能问题避免IO争抢并减少锁表的几率从架构设计上优化查询性能比学习知识更重要的是灵活地调用知识储备高效解决实际问题的能力。想要提高应用程序的查询性能，还要结合实际的业务需求设计合理的功能架构。举个简单例子来说明一下：假设我们现在需要提供一个接口供前端显示统计数据。用于统计的数据存在一张千万级数据的表中，并且数据量级在快速增加。你会怎么设计这个接口，分库分表？首先会想到的是设计合适的索引来加快查询和统计速度。但是因为表的数据级很大，如果每次统计都在这张表中进行，处理耗时依然会很长。此时我们可以想办法把统计表的数据缩减，例如将统计表的数据提前统计好存入“统计表2”，使用定时任务将统计表新插入的数据集也统计合并到“统计表2”，需要统计数据时就在这张”统计表2”中进行。因为表的数据量小了，所以查询性能可以提高很多。但是需要注意的是，这样做会额外占用了很多存储空间，并且返回的统计数据并不精准，这就要看实际业务来做取舍了。

浏览量2023