高性能Mysql学习笔记-创建高性能索引

最新推荐文章于 2021-01-27 14:22:10 发布

kiasur

最新推荐文章于 2021-01-27 14:22:10 发布

阅读量217

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/kiasur/article/details/79354866

版权

1 篇文章 0 订阅

订阅专栏

1.B-Tree创建一个伪哈希索引
新增一个被索引的crc列，使用CRC32做哈希
数据表大可以使用CRC64或者MD5取部分值，也可以使用FNV64()函数作为哈希函数（来自Percona Server插件）

2.索引的有点
减少需要扫描的数据量
避免排序和临时表
将随机I/O变为顺序I/O
索引存储实际列值

3.对于特大型表采用分区技术比建立索引更优

4.前缀索引优缺点
优点：使索引更小、更快
缺点：无法做ORDER BY和GROUP BY，无法做覆盖扫描

5.前缀索引的选择性
SELECT COUNT(DISTINCT col)/COUNT(*) FROM table
SELECT COUNT(DISTINCT LEFT(col,n)/COUNT(*) FROM table

6.optimizer_switch关闭索引合并功能

7.组合索引选择性
SELECT CONUNT(DISTINCT col1)/COUNT(*),
CONUNT(DISTINCT col2)/COUNT(*),
COUNT(*)
FROM table

SELECT COUNT(),SUM（col1=）,SUM(col2=)，SUM(col3=)FROM table

除了选择性和基数的经验法则外，还需要考虑WHERE子句中的排序、分组和范围条件等因素

8.使用UUID作为主键会影响插入速度以及占用更多的存储空间

9.OPTIMIZE TABLE 重建表并优化也填充

10.顺序主键也会造成更坏的结果
高并发，InnoDB会造成明显争用，可能导致间隙锁竞争以及AUTO_INCREMENT锁机制，此时可以考虑重新设计表或者应用，也可以更改innodb_autoinc_lock_mode配置

11.使用延迟关联复用覆盖索引
先利用二级索引来查主键ID，再通过主键ID使用覆盖索引获取数据
例如：
SELECT * FROM table
JOIN（
SELECT id FROM table
WHERE col=**
）AS T1 ON(t1.id=table.id)

12.组合索引前导列为常量时可以用组合索引排序

13.一些不能使用索引做排序的查询
使用两种不同的排序方向

引用了不在索引的列

查询条件与排序条件无法组成最左缀

前导列不是常量

查询多列时带有范围查询

14.解决冗余索引和重复索引方法
写一些复杂的访问INFORMATION_SCHEMA表的查询来找

使用Shlomi Noach的common_schema中的一些视图来定位

使用PerconaToolkit中的pt-duplicate-key-checker

使用Percona工具箱中的pt-upgrade工具来仔细检查计划中的索引变更

使用Percona Server或MariaDB中先打开userstates服务器变量，让服务器运行一段时间，再通过查询INFOMATION_SHEMA.INDEX_STATISTICS查询每个索引的使用率

使用Percona Toolkit中的pt-index-usage查询索引的使用率

15.使用IN()复用组合索引

16.CHECK TABLE能够找出大多数的表和索引的错误

17.ANALYZE TABLE重新生成统计信息

18.OPTIMIZE TABLE重新整理数据

19.使用pt-query-digest的查询审查“review”功能分析EXPLAIN出来的执行计划

20.InnoDB所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大

21.尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

关注