学员表 插 50万 条, 班级表 插 1万 条。
命令开启:允许创建函数设置:
保证每条数据都不同。
随机产生班级编号
创建往class表中插入数据的存储过程
class
stu
创建存储过程
执行存储过程
如果此时再插入一条主键值为 9 的记录,那它插入的位置就如下图:
可这个数据页已经满了,再插进来咋办呢?我们需要把当前 页面分裂 成两个页面,把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么?意味着: 性能损耗 !所以如果我们想尽量避免这样无谓的性能损耗,最好让插入的记录的 主键值依次递增 ,这样就不会发生这样的性能损耗了。所以我们建议:让主键具有 AUTO_INCREMENT ,让存储引擎自己为表生成主键,而不是我们手动插入 ,比如: person_info 表:
我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性,在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小,顺序写入,减少页分裂。
创建索引
第一种:索引优化生效
第二种:索引优化失效
type为“ALL”,表示没有使用到索引。
再举例:
再举例:
3. student表的字段name上设置有索引
下列哪个sql语句可以用到索引。(假设name字段上设置有索引)
name=123发生类型转换,索引失效。
统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。
下面开始 EXPLAIN 分析
结论:type 有All
添加索引优化
可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行,左边一定都有,所以 右边是我们的关键点,一定需要建立索引。
接着:
换成 inner join(MySQL自动选择驱动表)
添加索引优化
接着:
接着:
Index Nested-Loop Join
我们来看一下这个语句:
如果直接使用join语句,MySQL优化器可能会选择表t1或t2作为驱动表,这样会影响我们分析SQL语句的执行过程。所以,为了便于分析执行过程中的性能问题,我改用 straight_join 让MySQL使用固定的连接方式执行查询,这样优化器只会按照我们指定的方式去join。在这个语句里,t1 是驱动表,t2是被驱动表。
可以看到,在这条语句里,被驱动表t2的字段a上有索引,join过程用上了这个索引,因此这个语句的执行流程是这样的:
MySQL从4.1版本开始支持子查询,使用子查询可以进行SELECT语句的嵌套查询,即一个SELECT查询的结果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。
子查询是 MySQL 的一项重要的功能,可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是,子查询的执行效率不高。原因:
① 执行子查询时,MySQL需要为内层查询语句的查询结果 建立一个临时表 ,然后外层查询语句从临时表中查询记录。查询完毕后,再 撤销这些临时表 。这样会消耗过多的CPU和IO资源,产生大量的慢查询。
② 子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都 不会存在索引 ,所以查询性能会受到一定的影响。
③ 对于返回结果集比较大的子查询,其对查询性能的影响也就越大。
在MySQL中,可以使用连接(JOIN)查询来替代子查询。连接查询 不需要建立临时表 ,其 速度比子查询要快 ,如果查询中使用索引的话,性能就会更好。
问题:在 WHERE 条件字段上加索引,但是为什么在 ORDER BY 字段上还要加索引呢?
优化建议:
ORDER BY子句,尽量使用Index方式排序,避免使用FileSort方式排序。
执行案例前先清除student上的索引,只留主键:
场景:查询年龄为30岁的,且学生编号小于101000的学生,按用户名称排序
结论:type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
优化思路:
方案一: 为了去掉filesort我们可以把索引建成
方案二: 尽量让where的过滤条件和排序使用上索引
建一个三个字段的组合索引:
结果竟然有 filesort的 sql 运行速度, 超过了已经优化掉 filesort的 sql ,而且快了很多,几乎一瞬间就出现了结果。
从磁盘读取查询需要的 所有列 ,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出, 它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间, 因为它把每一行都保存在内存中了。
优化思路一:
在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。
优化思路二:
该方案适用于主键自增的表,可以把Limit 查询转换成某个位置的查询 。
理解方式一:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
理解方式二:非聚簇复合索引的一种形式,它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列(即建索引的字段正好是覆盖查询条件中所涉及的字段)。
简单说就是, 索引列+主键 包含 SELECT 到 FROM之间查询的列 。
好处:
有一张教师表,表定义如下:
讲师要使用邮箱登录,所以业务代码中一定会出现类似于这样的语句:
如果email这个字段上没有索引,那么这个语句就只能做 全表扫描 。
MySQL是支持前缀索引的。默认地,如果你创建索引的语句不指定前缀长度,那么索引就会包含整个字符串。
这两种不同的定义在数据结构和存储上有什么区别呢?下图就是这两个索引的示意图。
以及:
如果使用的是index1(即email整个字符串的索引结构),执行顺序是这样的:
结论:
使用前缀索引就用不上覆盖索引对查询性能的优化了,这也是你在选择是否使用前缀索引时需要考虑的一个因素。
Index Condition Pushdown(ICP)是MySQL 5.6中新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。
在不使用ICP索引扫描的过程:
storage层:只将满足index key条件的索引记录对应的整行记录取出,返回给server层。
server 层:对返回的数据,使用后面的where条件过滤,直至返回最后一行。
使用ICP扫描的过程:
ICP的使用条件:
① 只能用于二级索引(secondary index)
②explain显示的执行计划中type值(join 类型)为 range 、 ref 、 eq_ref 或者 ref_or_null 。
③ 并非全部where条件都可以用ICP筛选,如果where条件的字段不在索引列中,还是要读取整表的记录
到server端做where过滤。
④ ICP可以用于MyISAM和InnnoDB存储引擎
⑤ MySQL 5.6版本的不支持分区表的ICP功能,5.7版本的开始支持。
⑥ 当SQL使用覆盖索引时,不支持ICP优化方法。
案例1:
案例2:
从性能的角度考虑,选择唯一索引还是普通索引呢?选择的依据是什么呢?
假设,我们有一个主键列为ID的表,表中有字段k,并且在k上有索引,假设字段 k 上的值都不重复。
这个表的建表语句是:
假设,执行查询的语句是 select id from test where k=5。
为了说明普通索引和唯一索引对更新语句性能的影响这个问题,介绍一下change buffer。
当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下, InooDB会将这些更新操作缓存在change buffer中 ,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
将change buffer中的操作应用到原数据页,得到最新结果的过程称为 merge 。除了 访问这个数据页 会触发merge外,系统有 后台线程会定期 merge。在 数据库正常关闭(shutdown) 的过程中,也会执行merge操作。
如果能够将更新操作先记录在change buffer, 减少读磁盘 ,语句的执行速度会得到明显的提升。而且,数据读入内存是需要占用 buffer pool 的,所以这种方式还能够 避免占用内存 ,提高内存利用率。
唯一索引的更新就不能使用change buffer ,实际上也只有普通索引可以使用。
如果要在这张表中插入一个新记录(4,400)的话,InnoDB的处理流程是怎样的?
在表查询中,建议明确字段,不要使用 * 作为查询的字段列表,推荐使用SELECT <字段列表> 查询。原因:
① MySQL 在解析的过程中,会通过 查询数据字典 将"*"按序转换成所有列名,这会大大的耗费资源和时间。
② 无法使用 覆盖索引。
针对的是会扫描全表的 SQL 语句,如果你可以确定结果集只有一条,那么加上 LIMIT 1 的时候,当找到一条结果的时候就不会继续扫描了,这样会加快查询速度。
如果数据表已经对字段建立了唯一索引,那么可以通过索引进行查询,不会全表扫描的话,就不需要加上 LIMIT 1 了。
只要有可能,在程序中尽量多使用 COMMIT,这样程序的性能得到提高,需求也会因为 COMMIT 所释放的资源而减少。
COMMIT 所释放的资源:
有话要说...