索引是什么
MySQL 官方对索引的定义为: 索引 (Index) 是帮助 MySQL 高效获取数据的数据结构.
可以得到索引的本质: 索引是数据结构, 索引的目的是提高查询效率, 可以类比英语新华字典, 根据目录定位词语
如果没有目录呢, 就需要从 A 到 Z, 去遍历的查找一遍, 一个一个找和直接根据目录定位到数据, 差的就是天壤之别
索引底层数据结构
数据库除了存储数据本身之外, 还维护着一个满足特定查找算法的数据结构, 这些结构以某种方式指向数据, 这样就可以基于这些数据结构实现高效查找算法. 这种结构就是索引, MySQL 中索引是 B + 树实现的, 每个索引都对应一棵 B + 树
索引的优势
提高数据检索效率, 降低数据库 IO 成本
通过索引列对数据进行排序, 降低数据排序成本, 降低了 CPU 消耗
索引的劣势
一个索引都为对应一棵 B + 树, 树中每一个节点都是一个数据页, 一个页默认会占用 16KB 的存储空间, 所以一个索引也是会占用磁盘空间的.(空间的代价)
索引是对数据的排序, 当对表中的数据进行增, 删, 改操作时, 都要维护修改内容涉及到的 B + 树索引. 所以在进行这些操作时需要额外的时间进行一些记录移动, 页面分裂, 页面回收等操作来维护索引(时间上的代价)
索引语法
以 test_user 表为例, 建表 sql 如下
- CREATE TABLE `test_user` (
- `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
- `user_id` varchar(36) NOT NULL COMMENT '用户 id',
- `user_name` varchar(30) NOT NULL COMMENT '用户名称',
- `phone` varchar(20) NOT NULL COMMENT '手机号码',
- `lan_id` int(9) NOT NULL COMMENT '本地网',
- `region_id` int(9) NOT NULL COMMENT '区域',
- `create_time` datetime NOT NULL COMMENT '创建时间',
- PRIMARY KEY (`id`),
- KEY `idx_user_id` (`user_id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=1010001 DEFAULT CHARSET=utf8mb4;
1. 查看索引: SHOW INDEX FROM table_name\G
SHOW INDEX FROM test_user;
2. 删除索引: DROP INDEX [indexName] ON mytable;
DROP INDEX idx_user_id ON test_user;
3. 创建索引 alter tableName add [unique] index [indexName] on (columnName (length) )
ALTER TABLE test_user ADD INDEX idx_user_id(user_id);
哪些情况需要建索引
主键自动建立唯一索引
频繁作为查询的条件的字段应该创建索引
查询中与其他表关联的字段, 外键关系建立索引
频繁更新的字段不适合创建索引: 因为每次更新不单单是更新了记录还会更新索引, 加重 IO 负担
Where 条件里用不到的字段不创建索引
单间 / 组合索引的选择问题(在高并发下倾向创建组合索引)
查询中排序的字段, 若通过索引去访问将大大提高排序的速度
查询中统计或者分组字段
哪些不适合建索引
表记录太少
经常增删改的表
数据重复且分布平均的表字段, 如果某个数据列包含许多重复的内容, 为它建立索引就没有太大的实际效果.
索引实战
我们在 test_user 表中有 100 万数据
优化一: 使用全部索引
1. 不加索引, 关闭缓存查一条数据
SELECT SQL_NO_CACHE * FROM `test_user` WHERE phone='15190427892' AND lan_id=317 AND region_id=92
2. 加一条复合索引
ALTER TABLE test_user ADD INDEX idx_phone_lan_region(phone,lan_id,region_id);
再查一次, 看结果
可以看到, 加了索引以后, 查询效率提高了很多
这里我们建立的复合索引包含的 3 个字段, 查询的时候全部用到了, 而且 where 中的条件严格按照索引顺序, 这样查询效率是最高的
我们使用 EXPLAIN 关键字看一下
优化二: 最左前缀法则
我们把上面那个例子的第一个插件条件删掉
EXPLAIN SELECT SQL_NO_CACHE * FROM `test_user` WHERE lan_id=317 AND region_id=92;
我们使用 EXPLAIN 关键字看一下
因此, 我们得出结论: 如果建立的是复合索引, 索引的顺序要按照建立时的顺序, 即从左到右, 如: a->b->c(和 B + 树的数据结构有关)
无效索引举例
a->c:a 有效, c 无效
b->c:b,c 都无效
c:c 无效
优化三: 不要对索引做以下处理
计算, 如:+,-,*,/,!=,<>,is null,is not null,or
函数, 如: sum(),round()等等
手动 / 自动类型转换, 如: id = "1", 本来是数字, 给写成字符串了
我们以!= 为例演示, 我们使用 EXPLAIN 关键字看一下
优化四: 索引不要放在范围查询右边
比如复合索引: a->b->c, 当 where a=""and b>10 and c="", 这时候只能用到 a 和 b,c 用不到索引, 因为在范围之后索引都失效(和 B + 树结构有关)
如下
EXPLAIN SELECT SQL_NO_CACHE * FROM `test_user` WHERE phone='15190427892' AND lan_id>317 AND region_id=92;
我们使用 EXPLAIN 关键字看一下
我们把最后一个条件删除, 再看一下
优化五: 减少 select * 的使用
select * 会查询很多不必要的字段, 造成不必要的网络传输和 IO 消耗
优化六: like 模糊搜索
失效情况
- like "% 张三 %"
- like "% 张三"
解决方案
使用复合索引, 即 like 字段是 select 的查询字段, 如: select name from table where name like "% 张三 %"
使用 like "张三 %"
优化七: order by 优化
当查询语句中使用 order by 进行排序时, 如果没有使用索引进行排序, 会出现 filesort 文件内排序, 这种情况在数据量大或者并发高的时候, 会有性能问题, 需要优化.
filesort 出现的情况举例
order by 字段不是索引字段
order by 字段是索引字段, 但是 select 中没有使用覆盖索引, 如:
select * from staffs order by age asc;
order by 中同时存在 ASC 升序排序和 DESC 降序排序, 如:
select a, b from staffs order by a desc, b asc;
order by 多个字段排序时, 不是按照索引顺序进行 order by, 即不是按照最左前缀法则, 如:
select a, b from staffs order by b asc, a asc;
如下情况没有索引
filesort 文件内排序会在内存开辟一块空间, 然后把数据复制了一份放到这个空间内, 再进行排序, 这个是很影响性能的
我们可以为这个字段建一个索引
ALTER TABLE test_user ADD INDEX idx_create_time(create_time);
索引层面解决方法
使用主键索引排序
按照最左前缀法则, 并且使用覆盖索引排序, 多个字段排序时, 保持排序方向一致
在 SQL 语句中强制指定使用某索引, force index(索引名字)
不在数据库中排序, 在代码层面排序
优化八: group by
其原理也是先排序后分组, 其优化方式可参考 order by.where 高于 having, 能写在 where 限定的条件就不要去 having 限定了.
来源: https://www.cnblogs.com/lusaisai/p/13394435.html