MySQL优化技巧

MySQL优化技巧

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

索引

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

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

创建索引的两种方式

  1. Create Index
    CREATE INDEX 索引名称 ON 表名(字段名(Length))
  2. 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查询语句

举个列子

EXPLAIN
SELECT *
FROM ProductBaseInfo AS pbi
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;

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

-- 查询中的某个列没有使用到索引时，则其右边的所有列都不会使用到索引

SELECT语句执行顺序

SELECT
DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
  1. FORM——对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
  2. ON——对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。
  3. JOIN——如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, 如果 from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
  4. WHERE——对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
  5. GROUP BY——根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
  6. CUBE | ROLLUP——对表VT5进行cube或者rollup操作，产生表VT6.
  7. HAVING——对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。
  8. SELECT——执行select操作，选择指定的列，插入到虚拟表VT8中。
  9. DISTINCT——对VT8中的记录进行去重。产生虚拟表VT9.
  10. ORDER BY——将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
  11. LIMIT——取出指定行的记录，产生虚拟表VT11, 并将结果返回。
posted @ 2017-10-31 10:51  Alvin.Lee  阅读(725)  评论(0)  编辑  收藏  举报