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限定了。
MySQL索引介绍和实战
标签:获取 int 磁盘空间 数据结构 前缀 查询 create utf8mb4 成本
本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉
本文系统来源:https://www.cnblogs.com/lusaisai/p/13394435.html