说一说MySQL索引。
索引定义
为了提高检索数据库的数据的数据结构。
索引分类
根据数据结构分类
B+树索引，哈希索引
根据列数（B+数索引）
单列索引和多列索引
根据数据的位置（B+数索引）
聚集缩影和非聚集缩影
B+树索引
B+树 VS 平衡二叉树
B+数作为平衡搜索树结构，具有查询效率高，层次低的优点，常用作索引。对比其他平衡二叉搜索树结构，层次低的B+数作为索引，可以减少对磁盘的访问次数，从而提高效率。
B+树 VS B树
- B树的一个关键字只在一个节点，可以是叶子节点也可以不是。B+树的所有关键字都会出现在叶子节点中。
- B+树的叶子节点相互连接，并且按照关键字排好序了，方便顺序访问（范围查找）数据库数据。
- B+树的中间节点不保存数据，所有节点能存储更多元素，减少层次。
- B+树都要查到叶子节点，查询稳定。
聚集索引 vs 非聚集索引
- 聚集索引的叶子节点存储的就是数据库数据记录， 非聚集索引存储的是主键。
非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。 - 聚集索引只有一个，非聚集索引可以有多个
覆盖索引查询
多列索引中数据已经包含了要查询的字段，不需要再用主键查询，就可以得到需要的数据。
优化器选择选择索引
原则
- 在数据库里面，扫描行数是影响执行代价的因素之一。
- 扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。
- 当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
确定行数
- 使用采样统计
- 采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
- 而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。
索引失效
- or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效;
- 复合索引未用左列字段，即不是使用第一列索引，索引失效;
- like以%开头，当like前缀没有%，后缀有%时，索引有效;
- 需要类型转换
比如不加引号，在字符串列上查找 - where中索引列有运算，或者索引列使用了函数;;
- where中在索引字段上使用not，<>，!=。
（不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。key<>0 改为 key>0 or key<0。）不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法：key<>0 改为 key>0 or key<0。） - 如果mysql觉得全表扫描更快时（数据少）; 【优化器选择】
- 在索引列上使用 IS NULL 或 IS NOT NULL操作。
NULL不存在索引里面，所以IS NULL 不使用。如果在索引列上改条件为 Is Not Null ，因为索引列的所有非空值都存储在索引中，按道理也是可以走索引的。但是，为了解析查询语句，优化程序需要从索引中读取每一个值，在映射到表中索引返回的行。
没必要使用索引【不建议使用】
- 唯一性差;
- 频繁更新的字段不用（更新索引消耗）;
- where中不用的字段;
- 如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件，不建议使用索引
- 索引使用<>时，效果一般;
索引提示
主动告诉MySql是否使用索引，指定使用说明索引。
- use index：use index告诉MySql用列表中的其中一个索引去做本次查询
- ignore index：ignore index告诉mysql不要使用某些索引去做本次查询
- force index：force index和use index功能类似，都是告诉mySQL去使用某些索引
网友评论