SQL优化、索引优化

一、发现哪些SQL语句有性能问题

开启MySQL慢查询日志对SQL语句进行监控

1

    show variables like 'slow_query_log'; -- 查看是否开启慢查询日志

2

    set global slow_query_log = on; -- 开启慢查询日志

3

    set global log_queries_not_using_indexes = on;-- 记录没有使用索引查询的SQL语句

4

    set global long_query_time=1;-- 将查询时间大于1秒的语句记录下来

5

    show variables like 'slow%';--查看慢查询日志所在的文件地址

进行select语句查询

打开慢查询日志，查看日志信息

1

C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin\mysqld, Version: 5.1.73-community (MySQL Community Server (GPL)). started with:

2

TCP Port: 3306, Named Pipe: MySQL

3

Time                 Id Command    Argument

4

# Time: 170813 10:40:31

5

# User@Host: root[root] @ localhost [127.0.0.1]

6

# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 373  Rows_examined: 373

7

use eyesworld;

8

SET timestamp=1502592031;

9

select  * from city;

10

​

从第四行开始，日志分为5个部分：

1.time：查询语句执行开始时间

2.User@Host：用户名和ip

3.Query_time 查询所用时间 Lock_time 锁定时间 Rows_sent 发送的行数 Rows_examined 扫描行数

4.timestamp：查询语句执行时间的时间戳格式

5.查询语句内容

慢查询日志分析工具：MySQL dump slow和pt-query-digest

发现慢查询日志中有性能问题的SQL：

1.查询次数多，每次查询占用时间长的SQL（ pt-query-digest分析top前几个的查询语句）

2.IO大的SQL（rows examine扫描行数多的SQL语句，扫描行数越多，IO消耗越大）

3.未命名索引的SQL（对比Rows examined扫描行数 和 Rows Send实际发送结果的行数，如果扫描行数远远大于发送行数，则说明查找的 命中率不高，效率低）

二、通过explain+SQL语句查询SQL的执行计划

例如：

explain select id, province_name,city_name from city;

得到SQL执行这个select语句的执行计划：

select_type；select语句的类型，如果没有where条件，则为simple

table：表名

type：连接使用的类型：const、eq_reg、ref、range、index和ALL

possible_keys：可能使用的索引

key：

key_len：索引长度

ref：索引某一列被使用

rows：表扫描的行数（ 可以通过建立索引来减少扫描行数）

Extra：

三、Count（）和Max（）函数优化

max()函数：查询大值，时间最后

select max(pay_date) from payment;-- 查询最后一次的支付时间

执行max函数可能遇到的性能问题：

没有加入索引，导致select语句对全表进行扫描

优化方法：

加入索引，减少表扫描的行数

1

create index idx_paydate on payment(payment_date);

count()函数优化： count(*)优化成count(条件)

count(*)包含为null值的数据行

count(id OR NULL)不包含为null值的数据行

四、子查询优化

优化思路：把子查询优化为join查询

1

-- 子查询

2

SELECT * FROM table1 WHERE table1.id IN (SELECT table2.id FROM table2);

3

​

4

-- 优化成join查询

5

SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;

6

​

7

-- 去除重复数据（join查询的结果可能出现重复）

8

SELECT DISTINCE * FROM table1 JOIN table2 ON table1.id = table2.id;

五、GROUP BY查询优化

六、LIMIT优化（分页功能）

LIMIT 语句经常伴随 ORDER BY 从句使用，经常需要排序（fileSort），产生IO问题

优化思想：避免过多的行扫描

1

-- 按照名字排序后取出第50--55行数据（全表扫描，文件排序）

2

SELECT id FROM TABLE1 ORDER BY name LIMIT 50，5

3

​

4

-- 优化1：使用有索引的列或主键进行Order by操作（使用主键排序，扫描55行）

5

SELECT id FROM TABLE1 ORDER BY id LIMIT 50，5  -- 用id索引做 ORDER BY 排序

6

​

7

-- 优化2：主键（id）过滤，先缩小id范围，再进行order排序(只需扫描5行数据)

8

SELECT id FROM TABLE1 WHERE id>50 AND id<=60 ORDER BY id LIMIT 1,5 

七、索引优化

优化思想：避免过多的行扫描

    1.建立索引：where从句、group by、order by、on从句

    2.索引字段越小越好（数据库中存储数据以页为单位，字段越小，每页存的数据越多，IO操作越少）

    3.建立联合索引（离散度大的放在前面，可选择性越高）

1

-- 离散度大（重复值少）的字段放在前面

2

CREATE index idx_union ON table(id_big, id_small);

3

​

4

SELECT * FROM table WHERE id_big = 200 AND id_small = 10;

      4.索引不是越多越好

        建立索引可以提高查询效率（SELECT），但会降低写入（UPDATE\INSERT\DELETE）效率。索引越多，运行时分析索引所用时间越多。

        优化：找到冗余索引，将其删除