mysql索引

标签: mysql性能索引
2076人阅读 评论(0) 收藏 举报
分类:

1,索引

什么是索引?

索引------搜索---引导------引导着我去搜索到某个记录的

定义:

没有索引的情况下,我们查询一条记录,需要从第一条记录一直往下查询,知道查找到我们需要的记录,如果记录数特别大的话,相当于大海捞针,速度特别慢

索引:建一个索引-----指向的是数据的位置-----反映到新华字典-----记录所在的页数

 

优势:

查询速度快了,但是不足:

增加了维护索引的工作量-----每增加一条记录----我就要在索引列表中增加一个索引号指向这条记录的位置

查询速度快了,但是增  删除  更新 速度慢了

索引的分类:

1,主键索引:当我们创建一个表后,指定主键后,自动将主键设置为主键索引

2,普通索引:就是在一个普通的字段上建立索引

3,唯一索引:数据表中的某个字段是唯一的,没有重复----

4,全文索引:在某个字段上建立全文索引,将记录内的关键字抽取出来,然后对每个关键字进行索引    sphinx-----可以建立全文索引

创建索引:

(1)create [索引类型] index 索引名 on 表名(表的某个字段上)

(2)alter table 表名 add [索引类型] index (表的字段)

(3)删除索引 drop index 索引名 on 表名


修改索引:

只能是先删除在增加



唯一索引比普通索引的查询速度要快  详见 

mysql下普通索引和唯一索引的效率对比


快速对Mysql添加索引的五个方法 

1.添加PRIMARY KEY(主键索引)

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

mysql>ALTER TABLE `table_name` ADD UNIQUE (

`column`
)

3.添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )


4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT (
`column`
)

5.添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )


  1. 查看索引  
  2.   
  3. mysql> show index from tblname;  
  4.   
  5. mysql> show keys from tblname;  


先建索引后插数据与先插数据后建索引


1、新建一个表结构,创建索引,将百万或千万级的数据使用insert导入该表。

2、新建一个表结构,将百万或千万级的数据使用isnert导入该表,再创建索引。

这两种效率哪个高呢?或者说用时短呢?

例子一:

记录越大,索引个数越多,差异越明显。以前有过一个记录。
某表记录有1亿条左右,12个索引,删除全部索引的插入速度和保留这12个索引的,插入速度百倍。其实原理很简单,边插入边维护索引,开销太大了。

索引要小心的控制,我写的一些检查工具中,针对索引这个模块,就有制定过N个体检规则,比如对单表索引个数超过8个的情况,联合索引的联合列个数超过4个的情况,从未被用过的索引.....进行告警输出。


例子二:

这个问题很容易测试出来,你可以试验一下,印象会很深,但是我测试用insert into c_object select * from c_object,c_object有400w数据量,开启了nologging,用了7分钟,然后回滚,在创建了1个索引,再执行同样语句,完成后用了20分钟



也就是说 增加索引后  增加了查询速度 但是会 减少插入的速度  这方面要自己做权衡。






数据库建立索引的原则
基于合理的数据库设计,经过深思熟虑后为表建立索引,是获得高性能数据库系统的基础。而未经合理分析便添加索引,则会降低系统的总体性能。索引虽然说提高了数据的访问速度,但同时也增加了插入、更新和删除操作的处理时间。是否要为表增加索引、索引建立在哪些字段上,是创建索引前必须要考虑的问题。解决此问题的一个比较好的方法,就是分析应用程序的业务处理、数据使用,为经常被用作查询条件、或者被要求排序的字段建立索引。基于优化器对SQL语句的优化处理,我们在创建索引时可以遵循下面的一般性原则:

(1)为经常出现在关键字order by、group by、distinct后面的字段,建立索引。

在这些字段上建立索引,可以有效地避免排序操作。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。
(2)在union等集合操作的结果集字段上,建立索引。其建立索引的目的同上。
(3)为经常用作查询选择的字段,建立索引。
(4)在经常用作表连接的属性上,建立索引。
(5)考虑使用索引覆盖。对数据很少被更新的表,如果用户经常只查询其中的几个字段,可以考虑在这几个字段上建立索引,从而将表的扫描改变为索引的扫描。
除了以上原则,在创建索引时,我们还应当注意以下的限制:
(1)限制表上的索引数目。
对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。
(2)不要在有大量相同取值的字段上,建立索引。
在这样的字段(例如:性别)上建立索引,字段作为选择条件时将返回大量满足条件的记录,优化器不会使用该索引作为访问路径。
(3)避免在取值朝一个方向增长的字段(例如:日期类型的字段)上,建立索引;对复合索引,避免将这种类型的字段放置在最前面。
由于字段的取值总是朝一个方向增长,新记录总是存放在索引的最后一个叶页中,从而不断地引起该叶页的访问竞争、新叶页的分配、中间分支页
的拆分。此外,如果所建索引是聚集索引,表中数据按照索引的排列顺序存放,所有的插入操作都集中在最后一个数据页上进行,从而引起插入“
热点”。
(4)对复合索引,按照字段在查询条件中出现的频度建立索引。
在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复
合索引的第一个字段出现在查询条件中,该索引才可能被使用。
因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。
(5)删除不再使用,或者很少被使用的索引。
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引,将它们删除,从
而减少索引对更新操作的影响。





0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1558397次
    • 积分:20156
    • 等级:
    • 排名:第395名
    • 原创:570篇
    • 转载:60篇
    • 译文:0篇
    • 评论:271条
    博客专栏
    最新评论