MySQL数据库SQL语句优化原理专题（二）

之前公司接火车票下订单的项目，由于购买火车票的人数众多，所以订单记录也很多，单表有100多万，订单里会存放购票人的身份证号，由于监管要求，身份证属于敏感信息，不能明文存储，需要加密存储，用公司加密算法加密之后，身份证号就变成了128位的字符长度，而一些场景有需要使用身份证号去查询。

所以怎么写SQL和设计索引，既能减少数据库资源消耗，又能保证高效的查询性能呢？

下面创建一张示例表t_test4

[root@localhost]17:03:22[testdb]showcreatetablet_test4\G;***************************1.row***************************Table:t_test4CreateTable:CREATETABLE`t_test4`(`order_id`int(10)unsignedNOTNULLAUTO_INCREMENT,`id_sha2`varchar(512)COLLATEutf8mb4_unicode_ciDEFAULTNULL,PRIMARYKEY(`order_id`))ENGINE=InnoDBAUTO_INCREMENT=49997686DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ciMAX_ROWS=10000001rowinset(0.00sec)ERROR:Noqueryspecified

模拟创建1万条记录，取其中10条记录示例，其中id_sha2就是模拟的身份证号加密字段

[root@localhost]16:58:57[testdb]selectorder_id,id_sha2fromt_test4limit10;+----------+----------------------------------------------------------------------------------------------------------------------------------+|order_id|id_sha2|+----------+----------------------------------------------------------------------------------------------------------------------------------+|8217|375f902e071d1ac817b3aaa847b13335cc560761b168ec427ec5bf952b3029a96710c9111d8cd35b7f243d5f52302a4ba9ddded66c93b84a9e451ed62335eff8||13214|499415d203ab4bb5644c263645c213f2f93f20194e28ad5acde8de6ffd734853f4bfcd27222ab1957dbe92ee7b8487ca6fb02f3cde72af008a4f3369b51cc56f||27054|2c301044742d02dc4e1d82ebda451032dede921e0bc7ec08e2879b8406e32cb1155d86ac2900141b718dc818a3c2bfed974726fe34cc0dbc60cceb1facbe98ab||29352|127b2250a4823fa3b51d30b2d621f666e45c17c807bb1a909827aa277ec7ea6986c1ee75273d389748dd5e239595f3e45efeef2754aede5fd20e8fea6acc0bf2||30485|630f48ca90d93448e25c3ae4c838641ec9c45f49af191d992287ab6e28a7329dd3ce7afe239e5ed02a2aca4d976ca506d4b44c9c0d9f01107076c3015d40965d||40517|a5cd97274ac976271745990b5c7a9f05f806b15fdeca2a37431fdb84eb0868eb99ee2b0b43389c6c8ec5687d7c162300eebc14ecb7bcae17c502fd628e7de98b||41811|6698dbd0898ab61f78f7d7de3011248e7dd75aeef56c0e11c85779d9c62200773802cde1f7f95d19bac00c1d3251c3765be8b760cf11b8fae886dd90a908783c||49089|2b8a793e174669f41f9e7c56a01dc6a708b0071dbad3da5ca4be9ffd9ce577ba44bed92c7db86f7941fb14cb828e66a7c6315500decb7474e02e7637dcad6b99||56027|a237ab3a1398e3785f6dad02800055d4832e6cff4db7d4942a332961a8026fbf3f24eb0e80e458bad594d7e5c0efae8492eb1b0d3ba50a3f2a0aebaad958da73||56735|53bb95d87c8241b4d707a59b14b00fb14e3162ea91ada3e6cb17fc6339e85a76e870b56e8acf0c2220b5720971359916b2b1977c9c5aa1c1e43d201665448629|+----------+----------------------------------------------------------------------------------------------------------------------------------+10rowsinset(0.00sec)

业务查询SQL

selectorder_id,id_sha2fromt_test4whereid_sha2='a5cd97274ac976271745990b5c7a9f05f806b15fdeca2a37431fdb84eb0868eb99ee2b0b43389c6c8ec5687d7c162300eebc14ecb7bcae17c502fd628e7de98b';

看到这里，大家发现问题了，如果id_sha2上没有索引，从一张100W记录的业务表查出某个身份证号购买的所有火车票，简直要人命，可是直接id_sha2列上创建索引，又不合适。

原因有3个：
第一：Mysql数据库默认页大小是16K，那么一个页大约可以存放16*1024/128=128记录，而且很快因为页空间占满，导致页分裂，由于页分裂过快，影响性能
第二：占用空间比短长度列索引空间大
第三：数据库查询时，加载到内存占用更多的内存空间

关键问题是索引列太宽，优化思路就是缩小列宽度，在中是不支持列上创建函数索引，那我们有2种方案

方案1：在t_test4表上，创建一个新列（id_sha2_idx），列的长度可以设置成身份证号长度（14位），每次写入id_sha2列时，同时往id_sha2_idx写入substr(id_sha2,1,13)的值，并在id_sha2_idx列上创建索引

方案2：在t_test4表上，创建一个虚拟列（id_sha2_idx），列的长度同样设置成身份证号长度（14位），并在id_sha2_idx列上创建索引。

这2个方案有什么不一样的地方呢，方案一添加的列是需要在硬盘上写入实际数据的，而方案二创建的是虚拟列，不需要存储数据，而且创建虚拟列和删除虚拟列都不会改变表的存储结构，所以创建和删除很方便，快捷。

在这里，我选取方案二来给大家演示优化后的效果。

创建虚拟列，并创建索引

[root@localhost]17:30:00[testdb]altertablet_test4addid_sha2_idxvarchar(13)generatedalwaysas(substr(id_sha2,1,13));QueryOK,0rowsaffected(0.45sec)Records:0Duplicates:0Warnings:0[root@localhost]17:30:01[testdb]altertablet_test4addindexidx_t_test4_id_sha2_idx(id_sha2_idx);QueryOK,0rowsaffected(0.41sec)Records:0Duplicates:0Warnings:0[root@localhost]17:30:06[testdb]showcreatetablet_test4\G;***************************1.row***************************Table:t_test4CreateTable:CREATETABLE`t_test4`(`order_id`int(10)unsignedNOTNULLAUTO_INCREMENT,`id_sha2`varchar(512)COLLATEutf8mb4_unicode_ciDEFAULTNULL,`id_sha2_idx`varchar(13)COLLATEutf8mb4_unicode_ciGENERATEDALWAYSAS(substr(`id_sha2`,1,13))VIRTUAL,PRIMARYKEY(`order_id`),KEY`idx_t_test4_id_sha2_idx`(`id_sha2_idx`))ENGINE=InnoDBAUTO_INCREMENT=49997686DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ciMAX_ROWS=10000001rowinset(0.00sec)ERROR:Noqueryspecified

优化之后的业务SQL语句

[root@localhost]17:31:43[testdb]selectorder_id,id_sha2,id_sha2_idxfromt_test4whereid_sha2='499415d203ab4bb5644c263645c213f2f93f20194e28ad5acde8de6ffd734853f4bfcd27222ab1957dbe92ee7b8487ca6fb02f3cde72af008a4f3369b51cc56f'andid_sha2_idx='499415d203ab4';+----------+----------------------------------------------------------------------------------------------------------------------------------+---------------+|order_id|id_sha2|id_sha2_idx|+----------+----------------------------------------------------------------------------------------------------------------------------------+---------------+|13214|499415d203ab4bb5644c263645c213f2f93f20194e28ad5acde8de6ffd734853f4bfcd27222ab1957dbe92ee7b8487ca6fb02f3cde72af008a4f3369b51cc56f|499415d203ab4|+----------+----------------------------------------------------------------------------------------------------------------------------------+---------------+1rowinset(0.01sec)[root@localhost]17:32:28[testdb]explainselectorder_id,id_sha2,id_sha2_idxfromt_test4whereid_sha2='499415d203ab4bb5644c263645c213f2f93f20194e28ad5acde8de6ffd734853f4bfcd27222ab1957dbe92ee7b8487ca6fb02f3cde72af008a4f3369b51cc56f'andid_sha2_idx='499415d203ab4';+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+|1|SIMPLE|t_test4|NULL|ref|idx_t_test4_id_sha2_idx|idx_t_test4_id_sha2_idx|55|const|1|10.00|Usingwhere|+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+1rowinset,1warning(0.00sec)

可以看到，现在已经非常优雅的解决了，索引占用空大和慢的问题。

1.如果您喜欢这篇文章，请点赞+转发。