一,用explain查出分析查询语句
explain+SQL
explain
SELECT
sst.stu_code stuCode,
sst.`name` stuName,
sst.id_card idCard,
sst.`year` grade,
IFNULL( sst.stu_number, '' ) stuNumber,
IFNULL( sst.exam_number, '' ) examNumber,
sd.dept_name deptName,
em.major_name majorName,
IFNULL( ec.class_name, '' ) className,
'2019' `year`,
sst.enroll_type enrollType,
GROUP_CONCAT( DISTINCT mt1.cost_type_sign ) dormType,
SUM( IFNULL( fee.charge, 0 ) ) /
IF
(
COUNT( DISTINCT ftc.sort_id ) = 0,
1,
COUNT( DISTINCT ftc.sort_id )
) isFee,
SUM(
IFNULL(
IF
(
LENGTH( mt.cost_fees ) > 0,
mt.cost_fees,
ftc.fee_money
),
0
)
) /
IF
(
COUNT( DISTINCT fee.charge_id ) = 0,
1,
COUNT( DISTINCT fee.charge_id )
) + SUM( DISTINCT IFNULL( mt1.cost_fees, 0 ) ) AS needFee,
GROUP_CONCAT( DISTINCT jmfc.TOTAL_AMOUNT ) jm,
GROUP_CONCAT( DISTINCT dkfc.TOTAL_AMOUNT ) dk,
GROUP_CONCAT( DISTINCT hjfc.TOTAL_AMOUNT ) hj
FROM
stu_student sst
INNER JOIN edu_major em ON sst.major_code = em.country_code
INNER JOIN sys_dept sd ON em.dept_id = sd.dept_id
INNER JOIN fee_tuition ftt ON ftt.id = em.id
AND sst.`YEAR` = ftt.grade
AND '2019' BETWEEN ftt.`year`
AND ftt.year_end
INNER JOIN fee_tuitionCategory ftc ON ftc.feeMain_id = ftt.feeMain_id
LEFT JOIN mainCostAdjustment mt ON mt.STU_CODE = sst.STU_CODE
AND mt.academicYear = '2019'
AND mt.cost_type = ftc.fee_name
LEFT JOIN fee_charge fee ON fee.stu_code = sst.stu_code
AND fee.`year` = '2019'
AND fee.STATUS = '1'
LEFT JOIN mainCostAdjustment mt1 ON mt1.STU_CODE = sst.STU_CODE
AND mt1.academicYear = '2019'
AND mt1.cost_type = ( SELECT id FROM fee_detail_config WHERE is_dorm_fee = '1' )
LEFT JOIN edu_class ec ON sst.class_id = ec.class_id
LEFT JOIN stu_verify jmsv ON jmsv.YEAR = '2019'
AND jmsv.stu_code = sst.stu_code
AND jmsv.type = '2'
AND jmsv.STATUS = '1'
LEFT JOIN fee_chargeagainst jmfc ON jmfc.VERIFY_ID = jmsv.verify_id
LEFT JOIN stu_verify dksv ON dksv.YEAR = '2019'
AND dksv.stu_code = sst.stu_code
AND dksv.type = '3'
AND dksv.STATUS = '1'
LEFT JOIN fee_chargeagainst dkfc ON dkfc.VERIFY_ID = dksv.verify_id
LEFT JOIN stu_verify hjsv ON hjsv.YEAR = '2019'
AND hjsv.stu_code = sst.stu_code
AND hjsv.type = '5'
AND hjsv.STATUS = '1'
LEFT JOIN fee_chargeagainst hjfc ON hjfc.VERIFY_ID = hjsv.verify_id
WHERE
sst.`year` = '2019'
GROUP BY
sd.dept_id,
em.id,
sst.stu_code
查询结果:
二,分析:
type性能从最好到最差的依次是:
system > const > eq_ref > ref > range > index > all
SQL查询结果有9个类型是all,拖慢了整体运行速度
三,优化(加索引)
1,首先我们得知道索引有哪些?
- 主键索引:
主键索引不可以为空
主键索引可以做外键
一张表中只能有一个主键索引 - 普通索引:
用来加速数据访问速度而建立的索引。多建立在经常出现在查询条件的字段和经常用于排序的字段。
被索引的数据列允许包含重复的值 - 唯一索引:
被索引的数据列不允许包含重复的值
2,使用这些索引的原则:
- 普通索引:最基本的索引,没有任何限制
- 唯一索引:与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。
- 主键索引:它 是一种特殊的唯一索引,不允许有空值。
- 全文索引:仅可用于 MyISAM 表,针对较大的数据,生成全文索引很耗时好空间。
- 组合索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。
3,添加索引:
步骤:
- 找到第一个type=all的行,找到其中table名
- 在SQL中看那块用到这个表,找到这个表和其他表之间的关联条件
给关联条件加索引,并且保证该索引和关联索引的编码一致
添加索引两种方式:
- 方式一(SQL添加):
普通索引
ALTER TABLE sys_dept ADD INDEX dept_id ( dept_id );
ALTER TABLE edu_major ADD INDEX dept_id ( dept_id );
- 方式二(界面添加):
打开indexes,在此配置
注意:保证相关联的索引编码一致,否则索引不起作用
- 其他type=all的依次进行添加索引