mysql索引学习教程

在mysql 中，索引可以分为两种类型 hash索引和 btree索引。 什么情况下可以用到B树索引？ 1.全值匹配索引 比如： orderID=”123” 2.匹配最左前缀索引查询 比如：在userid 和 date字段上创建联合索引。 那么如果输入 userId作为条件，那么这个userid可以使用到索引，如果直接输入 date作为条件，那么将不能使用到索引。 3.匹配列前缀查询 比如： order_sn like ‘134%’ 这样可以使用到索引。 4.匹配范围值查询 createTime>’2015-01-09′ and createTime&lt;‘2015-01-10 MySQL索引是数据库管理系统中用来加速数据检索的关键技术。索引可以显著提升查询速度，减少数据扫描量，尤其是在大数据量的表中。本教程主要关注MySQL中的两种索引类型：哈希索引（Hash Index）和B树索引（BTree Index）。 **B树索引** 1. **全值匹配索引**：当查询条件是索引列的完整值时，如`orderID="123"`，B树索引能够快速定位到对应记录。 2. **匹配最左前缀索引**：联合索引`userId`和`date`，若查询条件为`userId`，索引可用；如果直接使用`date`，则索引不可用。这是因为B树索引遵循最左前缀原则。 3. **匹配列前缀查询**：如`order_sn LIKE '134%'`，索引可被利用。 4. **匹配范围值查询**：如`createTime > '2015-01-09' AND createTime < '2015-01-10'`，B树索引可以支持范围查找。 5. **精确匹配左前列并范围匹配另一列**：如`userId=1 AND createTime>'2016-9-18'`，这种情况也能有效利用索引。 6. **覆盖索引**：如果查询列完全包含在索引中，数据库可以直接从索引获取数据，无需访问数据文件，提高效率。 **B树索引的限制** 1. 查询不从最左列开始，索引无法使用。 2. 不能跳过中间列，如三列索引`日期，姓名，电话`，仅`日期`和`电话`不能利用索引。 3. `NOT IN`和`<>`操作无法使用索引。 4. 范围查询后，右侧所有列无法使用索引。 **哈希索引** 1. **特点**：哈希索引基于哈希表，适用于等值查询，不支持范围或部分查询。 2. **限制**：哈希冲突可能导致性能下降，且不支持排序和非精确匹配。此外，哈希索引需要两次读取，先读取哈希值，再读取实际数据。 3. **选择性**：哈希索引应在选择性较高的列上创建，避免选择性差的字段，如性别字段。 **为什么使用索引** 1. 减少数据扫描量，提高查询速度。 2. 帮助排序，避免创建临时表。 3. 改变I/O模式，从随机I/O变为顺序I/O。 **索引策略** 1. 避免在索引列上使用表达式或函数。 2. 注意索引列长度限制，InnoDB的索引列大小约为200个字符。 3. 考虑选择性和列大小创建前缀索引。 4. 联合索引的列顺序应考虑查询频率、选择性及列的大小。 在实际应用中，建立索引并非越多越好，过多的索引会影响写操作，也会增加查询优化器的负担。明智地选择索引，结合业务需求和查询模式，可以最大化数据库性能。