1、分页查询优化详解1.1、分页性能分析示例表CREATETABLEemployees(idint(11)NOTNULLAUTO_INCREMENT,namevarchar(24)NOTNULLDEFAULTCOMMENT姓名,ageint(11)NOTNULLDEFAULT0COMMENT年龄,positionvarchar(20)NOTNULLDEFAULTCOMMENT职位,hire_timetimestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT入职时间,PRIMARYKEY(id),KEYidx_name_age_position(name,age,position)USINGBTREE)ENGINEInnoDBAUTO_INCREMENT1DEFAULTCHARSETutf8COMMENT员工记录表;很多时候我们业务系统实现分页功能可能会用如下sql实现mysqlselect*fromemployeeslimit10000,10;表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录实际这条 SQL 是先读取 10010 条记录然后抛弃前 10000 条记录然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据执行效率是非常低的。1.2、常见的分页场景优化技巧1.2.1、根据自增且连续的主键排序的分页查询首先来看一个根据自增且连续主键排序的分页查询的例子mysqlselect*fromemployeeslimit90000,5;该 SQL 表示查询从第 90001开始的五行数据没添加单独 order by表示通过主键排序。我们再看表 employees 因为主键是自增并且连续的所以可以改写成按照主键去查询从第 90001开始的五行数据如下mysqlselect*fromemployeeswhereid90000limit5;查询的结果是一致的。我们再对比一下执行计划mysqlEXPLAINselect*fromemployeeslimit90000,5;mysqlEXPLAINselect*fromemployeeswhereid90000limit5;显然改写后的 SQL 走了索引而且扫描的行数大大减少执行效率更高。但是这条改写的SQL 在很多场景并不实用因为表中可能某些记录被删后主键空缺导致结果不一致。1.2.2、根据非主键字段排序的分页查询再看一个根据非主键字段排序的分页查询SQL 如下根据name字段排序mysqlselect*fromemployeesORDERBYnamelimit90000,5;mysqlEXPLAINselect*fromemployeesORDERBYnamelimit90000,5;发现并没有使用 name 字段的索引key 字段对应的值为 null具体原因上节课讲过扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高所以优化器放弃使用索引。知道不走索引的原因那么怎么优化呢其实关键是让排序时返回的字段尽可能少所以可以让排序和分页操作先查出主键然后根据主键查到对应的记录SQL改写如下mysqlselect*fromemployees einnerjoin(selectidfromemployeesorderbynamelimit90000,5)edone.ided.id;需要的结果与原 SQL 一致执行时间减少了一半以上我们再对比优化前后sql的执行计划原 SQL 使用的是 filesort 排序而优化后的 SQL 使用的是索引排序。2、表JOIN关联原理详解及优化t1插入了10000行数据t2插入了100条数据。-- 示例表CREATETABLEt1(idint(11)NOTNULLAUTO_INCREMENT,aint(11)DEFAULTNULL,bint(11)DEFAULTNULL,PRIMARYKEY(id),KEYidx_a(a))ENGINEInnoDBDEFAULTCHARSETutf8;createtablet2liket1;-- 插入一些示例数据-- 往t1表插入1万行记录dropprocedureifexistsinsert_t1;delimiter;;createprocedureinsert_t1()begindeclareiint;seti1;while(i10000)doinsertintot1(a,b)values(i,i);setii1;endwhile;end;;delimiter;callinsert_t1();-- 往t2表插入100行记录dropprocedureifexistsinsert_t2;delimiter;;createprocedureinsert_t2()begindeclareiint;seti1;while(i100)doinsertintot2(a,b)values(i,i);setii1;endwhile;end;;delimiter;callinsert_t2();2.1、mysql的表关联常见有两种算法索引是KEYidx_a(a)2.1.1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法一次一行循环地从第一张表称为驱动表中读取行在这行数据中取到关联字段根据关联字段在另一张表被驱动表里取出满足条件的行然后取出两张表的结果合集。mysqlEXPLAINselect*fromt1innerjoint2ont1.at2.a;从执行计划中可以看到这些信息驱动表是 t2被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)优化器一般会优先选择小表做驱动表用where条件过滤完驱动表然后再跟被驱动表做关联查询。所以使用 inner join 时排在前面的表并不一定就是驱动表。当使用left join时左表是驱动表右表是被驱动表当使用right join时右表时驱动表左表是被驱动表当使用join时mysql会选择数据量比较小的表作为驱动表大表作为被驱动表。使用了 NLJ算法。一般 join 语句中如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。上面sql的大致流程如下从表 t2 中读取一行数据如果t2表有查询过滤条件的用先用条件过滤完再从过滤结果里取出一行数据从第 1 步的数据中取出关联字段 a到表 t1 中查找取出表 t1 中满足条件的行跟 t2 中获取到的结果合并作为结果返回给客户端重复上面 3 步。整个过程会读取 t2 表的所有数据(扫描100行)然后遍历这每行数据中字段 a 的值根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引1次扫描可以认为最终只扫描 t1 表一行完整数据也就是总共 t1 表也扫描了100行)。因此整个过程扫描了 200 行。如果被驱动表的关联字段没索引使用NLJ算法性能会比较低(下面有详细解释)mysql会选择Block Nested-Loop Join算法。2.1.2、 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法索引是KEYidx_a(a)b是没有索引的把驱动表的数据读入到 join_buffer 中然后扫描被驱动表把被驱动表每一行取出来跟 join_buffer 中的数据做对比。mysqlEXPLAINselect*fromt1innerjoint2ont1.bt2.b;Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是BNL 算法。上面sql的大致流程如下把 t2 的所有数据放入到join_buffer中把表 t1 中每一行取出来跟 join_buffer 中的数据做对比返回满足 join 条件的数据整个过程对表 t1 和 t2 都做了一次全表扫描因此扫描的总行数为10000(表 t1 的数据总量) 100(表 t2 的数据总量) 10100。并且 join_buffer 里的数据是无序的因此对表 t1 中的每一行都要做 100 次判断所以内存中的判断次数是 100 * 10000100 万次。这个例子里表 t2 才 100 行要是表 t2 是一个大表join_buffer 放不下怎么办呢join_buffer 的大小是由参数 join_buffer_size 设定的默认值是 256k。如果放不下表 t2 的所有数据话策略很简单就是分段放。比如 t2 表有1000行记录 join_buffer 一次只能放800行数据那么执行过程就是先往 join_buffer 里放800行记录然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果然后清空 join_buffer 再放入 t2 表剩余200行记录再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢如果上面第二条sql使用 Nested-Loop Join那么扫描行数为 100 * 10000 100万次这个是磁盘扫描。很显然用BNL磁盘扫描次数少很多相比于磁盘扫描BNL的内存计算会快得多。因此MySQL对于被驱动表的关联字段没索引的关联查询一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法有索引的情况下 NLJ 算法比 BNL算法性能更高2.2、对于关联sql的优化关联字段加索引让mysql做join操作时尽量选择NLJ算法驱动表因为需要全部查询出来所以过滤的条件也尽量要走索引避免全表扫描总之能走索引的过滤条件尽量都走索引小表驱动大表写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式省去mysql优化器自己判断的时间2.3、straight_join解释straight_join功能同join类似但能让左边的表来驱动右边的表能改表优化器对于联表查询的执行顺序。比如select * from t2 straight_join t1 on t2.a t1.a; 代表指定mysql选着 t2 表作为驱动表。straight_join只适用于inner join并不适用于left joinright join。因为left joinright join已经代表指定了表的执行顺序尽可能让优化器去判断因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。2.4、对于小表定义的明确在决定哪个表做驱动表的时候应该是两个表按照各自的条件过滤过滤完成之后计算参与 join 的各个字段的总数据量数据量小的那个表就是“小表”应该作为驱动表。2.5、in和exsits优化原则小表驱动大表即小的数据集驱动大的数据集2.5.1、in当B表的数据集小于A表的数据集时in优于existsselect*fromAwhereidin(selectidfromB)#等价于for(selectidfromB){select*fromAwhereA.idB.id }2.5.2、exists当A表的数据集小于B表的数据集时exists优于in将主查询A的数据放到子查询B中做条件验证根据验证结果true或false来决定主查询的数据是否保留select*fromAwhereexists(select1fromBwhereB.idA.id)#等价于:for(select*fromA){select*fromBwhereB.idA.id }#A表与B表的ID字段应建立索引EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比EXISTS子查询往往也可以用JOIN来代替何种最优需要具体问题具体分析3、表COUNT查询优化3.1、count(1)、count(id)、count(name)、count(*)比较-- 临时关闭mysql查询缓存为了查看sql多次执行的真实时间mysqlsetglobalquery_cache_size0;mysqlsetglobalquery_cache_type0;mysqlEXPLAINselectcount(1)fromemployees;mysqlEXPLAINselectcount(id)fromemployees;mysqlEXPLAINselectcount(name)fromemployees;mysqlEXPLAINselectcount(*)fromemployees;注意以上4条sql只有根据某个字段count不会统计字段为null值的数据行四个sql的执行计划一样说明这四个sql执行效率应该差不多字段有索引count(*)≈count(1)count(字段)count(主键 id) //字段有索引count(字段)统计走二级索引二级索引存储数据比主键索引少所以count(字段)count(主键 id)字段无索引count(*)≈count(1)count(主键 id)count(字段) //字段没有索引count(字段)统计走不了索引count(主键 id)还可以走主键索引所以count(主键 id)count(字段)count(1)跟count(字段)执行过程类似不过count(1)不需要取出字段统计就用常量1做统计count(字段)还需要取出字段所以理论上count(1)比count(字段)会快一点。count(*) 是例外mysql并不会把全部字段取出来而是专门做了优化不取值按行累加效率很高所以不需要用count(列名)或count(常量)来替代 count(*)。为什么对于count(id)mysql最终选择辅助索引而不是主键聚集索引因为二级索引相对主键索引存储数据更少检索性能应该更高mysql内部做了点优化(应该是在5.7版本才优化)。3.2、常见优化方法3.2.1、查询mysql自己维护的总行数对于myisam存储引擎的表做不带where条件的count查询性能是很高的因为myisam存储引擎的表的总行数会被mysql存储在磁盘上查询不需要计算对于innodb存储引擎的表mysql不会存储表的总记录行数(因为有MVCC机制后面会讲)查询count需要实时计算3.2.2、show table status(估计值不精确)如果只需要知道表总行数的估计值可以用如下sql查询性能很高3.2.3、将总数维护到Redis里插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)但是这种方式可能不准很难保证表操作和redis操作的事务一致性3.2.4、增加数据库计数表插入或删除表数据行的时候同时维护计数表让他们在同一个事务里操作4、阿里巴巴MYSQL规范解读5、MYSQL数据类型选择分析在MySQL中选择正确的数据类型对于性能至关重要。一般应该遵循下面两步1确定合适的大类型数字、字符串、时间、二进制2确定具体的类型有无符号、取值范围、变长定长等。在MySQL数据类型设置方面尽量用更小的数据类型因为它们通常有更好的性能花费更少的硬件资源。并且尽量把字段定义为NOT NULL避免使用NULL。5.1、数值类型类型大小范围 (有符号)范围 (无符号)用途TINYINT1 字节(-128, 127)(0, 255)小整数值SMALLINT2 字节(-32 768, 32 767)(0, 65 535)大整数值MEDIUMINT3 字节(-8 388 608, 8 388 607)(0, 16 777 215)大整数值INT或INTEGER4 字节(-2 147 483 648, 2 147 483 647)(0, 4 294 967 295)大整数值BIGINT8 字节(-9 233 372 036 854 775 808, 9 223 372 036 854 775 807)(0, 18 446 744 073 709 551 615)极大整数值FLOAT4 字节(-3.402 823 466 E38, 1.175 494 351 E-38)0(1.175 494 351 E-383.402 823 466 351 E38)0, (1.175 494 351 E-38, 3.402 823 466 E38)单精度浮点数值DOUBLE8 字节(1.797 693 134 862 315 7 E308, 2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E308)0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E308)双精度浮点数值DECIMAL对DECIMAL(M,D) 如果MD为M2否则为D2依赖于M和D的值依赖于M和D的值小数值优化建议如果整形数据没有负数如ID号建议指定为UNSIGNED无符号类型容量可以扩大一倍。建议使用TINYINT代替ENUM、BITENUM、SET。避免使用整数的显示宽度(参看文档最后)也就是说不要用INT(10)类似的方法指定字段显示宽度直接用INT。DECIMAL最适合保存准确度要求高而且用于计算的数据比如价格。但是在使用DECIMAL类型的时候注意长度设置。建议使用整形类型来运算和存储实数方法是实数乘以相应的倍数后再操作。整数通常是最佳的数据类型因为它速度快并且能使用AUTO_INCREMENT。PSINT显示宽度我们经常会使用命令来创建数据表而且同时会指定一个长度如下。但是这里的长度并非是TINYINT类型存储的最大长度而是显示的最大长度。CREATETABLEuser(idTINYINT(2)UNSIGNED);这里表示user表的id字段的类型是TINYINT可以存储的最大数值是255。所以在存储数据时如果存入值小于等于255如200虽然超过2位但是没有超出TINYINT类型长度所以可以正常保存如果存入值大于255如500那么MySQL会自动保存为TINYINT类型的最大值255。在查询数据时不管查询结果为何值都按实际输出。这里TINYINT(2)中2的作用就是当需要在查询结果前填充0时命令中加上ZEROFILL就可以实现如idTINYINT(2)UNSIGNEDZEROFILL这样查询结果如果是5那输出就是05。如果指定TINYINT(5)那输出就是00005其实实际存储的值还是5而且存储的数据不会超过255只是MySQL输出数据时在前面填充了0。换句话说在MySQL命令中字段的类型长度TINYINT(2)、INT(11)不会影响数据的插入只会在使用ZEROFILL时有用让查询结果前填充0。5.2、日期和时间类型大小(字节)范围格式用途DATE31000-01-01 到 9999-12-31YYYY-MM-DD日期值TIME3‘-838:59:59’ 到 ‘838:59:59’HH:MM:SS时间值或持续时间YEAR11901 到 2155YYYY年份值DATETIME81000-01-01 00:00:00 到 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值TIMESTAMP41970-01-01 00:00:00 到 2038-01-19 03:14:07YYYYMMDDhhmmss混合日期和时间值时间戳优化建议MySQL能存储的最小时间粒度为秒。建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。用MySQL的内建类型DATE、TIME、DATETIME来存储时间而不是使用字符串。当数据格式为TIMESTAMP和DATETIME时可以用CURRENT_TIMESTAMP作为默认MySQL5.6以后MySQL会自动返回记录插入的确切时间。TIMESTAMP是UTC时间戳与时区相关。DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数与时区无关你存了什么读出来就是什么。除非有特殊需求一般的公司建议使用TIMESTAMP它比DATETIME更节约空间但是像阿里这样的公司一般会用DATETIME因为不用考虑TIMESTAMP将来的时间上限问题。有时人们把Unix的时间戳保存为整数值但是这通常没有任何好处这种格式处理起来不太方便我们并不推荐它。5.3、字符串类型大小用途CHAR0-255字节定长字符串char(n)当插入的字符数不足n时(n代表字符数)插入空格进行补充保存。在进行检索时尾部的空格会被去掉。VARCHAR0-65535字节变长字符串varchar(n)中的n代表最大字符数插入的字符数不足n时不会补充空格TINYBLOB0-255字节不超过 255 个字符的二进制字符串TINYTEXT0-255字节短文本字符串BLOB0-65 535字节二进制形式的长文本数据TEXT0-65 535字节长文本数据MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 215字节中等长度文本数据LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据LONGTEXT0-4 294 967 295字节极大文本数据优化建议字符串的长度相差较大用VARCHAR字符串短且所有值都接近一个长度用CHAR。CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存因为可能会导致一些与计算相关的问题。换句话说可能影响到计算的准确性和完整性。尽量少用BLOB和TEXT如果实在要用可以考虑将BLOB和TEXT字段单独存一张表用id关联。BLOB系列存储二进制字符串与字符集无关。TEXT系列存储非二进制字符串与字符集相关。BLOB和TEXT都不能有默认值。