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,如果变动的话代价比较大,二级索引看下面
InnoDB和MyISAM数据分布对比
聚簇非聚簇数据分布有别,对应的主键索引 二级索引数据分布也有别
主键取值1~10000,随机插入使用OPTIMIZE TABLE,磁盘已存储最优,行顺序随机,列col2值从1~100随机赋值,有很多重复值;
MyISAM主键索引即名为PRIMARY唯一非空索引;数据分布简单,按照插入顺序存储在磁盘
col2同col1 、略
InnoDB数据分布:
图省略了非叶子节点细节,非叶子节点包含索引列和指向下级节点的指针
在InnoDB表中按主键顺序插入行
无需要聚集的数据,自增列做主键(保证顺序写入,主键关联操作性能好);避免随机聚簇索引(I/O密集型的应用),UUID做聚簇索引很槽(插入随机、无聚集特性、时间长、空间大、页分裂、碎片)
UUID聚簇索引:新行的主键值不一定比之前的大,InnoDB要为新行寻找合适位置且分配空间(多次I/O 页分裂 移动数据 碎片),额外工作、分布不够优化
顺序主键何时造成更坏结果:
高并发工作负载,InnoDB按主键顺序插入可能造成明显争用,主键上界成为“热点”:alll插入发生在这里,并发插入导致间接锁竞争;
auto_increment锁机制;考虑重新设计表或应用,或更改innodb_autoinc_lock_mode配置(更新版本)
小结:
独立的列:索引列不是表达式的一部分或函数的参数
前缀索引和索引选择性;选择性算是为前缀索引服务
多列索引、单列索引:对比理解
选择合适的索引列顺序:很重要
聚簇索引:知识需要一个反复的过程