加快在MySQL中的插入 [英] Speed up insert in MySQL

问题描述

我有三个表InnoDB，这三个表有一些外键.

I have three tables InnoDB, those three have some foreign keys.

这是架构:

CREATE TABLE IF NOT EXISTS `tbl_member` (
  `id_member` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(80) COLLATE utf8_bin NOT NULL,
  `lastname` varchar(80) COLLATE utf8_bin NOT NULL,
  `username` varchar(40) COLLATE utf8_bin DEFAULT NULL,
  `password` varchar(64) COLLATE utf8_bin NOT NULL,
  `phone` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `email` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `status` enum('active','inactive') COLLATE utf8_bin NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id_member`),
  KEY `username` (`username`,`password`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=107 ;

-- --------------------------------------------------------

--
-- Table structure for table `tbl_notification`
--

CREATE TABLE IF NOT EXISTS `tbl_notification` (
  `id_notification` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Notification ID',
  `type` enum('notification','announcement') COLLATE utf8_bin NOT NULL DEFAULT 'notification',
  `title` varchar(255) COLLATE utf8_bin NOT NULL COMMENT 'Title',
  `notification` text COLLATE utf8_bin NOT NULL COMMENT 'Body',
  `datestart` int(13) NOT NULL COMMENT 'Date when the notification will start to be deployed',
  `dateend` int(13) NOT NULL,
  PRIMARY KEY (`id_notification`),
  KEY `datestart` (`datestart`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `tbl_notification_member`
--

CREATE TABLE IF NOT EXISTS `tbl_notification_member` (
  `id_notification_member` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `id_notification` int(10) unsigned NOT NULL COMMENT 'Notification ID',
  `id_member` int(10) unsigned NOT NULL COMMENT 'Member ID',
  `status` enum('read','unread') NOT NULL DEFAULT 'unread' COMMENT 'Defines if the notification was viewed already or not',
  `timestamp` int(15) NOT NULL COMMENT 'Time when the notification was saw',
  PRIMARY KEY (`id_notification_member`),
  KEY `id_notification` (`id_notification`),
  KEY `id_member` (`id_member`),
  KEY `timestamp` (`timestamp`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Constraints for table `tbl_notification_member`
--
ALTER TABLE `tbl_notification_member`
  ADD CONSTRAINT `tbl_notification_member_ibfk_2` FOREIGN KEY (`id_member`) REFERENCES `tbl_member` (`id_member`),
  ADD CONSTRAINT `tbl_notification_member_ibfk_3` FOREIGN KEY (`id_notification`) REFERENCES `tbl_notification` (`id_notification`) ON DELETE CASCADE;

因此，如您所见，如果我删除了一个通知，则通知和成员之间的关系将按级联删除，这很棒，但是问题是当我尝试插入数千行时.有什么办法可以像级联一样做到这一点吗?使用索引还是类似的东西? (例如，在我已删除的表中，我还有其他关系，因此您可以只为一个部分添加通知，并确定将接收该通知的用户数)

So as you can see if I delete one notification, the relation between the notifications and the members will be deleted on cascade, which is awesome, but the problem is when I tried to insert thousands of rows. Is there any way to do this in some way like cascade? Using indexes or something like that? (I've another relations in the tables I've remove, for example sections, so you can add notifications for only one section and delimit the number of users that will receive the notification)

推荐答案

您可以通过

还要检查您的应用程序是否可以在READ-COMMITED隔离模式下运行 –如果是–将其设置为默认值 transaction-isolation =已读.该选项具有一定的性能 好处，尤其是锁定5.0甚至更多 MySQL 5.1和行级复制.

Also check if your application can run in READ-COMMITED isolation mode – if it does – set it to be default as transaction-isolation=READ-COMMITTED. This option has some performance benefits, especially in locking in 5.0 and even more to come with MySQL 5.1 and row level replication.

还要检查此.

这篇关于加快在MySQL中的插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！