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

2023-01-30 00:00:00 索引 数据 排序 建立 主键

上篇文章介绍了条件列,排序,分组都可以建立索引,select查询不需要建立,长字符串建立二级索引可以用索引前缀建立或者建立hash索引,避免时间和空间的浪费。建立索引的时候,列的类型尽量小点。还要看当前列的基数,基数越小,所有数据都一样,都无法排序,大量数据需要回表查询,所以基数越大才适合建立所以。

索引列前缀对排序的影响

如果字符串太长,取前10个字符建立索引,但这时候也会导致当前索引不可以索引排序,因为索引排序是从字符串的个字符比较,相同则比较下一个字符,依次比较的,如果后面的无法比较则无法排序,只能放弃索引排序来用文件排序。

让索引以单独的形式存在

我们比较下面两个sql:1)where num /2 <4;2)where num /2 <4;

这两个sql查询的条件是一直的,但sql1中并没有用到sql,因为num值不是独立存在的,这样存储引擎会遍历所有数据,来查找到我们需要的值,这种情况下是使用不到b+树索引的。Sql2中因为独立存在,则会使用b+树索引查询。

所以让索引列独立存在,不要给他计算值,或者函数的形式,否则不会触发索引。


主键插入顺序

其实我们创建表给主键的时候,都会给个auto_increment,让我们表里的主键自增,但是为什么,估计很多人不知道?

因为我们如果不设置显性列索引,那我们叶子节点全部存储着全部数据(包含隐藏数据,transactionId,roll_proint回滚指针,rowId,及其大小槽点,还有变长字段长度列表,null值列表,头部信息列表)。如果主键不是自增的,而是乱插入,比如先插个100,然后在插入1,这样就会把100的主键放到1后面的页,保证100以下的主键全部在前面的页,这种重排序又称页分裂会很好性能。

所以我们主键依次递增,新增的数据只会比前面的大。创建sql的时候主键加上,id int unsigned not null auto_increment。

冗余和重复索引

CREATE TABLE person_info(
    id INT UNSIGNED 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),
    KEY idx_name (name(10))
);    

相关文章