黄兵的个人博客

SQLAlchemy lazy='joined'理解

在SQLAlchemy的关系选项有lazy选项，主要作用是：指定如何加载相关记录。

选项有如下值：

select: 首次访问按需加载；

immediate: 源对象加载后就加载；

joined: 加载记录，但使用连接；

subquery: 立即加载记录，使用子查询；

noload: 永不加载；

dynamic: 不加载记录，但提供加载记录的查询。

才开始没有理解上面的意义，所有记录都使用joined，写法如下：

payment_success_id = db.relationship('PaymentSuccess', backref='users', lazy='joined')

之后使用查询语句如下：

User.query.filter_by(id=user_id).first().id

上面查询很简单，但是翻译成SQL语句如下：

SELECT users.id AS users_id, users.email AS users_email, users.`givenName` AS `users_givenName`, users.`familyName` AS `users_familyName`, users.`Timestamp` AS `users_Timestamp`, payment_success_1.id AS payment_success_1_id, payment_success_1.user_id AS payment_success_1_user_id, payment_success_1.pay_method_id AS payment_success_1_pay_method_id, payment_success_1.time_created AS payment_success_1_time_created, payment_success_1.time_updated AS payment_success_1_time_updated, payment_success_1.user_shopping_cart_id AS payment_success_1_user_shopping_cart_id, user_shopping_carts_1.id AS user_shopping_carts_1_id, user_shopping_carts_1.`gdNames` AS `user_shopping_carts_1_gdNames`, user_shopping_carts_1.`isBill` AS `user_shopping_carts_1_isBill`, user_shopping_carts_1.`isBillTime` AS `user_shopping_carts_1_isBillTime`, user_shopping_carts_1.expired AS user_shopping_carts_1_expired, user_shopping_carts_1.price AS user_shopping_carts_1_price, user_shopping_carts_1.user_id AS user_shopping_carts_1_user_id, user_shopping_carts_1.payment_method_id AS user_shopping_carts_1_payment_method_id, user_shopping_carts_1.time_created AS user_shopping_carts_1_time_created, user_shopping_carts_1.time_updated AS user_shopping_carts_1_time_updated, `pay_PayPal_pay_succeed_1`.id AS `pay_PayPal_pay_succeed_1_id`, `pay_PayPal_pay_succeed_1`.order_id AS `pay_PayPal_pay_succeed_1_order_id`, `pay_PayPal_pay_succeed_1`.status AS `pay_PayPal_pay_succeed_1_status`, `pay_PayPal_pay_succeed_1`.intent AS `pay_PayPal_pay_succeed_1_intent`, `pay_PayPal_pay_succeed_1`.time_created AS `pay_PayPal_pay_succeed_1_time_created`, `pay_PayPal_pay_succeed_1`.time_updated AS `pay_PayPal_pay_succeed_1_time_updated`, `pay_PayPal_pay_succeed_1`.user_id AS `pay_PayPal_pay_succeed_1_user_id`, `pay_PayPal_pay_succeed_1`.`PayPayPalPayer_id` AS `pay_PayPal_pay_succeed_1_PayPayPalPayer_id`, `pay_PayPal_pay_succeed_1`.`PayPayPalPayments_id` AS `pay_PayPal_pay_succeed_1_PayPayPalPayments_id`, `pay_PayPal_pay_succeed_1`.`PayPayPalPayee_id` AS `pay_PayPal_pay_succeed_1_PayPayPalPayee_id`, `pay_PayPal_onclick_1`.id AS `pay_PayPal_onclick_1_id`, `pay_PayPal_onclick_1`.time_created AS `pay_PayPal_onclick_1_time_created`, `pay_PayPal_onclick_1`.time_updated AS `pay_PayPal_onclick_1_time_updated`, `pay_PayPal_onclick_1`.user_id AS `pay_PayPal_onclick_1_user_id`, `pay_PayPal_cancel_1`.id AS `pay_PayPal_cancel_1_id`, `pay_PayPal_cancel_1`.order_id AS `pay_PayPal_cancel_1_order_id`, `pay_PayPal_cancel_1`.time_created AS `pay_PayPal_cancel_1_time_created`, `pay_PayPal_cancel_1`.time_updated AS `pay_PayPal_cancel_1_time_updated`, `pay_PayPal_cancel_1`.user_id AS `pay_PayPal_cancel_1_user_id`, `pay_PayPal_approve_1`.id AS `pay_PayPal_approve_1_id`, `pay_PayPal_approve_1`.order_id AS `pay_PayPal_approve_1_order_id`, `pay_PayPal_approve_1`.time_created AS `pay_PayPal_approve_1_time_created`, `pay_PayPal_approve_1`.user_id AS `pay_PayPal_approve_1_user_id`, `pay_PayPal_pay_succeed_2`.id AS `pay_PayPal_pay_succeed_2_id`, `pay_PayPal_pay_succeed_2`.order_id AS `pay_PayPal_pay_succeed_2_order_id`, `pay_PayPal_pay_succeed_2`.status AS `pay_PayPal_pay_succeed_2_status`, `pay_PayPal_pay_succeed_2`.intent AS `pay_PayPal_pay_succeed_2_intent`, `pay_PayPal_pay_succeed_2`.time_created AS `pay_PayPal_pay_succeed_2_time_created`, `pay_PayPal_pay_succeed_2`.time_updated AS `pay_PayPal_pay_succeed_2_time_updated`, `pay_PayPal_pay_succeed_2`.user_id AS `pay_PayPal_pay_succeed_2_user_id`, `pay_PayPal_pay_succeed_2`.`PayPayPalPayer_id` AS `pay_PayPal_pay_succeed_2_PayPayPalPayer_id`, `pay_PayPal_pay_succeed_2`.`PayPayPalPayments_id` AS `pay_PayPal_pay_succeed_2_PayPayPalPayments_id`, `pay_PayPal_pay_succeed_2`.`PayPayPalPayee_id` AS `pay_PayPal_pay_succeed_2_PayPayPalPayee_id`, `pay_PayPal_payer_1`.id AS `pay_PayPal_payer_1_id`, `pay_PayPal_payer_1`.country_code AS `pay_PayPal_payer_1_country_code`, `pay_PayPal_payer_1`.email_address AS `pay_PayPal_payer_1_email_address`, `pay_PayPal_payer_1`.given_name AS `pay_PayPal_payer_1_given_name`, `pay_PayPal_payer_1`.surname AS `pay_PayPal_payer_1_surname`, `pay_PayPal_payer_1`.payer_id AS `pay_PayPal_payer_1_payer_id`, `pay_PayPal_payer_1`.time_created AS `pay_PayPal_payer_1_time_created`, `pay_PayPal_payer_1`.time_updated AS `pay_PayPal_payer_1_time_updated`, `pay_PayPal_payer_1`.user_id AS `pay_PayPal_payer_1_user_id`, `pay_PayPal_pay_succeed_3`.id AS `pay_PayPal_pay_succeed_3_id`, `pay_PayPal_pay_succeed_3`.order_id AS `pay_PayPal_pay_succeed_3_order_id`, `pay_PayPal_pay_succeed_3`.status AS `pay_PayPal_pay_succeed_3_status`, `pay_PayPal_pay_succeed_3`.intent AS `pay_PayPal_pay_succeed_3_intent`, `pay_PayPal_pay_succeed_3`.time_created AS `pay_PayPal_pay_succeed_3_time_created`, `pay_PayPal_pay_succeed_3`.time_updated AS `pay_PayPal_pay_succeed_3_time_updated`, `pay_PayPal_pay_succeed_3`.user_id AS `pay_PayPal_pay_succeed_3_user_id`, `pay_PayPal_pay_succeed_3`.`PayPayPalPayer_id` AS `pay_PayPal_pay_succeed_3_PayPayPalPayer_id`, `pay_PayPal_pay_succeed_3`.`PayPayPalPayments_id` AS `pay_PayPal_pay_succeed_3_PayPayPalPayments_id`, `pay_PayPal_pay_succeed_3`.`PayPayPalPayee_id` AS `pay_PayPal_pay_succeed_3_PayPayPalPayee_id`, `pay_PayPal_payments_1`.id AS `pay_PayPal_payments_1_id`, `pay_PayPal_payments_1`.create_time AS `pay_PayPal_payments_1_create_time`, `pay_PayPal_payments_1`.status AS `pay_PayPal_payments_1_status`, `pay_PayPal_payments_1`.update_time AS `pay_PayPal_payments_1_update_time`, `pay_PayPal_payments_1`.payments_amount_value AS `pay_PayPal_payments_1_payments_amount_value`, `pay_PayPal_payments_1`.payments_amount_currency_code AS `pay_PayPal_payments_1_payments_amount_currency_code`, `pay_PayPal_payments_1`.user_id AS `pay_PayPal_payments_1_user_id`, `pay_PayPal_pay_succeed_4`.id AS `pay_PayPal_pay_succeed_4_id`, `pay_PayPal_pay_succeed_4`.order_id AS `pay_PayPal_pay_succeed_4_order_id`, `pay_PayPal_pay_succeed_4`.status AS `pay_PayPal_pay_succeed_4_status`, `pay_PayPal_pay_succeed_4`.intent AS `pay_PayPal_pay_succeed_4_intent`, `pay_PayPal_pay_succeed_4`.time_created AS `pay_PayPal_pay_succeed_4_time_created`, `pay_PayPal_pay_succeed_4`.time_updated AS `pay_PayPal_pay_succeed_4_time_updated`, `pay_PayPal_pay_succeed_4`.user_id AS `pay_PayPal_pay_succeed_4_user_id`, `pay_PayPal_pay_succeed_4`.`PayPayPalPayer_id` AS `pay_PayPal_pay_succeed_4_PayPayPalPayer_id`, `pay_PayPal_pay_succeed_4`.`PayPayPalPayments_id` AS `pay_PayPal_pay_succeed_4_PayPayPalPayments_id`, `pay_PayPal_pay_succeed_4`.`PayPayPalPayee_id` AS `pay_PayPal_pay_succeed_4_PayPayPalPayee_id`, `pay_PayPal_payee_1`.id AS `pay_PayPal_payee_1_id`, `pay_PayPal_payee_1`.email_address AS `pay_PayPal_payee_1_email_address`, `pay_PayPal_payee_1`.merchant_id AS `pay_PayPal_payee_1_merchant_id`, `pay_PayPal_payee_1`.time_created AS `pay_PayPal_payee_1_time_created`, `pay_PayPal_payee_1`.time_updated AS `pay_PayPal_payee_1_time_updated`, `pay_PayPal_payee_1`.user_id AS `pay_PayPal_payee_1_user_id`, payment_success_2.id AS payment_success_2_id, payment_success_2.user_id AS payment_success_2_user_id, payment_success_2.pay_method_id AS payment_success_2_pay_method_id, payment_success_2.time_created AS payment_success_2_time_created, payment_success_2.time_updated AS payment_success_2_time_updated, payment_success_2.user_shopping_cart_id AS payment_success_2_user_shopping_cart_id
FROM users
  LEFT OUTER JOIN user_shopping_carts AS user_shopping_carts_1 ON users.id = user_shopping_carts_1.user_id
  LEFT OUTER JOIN payment_success AS payment_success_1 ON user_shopping_carts_1.id = payment_success_1.user_shopping_cart_id
  LEFT OUTER JOIN `pay_PayPal_pay_succeed` AS `pay_PayPal_pay_succeed_1` ON users.id = `pay_PayPal_pay_succeed_1`.user_id
  LEFT OUTER JOIN `pay_PayPal_onclick` AS `pay_PayPal_onclick_1` ON users.id = `pay_PayPal_onclick_1`.user_id
  LEFT OUTER JOIN `pay_PayPal_cancel` AS `pay_PayPal_cancel_1` ON users.id = `pay_PayPal_cancel_1`.user_id
  LEFT OUTER JOIN `pay_PayPal_approve` AS `pay_PayPal_approve_1` ON users.id = `pay_PayPal_approve_1`.user_id
  LEFT OUTER JOIN `pay_PayPal_payer` AS `pay_PayPal_payer_1` ON users.id = `pay_PayPal_payer_1`.user_id
  LEFT OUTER JOIN `pay_PayPal_pay_succeed` AS `pay_PayPal_pay_succeed_2` ON `pay_PayPal_payer_1`.id = `pay_PayPal_pay_succeed_2`.`PayPayPalPayer_id`
  LEFT OUTER JOIN `pay_PayPal_payments` AS `pay_PayPal_payments_1` ON users.id = `pay_PayPal_payments_1`.user_id
  LEFT OUTER JOIN `pay_PayPal_pay_succeed` AS `pay_PayPal_pay_succeed_3` ON `pay_PayPal_payments_1`.id = `pay_PayPal_pay_succeed_3`.`PayPayPalPayments_id`
  LEFT OUTER JOIN `pay_PayPal_payee` AS `pay_PayPal_payee_1` ON users.id = `pay_PayPal_payee_1`.user_id
  LEFT OUTER JOIN `pay_PayPal_pay_succeed` AS `pay_PayPal_pay_succeed_4` ON `pay_PayPal_payee_1`.id = `pay_PayPal_pay_succeed_4`.`PayPayPalPayee_id`
  LEFT OUTER JOIN payment_success AS payment_success_2 ON users.id = payment_success_2.user_id
WHERE users.id = %s;

简直是吓死人，链接了这么多表，难怪这个查询如此缓慢。使用joined即使没有用到需要查询的表，也使用了联结，导致性能严重下降。

赶快修改加载的额方式，修改后代码如下：

payment_success_id = db.relationship('PaymentSuccess', backref='users', lazy='select')

之后再次查询，翻译成SQL代码如下：

SELECT users.id AS users_id, users.email AS users_email, users.`givenName` AS `users_givenName`, users.`familyName` AS `users_familyName`, users.`Timestamp` AS `users_Timestamp` 
FROM users 
WHERE users.id = %s

这个正常多了。

看样子要多通过项目理解上面的意思，才能是程序的性能达到最佳。

黄兵个人博客原创。

转载请注明出处：黄兵个人博客 - SQLAlchemy lazy='joined'理解