mysql索引优化详解
文章目录
2.4. 不能使用索引中范围条件右边的列（范围之后的索引全失效）
2.6. mysql在使用不等于(!=或者<>)的时候无法使用导致全表扫描
2.8. is null和is not null 导致索引失效
Explain
- 使用explain能够知道自己写的sql语句在mysql中到底是怎样运行的，到底扫描了多少行，是否使用了索引，返回的结果如下：
+------+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t_blogger | ALL | NULL | NULL | NULL | NULL | 2 | |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------+- 下面将会针对上面的值详细讲解
id
- sql执行查询的序列号，决定了查询中select子句的查询顺序，分为三种情况，如下：
id相同
- 查询的select子句从上到到下执行，如下：
MariaDB [db_blog3]> explain select * from t_blog ,t_blogger;
+------+-------------+-----------+------+---------------+------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+------+------------------------------------+
| 1 | SIMPLE | t_blogger | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | SIMPLE | t_blog | ALL | NULL | NULL | NULL | NULL | 16 | Using join buffer (flat, BNL join) |
+------+-------------+-----------+------+---------------+------+---------+------+------+------------------------------------+- 那么执行的孙顺序就是先查询
t_blogger，之后查询t_blog
id不同
- id的值越大优先级越高，就先执行，剩下相同的id的值，按照顺序从上到下执行
table
- select语句执行查询的表，如果是使用联合查询的，那么会使用这个值可能是虚拟的表
索引优化
全值匹配
- 全部使用了索引，并且如果是复合索引，一定要按照复合索引的顺序查询，这样才能达到最高效的查询，如下：
-- 为user表创建组合索引 index_nameAgePos
-- 全值匹配的实例 ,查询的条件的顺序必须和创建索引的顺序一致
select * from t_user where name="Tom" and age=22 and pos="1"最佳左前缀法则
- 如果使用了组合索引（索引了多列） ，那么一定查询要从最左前列开始并且不能跳过索引中的列
- 比如
index_nameAgePos这个索引，实例如下：
-- 全值匹配，最为高效
explain select * from t_user where name="Tom" and age=22 and pos="1"
-- 去掉最后一个，使用前两个，那么前两个索引会有效，使用了部分索引
explain select * from t_user where name="Tom" and age=22
-- 去掉后面两个，只是用第一个，索引依然有效，使用了第一个索引的类，部分索引
explain select * from t_user where name="Tom"
-- 去掉第一个，使用后面两个索引查询，没有使用做前缀，索引失效，
explain select * from t_user where and age=22 and pos="1"
-- 去掉中间的一个，只使用第一个和第三个,中间断了，不能查找到索引，索引失效，即使有了做前缀依然会失效
explain select * from t_user where name="Tom" and pos="1"- 通过上面的例子得出：使用组合索引的时候，一定要带上左前缀，并且不能跳过中间的索引，否则将会索引失效
不在索引上列上做任何操作
- 不要在索引列上做任何的操作，包括计算、函数、自动或者手动类型的转换，这样都会导致索引失效
select * from user where name=2000 ---- 我们知道name是一个varchar类型的，但是用name=2000虽然能够查到，但是在内部其实是将name转换成了数值类型，因此不能使用索引
select * from user where left(name,4)="TOm" -- 这里将对name使用了left这个函数，索引失效不能使用索引中范围条件右边的列（范围之后的索引全失效）
- 在使用组合索引的时候，一旦索引中有列使用了范围查询（>=…in….like,between子句），那么在其右边的索引将会失效
- 假设创建了组合索引，顺序为name，age，address
-- age使用了范围查询，那么在其右边的address将不会使用索引查询，但是name和age使用了索引
explain select age from user where name="JOhn" and age>22 and address="江苏"；使用覆盖索引，少使用select*
- 需要用到什么数据就查询什么数据，这样可以减少网络的传输和mysql的全表扫描
- 尽量使用覆盖索引，比如索引为
name，age，address的组合索引，那么尽量覆盖这三个字段之中的值，mysql将会直接在索引上取值（using index）。并且返回值不包含不是索引的字段
mysql在使用不等于(!=或者<>)的时候无法使用导致全表扫描
- 在查询的时候，如果对索引使用不等于的操作将会导致索引失效，进行全表扫描
在使用or的时候，前后两个都是索引的时候才会生效
- 比如我们创建组合索引
name，age，address
select * from user where name="John" or age=22; -- name和age都是索引，生效
select * from user where name="John" or pos=22; -- pos不是索引，因此导致全表扫描，索引失效is null和is not null 导致索引失效
- 索引条件一旦是is null或者is not null 将会导致索引失效
like使用%开头的将会导致索引失效
- 如果使用模糊查找的时候，使用
%a%的时候将会导致索引失效
explain select * from user where name like "%a%"; -- 索引失效
explain select * from user where name like "a%"; -- 索引生效、
explain select * from user where name like "%a"; --- 索引失效解决方法
- 需求就需要使用
%$%查询，那么我们如何解决索引失效？我们可以使用覆盖索引避免索引失效 - 假设我们的索引为
name，age
select * from user where name like "%aa%"; -- 索引失效，没有使用覆盖索引而是select*
select name from user where name like "%a%" ; -- 索引生效，使用了覆盖索引，返回索引列name
select name,age from user where name like "%aa%" -- 索引生效，name和age都是索引
select naem，pos from user where name like "%a" -- 索引失效，pos不是索引字符串不加单引号导致索引失效
select * from user where pos=2000，将会导致name这个索引失效，因为mysql在底层会自动为name这个字段进行类型转换
单表查询优化
- 在经常查询或者排序的字段建立索引
两表查询优化
- 我们一般会使用联合查询，比如left Join，right Join
- 我们在不建立索引的情况下，如下：
-- 没有索引，全表扫描
explain select * from user left join image on user.url=image.url- 那么我们这个索引应该建在哪张表上呢？我们验证之后知道，应该在image表中对url建立索引
- 总结：左连接在右边的表上加索引，右连接在左表添加索引
三表查询优化
- 三表建立索引，依然按照左连接在右表上建立索引，右连接在左表上建立索引。
-- 没有建立索引，全表扫描
select * from t1 left jon t2 t1.name=t2.name left join t3 t2.url=t3.url- 我们可以在
t2的表上为name字段建立索引，在t3表上为url字段建立索引，那么将会使用索引查询
小表驱动大表
- 在链接查询的时候，比如
left Join，这种查询是左边的表驱动右边的表，那么我们应该小表驱动大表的策略，对于左连接的时候，左边的表应该是小表，右连接反之
order by 排序的索引生效
- 假设组合索引为
name，age，address - 对于order by排序问题，只有满足以下两种情况才会使用索引排序（using index)
- 对于组合索引，order by 语句使用最左前缀查询
select * from user order by name： 使用索引select * from user order by age： 不使用索引select * from user order by name,age： 使用索引，因为排序规则一样并且是左前缀查询select * from user order by name asc,age desc：不使用索引，因为排序规则不同，即使使用了最佳左前缀
- 使用
where子句与order by子句条件列组合满足索引最左前缀查询select * from user where name="John" order by age： 使用索引，因为where中的name和order by中的age组合在一起符合最佳左前缀原则select * from user where age=22 order by address：不使用索引
- 对于组合索引，order by 语句使用最左前缀查询
- 总结：order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排序，那么排序的规则必须相同（同是升序或者降序）
总结
- 使用最佳左前缀
- 提高
sort_buffer_size的值：不管是使用单路排序还是双路排序，提高这个参数都会提高查询效率 - 提高
max_length_for_sort_data的值：提高这个参数的值，会增加使用单路排序算法的概率，但是如果设置的太高，数据总容量超出sort_buffer_size的概率增大，明显症状是磁盘I/O活动和低的处理器使用率
###
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2018-09-07，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
腾讯云开发者
