mysql执行计划基本优化实战技巧

1、首先要为表建立主键，笔者使用Aqua Data Studio，直接查询一条记录出来如：select * from hire_employee limit 1，直接对这一条数据右键-保存数据-复制到剪切板即可得到create table语句和insert语句，如

CREATE TABLE hire_employee  ( 
    id                        VARCHAR(64) NOT NULL,
    office_id              VARCHAR(128) NULL,
    name                   VARCHAR(128) NULL,
    mobile                 VARCHAR(128) NULL,

    primary key(id)             --建表时加入主键索引
    )
GO

如果已经有表也可以后面修改，加入主键索引如：

alter table hire_employee add primary key(id)

2、查看自己的查询语句，将where的字段、left join的字段加入单列索引或多列索引，一般left join的字段加入单列索引即可，而where的字段需要看查询的条件是否有规律再看看是否可以加入多列索引，不然就只加单列索引也可。这里的索引只是普通索引即可，当然还有UNIQUE INDEX唯一索引，主键索引PRIMARY INDEX等

索引就像字典的拼音表目录，sql server才分聚合索引（拼音表）和非聚合索引（部首表），而mysql只分单列索引和多列索引（都是拼音表类型）

a、text字段不应该创建索引，因为text字段的内容一般都很长，里面重复性也可能比较大，如果非要创建，只能取text字段内容的前n个字符作为索引键

--text字段需要加入大小限制，取前多少个字符作为索引，这里是取前255位字符作为索引键
CREATE INDEX index_check_record_reg_photo ON hire_check_record (reg_photo(255))
CREATE INDEX index_check_record_collect_photo ON hire_check_record (collect_photo(255))

图片和文件不应该以text存base64字符串或者blob存二进制的方式存进数据库，因为实践过select 1查出来速度是快的，但是慢就慢在取这个字段的值的时候非常慢，如select  collect_photo就很慢了，因为这里base64寸了10几w的字符数，一下子读取出来会非常慢，这里是取的时候影响的查询的性能，即查和取的速度都要考虑。

这里建索引是a.office_id=b.id，这里a表的office_id和b表的id这两个字段，两头都要建立单独的索引

b、有时你执行计划发现这条sql查询都是走全表扫描的，可能性有：

可能一点：如果你已经建好了索引，那是mysql查询优化器认为当前查询走全表扫描更快，因为数据量少的情况下，与其先消耗IO去看索引文件，然后再消耗IO在数据文件找，这样二次IO可能导致更慢，所以mysql查询优化器决定直接全表扫描

可能二点：如果你还没建索引，数据量又比较大（起码也要上w），那就是你还没建索引优化

执行计划的type如果是ALL就是没走索引，key就是使用的索引,rows是找了多少行才找到这条数据，Extra主要看排序，数据量不一样，执行计划也会不一样

c、mysql多个单列索引可能只会用最优一个

其实这里其实涉及到了mysql优化器的优化策略！当多条件联合查询时，优化器会评估用哪个条件的索引效率最高！它会选择最佳的索引去使用，也就是说，单列索引a 、b 、c这三个索引列都能用，只不过如优化器判断只需要使用a索引这一个单例索引就能完成本次查询，故最终explain展示的key为a。当然，如果优化器判断本次查询非要全使用三个索引才能效率最高，那么explain的key就会是a、b 、c，三个索引都会使用！

d、mysql多列索引最左原则

3、执行计划使用技巧，你可以注释部分where条件、注释部分left join条件来观察、创建/删除索引来观测执行计划是否大部分走索引，尽量不要走全表扫描，如：

explain
select * from hire_check_record a 
left join sys_office o on a.office_id = o.id and o.del_flag = '0'
left join hire_employee h on h.id = a.hire_employee_id 
left join data_request_log d on d.id = a.report_id
--去掉where也可以观察查询计划，这里因为select * 而且没where条件直接走全表扫描了
where a.report_id = '1' and a.hire_employee_id = '1' and a.office_id = '1'

4、实战型

如SQL如下：

SELECT
    a.id                  AS "id",
    a.name                AS "name",
    a.id_card             AS "idCard",
    a.sex                 AS "sex",
    drl.status            AS "dataRequestLog.status",
    drl.risk              AS "dataRequestLog.risk",
    a.create_by           AS "createBy.id",
    a.create_date         AS "createDate",
    a.update_by           AS "updateBy.id",
    a.update_date         AS "updateDate",
    a.remarks             AS "remarks",
    a.del_flag            AS "delFlag",
    o.id                  AS "office.id",
    o.name                AS "office.name",
    o.path_name           AS "office.pathName",
    e.id                  AS "hireEmployee.id",
    e.office_id           AS "hireEmployee.office.id",
    e.report_id           AS "hireEmployee.dataRequestLog.id",
    e.mobile              AS "hireEmployee.mobile",
    e.name                AS "hireEmployee.name",
    e.id_card             AS "hireEmployee.idCard",
    e.reg_photo           AS "hireEmployee.regPhoto.id",
    e.reg_finger          AS "hireEmployee.regFinger",
    e.collect_photo       AS "hireEmployee.collectPhoto.id",
    e.create_by           AS "hireEmployee.createBy.id",
    e.create_date         AS "hireEmployee.createDate",
    e.update_by           AS "hireEmployee.updateBy.id",
    e.update_date         AS "hireEmployee.updateDate",
    e.remarks             AS "hireEmployee.remarks",
    e.del_flag            AS "hireEmployee.delFlag",
    h.path                AS "regPhoto.path",
    hh.path               AS "collectPhoto.path" 
FROM
    hire_check_record a 
        LEFT JOIN sys_office o 
        ON a.office_id = o.id AND
        o.del_flag = '0' 
            LEFT JOIN hire_employee e 
            ON a.hire_employee_id = e.id AND
            e.del_flag = '0' 
                LEFT JOIN sys_attach h 
                ON a.reg_photo = h.id AND
                h.del_flag = '0' 
                    LEFT JOIN sys_attach hh 
                    ON a.collect_photo = hh.id AND
                    hh.del_flag = '0' 
                        LEFT JOIN data_request_log drl 
                        ON a.report_id = drl.id AND
                        drl.del_flag = '0' 
WHERE
    a.del_flag = '0' 
ORDER BY
    a.update_date DESC LIMIT 30

上面是例子SQL，SQL慢可能在于某个where条件没走索引慢，可以通过注释部分条件来观察，也可能是查询后抽取数据的部分慢，例如select remark，抽取每条记录的remark字段显示，假如remark字段是text文本存base64，那样抽取是非常慢的，SQL的查询包含：查询是横向的、抽取显示的字段是纵向的，纵向时也可能做一系列count、sum、case when、if null的操作，然后再groud by

4、关于联合索引的最左原则和单列索引的最优原则，我引用一篇博文解说：

多个单列索引和联合索引的区别详解 可以看我转载的博客有此文

5、建立索引的时机

a、一般来说，在where和join中出现的列需要建立索引，但也不完全如此，因为mysql只对<，<=，=，>，>=，between，in，以及只有左边不模糊右边模糊的like才会使用索引。例如：
SELECT * FROM mytable WHERE username like'admin%' 这种才使用索引

b、索引不会包含有NULL值的列，就像字典拼音索引a、b、c不应该有null座位索引项
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时就算要让字段的默认值为NULL，写入时也要最好补充''空字符串（但是实际情况不建议这样做，因为会对真实环境空字符串的数据有歧义）

c、可能的话可以使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

d、索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

e、like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

f、不要在列上进行运算
select * from users where YEAR(adddate)<2007; 

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select * from users where adddate<‘2007-01-01’;  

g、不使用not in 和 <>操作

h、对于联合索引，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到。这里有两个概念，走索引是否走得足够多，和where条件的顺序和建索引的字段的顺序有关，查询都是查询优化器先看索引能找到多少数据，然后再去根据where条件自动晒选数据范围，自动先走过滤足够多的数据的where条件，而这部分不需要人为将where过滤足够多数据的条件放在左边。人为方面只需要考虑索引顺序(a,b,d,c)因为已经知道c是>会中断索引，然后尽量保证where字段的顺序与索引顺序对应即可。

i、对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

j、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null

k、应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

l、应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or Name = 'admin'
可以这样查询：

select id from t where num = 10
union all
select id from t where Name = 'admin'

m、对于in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)
用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)

n、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2 = 100
应改为:
select id from t where num = 100*2

o、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3) = ’abc’       -–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′) = 0    -–'2005-11-30'    --生成的id
应改为:
select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'

p、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。nvarchar适用中文和其他字符，其中N表示Unicode编码，从名字上看多了个“N”。它表示存储的是Unicode数据类型的字符。我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较起来，nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。所以一般来说，如果含有中文字符，用nchar/nvarchar，如果纯英文和数字，用char/varchar

q、建立了索引的字段尽量不应该频繁地update