mysql数据优化原理

mysql数据优化

mysql索引类型

（1） 普通索引

普通索引是 MySQL，中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度 普通索引允许在定义索引的列中插入重复值和空值 创建普通索引时，通常使用的关键字是 INDEX 或 KEY.

例1 下面在tb_student 表中的 id 字段上建立名为 index_id 的索引。 CREATE INDEX index_id ON tb_student(id); alter table user add index index_name(name);

（2） 唯一索引

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。 唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯

例 2 下面在 tb_student 表中的 id 字段上建立名为 index_id 的索引，SQL 语句如下： CREATE UNIQUE INDEX index_id ON tb_student (id);

(3 ) 主键索引

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。 主键索引是一种特殊的唯一索引，不允许值重复或者值为空。 创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引，

（4) 联合素引

由两个及两个以上字段组合成的索引叫联合索引，联合索引最左原则

(5） 全文索引

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。 全文索引允许在索引列中插入重复值和空值。 不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。 创建全文索引使用 FULLTEXT 关键字。

explain执行计划

1.查询表加载的顺序

2.查看sql的查询类型

3.哪些索引可能被使用,那些索引又被实际使用了

4.表之间的引用关系

5.一个表中有多少行被优化器查询

6.其他额外的辅助信息

expain出来的信息有10列，分别是id. select_type、table、 type、 possible_ keys.key、key _len、 ref、 rows, Extra id:选择标识符 select_type：表示查询的类型。 table:输出结果集的表 partitions :匹配的分区 type：表示表的连接类型 possible_keys：表示查询时，可能使用的索引 key:表示实际使用的索引 key_len：索引字段的长度 ref:列与索引的比较 rows:扫描出的行数（估算的行数） filtered:条件过滤的行百分比 Extra:执行情況的描述和说明

type:对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。ALL. index, range,ref.eq_ref、const、system、 NULL (从左到右，性能从差到好）

ALL: Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan, index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用—个索引来选择行

ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

const、 system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表 中，MySQL就能将该查询转换为一个常量，

system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL： MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引l列里选取最小值可以通过单独索引查找完成

ex索引为什么效率高

基于倒排实现

把文章进行结巴分词作为key，把文档id作为value

索引效率为什么高

索引底层实现

 

一棵B+Tree树，浅蓝色的块我们称之为一个磁盘快。可以看到每个磁盘快包含几个数据项（深蓝色所示）和指针（黄色所
示)
如磁盘块1包含数据项17和35，包含p1、pz、p3
P1表示小于17的磁盈块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子结点即3、5、9、10、13、15、28、29、60、75、79、90.99.
非叶子结点不存储真实的数据，只存储只因搜索方向的数据项，如17、35并不真实存在于数据表中。
3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次I0，性能提高将是巨大的，如果没有索引，每个数据
项都要发生一次I0，那么总共需要百万次的I0，显然成本非常非常的高。
​
只有叶子节点存的是真实的数据，根节点和子节点存的是区间信息

btree和b+tree的区别

 B-树的关键词和记录放在一起，叶节点可以看作是外部节点，不包含任何信息；B+树的非叶节点只有关键词和指向下-
个节点的索引，记录只放在叶节点上。
2、在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字就可以确定记录的存在；而B+树中每一个记录的查找时
间基本相同，都需要从根节点到叶节点，并且在叶节点中再比较一下关键字。在这一点上，B-树的性能似乎比B+树好，而在
实际应用中，B+树的性能则更好。由于B+树的非叶节点不存放实际数据，因此每一节点所能容纳的元素数量比B-树多，树高
比B-树小，其优点是减少了磁盘的访问次数。虽然B+树找到记录所需的比较次数比B-树多，但一次磁盘访问的时间相当于数
百次内存比较的时间，所以实际上B+树的性能可能会更好，而B+树的叶节点也可以用指针连接在一起，方便顺序遍历(例如
查看一个目录下的所有文件，一次磁盘访问的所有数据库等），这也是B+树的所有记录系统使用的数据库和文件。
3、B+树的磁盘读写代价更低
B+树的内部结点井没有指向关键宇具体信息的指针。因此其内部结点相对B-树更小。
B+树的查询效率更加稳定。

什么情况下没必要加索引

1.唯一性差

2.频繁更新的字段

3.where中不用的字段

4.索引使用<>时效果一般

什么情况下索引失效

1.有or必全有索引

2.联合索引未用左列字段

3.like以%开头（模糊查询）

4.需要类型转换

5.where索引列有运算的

6.where中索引使用了函数

7.如果mysql觉得全表扫描更快时