Mysql索引

引入

引起 性能下降 sql 慢的原因：

1.查询语句烂

2.索引失效 单值索引 ，复合索引

3.关联太多join

4.服务器调优及各个参数设置

sql 执行顺序：

from on join/where group by hanving select order by limit

join 图与sql示例

select distinct <select list> 
from <left table> <join type> 
JOIN <right table > 
on <join condition> 
where <where condition> 
group by <group by list> 
having <having condition> 
order by <order by condition> 
Limit <Limit number>

内连接

select <select list> from table A A inner join table B B on A.key = B.key

左连接

select <select list> from table A A left join table B B on A.key = B.key

右连接

select <select list> from table A A right join B B on A.key = B.key

A-B

select <select list > from table A A left join table B B on A.key = B.key where B.key is null

B-A

select <select list> from table A A right join table B B on A.key = B .key where A .key is null

全连接

select <select list > from table A A full outer join table B B on A.key =B.key

C - (A^B)

select <select list > from table A A full outer join table B B on A.key = B.key where A.key is null and B.key is null

Mysql的索引

索引是帮助mysql高效获取数据的数据结构

优点：提高了检索效率，降低了IO成本

缺点：降低了更新表的速度，索引本身占用存储空间

Btree索引

B树每个节点都存储数据，所有节点组成这棵树

B+tree 索引

B+树只有叶子节点存储数据，所有的叶子结点使用链表相连，便于区间查找和遍历，所有非叶节点起到索引作用。

B+树的内部结点并没有指向具体关键字信息的指针，内部结点相对于B树更小，降低了IO次数。

B+树中查找，每次都是一条从根节点到叶节点的路径，所有关键字查询的路径相同。

索引策略

• 普通索引：没有任何限制，用于加速查询
• 主键索引：只能有一个，加速查找，不能为空
• 唯一索引 索引的值必须唯一，但是可以有空值 unique
• 复合索引：一个索引包含多个列
• 聚簇索引：数据行和键值紧密联系在一起。

优点：数据访问更快，索引与数据同时保存在一个Btree中。

缺点：插入速度严重依赖插入顺序，维护起来困难。

• 覆盖索引：如果一个索引包含所需要查询的字段的值，就称它为覆盖索引。

索引类型

• B-tree 适合范围查找，直接支持数据排序
• hash索引 仅支持精确 查询，不支持排序，无法利用利用hash 索引避免排序运算，不能避免全表扫描

优点：检索效率高，不需要多次IO访问 ，hash 索引的效率远高于 B-tree 索引

• R-tree 空间数据索引 myisam支持空间数据索引，可以用作地理数据存储。
• 全文索引

它查找的是文本中的关键词而不是匹配具体的值。MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；

MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

哪些情况需要创建索引

1.主键自动建立唯一索引

2.频繁作为查询条件的字段应该创建索引

3。查询中与其他表关联的字段，外键关系建立索引。

4. 频繁更新的字段不合适建索引。 因为每次更新不单单是更新记录还会更新索引

5.where 条件里用不到的字段不创建索引。

6.查询中排序的字段，排序字段若通过索引去访问去访问大大提高排序速度

7.查询中统计或者分组字段

哪些情况不要建索引

1.表记录太少

2.经常增删改的表

3.如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

Explain性能分析

模拟优化器执行sql语句

主要字段

id

表示查询中执行select 子句的顺序，id 值越大优先级越高。如果id值相同，从上往下顺序执行。

select_type

代表查询的类型，区别普通查询，联合查询，子查询

属性：

simple：简单的查询，不包含子查询

primary: 如果包含子查询，最外层被标记为primary

subquery：包含了子查询

union: union后的select 被标记为union

table

基于哪张表

type

查询的访问指标，从好到坏依次为

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >all

至少是range以上

system

表里只有一行记录

const

通过索引一次就找到了,用于比较主键索引或唯一索引，只匹配到一行数据

eq_ref

唯一性索引扫描，表里只匹配到一行记录。

ref

非唯一性索引扫描，返回匹配某个值的所有行

range

只检索给定范围的所有行。

index

使用了索引但是没通过索引进行过滤，一般是利用索引进行排序分组

all

全表扫描

possible_keys

显示可能应用在这张表的索引，但不一定被查询实际使用。

key

实际使用的索引

key_len

索引中使用的字节数，可通过计算查询中使用的索引长度。它可帮助检查是否充分的利用上了索引。

key_len越长，利用越充分。

ref

显示索引的哪一列被使用了

rows

查询时所需要检查的行数。越少越好。

Extra

其他重要的信息

using filesort

使用了外部的索引情况，而不是按照表内的索引顺序进行读取。

using temporary

使用了临时表，常见于排序和分组

using index

使用了覆盖索引，避免访问了表的数据行

using where

使用了where过滤

使用索引及索引失效

1.如果索引中的字段在sql 中可以全部匹配到，这样的效率是最高的

2.最左前缀法则： 使用复合索引时，查询要从索引的最左前列开始并且不跳过中间的列。一旦跳过，索引后面的字段都无法满足。

3.不要在索引上做任何计算，这样索引会失效转向全表扫描

4.索引列上不能有范围查询，将可能做范围查询的字段的索引顺序放在最后

5.尽可能使用覆盖索引。即查询列和索引列一致，不写select *

6.使用不等于的时侯，有时会无法使用索引会导致全表扫描。

7.字段is null

当字段查询允许为null的情况下（select * from table where a is null）

is null 可以用到索引，is not null 用不到索引。

8.like 前缀不能使用模糊匹配，这样用不到索引。

9.减少all的使用all用不到索引。（使用union all替代）

关联查询优化

left join

左侧为驱动表，右侧为被驱动表。在优化查询时，只有建立在被驱动表上的索引才有效。

inner join

mysql 会自动将小结果集的表选为驱动表

排序分组优化

1.无过滤不索引 在使用group by 或order by时， 如果没有过滤条件就用不上索引

2.如果排序的字段中，没有索引，则索引失效

3.如果order by排序的顺序换了，则索引失效

4.方向反 如果排序的字段顺序有差异，一个正序，一个逆序，则无法按照索引的顺序读取，需要手动排序。

慢查询日志

用来记录mysql 中响应时间超过阈值的语句，具体指时间超过long_query _time值的sql

long_query _time 默认为10s

set global slow_query_log = 1 开启慢查询日志 （仅对当前数据库生效，mysql 重启后失败）

如果想要永久生效，必须修改my.cnf