mysql数据库表太大查询慢优化的几种方式？

优化方案：主从同步+读写星散：这个表在有装备条件的情形下，读写星散，这样能削减许多压力，而且数据稳固性也能提高纵向分表：凭证原则，每个表最多不要跨越5个索引，纵向拆分字段，将部门字段拆到一个新表通常我们按以下原则进行垂直拆分:（先区分这个表中的冷热数据字段）把不常用的字段单独放在一张表;把text，blob等大字段拆分出来放在附表中;经常组合查询的列放在一张表中;瑕玷是：许多逻辑需要重写，带来很大的事情量。使用表分区：这个是推荐的一个解决方案，不会带来重写逻辑等，可以凭证时间来进行表分区，相当于在统一个磁盘上，表的数据存在差其余文件夹内，能够极大的提高查询速率。横向分表：1000W条数据不少的，会带来一些运维压力，备份的时刻，单表备份所需时间会很长，以是可以凭证服务器硬件条件进行水中分表，每个表有若干数据为准。

如何使用MySQL中的适用函数及查询技巧？

一直以来，MySQL 只有针对聚合函数的汇总类功效，好比MAX, AVG 等，没有从 SQL 层针对聚合类每组睁开处置的功效。不外 MySQL 开放了 UDF 接口，可以用 C 来自己写UDF，这个就增添了功效行难度。

这种针对每组睁开处置的功效就叫窗口函数，有的数据库叫剖析函数。

在 MySQL 8.0 之前，我们想要获得这样的效果，就得用以下几种方式来实现：

1. session 变量

2. group_concat 函数组合

3. 自己写 store routines

接下来我们用经典的学生/课程/成就来做窗口函数演示

准备

学生表

mysql> show create table student \G*************************** 1. row ***************************Table: studentCreate Table: CREATE TABLE student (sid int(10) unsigned NOT NULL,sname varchar(64) DEFAULT NULL,PRIMARY KEY (sid)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)课程表

mysql> show create table course\G*************************** 1. row ***************************Table: courseCreate Table: CREATE TABLE `course` (`cid` int(10) unsigned NOT NULL,`cname` varchar(64) DEFAULT NULL,PRIMARY KEY (`cid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)成就表

mysql> show create table score\G*************************** 1. row ***************************Table: scoreCreate Table: CREATE TABLE `score` (`sid` int(10) unsigned NOT NULL,`cid` int(10) unsigned NOT NULL,`score` tinyint(3) unsigned DEFAULT NULL,PRIMARY KEY (`sid`,`cid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)测试数据

mysql> select * from student;+———–+————–+| sid | sname |+———–+————–+| 201910001 | 张三 || 201910002 | 李四 || 201910003 | 武松 || 201910004 | 潘金莲 || 201910005 | 菠菜 || 201910006 | 杨发家 || 201910007 | 欧阳修 || 201910008 | 郭靖 || 201910009 | 黄蓉 || 201910010 | 东方不败 |+———–+————–+10 rows in set (0.00 sec)

mysql> select * from score;;+———–+———-+——-+| sid | cid | score |+———–+———-+——-+| 201910001 | 20192001 | 50 || 201910001 | 20192002 | 88 || 201910001 | 20192003 | 54 || 201910001 | 20192004 | 43 || 201910001 | 20192005 | 89 || 201910002 | 20192001 | 79 || 201910002 | 20192002 | 97 || 201910002 | 20192003 | 82 || 201910002 | 20192004 | 85 || 201910002 | 20192005 | 80 || 201910003 | 20192001 | 48 || 201910003 | 20192002 | 98 || 201910003 | 20192003 | 47 || 201910003 | 20192004 | 41 || 201910003 | 20192005 | 34 || 201910004 | 20192001 | 81 || 201910004 | 20192002 | 69 || 201910004 | 20192003 | 67 || 201910004 | 20192004 | 99 || 201910004 | 20192005 | 61 || 201910005 | 20192001 | 40 || 201910005 | 20192002 | 52 || 201910005 | 20192003 | 39 || 201910005 | 20192004 | 74 || 201910005 | 20192005 | 86 || 201910006 | 20192001 | 42 || 201910006 | 20192002 | 52 || 201910006 | 20192003 | 36 || 201910006 | 20192004 | 58 || 201910006 | 20192005 | 84 || 201910007 | 20192001 | 79 || 201910007 | 20192002 | 43 || 201910007 | 20192003 | 79 || 201910007 | 20192004 | 98 || 201910007 | 20192005 | 88 || 201910008 | 20192001 | 45 || 201910008 | 20192002 | 65 || 201910008 | 20192003 | 90 || 201910008 | 20192004 | 89 || 201910008 | 20192005 | 74 || 201910009 | 20192001 | 73 || 201910009 | 20192002 | 42 || 201910009 | 20192003 | 95 || 201910009 | 20192004 | 46 || 201910009 | 20192005 | 45 || 201910010 | 20192001 | 58 || 201910010 | 20192002 | 52 || 201910010 | 20192003 | 55 || 201910010 | 20192004 | 87 || 201910010 | 20192005 | 36 |+———–+———-+——-+50 rows in set (0.00 sec)

mysql> select * from course;+———-+————+| cid | cname |+———-+————+| 20192001 | mysql || 20192002 | oracle || 20192003 | postgresql || 20192004 | mongodb || 20192005 | dble |+———-+————+5 rows in set (0.00 sec)MySQL 8.0 之前

好比我们求成就排名前三的学生排名，我来举个用 session 变量和 group_concat 函数来划分实现的例子：

session 变量方式

每组最先赋一个初始值序号和初始分组字段。

SELECTb.cname,a.sname,c.score, c.ranking_scoreFROMstudent a,course b,(SELECTc.*,IF(@cid = c.cid,@rn := @rn + 1,@rn := 1) AS ranking_score,@cid := c.cid AS tmpcidFROM(SELECT*FROMscoreORDER BY cid,score DESC) c,(SELECT@rn := 0 rn,@cid := ”) initialize_table) cWHERE a.sid = c.sidAND b.cid = c.cidAND c.ranking_score <= 3ORDER BY b.cname,c.ranking_score;

+————+———–+——-+—————+| cname | sname | score | ranking_score |+————+———–+——-+—————+| dble | 张三 | 89 | 1 || dble | 欧阳修 | 88 | 2 || dble | 菠菜 | 86 | 3 || mongodb | 潘金莲 | 99 | 1 || mongodb | 欧阳修 | 98 | 2 || mongodb | 郭靖 | 89 | 3 || mysql | 李四 | 100 | 1 || mysql | 潘金莲 | 81 | 2 || mysql | 欧阳修 | 79 | 3 || oracle | 武松 | 98 | 1 || oracle | 李四 | 97 | 2 || oracle | 张三 | 88 | 3 || postgresql | 黄蓉 | 95 | 1 || postgresql | 郭靖 | 90 | 2 || postgresql | 李四 | 82 | 3 |+————+———–+——-+—————+15 rows in set, 5 warnings (0.01 sec)group_concat 函数方式

使用 findinset 内置函数来返回下标作为序号使用。

SELECT*FROM(SELECTb.cname,a.sname,c.score,FIND_IN_SET(c.score, d.gp) score_rankingFROMstudent a,course b,score c,(SELECTcid,GROUP_CONCAT(scoreORDER BY score DESC SEPARATOR ‘,’) gpFROMscoreGROUP BY cidORDER BY score DESC) dWHERE a.sid = c.sidAND b.cid = c.cidAND c.cid = d.cidORDER BY d.cid,score_ranking) yttWHERE score_ranking <= 3；

+————+———–+——-+—————+| cname | sname | score | score_ranking |+————+———–+——-+—————+| dble | 张三 | 89 | 1 || dble | 欧阳修 | 88 | 2 || dble | 菠菜 | 86 | 3 || mongodb | 潘金莲 | 99 | 1 || mongodb | 欧阳修 | 98 | 2 || mongodb | 郭靖 | 89 | 3 || mysql | 李四 | 100 | 1 || mysql | 潘金莲 | 81 | 2 || mysql | 欧阳修 | 79 | 3 || oracle | 武松 | 98 | 1 || oracle | 李四 | 97 | 2 || oracle | 张三 | 88 | 3 || postgresql | 黄蓉 | 95 | 1 || postgresql | 郭靖 | 90 | 2 || postgresql | 李四 | 82 | 3 |+————+———–+——-+—————+15 rows in set (0.00 sec)MySQL 8.0 窗口函数

MySQL 8.0 后提供了原生的窗口函数支持，语法和大多数数据库一样，好比照样之前的例子：

用 row_number() over () 直接来检索排名。

mysql>SELECT*FROM(SELECTb.cname,a.sname,c.score,row_number() over (PARTITION BY b.cnameORDER BY c.score DESC) score_rankFROMstudent AS a,course AS b,score AS cWHERE a.sid = c.sidAND b.cid = c.cid) yttWHERE score_rank <= 3;

+————+———–+——-+————+| cname | sname | score | score_rank |+————+———–+——-+————+| dble | 张三 | 89 | 1 || dble | 欧阳修 | 88 | 2 || dble | 菠菜 | 86 | 3 || mongodb | 潘金莲 | 99 | 1 || mongodb | 欧阳修 | 98 | 2 || mongodb | 郭靖 | 89 | 3 || mysql | 李四 | 100 | 1 || mysql | 潘金莲 | 81 | 2 || mysql | 欧阳修 | 79 | 3 || oracle | 武松 | 98 | 1 || oracle | 李四 | 97 | 2 || oracle | 张三 | 88 | 3 || postgresql | 黄蓉 | 95 | 1 || postgresql | 郭靖 | 90 | 2 || postgresql | 李四 | 82 | 3 |+————+———–+——-+————+15 rows in set (0.00 sec)那我们再找出课程 MySQL 和 DBLE 里不及格的倒数前两名学生名单。

mysql>SELECT*FROM(SELECTb.cname,a.sname,c.score,row_number () over (PARTITION BY b.cidORDER BY c.score ASC) score_rankingFROMstudent AS a,course AS b,score AS cWHERE a.sid = c.sidAND b.cid = c.cidAND b.cid IN (20192005, 20192001)AND c.score < 60) yttWHERE score_ranking < 3;

+——-+————–+——-+—————+| cname | sname | score | score_ranking |+——-+————–+——-+—————+| mysql | 菠菜 | 40 | 1 || mysql | 杨发家 | 42 | 2 || dble | 武松 | 34 | 1 || dble | 东方不败 | 36 | 2 |+——-+————–+——-+—————+4 rows in set (0.00 sec)到此为止，我们只是演示了row_number() over() 函数的使用方式，其他的函数有兴趣的同伙可以自己体验体验，方式都差不多。