AI智能
改变未来

MySQl索引优化


环境搭建

CREATE TABLE `employees` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(24) NOT NULL DEFAULT \'\' COMMENT \'姓名\',`age` int(11) NOT NULL DEFAULT \'0\' COMMENT \'年龄\',`position` varchar(20) NOT NULL DEFAULT \'\' COMMENT \'职位\',`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT \'入职时间\',PRIMARY KEY (`id`),KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=\'员工记录表\';INSERT INTO employees(name,age,position,hire_time) VALUES(\'LiLei\',22,\'manager\',NOW());INSERT INTO employees(name,age,position,hire_time) VALUES(\'HanMeimei\', 23,\'dev\',NOW());INSERT INTO employees(name,age,position,hire_time) VALUES(\'Lucy\',23,\'dev\',NOW());drop procedure if exists insert_emp;delimiter ;;create procedure insert_emp()begindeclare i int;set i=1;while(i<=100000)doinsert into employees(name,age,position) values(CONCAT(\'zhuge\',i),i,\'dev\');set i=i+1;end while;end;;delimiter ;call insert_emp();

索引优化

建立了联合索引(name,age,position)

  1. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描

    这样索引会失效

    EXPLAIN SELECT * FROM employees WHERE left(name,3) = \'LiLei\';
  2. 存储引擎不能使用索引中范围条件右边的列

    EXPLAIN SELECT * FROM employees WHERE name= \'LiLei\' AND age > 22 AND position =\'manager\';

    这样只能匹配到name,后面的都无效,联合索引范围之后条件的是不能用索引的

  3. 尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少 select * 语句

    具体查询到字段

    EXPLAIN SELECT name,age FROM employees WHERE name= \'LiLei\' AND age = 23 AND position=\'manager\';
  4. mysql在使用不等于(!=或者<>),not in ,not exists 的时候无法使用索引会导致全表扫描 < 小于、 > 大于、 <=、>= 这些,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

  5. is null,is not null 一般情况下也无法使用索引

  6. like以通配符开头(\’%abc…\’)mysql索引失效会变成全表扫描操作

    例如

    EXPLAIN SELECT * FROM employees WHERE name like \'%Lei\'
  7. 字符串不加单引号索引失效

  8. 少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引,详见范围查询优化

  9. 范围查询优化

    由于单次数据量查询过大导致优化器最终选择不走索引 ,可以将大的范围拆分成多个小范围

    explain select * from employees where age >=1 and age <=1000;explain select * from employees where age >=1001 and age <=2000;
  10. 全值匹配

    能用到所有索引一起查询最好,这样筛选数据的精准率越高

    EXPLAIN SELECT * FROM employees WHERE name= \'LiLei\' AND age = 22 AND position =\'manager\';
  11. 最左前缀法则

    如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

    EXPLAIN SELECT * FROM employees WHERE name = \'Bill\' and age = 31;EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = \'dev\';EXPLAIN SELECT * FROM employees WHERE position = \'manager\';

    这样只有第一个语句会走索引,其他不符合最左前缀法则失效

索引下推概念

对于辅助的联合索引(name,age,position),正常情况按照最左前缀原则,SELECT * FROM employees WHERE name like \’LiLei%\’ AND age = 22 AND position =\’manager\’ 这种情况只会走name字段索引,因为根据name字段过滤完,得到的索引行里的age和 position是无序的,无法很好的利用索引。

在MySQL5.6之前的版本,这个查询只能在联合索引里匹配到名字是 \’LiLei\’ 开头的索引,然后拿这些索引对应的主键逐个回表,到主键索引上找出相应的记录,再比对ageposition这两个字段的值是否符合。

MySQL 5.6引入了索引下推优化,可以在索引遍历过程中,对索引中包含的所有字段先做判断,过滤掉不符合条件的记录之后再回表,可 以有效的减少回表次数。使用了索引下推优化后,上面那个查询在联合索引里匹配到名字是 \’LiLei\’ 开头的索引之后,同时还会在索引里过 滤ageposition这两个字段,拿着过滤完剩下的索引对应的主键id再回表查整行数据。

索引下推会减少回表次数,对于innodb引擎的表索引下推只能用于二级索引,innodb的主键索引(聚簇索引)树叶子节点上保存的是全行数据,所以这个时候索引下推并不会起到减少查询全行数据的效果。

like KK% 在绝大多数情况来看,过滤后的结果集比较小,所以这里Mysql选择给 like KK% 用了索引下推优化,当然这也不是绝对的,有时like KK% 也不一定就会走索引下推

OrderBy和GroupBy优化

1、MySQL支持两种方式的排序filesort和index,Using index是指MySQL扫描索引本身完成排序。index效率高,filesort效率低。

2、order by满足两种情况会使用Using index。

​ 1) order by语句使用索引最左前列。

​ 2) 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序,遵循索引建立(索引创建的顺序)时的最左前缀法则。

4、如果order by的条件不在索引列上,就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似,其实质是先排序后分组,遵照索引创建顺序的最左前缀法则。对于groupby的优化如果不需要排序的可以加上order by null禁止排序。注意,where高于having,能写在where中的限定条件就不要去having限定了

分页优化

根据自增且连续的主键排序的分页查询

1、通过主键索引排序,SQL 走索引,而且扫描的行数大大减少,执行效率更高

EXPLAIN select * from employees where id > 90000 limit 5;

根据非主键字段排序的分页查询

1、需要根据非主键字段排序,查询出主键id,再通过主键id查询分页

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

Join关联查询优化

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

t1、t2表的索引都为a字段,t1 1w条数据,t2 100条数据

mysql的表关联常见有两种算法

  • Nested-Loop Join 算法
  • Block Nested-Loop Join 算法

1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法

一次一行循环地从第一张表(称为驱动表)中读取行,在这行数据中取到关联字段,根据关联字段在另一张表(被驱动表)里取出满足条件的行,然后取出两张表的结果合集

  1. 从表 t2 中读取一行数据(如果t2表有查询过滤条件的,会从过滤结果里取出一行数据);
  2. 从第 1 步的数据中,取出关联字段 a,到表 t1 中查找;
  3. 取出表 t1 中满足条件的行,跟 t2 中获取到的结果合并,作为结果返回给客户端;
  4. 重复上面 3 步。

整个过程会读取 t2 表的所有数据(扫描100行),根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引,1次扫描可以认为最终只扫描 t1 表一行完整数据,也就是总共 t1 表也扫描了100行)。因此整个过程扫描了 200 行

如果被驱动表的关联字段没索引,使用NLJ算法性能会比较低(下面有详细解释),mysql会选择Block Nested-Loop Join算法。

2、 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

mysql>EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

驱动表的数据读入到 join_buffer 中,然后扫描被驱动表,把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

上面sql的大致流程如下:

  1. 把 t2 的所有数据放入到 join_buffer
  2. 把表 t1 中每一行取出来,跟 join_buffer 中的数据做对比
  3. 返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描,因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的,因此对表 t1 中的每一行,都要做 100 次判断,所以内存中的判断次数是100 * 10000= 100 万次

因此MySQL对于被驱动表的关联字段没索引的关联查询,一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法,有索引的情况下 NLJ 算法比 BNL算法性能更高

对于关联sql的优化

  • 关联字段加索引,让mysql做join操作时尽量选择NLJ算法
  • 小表驱动大表,写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式,省去mysql优化器自己判断的时间

straight_join解释:straight_join功能同join类似,但能让左边的表来驱动右边的表,能改表优化器对于联表查询的执行顺序,straight_join只适用于inner join

select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。

in和exsits优化

原则:小表驱动大表,即小的数据集驱动大的数据集

**in:**当B表的数据集小于A表的数据集时,in优于exists

**exists:**当A表的数据集小于B表的数据集时,exists优于in

count(*)查询优化

mysql> EXPLAIN select count(1) from employees;mysql> EXPLAIN select count(id) from employees;mysql> EXPLAIN select count(name) from employees;mysql> EXPLAIN select count(*) from employees; 注意:以上4条sql只有根据某个字段count不会统计字段为n

字段有索引:count(*)≈count(1)>count(字段)>count(主键 id) //字段有索引,count(字段)统计走二级索引,二级索引存储数据比主键索引少,所以count(字段)>count(主键 id)

字段无索引:count(*)≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引, count(主键 id)还可以走主键索引,所以count(主键 id)>count(字段)

常见优化方法

1、查询mysql自己维护的总行数

对于myisam存储引擎的表做不带where条件的count查询性能是很高的,因为myisam存储引擎的表的总行数会被mysql存储在磁盘上,查询不需要计算

对于innodb存储引擎的表mysql不会存储表的总记录行数\\

2、show table status

如果只需要知道表总行数的估计值可以用如下sql查询,性能很高

3、将总数维护到Redis里

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令),但是这种方式可能不准,很难保证表操作和redis操作的事务一致性

4、增加数据库计数表

插入或删除表数据行的时候同时维护计数表,让他们在同一个事务里操作

索引设计原则

1、代码先行,索引后上

不知大家一般是怎么给数据表建立索引的,是建完表马上就建立索引吗?

这其实是不对的,一般应该等到主体业务功能开发完毕,把涉及到该表相关sql都要拿出来分析之后再建立索引。

2、联合索引尽量覆盖条件

比如可以设计一个或者两三个联合索引(尽量少建单值索引),让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段,还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。

3、不要在小基数字段上建立索引

索引基数是指这个字段在表里总共有多少个不同的值,比如一张表总共100万行记录,其中有个性别字段,其值不是男就是女,那么该字段的基数就是2。如果对这种小基数字段建立索引的话,还不如全表扫描了,因为你的索引树里就包含男和女两种值,根本没法进行快速的二分查找,那用索引就没有太大的意义了。 一般建立索引,尽量使用那些基数比较大的字段,就是值比较多的字段,那么才能发挥出B+树快速二分查找的优势来。

4、长字符串我们可以采用前缀索引

尽量对字段类型较小的列设计索引,比如说什么tinyint之类的,因为字段类型较小的话,占用磁盘空间也会比较小,此时你在搜索的时候性能也会比较好一点。

当然,这个所谓的字段类型小一点的列,也不是绝对的,很多时候你就是要针对varchar(255)这种字段建立索引,哪怕多占用一些磁盘空间也是有必要的。

对于这种varchar(255)的大字段可能会比较占用磁盘空间,可以稍微优化下,比如针对这个字段的前20个字符建立索引,就是说,对这个字段里的每个值的前20个字符放在索引树里,类似于 KEY index(name(20),age,position)。

此时你在where条件里搜索的时候,如果是根据name字段来搜索,那么此时就会先到索引树里根据name字段的前20个字符去搜索,定位到之后前20个字符的前缀匹配的部分数据之后,再回到聚簇索引提取出来完整的name字段值进行比对。但是假如你要是order by name,那么此时你的name因为在索引树里仅仅包含了前20个字符,所以这个排序是没法用上索引的, group by也是同理。所以这里大家要对前缀索引有一个了解。

5、where与order by冲突时优先where

在where和order by出现索引设计冲突时,到底是针对where去设计索引,还是针对order by设计索引?到底是让where去用上索引,还是让order by用上索引?一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据,接着再进行排序。

因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据,然后做排序的成本可能会小很多。

6、基于慢sql查询做优化

可以根据监控后台的一些慢sql,针对这些慢sql查询做特定的索引优化

索引设计实战

以社交场景APP来举例,我们一般会去搜索一些好友,这里面就涉及到对用户信息的筛选,这里肯定就是对用户user表搜索了,这个表一般来说数据量会比较大,我们先不考虑分库分表的情况,比如,我们一般会筛选地区(省市),性别,年龄,身高,爱好之类的,有的APP可能用户还有评分,比如用户的受欢迎程度评分,我们可能还会根据评分来排序等等。

对于后台程序来说除了过滤用户的各种条件,还需要分页之类的处理,可能会生成类似sql语句执行:select xx from user where xx=xx and xx=xx order by xx limit xx,xx对于这种情况如何合理设计索引了,比如用户可能经常会根据省市优先筛选同城的用户,还有根据性别去筛选,那我们是否应该设计一个联合索引 (province,city,sex) 了?这些字段好像基数都不大,其实是应该的,因为这些字段查询太频繁了。

假设又有用户根据年龄范围去筛选了,比如 where province=xx and city=xx and age>=xx and age<=xx,我们尝试着把age字段加入联合索引 (province,city,sex,age),注意,一般这种范围查找的条件都要放在最后,之前讲过联合索引范围之后条件的是不能用索引的,但是对于当前这种情况依然用不到age这个索引字段,因为用户没有筛选sex字段,那怎么优化了?其实我们可以这么来优化下sql的写法:where province=xx and city=xx and sex in (\’female\’,\’male\’) and age>=xx and age<=xx 对于爱好之类的字段也可以类似sex字段处理,所以可以把爱好字段也加入索引 (province,city,sex,hobby,age)

假设可能还有一个筛选条件,比如要筛选最近一周登录过的用户,一般大家肯定希望跟活跃用户交友了,这样能尽快收到反馈,对应后台sql可能是这样:where province=xx and city=xx and sex in (\’female\’,\’male\’) and age>=xx and age<=xx and latest_login_time>= xx 那我们是否能把 latest_login_time 字段也加入索引了?比如

(province,city,sex,hobby,age,latest_login_time) ,显然是不行的,那怎么来优化这种情况了?其实我们可以试着再设计一个字段is_login_in_latest_7_days,用户如果一周内有登录值就为1,否则为0,那么我们就可以把索引设计成 (province,city,sex,hobby,is_login_in_latest_7_days,age) 来满足上面那种场景了!一般来说,通过这么一个多字段的索引是能够过滤掉绝大部分数据的,就保留小部分数据下来基于磁盘文件进行order by语句的排序,最后基于limit进行分页,那么一般性能还是比较高的。不过有时可能用户会这么来查询,就查下受欢迎度较高的女性,比如sql:where sex = \’female\’ order by score limit xx,xx,那么上面那个索引是很难用上的,不能把太多的字段以及太多的值都用 in 语句拼接到sql里的,那怎么办了?其实我们可以再设计一个辅助的联合索引,比如 (sex,score),这样就能满足查询要求了。

赞(0) 打赏
未经允许不得转载:爱站程序员基地 » MySQl索引优化