MySQL添加、修改、删除索引以及索引的使用场景

索引是查询优化最主要的方式；

查询方式：

  一种是：全表扫描；

  一种是：利用数据表上建立的所以进行扫描。

1.索引类型分类

• UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值；（

     加快查询速度，并且可以限制列值的唯一性，允许为空

          唯一索引包括联合唯一索引，多列形成的唯一索引，这些列可以唯一确定一条表记录，可以为空

  ）
• INDEX(普通索引)：允许出现相同的索引内容；（加快查询速度）
• PROMARY KEY(主键索引)：不允许出现相同的值；（主键唯一性，加快查询速度，不能为空）
• fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维；（主要针对文本的内容进行分词，加快查询速度）
• 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一；（多列组成的索引，查询效率提升高于多个单列索引合并的效率）

2.索引结构分类

索引有很多分类，例如B-tree索引，哈希索引，全文索引等等，索引的实现是在存储引擎层，并不是在后端服务器层，所以不同的存储引擎支持的索引结构也不一定一样

•  B-tree索引

     B-tree 索引普遍存在于存储引擎中，他使用B-tree数据结构来存储数据，如果对树形数据结构比较了解的话，就知道B-tree索引所带来的好处了，他的每个叶子节点都会包含下一个节点的指针，非常方便查询数据

     B-tree 适用于全键值，键值范围，或者前缀查找

• 哈希索引

     哈希索引基于哈希表实现，对于每一行数据，存储引擎会对所有的索引列计算一个哈希码，然后存储引擎会基于这个哈希码来查找数据，小编感觉有点像HashMap 中槽的查询过程

•   全文索引

     全文索引在几种索引结构类型中比较特殊，他查找的是文本中关键词

3. 用 alter table 语句创建索引

应用于表创建完毕之后再添加；

• alter table 可用于创建普通索引、UNIQUE索引和PRIMARY KEY索引3种索引格式，
• table_name是要增加索引的表名，
• column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。
• 索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。
• 另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。

 ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）;

-- 普通索引
alter table table_name add index index_name (column_list) ;
-- 唯一索引
alter table table_name add unique (column_list) ;
-- 主键索引
alter table table_name add primary key (column_list) ;

4.用 create index 语句创建索引

• CREATE INDEX可用于对表增加普通索引或UNIQUE索引，可用于建表时创建索引
• 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；
• 如果是BLOB和TEXT类型，必须指定 length

CREATE INDEX index_name ON table_name(username(length)); 

--  create只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

-- 为表my_text中的列name创建普通索引名称index_n
create index index_n on my_test(name);
-- 为age做降序索引
create index index_age on my_test(age DESC);
-- #建立表列age和name的联合索引；按照age升序后再按照name降序排序
create index index_age_name on my_test(age,name DESC);

4.删除索引

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现;

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

5.修改索引

修改索引可以使用先删除再添加来实现;

--修改索引
alter table my_test drop index index_name;--删除索引

alter table my_test add index index_name(column_name);

 

6. 索引的正确使用

    索引是建立在系统文件上的，会占用一定的内存空间，另外数据在更新的时候也会去维护索引，消耗内存，所以索引一定要正确的使用，索引并不是越多越好，要根据具体的查询业务来规划索引的建立。

建议不要使用索引的几种情况：

    1. 区分度不是很大的字段，例如 性别 sex

    2. 频繁更新的字段

    3. 字符串类型的字段 或者 文本类型的字段

    4. 不在where列中出现的索引

索引失效的几种情况：

    1. 查询列中有函数计算 

    2. 查询列中有模糊查询，"%cloum",可以使用"cloum%" 代替,如果要使用"%column%"，那么select 列中是索引列

    3. 如果查询条件中有or, 索引会失效，除非所有条件都加上索引

    4. 使用不等于(!= 或者 <>)

    5. is null 或者 is not null

    6. 字符串不加引号，会导致索引失效

    7. 最左原则，联合索引中会遵循最左原则，即如果要使用联合索引，那么前面的索引列一定要包含，举个例子，

       有个联合索引(a,b,c) 那么查询条件中只能是 a=1 或者 a=1 and b=1 或者 a=1 and b=1 and c=1,不然索引就会失效

7. 慢查询日志

    慢查询日志是指 mysql中查询 时间超过固定阈值的查询记录，默认时间是10秒，mysql默认情况下不开启慢查询

    默认：show variables like "%slow_query_log%"

 手动开启 set global 变量名 = 值

 set global slow_query_log = on ; 手动开启慢查询日志

 set global long_query_time = 10; 手动设定查询时间超过的值，超过就会记录查询日志

 set global slow_query_log_file = "/var/slow_log.log"; 手动设定慢查询日志的记录地址

 set global log_queries_not_using_indexs = on ; 手动设定 是否要记录 查询中使用到索引的记录