1.索引

1.1索引简介

索引是什么?
MySQL官方对索引的定义为: 索引 (INDEX) 是帮助MySQL高效获取数据的数据结构
从而可以获得索引的本质: 索引是排好序的快速查找数据结构

索引的目的在于提高查询效率，可以类比字典的目录。如果要查 mysql 这个这个单词，我们肯定要先定位
到 m 字母，然后从上往下找 y 字母，再找剩下的 sql 。如果没有索引，那么可能需要 a---z  ,这样全字典
扫描，如果我想找 Java  开头的单词呢?如果我想找 Oracle 开头的单词呢?

重点:索引会影响到MySQL 查找(WHERE的查询条件) 和 排序(ORDER BY) 两大功能!

除了数据本身之外，数据库还维护着一个 满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这
样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

[root@Ringo ~]# df -h                    # Linux下查看磁盘空间命令 df -h 
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        40G   16G   23G  41% /
devtmpfs        911M     0  911M   0% /dev
tmpfs           920M     0  920M   0% /dev/shm
tmpfs           920M  480K  920M   1% /run
tmpfs           920M     0  920M   0% /sys/fs/cgroup
overlay          40G   16G   23G  41%

我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二
叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索.
引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的
索引之外，还有哈希索引(Hash Index)等

索引的优势和劣势

优势
　　●查找:类似大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本
　　●排序:通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗
劣势
　　●实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所
以索引列也是要占用空间的
　　●虽然索引大大提高了查询速度，但是同时会降低表的更新速度，例如对表频繁的
进行 INSERT 、UPDATE 和 DELETE 。因为更新表的时候，MySQL不仅要保
存数据，还要保存一下索引文件每次更新添加的索引列的字段,都会调整因为更
新所带来的键值变化后的索引信息
●索引只是提高效率的一个因素， 如果MySQL有大数据量的表，就需要花时间研究
建立最优秀的索引

1.2 MySQL索引分类

索引分类
　　● 单值索引: 一个索引只包含单个列，一个表可以有多个单列索引
　　● 唯一索引: 索引列的值必须唯一， 但是允许空值
　　● 复合索引: 一个索引包含多个字段
 

　　建议: 一张表建的索引最好不要超过5个!

基本语法

-- 1、创建索引 [UNIQUE]可以省略
-- 如果只写一个字段就是单值索引，写多个字段就是复合索引
CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length));
ALTER TABLE tabName ADD [UNIQUE] INDEX indexName ON (columnName(length));

-- 2、删除索引
DROP INDEX [indexName] ON tabName;

-- 3、查看索引
-- 加上\G就可以以列的形式查看了 不加\G就是以表的形式查看
SHOW INDEX FROM tabName \G;

使用ALTER 命令来为数据表添加索引

-- 1、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL
ALTER TABLE tabName ADD PRIMARY KEY(column_list);

-- 2、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次)
ALTER TABLE tabName ADD UNIQUE INDEX indexName(column_list);

-- 3、该语句创建普通索引，索引值可以出现多次
ALTER TABLE tabName ADD INDEX indexName(column_list);

-- 4、该语句指定了索引为FULLTEXT，用于全文检索
ALTER TABLE tabName ADD FULLTEXT indexName(column_list);

1.3MySQL索引数据结构

索引数据结构
　　● B+Tree 索引
　　● Hash 索引
　　● Full-text 全文索引
　　● R-Tree 索引

B+Tree 索引检索原理

1.4.哪些情况需要建索引

1.主键自动建立主键索引 (唯一 + 非空)
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度，建的复合
索引尽量与 order by 一致
5.查询中统计或者分组字段( group by 也和索引有关)

1.5.那些情况不要建索引

1.记录太少的表
2.经常增删改的表
3.频繁更新的字段不适合创建索引
4.Where 条件里用不到的字段不创建索引
5.假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的
分布概率大约为50%，那么对A字段建索引一般不会提高数据库的查询速度。索
引的选择性是指索引列中不同值的数目与表中记录数的比。如果-个表中有2000
条记录，表索引列有1980个不同的值，那么这个索引的选择性就是
1980/2000=0.99。一个索引的选择性越接近于1,这个索引的效率就越高

2.性能分析

2.1.MySQL Query Optimizer

MySQL 中专[负责优化 SELECT 语句的优化器模块
主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认
为最优的执行计划 (它认为最优的数据检索方式，但不见得是DBA认为最优的，这部
分最耗费时间)

当客户端向 MySQL 请求一条Query, 命令解析器模块完成请求分类，去别处是SEL
ECT并转发给MySQL Query Optimizer ，MySQL Query Optimizer 首先会对
整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对
Query中的查询条件进行简化和转换，如去掉一些无用或显而易 见的条件，结构调整
等。然后分析Query中的 Hint 信息 (如果有) ，看显示 Hint 信息是否可以完全
确定该Query的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计
划，则会读区所涉及对象的统计信息，根据Query进行写相应的计算分析,然后再得
出最后的执行计划

2.2.MySQL常见瓶颈

CPU : CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
IO : 磁盘IO瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈 : top free iostat 和 vmstat 来查看系统的性能状态

2.3. EXPLAIN

EXPLAIN 是什么
　　● EXPLAIN: SQL 的执行计划，使用 EXPLAIN 关键字可以模拟优化器执行SQ
　　L查询语句，从而知道 MySQL 是如何处理 SQL 语句的
EXPLAIN 怎么使用
　　● 语法: explain + SQL语句

mysql> explain select * from pms_category \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pms_category
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1425
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

EXPLAIN能干嘛
　　可以查看以下信息
　　　　id : 表的读取顺序
　　　　select_type : 数据读取操作的操作类型
　　　　possible_keys : 哪些索引可以使用.
　　　　key : 哪些索弓|被实际使用
　　　　ref : 表之间的引用
　　　　rows : 每张表有多少行被优化器查询

EXPLAIN字段

▶id 表的读取和加载顺序
　　值有以下三种情况
　　　id相同，执行顺序由上至下
　　　id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
　　　id相同不同，同时存在。永远是 id大的优先级最高，id相等的时候顺序执行

▶select_type 数据查询的类型，主要是用于区别，普通查询、联合查询、子查询等的复杂查询。
　　● SIMPLE 　　　　简单的 SELECT 查询，查询中不包含子查询或者 UNION
　　● PRIMARY 　　　 查询中如果包含任何复杂的子部分，最外层查询则被标记为 PRIMARY
　　● SUBQUERY　　  在 SELECT 或者WHERE 子句中包含了子查询
　　● DERIVED 　　　 在 FROM 子句中包含的子查询被标记为 DERIVED(衍生 )，MySQL会递归执行这些子查询，把结果放在临时表中
　　● UNION　　　　　如果第二个 SELECT 出现在 UNION 之后，则被标记为 UNION ; 若 UNION 包含在 FROM子句的子查询中，外层 SELECT 将被标记为 DERIVED
　　● UNION RESULT　从 UNION 表获取结果的 SELECT

▶type 访问类型排列
　　从最好到最差依次是: system > const > eq_ref > ref > range > index > ALL。除了ALL没有用到索引，其他级别都用到索引了。
　　一般来说，得保证查询至少达到 range 级别，最好达到 ref
　　● system
　　　　表只有一行记录(等于系统表)，这是 const 类型的特例，平时不会出现，这个可以忽略不计
　　● const
　　　　表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很　　　　　　　　快。如将主键置于where 中，MySQL就能将该查询转化为-个常量
　　● eq_ref
　　　　唯一性索引扫描，对于每个索引键,表中只有一条记录与之匹配，常见于主键或唯一索引扫描。除了system 和 const 类型之外, 这是最好的联接类型
　　● ref
　　　　非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录
　　● range
　　　　只检索给定范围的行，-般就是在WHERE 语句中出现了 BETWEEN 、<>、in 等的查询。这种范围扫描索引比全表扫描要好,因为它只需要开始于索引树的某一点， 而结束于另一点，不用扫描全部索引
　　● index
　　　　Full Index Scan ，全索引扫描，index 和 ALL 的区别为index 类型只遍历索引树。
　　　　也就是说虽然 ALL 和 index 都是读全表，但是 index 是从索引中读的，ALL 是从磁盘中读取的
　　● ALL
　　　　Full Table Scan ，没有用到索引，全表扫描

▶ possible_keys
　　显示可能应用在这张表中的索引，一个或者多个
　　查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

▶ key
　　实际使用的索引。如果为 NULL ，则没建或没有使用索引，即索引失效
　　查询中如果使用了覆盖索引,则该索引仅仅出现在key列表中。与 Extra 有关

▶ key_len
　　表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的。在不损失精度的情况下，长度越短越好。

　　key_len 计算规则
　　　　--索引|列为字符串类型的情况
　　　　　　1)列长度:
　　　　　　2)列是否为空: NULL(+1)， NOT NULL(+0)
　　　　　　3)字符集: 如 utf8mb4=4,utf8=3,gbk=2,latin1=1
　　　　　　4)列类型为字符: 如 varchar(+ 2), char(+0)
　　　　　　计算公式: key_len=(表字符集长度) *列长度+ 1(null) + 2(变长列)
　　　　-- 数值数据的key_len计算公式:
　　　　　　TINYINT允许NULL= 1 + 1(NULL)
　　　　　　SMALLINT允许为NULL = 2 + 1(NULL)
　　　　　　INT允许为NULL= 4 + 1(NULL)
　　　　-- 日期时间型的key_len计算: (针对mysql 5.5及之前版本)
　　　　　　DATETIME允许为NULL= 8 + 1(NULL)
　　　　　　TIMESTAMP允许为NULL= 4 + 1(NULL)

mysql> desc pms_category;
+---------------+------------+------+-----+---------+----------------+
| Field         | Type       | Null | Key | Default | Extra          |
+---------------+------------+------+-----+---------+----------------+
| cat_id        | bigint(20) | NO   | PRI | NULL    | auto_increment |
| name          | char(50)   | YES  |     | NULL    |                |
| parent_cid    | bigint(20) | YES  |     | NULL    |                |
| cat_level     | int(11)    | YES  |     | NULL    |                |
| show_status   | tinyint(4) | YES  |     | NULL    |                |
| sort          | int(11)    | YES  |     | NULL    |                |
| icon          | char(255)  | YES  |     | NULL    |                |
| product_unit  | char(50)   | YES  |     | NULL    |                |
| product_count | int(11)    | YES  |     | NULL    |                |
+---------------+------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)


mysql> explain select cat_id from pms_category where cat_id between 10 and 20 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pms_category
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY  # 用到了主键索引，通过查看表结构知道，cat_id是bigint类型，占用8个字节
      key_len: 8        # 这里只用到了cat_id主键索引，所以长度就是8！
          ref: NULL
         rows: 11
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

▶ ref
　　显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
　　NULL const　　库名.表名.字段

▶ rows
　　根据表统计信息及索弓|选用情况，大致估算出找到所需的记录需要读取的行数

▶ Extra
　　包含不适合在其他列中显示但十分重要的额外信息
　　● Using filesort
　　　　说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取
　　　　MySQL中无法利用索引完成的排序操作称为”文件内排序"

create table user (
    id     integer primary key auto_increment,
    name   varchar(20) not null,
    age    integer     not null,
    gender tinyint     not null
);
create index user_name_gender on user(name, gender);

# 排序没有使用索引
explain select * from user where name ='zhangsan1' order by id \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: user_name_gender
          key: user_name_gender
      key_len: 62
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

#~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
# 排序使用到了索引
explain select * from user where name ='zhangsan1' order by gender \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: user_name_gender
          key: user_name_gender
      key_len: 62
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

　　● Using temporary

　　　　使用了临时表保存中间结果，MySQL 在对查询结果排序时使用了临时表。
　　　　常见于排序 order by 和分组查询 group by 。临时表对系统性能损耗很大

explain select count(*) from user where gender = 18 group by age;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20
        Extra: Using where; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)


explain select count(*) from user where gender =18 group by name;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: index 
possible_keys: user_name_gender
          key: user_name_gender
      key_len: 63
          ref: NULL
         rows: 5
     filtered: 20
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

　　● Using index 
　　　　表示相应的 SELECT 操作中使用了覆盖索引，避免访问了表的数据行,效率不错! 如果同时出现 Using where ，表示索引被用来执行索引键值的查找; 如果没有同时出现 Using where ，表明索引用来读取数据而非
执行查找动作

# 覆盖索引
# 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，即查询列要被所使用的索引覆盖
# 注意：如果要使用覆盖索引，一定不能写SELECT *，要写出具体的字段。
explain select name, gender from user;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pms_category
   partitions: NULL
         type: index
possible_keys: NULL       
          key: user_name_gender
      key_len: 63
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using index   # select的数据列只用从索引中就能够取得，不必从数据表中读取   
1 row in set, 1 warning (0.00 sec)

　　● Using where : 表明使用了WHERE过滤.
　　● Using join buffer : 使用了连接缓存
　　● impossible where : WHERE 子句的值总是false,不能用来获取任何元组

mysql> explain select name from user where name = 'zs' and name = 'ls'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE   # 不可能字段同时查到两个名字
1 row in set, 1 warning (0.00 sec)

3.索引分析

3.1.单表索引分析

数据准备

DROP TABLE IF EXISTS `article`;

CREATE TABLE IF NOT EXISTS `article` (
    `id`          INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    `author_id`   INT(10) UNSIGNED NOT NULL COMMENT '作者id',
    `category_id` INT(10) UNSIGNED NOT NULL COMMENT '分类id',
    `views`       INT(10) UNSIGNED NOT NULL COMMENT '被查看的次数',
    `comments`    INT(10) UNSIGNED NOT NULL COMMENT '回帖的备注',
    `title`       VARCHAR(255)     NOT NULL COMMENT '标题',
    `content`     VARCHAR(255)     NOT NULL COMMENT '正文内容'
) COMMENT '文章';

INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`)
VALUES (1, 1, 1, 1, '1', '1'),
       (2, 2, 2, 2, '2', '2'),
       (3, 3, 3, 3, '3', '3'),
       (1, 1, 3, 3, '3', '3'),
       (1, 1, 4, 4, '4', '4');

案例:查询 category_id 为1且 comments 大于1的情况下，views 最多的article_id

1、编写SQL语句并查看SQL执行计划

# 1、sql语句
SELECT id,author_id FROM article 
WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

# 2、sql执行计划
mysql> EXPLAIN SELECT id,author_id FROM article 
WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where; Using filesort  # 产生了文件内排序，需要优化SQL
1 row in set, 1 warning (0.00 sec)

2、创建索引idx_article_ccv

CREATE INDEX idx_article_ccv ON article(category_id,comments,views);

3、查看当前索引

4、查看现在SQL语句的执行计划

　　创建复合索引 idx_article_ccv之后，虽然解决了全表扫描的问题，但是在 order by 排序的时候没有
　　用到索引，MySQL 居然还是用的Using filesort ，为什么?
5、我们试试修改SQL，看看SQL的执行计划

SELECT id,author_id FROM article 
WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;

　　推论:当 comments > 1 的时候 order by 排序 views 字段索引就用不上，但是当 comments = 1 的
　　时候 order by 排序 views 字段索引就可以用上! ! !所以，范围之后的索引会失效
6、知道了范围之后的索引会失效，原来的索引 idx_article_ccv 最后一个字段 views 会失效，那么如果删
除这个索引，创建 idx_article_cv索引呢?

/* 创建索引 idx_article_cv */
CREATE INDEX idx_article_cv ON article(category_id,views);

查看当前的索引

7、 当前索引是idx_article_cv，来看一下SQL执行计划

3.2.两表索引分析

数据准备

DROP TABLE IF EXISTS `class`;
DROP TABLE IF EXISTS `book`;

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类' 
) COMMENT '商品类别';

CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类'
) COMMENT '书籍';