MySQL核心优化（二）

一、索引

1.1什么是索引

索引是一种数据结构（存储数据的方式），存储字段值的内容和对应真实数据的物理地址。

查询数据通过索引查询到物理地址，再通过物理地址直接定位数据。

索引是一种以空间换取时间的方式，牺牲了写的速度，提高查询速度。

之所以索引速度快，是因为建立索引后，查询变为：查询字段->索引->物理地址->真实数据

1.2 创建索引

索引的类型：

主键索引  primary key  主键约束  唯一性 不能为null

唯一索引  unique key  唯一约束  唯一性  可以为null

普通索引  key

全文索引  fulltext  key  只支持文本类型（char varchar text），

文本中的内容进行分词，分别建立索引  mysql5.6以下是innodb不支持

特殊索引：

复合（联合）索引  多个字段共同组成一个索引  查询条件有多个时，可以使用

前缀索引

注意：如果不指定索引名称，会默认选择字段名称，是复合索引的话，选择第一个字段作为索引名称。如果索引名称有重复冲突，会自动顺延编号。

1.3 删除索引

alter table 表名称  drop 索引类型key   索引名称

①删除主键带自增属性的

②删除非主键索引

1.4 索引适用的场合

①where 条件字段

②order by 排序字段

③索引覆盖 

④连表字段

给连表的字段建立索引，可以使用到索引，提高查询速度。

on 语法之后的关键字段

1.5 索引原则

①列独立

字段类型不能进行运算操作。

如果列独立不独立，就不能使用到索引

②左固定

like "1000%";

③复合索引

需要同时出现在条件中，才可以使用到复合索引，单独使用不到

④or 条件

or条件字段如果都有索引可用，则索引都可用；如果有其中一个字段索引不可用，则都不可用。

 1.6 前缀索引

前缀索引：可以通过数据的前一部分区别出数据的唯一性，把这部分增加索引。

更加节省时间，速度更快。

①确定建立索引前缀长度

语法：distinct()

②建立前缀索引

例：alter table emp add key epwd (epassword(11));

1.7 in条件索引使用

in条件 可以查询多条（不连续的）

如果某个字段，经常使用到in条件进行查询。可以选择给此字段增加索引。in条件可以使用到索引。

1.8 全文索引

文本类型   char  varchar   text  

match against是查询全文索引的语法。

全文索引里没有给查询的关键词建立索引。mysql本身不会把经常使用的一些词汇建立索引。

在实际业务开发环境，经常使用到的单词，反而没有建立索引。不适用于使用需求。会选择一些第三方软件配合mysql实现全文索引技术和搜索引擎技术。如：php + sphinx

注意：

①只支持文本类型  char varchar text  

②mysql5.6以下  myisam支持 　　innodb不支持

③分词不适合实际使用，不支持中文

④选择sphinx等第三方软件代替mysql的全文索引

1.9 分页优化

分页原理：集合sql语法  limit（skip,length）

在大数据量下的表，进行分页limit语法操作，出现分页skip太大，使用不到索引的情况。

优化方案：

①进行数据的卡位操作

注意：在进行where条件限制范围的时候，注意参数条件的起始值。

正常来说，数据表的主键id是自增和顺序的，所以skip跳过值和主键id是一致的。

如果id不是从1开始，所有要加上起始值。

如果在数据表中，有删除的数据，主键id不连续，那么查询出来的结果，也就不一致。

分页优化，就是把查询的limit范围，先通过where字段条件缩小，就可以用索引；

如果mysql认为查询索引之后，获取到的数据还是范围很大，就不会使用索引。

1.10 索引结构的类型

 ①聚集型索引 

数据文件和索引文件在一起的。

innodb  .ibd

②非聚集型索引 非聚簇型索引
数据文件和索引文件是分离的   myisam     .MYD    .MYI 

1.10.1 myisam 索引数据结构

1)主键索引查询过程：
查询关键字=>主键索引=>物理地址=>真实数据
2)非主键索引查询过程：
查询关键字=>非主键索引=>物理地址=>真实数据

1.10.2 innodb 索引数据结构

1)主键查询数据过程：
关键字=>主键索引=>真实数据
2)非主键查询数据过程：
关键字=>非主键索引=>主键索引=>真实数据
注意：如果innodb没有主键索引
则记录一个数据的唯一值。如果没有主键，会生成一个rowid（唯一）。非主键索引就记录这个rowid。

 1.11 explain

建立索引是为了能提高查询效率。检查索引是否使用到。

explain执行计划，不会真实执行，分析sql语句执行的过程和使用的资源

别名语法 desc

语法：desc|explain 执行的sql语句 \G;