MySQL优化技巧

转自：https://www.zybuluo.com/chubin518/note/920443

---

• 索引
• Explain 查询计划
• 最左前缀匹配特性
• SELECT语句执行顺序

---

索引

---

索引是帮助MySQL高效获取数据的数据结构，所以索引的本质是一种数据结构

• 利 
  索引加快数据查询速度，提高数据库查询性能
• 弊 
  数据库中索引是以文件的方式存储的，需要用的时候读取到内存中，因此索引的I/O操作会影响数据库的性能 
  插入和更新操作会更改索引，因此会影响数据库插入和更新的性能，并且索引会占用一定的磁盘空间，使数据库变大。

创建索引的两种方式

    Create Index 
    CREATE INDEX 索引名称 ON 表名(字段名(Length))
Alter Table 
    ALTER 表名 ADD INDEX 索引名称 ON (字段名称(Length))

 

Length——索引长度，不设置此值时表示对全字段进行索引，如果是CHAR，VARCHAR类型，Length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 Length

举个例子：

    -- 创建索引
    CREATE INDEX Idx_MainTitle ON ProductBaseInfo (PBIMainTitle);

    -- 删除索引
    DROP INDEX Idx_MainTitle ON ProductBaseInfo;

    -- 查看索引
    SHOW INDEX
    FROM ProductBaseInfo;

 

使用索引的时机 
一般情况下，在WHERE或JOIN字句中出现的列需要添加索引 
但是 因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引（因为在以通配符%和_开头作查询时，MySQL不会使用索引）

以下几种情况不建议创建索引：

1. 表记录较少，网上有建议2000为界限，具体视情况自己把握
2. 索引的选择性较低，所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值： 
   Index Selectivity = Cardinality / #T 
   值越大越好
3. 频繁更新型字段 
   如：登录次数，产品销量等，字段频繁更新导致索引频繁变化，增加数据库工作量，降低效率

使用索引的注意事项

• 索引不会包含有NULL值的列 
  只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
• 使用短索引 
  对字符串列进行索引，如果可能应该指定一个前缀长度。如，如果有一个varchar(50)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
• 索引列排序 
  MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
• 不要在索引列上进行运算操作
• 建组合索引的时候，区分度最高的在最左边。 
  正例：如果 where a=? and b=? ，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。

Explain 查询计划

---

Explain显示了MySQL如何使用索引来处理Select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法 
EXPLAIN SELECT查询语句

举个列子

1. EXPLAIN
2. SELECT *
3. FROM ProductBaseInfo AS pbi
4. WHERE pbi.PBIMainTitle LIKE '测%';

EXPLAIN列解释 
- id ：id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询 
- select_type ：

1. SIMPLE——简单SELECT(不使用UNION或子查询) 
2. PRIMARY——最外面的SELECT 
3. UNION——UNION中的第二个或后面的SELECT语句 
4. DEPENDENT UNION——UNION中的第二个或后面的SELECT语句，取决于外面的查询 
5. UNION RESULT——UNION的结果 
6. SUBQUERY——子查询中的第一个SELECT 
7. DEPENDENT SUBQUERY——子查询中的第一个SELECT，取决于外面的查询 
8. DERIVED——导出表的SELECT(FROM子句的子查询) 

- table ：输出的行所引用的表。 
- type ：联接类型。按照从最佳类型到最坏类型进行排序 

1. system——表仅有一行(=系统表)。这是const联接类型的一个特例。 
2. const——表最多只有一个匹配行，在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次！const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时 
3. eq_ref——出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref 
4. ref——不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。 
5. ref_or_null——与ref方法类似，只是增加了null值的比较。实际用的不多。 
6. unique_subquery——该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。 
7. index_subquery——该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr) 
8. range——只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range 
9. index_merge——表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range 
10. index——索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。 
11. all——对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。 

- possible_keys ：查询可能使用到的索引都会在这里列出来 
- key ：查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。 
- key_len ：用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。 
- ref ：如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func 
- rows ：这里是执行计划中估算的扫描行数，不是精确值 
- extra ：包含MySQL解决查询的详细信息

为IN,LIKE,OR,<>等平反

    -- 查询表中所有的索引
    SHOW INDEX
    FROM ProductBaseInfo;
    -- IN是否使用索引
    EXPLAIN
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIBelong IN (2,3);
    EXPLAIN
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIId IN(100,200,300,400,500);
    EXPLAIN
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIOldProductId IN(1000001,1000010,1000020,1000033,1000009,1000006);
    -- in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内
    -- eg :
    EXPLAIN EXTENDED
    SELECT *
    FROM SGPProductBaseInfo AS pbi
    WHERE pbi.PBIOldProductId>1000 ;
    EXPLAIN EXTENDED
    SELECT *
    FROM SGPProductBaseInfo AS pbi
    WHERE pbi.PBIOldProductId>1000000 ;
    -- <>是否使用索引
    EXPLAIN EXTENDED
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIId <>1000;
    EXPLAIN EXTENDED
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIBelong <> 1;
    -- MySQL针对数据分布较均匀的列会导致索引失效，对于数据分布不均匀的列，索引不一定失效
    -- OR 是否使用索引
    EXPLAIN EXTENDED
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIId =1000 OR pbi.PBIOldProductId=101 ;
    EXPLAIN EXTENDED
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIId =1000 OR pbi.PBIMainTitle LIKE '%测%' ;
    -- 使用OR 进行连接时，如果有一个字段没有使用到索引 则会导致数据库引擎放弃使用索引进行全表扫描
    -- LIKE 是否使用索引
    EXPLAIN EXTENDED
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIMainTitle LIKE '%测%';
    EXPLAIN EXTENDED
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIMainTitle LIKE '测%';
    -- 在以通配符%和_开头作查询时，MySQL不会使用索引
    -- 索引文件具有 B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引
    -- 模糊查询不仅仅是LIKE
    -- INSTR(列,keyword)
    EXPLAIN EXTENDED
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE INSTR(pbi.PBIMainTitle,'周边')>0;
    -- LOCATE(keyword,列) 或别名 position
    EXPLAIN EXTENDED
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE LOCATE('周边',pbi.PBIMainTitle)>0;
    -- 效率上以上三个函数比LIKE稍快 待验证
    -- FIND_IN_SET(keyword,列)
    SELECT FIND_IN_SET('2','1,11,2,3,23') ; -- 返回对应的索引位置，索引1开始
    EXPLAIN EXTENDED
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIChannels & 1 = 1 ;
    EXPLAIN EXTENDED
    SELECT *
    FROM ProductLinePrice AS plp
    WHERE FIND_IN_SET('20171002',plp.PLPriceDates)>=1;

View Code

MySQL针对数据分布较均匀的列会导致索引失效，对于数据分布不均匀的列，索引不一定失效

最左前缀匹配特性

---

最左前缀——顾名思义，就是最左优先,只发生在多列组合索引中

例如： 
IX_OldProductId_Belong_DataFlag_IfDel 相当于创建了以下索引 
- OldProductId ——单列索引 
- OldProductId,Belong ——多列组合索引 
- OldProductId,Belong,DataFlag ——多列组合索引 
- OldProductId,Belong,DataFlag,IfDel ——多列组合索引

影响：

• 不按索引最左列开始查询（多列索引）时，则不会使用到索引 
  例如：IX_OldProductId_Belong_DataFlag_IfDel ， WHERE Belong = 1 不使用索引,WHERE Belong = 1 and DataFlag=1 不能使用索引
• 查询中某个列有范围查询时，则其右边的所有列都不会使用到索引 
  例如：IX_OldProductId_Belong_DataFlag_IfDel ， OldProductId=1000001 AND Belong>1 AND DataFlag=1 AND IfDel=0 不使用索引
• 查询中的某个列没有使用到索引时，则其右边的所有列都不会使用到索引 
  例如：IX_OldProductId_Belong_DataFlag_IfDel，OldProductId>100 AND Belong=1 AND DataFlag=1 AND IfDel=0 不使用索引，OldProductId>1000000 AND Belong=1 AND DataFlag=1 AND IfDel=0 可以使用索引

    -- 查询表中所有的索引
    SHOW INDEX
    FROM ProductBaseInfo;
    -- 最左前缀匹配特性发生在多列组合索引中，最左前缀——顾名思义，就是最左优先
    -- 如：IX_PBIOldProductId_Belong_DataFlag_IfDel 相当于创建了以下索引
    -- PBIOldProductId ——单列索引
    -- PBIOldProductId,PBIBelong ——多列组合索引
    -- PBIOldProductId,PBIBelong,PBIDataFlag ——多列组合索引
    -- PBIOldProductId,PBIBelong,PBIDataFlag,PBIIfDel ——多列组合索引
    EXPLAIN extended
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIOldProductId=1000001 AND pbi.PBIBelong=3 AND pbi.PBIDataFlag=1 AND pbi.PBIIfDel=0
    EXPLAIN extended
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIBelong=3 AND pbi.PBIDataFlag=1 AND pbi.PBIIfDel=0
    EXPLAIN extended
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIOldProductId=1000001 AND pbi.PBIDataFlag=1 AND pbi.PBIBelong=3 AND pbi.PBIIfDel=0;
    SHOW WARNINGS;
    EXPLAIN extended
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIDataFlag=1 AND pbi.PBIBelong=3 AND pbi.PBIIfDel=0 AND pbi.PBIOldProductId=1000001;
    SHOW WARNINGS;
    -- 不按索引最左列开始查询（多列索引）时，则不会使用到索引
    EXPLAIN extended
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIOldProductId=1000001 AND pbi.PBIBelong>1 AND pbi.PBIDataFlag=1 AND pbi.PBIIfDel=0
    -- 查询中某个列有范围查询时，则其右边的所有列都不会使用到索引
    EXPLAIN extended
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIOldProductId>100 AND pbi.PBIBelong=1 AND pbi.PBIDataFlag=1 AND pbi.PBIIfDel=0
    EXPLAIN extended
    SELECT *
    FROM ProductBaseInfo AS pbi
    WHERE pbi.PBIOldProductId>1000000 AND pbi.PBIBelong=1 AND pbi.PBIDataFlag=1 AND pbi.PBIIfDel=0
    -- 查询中的某个列没有使用到索引时，则其右边的所有列都不会使用到索引

View Code

SELECT语句执行顺序

---

1. SELECT
2. DISTINCT <select_list>
3. FROM <left_table>
4. <join_type> JOIN <right_table>
5. ON <join_condition>
6. WHERE <where_condition>
7. GROUP BY <group_by_list>
8. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10. LIMIT <limit_number>

　　详细步骤解释：

        FORM——对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
        ON——对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。
        JOIN——如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, 如果 from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
        WHERE——对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
        GROUP BY——根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
        CUBE | ROLLUP——对表VT5进行cube或者rollup操作，产生表VT6.
        HAVING——对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。
        SELECT——执行select操作，选择指定的列，插入到虚拟表VT8中。
        DISTINCT——对VT8中的记录进行去重。产生虚拟表VT9.
        ORDER BY——将虚拟表VT9中的记录按照进行排序操作，产生虚拟表VT10.
        LIMIT——取出指定行的记录，产生虚拟表VT11, 并将结果返回。

 

转载于:https://www.cnblogs.com/ericli-ericli/articles/7762994.html