B+树挑选索引(1)---mysql从入门到精通(二十二)

2023-01-30 00:00:00 索引 数据 字符串 基数 字符

上篇文章介绍了用索引列分组也可以提高效率,及其回表的代价,回表是二级索引+回表查询,如果回表数据量太庞大,mysql优化器就会采用全表扫描查询,而覆盖索引不会回表

如何挑选索引

在了解索引的强大后,我们如何使用索引呢?

在搜索、排序、分组的列创建索引

也就是在where语句后面的列,连接子句中的连接列,或者出现在order by 、group by子句中的列需要创建索引,而select查询列就不需要。比如

Select birthday,country from person_info where name = ‘’;这个sql语句里面birthday 和country不需要创建索引,而name则需要创建索引。

考虑列的基数

列的基数是指某一列中不重复的数据,比如name列有3,2,3,5,5,3,2,3,3,虽然数据有不少,但是他的不同数据只有三,所以基数是3。在数据记录中,列的基数越大,说明数据越分散,列的基数越小,说明数据越集中。假设列的基数是1,那当前列所有数据都一样,就无法排序,建立索引是没有意义的,如果建立二级索引,还会因为大量数据的回表操作而损耗性能,所以只有列基数越大的时候,才适合建立索引。

索引列的类型尽量小

我们在定义表结构有显示的指定列类型,我们以整数类型为例,有TINYINT,MEDIUMINT,INT,BIGINT这么几种,他们占用的内存依次递增,我们这里指的类型大小,就是建立该列的数据范围,尽量小化选择,这是因为:1)数据类型越小,查询速度越快(CPU层次的东西)。2)数据量越小,索引占用的存储空间越小,在一个数据页放下更多的记录,从而减少磁盘I/O带来的性能损耗,减少查询更多数据页带来的性能消耗。

这种不光对二级索引适用,对主键也同样适用,因为二级索引的叶子节点都是有主键的,主键的类型越小,占用的内存就越小。

索引字符串前缀

我们知道字符串由若干个字符组成的,我们用utf8组成的话需要占用1~3个字节,1)空间性:b+树需要把列的字符串全部存储起来,字符串越长,b+树叶子节点占用的空间越大。2)时间性:字符串越长,排序比较的时候,一个字符一个字符比较能耗费的时间越长。

我们之前说过,对于字符串的列,是一个字符一个字符比较排序的,所以索引设计者,考虑到空间和时间,只对字符串前几个指定字符进行存储和排序,这样也能相对定位到所在的位子,在用对应的主键id去聚簇索引的b+树查询所有值。写sql的时候我们可以这么写,创建name为100的字符串范围,但索引指定只指定name前10的个字符。

mysql> create table person_info2(
    -> id int not null auto_increment,
    -> name varchar(100) not null,
    -> birthday date not null,
    -> phone char(11) not null,
    -> country varchar(100) not null,
    -> primary key (id),
    -> key idx_name_birthday_phone (name(10),birthday,phone)
    -> );
Query OK, 0 rows affected (0.04 sec)

//也可以给长字符串列创建hash索引:
mysql> create index hash_name using hash on person_info2(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from person_info2;
+--------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| person_info2 |          0 | PRIMARY                 |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| person_info2 |          1 | idx_name_birthday_phone |            1 | name        | A         |           0 |       10 | NULL   |      | BTREE      |         |               |
| person_info2 |          1 | idx_name_birthday_phone |            2 | birthday    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| person_info2 |          1 | idx_name_birthday_phone |            3 | phone       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| person_info2 |          1 | hash_name               |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

相关文章