最全MySQL性能优化—索引篇

前言

今天我们来讲讲如何优化MySQL的性能，主要从索引方面优化。下期文章讲讲MySQL慢查询日志，我们是依据慢查询日志来判断哪条SQL语句有问题，然后在进行优化，敬请期待MySQL慢查询日志篇

建表

//?建表
CREATE?TABLE?IF?NOT?EXISTS?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?`test`.`staffs`?(`name`,?`age`,?`pos`,?`add_time`)?VALUES?('z3',?22,?'manager',?now());
INSERT?INTO?`test`.`staffs`?(`name`,?`age`,?`pos`,?`add_time`)?VALUES?('July',?23,?'dev',?now());
INSERT?INTO?`test`.`staffs`?(`name`,?`age`,?`pos`,?`add_time`)?VALUES?('2000',?23,?'dev',?now());
//?建立复合索引（即一个索引包含多个字段）
ALTER?TABLE?staffs?ADD?INDEX?idx_staffs_nameAgePos(name,?age,?pos);

优化一：全部用到索引 介绍

建立的复合索引包含了几个字段，查询的时候最好能全部用到，而且严格按照索引顺序，这样查询效率是最高的。（最理想情况，具体情况具体分析）

SQL 案例

优化二：最左前缀法则 介绍

如果建立的是复合索引mysql 模糊搜索 优化，索引的顺序要按照建立时的顺序，即从左到右，如：a->b->c（和 B+树的数据结构有关）

无效索引举例 SQL 案例

优化三：不要对索引做以下处理 以下用法会导致索引失效 SQL 案例

优化四：索引不要放在范围查询右边 举例

比如复合索引：a->b->c，当 where a="" and b>10 and 3=""，这时候只能用到 a 和 b，c 用不到索引，因为在范围之后索引都失效（和 B+树结构有关）

SQL 案例

优化五：减少 select * 的使用 使用覆盖索引

即：select 查询字段和 where 中使用的索引字段一致。

SQL 案例

优化六：like 模糊搜索 失效情况 解决方案 SQL 案例

优化七：order by 优化

当查询语句中使用 order by 进行排序时，如果没有使用索引进行排序，会出现 filesort 文件内排序，这种情况在数据量大或者并发高的时候，会有性能问题，需要优化。

filesort 出现的情况举例 索引层面解决方法 order by 排序算法

文件的磁盘 IO 非常耗时的，所以在 Mysql4.1 之后，出现了第二种算法，就是单路排序。

当我们无可避免要使用排序时，索引层面没法在优化的时候又该怎么办呢？尽可能让 MySQL 选择使用第二种单路算法来进行排序。这样可以减少大量的随机 IO 操作,很大幅度地提高排序工作的效率。下面看看单路排序优化需要注意的点

单路排序优化点

但是sort_buffer_size 不是越大越好：

优化八：group by

其原理也是先排序后分组，其优化方式可参考order by。where高于having,能写在where限定的条件就不要去having限定了。

?文末福利

可以加小新老师vx免费获取【Java高清路线图】和【全套学习视频和配套资料】

?

（编辑：海南站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!