MySql常用（死锁，加素引，查看素引）

SQL查询优化技巧

最新推荐文章于 2025-07-02 22:33:16 发布

chen-jake

最新推荐文章于 2025-07-02 22:33:16 发布

阅读量235

点赞数

CC 4.0 BY-SA版权

分类专栏： MySql

本文链接：https://blog.youkuaiyun.com/qq_36815739/article/details/82500836

MySql 专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍了一种使用SQL语句进行数据库查询优化的方法，包括如何利用showprocesslist检查进程，使用showOPENTABLESwhereIn_use>0监控打开的表，通过KILL命令终止特定进程，以及运用explain分析查询是否使用了索引，特别强调了在复杂查询中创建索引的重要性。

show processlist; 查看进程
show OPEN TABLES where In_use > 0

KILL 73624      kill进程

show index from t_reb_ebroomlisting 查看表素引

CREATE INDEX index_ebroomlisting_gardenid ON t_reb_ebroomlisting (fkgardenid); 加素引

explain
SELECT
D.FID    AS  "id",
    D.Furl3D AS "url3D",
    D.FBUILDNAME AS "buildName",
    D.Fadvtitle  AS  "advtitle",
    A.FNAME   AS   "areaName",
fm.furl as "coverUrl",
    CASE WHEN D.FLISTINGTYPE='SALE' THEN D.FPRICE ELSE D.FRENT END AS "totalPrice",
    PA.FNAME AS "parentAreaName",
    D.FGARDENNAME AS "gardenName",
    D.FMAPX AS "mapx",
    D.FMAPY AS "mapy",
    D.FDIRECTION AS "direction",
    D.FFLOOR AS "floor",
    D.FDECORATION AS "decoration",
    D.FBUILDYEAR AS "buildYear",
    D.FBEDROOM AS "bedRoom",
    D.FLIVINGROOM AS "livingRoom",
    D.FBATHROOM AS "bathRoom",
    D.FBUILDAREA AS "buildArea",
    D.FLIGHTSPOT AS "lightspot",
    D.Fdescription AS "description",
    D.Faddress AS "address",
    CASE WHEN D.FLISTINGTYPE='SALE' THEN D.faverageprice ELSE D.faveragerent END AS "perPrice",
    CASE WHEN D.FLISTINGTYPE='SALE' THEN D.FPRICE ELSE D.FRENT END AS "price",
fm.furl as "coverUrl",
    (select CONCAT("距离",s.flinename,s.fstationname,s.fdistance,"米")   from t_bc_gardenstation s
where s.fkgardenid=D.fkgardenid  LIMIT 1) AS "station"
FROM T_reb_ebroomListing D
LEFT JOIN T_BD_AREA A ON D.fkareaid=A.FID
LEFT JOIN T_BD_AREA PA ON D.FPARENTAREAID=PA.FID
LEFT JOIN T_REB_EBROOMLISTINGIMAGE fm on fm.FKROOMLISTINGID=d.fid and fm.FFIRSTCOVER=1
LEFT JOIN T_bc_garden G on D.fkgardenid=G.FID
WHERE 1=1 AND FLISTINGTYPE='SALE' AND D.FpropertyType IN ('APARTMENT','LIVINGBUILDING','VILLA')

explain 查询是否用到素引