mysql索引

常见的索引模型：哈希表、有序数组和搜索树。

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的值即 key，就可以找到其对应的值即 Value。

哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

哈希表这种结构适用于只有等值查询的场景，有序数组在等值查询和范围查询场景中的性能就都非常优秀，只适用于静态存储引擎。二叉搜索树 搜索和更新的时间复杂度O(log(N))，使用N叉树可以减少磁盘访问。

索引维护：数据页的合并和分裂，降低性能，普通索引存放的是主键的值，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也小，所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。K-V模型的结构可以自定义主键。

重建索引和主键索引：

alter table T drop index k;
alter table T add index(k);

alter table T drop primary key;
alter table T add primary key(id);

alter table T engine=InnoDB

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

联合索引的最左前缀原则：第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

普通索引和唯一索引 

查询过程

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

更新过程

change buffer：当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

如果更新内容在磁盘上，普通索引更新可以使用change buffer，唯一索引不能。

mysql选错索引

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/
 

索引的区分度

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。

这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。

查看方法: show index from t;

重新统计索引信息: analyze table t; 

索引的选择：扫描行数，是否回表，是否排序

 

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。使用前缀索引就用不上覆盖索引对查询性能的优化了。

第一种方式是使用倒序存储。

第二种方式是使用 hash 字段。

测试磁盘io性能
innodb_io_capacity 这个参数了，它会告诉InnoDB 你的磁盘能力。这个值我建议你设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试，下面的语句是我用来测试磁盘随机读写的命令：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest