mysql - 需要优化一个PHP框架的mysql查询

Question

我有一个通过 PHP 框架核心函数生成的查询。我无法控制更改查询。因此我需要在服务器端执行优化，即 mysql 以高效地执行此查询。我已经应用了一些索引，但它仍然需要大约 4-5 秒，理想情况下应该需要 1-1.5 秒。以下是查询:

(
  SELECT rr.rt_bids_aos_quotes_relaos_quotes_idb AS so_id,
    rr.sales_order_sequence sequence,
    so.*
  FROM rt_bids_aos_quotes_rel AS rr
  INNER JOIN aos_quotes AS so ON so.id = rr.rt_bids_aos_quotes_relaos_quotes_idb
  WHERE rr.deleted = 0
    AND rr.rt_bids_aos_quotes_relrt_bids_ida='490395-403600-b'
)
UNION
(
  SELECT ra.rt_bids_aos_quotes_altaos_quotes_idb AS so_id,
    '' AS sequence,
    so.*
  FROM rt_bids_aos_quotes_alternate AS ra
  INNER JOIN aos_quotes AS so ON so.id = ra.rt_bids_aos_quotes_altaos_quotes_idb
  WHERE ra.deleted = 0
    AND ra.rt_bids_aos_quotes_altrt_bids_ida='490395-403600-b'
)

以下是解释查询结果:Image_here

id  select_type table   type    possible_keys   key key_len ref rows    Extra   
1   PRIMARY rt_bids_aos_quotes_rel  const   idx_rt_bids_aos_quotes_relrt_bids_ida_deleted,rt_bids_aos_quotes_rel_alt,idx_rt_bids_aos_quotes_rel_rt_bids_aos_quotes_relaos_quotes_idb    idx_rt_bids_aos_quotes_relrt_bids_ida_deleted   113 const,const 1   NULL    
1   PRIMARY so  ALL NULL    NULL    NULL    NULL    631950  Using where 
2   UNION   NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables 
NULL    UNION RESULT    <union1,2>  ALL NULL    NULL    NULL    NULL    NULL    Using temporary 

显示建表结果:

CREATE TABLE `rt_bids_aos_quotes_rel` (
 `id` varchar(36) NOT NULL,
 `date_modified` datetime DEFAULT NULL,
 `deleted` tinyint(1) DEFAULT '0',
 `rt_bids_aos_quotes_relrt_bids_ida` varchar(36) DEFAULT NULL,
 `rt_bids_aos_quotes_relaos_quotes_idb` varchar(36) DEFAULT NULL,
 `sales_order_sequence` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `idx_rt_bids_aos_quotes_relrt_bids_ida_deleted` (`deleted`,`rt_bids_aos_quotes_relrt_bids_ida`),
 KEY `rt_bids_aos_quotes_rel_alt` (`rt_bids_aos_quotes_relrt_bids_ida`,`rt_bids_aos_quotes_relaos_quotes_idb`),
 KEY `idx_rt_bids_aos_quotes_rel_rt_bids_aos_quotes_relaos_quotes_idb` (`rt_bids_aos_quotes_relaos_quotes_idb`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `aos_quotes` (
 `id` char(36) NOT NULL,
 `name` varchar(255) DEFAULT NULL,
 `date_entered` datetime DEFAULT NULL,
 `date_modified` datetime DEFAULT NULL,
 `modified_user_id` char(36) DEFAULT NULL,
 `created_by` char(36) DEFAULT NULL,
 `description` text,
 `deleted` tinyint(1) DEFAULT '0',
 `assigned_user_id` char(36) DEFAULT NULL,
 `approval_issue` text,
 `billing_account_id` char(36) DEFAULT NULL,
 `billing_contact_id` char(36) DEFAULT NULL,
 `billing_address_street` varchar(150) DEFAULT NULL,
 `billing_address_city` varchar(100) DEFAULT NULL,
 `billing_address_state` varchar(100) DEFAULT NULL,
 `billing_address_postalcode` varchar(20) DEFAULT NULL,
 `billing_address_country` varchar(255) DEFAULT NULL,
 `shipping_address_street` varchar(150) DEFAULT NULL,
 `shipping_address_city` varchar(100) DEFAULT NULL,
 `shipping_address_state` varchar(100) DEFAULT NULL,
 `shipping_address_postalcode` varchar(20) DEFAULT NULL,
 `shipping_address_country` varchar(255) DEFAULT NULL,
 `expiration` date DEFAULT NULL,
 `number` int(11) NOT NULL,
 `opportunity_id` char(36) DEFAULT NULL,
 `template_ddown_c` text,
 `total_amt` decimal(26,6) DEFAULT NULL,
 `total_amt_usdollar` decimal(26,6) DEFAULT NULL,
 `subtotal_amount` decimal(26,6) DEFAULT NULL,
 `subtotal_amount_usdollar` decimal(26,6) DEFAULT NULL,
 `discount_amount` decimal(26,6) DEFAULT NULL,
 `discount_amount_usdollar` decimal(26,6) DEFAULT NULL,
 `tax_amount` decimal(26,6) DEFAULT NULL,
 `tax_amount_usdollar` decimal(26,6) DEFAULT NULL,
 `shipping_amount` decimal(26,6) DEFAULT NULL,
 `shipping_amount_usdollar` decimal(26,6) DEFAULT NULL,
 `shipping_tax` varchar(100) DEFAULT NULL,
 `shipping_tax_amt` decimal(26,6) DEFAULT NULL,
 `shipping_tax_amt_usdollar` decimal(26,6) DEFAULT NULL,
 `total_amount` decimal(26,6) DEFAULT NULL,
 `total_amount_usdollar` decimal(26,6) DEFAULT NULL,
 `currency_id` char(36) DEFAULT NULL,
 `stage` varchar(100) DEFAULT 'Draft',
 `term` varchar(100) DEFAULT NULL,
 `terms_c` text,
 `approval_status` varchar(100) DEFAULT NULL,
 `invoice_status` varchar(100) DEFAULT 'Not Invoiced',
 `subtotal_tax_amount` decimal(26,6) DEFAULT NULL,
 `subtotal_tax_amount_usdollar` decimal(26,6) DEFAULT NULL,
 `bid_id` char(36) DEFAULT NULL,
 `alt` varchar(255) DEFAULT NULL,
 `group_desc` longtext,
 `hold` tinyint(1) DEFAULT '0',
 `order_amount` decimal(26,2) DEFAULT NULL,
 `order_description` longtext,
 `status` varchar(100) DEFAULT NULL,
 `type` varchar(255) DEFAULT NULL,
 `contract_id` char(36) DEFAULT NULL,
 `customer_discount` decimal(26,2) DEFAULT NULL,
 `customer_markup` decimal(26,2) DEFAULT NULL,
 `markup_inv_type` decimal(26,2) DEFAULT NULL,
 `location_id` char(36) DEFAULT NULL,
 `active` varchar(100) DEFAULT 'Active',
 `city` varchar(255) DEFAULT NULL,
 `rt_jobs_id` char(36) DEFAULT NULL,
 `system_type` varchar(36) DEFAULT NULL,
 `com_address` varchar(255) DEFAULT NULL,
 `com_city` varchar(255) DEFAULT NULL,
 `com_mapsco` varchar(255) DEFAULT NULL,
 `com_state_zip` varchar(255) DEFAULT NULL,
 `job_type` varchar(100) DEFAULT NULL,
 `calc_labor` varchar(100) DEFAULT 'Item_Install_amt',
 `comission` decimal(10,4) DEFAULT '0.0000',
 `hourly_labor_rate` decimal(8,4) DEFAULT NULL,
 `labor_percentage_of_price` decimal(12,2) DEFAULT NULL,
 `overhead` decimal(12,4) DEFAULT '0.0000',
 `profit` decimal(12,4) DEFAULT '0.0000',
 `pricing_checkbox` tinyint(1) DEFAULT '0',
 `pkg_package_id` char(36) DEFAULT NULL,
 `dh_factor` decimal(6,2) DEFAULT '0.00',
 `addl_builder_price` decimal(12,4) DEFAULT '0.0000',
 `billing_notes` longtext,
 `billing_notes_home` longtext,
 `builder_percentage` decimal(12,4) DEFAULT '0.0000',
 `discount` decimal(12,4) DEFAULT '0.0000',
 `jobs_contact_homeowner_id` char(36) DEFAULT NULL,
 `mortage` varchar(100) DEFAULT 'mortage',
 `oh_percentage` decimal(12,2) DEFAULT '0.00',
 `origin` varchar(255) DEFAULT NULL,
 `package_items` decimal(10,2) DEFAULT NULL,
 `package_items_unit_left` decimal(10,2) DEFAULT '0.00',
 `selected_package_units` decimal(10,2) DEFAULT '0.00',
 `jobs_account_superintendent_id` char(36) DEFAULT NULL,
 `subdivision_selected_id` char(36) DEFAULT NULL,
 `subdivision_selected_name` varchar(255) DEFAULT NULL,
 `department` varchar(255) DEFAULT 'Dallas',
 `locked` tinyint(1) DEFAULT '0',
 `billed_amount` decimal(26,2) DEFAULT '0.00',
 `billed_percentage` decimal(26,2) DEFAULT '0.00',
 `retained_amount` decimal(26,2) DEFAULT '0.00',
 `selected_package_amount` decimal(26,2) DEFAULT '0.00',
 `builder_amount` decimal(26,2) DEFAULT '0.00',
 `home_owner_amount` decimal(26,2) DEFAULT '0.00',
 `builderContractAmount` decimal(26,2) DEFAULT '0.00',
 `homeOwnerContractAmount` decimal(26,2) DEFAULT '0.00',
 `ho_pct` decimal(26,6) DEFAULT '0.000000',
 `builder_pct` decimal(26,6) DEFAULT '0.000000',
 `labor_pct` decimal(26,6) DEFAULT '0.000000',
 `mortgage` tinyint(1) DEFAULT '0',
 `ho_order` tinyint(1) DEFAULT '0',
 `home_owner_sale_order` tinyint(1) DEFAULT '0',
 `directly_created_contract` tinyint(1) DEFAULT '0',
 `crew_manager_id` char(36) DEFAULT NULL,
 `estimate_date` date DEFAULT NULL,
 `complete` tinyint(1) DEFAULT '0',
 `complete_note` text,
 `plan_estimate_date` date DEFAULT NULL,
 `plan_manager_id` char(36) DEFAULT NULL,
 `lock_bid_id` char(36) DEFAULT NULL,
 `documents_id` text,
 `no_item_change` tinyint(1) DEFAULT '0',
 `tagged_at_yard` tinyint(1) DEFAULT '0',
 `soap_created_so` tinyint(1) DEFAULT '0',
 `soap_created_so_amount` decimal(10,2) DEFAULT '0.00',
 `subcon_vendor` varchar(255) DEFAULT NULL,
 `plan_complete` varchar(100) DEFAULT 'In Progress',
 `plan_required` tinyint(1) DEFAULT '0',
 `plan_important` varchar(255) DEFAULT '0',
 `confirm` varchar(100) DEFAULT 'Unconfirm',
 `designer_notes` text,
 `plan_due_date` date DEFAULT NULL,
 `plan_required_so` tinyint(1) DEFAULT '0',
 `plan_request` tinyint(1) DEFAULT '0',
 `material_hold_status` varchar(255) DEFAULT NULL,
 `wh_warehouse_id` char(36) DEFAULT NULL,
 `work_order_created` tinyint(1) DEFAULT '0',
 `maintenance_status` varchar(100) DEFAULT NULL,
 `classification_type` varchar(255) DEFAULT NULL,
 `pricing_type` varchar(255) DEFAULT NULL,
 `estimate_end_date` date DEFAULT NULL,
 `install_date` date DEFAULT NULL,
 `is_matched` tinyint(1) DEFAULT '0',
 `builder_discount` decimal(10,2) DEFAULT '0.00',
 `ho_discount` decimal(10,2) DEFAULT '0.00',
 `locate_required` tinyint(1) DEFAULT '0',
 `priority` varchar(100) DEFAULT NULL,
 `is_no_charged` tinyint(1) DEFAULT '0',
 `no_charge_reasons` varchar(255) DEFAULT NULL,
 `no_charge_users` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_aos_quotes_type` (`type`),
 KEY `idx_aos_quotes_rt_jobs_id` (`rt_jobs_id`),
 KEY `idx_aos_quotes_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `rt_bids_aos_quotes_alternate` (
 `id` varchar(36) NOT NULL,
 `date_modified` datetime DEFAULT NULL,
 `deleted` tinyint(1) DEFAULT '0',
 `rt_bids_aos_quotes_altrt_bids_ida` varchar(36) DEFAULT NULL,
 `rt_bids_aos_quotes_altaos_quotes_idb` varchar(36) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `idx_rt_bids_aos_quotes_altrt_bids_ida_deleted` (`deleted`,`rt_bids_aos_quotes_altrt_bids_ida`),
 KEY `rt_bids_aos_quotes_alt` (`rt_bids_aos_quotes_altrt_bids_ida`,`rt_bids_aos_quotes_altaos_quotes_idb`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

请告诉我如何改进它。

Answer 1

aos_quotes 的默认字符集是 latin1，但其他表的默认字符集是 utf8。当您基于比较具有不同排序规则的两个字符串进行 JOIN 时，它们无法使用索引，因此它们被迫进行表扫描。我毫不怀疑这就是减慢您的查询速度的原因。

当我按原样使用您的表时，我得到了 so 表的解释:

*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: so
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

当我将您的 aos_quotes 表转换为 utf8 时，我得到了 so 表的解释:

*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: so
   partitions: NULL
         type: const
possible_keys: PRIMARY,idx_aos_quotes_id
          key: PRIMARY
      key_len: 108
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

“type: PRIMARY”比“type: ALL”好得多。

因此您需要将您的aos_quotes 表转换为utf8。

参见 How do I change a MySQL table to UTF-8?