【高性能MySQL】第5章高性能的索引策略

本文链接：https://blog.csdn.net/ma15732625261/article/details/81502615

5.3高性能的索引策略

诶~这个标题？

5.3.1独立的列

索引列不是表达式的一部分也不是函数的参数

养成简化where条件的习惯，将索引列单独放在比较符号的一侧

5.3.2前缀索引和索引选择性

1、索引很长可以截取部分字符（索引开始的部分字符），节省空间提高效率，但会降低选择性；

对于BLOB/TEXT或很长的VARCHAR列，必须使用前缀索引

2、索引选择性：不重复索引值（基础）和数据表的记录总数（#T）的比值，范围从1/#T到1间，正比

3、前缀的“基数”应接近于完整列的“基数”

1）找到最常见的值的列表，2）和最常见的前缀列表比较，3）需考虑最坏情况下选择性

如何创建前缀索引：

//结果0.0312，如果前缀的选择性接近0.031就可以安排了 
select count(distinct city)/count(*) from sakila.city_demo;

如果数据分布不均匀，可能会有陷阱

选择性比较低

创建：alter table sakila.city_demo add key (city(7));

前缀索引缺点：

无法做order by 和group by ，无法做覆盖扫描

5.3.3多列索引

多列上建独立索引大部分情况下并不能提高mysql查询性能

5.0及更新版本引入“索引合并”，一定程度上可使用表上多个单列索引来定位指定的行

糟糕的情况：

对多个索引相交操作（多个and），意味着需要一个包含all相关列的多列索引，不是多个独立单列索引
服务器对多个索引做联合操作（多个OR），需要耗费大量cpu和内存资源
优化器只关心随机页读取，不会将这些计算到“查询成本”中，消耗cpu 内存资源影响并发性
explain看到索引合并，应检查下查询和表结构，也可通过参数optimizer_switch关闭索引合并功能，也可使用ignore index提示让优化器忽略掉某些索引，直接全表扫描、union

5.3.4选择合适的索引列顺序

正确的顺序依赖使用该索引的查询，且考虑如何更好满足排序和分组的需要（本章适B-Tree 顺序存）

经验法则：

选择性最高的列放到索引最前列（不需考虑排序和分组时这种是最好的，此时只是用于优化where条件的查找）
性能依赖于：all索引列的选择性、查询条件具体值、值的分布、需据运行频率最高的查询来调整索引列的顺序

customer_id选择性更高，作为索引列第一列

示例：

一、

select * from payment where staff_id= 2 and customer_id =584;创建多列索引还是颠倒顺序：

select sum(staff_id = 2),sum(customer_id = 584) from payment结果sum(staff_id=2)7992 ……30

据经验法则，将索引列customer_id放前面，再看看，对应staff_id列的选择性：

select sum(staff_id=2) from payment where customer_id = 584结果：sum(staff_id=2):17

1、结果非常依赖于选定的具体值，这种优化方式，可能对其他条件值的查询不公平，性能下等

2、如果是从诸如pt-query-digest工具的报告中提取“最差”查询，可按上述办法选定索引顺序

二、

mysql为查询选择了(groupId,userId)看似合理

基本没用上

选择性和基数的经验法则值得研究分析，别忘了where子句中排序、分组和范围条件等因素

5.3.5聚簇索引

聚簇：数据行和相邻键值紧凑存储在一起

聚簇索引是一种数据存储方式，具体的细节依赖其实现方式，InnoDB的聚簇索引在同一个结构中保存了B-Tree索引和数据行：数据上存放在索引的叶子页中:聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针 ;

实际存储的循序结构与数据存储的物理机构是一致的，通俗地说：聚簇索引的顺序是数据的物理存储顺序；通常来说物理顺序结构只有一种，一个表的聚簇索引也只能有一个，通常默认主键，设置了主键，系统默认就为你加上了聚簇索引，也可自定义；说到这里非聚簇索引是不是就知道了【源】

存储引擎负责实现索引，不是all引擎支持聚簇索引；InnoDB通过主键聚集数据，上图被索引的列：主键，如果没有定义主键，innoDB会选择唯一的非空索引代替，如果没有则隐式定义一个主键来作为聚簇索引；InnoDB只聚集在同一个页面中的记录，包含相邻键值的页可能相距甚远；

聚簇主键可能利性能也可能累性能

优点：

1、相关数据保存在一起减磁盘I/O次数，2、数据访问快（都在一起），3、使用覆盖索引的查询可直接用页节点的主键值

缺点：

1、如果数据全在内存中，无优势；2、插入速度依赖插入顺序，按主键顺序插入速度最快，不是的话加载完成最好optimize table重新组织一下表；3、更新聚簇索引列的代价很高，强制InnoDB将每列更新的行移动到新的位置；4、基于聚簇索引的表在插入新行、主键被更新导致需移动行的时候，可能面临“页分裂”问题：当行的主键要求将这一行插入到某个已经满的页中时，引擎将该也分裂成两个页面来容纳该行、页分裂、占更多磁盘空间；5、可能导致全表扫描变慢，特别是行较稀疏时，或分页导致数据存储不连续时；6、二级索引（非聚簇索引）可能比较大：他的叶子节点包含了引用行的主键列；6、二级索引访问需两次索引查找：保存的是“行指针”主键 —— 行两次B-Tree

总的来猜：优势减少磁盘I/O，如果变动的话代价比较大，二级索引看下面