做电子相册的大网站,发帖推广,洛阳市新区建设投资有限公司网站,网站做优化效果怎么样文章目录 一、SQL执行顺序二、索引简介1、关于索引2、索引的类型Btree 索引Btree 索引 三、Explain简介四、Explain 详解1、id2、select_type3、table4、type5、possible_keys6、key7、key_len8、ref9、rows10、Extra11、小案例 五、索引优化1、单表索引优化2、两表索引优化3、… 文章目录 一、SQL执行顺序二、索引简介1、关于索引2、索引的类型Btree 索引Btree 索引 三、Explain简介四、Explain 详解1、id2、select_type3、table4、type5、possible_keys6、key7、key_len8、ref9、rows10、Extra11、小案例 五、索引优化1、单表索引优化2、两表索引优化3、三表索引优化 六、索引失效1、带头大哥不能死中间兄弟不能断2、不在索引列上做任何操作函数、计算、类型转换等操作3、范围之后的索引字段全失效4、索引列和查询列尽量一致减少select *5、mysql在使用不等于!或者的时候无法使用索引会导致全表扫描6、is nullis not null 也无法使用索引7、 like操作时%开头会导致索引失效like %abc%还有like %abc会导致索引失效但是like abc%不会导致索引失效8、解决【like ‘%str%’ 】索引失效的问题——覆盖索引9、字符串不加单引号索引失效10、少用or用它连接时会索引失效11、索引优化面试题12、索引失效总结 七、in和Exists语句怎么使用性能好八、ORDER BY 优化 一、SQL执行顺序
我们手写的 SQL 顺序 MySQL 实际执行 SQL 顺序 二、索引简介
1、关于索引
单值索引给一个字段添加索引。在user表中给name属性建个索引create index idx_user_name on user(name)复合索引给几个字段添加索引。在user表中给name、email属性建个索引create index idx_user_nameEmail on user(name,email) 索引的本质 索引就是排序你对某个字段建索引数据库就会对所有数据针对这个字段做一个排序。站队从高到低站好找人就很好找如果没有索引那就是全表扫描。 建索引时注意点 由于肯定有一张表要全表扫描被全表扫描的这个表最好是数据量很小的表。 务必小表驱动大表然后在大表上面建索引 哪些情况下适合建立索引 主键自动建立唯一索引频繁作为查询的条件的字段应该创建索引查询中与其他表关联的字段外键关系建立索引频繁更新的字段不适合创建索引Where 条件里用不到的字段不创建索引单间/组合索引的选择问题Who在高并发下倾向创建组合索引查询中排序的字段排序字段若通过索引去访问将大大提高排序的速度查询中统计或者分组字段 哪些情况不要创建索引 表记录太少经常增删改的表数据重复且分布平均的表字段如性别只有男女两种结果建了索引也没有意义。如果一个表中有2000条记录表索引列有1980个不同的值那么这个索引的选择性就是1980/20000.99一个索引的选择性越接近于1这个索引的效率就越高。
2、索引的类型
参考文献
Btree 索引 Btree 索引搜索过程 【初始化介绍】
一颗 b 树 浅蓝色的块我们称之为一个磁盘块 可以看到每个磁盘块包含几个数据项深蓝色所示 和指针黄色所示如磁盘块 1 包含数据项 17 和 35 包含指针 P1、 P2、 P3P1 表示小于 17 的磁盘块 P2 表示在 17 和 35 之间的磁盘块 P3 表示大于 35 的磁盘块真实的数据存在于叶子节点和非叶子节点中
【查找过程】
如果要查找数据项 29 那么首先会把磁盘块 1 由磁盘加载到内存 此时发生一次 IO 在内存中用二分查找确定 29在 17 和 35 之间 锁定磁盘块 1 的 P2 指针 内存时间因为非常短相比磁盘的 IO 可以忽略不计通过磁盘块 1的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存 发生第二次 IO 29 在 26 和 30 之间 锁定磁盘块 3 的 P2 指针通过指针加载磁盘块 8 到内存 发生第三次 IO 同时内存中做二分查找找到 29 结束查询 总计三次 IO。 Btree 索引 Btree 索引搜索过程 【BTree 与 BTree 的区别】
B-树的关键字数据项和记录是放在一起的 B树的非叶子节点中只有关键字和指向下一个节点的索引 记录只放在叶子节点中。
【BTree 与 BTree 的查找过程】
在 B 树中 越靠近根节点的记录查找时间越快 只要找到关键字即可确定记录的存在 而 B 树中每个记录的查找时间基本是一样的 都需要从根节点走到叶子节点 而且在叶子节点中还要再比较关键字。从这个角度看 B 树的性能好像要比 B 树好 而在实际应用中却是 B 树的性能要好些。 因为 B 树的非叶子节点不存放实际的数据这样每个节点可容纳的元素个数比 B 树多 树高比 B 树小 这样带来的好处是减少磁盘访问次数。尽管 B 树找到一个记录所需的比较次数要比 B 树多 但是一次磁盘访问的时间相当于成百上千次内存比较的时间 因此实际中B 树的性能可能还会好些 而且 B树的叶子节点使用指针连接在一起 方便顺序遍历范围搜索 这也是很多数据库和文件系统使用 B树的缘故。
【性能提升】
真实的情况是 3 层的 B 树可以表示上百万的数据 如果上百万的数据查找只需要三次 IO 性能提高将是巨大的如果没有索引 每个数据项都要发生一次 IO 那么总共需要百万次的 IO 显然成本非常非常高。
【思考 为什么说 B树比 B-树更适合实际应用中操作系统的文件索引和数据库索引】
B树的磁盘读写代价更低B树的内部结点并没有指向关键字具体信息的指针。 因此其内部结点相对 B 树更小。 如果把所有同一内部结点的关键字存放在同一盘块中 那么盘块所能容纳的关键字数量也越多。 一次性读入内存中的需要查找的关键字也就越多。 相对来说 IO 读写次数也就降低了。B树的查询效率更加稳定由于非终结点并不是最终指向文件内容的结点 而只是叶子结点中关键字的索引。 所以任何关键字的查找必须走一条从根结点到叶子结点的路。 所有关键字查询的路径长度相同 导致每一个数据的查询效率相当。 三、Explain简介
官网地址
Explain能干嘛
能看到一条sql语句实际执行时的MySQL底层的加载顺序根据输出的id 字段去看数据读取操作的操作类型select_type 字段哪些索引可能被使用possible_keys 字段哪些索引被实际使用keys 字段表之间的引用ref 字段每张表有多少行被优化器查询rows 字段 Explain的用法
Explain SQL语句
mysql explain select * from tbl_emp;
---------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------
| 1 | SIMPLE | tbl_emp | ALL | NULL | NULL | NULL | NULL | 8 | NULL |
---------------------------------------------------------------------------------
1 row in set (0.00 sec)四、Explain 详解
1、id id根据id字段你可以看到这条sql实际执行时的MySQL底层的加载顺序 id 取值的三种情况
id相同执行顺序由上至下 可以看到MySQL底层是先读取t1然后读取t3最后读取的t2 id不同id值越大优先级越高越先被执行 可以看到MySQL底层是先读取t3然后读取t1最后读取的t2 id既有相同的也有不同的id相同时可以认为是一组从上往下顺序执行id不同时id值越大优先级越高越先执行 可以看到MySQL底层是先读取t3然后读取DERIVED2最后读取的t2。 DERIVED是衍生的意思DERIVED2中的2是指基于id2的查询结果做衍生下图中DERIVED2的含义就是基于t3表的查询结果继续执行查询语句 2、select_type
select_type查询的类型主要用于区别普通查询、联合查询、子查询等复杂查询
SIMPLE简单的select查询查询中不包含子查询或者UNIONPRIMARY查询中若包含任何复杂的子部分最外层查询则被标记为PRIMARY。 SUBQUERY在SELECT或者WHERE列表中包含了子查询DERIVED在FROM列表中包含的子查询被标记为DERIVED衍生MySQL会递归执行这些子查询把结果放在临时表里UNION若第二个SELECT出现在UNION之后则被标记为UNION若UNION包含在FROM子句的子查询中外层SELECT将被标记为DERIVEDUNION RESULT从UNION表获取结果的SELECT UNION 和 UNION RESULT举例
explain- select * from tbl_emp e left join tbl_dept d on e.deptId d.id- union- select * from tbl_emp e right join tbl_dept d on e.deptId d.id;
---------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | e | ALL | NULL | NULL | NULL | NULL | 8 | NULL |
| 1 | PRIMARY | d | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where; Using join buffer (Block Nested Loop) |
| 2 | UNION | d | ALL | NULL | NULL | NULL | NULL | 5 | NULL |
| 2 | UNION | e | ref | fk_dept_Id | fk_dept_Id | 5 | db01.d.id | 1 | NULL |
| NULL | UNION RESULT | union1,2 | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
---------------------------------------------------------------------------------------------------------------------------------------------
5 rows in set (0.00 sec)
3、table
table显示这一行的数据是关于哪张表的 4、type type访问类型排列显示查询使用了何种类型 type显示的是访问类型是较为重要的一个指标结果值从最好到最坏依次是systemconsteq_refreffultextref_or_nullindex_mergeunique_subqueryindex_subqueryrangeindexALL挑重要的来说systemconsteq_refrefrangeindexALL一般来说得保证查询至少达到range级别最好能达到ref。 从最好到最差依次是systemconsteq_refrefrangeindexALL system一张表中只有一行记录才会出现的情况。那么这种表类似于系统表一样只有一条记录平时基本不会出现。 const表示通过索引一次就找到了const用于比较primary key或者unique索引。因为只匹配一行数据所以很快。如将主键置于where列表中MySQL就能将该查询转换为一个常量 eq_ref唯一性索引对于每个索引键表中只有一条记录与之匹配常见于主键或唯一索引扫描 下图中的sql语句执行时先全表扫描了t2然后以t2的数据为基石去匹配t1的数据匹配t1的数据时因为是一对一的关系所以就是eq_ref ref非唯一索引扫描返回匹配某个单独值的所有行。本质上也是一种索引访问它返回所有匹配某个单独值的行然而它可能会找到多个符合条件的行所以他应该属于查找和扫描的混合体 找t1表中col1为ac的数据由于是一对多所以type就是ref range在你的where语句中出现了between、、、in等的查询这种范围扫描索就是range类型因为他只需要开始索引的某一点而结束于另一点不用扫描全部数据 indexFull Index Scanindex与ALL区别为index类型是全索引扫描而all是全表扫描。index通常比ALL快因为索引文件通常比数据文件小。也就是说虽然all和index都是读全表但index是从索引中读取的而all是从硬盘数据库文件中读的 all将遍历全表以找到匹配的行全表扫描 备注一般来说得保证查询只是达到range级别最好达到ref
5、possible_keys
possible_keys显示可能会用到的索引若查询涉及的字段上存在索引则该索引将被列出但不一定被查询实际使用
好比是大摆筵席possible_keys就是你可能会摆多少桌但实际有多少桌这个看的是key
6、key 实际使用的索引如果为null则没有使用索引 若查询中使用了覆盖索引则该索引仅出现在key列表中 下图就很有意思程序判断可能用到的索引是空结果实际用到的索引是idx_col1_col2这种情况就是索引覆盖这是好事情我本来以为么有索引可以使用结果有。 7、key_len
key_len显示的值为索引最大可能长度一般来讲长度越短越好但是随着你查询条件的变多key_len会不可避免的变长。我找找山西里面的李华我要找山西太原的李华我要找山西太原小店区的李华由于你找的越来越精确所以key_len会不可避免的变长这是正常情况 8、ref
显示索引哪一列被使用了如果可能的话最好是一个常数。哪些列或常量被用于查找索引列上的值由key_len可知t1表的索引idx_col1_col2被充分使用t1表的col1匹配t2表的col1t1表的col2匹配了一个常量即’ac’ 下图的sql就是说我先全表扫描了t2然后以t2的数据为基石查找t1表中t1.col1 t2.col1 而且 t1.col2为ac的数据。 ref中的shared.t2.col1是啥意思它表示t1表的col1用到了shared数据库下t2表的col1字段。 ref中的const是啥意思它表示t1表的col2匹配了一个常量即’ac’。 也就是说查找t1表中t1.col1 t2.col1 而且 t1.col2为ac的数据时既用到shared数据库下t2表的col1字段 还用到了一个常量。 9、rows
根据表统计信息及索引选用情况大致估算出找到所需的记录所需要读取的行数 10、Extra
Extra包含不适合在其他列中显示但十分重要的额外信息这个是重点一定不能忽视 Extra可能有以下几个值 Using filesort文件排序 出现 Using filesort 不好九死一生需要尽快优化 SQLMySQL中无法利用索引完成排序操作成为“文件排序”说明mysql会对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取
你定义了一个索引然后MySQL底层按照你的索引给你排好序了结果你不仅没有用上这个索引你还和索引规定的东西反着来那么就会出现 Using filesort 这个非常不好需要尽快优化 SQL。举例你基于col1,col2,col3做了一个索引然后你select查询时要求按照col1的升序、col2的降序、col3的升序排序MySQL底层的B树是按照col1,col2,col3的升序排队的结果你查询语句是按照col1的升序、col2的降序、col3的升序排序你的要求有升又有降和底层不符合那么就会出现Using filesort 。 下图中\G表示竖着展示以前都是横着展示指标。你基于col1,col2,col3做了一个索引示例中第一个查询只使用了 col1然后按照col3做了排序原有索引派不上用场所以进行了外部文件排序。示例中第二个查询使用了 col1、col2 和col3原有索引派上用场无需进行文件排序 Using temporary创建临时表 使用了临时表保存中间结果MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by出现 Using temporary 超级不好十死无生需要立即优化 SQL 你基于col1,col2,col3做了一个索引示例中第一个查询只使用了col1原有索引派不上用场所以创建了临时表进行分组示例中第二个查询使用了 col1、col2原有索引派上用场无需创建临时表 Using index覆盖索引 表示相应的select操作中使用了覆盖索引Coveing Index避免访问了表的数据行效率不错 如果同时出现using where表明索引被用来执行索引键值的查找 如果没有同时出现using where表明索引用来读取数据而非执行查找动作 覆盖索引Covering Index也说为索引覆盖 理解方式一就是select的数据列只用从索引中就能够取得不必读取数据行MySQL可以利用索引返回select列表中的字段而不必根据索引再次读取数据文件换句话说查询列要被所建的索引覆盖。理解方式二索引是高效找到行的一个方法但是一般数据库也能使用索引找到一个列的数据因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据当能通过读取索引就可以得到想要的数据那就不需要读取行了。一个索引包含了或覆盖了满足查询结果的数据就叫做覆盖索引。注意如果要使用覆盖索引一定要注意select列表中只取出需要的列不可以用select * 因为如果将所有字段一起做索引会导致索引文件过大查询性能下降。 Using where表明使用了where过滤 Using join buffer表明使用了连接缓存 impossible wherewhere子句的值总是false不能用来获取任何元组 select tables optimized away在没有GROUPBY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化。 distinct优化distinct在找到第一匹配的元组后即停止找同样值的工作
11、小案例
第一行id为1但它的执行顺序是4id列为1select_type列的primary表示该查询为外层查询table列被标记为derived3表示查询结果来自一个衍生表其中derived3中3代表该查询衍生自id为3的select查询。【select d1.name ...】第二行id为3但执行顺序2id为3是整个查询中第三个select的一部分。因查询包含在from中所以为derived。【select id, name from t1 where other_column 】第三行id为2但执行顺序3select列表中的子查询select_type为subquery为整个查询中的第二个select。【select id from t3】第四行id为4但执行顺序1select_type为union说明第四个select是union里的第二个select最先执行【select name, id from t2】第五行id为null执行顺序5代表从union的临时表中读取行的阶段table列的union1, 4表示用第一个和第四个select的结果进行union操作。【两个结果进行uinion操作】 五、索引优化
1、单表索引优化 单表索引优化分析 创建表
建表 SQL
CREATE TABLE IF NOT EXISTS article(id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,author_id INT(10) UNSIGNED NOT NULL,category_id INT(10) UNSIGNED NOT NULL,views INT(10) UNSIGNED NOT NULL,comments INT(10) UNSIGNED NOT NULL,title VARCHAR(255) NOT NULL,content TEXT NOT NULL
);INSERT INTO article(author_id,category_id,views,comments,title,content)
VALUES
(1,1,1,1,1,1),
(2,2,2,2,2,2),
(1,1,3,3,3,3);表中的测试数据
mysql SELECT * FROM article;
-------------------------------------------------------------
| id | author_id | category_id | views | comments | title | content |
-------------------------------------------------------------
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 1 | 1 | 3 | 3 | 3 | 3 |
-------------------------------------------------------------
3 rows in set (0.00 sec)查询案例
查询category_id为1且comments 大于1的情况下views最多的article_id。
mysql SELECT id, author_id FROM article WHERE category_id 1 AND comments 1 ORDER BY views DESC LIMIT 1;
---------------
| id | author_id |
---------------
| 3 | 1 |
---------------
1 row in set (0.00 sec)此时 article 表中只有一个主键索引
mysql SHOW INDEX FROM article;
----------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
----------------------------------------------------------------------------------------------------------------------------------------------
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
----------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
1234567使用 explain 分析 SQL 语句的执行效率EXPLAIN SELECT id, author_id FROM article WHERE category_id 1 AND comments 1 ORDER BY views DESC LIMIT 1;
mysql EXPLAIN SELECT id, author_id FROM article WHERE category_id 1 AND comments 1 ORDER BY views DESC LIMIT 1;
-------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | article | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using filesort |
-------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)结论 很显然type是ALL即最坏的情况。Extra 里还出现了Using filesort也是最坏的情况。优化是必须的。 开始优化新建索引
创建索引的 SQL 命令
# ALTER TABLE article ADD INDEX idx_article_ccv(category_id, comments, views);
create index idx_article_ccv on article(category_id, comments, views);在 category_id 列、comments 列和 views 列上建立联合索引
mysql create index idx_article_ccv on article(category_id, comments, views);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql SHOW INDEX FROM article;
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 1 | category_id | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 2 | comments | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 3 | views | A | 3 | NULL | NULL | | BTREE | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
4 rows in set (0.00 sec)再次执行查询type变成了range这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。
mysql EXPLAIN SELECT id, author_id FROM article WHERE category_id 1 AND comments 1 ORDER BY views DESC LIMIT 1;
-------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | article | range | idx_article_ccv | idx_article_ccv | 8 | NULL | 1 | Using index condition; Using filesort |
-------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)分析 但是我们已经建立了索引为啥没用呢这是因为按照BTree索引的工作原理先排序 category_id如果遇到相同的 category_id 则再排序comments如果遇到相同的 comments 则再排序 views。当comments字段在联合索引里处于中间位置时因为comments1条件是一个范围值所谓 rangeMySQL 无法利用索引再对后面的views部分进行检索即 range 类型查询字段后面的索引无效。 将查询条件中的 comments 1 改为 comments 1 发现 Use filesort 神奇地消失了从这点可以验证范围查询会导致索引失效范围查询会导致索引失效但是并不是说该索引就没有用到。你的索引顺序是ctegory_id、comments、views你的查询语句是category_id 1 AND comments 1 ORDER BY views 这个comments 1是个范围会导致索引失效但是它是从comments后面才失效的ctegory_id和comments还是用到索引了的。
mysql EXPLAIN SELECT id, author_id FROM article WHERE category_id 1 AND comments 1 ORDER BY views DESC LIMIT 1;
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | article | ref | idx_article_ccv | idx_article_ccv | 8 | const,const | 1 | Using where |
-----------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)删除索引
删除索引的 SQL 指令
DROP INDEX idx_article_ccv ON article;删除刚才创建的 idx_article_ccv 索引
mysql DROP INDEX idx_article_ccv ON article;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql SHOW INDEX FROM article;
----------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
----------------------------------------------------------------------------------------------------------------------------------------------
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
----------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)再次创建索引
创建索引的 SQL 指令
# ALTER TABLE article ADD INDEX idx_article_ccv(category_id, views);
create index idx_article_ccv on article(category_id, views);由于 range 后comments 1的索引会失效这次我们建立索引时直接抛弃 comments 列先利用 category_id 和 views 的联合索引查询所需要的数据再从其中取出 comments 1 的数据我觉着应该是这样的
mysql create index idx_article_ccv on article(category_id, views);
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql SHOW INDEX FROM article;
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 1 | category_id | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 2 | views | A | 3 | NULL | NULL | | BTREE | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)再次执行查询可以看到type变为了refExtra中的Using filesort也消失了结果非常理想
ysql EXPLAIN SELECT id, author_id FROM article WHERE category_id 1 AND comments 1 ORDER BY views DESC LIMIT 1;
-----------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------
| 1 | SIMPLE | article | ref | idx_article_ccv | idx_article_ccv | 4 | const | 2 | Using where |
-----------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)为了不影响之后的测试删除该表的 idx_article_ccv 索引
mysql DROP INDEX idx_article_ccv ON article;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql SHOW INDEX FROM article;
----------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
----------------------------------------------------------------------------------------------------------------------------------------------
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
----------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)2、两表索引优化 两表索引优化分析主外键 创建表
建表 SQL
CREATE TABLE IF NOT EXISTS class(id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,card INT(10) UNSIGNED NOT NULL,PRIMARY KEY(id)
);CREATE TABLE IF NOT EXISTS book(bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,card INT(10) UNSIGNED NOT NULL,PRIMARY KEY(bookid)
);INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1(RAND()*20)));class 表中的测试数据
mysql select * from class;
----------
| id | card |
----------
| 1 | 12 |
| 2 | 13 |
| 3 | 12 |
| 4 | 17 |
| 5 | 11 |
| 6 | 3 |
| 7 | 1 |
| 8 | 16 |
| 9 | 17 |
| 10 | 16 |
| 11 | 9 |
| 12 | 17 |
| 13 | 18 |
| 14 | 16 |
| 15 | 7 |
| 16 | 8 |
| 17 | 19 |
| 18 | 9 |
| 19 | 6 |
| 20 | 5 |
| 21 | 6 |
----------
21 rows in set (0.00 sec)book 表中的测试数据
mysql select * from book;
--------------
| bookid | card |
--------------
| 1 | 16 |
| 2 | 1 |
| 3 | 17 |
| 4 | 3 |
| 5 | 20 |
| 6 | 12 |
| 7 | 18 |
| 8 | 13 |
| 9 | 13 |
| 10 | 4 |
| 11 | 1 |
| 12 | 13 |
| 13 | 20 |
| 14 | 20 |
| 15 | 1 |
| 16 | 2 |
| 17 | 9 |
| 18 | 16 |
| 19 | 14 |
| 20 | 2 |
--------------
20 rows in set (0.00 sec)查询案例
实现两表的连接连接条件是 class.card book.card
mysql SELECT * FROM class LEFT JOIN book ON class.card book.card;
------------------------
| id | card | bookid | card |
------------------------
| 1 | 12 | 6 | 12 |
| 2 | 13 | 8 | 13 |
| 2 | 13 | 9 | 13 |
| 2 | 13 | 12 | 13 |
| 3 | 12 | 6 | 12 |
| 4 | 17 | 3 | 17 |
| 5 | 11 | NULL | NULL |
| 6 | 3 | 4 | 3 |
| 7 | 1 | 2 | 1 |
| 7 | 1 | 11 | 1 |
| 7 | 1 | 15 | 1 |
| 8 | 16 | 1 | 16 |
| 8 | 16 | 18 | 16 |
| 9 | 17 | 3 | 17 |
| 10 | 16 | 1 | 16 |
| 10 | 16 | 18 | 16 |
| 11 | 9 | 17 | 9 |
| 12 | 17 | 3 | 17 |
| 13 | 18 | 7 | 18 |
| 14 | 16 | 1 | 16 |
| 14 | 16 | 18 | 16 |
| 15 | 7 | NULL | NULL |
| 16 | 8 | NULL | NULL |
| 17 | 19 | NULL | NULL |
| 18 | 9 | 17 | 9 |
| 19 | 6 | NULL | NULL |
| 20 | 5 | NULL | NULL |
| 21 | 6 | NULL | NULL |
------------------------
28 rows in set (0.00 sec)使用 explain 分析 SQL 语句的性能可以看到驱动表是左表 class 表
mysql EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card book.card;
----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 21 | NULL |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) |
----------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)结论 type 有 All rows 为表中数据总行数说明 class 和 book 进行了全表检索即每次 class 表对 book 表进行左外连接时都需要在 book 表中进行一次全表检索 添加索引在右表添加索引
添加索引的 SQL 指令
ALTER TABLE book ADD INDEX Y (card);在 book 的 card 字段上添加索引
mysql ALTER TABLE book ADD INDEX Y (card);
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql SHOW INDEX FROM book;
--------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
--------------------------------------------------------------------------------------------------------------------------------------------
| book | 0 | PRIMARY | 1 | bookid | A | 20 | NULL | NULL | | BTREE | | |
| book | 1 | Y | 1 | card | A | 20 | NULL | NULL | | BTREE | | |
--------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)测试结果可以看到第二行的type变为了refrows也变成了优化比较明显。
mysql EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card book.card;
------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 21 | NULL |
| 1 | SIMPLE | book | ref | Y | Y | 4 | db01.class.card | 1 | Using index |
------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)分析 这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行左边一定都有所以右边是我们的关键点一定需要建立索引。左表连接右表则需要拿着左表的数据去右表里面查索引需要在右表中建立索引 添加索引在右表添加索引
删除之前 book 表中的索引
DROP INDEX Y ON book;在 class 表的 card 字段上建立索引
ALTER TABLE class ADD INDEX X(card);再次执行左连接凉凉~~~
mysql EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card book.card;
-----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | class | index | NULL | X | 4 | NULL | 21 | Using index |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) |
-----------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)别怕我们来执行右连接可以看到第二行的type变为了refrows也变成了优化比较明显。
mysql EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card book.card;
-----------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | NULL |
| 1 | SIMPLE | class | ref | X | X | 4 | db01.book.card | 1 | Using index |
-----------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)分析 这是因为RIGHT JOIN条件用于确定如何从左表搜索行右边一定都有所以左边是我们的关键点一定需要建立索引。class RIGHT JOIN book book 里面的数据一定存在于结果集中我们需要拿着 book 表中的数据去 class 表中搜索所以索引需要建立在 class 表中 为了不影响之后的测试删除该表的 idx_article_ccv 索引
mysql DROP INDEX X ON class;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql SHOW INDEX FROM class;
--------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
--------------------------------------------------------------------------------------------------------------------------------------------
| class | 0 | PRIMARY | 1 | id | A | 21 | NULL | NULL | | BTREE | | |
--------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)3、三表索引优化 三表索引优化分析 创建表
建表 SQL
CREATE TABLE IF NOT EXISTS phone(phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,card INT(10) UNSIGNED NOT NULL,PRIMARY KEY(phoneid)
)ENGINEINNODB;INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1(RAND()*20)));phone 表中的测试数据
mysql select * from phone;
---------------
| phoneid | card |
---------------
| 1 | 7 |
| 2 | 7 |
| 3 | 13 |
| 4 | 6 |
| 5 | 8 |
| 6 | 4 |
| 7 | 16 |
| 8 | 4 |
| 9 | 15 |
| 10 | 1 |
| 11 | 20 |
| 12 | 18 |
| 13 | 9 |
| 14 | 9 |
| 15 | 20 |
| 16 | 11 |
| 17 | 15 |
| 18 | 3 |
| 19 | 8 |
| 20 | 10 |
---------------
20 rows in set (0.00 sec)查询案例
实现三表的连接查询
mysql SELECT * FROM class LEFT JOIN book ON class.card book.card LEFT JOIN phone ON book.card phone.card;
---------------------------------------
| id | card | bookid | card | phoneid | card |
---------------------------------------
| 2 | 13 | 8 | 13 | 3 | 13 |
| 2 | 13 | 9 | 13 | 3 | 13 |
| 2 | 13 | 12 | 13 | 3 | 13 |
| 8 | 16 | 1 | 16 | 7 | 16 |
| 10 | 16 | 1 | 16 | 7 | 16 |
| 14 | 16 | 1 | 16 | 7 | 16 |
| 8 | 16 | 18 | 16 | 7 | 16 |
| 10 | 16 | 18 | 16 | 7 | 16 |
| 14 | 16 | 18 | 16 | 7 | 16 |
| 7 | 1 | 2 | 1 | 10 | 1 |
| 7 | 1 | 11 | 1 | 10 | 1 |
| 7 | 1 | 15 | 1 | 10 | 1 |
| 13 | 18 | 7 | 18 | 12 | 18 |
| 11 | 9 | 17 | 9 | 13 | 9 |
| 18 | 9 | 17 | 9 | 13 | 9 |
| 11 | 9 | 17 | 9 | 14 | 9 |
| 18 | 9 | 17 | 9 | 14 | 9 |
| 6 | 3 | 4 | 3 | 18 | 3 |
| 4 | 17 | 3 | 17 | NULL | NULL |
| 9 | 17 | 3 | 17 | NULL | NULL |
| 12 | 17 | 3 | 17 | NULL | NULL |
| 1 | 12 | 6 | 12 | NULL | NULL |
| 3 | 12 | 6 | 12 | NULL | NULL |
| 5 | 11 | NULL | NULL | NULL | NULL |
| 15 | 7 | NULL | NULL | NULL | NULL |
| 16 | 8 | NULL | NULL | NULL | NULL |
| 17 | 19 | NULL | NULL | NULL | NULL |
| 19 | 6 | NULL | NULL | NULL | NULL |
| 20 | 5 | NULL | NULL | NULL | NULL |
| 21 | 6 | NULL | NULL | NULL | NULL |
---------------------------------------
30 rows in set (0.00 sec)使用 explain 分析 SQL 指令
mysql EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card book.card LEFT JOIN phone ON book.card phone.card;
----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 21 | NULL |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | phone | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) |
----------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)结论 type 有All rows 为表数据总行数说明 class、 book 和 phone 表都进行了全表检索Extra 中 Using join buffer 表明连接过程中使用了 join 缓冲区 创建索引
创建索引的 SQL 语句
ALTER TABLE book ADD INDEX Y (card);
ALTER TABLE phone ADD INDEX Z (card);进行 LEFT JOIN 永远都在右表的字段上建立索引
mysql ALTER TABLE book ADD INDEX Y (card);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql SHOW INDEX FROM book;
--------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
--------------------------------------------------------------------------------------------------------------------------------------------
| book | 0 | PRIMARY | 1 | bookid | A | 20 | NULL | NULL | | BTREE | | |
| book | 1 | Y | 1 | card | A | 20 | NULL | NULL | | BTREE | | |
--------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)mysql ALTER TABLE phone ADD INDEX Z (card);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql SHOW INDEX FROM phone;
--------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
--------------------------------------------------------------------------------------------------------------------------------------------
| phone | 0 | PRIMARY | 1 | phoneid | A | 20 | NULL | NULL | | BTREE | | |
| phone | 1 | Z | 1 | card | A | 20 | NULL | NULL | | BTREE | | |
--------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)执行查询后2行的type都是ref且总rows优化很好效果不错。因此索引最好设置在需要经常查询的字段中。
mysql EXPLAIN SELECT * FROM class LEFT JOIN book ON class.cardbook.card LEFT JOIN phone ON book.card phone.card;
------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 21 | NULL |
| 1 | SIMPLE | book | ref | Y | Y | 4 | db01.class.card | 1 | Using index |
| 1 | SIMPLE | phone | ref | Z | Z | 4 | db01.book.card | 1 | Using index |
------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)Join 语句优化的结论 将 left join 看作是两层嵌套 for 循环
尽可能减少Join语句中的NestedLoop的循环总次数永远用小结果集驱动大的结果集在大结果集中建立索引在小结果集中遍历全表优先优化NestedLoop的内层循环保证Join语句中被驱动表上Join条件字段已经被索引当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下不要太吝惜JoinBuffer的设置 我的理解
使用小表驱动大表这就相当于外层 for 循环的次数少内层 for 循环的次数多然后我们在大表中建立了索引这样内层 for 循环的效率明显提高综上使用小表驱动大表在大表中建立了索引
六、索引失效
索引失效判断准则
全值匹配我最爱带头大哥不能死中间兄弟不能断如果索引包含多列查询从索引的最左前列开始并且不要跳过索引中的列。不在索引列上做任何操作函数、计算、类型转换等操作会导致索引失效而转向全表扫描存储引擎不能使用索引中范围条件右边的列索引列和查询列尽量一致减少select *mysql在使用不等于!或者的时候无法使用索引会导致全表扫描is nullis not null 也无法使用索引早期版本不能走索引后续版本应该优化过可以走索引like操作时%开头会导致索引失效like %abc%还有like %abc会导致索引失效但是like abc%不会导致索引失效字符串不加单引号会导致索引失效少用or用它连接时会索引失效
先建一张表用来做案例
建表 SQL
CREATE TABLE staffs(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(24)NOT NULL DEFAULT COMMENT姓名,age INT NOT NULL DEFAULT 0 COMMENT年龄,pos VARCHAR(20) NOT NULL DEFAULT COMMENT职位,add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT入职时间
)CHARSET utf8 COMMENT员工记录表;INSERT INTO staffs(name,age,pos,add_time) VALUES(z3,22,manager,NOW());
INSERT INTO staffs(name,age,pos,add_time) VALUES(July,23,dev,NOW());
INSERT INTO staffs(name,age,pos,add_time) VALUES(2000,23,dev,NOW());ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos);staffs 表中的测试数据
mysql select * from staffs;
---------------------------------------------
| id | name | age | pos | add_time |
---------------------------------------------
| 1 | z3 | 22 | manager | 2020-08-04 14:42:33 |
| 2 | July | 23 | dev | 2020-08-04 14:42:33 |
| 3 | 2000 | 23 | dev | 2020-08-04 14:42:33 |
---------------------------------------------
3 rows in set (0.00 sec)staffs 表中的复合索引name、age、pos
mysql SHOW INDEX FROM staffs;
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
4 rows in set (0.00 sec)1、带头大哥不能死中间兄弟不能断
staffs 表中的复合索引name、age、pos你只根据name去找是可以走索引的
mysql EXPLAIN SELECT * FROM staffs WHERE name July;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |
------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)key index_staffs_nameAgePos 表明索引生效ref const 这个常量就是查询时的 ‘July’ 字符串常量可以看到有用到索引带头大哥 name 带上小弟 agestaffs 表中的复合索引name、age、pos你根据name和age去找也是走索引的 key index_staffs_nameAgePos 表明索引生效ref const,const两个常量分别为 ‘July’ 和 23
mysql EXPLAIN SELECT * FROM staffs WHERE name JulyAND age 23;
------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | Using index condition |
------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)带头大哥 name 带上小弟 age 小弟 age 带上小小弟 posstaffs 表中的复合索引name、age、pos你根据name和age和pos去找也是走索引的 key index_staffs_nameAgePos 表明索引生效ref const,const,const 三个常量分别为 ‘July’、23 和 ‘dev’
mysql EXPLAIN SELECT * FROM staffs WHERE name JulyAND age 23 AND pos dev;
------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using index condition |
------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)带头大哥 name 挂了staffs 表中的复合索引name、age、pos你根据age和pos去找就索引失效了 key NULL 说明索引失效ref null 表示 ref 也失效
mysql EXPLAIN SELECT * FROM staffs WHERE age 23 AND pos dev;
--------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
--------------------------------------------------------------------------------------
1 row in set (0.00 sec)带头大哥 name 没挂小弟 age 跑了staffs 表中的复合索引name、age、pos你根据name和pos去找就只用到name索引pos没有走索引 key index_staffs_nameAgePos 说明索引没有失效ref const 表明只使用了一个常量即第二个常量pos ‘dev’没有生效
mysql EXPLAIN SELECT * FROM staffs WHERE name JulyAND pos dev;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |
------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)2、不在索引列上做任何操作函数、计算、类型转换等操作
不对带头大哥 name 进行任何操作key index_staffs_nameAgePos 表明索引生效
mysql EXPLAIN SELECT * FROM staffs WHERE name July;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |
------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)对带头大哥 name 进行操作使用 LEFT 函数截取子串 key NULL 表明索引生效type ALL 表明进行了全表扫描
mysql EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4) July;
--------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
--------------------------------------------------------------------------------------
1 row in set (0.00 sec)3、范围之后的索引字段全失效
精确匹配 type ref 表示非唯一索引扫描SQL 语句将返回匹配某个单独值的所有行。key_len 140 表明表示索引中使用的字节数
mysql EXPLAIN SELECT * FROM staffs WHERE name JulyAND age 23 AND pos dev;
------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using index condition |
------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)将 age 改为范围匹配 type range 表示范围扫描key index_staffs_nameAgePos 表示索引并没有失效key_len 78 ref NULL 均表明范围搜索使其后面的索引均失效
mysql EXPLAIN SELECT * FROM staffs WHERE name JulyAND age 23 AND pos dev;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | Using index condition |
------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)4、索引列和查询列尽量一致减少select *
SELECT * 的写法
mysql EXPLAIN SELECT * FROM staffs WHERE name JulyAND age 23 AND pos dev;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | Using index condition |
------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)覆盖索引的写法Extra Using where; Using index Using index 表示使用索引列进行查询将大大提高查询的效率
mysql EXPLAIN SELECT name, age, pos FROM staffs WHERE name JulyAND age 23 AND pos dev;
---------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using where; Using index |
---------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)覆盖索引中包含 range 条件type ref 并且 Extra Using where; Using index 虽然在查询条件中使用了 范围搜索但是由于select的字段是索引列所以无需进行全表扫描
mysql EXPLAIN SELECT name, age, pos FROM staffs WHERE name JulyAND age 23 AND pos dev;
---------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using where; Using index |
---------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)5、mysql在使用不等于!或者的时候无法使用索引会导致全表扫描
在使用 ! 会 时会导致索引失效 key null 表示索引失效rows 3 表示进行了全表扫描
mysql EXPLAIN SELECT * FROM staffs WHERE name ! July;
------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql EXPLAIN SELECT * FROM staffs WHERE name July;
------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)6、is nullis not null 也无法使用索引
is nullis not null 会导致索引失效key null 表示索引失效
ysql EXPLAIN SELECT * FROM staffs WHERE name is null;
------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql EXPLAIN SELECT * FROM staffs WHERE name is not null;
------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)7、 like操作时%开头会导致索引失效like %abc%还有like %abc会导致索引失效但是like abc%不会导致索引失效
staffs 表的索引关系
mysql SHOW INDEX from staffs;
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
4 rows in set (0.00 sec)like % 写在左边的情况 type All rows 3 表示进行了全表扫描key null 表示索引失效
mysql EXPLAIN SELECT * FROM staffs WHERE name like %July;
--------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
--------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql EXPLAIN SELECT * FROM staffs WHERE name like %July%;
--------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
--------------------------------------------------------------------------------------
1 row in set (0.00 sec)like % 写在右边的情况key index_staffs_nameAgePos 表示索引未失效
mysql EXPLAIN SELECT * FROM staffs WHERE name like July%;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | NULL | 1 | Using index condition |
------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)8、解决【like ‘%str%’ 】索引失效的问题——覆盖索引
创建表
建表 SQL
CREATE TABLE tbl_user(id INT(11) NOT NULL AUTO_INCREMENT,name VARCHAR(20) DEFAULT NULL,ageINT(11) DEFAULT NULL,email VARCHAR(20) DEFAULT NULL,PRIMARY KEY(id)
)ENGINEINNODB AUTO_INCREMENT1 DEFAULT CHARSETutf8;INSERT INTO tbl_user(name,age,email)VALUES(1aa1,21,a163.com);
INSERT INTO tbl_user(name,age,email)VALUES(2bb2,23,b163.com);
INSERT INTO tbl_user(name,age,email)VALUES(3cc3,24,c163.com);
INSERT INTO tbl_user(name,age,email)VALUES(4dd4,26,d163.com);tbl_user 表中的测试数据
mysql select * from tbl_user;
---------------------------
| id | name | age | email |
---------------------------
| 1 | 1aa1 | 21 | a163.com |
| 2 | 2bb2 | 23 | b163.com |
| 3 | 3cc3 | 24 | c163.com |
| 4 | 4dd4 | 26 | d163.com |
---------------------------
4 rows in set (0.00 sec)创建索引
创建索引的 SQL 指令
CREATE INDEX idx_user_nameAge ON tbl_user(name, age);在 tbl_user 表的 name 字段和 age 字段创建联合索引
mysql CREATE INDEX idx_user_nameAge ON tbl_user(name, age);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql SHOW INDEX FROM tbl_user;
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| tbl_user | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| tbl_user | 1 | idx_user_nameAge | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
| tbl_user | 1 | idx_user_nameAge | 2 | age | A | 4 | NULL | NULL | YES | BTREE | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)测试覆盖索引
如下 SQL 的索引均不会失效 只要查询的字段是索引或主键字段并且没有多余字段覆盖索引就不会失效。所有当你万不得已要使用like查询时你可以限制查询的字段是id或索引字段这样索引就不会失效。
EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE %aa%; EXPLAIN SELECT name FROM tbl_user WHERE NAME LIKE %aa%;
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE %aa%;EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE %aa%;
EXPLAIN SELECT id, name FROM tbl_user WHERE NAME LIKE %aa%;
EXPLAIN SELECT id, age FROM tbl_user WHERE NAME LIKE %aa%;
EXPLAIN SELECT id, name, age FROM tbl_user WHERE NAME LIKE %aa%;mysql EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE %aa%;
------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index |
------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE %aa%;
------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index |
------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
如下 SQL 的索引均会失效但凡有多余字段覆盖索引就会失效
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE %aa%;
EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE %aa%;mysql EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE %aa%;
----------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------
| 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
----------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE %aa%;
----------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------
| 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
----------------------------------------------------------------------------------------
1 row in set (0.00 sec)9、字符串不加单引号索引失效
正常操作索引没有失效
mysql SHOW INDEX FROM staffs;
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
4 rows in set (0.00 sec)mysql explain select * from staffs where name2000;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |
------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)如果字符串忘记写 ‘’ 那么 mysql 会为我们进行隐式的类型转换但凡进行了类型转换索引都会失效
mysql explain select * from staffs where name2000;
------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)10、少用or用它连接时会索引失效
使用 or 连接会导致索引失效
mysql SHOW INDEX FROM staffs;
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
4 rows in set (0.00 sec)mysql explain select * from staffs where namez3 or name July;
------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)11、索引优化面试题
创建表
建表 SQL
create table test03(id int primary key not null auto_increment,c1 char(10),c2 char(10),c3 char(10),c4 char(10),c5 char(10)
);insert into test03(c1,c2,c3,c4,c5) values (a1,a2,a3,a4,a5);
insert into test03(c1,c2,c3,c4,c5) values (b1,b2,b3,b4,b5);
insert into test03(c1,c2,c3,c4,c5) values (c1,c2,c3,c4,c5);
insert into test03(c1,c2,c3,c4,c5) values (d1,d2,d3,d4,d5);
insert into test03(c1,c2,c3,c4,c5) values (e1,e2,e3,e4,e5);create index idx_test03_c1234 on test03(c1,c2,c3,c4);test03 表中的测试数据
mysql select * from test03;
----------------------------------
| id | c1 | c2 | c3 | c4 | c5 |
----------------------------------
| 1 | a1 | a2 | a3 | a4 | a5 |
| 2 | b1 | b2 | b3 | b4 | b5 |
| 3 | c1 | c2 | c3 | c4 | c5 |
| 4 | d1 | d2 | d3 | d4 | d5 |
| 5 | e1 | e2 | e3 | e4 | e5 |
----------------------------------
5 rows in set (0.00 sec)test03 表中的索引
mysql SHOW INDEX FROM test03;
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| test03 | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 3 | c3 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
5 rows in set (0.00 sec)问题我们创建了复合索引idx_test03_c1234根据以下SQL分析下索引使用情况 EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 AND c3a3 AND c4a4;即全值匹配索引不会失效
mysql EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 AND c3a3 AND c4a4;
----------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using index condition |
----------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c4a4 AND c3a3 AND c2a2 AND c1a1;mysql 优化器进行了优化所以我们的索引都生效了
mysql EXPLAIN SELECT * FROM test03 WHERE c4a4 AND c3a3 AND c2a2 AND c1a1;
----------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using index condition |
----------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 AND c3a3 AND c4a4;c3 列使用了索引进行排序并没有进行查找导致 c4 无法用索引进行查找c1、c2、c3是用到索引的c4没有
mysql EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 AND c3a3 AND c4a4;
----------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93 | NULL | 1 | Using index condition |
----------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 AND c4a4 AND c3a3;mysql 优化器进行了优化所以我们的索引都生效了在 c4 时进行了范围搜索c1、c2、c3和c4都用到索引了
mysql EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 AND c4a4 AND c3a3;
----------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 124 | NULL | 1 | Using index condition |
----------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 AND c4a4 ORDER BY c3;c3 列将索引用于排序而不是查找c4 列没有用到索引
mysql EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 AND c4a4 ORDER BY c3;
-----------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
-----------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 ORDER BY c3;那不就和上面一样的嘛~~~c4 列都没有用到索引
mysql EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 ORDER BY c3;
-----------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
-----------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 ORDER BY c4;因为索引建立的顺序c1、c2、c3、c4和使用的顺序c1、c2、c4不一致导致 mysql 动用了文件排序看到 Using filesort 就要知道此句 SQL 必须优化
mysql EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 ORDER BY c4;
---------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where; Using filesort |
---------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c5a5 ORDER BY c2, c3;c1用于查找c2、c3用于排序c1、c2、c3都用到索引了无filesort
mysql EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c5a5 ORDER BY c2, c3;
-----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where |
-----------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c5a5 ORDER BY c3, c2;出现了filesort因为索引建立的顺序c1、c2、c3、c4和使用的顺序c1、c3、c2不一致导致 mysql 动用了文件排序
mysql EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c5a5 ORDER BY c3, c2;
---------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where; Using filesort |
---------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 ORDER BY c2, c3;用c1、c2两个字段索引但是c2、c3用于排序c1、c2、c3都用到索引了无filesort
mysql EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 ORDER BY c2, c3;
-----------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
-----------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 AND c5a5 ORDER BY c2, c3;用c1、c2两个字段做查询c2、c3用于排序c1、c2、c3都用到索引了无filesort和c5没有关系c5不会影响什么。
mysql EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 AND c5a5 ORDER BY c2, c3;
-----------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
-----------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 AND c5a5 ORDER BY c3, c2;用c1、c2两个字段做查询c3、c2用于排序c1、c2、c3都用到索引了无filesort
mysql EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c2a2 AND c5a5 ORDER BY c3, c2;
-----------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
-----------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c4a4 GROUP BY c2, c3;这次是group by顺序为 1 2 3 没有产生文件排序
mysql EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c4a4 GROUP BY c2, c3;
-----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where |
-----------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c4a4 GROUP BY c3, c2;这次是group bygroup by 表面上叫分组分组之前必排序group by 和 order by 在索引上的问题基本是一样的。索引建立顺序c1234和使用顺序c132不一致导致性能差Using temporary; Using filesort 两个都有我只能说是灭绝师太性能很差
mysql EXPLAIN SELECT * FROM test03 WHERE c1a1 AND c4a4 GROUP BY c3, c2;
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where; Using temporary; Using filesort |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)结论 group by 基本上都需要进行排序但凡使用不当会有临时表产生定值为常量、范围之后失效最终看排序的顺序
12、索引失效总结 七、in和Exists语句怎么使用性能好
永远小表驱动大表
select * from A where id in (select id from B)上面的语句你可以理解为先查B表然后查询A表所以B表数据集小于A表的数据集时用in的效率要高小表驱动大表select * from A where id in (select id from B)
等价于
select * from A where exists (select 1 from B where B.id A.id)对于exists语句 select * from A where exists (select 1 from B where B.id A.id)
你可以理解为先查A表然后将A表的数据在exists语句后面做过滤所以查询顺序是先查A表后查B表。所以A表数据集小于B表的数据集时用exists的效率要高小表驱动大表八、ORDER BY 优化
MySQL支持二种方式的排序FileSort和IndexIndex效率高它指MySQL扫描索引本身完成排序FileSort方式效率较低。 ORDER BY子句尽量使用Index方式排序避免使用FileSort方式排序 创建表
建表 SQL
create table tblA(#id int primary key not null auto_increment,age int,birth timestamp not null
);insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());create index idx_A_ageBirth on tblA(age, birth);
tblA 表中的测试数据
mysql select * from tblA;
---------------------------
| age | birth |
---------------------------
| 22 | 2020-08-05 10:36:32 |
| 23 | 2020-08-05 10:36:32 |
| 24 | 2020-08-05 10:36:32 |
---------------------------
3 rows in set (0.00 sec)tbl 中的索引
mysql SHOW INDEX FROM tblA;
--------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
--------------------------------------------------------------------------------------------------------------------------------------------------
| tblA | 1 | idx_A_ageBirth | 1 | age | A | 3 | NULL | NULL | YES | BTREE | | |
| tblA | 1 | idx_A_ageBirth | 2 | birth | A | 3 | NULL | NULL | | BTREE | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)CASE1能使用索引进行排序的情况
只有带头大哥 age
mysql EXPLAIN SELECT * FROM tblA where age20 order by age;
--------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index |
--------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)mysql EXPLAIN SELECT * FROM tblA where birth2016-01-28 00:00:00 order by age;
-------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index |
-------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)带头大哥 age 小弟 birth
mysql EXPLAIN SELECT * FROM tblA where age20 order by age,birth;
--------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index |
--------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql 默认升序排列全升序或者全降序都扛得住方向要一致同升同降不能说有升有降
mysql EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth ASC;
------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index |
------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql EXPLAIN SELECT * FROM tblA ORDER BY age DESC, birth DESC;
------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index |
------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)CASE2不能使用索引进行排序的情况
带头大哥 age 挂了
mysql EXPLAIN SELECT * FROM tblA where age20 order by birth;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index; Using filesort |
------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)
小弟 birth 居然敢在带头大哥 age 前面
mysql EXPLAIN SELECT * FROM tblA where age20 order by birth,age;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index; Using filesort |
------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
mysql 默认升序排列如果全升序或者全降序都 ok 但是一升一降 mysql 就扛不住了
mysql EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth DESC;
----------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index; Using filesort |
----------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec) 遵循如下规则可提高Order By的速度
Order by时select *是一个大忌只查询要的字段最好是索引字段这点非常重要。在这里的影响是
Order By 排序索引优化的总结 group by关键字优化
group by实质是先排序后进行分组遵照索引的最佳左前缀where高于having能写在where限定的条件就不要去having限定了其余的规则均和 order by 一致
