mysql索引查询优化

mysql索引查询优化

一、每个索引值对应一个主键ID,因为InnoDB根据主键ID排序存储数据。

二、索引同样占用空间,存在IBD文件中。(也许吧!还没得到确认)

三、MyIsam表和ORACLE都是堆表结构。

四、索引使用二分查找法,B+tree结构。

  • B+TREE的爷节点存放子节点的存储范围,数据都存在叶子点中。如果叶子节点上有多条重复数据,重复数据用全表扫描的方式。(应该 就是这样)

五、InnoDB表聚簇索引

  • 索引中包含索引项和主键ID
  • 索引值如果相同,那么根据主键ID排序。

六、建立索引的命令:

  • create index idx_name on table_name(column_name[(len)])
    len是可选参数,因为在长字段上建立索引非常影响性能,所以可以通过len指定字段长度。innoDB只能取前767bytes做为索引,除非设置了innodb_large_prefix 变量。
  • 所有的存储引擎都支持每个表至少16个索引并且总的索引长度至少256字节。(All storage engines support at least 16 indexes per table and a total index length of at least 256 bytes.)
  • 复合索引最多由16个列组成。(An index may consist of up to 16 columns)
  • 列可以是NULL,除非索引是主键。(Columns can be NULL unless the index is a primary key)
  • 对长字段的索引处理方法是截取前面一部分作为索引项,如,email字段,通常@的后面变化不大,所以只要截取前面30个字符足够处理了。
  • 如果长字段前面的变化很小,后面的变化比较多,如,住址信息,这种可以采取分字段存储的方式,也就是省、市、街道分成三个字段存储。

七、索引维护

  • 由于数据库内容变更导至索引值重新排序的情况,叫做索引维护,索引维护是由数据库自动完成的,(什么时候进行?)
  • 由于索相维护是在用户不可感知的情况下自动完成的,数据库对影响索引的insert,update,delete的操作,内部会封装成一个事务,更新索引表(每个索引是一个单独的表,内部数据是索引项和主键ID构成),所以如果无用索引太多I/O代价增高,插入和索引字段修改就越慢。

八、索引可以通过索引覆盖扫描的方式提高排序、分组和distinct的速度。

  • 索引覆盖扫描:因为索引中存放了索引项的值,在实际使用时,需要返回索引的值是不用回表获取相关字段值的,这样可以减少IO操作,效率最高。
  • 因为索引已经是有序的了,在排序、分组和distinct时可以直接使用索引的有序结果直接进行操作。
  • 比如经常要检索name和passwd,那么可以把这两个组成联合索引,select passwd from tab where name like 'name';这样的操作不用回到tab表获取passwd,直接从索引中就可以得到想要的值。

九、索引字段的选择性:

  1. 没有重复折字段—-选择性极好
  2. 偶尔重复的字段—-选择性较好
  3. 重复很多的字段—-选择性很差
  • 选择性很差的字段不适合创建单列索引,因为重复太多,反面不如全表扫描性能高。
  • 在联合索引中选择性好的放在前面,选择性差的放到后面,这样可以使性能最优。

十、联合索引与前缀查询

  • 联合索引能为前缀单列、复列查询提供邦助。如,联合索引(a,b,c)中有3个列,那么a是a,b,c的前缀,a,b也是a,b,c的前缀。于这个联合索引可以对包含前缀项的查寻提速,
    where a=? and b=?
    或
    where a=? and c=?在这里MYsql5.6以后可以提速,因为c不是前缀,这句中跳过了b列,让5.6以前的版本只能为a列加速,c列为全表扫描。但在5.6版本后加入了新的功能(什么功能没记住),让c列也可以得到索引加速

十一、不能使用索引的情况(好像在5.7版本里这些都能使用索引了,但重试过一次又不行了,下次可以使用时用show warnings看一下为什么!)

  1. 索引列进行过数学运算或函数运算,如:where a+1 = 10,a列有所引,但经过数学运算后就不能使用索引,只能使用全表扫描。
  2. 不包含复合索引的前缀字段,如:idx(a,b,c),where b=? and c=?不含前缀,如果有这样的检索需求很多,可以加一个(b,c)的索引项。
  3. 前缀通配,‘_’和‘%’,如:like ‘%xxx%’不能用索引,liek ‘xxx%’可以用索引。
  4. where条件使用了not, <>, !=
  5. 字段类型不匹配,如:字段类型为int但在搜索时使用了字符,这种情况有时很难发现,因为很有可能数据库内部为其进行类型转换,然后匹配到了检索项,但是并没有使用索引。
  6. 如果检索前缀是一个范围操作,而不是一个等值操作,那么后缀部分就无法使用索引。

发表评论