转自: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不会使用索引)
以下几种情况不建议创建索引:
- 表记录较少,网上有建议2000为界限,具体视情况自己把握
- 索引的选择性较低,所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:
Index Selectivity = Cardinality / #T
值越大越好 - 频繁更新型字段
如:登录次数,产品销量等,字段频繁更新导致索引频繁变化,增加数据库工作量,降低效率
使用索引的注意事项
- 索引不会包含有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,<>等平反
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
-- 查询表中所有的索引 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 可以使用索引
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
-- 查询表中所有的索引 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>
详细步骤解释:
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, 并将结果返回。