索引命中规则b-tree index vs hash index

最新推荐文章于 2024-05-30 16:24:50 发布

jupiter_888

最新推荐文章于 2024-05-30 16:24:50 发布

阅读量253

点赞数

本文链接：https://blog.csdn.net/jupiter_888/article/details/103853145

版权

https://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. For example, the following SELECT statements use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

The following SELECT statements do not use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

The following WHERE clauses use indexes:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

/* index = 1 OR index = 2 */

... WHERE index=1 OR A=10 AND index=2

/* optimized like "index_part1='hello'" */

... WHERE index_part1='hello' AND index_part3=5

/* Can use index on index1 but not on index2 or index3 */

... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

These WHERE clauses do not use indexes:

/* index_part1 is not used */

... WHERE index_part2=1 AND index_part3=2

/*  Index is not used in both parts of the WHERE clause  */

... WHERE index=1 OR A=10

/* No index spans all rows  */

... WHERE index_part1=1 OR index_part2=10

有索引但是不走

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.) However, if such a query uses LIMIT to retrieve only some of the rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in the result.

有时MySQL不使用索引，即使索引是可用的。出现这种情况的一种情况是，优化器估计使用索引将需要MySQL访问表中很大一部分行。（在这种情况下，表扫描可能要快得多，因为它需要更少的seek。）但是，如果这样的查询只使用LIMIT来检索某些行，MySQL无论如何都会使用索引，因为它可以更快地找到返回结果中的几行。

全表扫描和锁的关系、和行锁的关系、表锁的关系

不同场景，不同隔离级别下的加锁行为都有所不同，例如在RC隔离级别下，不符合WHERE条件的扫描到的记录，会被立刻释放掉，但RR级别则会持续到事务结束。你可以通过GDB，断点函数lock_rec_lock来查看某条SQL如何执行加锁操作。

http://mysql.taobao.org/monthly/2016/01/01/

Hash Index Characteristics

Hash indexes have somewhat different characteristics from those just discussed:

They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values. Systems that rely on this type of single-value lookup are known as “key-value stores”; to use MySQL for such applications, use hash indexes wherever possible.

The optimizer cannot use a hash index to speed up ORDER BY operations. (This type of index cannot be used to search for the next entry in order.)

MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM or InnoDB table to a hash-indexed MEMORY table.

Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)

locking read

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

应用层面的乐观锁和悲观锁; MySQL 表锁和行锁。

https://www.souyunku.com/2018/07/30/mysql/

乐观锁
用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

举例

1、数据库表设计

三个字段，分别是id,value、version
select id,value,version from TABLE where id=#{id}

2、每次更新表中的value字段时，为了防止发生冲突，需要这样操作
update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version};

悲观锁
说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

注意：行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。