第五章

2020-10-29 本文已影响0人 frankie_cheung

mysql b+树索引基础

作者在索引基础讲了一些废话，关于索引的基础，看下图足以。

image.png

b+树索引作者列举的一些信息

示例表：

Create Table: CREATE TABLE `people` (
  `last_name` varchar(50) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `dob` date NOT NULL,
  KEY `last_name` (`last_name`,`first_name`,`dob`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

mysql> select * from people;
+-----------+------------+------------+
| last_name | first_name | dob        |
+-----------+------------+------------+
| bob       | kk         | 1997-12-24 |
| frankie   | cheung     | 1998-12-24 |
| hhhaa     | erkk       | 1996-12-24 |
+-----------+------------+------------+
3 rows in set (0.00 sec)

以下均为可以使用索引的案例

全值匹配:按照索引的顺序全部都使用索引

mysql> explain select * from people where last_name='bob' and first_name='kk' and dob='1997-12-24';
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | people | NULL       | ref  | last_name     | last_name | 407     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

最左匹配

mysql> explain select * from people where last_name='bob';
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | people | NULL       | ref  | last_name     | last_name | 202     | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

匹配列前缀

mysql> explain select * from people where last_name like '%b';
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | people | NULL       | index | NULL          | last_name | 407     | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

匹配范围值

mysql> explain select * from people where last_name >='bob' and last_name <='frankie' ;
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | people | NULL       | range | last_name     | last_name | 202     | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

精确匹配某一列且模糊匹配领一列

mysql> explain select * from people where last_name ='bob' and first_name like '%k' ;
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | people | NULL       | ref  | last_name     | last_name | 202     | const |    1 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

覆盖索引:注意Extra中的Using index

mysql> explain select last_name from people where last_name ='bob' ;
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | people | NULL       | ref  | last_name     | last_name | 202     | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

因为索引是有序的，所以假如你可以使用上述方式，则那你也可以进行排序

b+树索引的限制

不使用最左匹配原则
如下方式其实是走索引的

mysql> explain select last_name from people where first_name ='kk' ;
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | people | NULL       | index | NULL          | last_name | 407     | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

其他索引

作者又开始介绍了哈希索引，空间数据索引R-TREE，全文索引，因为基本不使用，所以直接略过

索引的优点

减少扫描量
避免排序和临时表
随机IO变顺序IO

作者在此介绍了一种索引评价规则：三星系统
1.索引把相关信息放到一起。则一星，这里的放到一起我个人理解为多列索引组合的
2.索引的数据顺序和查询顺序一直。则二星这里就是要按照顺序where条件
3.覆盖索引则三星

高性能索引策略

独立的列
其实就是不要隐式转换，不要在索引列使用函数，例如date_format 不用表达式不要进行字段转换。
字段的前缀做索引
例如有个字段是email 其实后缀差不多，所以索引创建为 index2(email(6) 对于每个记录都是只取前 6 个字节。
索引选择性
索引的选择性指的是不重复的索引值和总count的比例
索引值=distinc(col)/count()
该值处于1/count()和1之间，该值越高则，查询效率越高。
唯一索引(主键索引)该值为1，性能最好
为什么如此呢？因为一个字段不同的值约多，索引就更加高效的排除一些无用数据。
多列索引
不要在表中查询的每个字段都创建一个单独的索引，非常浪费磁盘空间，建议创建多列索引。上述也试验过多列索引，where 只使用某个列依然可以使用到索引。
将选择性最高的字段放在索引的最前面
假如创建的是组合索引，那么那些字段在前，那些字段在后这肯定会影响查询效率，本书中讲的是选择性最高的放前面。
聚簇索引
其实聚簇索引相当于主键索引，但是聚簇索引要比主键索引大，因为规则如下：
1.假如你创建的表有主键索引，则聚簇索引就是主键索引。
2.假如你没有主键索引，则按照你创建索引的顺序，找到第一个unique索引，来作为聚簇索引
3.假如上述都没有，则MySQL会自己产生一个聚簇索引GEN_CLUST_INDEX，该索引以行id为索引列,就是大家平时听说的，假如没主键，依然会创建一个虚拟主键的过程。
聚簇索引在同一个表有且只有一个。
reference：https://www.mysqltutorial.org/mysql-index/mysql-clustered-index/
覆盖索引
想象一下，假如你在翻看一本书的目录，只需要查看章节的名称，你肯定不需要到章节对应的页数查看，因为章节名称在目录上就有。

第五章