相似功能不风流倜傥写法编程,深切mysql慢查询设

作者:编程

几天前给外人做网站时有个要求是要有域名询问效能,查了点资料写了个简单的查询功能前台页面用的是checkbox,代码如下: 复制代码 代码如下: PHP代码: 复制代码 代码如下:

生龙活虎、SQL、Linq、lamda表明式 同风流洒脱功用十分小器晚成写法

在web开采中,大家平时会写出有个别SQL语句,一条倒霉的SQL语句可能令你的全套程序都相当慢,超越10秒平常客户就能接受关闭网页,怎样优化SQL语句将这几个运营时刻 相比长的SQL语句搜索吧?MySQL给我们提供了叁个很好的效用,那正是慢查询!所谓的慢查询正是经过设置来记录超越一定时期的SQL语句!那么如何使用慢查询呢?

 

1.敞开MySQL的慢查询日志功用 私下认可情状下,MySQL是不会记录抢先一定奉行时间的SQL语句的。要敞开那些效果,我们必要纠正MySQL的安顿文件,windows下修正my.ini,Linux下修正my.cnf文件,在[mysqld]最终扩大如下命令:

SQL

LINQ

Lambda

SELECT *

FROM HumanResources.Employee

from e in Employees

select e

Employees
   .Select (e => e)

SELECT e.LoginID, e.JobTitle

FROM HumanResources.Employee AS e

from e in Employees

select new {e.LoginID, e.JobTitle}

Employees
   .Select (
      e =>
         new
         {
            LoginID = e.LoginID,
            JobTitle = e.JobTitle
         }
   )

SELECT e.LoginID AS ID, e.JobTitle AS Title

FROM HumanResources.Employee AS e

from e in Employees

select new {ID = e.LoginID, Title = e.JobTitle}

Employees
   .Select (
      e =>
         new
         {
            ID = e.LoginID,
            Title = e.JobTitle
         }
   )

SELECT DISTINCT e.JobTitle

FROM HumanResources.Employee AS e

(from e in Employees

select e.JobTitle).Distinct()

Employees
   .Select (e => e.JobTitle)
   .Distinct ()

SELECT e.*

FROM HumanResources.Employee AS e

WHERE e.LoginID = 'test'

from e in Employees

where e.LoginID == "test"

select e

Employees
   .Where (e => (e.LoginID == "test"))

SELECT e.*

FROM HumanResources.Employee AS e

WHERE e.LoginID = 'test' AND e.SalariedFlag = 1

from e in Employees

where e.LoginID == "test" && e.SalariedFlag

select e

Employees
   .Where (e => ((e.LoginID == "test") && e.SalariedFlag))

SELECT e.*
FROM HumanResources.Employee AS e

WHERE e.VacationHours >= 2 AND e.VacationHours <= 10

from e in Employees

where e.VacationHours >= 2 && e.VacationHours <= 10

select e

Employees
   .Where (e => (((Int32)(e.VacationHours) >= 2) && ((Int32)(e.VacationHours) <= 10)))

SELECT e.*

FROM HumanResources.Employee AS e
ORDER BY e.NationalIDNumber

from e in Employees

orderby e.NationalIDNumber

select e

Employees
   .OrderBy (e => e.NationalIDNumber)

SELECT e.*

FROM HumanResources.Employee AS e

ORDER BY e.HireDate DESC, e.NationalIDNumber

from e in Employees

orderby e.HireDate descending, e.NationalIDNumber

select e

Employees
   .OrderByDescending (e => e.HireDate)
   .ThenBy (e => e.NationalIDNumber)

SELECT e.*
FROM HumanResources.Employee AS e

WHERE e.JobTitle LIKE 'Vice%' OR SUBSTRING(e.JobTitle, 0, 3) = 'Pro'

from e in Employees

where e.JobTitle.StartsWith("Vice") || e.JobTitle.Substring(0, 3) == "Pro"

select e

Employees
   .Where (e => (e.JobTitle.StartsWith ("Vice") || (e.JobTitle.Substring (0, 3) == "Pro")))

SELECT SUM(e.VacationHours)

FROM HumanResources.Employee AS e

 

Employees.Sum(e => e.VacationHours);

SELECT COUNT(*)

FROM HumanResources.Employee AS e

 

Employees.Count();

SELECT SUM(e.VacationHours) AS TotalVacations, e.JobTitle

FROM HumanResources.Employee AS e

GROUP BY e.JobTitle

from e in Employees

group e by e.JobTitle into g

select new {JobTitle = g.Key, TotalVacations = g.Sum(e => e.VacationHours)}

Employees
   .GroupBy (e => e.JobTitle)
   .Select (
      g =>
         new
         {
            JobTitle = g.Key,
            TotalVacations = g.Sum (e => (Int32)(e.VacationHours))
         }
   )

SELECT e.JobTitle, SUM(e.VacationHours) AS TotalVacations

FROM HumanResources.Employee AS e

GROUP BY e.JobTitle

HAVING e.COUNT(*) > 2

from e in Employees

group e by e.JobTitle into g

where g.Count() > 2

select new {JobTitle = g.Key, TotalVacations = g.Sum(e => e.VacationHours)}

Employees
   .GroupBy (e => e.JobTitle)
   .Where (g => (g.Count () > 2))
   .Select (
      g =>
         new
         {
            JobTitle = g.Key,
            TotalVacations = g.Sum (e => (Int32)(e.VacationHours))
         }
   )

SELECT *

FROM Production.Product AS p, Production.ProductReview AS pr

from p in Products

from pr in ProductReviews

select new {p, pr}

Products
   .SelectMany (
      p => ProductReviews,
      (p, pr) =>
         new
         {
            p = p,
            pr = pr
         }
   )

SELECT *

FROM Production.Product AS p

INNER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID

from p in Products

join pr in ProductReviews on p.ProductID equals pr.ProductID

select new {p, pr}

Products
   .Join (
      ProductReviews,
      p => p.ProductID,
      pr => pr.ProductID,
      (p, pr) =>
         new
         {
            p = p,
            pr = pr
         }
   )

SELECT *

FROM Production.Product AS p

INNER JOIN Production.ProductCostHistory AS pch ON p.ProductID = pch.ProductID AND p.SellStartDate = pch.StartDate

from p in Products

join pch in ProductCostHistories on new {p.ProductID, StartDate = p.SellStartDate} equals new {pch.ProductID, StartDate = pch.StartDate}

select new {p, pch}

Products
   .Join (
      ProductCostHistories,
      p =>
         new
         {
            ProductID = p.ProductID,
            StartDate = p.SellStartDate
         },
      pch =>
         new
         {
            ProductID = pch.ProductID,
            StartDate = pch.StartDate
         },
      (p, pch) =>
         new
         {
            p = p,
            pch = pch
         }
   )

SELECT *

FROM Production.Product AS p

LEFT OUTER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID

from p in Products

join pr in ProductReviews on p.ProductID equals pr.ProductID

into prodrev

select new {p, prodrev}

Products
   .GroupJoin (
      ProductReviews,
      p => p.ProductID,
      pr => pr.ProductID,
      (p, prodrev) =>
         new
         {
            p = p,
            prodrev = prodrev
         }
   )

SELECT p.ProductID AS ID

FROM Production.Product AS p

UNION

SELECT pr.ProductReviewID

FROM Production.ProductReview AS pr

(from p in Products

select new {ID = p.ProductID}).Union(

from pr in ProductReviews

select new {ID = pr.ProductReviewID})

Products
   .Select (
      p =>
         new
         {
            ID = p.ProductID
         }
   )
   .Union (
      ProductReviews
         .Select (
            pr =>
               new
               {
                  ID = pr.ProductReviewID
               }
         )
   )

SELECT TOP (10) *

FROM Production.Product AS p

WHERE p.StandardCost < 100

(from p in Products

where p.StandardCost < 100

select p).Take(10)

Products
   .Where (p => (p.StandardCost < 100))
   .Take (10)

SELECT *

FROM [Production].[Product] AS p

WHERE p.ProductID IN(

    SELECT pr.ProductID

    FROM [Production].[ProductReview] AS [pr]

    WHERE pr.[Rating] = 5

    )

from p in Products

where (from pr in ProductReviews

where pr.Rating == 5

select pr.ProductID).Contains(p.ProductID)

select p

Products
   .Where (
      p =>
         ProductReviews
            .Where (pr => (pr.Rating == 5))
            .Select (pr => pr.ProductID)
            .Contains (p.ProductID)
   )

 

复制代码 代码如下:

 

slow_query_log
long_query_time = 1

二、同大器晚成效率的三种不相同写法的实例

2.测验慢查询日志作用
(1)步向MySql调节台,实践如下语句:

 

复制代码 代码如下:

1、轻巧的函数总结(count,min,max,sum卡塔尔

select sleep(2);

 

mysql> select sleep(2);
----------
| sleep(2) |
----------
|        0 |
----------
1 row in set (2.12 sec)
(2)查看慢查询日志文件think-slow.log,在文书最后发掘:

 

复制代码 代码如下:

C# 代码   复制

# Time: 121120 20:06:23
# [email protected]: root[root] @ localhost [127.0.0.1]
# Query_time: 2.104120  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1353413183;
select sleep(2);

编程 1编程 2

3.解释:
(1)slow_query_log 那句是翻开记录慢查询功效,slow_query_log=0关闭;slow_query_log=1开启(这几个1得以不写卡塔尔

            //1

(2)long_query_time = 1 那句是记录超越1秒的SQL实践语句

编程 3

(3)那么这一个日志文件寄放在如何地点呢? 暗中认可是身处mysql的data目录,并且文件名字为host_name-slow.log即 主机名-slow.log,譬喻在作者的开销机上正是THINK-slow.log(因为偶用的Thinkpad,呵呵)

            ////获取最大的rpId
            var ss = (from r in db.Am_recProScheme
                      select r).Max(p => p.rpId);
            ////获取最小的rpId
            var ss = (from r in db.Am_recProScheme
                      select r).Min(p => p.rpId);
            //获取结果集的总数
              var ss = (from r in db.Am_recProScheme                  
                     select r).Count();
            //获取rpId的和
            var ss = (from r in db.Am_recProScheme
                      select r).Sum(p => p.rpId);


            //2
            var ss1 = db.Am_recProScheme.Max(p=>p.rpId);
            var ss1 = db.Am_recProScheme.Min(p => p.rpId);
            var ss1 = db.Am_recProScheme.Count() ;
            var ss1 = db.Am_recProScheme.Sum(p => p.rpId);
            Response.Write(ss);

            //3
            string sssql = "select max(rpId) from Am_recProScheme";
            sssql = "select min(rpId) from Am_recProScheme";
            sssql = "select count(1) from Am_recProScheme";
            sssql = "select sum(rpId) from Am_recProScheme";

(4)借使日志文件不想放在data目录,大家得以经过如下配置内定寄存的目录及日志文件名: slow_query_log_file=file_name
其中file_name就是你的寄存日志的目录和文件名,在那注意有的资料上恐怕是log-slow-queries=file_name,那么些在mysql5.5版已经不适合时宜宜!

 

4.什么样记录低于1s的慢查询记录呢? MySQL5.21版以前long_query_time 参数的单位是秒,暗许值是10。这一定于说最低只可以记录推行时间超越 1 秒的询问,怎么记录查询时间超越100飞秒的SQL语句记录呢?在mysql5.21 后版本扶持皮秒记录
(1)进入MySql调整台,运维如下sql语句:

2、排序order by desc/asc

复制代码 代码如下:

 

set global long_query_time=0.1

 

该句是安装记录慢查询超过时间100ms的SQL,记住要重启mysql手艺奏效!
(2)测试 踏入mysql调节台,执行如下sql语句:

C# 代码   复制

复制代码 代码如下:

编程 4编程 5

select sleep(0.5);

          var ss = from r in db.Am_recProScheme

翻看慢查询日志文件,大家见到最终增多的新音讯:

编程 6

复制代码 代码如下:

                     where r.rpId > 10

# Time: 121120 20:42:06
# [email protected]: root[root] @ localhost [127.0.0.1]
# Query_time: 0.500028  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1353415326;
select sleep(0.5);

编程 7

...

                     orderby r.rpId descending  //倒序

编程 8

                     //  orderby r.rpId ascending   //正序

编程 9

                     select r;

编程 10编程 11

          //正序

编程 12

           var ss1 = db.Am_recProScheme.OrderBy(p => p.rpId).Where(p => p.rpId > 10).ToList();

编程 13编程 14

          //倒序

编程 15

           var ss2 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).ToList();

编程 16编程 17

          string sssql = "select * from Am_recProScheme where rpid>10 order by rpId [desc|asc]";

编程 18

 

3、top

 

 

C# 代码   复制

编程 19编程 20

          //1

编程 21

          //如果取最后一个可以按倒叙排列再取值

编程 22

           var ss = (from r in db.Am_recProScheme select r).FirstOrDefault();

编程 23编程 24

          //2

编程 25

          string sssql = "select top(1) * from Am_recProScheme";

编程 26

 

4、跳过前边多少条数据取余下的多少

 

 

C# 代码   复制

编程 27编程 28

             //1

编程 29

           var ss = (from r in db.Am_recProScheme

编程 30

                      orderby r.rpId descending

编程 31

                      select r).Skip(10); //跳过前10条数据,取10条之后的所有数据 

编程 32编程 33

             //2  

编程 34

           var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Skip(10).ToList();

编程 35编程 36

           //3

编程 37

           string sssql = "select * from  (select ROW_NUMBER()over(order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum>10";

编程 38

 

5、分页数据查询

 

 

C# 代码   复制

编程 39编程 40

             //1

编程 41

           var ss = (from r in db.Am_recProScheme

编程 42

                      where r.rpId > 10

编程 43

                      orderby r.rpId descending

编程 44

                      select r).Skip(10).Take(10); //取第11条到第20条数据                   

编程 45编程 46

            //2 Take(10): 数据从开始获取,获取指定数量(10)的连续数据

编程 47

            var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).Skip(10).Take(10).ToList();

编程 48编程 49

           //3

编程 50

           string sssql = "select * from  (select ROW_NUMBER()over(order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum>10 and rowNum<=20";

编程 51

 

6、包含,类似like '%%'

 

 

C# 代码   复制

编程 52编程 53

              //1

编程 54

            var ss = from r in db.Am_recProScheme

编程 55

                     where r.SortsText.Contains("张")

编程 56

                     select r;

编程 57

            //2

编程 58

            var ss1 = db.Am_recProScheme.Where(p => p.SortsText.Contains("张")).ToList();

编程 59编程 60

            //3

编程 61

            string sssql = "select * from Am_recProScheme where SortsText like '%张%'";

编程 62

 

7、分组group by

 

 

C# 代码   复制

编程 63编程 64

              //1

编程 65

            var ss = from r in db.Am_recProScheme

编程 66

                     orderby r.rpId descending

编程 67

                     group r by r.recType into n

编程 68

                     select new

编程 69

                     {
                         n.Key,  //这个Key是recType
                         rpId = n.Sum(r => r.rpId), //组内rpId之和
                         MaxRpId = n.Max(r => r.rpId),//组内最大rpId
                         MinRpId = n.Min(r => r.rpId), //组内最小rpId
                     };
            foreach (var t in ss)
            {
                Response.Write(t.Key   "--"   t.rpId   "--"   t.MaxRpId   "--"   t.MinRpId);
            }

            //2
            var ss1 = from r in db.Am_recProScheme
                     orderby r.rpId descending
                     group r by r.recType into n
                     select n;
            foreach (var t in ss1)
            {
                Response.Write(t.Key   "--"   t.Min(p => p.rpId));
            }

            //3
            var ss2 = db.Am_recProScheme.GroupBy(p => p.recType);
            foreach (var t in ss2)
            {
                Response.Write(t.Key   "--"   t.Min(p => p.rpId));
            }

            //4
            string sssql = "select recType,min(rpId),max(rpId),sum(rpId) from Am_recProScheme group by recType";

 

8、连接查询 

 

 

C# 代码   复制

编程 70编程 71

            //1

编程 72

            var ss = from r in db.Am_recProScheme

编程 73

                     join w in db.Am_Test_Result on r.rpId equals w.rsId

编程 74

                     orderby r.rpId descending

编程 75

                     select r;

编程 76编程 77

            //2

编程 78

            var ss1 = db.Am_recProScheme.Join(db.Am_Test_Result, p => p.rpId, r => r.rsId, (p, r) => p).OrderByDescending(p => p.rpId).ToList();

编程 79编程 80

            //3

编程 81

            string sssql = "select r.* from  [Am_recProScheme] as r inner join [dbo].[Am_Test_Result] as t on r.[rpId] = t.[rsId] order by r.[rpId] desc";

编程 82

 

9、sql中的In

 

 

C# 代码   复制

编程 83编程 84

              //1

编程 85

            var ss = from p in db.Am_recProScheme

编程 86

                              where (new int?[] { 24, 25,26 }).Contains(p.rpId)
                              select p;
            foreach (var p in ss)
            {
                Response.Write(p.Sorts);
            }

            //2
            string st = "select * from Am_recProScheme where rpId in(24,25,26)";

本文由www.204.net发布,转载请注明来源

关键词: www.204.net