mysql索引
来源:互联网 发布:无线暴力破解软件 编辑:程序博客网 时间:2024/06/14 20:44
索引:
explain select * from TableName where ... //分析语法
show index from TableName \G; //查看表中索引
Show profiles; //查看效率(默认是关闭的,开启:setprofiling=1)
----------------------------------------------------------------------------------------------------------------------------
普通索引:
create index 索引名 on 表名(column(length)) //直接创建索引
create table 表名( //创建表的时候同时创建索引
'column' ... ,
index 索引名(column(length))
)
alter table 表名add index 索引名 (column(length)) //修改表结构的方式添加索引
alter table 表名drop index 索引名 //删除索引
drop index 索引名 on 表名 //删除索引
全文索引:
create fulltext index 索引名 on 表名(columb) //创建全文索引
create table 表名( //创建表的时候同时创建索引
'column' ... ,
fulltext(
column)
)
alter table 表名 add fulltext 索引名(column) //修改表结构的方式添加索引
alter table 表名 drop index 索引名 //删除索引
唯一索引:
create unique index 索引名 on 表名 (column(length)) //创建唯一索引
create table 表名( //创建表的时候同时创建索引
'column' ... ,
unique 索引名(column(length))
)
alter table 表名 add unique 索引名 (column(length)) //修改表结构的方式添加索引
alter table 表名 drop index 索引名 //删除索引
主键索引:
create table 表名( //创建表的时候同时创建索引
'id' ... ,
primary key('id')
)
create table 表名( //创建表的时候同时创建索引
'id' ...primary key
)
alter table 表名 add primary key(column) //修改表结构的方式添加索引
alter table表名 drop primary key //删除索引
---------------------------------------------------------------------------------------------------------------------------
索引优化策略:理想的索引
1:查询频繁 2:区分度高 3:长度小 4: 尽量能覆盖常用查询字段.
1: 索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).
针对列中的值,从左往右截取部分,来建索引
1: 截的越短, 重复度越高,区分度越小, 索引效果越不好
2: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.
索引类型
B-tree索引
Myisam,innodb中,默认用B-tree索引
抽象一下---B-tree系统,可理解为”排好序的快速查找结构”.
hash索引
在memory表里,默认是hash索引, hash的理论查询时间复杂度为O(1)
聚簇索引与非聚簇索引
Myisam与innodb引擎,索引文件的异同
myisam中, 主索引和次索引,都指向物理行(磁盘位置).
innodb的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对主键的引用
注意:对于 innodb来说
1: 主键索引 既存储索引值,又在叶子中存储行的数据
2: 如果没有主键, 则会Unique key做主键
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”
聚簇索引
优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)
劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.
高性能索引策略
对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢.
对于innodb的主键,尽量用整型,而且是递增的整型.
如果是无规律的数据,将会产生的页的分裂,影响速度.
索引覆盖:
索引覆盖是指如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.
这种查询速度非常快,称为”索引覆盖”
对于左前缀不易区分的列 ,建立索引的技巧
如 url列
http://www.baidu.com
http://www.zixue.it
列的前11个字符都是一样的,不易区分, 可以用如下2个办法来解决
1: 把列内容倒过来存储,并建立索引
Moc.udiab.www//:ptth
Ti.euxiz.www//://ptth
这样左前缀区分度大,
2: 伪hash索引效果
同时存 url_hash列,将url列转成整型"可以使用crc32(String)"
索引与排序
排序可能发生2种情况:
1: 对于覆盖索引,直接在索引上查询时,就是有顺序的, usingindex
2: 先取出数据,形成临时表做filesort(文件排序,但文件可能在磁盘上,也可能在内存中)
我们的争取目标-----取出来的数据本身就是有序的! 利用索引来排序.
比如: goods商品表, (cat_id,shop_price)组成联合索引,
where cat_id=Norder by shop_price ,可以利用索引来排序,
select goods_id,cat_id,shop_price fromgoods order by shop_price;
// using where,按照shop_price索引取出的结果,本身就是有序的.
select goods_id,cat_id,shop_price fromgoods order by click_count;
// using filesort 用到了文件排序,即取出的结果再次排序
重复索引与冗余索引
重复索引: 是指在同1个列(如age), 或者顺序相同的几个列(age,school), 建立了多个索引,
称为重复索引, 重复索引没有任何帮助,只会增大索引文件,拖慢更新速度, 去掉.
冗余索引:
冗余索引是指2个索引所覆盖的列有重叠, 称为冗余索引
比如 x,m,列 , 加索引 index x(x), index xm(x,m)
x,xm索引, 两者的x列重叠了, 这种情况,称为冗余索引.
甚至可以把 index mx(m,x) 索引也建立, mx, xm 也不是重复的,因为列的顺序不一样.
索引碎片与维护
在长期的数据更改过程中, 索引文件和数据文件,都将产生空洞,形成碎片.
我们可以通过一个nop操作(不产生对数据实质影响的操作), 来修改表.
比如: 表的引擎为innodb , 可以 alter table 表名engine innodb
optimizetable 表名, 也可以修复.
注意: 修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐.
这个过程,如果表的行数比较大,也是非常耗费资源的操作.
所以,不能频繁的修复.
如果表的Update操作很频率,可以按周/月,来修复.
如果不频繁,可以更长的周期来做修复.
- MySQL索引 聚集索引
- mysql 索引 & 索引类型
- MySQL索引 聚集索引
- mysql 索引 & 索引类型
- mysql索引
- mysql索引
- mysql 索引
- MySql索引
- Mysql索引
- mysql 索引
- mysql 索引
- MySQL索引
- mysql索引
- mysql 索引
- mysql索引
- mysql 索引
- MySQL索引
- mysql索引
- PBR基础知识干货总结(1)
- 设计模式之访问者模式
- 搜索练习6/poj.org/problem3278 /Catch That Cow/简单的bfs模板
- 4-4 链式表的按序号查找 (10分) PTA
- ubuntu 常见错误--Could not get lock /var/lib/dpkg/lock
- mysql索引
- c语言知识总结
- C语言学习历程——Training04字符串训练02
- 【MOOC】Python数据分析与展示-北京理工大学-【第一周】数据分析之表示
- mysql集群(主从复制)
- 最简单的曲面细分着色器(Tesselation Shader)【OpenGL】【GLSL】
- C语言--字符串操作函数
- BNUOJ 33993 Stealing Harry Potter's Precious(bfs+dfs)
- Postgresql后备服务器部署实战