mysql - mysql sql qyery 300k 结果检索的性能缓慢
问题描述
我的数据库中有 300k 数据。通过 ajax 和 php,我正在从表中检索结果。当我尝试使用以下查询检索此数据时,它非常慢。
有没有其他方法可以编写这个查询,以便我很快得到结果?
SELECT e_user_id,
assembly_no,
polling_station_number,
polling_station_name_hindi,
polling_station_name_eng,
serial_number,
section_no,
section_name_hindi,
section_name_eng,
house_no,
e_first_name_hindi,
e_first_name_eng,
e_last_name_eng,
e_relation_type,
e_relative_first_name_hindi,
e_relative_last_name_hindi,
e_relative_first_name_eng,
e_relative_last_name_eng,
id_card_number,
gender,
age,
dob,
contact_number,
uid
FROM vw_absent_record
WHERE polling_station_number IN ( 1, 2, 3, 4,
5, 6, 7, 8,
9, 10, 11, 12,
13, 14, 15, 16,
17, 18, 19, 20,
21, 22, 23, 24,
25, 26, 27 )
AND assembly_no = 48
ORDER BY e_user_id ASC
这是索引查询和 e_voter_list 表
CREATE view vw_absent_record
AS
SELECT e_voter_list.e_user_id,
e_voter_list.assembly_no,
e_voter_list.polling_station_number,
e_voter_list.polling_station_name_hindi,
e_voter_list.polling_station_name_eng,
e_voter_list.serial_number,
e_voter_list.section_no,
e_voter_list.section_name_hindi,
e_voter_list.section_name_eng,
e_voter_list.house_no,
e_voter_list.e_first_name_hindi,
e_voter_list.e_first_name_eng,
e_voter_list.e_last_name_eng,
e_voter_list.e_relation_type,
e_voter_list.e_relative_first_name_hindi,
e_voter_list.e_relative_last_name_hindi,
e_voter_list.e_relative_first_name_eng,
e_voter_list.e_relative_last_name_eng,
e_voter_list.id_card_number,
e_voter_list.gender,
e_voter_list.age,
e_voter_list.dob,
e_voter_list.contact_number,
e_voter_list.uid
FROM e_voter_list
LEFT OUTER JOIN vw_attendance_record
ON
( e_voter_list.e_user_id = vw_attendance_record.e_user_id )
WHERE vw_attendance_record.e_user_id IS NULL
这是我的 e_voter_list 表
CREATE TABLE `e_voter_list` (
`e_user_id` bigint(11) NOT NULL,
`assembly_no` bigint(8) NOT NULL DEFAULT '0',
`polling_station_number` int(4) NOT NULL DEFAULT '0',
`polling_station_name_hindi` varchar(350) DEFAULT NULL,
`polling_station_name_eng` varchar(350) DEFAULT NULL,
`serial_number` int(4) NOT NULL DEFAULT '0',
`section_no` int(5) DEFAULT NULL,
`section_name_hindi` varchar(450) DEFAULT NULL,
`section_name_eng` varchar(450) DEFAULT NULL,
`house_no` varchar(150) DEFAULT NULL,
`e_first_name_hindi` varchar(150) DEFAULT NULL,
`e_last_name_hindi` varchar(150) DEFAULT NULL,
`e_first_name_eng` varchar(150) DEFAULT NULL,
`e_last_name_eng` varchar(150) DEFAULT NULL,
`e_relation_type` varchar(5) DEFAULT NULL,
`e_relative_first_name_hindi` varchar(150) DEFAULT NULL,
`e_relative_last_name_hindi` varchar(150) DEFAULT NULL,
`e_relative_first_name_eng` varchar(150) DEFAULT NULL,
`e_relative_last_name_eng` varchar(150) DEFAULT NULL,
`id_card_number` varchar(30) NOT NULL,
`gender` varchar(8) DEFAULT NULL,
`age` varchar(10) DEFAULT NULL,
`dob` varchar(30) DEFAULT NULL,
`contact_number` varchar(15) DEFAULT NULL,
`uid` varchar(25) DEFAULT NULL,
`status_type` varchar(20) DEFAULT NULL,
`year_of_birth` int(4) DEFAULT NULL,
`date_of_attendance` date DEFAULT NULL,
`attendance_status` int(2) NOT NULL DEFAULT '0',
`e_attendance_timestamp` datetime DEFAULT NULL,
`update_agent_id` int(7) DEFAULT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
解决方案
您是否在表中使用过索引?如果没有,请在列上实施索引,那么您的选择查询将很快。另外我会建议你通过使用限制和偏移量来获取记录,如下所示:
SELECT e_user_id,assembly_no,polling_station_number,polling_station_name_hindi,polling_station_name_eng,serial_number,section_no,section_name_hindi,section_name_eng,house_no,e_first_name_hindi,e_first_name_eng,e_last_name_eng,e_relation_type,e_relative_first_name_hindi,e_relative_last_name_hindi,e_relative_first_name_eng,e_relative_last_name_eng,id_card_number,gender,age,dob,contact_number,uid FROM vw_absent_record where polling_station_number IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27) and assembly_no=48 ORDER BY e_user_id ASC LIMIT 10,0
推荐阅读
- scala - Scala Spark 如何将列数组 [string] 转换为包含 JSON 数组的字符串?
- android - 如何使用 TensorFlow Lite 在设备上使用多个自定义模型?
- regex - 正则表达式,在字符串中查找包含字符串的字符串
- php - 如何在PHP中将多个数组合并为一个
- flutter - Flutter onChanged 和 onSaved 一起用于文本输入
- java - 在 Velocity 模板中将时间显示为东部时间
- javascript - 在 Laravel 5.1 控制器中的 Create 方法上从多个输入值编码和传递 JSON 对象
- python - 获取字符串及其子字符串的所有组合
- c# - DBF dBase - Where 子句日期运算符/操作数类型不匹配错误
- sql - 即使某些节点不存在,也将所有 XML 导入 SQL 表