mysql 8.0 ~ 索引优化

最新推荐文章于 2024-07-13 09:26:28 发布

weixin_33704234

最新推荐文章于 2024-07-13 09:26:28 发布

阅读量1k

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/danhuangpai/p/10314557.html

版权

一简介:今天来聊天mysql8.0关于索引
二增强
1 支持隐藏索引，这个可以用来测试创建的索引是否具有优化作用
创建 alter table t1 alter index u1 invisible/visible
注意

1 哪怕是隐藏索引,也必须保持索引的更新
2 通过show index from最后一列可以查看索引相关情况
3 这项操作不会阻塞DDL,是非常快的
2 增加降序索引，可以加快排序相关查询
简介:mysql5.7即便指定降序索引,也是无法进行创建的
创建: `idx_c1_c2` (`c1`,`c2` DESC) 可以在show create table查看
目标: 针对是多列的order by排序,按照不同顺序,针对单列做降序索引没有任何意义
解决问题: 1 能消除因为多列排序导致的filesoft,explain中会出现 Backward index scan(反向扫描) 2 不再对group by进行隐式排序 GROUP BY ASC和GROUP BY DESC语法已经被废弃

3 增加函数索引

简介: 针对列的运算也可以组合成索引了

创建: alter table add index aaa((c2-c1),);

注意: 1 是双括号 2 可以包含多列计算,和联合索引一样 3 不能包含主键

4 skip scan

简介: 非前缀索引也可以用了这是一个重要的信息

说明: 在5.X版本的mysql中,一旦建立的联合索引无法应用到第一个索引,就不会再选用该联合索引.到了8.0,mysql会对前列的索引值进行赋予然后分别进行查询,最后合并汇总,这样就能构建联合索引的使用场景了

三新功能直方图
1 直方图的解释
定义直方图能近似获得一列的数据分布情况，从而让数据库知道它含有哪些数据(数据分布)
2 创建删除直方图命令
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] WITH N BUCKETS;
ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name];
3 注意事项
1 可指定多个列
2 N值为1-1024 默认为100
3 直方图不能通过show create table/index from查看
4 直方图针对的是普通索引,唯一性索引是无法创建直方图的
5 直方图针对的基数很低的列,因为mysql可能认为它是分布均匀的
6 直方图是针对索引无法优化后的一个补偿手段,而且是0成本的
7 直方图建立是有成本的,因为要读数据到内存上,建议低峰期建立
8 直方图分部等宽直方图（singleton）、等高直方图（equi-height）

4 直方图和索引的对比
1 维护一个索引有代价。每一次的insert、update、delete都会需要更新索引，会对性能有一定的影响。而直方图一次创建永不更新
2 如果有索引，优化器用使用index dives技术来估算符合条件范围的记录数量。这种方式也是有代价的，特别是查询语句条件中有很长的IN列表。直方图相对而言代价小，因此可能更合适
3 通过explain的filtered(精确率)进行判断,如果通过直方图能提高精确率,哪怕是全表扫描,依然速度会很快
5 直方图相关变量
histogram_generation_max_mem_size 默认值 20000000
作用 MySQL会根据给定的histogram_generation_max_mem_size的值计算该将多少行数据读到内存中。如果根据当前histogram_generation_max_mem_size的限制，MySQL认为只能读一部分数据，那么MySQL会进行取样
6 常用sql语句
1 SELECT histogram->>'$."sampling-rate"' FROM information_schema.column_statistics WHERE table_name = "table" AND column_name = "column"; 查询取样率
2 SELECT TABLE_NAME,COLUMN_NAME,HISTOGRAM->>'$."number-of-buckets-specified"' AS num_buckets_specified,JSON_LENGTH(HISTOGRAM, '$.buckets') AS num_buckets_created FROM INFORMATION_SCHEMA.COLUMN_STATISTICS WHERE SCHEMA_NAME = "";
1 如果num_buckets_created与num_buckets_specified相等，那么存在可能，在创建直方图的时候指定的buckets不够多，那么此时可以通过增加buckets的数量，来提高直方图的准确性
7 总结
直方图能对重复值较多的列实现加速过滤效果,如果区分度小于1024的列,那么真的不建议使用

转载于:https://www.cnblogs.com/danhuangpai/p/10314557.html