MySQL索引和调优

索引介绍

什么是索引

在mysql中，索引是高效获取数据的最重要的数据结构，通常在表数据越来越多的情况下，获取数据的效率开始下降，而索引或者叫做键可以有效提升效率
理解索引工作最好的办法就是把索引比喻成书的目录，当需要查看特定的章节时，通过查看目录的方式往往要比查看整个书的内容要有效的多
当索引包含多个字段时，索引字段的顺序就非常重要，因为mysql是从左开始匹配使用索引，意味着如果没有最左边字段时，语句是用不了索引的。

使用索引的优势

大大减少服务器需要扫描的数据量
帮助服务区避免排序和临时表
可以将随机IO变成顺序IO

使用索引的缺点

在创建和维护索引会耗费时间，而且随着数据量的增加而增加
索引文件会占用物理空间
当对表的数据进行insert、update、delete操作时，索引也要动态维护。降低了DML语句的执行效率

B-Tree索引

B-Tree索引使用B-Tree的数据结构存储数据，不同的存储引擎以不同的方式使用B-Tree索引。

MyISAM使用前缀压缩技术使得索引空间更小，且MyISAM在索引中记录了对应数据的物理位置。

InnoDB则按照原数据格式存储，在索引中记录了对应的主键数值。
B-Tree通常意味着所有的值都是按顺序存储，并且每个叶子页到根的距离相同。
InnDb的B-Tree索引结构
B-Tree索引驱使存储引擎不再通过全表扫获取数据，而是从索引的根节点开始查找，在根节点和中间节点都存放了指向下层节点的指针，通过比较节点的值和要查找的值可以找到合适的指针进入下层子节点，直到最下层的叶子节点，最终的结果就是要么找到对应的值，要不找不到对应的值。整个B-tree的树的深度和表的大小直接相关。

B-Tree多索引列是顺序组织存储的，所以也很适合查找范围数据。

mysql> CREATE TABLE people (
    -> last_name varchar(50) not null,
    -> first_name varchar(50) not null,
    -> dob date not null,
    -> gender enum('m', 'f') not null,
    -> key(last_name, first_name, dob)  # 建立联合索引
    -> );
Query OK, 0 rows affected (0.02 sec)
  
mysql> show create table people;
| Table  | Create Table                                                                                                                                                                                                                                       | people | CREATE TABLE `people` (
  `last_name` varchar(50) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `dob` date NOT NULL,
  `gender` enum('m','f') NOT NULL,
  KEY `last_name` (`last_name`,`first_name`,`dob`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------
  
mysql> show index from people;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people |          1 | last_name |            1 | last_name   | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| people |          1 | last_name |            2 | first_name  | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| people |          1 | last_name |            3 | dob         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)
  
mysql> insert into people values('zhang','san','1982-1-1','m');
mysql> insert into people values('li','si','1985-3-2','m');
mysql> insert into people values('wang','wu','1988-6-15','f');
mysql>
mysql> select * from people;
+-----------+------------+------------+--------+
| last_name | first_name | dob        | gender |
+-----------+------------+------------+--------+
| zhang     | san        | 1982-01-01 | m      |
| li        | si         | 1985-03-02 | m      |
| wang      | wu         | 1988-06-15 | f      |
+-----------+------------+------------+--------+
3 rows in set (0.01 sec)

B-Tree也有一定的限制：
- 如果查询条件不是按照索引最左列开始查找，则无法使用索引，比如如果查找名字为san的人，查找某个特定生日的人、查看姓氏以某字母结尾的人，都无法使用此所用
- 如果查询条件跳过了索引的中间列，则查询仅使用最左边的列，比如查找姓为zhang且在某个特定日期出生的人
- 如果查询的某列有范围查找，则其右边的列无法使用索引优化查找，如查询姓为zhang，名字以s开头的，且生日为1982-01-01的人，则查询只能使用前两列的索引。

# 查看如下执行计划
## 全键值索引 索引中的所有字段，都能匹配
select * from people where last_name='zhang' and first_name='san' and dob='1982-1-1';
## 最左边匹配原则，创建索引的最左字段是last_name。只要都可以是使用到索引
select * from people where last_name='zhang' and first_name='san';
select * from people where last_name='zhang';
select * from people where last_name='zhang' and first_name like 's%';
## 但是如果查询最左边的列，但是查找以。。结尾的时候也无法使用索引。
select * from people where last_name='zhang' and last_name like '%ang';
## 
select last_name,first_name from people where last_name='zhang';
## 如果条件中未使用到最左索引列，但是select中用到，也会走索引查询。
select last_name,first_name from people where first_name='san';

执行计划 explain官方文档：

哈希索引

MySQL中只有Memory引擎支持哈希索引，但Memory引擎也支持B-Tree索引。
- 哈希索引是基于哈希表实现，只有精确匹配索引中的所有列的查询才有效。
- 对每一行数据，会将所对应的索引列计算出一个哈希码，在索引中存放此哈希码和对应数据行的指针。
- 当存在多行数据的哈希码相同时，索引会以链表的方式存放多个记录指针到同一个哈希条目中。
- 不同的索引列值却有相同哈希码的情况，叫哈希冲突，当存在哈希冲突时，存储引擎必须遍历链表中所有行指针，于对应的行数据比较，直到找到所有符合条件的行。
虽然InnoDB表创建索引时指定hash索引也能创建成功，但底层创建的索引依旧是btree索引
哈希索引的限制
- 只存储哈希值和行指针，不存储字段值，所以最后的查询会真正查询行数据，无法使用覆盖索引
- 索引数据并不是按照索引值顺序存储的，所以排序操作无法使用索引
- 由于哈希索引是用索引列的全部内容计算哈希码，所以如果查询只有部分索引字段时是无法使用索引的
- 哈希索引只支持等值比较查询，比如=，IN操作等，不支持范围查询

F('Aejen') = 2323
f('Baron') = 7437
f('Peter') = 8784
f('Vadim') = 2458

此哈希索引的数据结构为：

槽（solt）	值（ Value ）
2323	指向第1行的指针
2458	指向第2行的指针
7437	指向第3行的指针
8784	指向第4行的指针

索引表会根据哈希值进行排序，但是哈希值的排序不能对应插入顺序
当要查找fname=’peter’的数据时，存储引擎首先计算peter的哈希码，并通过哈希码在对应的索引中找到行数据的指针，查找到行数据。
在哈希索引中，索引叶子不存放数据行，只存放指向对应行数据的指针

全文索引 - Fulltext index

Fulltext
- Fulltext索引是创建在char, varchar, text文本字段上的加速查询和DML操作的索引。
- 它不是直接比较索引中的值，而是查找文本中的关键词，所以全文索引更类似于搜索引擎而不是简单的where条件匹配。
- Fulltext索引的使用时通常用match()…against语句。

添加全文索引

# 创建表的适合添加全文索引
CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);
  
# 修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
  
# 直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)

全文搜索的语法
- MATCH(col1,col2,…) AGAINST (expr[search_modifier])
- 其中MATCH中的内容为已建立FULLTEXT索引并要从中查找数据的列，AGAINST中的expr为要查找的文本内容，search_modifier为可选搜索类型。
- search_modifier的可能取值有：IN NATURAL LANGUAGEMODE、IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION、IN BOOLEAN MODE、WITH QUERY EXPANSION。
- search_modifier的每个取值代表一种类型的全文搜索，分别为自然语言全文搜索、带查询扩展的自然语言全文搜索、布尔全文搜索、查询扩展全文搜索（默认使用IN NATURAL LANGUAGE MODE）。
```
insert into article values(1,’ac’,’abcd’,1), (2,’bd’,’bcde’,1),(3,’ab’,’defg’,1),(4,’be’,’degh’,1);
  
# 使用全文索引
SELECT * FROM tablename WHERE MATCH(column1, column2) AGAINST(‘xxx’, ‘sss’, ‘ddd’)
```

索引的维护方式

创建索引

mysql> help create index;
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes.
See [HELP ALTER TABLE]. CREATE INDEX cannot be used to create a PRIMARY
KEY; use ALTER TABLE instead. For more information about indexes, see
http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html.

URL: http://dev.mysql.com/doc/refman/5.6/en/create-index.html

创建索引参数
- Index_col_name可以包含一个字段，也可以包含多个字段（逗号隔开），如果包含多个字段，则表明此索引是联合索引
- Unique index代表索引中的值不能有重复
- Fulltext index只能创建在innodb和myisam存储引擎的char,varchar和text字段上Index可以创建在包含NULL值的字段上
- Key_block_size=value是在myisam存储引擎的表上指定索引键的block大小

创建索引的三种方式

直接创建索引

CREATE INDEX index_name ON table(column(length))
修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

创建表的时候同时创建索引

CREATE TABLE `table` (
	`id` int(11) NOT NULL AUTO_INCREMENT ,
	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
	`time` int(10) NULL DEFAULT NULL ,
	PRIMARY KEY (`id`), # 主键索引
	INDEX index_name (title(length)) # 普通索引
);

创建不同类型的索引

create index idx_st_sname on students(sname); ##创建普通索引
create index idx_st_union on students(sname,sex); ##创建复合索引
create unique index idx_st_sid on students(sid); ##创建唯一索引
mysql> insert into students values(1,‘aaa’,0);
mysql> insert into students values(1,‘eee’,0); ##插入重复数据失败
ERROR 1062 (23000): Duplicate entry '1' for key 'idx_st_sid'

Index_type 代表创建索引的类型

Storage engine	permissible index type
InnoDB	B-tree
MyISAM	B-tree、HASH
Memory/HEAP	HASH、B-tree
NDB	HASH、B-tree(see node in text)

Create index 注释

comment ‘string ‘代表可以为索引添加最长1024的注释

CREATE TABLE t1 (id INT);
CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';

唯一索引

与普通索引类似，不同的就是在索引功能的基础上又增加了对数据的唯一性要求。
与主键索引的不同是，允许空值。如果是多列索引，则列的组合在每行都必须唯一，否则插入数据会失败

其创建方法和普通索引类似：

# 创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))
# 修改表结构创建索引
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
# 创建表的时候创建索引 
CREATE TABLE `table` (
	`id` int(11) NOT NULL AUTO_INCREMENT ,
	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
	`time` int(10) NULL DEFAULT NULL ,
	PRIMARY KEY (`id`),
	UNIQUE indexName (title(length))
);

###索引的删除

删除普通索引和唯一索引的两种方式

DROP INDEX `index_name` ON `table_name`;
ALTER TABLE `table_name` DROP INDEX `index_name`;

删除主键索引的唯一方式 - alter table
```
ALTER TABLE `table_name` DROP PRIMARY KEY;
```

索引的查看

通过执行show create table table_name查看基本索引信息

mysql> show create table students;
| students | CREATE TABLE `students` (
`sid` int(11) NOT NULL,
`sname` varchar(10) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL,
PRIMARY KEY (`sid`),
KEY `idx_s_1` (`dept_id`),
KEY `idx_union_1` (`gender`,`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

通过执行show index from tables\G命令查看索引的信息

mysql> show index from students\G
*************************** 1. row ***************************
Table: students      # 表的名称
Non_unique: 0        # 如果索引不能包括重复值，则为0；如果可以，为1
Key_name: PRIMARY    # 索引名称
Seq_in_index: 1      # 索引中的序列号，从1开始 
Column_name: sid     # 列名称
Collation: A         # 列以什么方式存储在索引中。在mysql中有值'A'(升序)或'NULL'(无序)。
Cardinality: 8       # 基数。非重复值个数。索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，mysql使用该索引的机会就越大。
Sub_part: NULL       # 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed: NULL         # 指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null:                # 如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type: BTREE    # 索引类型（BTREE, FULLTEXT, HASH, RTREE）。
Comment:             # 索引注释
Index_comment: 
*************************** 2. row ***************************
Table: students
Non_unique: 1
Key_name: idx_s_1
Seq_in_index: 1
Column_name: dept_id
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment: 
Index_comment: 
……
4 rows in set (0.00 sec)

MySQL聚簇索引和辅助索引

聚簇索引

每个InnoDB表都会有一个特殊的索引，叫聚簇索引，索引中包含了所有的行数据。聚簇索引和主键是一个意思的两种叫法。
- 当显式定义一个主键时，则InnoDB就把它作为聚簇索引，当表中没有代表唯一的一个或一组字段时，可以增加一个auto-increment字段作为主键
- 当没有定义主键时，则MySQL会寻找是否有非NULL的唯一索引，如果有就把第一个唯一索引作为聚簇索引
- 当没有主键或合适的唯一索引时，InnoDB内部会创建一个虚构的聚簇索引，其中包含row ID
聚簇索引的优势
- 当SQL语句通过聚簇索引访问表数据时，由于通过索引能直接定位并访问表数据，所以性能很高。
- 相关数据会保存在一起，比如表是包含用户的邮件信息，通过用户ID创建聚簇索引，则查询一个用户的所有邮件只需要读取少量的数据页。
- 使用覆盖索引扫描的查询可以直接使用页节点上的主键值

聚簇索引的叶子节点包含了行的全部数据，而中间节点只包含了索引列，比如下图索引列

辅助索引

所有非聚簇索引都叫做辅助索引，在InnoDB里，辅助索引的每一行包含了对应的主键值和辅助索引值，索引对辅助索引的SQl执行是先定位对应的主键值，然后再到聚簇索引中查找对应的行数据。

MyISAM索引数据分布

MyUSAM存储引擎对定长的行数据采用行号，行号从0开始递增，按数据插入的顺序存储在磁盘上。
- 数据分布如下
- 主键索引和普通索引分布如下：
  - 所以MyISAM中的主键索引和其他索引在结构上是一致的

InnoDB索引数据分布

因为InnoDB支持聚簇索引，所以使用了不同的方式存储数据。在InnoDB中，聚簇索引相当于数据表，不像MyISAM一样需要独立的物理空间去存储行数据。
聚簇索引数据分布情况如下图：

聚簇索引的每个叶子节点包含了主键值、事务ID，用于事务和多版本并发控制的回滚指针以及其他剩余的列。
另外，InnoDB的二级索引（非聚簇索引）也和MyISAM不同，其叶子节点中存储的不是行指针，而是主键值。从而当出现行移动或者数据页分裂时无需更新二级索引中的该值。
二级索引的数据分布情况如下：

MyISAM和InnoDB的索引数据对比

InnoDB索引物理结构

InnoDB索引的物理结构是B-tree结构，所有的索引数据都存放在B-tree的叶节点上。
默认的索引页的大小为16KB。当新数据插入到聚簇索引时，InnoDB会保留1/16的索引页空闲空间作为将来的数据插入和修改所用。
在创建和重建和B-tree索引时，InnoDB会执行bulk加载将数据加载到索引页中。当索引页的数据占据的空间低于设置的merge_threshod，默认是50%时，则InnoDB会执行索引页的合并并将当前索引页释放。
创建MySQL实例时可以指定InnoDB的数据页大小，通过指定innodb_page_size参数，默认是16KB，允许的值包括64KB，32KB，8KB和4KB，一旦实例创建完毕，就无法修改。
在数据库实例之间复制数据文件和日志文件要确保使用相同的页大小，否则无法使用。

索引使用策略

如何建立索引

索引建立在经常搜索的列上
- 在经常使用的列上创建索引，才能保证索引会被重复使用，从而发挥索引提升语句执行性能的优势，反之如果建立在很少使用的列上则索引带来的劣势要大于优势。
- 对于经常使用的列常见于where条件中，表连接中的关联字段，以及排序字段等。
使用独立的列
- 独立的列是指索引列不能是表达式的一部分，也不能是函数的一部分
- 在索引字段上尽量避免使用函数和表达式，否则该索引不会被使用到

        mysql> explain select * from students where sid=1;
        +----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
        | id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
        +----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
        |  1 | SIMPLE      | students | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
        +----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
        1 row in set (0.01 sec)
        
        mysql> explain select * from students where sid+1=2;
        +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
        | id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
        +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
        |  1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
        +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
        1 row in set (0.01 sec)
        
        mysql> explain select * from students where abs(sid)=1;
        +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
        | id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
        +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
        |  1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
        +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
        1 row in set (0.01 sec)

前缀索引

当要增加索引的字段是很长的字符列时，索引会变得很大和很慢，所以要考虑在列开始的部分字符串上创建索引，这里要求索引的选择性，即不重复的索引值和表的记录总数对比的比例约接近1越好，因为选择性越高意味着在查询时能筛选掉的数据量就越多。
对于BLOB，TEXT或者长度很长的varchar字段，要选择合适的前缀字段长度建立前缀索引。
但前缀索引的缺点是无法用在order by和group by情况下，且也无法执行覆盖扫描。

# 创建前缀索引
mysql> create table test(id int, name varchar(2000));
Query OK, 0 rows affected (0.02 sec)
mysql> insert INTO test values(1,'abcaaaaaaa'),(2,'abdaaaaaa'),(3, 'aceaaaaaaa');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> create index idx_1 on test(name);
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
mysql> create index idx1 on  test(name(3));  # 创建前缀索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0 

评估前缀索引是否足够好，可以通过以下方法对比：

# 可以先通过如下方式计算下此字段的不重复率（去重后的行/总行数）
mysql> SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo; 
+-------------------------------+
| COUNT(DISTINCT city)/COUNT(*) |
+-------------------------------+
| 0.0312 | 
+-------------------------------+
# 通过指定前缀位数去重，计算不重复率，看去整个字段的不重复率哪个更接近
mysql> SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
-> COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
-> COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
-> COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
-> COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
-> FROM sakila.city_demo; 
+--------+--------+--------+--------+--------+
| sel3 | sel4 | sel5 | sel6 | sel7 |
+--------+--------+--------+--------+--------+
| 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310 | 
+--------+--------+--------+--------+--------+
mysql> ALTER TABLE sakila.city_demo ADD KEY (city(7));

多列索引（联合索引）
- 通常一般的索引是创建在单独的一个列上，但索引也可以创建在多个列上。这就要随列的前后顺序进行选择。
- 索引的错误使用方法是在所有限制条件语句涉及的字段上都建立单独的索引，而是应该判断多个列的组合是否经常出现而且组合之后的数据筛选范围要优于单独的索引使用。如果是，那就可以再多个列上创建联合索引，比如：
  
  create index ind_union on students(sname, dept_id,teacher_id);

索引使用策略

覆盖索引

当出现语句的执行过程所需要的数据完全可以通过索引来获取时，这样的索引叫做覆盖索引。尝尝出现在多列索引的情况。对于经常出现的SQL语句，可以通过创建覆盖索引而使得语句执行不需要扫描表数据，从而加快语句的执行效率。
当发起一个覆盖索引的查询时，在explain语句的Extra字段中可以看到use index 的信息，代表发生了在索引身上的覆盖查询

覆盖索引示例

# 表结构
CREATE TABLE `students` (
  `sid` int(11) NOT NULL,
  `sname` varchar(10) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `idx_dept_id` (`dept_id`),
  KEY `idx_gender` (`gender`),
  KEY `idx_name` (`sname`),
  KEY `idx_union` (`sname`,`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8_mb4;
# 创建数据
insert into students values(18,'a', 1, 8),(93, 'b', 1, 3),(99,'c',1,8),(4700,'a',1,13);
# use index
mysql> explain select sname,dept_id from students where sname='a' and dept_id=8;
+----+-------------+----------+------+--------------------------------+-----------+---------+-------------+------+--------------------------+
| id | select_type | table    | type | possible_keys                  | key       | key_len | ref         | rows | Extra                    |
+----+-------------+----------+------+--------------------------------+-----------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | students | ref  | idx_dept_id,idx_name,idx_union | idx_union | 18      | const,const |    1 | Using where; Using index |
+----+-------------+----------+------+--------------------------------+-----------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

覆盖索引的优势
- 覆盖索引是非常有用的工具，能够极大的提高性能，其主要优势在于：
  1. 索引通常远小于数据行大小，所以如果只需要读取索引，那MySQL会极大地减少数据访问量
  2. 因为索引是按照列值顺序存储的，所以对IO密集型的范围查询会比随机读取每一行数据的IO要少很多。
  3. InnoDB的二级索引能够覆盖查询的话，就可以避免对主键索引的二次查询
由于覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等不存储索引列的值，所以MySQL只能够使用B-tree索引做覆盖索引。
索引列顺序选择
- 当创建的索引包含多个字段时，字段在索引中的顺序就非常重要，正确的顺序依赖于使用索引的查询语句。
- 此要求仅限于B-tree索引，其他类型的索引则不关注索引列顺序。
- 通常的做法是基于全局基数和选择性来决定字段顺序，某个列的选择性高就意味着更能首先过滤掉大部分无用的数据，所以将此列作为索引的第一列。
- 针对如下查询
  
  select * from payment where staff_id=? And customer_id=?
  
  Customer_id列的选择性更高，所以应该将其作为索引列的第一列
索引统计信息

MySQL查询优化器通过两个API来了解存储引擎的索引值的分布信息，以决定如何使用索引。如果存储引擎向优化器提供的扫描行数信息不准确，或执行计划太复杂以致于无法准确获取匹配的行数，那优化器会使用索引统计信息来估算扫描行数。如果表没有统计信息，或者统计信息不准确，优化器很可能做出错误的决定。可以通过analyze table命令来重新生成统计信息。

可以通过执行show index from命令来查看索引的基数（存储引擎估算索引列有多少不同的值）等信息：

mysql> show index from students\G
*************************** 1. row ***************************
Table: students      # 表的名称
Non_unique: 0        # 如果索引不能包括重复值，则为0；如果可以，为1
Key_name: PRIMARY    # 索引名称
Seq_in_index: 1      # 索引中的序列号，从1开始 
Column_name: sid     # 列名称
Collation: A         # 列以什么方式存储在索引中。在mysql中有值'A'(升序)或'NULL'(无序)。
Cardinality: 8       # 基数。非重复值个数。索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，mysql使用该索引的机会就越大。
Sub_part: NULL       # (前缀索引)如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed: NULL         # 指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null:                # 如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type: BTREE    # 索引类型（BTREE, FULLTEXT, HASH, RTREE）。
Comment:             # 索引注释
Index_comment:

还可以通过查看information_schema.statistics表来查看索引的信息。
- InnoDB存储引擎通过抽样的方式计算统计信息，首先随机读取少量的索引页面，以此为样本计算索引的统计信息，通过参数innodb_stats_sample_pages参数来设置样本页的数量。
```
mysql> show variables like '%innodb_stats_sample_pages%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_stats_sample_pages | 8     |
+---------------------------+-------+
1 row in set (0.00 sec)
```
- InnoDB会在表首次打开，或者执行analyzetable，或者表的大小变化超过1/16时，都会计算索引的统计信息
- 如果希望持久化索引的统计信息，则可以再5.6或以上版本使用innodb_analyze_is_persistent参数控制。

索引碎片处理

B-Tree索引随着表数据的修改变化，包括插入、修改和删除动作而导致碎片化，这会降低查询的效率。碎片化的索引数据可能会以无序的方式存储在磁盘上，而如果叶子节点在物理分布上是顺序且紧密的，那查询性能会更好。
对于支持optimize table命令的存储引擎来说，可以通过此命令来重新整理表数据，或者通过导出再导入的方式重置数据。
对索引可以用删除再重建的方式，也可以使用rebuild的方式。
对于不支持optimize table命令的存储引擎，可以用alter table指定表修改为当前的引擎来重建表 mysql>alter table table_name engine=<原来的存储引擎>

Explain执行计划

Explain说明

Explain官方说明文档
执行计划用来显示对应语句在MySQL中是如何执行的。Explain语句对select，delete，update，insert，replace语句有效。
在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）。

mysql> explain select * from students;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from students\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: NULL
1 row in set (0.00 sec)

Explain输出列名详解

ID：表示执行顺序，值越大则优先级越高；值相同则从上而下执行。
Select_type: 显示本行是简单或复杂select。如果查询有任何复杂的子查询，则最外层标记为PRIMARY（DERIVED、UNION、UNION RESUlT）
- simple：简单查询，不包含子查询和union
- primary：包含union或者子查询，最外层的部分标记为primary
- subquery：一般查询中的子查询被标记为subquery，也就是位于select列表中的查询
- derived：派生表——该临时表是从子查询派生出来的，位于form中的子查询
- union：位于union中第二个及其以后的子查询被标记为union，第一个就被标记为primary如果是union位于from中则标记为derived
- union result：用来从匿名临时表里检索结果的select被标记为union result。包含union的结果集，在union和unionall语句中,因为它不需要参与查询，所以id字段为null。
- dependent union：首先需要满足UNION的条件，及UNION中第二个以及后面的SELECT语句，同时该语句依赖外部的查询
- dependent subquery：和DEPENDENT UNION相对UNION一样
table：访问的是哪个表（引用某个查询。如”derived3”）
Type: 数据访问、读取操作类型。重要！**性能从低到高依次是：ALL->index->range->ref->eq_ref->const,system->NULL **
- ALL：Full Table Scan，遍历全表以找到匹配的行
- index：Full index scan，index与all的区别为index类型只遍历索引数
- range：索引范围臊面，多索引的扫描开始于某一点，返回匹配阈值的行，常见于”between、<、>”等查询
- ref：非唯一性索引扫描：返回匹配某个单独值的所有行。常见于使用非唯一索引和唯一索引的非唯一前缀进行的查找。
- eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录一直匹配。常见于主键或唯一索引扫描的多表连接操作中。
- system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。System为表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
- const：使用唯一索引或者主键，返回记录一定是一行记录的等值where条件时，通常type是const。在其他数据库也叫做唯一索引扫描。
possible_key: 可能使用到的key，列出但不一定被使用。
key：决定使用哪个key来进行优化，若没有使用，则显示为NULL
key_len: 显示mysql的key在索引里使用的字节数
ref：如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
row: 为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘，可粗略估算整个查询会检查的行数
Extra: 包含不适合在其他列中显示但十分重要的额外信息
- Using index：该值表示响应的select操作中使用了覆盖索引（Covering Index）
- Using where: 表示MySQL服务器在存储引擎收到记录后进行“后过滤”
- Using index condition: 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；
- Using where && Using index：和Using index condition有啥区别呢？？
- Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
- Using filesort: MySQL中无法利用索引完成的排序操作称为“文件排序”，常见于orderby和groupby语句中。

Explain使用

示例表结构与数据

# 表结构
CREATE TABLE `students` (
  `sid` int(11) NOT NULL,
  `sname` varchar(10) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `idx_dept_id` (`dept_id`),
  KEY `idx_gender` (`gender`),
  KEY `idx_name` (`sname`),
  KEY `idx_union` (`sname`,`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8_mb4;

#  索引信息
mysql> show index from students;
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY     |            1 | sid         | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | idx_dept_id |            1 | dept_id     | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| students |          1 | idx_gender  |            1 | gender      | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| students |          1 | idx_name    |            1 | sname       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| students |          1 | idx_union   |            1 | sname       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| students |          1 | idx_union   |            2 | dept_id     | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

# 表数据
mysql> select * from students;
+------+-------+--------+---------+
| sid  | sname | gender | dept_id |
+------+-------+--------+---------+
|   18 | a     |      1 |       8 |
|   93 | b     |      1 |       3 |
|   99 | c     |      1 |       8 |
| 4700 | a     |      1 |      13 |
+------+-------+--------+---------+

where条件基于主键：

# 基于主键select *。 extra显示为null是啥？？
mysql> explain select * from students where sid=18;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | students | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

# where基于主键select 主键，覆盖索引
mysql> explain select sid from students where sid=18;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | students | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

where条件基于普通索引

mysql> explain select sid from students where sname='a';
+----+-------------+----------+------+--------------------+----------+---------+-------+------+--------------------------+
| id | select_type | table    | type | possible_keys      | key      | key_len | ref   | rows | Extra                    |
+----+-------------+----------+------+--------------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | students | ref  | idx_name,idx_union | idx_name | 13      | const |    2 | Using where; Using index |
+----+-------------+----------+------+--------------------+----------+---------+-------+------+--------------------------+
1 row in set (0.01 sec)

mysql> explain select sname from students where sname='a';
+----+-------------+----------+------+--------------------+----------+---------+-------+------+--------------------------+
| id | select_type | table    | type | possible_keys      | key      | key_len | ref   | rows | Extra                    |
+----+-------------+----------+------+--------------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | students | ref  | idx_name,idx_union | idx_name | 13      | const |    2 | Using where; Using index |
+----+-------------+----------+------+--------------------+----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select * from students where sname='a';
+----+-------------+----------+------+--------------------+----------+---------+-------+------+-----------------------+
| id | select_type | table    | type | possible_keys      | key      | key_len | ref   | rows | Extra                 |
+----+-------------+----------+------+--------------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | students | ref  | idx_name,idx_union | idx_name | 13      | const |    2 | Using index condition |
+----+-------------+----------+------+--------------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

MySQL调优

SQL优化规则

优化规则1：尽可能消除全表扫描，除非表数据极少

对count(*)而言增加主键的方式可以确保查询仅扫描索引，而不扫描表数据

mysql> show create table COMPANY_TMP;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                        |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------+
| COMPANY_TMP | CREATE TABLE `COMPANY_TMP` (
  `cid` int(11) DEFAULT NULL,
  `cname` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  
# 在没有主键的情况下，counts(*)的type为all，走全表扫描
mysql> explain select count(*) from COMPANY_TMP;
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | COMPANY_TMP | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
  
mysql> alter table company_tmp modify cid int not null primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
  
mysql> show index from COMPANY_TMP;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| company_tmp |          0 | PRIMARY  |            1 | cid         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
  
# # 在有主键的情况下，counts(*)的type为index，走索引扫描
mysql> explain select count(*) from COMPANY_TMP;
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | COMPANY_TMP | index | NULL          | PRIMARY | 4       | NULL |    1 | Using index |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

优化规则2：增加适当的索引提高查询的速度

增加适当的索引可以提高查询的速度，但增加索引需要遵循一定的基本规则：

加在where条件列上
加载表直接join的键值上
如果查询范围是少量字段，可以考虑增加覆盖索引（仅走索引）
有多个查询条件时，考虑增加符合索引，并把最常用的字段放在索引最前面
不要将索引加载区别率不高的字段上
字段上增加函数，则字段上的索引用不了，需考虑改变写法

SELECT m.id,m.user_type,m.user_id,m.email,m.name,m.avatar,m.first_letter,m.nickname,m.sourse,
m.pinyin,m.short_pinyin,m.init_process,m.actived,m.default_qz,m.quick_help,m.group_exist,m.created,
m.updatetime,mp.qz_id,mp.cid,mp.status,mp.lock_reason,mp.created,mp.mp_dept_id,dept_id,mp.mp_dutyduty,
me.birthday FROM `qz_member` AS `m` LEFT JOIN `qz_member_extension` AS `me` ON 
`m`.`id`=me.member_id 
LEFT JOIN`qz_member_mapping `AS` mp` ON mp.member_id=me.member_id WHERE (`mp`.`qz_id`='26348')
AND (`mp`.`status`='0') AND (DATE_FORMAT(me.birthday,'%c-%d') IN ('1-07'));

如上查询，在where中用到了DATE_FORMAT函数，导致不能走索引。解决办法是在qz_member_extension上再创建一个单独存储月和日期的字段并创建索引，这样查询就可以走这个字段的索引，效率能大大提高。

alter tabl eqz_member_extension add birthday_md varchar(10) null comment '生日月份和日期'
create ndex idx_qme on qz_member_extension(birthday_md);

优化规则3：去掉不影响查询结果的表

SELECT COUNT(F.member_id) AS nums 
FROM `qz_followed` AS `F`
JOIN  `qz_member` AS `M` ON `F`.`member_id`=M.id 
JOIN `qz_member_mapping` AS `MP` ON `M`.`id`=MP.member_id 
WHERE (`F`.`follow_id`='445947')
AND (`F`.`follow_type`='10')
AND (`MP`.`qz_id`='26164')
AND (`MP`.`status`='0')
AND (`MP`.`cid`='1');

# 去掉无用jion的多余表qz_member，效率提高10倍以上

SELECT COUNT(F.member_id) AS nums 
FROM `qz_followed` AS `F`
JOIN`qz_member_mapping`AS`MP`ON `F`.`member_id`=MP.member_id 
WHERE (`F`.`follow_id`='445947')
AND (`F`.`follow_type`='10')
AND (`MP`.`qz_id`='26164')
AND (`MP`.`status`='0')
AND (`MP`.`cid`='1');

MySQL索引和调优

mysql索引原理和sql优化

MySQL索引和调优

索引介绍

什么是索引

使用索引的优势

使用索引的缺点

B-Tree索引

哈希索引

全文索引 - Fulltext index

索引的维护方式

创建索引

索引的查看

MySQL聚簇索引和辅助索引

聚簇索引

辅助索引

MyISAM索引数据分布

InnoDB索引数据分布

MyISAM和InnoDB的索引数据对比

InnoDB索引物理结构

索引使用策略

如何建立索引

索引使用策略

索引统计信息

索引碎片处理

Explain执行计划

Explain说明

Explain输出列名详解

Explain使用

MySQL调优

SQL优化规则

优化规则1：尽可能消除全表扫描，除非表数据极少

优化规则2：增加适当的索引提高查询的速度

优化规则3：去掉不影响查询结果的表

开启慢日志

相关配置

相关连接

CATALOG

FEATURED TAGS

FRIENDS

MySQL索引和调优

索引介绍

什么是索引

使用索引的优势

使用索引的缺点

B-Tree索引

哈希索引

全文索引 - Fulltext index

索引的维护方式

创建索引

索引的查看

MySQL聚簇索引和辅助索引

聚簇索引

辅助索引

MyISAM索引数据分布

InnoDB索引数据分布

MyISAM和InnoDB的索引数据对比

InnoDB索引物理结构

索引使用策略

如何建立索引

索引使用策略

索引统计信息

索引碎片处理

Explain执行计划

Explain说明

Explain输出列名详解

Explain使用

MySQL调优

SQL优化规则

优化规则1：尽可能消除全表扫描，除非表数据极少

优化规则2：增加适当的索引提高查询的速度

优化规则3： 去掉不影响查询结果的表

开启慢日志

相关配置

相关连接

CATALOG

FEATURED TAGS

FRIENDS

优化规则3：去掉不影响查询结果的表