mysql 创建索引

http://technet.microsoft.com/zh-cn/library/ms191432%28v=sql.105%29.aspx

CREATE VIEW `v_standingbook` AS
  select `t1`.`ID` AS `ID`,`t1`.`ID` AS `SB_ID`,`t1`.`RELATION_ID` AS `RELATION_ID`,`t1`.`CHANNEL` AS `CHANNEL`,`t1`.`SORT` AS `SORT`,`t1`.`MEMBER_ID` AS `MEMBER_ID`,
  (select `t2`.`STUDENT_CODE` AS `STUDENT_CODE` from `erp_student` `t2` where (`t1`.`MEMBER_ID` = `t2`.`ID`)) AS `MEMBER_CODE`,
  (select `t2`.`STUDENT_NAME` AS `STUDENT_NAME` from `erp_student` `t2` where (`t1`.`MEMBER_ID` = `t2`.`ID`)) AS `MEMBER_NAME`,
  (select `t2`.`USERNAME` AS `USERNAME` from `erp_student` `t2` where (`t1`.`MEMBER_ID` = `t2`.`ID`)) AS `MEMBER_USERNAME`,
  (select `t3`.`CARD_CODE` AS `CARD_CODE` from `erp_card` `t3` where (`t1`.`ID` = `t3`.`STANDINGBOOK_ID`)) AS `CARD_CODE`,`t1`.`BUSI_TYPE` AS `BUSI_TYPE`,`t1`.`BUSI_AMOUNT` AS `BUSI_AMOUNT`,
  (select sum(`t4`.`PAYMENT_AMOUNT`) AS `SUM(T4.PAYMENT_AMOUNT)` from `erp_payment` `t4` where (`t1`.`ID` = `t4`.`STANDINGBOOK_ID`) group by `t1`.`ID`) AS `PAYMENT_AMOUNT`,
  (select sum(`t4`.`CHANGE_AMOUNT`) AS `SUM(T4.CHANGE_AMOUNT)` from `erp_payment` `t4` where (`t1`.`ID` = `t4`.`STANDINGBOOK_ID`) group by `t1`.`ID`) AS `CHANGE_AMOUNT`,
  (select sum(`t4`.`FEE`) AS `SUM(T4.FEE)` from `erp_payment` `t4` where (`t1`.`ID` = `t4`.`STANDINGBOOK_ID`) group by `t1`.`ID`) AS `PAYMENT_FEE_CALC`,
  (select sum(`t4`.`FEE_FINAL`) AS `SUM(T4.FEE_FINAL)` from `erp_payment` `t4` where (`t1`.`ID` = `t4`.`STANDINGBOOK_ID`) group by `t1`.`ID`) AS `PAYMENT_FEE_FINAL`,`t1`.`DELIVERY_FEE` AS `DELIVERY_FEE`,`t1`.`PAYMENT_FEE` AS `PAYMENT_FEE`,`t1`.`COUPON_FEE` AS `COUPON_FEE`,
  (select sum(`t2`.`PRODUCT_AMOUNT_FINAL`) AS `SUM(T2.PRODUCT_AMOUNT_FINAL)` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1'))) AS `CLASS_AMOUNT_NORMAL`,
  (select sum(`t2`.`PRODUCT_AMOUNT_FINAL`) AS `SUM(T2.PRODUCT_AMOUNT_FINAL)` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'2'))) AS `BOOK_AMOUNT`,
  (select sum(`t2`.`PRODUCT_AMOUNT_FINAL`) AS `SUM(T2.PRODUCT_AMOUNT_FINAL)` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'3'))) AS `ROOM_AMOUNT`,
  (select sum(`t2`.`PRODUCT_AMOUNT_FINAL`) AS `SUM(T2.PRODUCT_AMOUNT_FINAL)` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'4'))) AS `AUDITION_AMOUNT`,
  (select group_concat(`t2`.`PRODUCT_ID` separator '/') AS `GROUP_CONCAT(T2.PRODUCT_ID SEPARATOR '/')` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1')) group by `t2`.`STANDINGBOOK_ID`) AS `CLASS_ID`,
  (select group_concat(`t5`.`CLASS_CODE` separator '/') AS `GROUP_CONCAT(T5.CLASS_CODE SEPARATOR '/')` from (`erp_standingbook_detail` `t2` join `erp_class` `t5`) where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_ID` = `t5`.`ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1')) group by `t2`.`STANDINGBOOK_ID`) AS `CLASS_CODE`,
  (select group_concat(`t2`.`PRODUCT_NAME` separator '/') AS `GROUP_CONCAT(T2.PRODUCT_NAME SEPARATOR '/')` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1')) group by `t2`.`STANDINGBOOK_ID`) AS `CLASS_NAME`,
  (select group_concat(`t5`.`TREECODE` separator '/') AS `GROUP_CONCAT(T5.TREECODE SEPARATOR '/')` from (`erp_standingbook_detail` `t2` join `erp_class` `t5`) where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_ID` = `t5`.`ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1')) group by `t2`.`STANDINGBOOK_ID`) AS `TREECODE`,
  (select group_concat(`t2`.`PRODUCT_ID` separator '/') AS `GROUP_CONCAT(T2.PRODUCT_ID SEPARATOR '/')` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'2')) group by `t2`.`STANDINGBOOK_ID`) AS `BOOK_ID`,
  (select group_concat(`t6`.`BOOK_CODE` separator '/') AS `GROUP_CONCAT(T6.BOOK_CODE SEPARATOR '/')`
  from (`erp_standingbook_detail` `t2` join `erp_book` `t6`) where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_ID` = `t6`.`ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'2')) group by `t2`.`STANDINGBOOK_ID`) AS `BOOK_CODE`,
  (select group_concat(`t2`.`PRODUCT_NAME` separator '/') AS `GROUP_CONCAT(T2.PRODUCT_NAME SEPARATOR '/')` from `erp_standingbook_detail` `t2`
  where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'2')) group by `t2`.`STANDINGBOOK_ID`) AS `BOOK_NAME`,`t1`.`STATUS`
  AS `STATUS`,`t1`.`STATUS` AS `SB_STATUS`,`t1`.`FINANCE_STATUS` AS `FINANCE_STATUS`,`t1`.`FINANCE_RECV_DATE` AS `FINANCE_RECV_DATE`,`t1`.`CREATE_PARTY_ID`
  AS `CREATE_PARTY_ID`,`t1`.`CREATE_PARTY_NAME` AS `CREATE_PARTY_NAME`,`t1`.`CREATE_PARTY_NAME` AS `SB_OPERATOR`,`t1`.`CREATE_DATE`
  AS `CREATE_DATE`,`t1`.`CREATE_DATE` AS `SB_DATE`,`t1`.`NORMAL_DATE` AS `NORMAL_DATE`,`t1`.`REMARK` AS `REMARK`,`t1`.`TEACHER_ID` AS `STEACHER_ID`,`t1`.`TEACHER_NAME` AS `STEACHER_NAME` from `erp_standingbook` `t1`;  不好意思，我以为自动能换。。

视图就是鸡肋。
如果你建好了索引，视图会跟你的 sql 一样，走索引的。
相对来说，物化视图就给力的多。但是需要你维护。

view是视图，没有索引，索引在视图引用的表上mysql-> SHOW INDEX FROM "你的db".erp_standingbook_detail
mysql-> SHOW INDEX FROM "你的db".erp_bookmysql-> EXPLAIN SELECT * from v_standingbook 看执行计划是否有用到后台表的index

调试易

mysql 创建索引

解决方案 »