时间:2021-07-01 10:21:17 帮助过:26人阅读
1.查看索引:SHOW INDEX FROM table_name\G
- SHOW <span style="color: #0000ff;">INDEX</span> <span style="color: #0000ff;">FROM</span> test_user;
2.删除索引:DROP INDEX [indexName] ON mytable;
- <span style="color: #0000ff;">DROP</span> <span style="color: #0000ff;">INDEX</span> idx_user_id <span style="color: #0000ff;">ON</span> test_user;
3.创建索引 alter tableName add [unique] index [indexName] on (columnName (length) )
- <span style="color: #0000ff;">ALTER</span> <span style="color: #0000ff;">TABLE</span> test_user <span style="color: #0000ff;">ADD</span> <span style="color: #0000ff;">INDEX</span> idx_user_id(<span style="color: #ff00ff;">user_id</span>);
表记录太少
经常增删改的表
数据重复且分布平均的表字段,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
我们在test_user表中有100万数据
1.不加索引,关闭缓存查一条数据
- <span style="color: #0000ff;">SELECT</span> SQL_NO_CACHE <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> `test_user` <span style="color: #0000ff;">WHERE</span> phone<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">15190427892</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">AND</span> lan_id<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">317</span> <span style="color: #808080;">AND</span> region_id<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">92</span>
2.加一条复合索引
- <span style="color: #0000ff;">ALTER</span> <span style="color: #0000ff;">TABLE</span> test_user <span style="color: #0000ff;">ADD</span> <span style="color: #0000ff;">INDEX</span> idx_phone_lan_region(phone,lan_id,region_id);
再查一次,看结果
可以看到,加了索引以后,查询效率提高了很多
这里我们建立的复合索引包含的3个字段,查询的时候全部用到了,而且where中的条件严格按照索引顺序,这样查询效率是最高的
我们使用EXPLAIN关键字看一下
我们把上面那个例子的第一个插件条件删掉
- EXPLAIN <span style="color: #0000ff;">SELECT</span> SQL_NO_CACHE <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> `test_user` <span style="color: #0000ff;">WHERE</span> lan_id<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">317</span> <span style="color: #808080;">AND</span> region_id<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">92</span>;
我们使用EXPLAIN关键字看一下
因此,我们得出结论:如果建立的是复合索引,索引的顺序要按照建立时的顺序,即从左到右,如:a->b->c(和 B+树的数据结构有关)
我们以!=为例演示,我们使用EXPLAIN关键字看一下
比如复合索引:a->b->c,当 where a="" and b>10 and c="",这时候只能用到 a 和 b,c 用不到索引,因为在范围之后索引都失效(和 B+树结构有关)
如下
- <br><br>EXPLAIN <span style="color: #0000ff;">SELECT</span> SQL_NO_CACHE <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> `test_user` <span style="color: #0000ff;">WHERE</span> phone<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">15190427892</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">AND</span> lan_id<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">317</span> <span style="color: #808080;">AND</span> region_id<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">92</span>;
我们使用EXPLAIN关键字看一下
我们把最后一个条件删除,再看一下
select *会查询很多不必要的字段,造成不必要的网络传输和IO消耗
当查询语句中使用 order by 进行排序时,如果没有使用索引进行排序,会出现 filesort 文件内排序,这种情况在数据量大或者并发高的时候,会有性能问题,需要优化。
select * from staffs order by age asc;
select a, b from staffs order by a desc, b asc;
select a, b from staffs order by b asc, a asc;
如下情况没有索引
filesort 文件内排序会在内存开辟一块空间,然后把数据复制了一份放到这个空间内,再进行排序,这个是很影响性能的
我们可以为这个字段建一个索引
- <span style="color: #0000ff;">ALTER</span> <span style="color: #0000ff;">TABLE</span> test_user <span style="color: #0000ff;">ADD</span> <span style="color: #0000ff;">INDEX</span> idx_create_time(create_time);
其原理也是先排序后分组,其优化方式可参考order by。where高于having,能写在where限定的条件就不要去having限定了。
MySQL索引介绍和实战
标签:获取 int 磁盘空间 数据结构 前缀 查询 create utf8mb4 成本