mysql高性能读书笔记8

MySQL只访问索引的查询叫覆盖索引，即只需访问索引，不需访问数据行，就是不需要回表查询，减少一次读取数据行的io操作，由于二级索引在叶子节点上保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免根据主键值在主键索引（也叫聚簇索引）中对索引树进行的二次查询来读取数据行，而且覆盖索引必须存储索引列的值。注意：索引只能最左匹配，即按照索引的左列开始匹配。查找某个范围列的查询，右边的列都无法使用索引优化查找。哈希索引：只有精确匹配索引的所有列的查询才有效，只支持等值查询，不支持范围查询。假如一个列的字符串比较长可以截取前面的字符串作为索引，也就前缀索引，具体截取多长，可以使用函数count(distinct left(列名,length))/count(*)来查询比率接近0.031比较好，或者查询每个结果分布比较均匀也比较好，但是MySQL无法使用前缀索引最order by和group by。也可以使用后缀索引，但是MySQL原生不支持后缀索引，可以反转字符串存储。索引列的顺序影响查询速度，可以比较select sum(column1=1),sum(column2=2) from table来查看哪个值小就过滤哪个。聚簇索引（也叫主键索引）不是单独的索引类型，而是一种数据存储的方式，保存了B-tree索引和数据行，数据行实际上存放在索引的叶子页leaf page上，表示数据行和相邻的键值紧凑的存储在一起，叶子节点上包含主键列，事务ID，回滚指针，其他列，可以说聚簇索引就是表。因为无法同时把数据行在两个不同的地方，所以一个表只能有一个聚簇索引，不过覆盖索引可以模拟多个聚簇随索引的情况。缺点是：更新聚簇索引列代价很高，会强制innodb将每个被更新的行移动到新的位置，插入或更新数据容易导致页分裂问题，这样会占用更多磁盘空间，二级索引会更大，因为二级索引的叶子节点包含了引用行的主键列，占用更多的空间，而不是行指针，换来的好处是innodb在移动行时无需更新二级索引中的行指针，而且二级索引需要两次索引查找，先找到主键列的值，再根据值去聚簇索引的索引树中查找数据行，两次B-tree查找。顺序主键容易成为热点，因为所有的插入都发生在这里，所以并发插入容易导致间隙锁竞争。使用索引对order by子句做排序跟其他索引查询的限制一样，满足索引列的最左前缀要去，甚至最左列可以是常量。索引可以让查询锁定更少的行，减少锁争用，提高并发性，innodb行锁效率高，只有访问行的时候才会进行加锁，只锁定过滤过的行，立即释放不需要的行（innodb_locks_unsafe_for_binlog是否对gap加锁，主键索引不受它影响还是会加写锁）。innodb在二级索引上使用共享锁（读锁），但是访问主键索引的时候需要排他锁（写锁），消除使用覆盖索引的可能性。延迟关联就是通过覆盖索引查询返回需要的主键，再根据这些主键关联原表需要的行，这个就是高效使用索引进行排序和分页。