移动列中的全文索引会有帮助吗?移动列的内容始终是一个块字符串

我有一个表,其中有一列包含大约 4000 万条记录,其中包含手机号码。这是带有一些转储数据查询的表架构

-- phpMyAdmin SQL Dump
-- version 4.9.7
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Jun 08, 2022 at 12:19 PM
-- Server version: 5.7.38-log
-- PHP Version: 7.4.29
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
--
-- Database: `database_name`
--

-- --------------------------------------------------------
--
-- Table structure for table `profiles`
--
CREATE TABLE `profiles` (
  `profile_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `birth_date` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gender` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `profile_link` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `full_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employer` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `job` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `city` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `facebook_email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `marital_status` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Dumping data for table `profiles`
--
INSERT INTO `profiles` (`profile_id`, `mobile`, `birth_date`, `first_name`, `last_name`, `gender`, `profile_link`, `full_name`, `employer`, `job`, `city`, `facebook_email`, `marital_status`) VALUES
('100000000000529', '201226063866', NULL, 'Samer', 'Aly', 'male', 'https://www.facebook.com/samer.Hussein.aly', 'Samer Hussein Aly', NULL, NULL, 'Mansoura', 'samer.Hussein.aly@facebook.com', NULL),
('100000000004160', '201002299054', NULL, 'Sherif', 'Mansour', 'male', 'https://www.facebook.com/sherif.mansour.946', 'Sherif Mansour', NULL, NULL, NULL, 'sherif.mansour.946@facebook.com', 'Married'),
('100000000005840', '201000914451', NULL, 'Moniem', 'Allaban', 'male', 'https://www.facebook.com/moniemvilla', 'Moniem Ayman Allaban', 'Civil Engineers', NULL, 'Sohag', 'moniemvilla@facebook.com', 'Single'),
('100000000006051', '201009975151', NULL, 'Adnan', 'Tahawy', 'male', 'https://www.facebook.com/adnan.tahawy', 'Adnan El Tahawy', 'Allianz Egypt -Head Office', 'Bancasurance', NULL, 'adnan.tahawy@facebook.com', NULL),
('100000000007938', '201282553531', '11/21/1991', 'Alaa', 'Horira', 'male', 'https://www.facebook.com/alaa.logo', 'Alaa Abo Horira', 'شركة الدسوقي جروب لأمهات الدواجن', 'Poultry Production Engineer', 'Giza', 'alaa.logo@facebook.com', 'Single'),
('100000000010755', '201020222026', NULL, 'Ola', 'Abd El-Kader', 'female', 'https://www.facebook.com/ola.eldeeb', 'Ola Abd El-Kader', NULL, NULL, NULL, 'ola.eldeeb@facebook.com', NULL),
('100000000014493', '201001041238', '03/09', 'Sherif', 'Ibrahim', 'male', 'https://www.facebook.com/sherifi', 'Sherif Ibrahim', 'The Arab Contractors (Osman Ahmed Osman & Co)', 'Civil engineer', 'Cairo  Egypt', 'sherifi@facebook.com', NULL),

('100000000023706', '201061016412', NULL, 'Ahmed', 'Ramadan', 'male', 'https://www.facebook.com/ahmed.rando', 'Ahmed Ramadan', 'Dubai Islamic Bank', NULL, 'Ma`Adi  Al Qahirah  Egypt', 'ahmed.rando@facebook.com', NULL),
('100000000026495', '201009909238', '10/01/1993', 'Mohamed', 'Fathy', 'male', 'https://www.facebook.com/mido.strong.mohamed', 'Mohamed Fathy', NULL, NULL, 'Giza', 'mido.strong.mohamed@facebook.com', NULL),
('100000000028259', '201112752668', NULL, 'Saher', 'Galal', 'male', 'https://www.facebook.com/SaherrGalall', 'Saher Galal', 'Nothing', NULL, 'Maddi', 'SaherrGalall@facebook.com', NULL),
('100000000034989', '201225550201', NULL, 'Hytham', 'Omar', 'male', 'https://www.facebook.com/hytham.omar', 'Hytham Omar', NULL, NULL, NULL, 'hytham.omar@facebook.com', 'Married'),
('100000000036425', '201120615833', NULL, 'Youssef', 'Ahmed', 'male', 'https://www.facebook.com/hamada.mody.58910', 'Youssef Ahmed', NULL, NULL, NULL, 'hamada.mody.58910@facebook.com', NULL),
('100000000046485', '201111755011', NULL, 'Dalia', 'El-Shamy', 'female', 'https://www.facebook.com/AMROKAMAL99', 'Dalia El-Shamy', 'Civil Engineering', NULL, 'Helwan', 'AMROKAMAL99@facebook.com', 'Married'),
('100000000048402', '201099407065', NULL, 'Waseem', 'Wafeek', 'male', 'https://www.facebook.com/waseem.wafeek', 'Waseem Wafeek', NULL, NULL, NULL, 'waseem.wafeek@facebook.com', NULL),
('100000000051463', '201002633598', NULL, 'Sabry', 'Nasrala', 'male', 'https://www.facebook.com/sabry.nasrala', 'Sabry Nasrala', 'Diet House', 'Restaurant manager', 'Cairo  Egypt', 'sabry.nasrala@facebook.com', 'Single'),
('100000000053198', '201282832447', '11/14/1968', 'Hatem', 'Moamen', 'male', 'https://www.facebook.com/hatem.moamen', 'Hatem Moamen', 'AOI', 'محاسب', NULL, 'hatem.moamen@facebook.com', 'Married'),
('100000000053761', '201282591776', NULL, 'Bisho', 'Ehab', NULL, 'https://www.facebook.com/bisho.ehab.7', 'Bisho Ehab', NULL, NULL, 'Cairo  Egypt', 'bisho.ehab.7@facebook.com', NULL),
('100000000053907', '201111621168', NULL, 'Mamduh', 'Zakaria', 'male', 'https://www.facebook.com/abofama', 'Mamduh Mohamed Zakaria', NULL, NULL, NULL, 'abofama@facebook.com', NULL),
('100000000054846', '201122930402', NULL, 'Manar', 'Boulad', 'male', 'https://www.facebook.com/manar.boulad.7', 'Manar Boulad', 'Damascus  Syria', NULL, 'Damascus  Syria', 'manar.boulad.7@facebook.com', NULL),
('100000000056063', '201277682638', NULL, 'Kholoud', 'Osama', 'female', 'https://www.facebook.com/kholoud.osama.31', 'Kholoud Osama', NULL, NULL, NULL, 'kholoud.osama.31@facebook.com', NULL),
('100000000058700', '201272298772', NULL, 'Magdi', 'Talat', 'male', 'https://www.facebook.com/magdi.talat', 'Magdi Talat', NULL, NULL, 'Minya  Egypt', 'magdi.talat@facebook.com', 'Married'),
('100000000059048', '201288847174', NULL, 'د. محمد', 'أبوحسين', NULL, 'https://www.facebook.com/100000000059048', 'د. محمد أبوحسين', 'الجهاز المركزي للتنظيم والإدارة', NULL, 'El-Mahmudiya  Al Buhayrah  Egypt', '100000000059048@facebook.com', NULL),
('100000000059243', '201004700599', NULL, 'Ashraf', 'Fathy', 'male', 'https://www.facebook.com/ossoris', 'Ashraf Fathy', NULL, NULL, NULL, 'ossoris@facebook.com', NULL),
('100000000061207', '201002166661', NULL, 'Aisha', 'Abd-Elkader', NULL, 'https://www.facebook.com/toka.mesho', 'Aisha Abd-Elkader', NULL, NULL, NULL, 'toka.mesho@facebook.com', NULL),

('100000000061245', '201119808229', NULL, 'Sara', 'Mohamed', 'male', 'https://www.facebook.com/ahmed.zohdi.3', 'Sara Mohamed', 'farrahtel', NULL, NULL, 'ahmed.zohdi.3@facebook.com', NULL);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `profiles`
--
ALTER TABLE `profiles`
  ADD PRIMARY KEY (`profile_id`),
  ADD KEY `profiles_mobile_index` (`mobile`);
COMMIT;

我想在表中搜索相似的记录

SELECT profiles.*, coalesce(profiles.mobile, search.mobile) as mobile

图片[1]-移动列中的全文索引会有帮助吗?移动列的内容始终是一个块字符串-唐朝资源网

from ( select '2011123123' as mobile union all select '20115434345' union all select '20143534535' union all select '1152840065' union all select '+201152840066' union all select '01152840067' union all select '201152840069' ) search left join profiles on profiles.mobile like concat('%', search.mobile, '%')

我以这种方式使用此查询,因为 – 首先我不需要丢失搜索结果中未找到的数字,我希望在结果中返回未找到的数字,但在除移动设备之外的所有其他列中为空,那就是为什么我使用左连接和在运行时构建的搜索表 一列是我正在搜索的手机号码 – 第二列我在 join 子句中使用了 like 运算符,因为某些数字可能包含国家代码,例如 20 ,而其他数字可能包含+20,而其他一些数字可能根本不包含国家/地区代码,例如直接以011开头,这就是为什么我需要在like运算符之前和之后两个通配符

我的问题是如何优化数据库以执行此查询?在列中移动全文索引会有帮助吗?知道move栏的内容始终是块字符串,我的意思是“0313131231”,不是一段可能包含一些单词组成的字符串(不知道全文索引是基于字符还是基于单词)谢谢你提前,是的对不起我的英语

© 版权声明
THE END
喜欢就支持一下吧
点赞269 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片