索引

概述

索引（index）是有序的数据结构。

优点：
高效获取数据，降低数据库IO成本
通过索引对数据进行排序，降低数据排序成本，降低CPU消耗

缺点：
占用磁盘空间
降低更新表的效率（INSERT,UPDATE,DELETE）

索引结构

索引在存储引擎层实现，不同的存储引擎有不同的索引结构。

二叉树

定义一个根，根节点左边大于右边，每个节点只能分出两个节点，树形结构。
进行顺序插入时可能导致出现链表结构，一侧节点增加，另一侧节点没有数据，导致数据查询效率降低。
大量数据下，层级较深，检索速度慢。

平衡二叉树
当两边节点高度差大于1时进行选择和纠正，修正最新插入的节点。

红黑树

B-Tree(多路平衡查找树)

每个节点存储N-1个KEY，和N个指针，指针指向分支节点。
每个KEY都存储数据，中间数向上分裂。

B+Tree

叶子节点存储数据，非叶子节点只负责索引。
所有数据在叶子节点通过指针形成单向链表，便于范围查找。
mysql的B+Tree树优化，增加一个指向相邻叶子节点的指针形成双向链表。
磁盘页的大小固定，不存储数据的情况下作为索引效率更高，底层形成链表支持范围查找。

Hash结构

根据一定的hash算法，将键值换算成hash值，映射到对应的槽位，存储在hash表

索引的分类

根据存储形式分为：
聚集索引：将数据存储与索引放到一块，索引结构的叶子节点保存了数据，必须有且只有一个。
二级索引：数据与索引分开，叶子节点关联对应的主键。

回表查询，通过二级索引查询到聚集索引，通过聚集索引查询到数据。

索引语法

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...)
# 示例
# 常规索引
CREATE INDEX idx_user_name ON tb_user(name);
# 唯一索引
CREATE UNIQUE INDEX idx_user_username ON tb_user(username);
# 联合索引
CREATE INDEX idx_user_gen_dep_tit ON tb_user(gender,department,title);

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

SQL性能优化

# 语法,下划线代表模糊匹配次数
SHOW [SESSION|GLOBAL] STATUS LIKE 'Come_______'
SHOW global status LIKE 'Com_______';

慢查询日志

记录了所有执行时间超过指定参数（long_query_time,默认10秒）的所有SQL日志，慢查询日志默认没有开启。

SHOW VARIABLES LIKE 'slow_query_log'
# 修改my.cnf文件
# 开启Mysql慢查询日志开关
slow_query_log = 1
# 自定义慢日志的时间2秒
long_query_time = 2

# 修改完成后需要重启mysqld服务
# 日志位置/var/lib/mysql/xxx-slow.log
tail - f xxx-slow.log

profile详情

# 确定服务器是否支持profile,YES代表支持
SELECT @@have_profiling;
# profile默认关闭，需要手动开启
select @@profiling;
SET [SESSION|GLOBAL] profiling = [0|1] 
# 查看每一条SQL的耗时基本情况
SHOW profiles;
# 查看指定query_id的语句各个阶段的耗时情况
SHOW profile for query query_id;
# 查看每个SQL CPU的耗费情况
SHOW profile cpu for query quert_id;

explain执行计划

通过EXPLAIN或者DESC获取SELECT 语句的信息，执行过程。

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
id:表的执行顺序,相同执行顺序从上到下
select_type:SIMPLE(简单表，单表查询)，PRIMARY(主查询，外层查询)，UNION(UNION的第二个查询或者后面的查询)，SUBQUERY(SELECT/WHERE之后查询，包括子查询)
type:表示连接类型，性能由好到差为：NULL,system,const,eq_ref,range,index,all
possible_key:应用在该表上可能的索引，一个或多个
key:实际使用的索引，NULL没有使用
key_len:索引使用的字段长度
rows:执行查询的行数
filtered:返回结果的行数占读取行数的百分比，值越大越好

索引使用

索引使用以及索引失效

• 最左前缀法则，需要满足索引最左边的字段，否则索引失效，如果中间跳过会导致索引部分失效，和条件顺序无关。
• 范围查询，出现（><），右边的索引失效，使用>=,<=。
• 索引列运算，索引失效。
• 字符串类型，查询时需要添加单引号，否则索引失效。
• 模糊查询，头部模糊查询，索引失效。
• 数据发布影响，使用索引比全表更慢，则不使用索引。
• WHERE使用or，有一个条件不是索引则全部不使用索引。
• 索引字段是否NULL/NOT NULL与数据分布有关。

SQL提示，在SQL语句加入人为提示达到优化操作。

• use index: 指定使用的索引
  EXPLAIN SELECT * FROM tb_user use index(idx_user_name) WHERE name = 'chuck';
• ignore index:指定不使用的索引
  EXPLAIN SELECT * FROM tb_user ignore index(idx_user_name) WHERE name = 'chuck';
• force index:强制使用索引
  EXPLAIN SELECT * FROM tb_user force index(idx_user_name) WHERE name = 'chuck';

覆盖索引，尽量使用索引列查询数据，减少使用SELECT *，没有索引的列需要回表查询，效率较低。

前缀索引，针对长字符串或者文本类型字段，取字符串的前面一部分建立索引，减少硬盘空间占用。
create index idx_xxx on table_name(column(n));
需要确定合适的前缀长度，需要根据字段的选择性。

select count(distinct email)/count(*) from tb_user;# 先去重，然后除以总的记录数，值越大越好，但是需要考虑性能和空间。

单列索引和联合索引的选择

单列索引：一个索引只包含单个列
联合索引：一个索引包含多个列
业务场景中，如果存在多个查询条件，建议建立联合索引。

索引设计原则

• 针对数据量大，且查询比较频繁的表建立索引
• 针对常作为查询条件(where)，排序(order by),分组(group by)操作的字段设置索引
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
• 如果是字符串，针对字段的特点，建立前缀索引
• 尽量使用联合索引，减少单列索引，可以覆盖索引，减少回表
• 控制索引的数量，索引需要维护索引结构，影响增删改的效率
• 针对字段是否存储NULL,可以在建立设置NOT NULL，有利于mysql优化。