index在mysql_MySQL(Index)

mysql index type:

primary key索引: 特殊唯一索引，不允许重复的索引, 不允许为null, 索引名字为Primary. Primary的索引一定是unique的 => btree(clusted)

unique索引: 普通唯一索引, 不允许重复的索引，可以为null                                                   => btree

index索引: 普通非唯一索引, 允许重复的索引即key索引，可以为null　　　　　　　　　　　　　　　　　　　　　　        => btree

fulltext: 全文索引, mysql5.7.5以上. FULLTEXT适合长文章或者较长文本，用来代替like "%***%"效率低下的问题      => fulltext

spatial: 空间索引, mysql5.7.6以上版本)                                                               => spatial

当用alter table创建索引时，可以不指定索引名字，若不指定mysql会自动生成索引名字

当用create index创建索引,必须指定索引的名字，否则mysql会报错

当用alter table删除索引时，必须指定索引的名字，否则mysql会报错

当用drop index删除索引时,必须指定索引的名字，否则mysql会报错

建立索引时，若不想用存储引擎的默认索引数据结构类型，可以指定索引的数据结构

index type(数据结构):

b-tree 　适合连续读取数据,　O(log(n)), primary key和secondary index

hash　　　适合随机读取数据,只支持精确查找，不支持范围查找，不支持排序, memory和ndb cluster支持这种索引, 仅支持<=>,=以及in操作

fulltext 适合长文章或者较长文本, 仅支持char、varchar、text数据类型

r-tree 　适合根据一条数据找附近的数据，即spatial，仅支持geometry、point、linestring、polygon数据类型，优势在于范围查找

index type(physical):

clustered index

no-clusterd index

index type(logical):

主键索引

普通索引或者单列索引

复合索引或者多列索引：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

唯一索引或者非唯一索引

空间索引：对空间数据类型的字段建立的索引，空间数据类型有4种，分别是geometry、point、linestring、polygon

index 语法:

CREATE TABLE table_name

([col_name data_type], ...,[unique|fulltext|spatial] [index|key] [index_name] (col_name[length])[asc|desc])

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[index_type]

ON tbl_name (index_col_name,...)

[index_type]

index_col_name:

col_name [(length)] [ASC | DESC]

index_type:

USING {BTREE | HASH | RTREE}

1、unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；

2、index和key为同义词，两者作用相同，用来指定创建索引

3、col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；

4、index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；

5、length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

6、asc或desc指定升序或降序的索引值存储

index dml:

primary key(主键索引)

alter table `table_name` add primary key (`column`);

create table `table_name`(....., primary key(`column`));

多列索引

alter table `table_name` add primary key (`column1`, `column2`, `column3`);

create table `table_name`(....., primary key (`column1`, `column2`, `column3`));

index(普通索引)

create index 索引的名字 on `table_name` (`column`);

alter table `table_name` add index|key [索引的名字] ( `column` );

create table `table_name`(....., index|key 索引的名字 (`column`));

多列索引

create index 索引的名字 on `table_name` (`column1`, `column2`, `column3`);

alter table `table_name` add index|key [索引的名字] ( `column1`, `column2`, `column3`);

create table `table_name`(....., index|key 索引的名字 (`column1`, `column2`, `column3`));

unique(唯一索引)

create unique index 索引的名字 on `table_name` (`column`);

alter table `table_name` add unique index|key [索引的名字] ( `column` );

create table `table_name`(....., unique 索引的名字 (`column`));

多列索引

create unique index 索引的名字 on `table_name` (`column1`, `column2`, `column3`);

alter table `table_name` add unique index|key [索引的名字] (`column1`, `column2`, `column3`);

create table `table_name`(....., unique index|key 索引的名字 (`column1`, `column2`, `column3`));

fulltext(全文索引)

create fulltext index 索引的名字 on `table_name` (`column`);

create fulltext index 索引的名字 on `table_name` (`column`) WITH PARSER ngram;

alter table `table_name` add fulltext index|key ( `column`);

alter table `table_name` add fulltext index|key ( `column`) WITH PARSER ngram;

create table `table_name`(....., fulltext index|key 索引的名字 (`column`));

create table `table_name`(....., fulltext index|key 索引的名字 (`column`)) WITH PARSER ngram;

多列索引

create fulltext index 索引的名字 on `table_name` (`column`);

create fulltext index 索引的名字 on `table_name` (`column`) WITH PARSER ngram;

alter table `table_name` add fulltext index|key ( `column1`, `column2`, `column3``);

alter table `table_name` add fulltext index|key ( `column1`, `column2`, `column3`) WITH PARSER ngram;

create table `table_name`(....., fulltext index|key 索引的名字 ( `column1`, `column2`, `column3`));

create table `table_name`(....., fulltext index|key 索引的名字 ( `column1`, `column2`, `column3`)) WITH PARSER ngram;

index dml:

show index from table_name;

show index from db_name.table_name;

show index from table_name from db_name;

drop index index_name on `table_name`

alter table `table_name` drop index index_name

show index:

Table 表的名称。

Non_unique 如果索引不能包括重复词,则为0,如果可以则为1。

Key_name 索引的名称

Seq_in_index 索引中的列序列号，从1开始。

Column_name 列名称。

Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’(升序)或NULL(无分类)。

Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。

基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当

进行联合时，MySQL使用该索引的机会就越大。

Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。

Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。

Index_type 用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

Comment评注

索引变量:

show variables like "%index%";

+----------------------------------------+-------+

| eq_range_index_dive_limit | 200 |

| innodb_adaptive_hash_index | ON |

| innodb_adaptive_hash_index_parts | 8 |

| innodb_cmp_per_index_enabled | OFF |

| log_bin_index | |

| log_queries_not_using_indexes | OFF |

| log_throttle_queries_not_using_indexes | 0 |

| performance_schema_max_index_stat | -1 |

| relay_log_index | |

+----------------------------------------+-------+

索引效率

MySQL建表，字段需设置为非空，需设置字段默认值。

MySQL建表，字段需NULL时，需设置字段默认值，默认值不为NULL。

MySQL建表，如果字段等价于外键，应在该字段加索引。

MySQL建表，不同表之间的相同属性值的字段，列类型，类型长度，是否非空，是否默认值，需保持一致，否则无法正确使用索引进行关联对比。

MySQL使用时，一条SQL语句只能使用一个表的一个索引。所有的字段类型都可以索引，多列索引的属性最多15个。

如果可以在多个索引中进行选择，MySQL通常使用找到最少行的索引，索引唯一值最高的索引。

建立索引index(part1,part2,part3),相当于建立了 index(part1),index(part1,part2)和index(part1,part2,part3)三个索引。

MySQL针对like语法必须如下格式才使用索引：SELECT * FROM t1 WHERE key_col LIKE 'ab%' ；

SELECT COUNT(*) 语法在没有where条件的语句中执行效率没有SELECT COUNT(col_name)快，但是在有where条件的语句中执行效率要快。

在where条件中多个and的条件中，必须都是一个多列索引的key_part属性而且必须包含key_part1。各自单一索引的话，只使用遍历最少行的那个索引。

在where条件中多个or的条件中，每一个条件，都必须是一个有效索引。

ORDER BY 后面的条件必须是同一索引的属性，排序顺序必须一致(比如都是升序或都是降序)。

所有GROUP BY列引用同一索引的属性，并且索引必须是按顺序保存其关键字的。

JOIN 索引，所有匹配ON和where的字段应建立合适的索引。

对智能的扫描全表使用FORCE INDEX告知MySQL，使用索引效率更高。

定期ANALYZE TABLE tbl_name为扫描的表更新关键字分布 。

定期使用慢日志检查语句，执行explain，分析可能改进的索引。

条件允许的话，设置较大的key_buffer_size和query_cache_size的值(全局参数)，和sort_buffer_size的值(session变量，建议不要超过4M)

索引Note:

myisam键的长度仅支持1000字节，innodb是767.

blob和text字段仅支持前缀索引.

使用!=以及<>不等于的时候，mysql不使用索引

当在字段时候函数的时候，mysql无法使用索引；在join时条件字段类型不一致的时候，mysql无法使用索引；在组合索

引里使用非第一个索引时也不使用索引.

在使用like的时候，以%开头，即"%***"的时候无法使用索引；在使用or的时候，要求or前后字段都有索引.

有时候mysql query optimizer会认为使用索引并不是最优计划,所以不使用索引。

强制使用索引,在sql语句里可以用use,force index,

不使用索引,在sql语句里可以用ignore index.

不使用查询缓存,在sql语句里可以用sql_no_cache: select sql_no_cache * from table_name;

强制使用缓存,在sql语句里可以用sql_cache: select no_cache * from table_name;

高优先操作,在sql语句里可以用high_priority: select high_priority * fromtable_name;

低优先操作,在sql语句里可以用low_priority: select low_priority * fromtable_name;

延时插入,在insert语句里可以用delayed: insert delayed into table_name....;

强制连接顺序： STRAIGHT_JOIN

强制使用临时表： SQL_BUFFER_RESULT

分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT