问题和现象

我就遇到了一个这样的情况。我有两个一亿行数据的表table_a和table_b，table_a表大约1.5亿行，table_b表大约1亿行数据。这是两个非常大的表了，为了加速查询速度，对很多列都创建了索引，table_a表除了主键索引之外，几乎每个列上还都创建了一个索引；table_b表也类似。

现在遇到的现象是一些大并发任务（1000+并发）的时候还是比较慢， show processlist 发现正在打开并运行的正是这两个表（达到100次之多）。

虽然这些语句也用到了索引，但是很明显还是因为这两个表拖慢了速度，所以我想索引上还能不能进一步优化呢？首先我做了一个数据统计，数据如下：

select * from information_schema.TABLES
where  table_name in('table_a','table_b')
可以得到结果：
table_name Data_length index_size
table_a 12GB 24GB
table_b 7GB 21GB
For InnoDB, Data_length is the approximate amount of memory allocated for the clustered index, in bytes. Specifically, it is the clustered index size, in pages, multiplied by the InnoDB page size.
For InnoDB, Index_length is the approximate amount of memory allocated for non-clustered indexes, in bytes. Specifically, it is the sum of non-clustered index sizes, in pages, multiplied by the InnoDB page size.
当我统计出这个数据的时候，发现非聚集索引存储空间几乎是clustered索引的存储空间的两三倍，我当时简直惊呆了！
为啥索引这么大呢？
下面首先会深入讲一下索引的基础和背景，然后慢慢地进一步分析这个问题的原因（也可以直接跳过基础）。
索引的基础
创建索引的方式有好几种语法，但是效果都一样。下面给出表table_a和table_b的创建，以及他们的索引的创建方式。
创建表和索引
创建表和主键索引、以及二级索引的SQL语句如下所示：
CREATE TABLE `table_a` (
  `id` bigint(64) NOT NULL AUTO_INCREMENT,
  `git_id` bigint(64) DEFAULT NULL,
  `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `col4` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `col5` int(11) NOT NULL DEFAULT '0' ,
  `unit_id` bigint(64) NOT NULL,
  `col7` int(1) NOT NULL DEFAULT '1' ,
  PRIMARY KEY (`id`),
  KEY `git_id` (`git_id`),
  KEY `index_name` (`name`),
  KEY `unit_id` (`unit_id`),
  KEY `col4` (`col4`),
  KEY `col7` (`col7`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8
这就是table_a表结构和索引创建的全部，
这里一共有6个索引，除了id是主键索引，其他5个都是其他列上面的索引。
id和git_id等类型都是bigint，因为设计的初衷是要支持到很大的数据量。对于整数的数字类型，MySQL 中主要有 int 和 bigint 类型。其中 int 占用 4 个字节，bigint 占用 8 个字节，这和 Java 中的 int 和 long 对应。如果使用无符号的 int 类型作为主键，那么主键的最大值为 2^32-1，即 4294967295，这个值不到 43 亿。虽然一张表的数据，我们不可能让其达到 43 亿条（太大会影响性能），但是对于频繁进行插入、删除的表来说，43 亿这个值是可以达到的。而如果使用无符号的 bigint 类型的话，主键的最大值可以达到 2^64-1，这个数足够大了，如果以每秒插入 100 万条数据计算的，58 万年以后才能达到最大值。所以 bigint 作为主键的数据类型，完全不用担心超过最大值的问题。
table_b类似：
CREATE TABLE `table_b` (
  `id` bigint(64) NOT NULL AUTO_INCREMENT,
  `col1` bigint(64) DEFAULT NULL,
  `col2` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `col3` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `col4` varchar(128) DEFAULT NULL,
  `col5` int(11) NOT NULL DEFAULT '0',
  `col6` tinyint(1) NOT NULL DEFAULT '0' ,
  `col7` bigint(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `col1` (`col1`),
  KEY `clo2` (`col2`),
  KEY `col3` (`col3`),
  KEY `col4` (`col4`),
  KEY `col5_7` (`col5`,`col7`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8
table_b也有6个索引
table_b有一个联合索引: col3_7 (col3,col7)
Btree
我们创建索引的时候没有制定索引类型，对于MYSQL来说，那么默认就是Btree，其底层的实现是数据结构B+树。
感兴趣的同学欢迎翻阅我的文章[Mysql索引查漏补缺]
简单的介绍完索引的知识了，下面来慢慢地进一步分析索引。如下是B-树分裂的过程(juejin.cn/post/709353…)
图上的每一个节点是是一个单位，mysql的索引页（非叶子结点）和数据页（页字节点）的大小是一样的，一页的大小默认为16KB。
这些索引的树结构有几层
表table_a的id是主键索引，而id类型是bigint，一个bigint数据类型存储占8B，B+树指针占4B。如果使用率为100%，那么16KB的一个page可以大约存储1333个id索引，往下层分裂出1333个page。也就是，第二层最多可以使用13331333个id，数据量大约是177万，第三层，最多可以往下分裂出13331333(177万)个page，第三层大约能存储23.6亿数据。
通过这样的分析，我们知道table_a的id主键索引有3层。因为数据量大概在1.5亿，两层的索引确实不够的，三层的索引远远足够。
我们来验证一下我们的分析是否正确吧。
SELECT
    table_name part,
    index_name,  sum(stat_value) pages,
    concat(round(sum(stat_value)/1024/1044,2),'M',' pages') * @@innodb_page_size size
    mysql.innodb_index_stats
WHERE
        table_name = 'table_a'
  AND stat_description LIKE 'Number of pages in the index'
group by  index_name, table_name;
table_a的索引统计信息：
主键索引：80万个非叶子page，需要12G容量
二级索引git_id:：29万个非叶子page,需要12G容量，里面有很多未完全利用的空间，也就是碎片。
二级索引unit_id： 43万个非叶子page,也是有不少的碎片。
其他索引类似
主键id和unit_id都是bigint类型，主键的一共超过一亿，但是unit_id才1000万，但是所占的page比起来多了两倍，这说明主键索引是自增的，自增的索引也非常紧凑，非常节省空间。而强制要求主键 id 是自增的，则是为了在数据插入的过程中，尽可能的避免索引树上页分裂的问题。
正因为索引很大，导致了增删改查的速度慢，也就导致了我文章开头提到的现象。
我意识到，我们使用数据的场景并不会有那么频繁的修改，如果我们使用那样的方式每天更新一次，也就意味着每天都需要1个亿的id,那么这张表42天后就会用完。那么到时候就不够用了。而业务增量的分析方式会使得这个表大约每天增长40万，分析也快，那么需要大约1亿天的时间才可能超出。
如果能选择增量，那么完全可以摒弃费时费力的全量处理方式。
int数据类型已经够用，所以bigint是可以优化为int的。
于是则将这两个表里的bigint全部转为int了，这样之后，可以很明显的发现数据和索引都会减小很多。数据整体大约减少了一半的存储空间，索引减少了三分之一以上。并且，增删改查操作都变快很多。
增删改查操作会将需要的索引先加载到Buffer Pool里面，索引更小也意味着更少的换入换出，系统的开销减少了很多！
可以使用如下命令查看Buffer Pool的使用情况：
show global status like 'Innodb_buffer_pool_pages_data'; 292637  
show global status like 'Innodb_buffer_pool_pages_total';306304  
        高级开发工程师@某互联网大厂
        86.4k