索引

索引：是特殊数据结构，定义在查找时作为查找条件的字段，在MySQL又称为键key，索引通过存储引擎实现

优点：
索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O
缺点：
占用额外空间，影响插入速度

索引类型：
- B+ TREE、HASH、R TREE
- 聚集索引、非聚集索引：数据和索引是否存储在一起
- 主键索引、二级（辅助）索引
- 稠密索引、稀疏索引：是否索引了每一个数据项
- 简单索引、组合索引
- 左前缀索引：取前面的字符做索引
- 覆盖索引：从索引中即可取出要查询的数据，性能高

聚集索引是基于主键列来组织的，来加速主键的排序和查询。修改带有聚集索引的列是很消耗性能的，所以要选择那些不经常更新的列。

二叉树：最上面的节点A被称为根节点，最下面的G、H、I称之为叶子节点，中间的那些称之为分支节点

二叉树

红黑树本质是平衡二叉树

红黑树

B tree这里的B并不是binary(二分)，而是balance平衡。也就是平衡二叉树。而B+ tree是为磁盘或其它直接存取辅助设备设计的一种平衡查找树，在B+ tree中，所有记录节点都是按键值的大小顺序放在同一层的叶子节点上，由各叶子节点指针进行连接。

B树索引

这里蓝色的块是数据，黄色是指针。从顶部的磁盘块1的17和35到第二层，P1指向往下左边是小于17的，P2指向中间是介于17和35之间的，P3指向住下右边是大于35的。
第二层到第三层：磁盘块2的8和12，也同样有3个指针P1/P2/P3，P1指向往下左边是小于8的，P2指向中间是介于8和12之间的，P3指向住下右边是大于12的。其它类似

B+TREE索引

蓝色块只放字段，根节点的5/28/65对应P1/P2/P3，P1指向往下左边，表示5 ~ 28的值，下同，分支节点的5的指针P1，指向往下左边的值，表示5 ~ 10之间以及5之前的内容。最下面的Q左右有个箭头，这其实表示的是链表结构。比如我们要找5~30的内容，只需要找到5，通过这样的链表结构可以找到后续的内容，而不需要重新在根找。
根节点和分支节点只存放字段，而数据存放在叶子节点上。

B+TREE索引

B+Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据

可以使用B+Tree索引的查询类型：
- 全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30
- 匹配最左前缀：即只使用索引的第一列，如：姓wang
- 匹配列前缀：只匹配一列值开头部分，如：姓以w开头的
- 匹配范围值：如：姓ma和姓wang之间
- 精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的
- 只访问索引的查询
B+Tree索引的限制：
- 如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
- 不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列
特别提示：
- 索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
- 为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求
Cardinality
Cardinality值是用来衡量字段的唯一性。

MariaDB [hellodb]> show index from testlog\G
*************************** 1. row ***************************
        Table: testlog
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 997880
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:

Cardinality值比较低时，就要考虑有没有必要使用索引了，值越大，说明重复性越低，越有必要使用索引

Hash索引

Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好
Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持

适用场景：只支持等值比较查询，包括=, <=>, IN()
不适合使用hash索引的场景
- 不适用于顺序查询：索引存储顺序的不是值的顺序
- 不支持模糊匹配
- 不支持范围查询
- 不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效

空间数据索引R-Tree（ Geospatial indexing ）

MyISAM支持地理空间索引，可以使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多
InnoDB从MySQL5.7之后也开始支持

全文索引(FULLTEXT)

在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎

比如这样的一个查找select * from blog where content like '%xxx%',使用B+树索引就不是很好的完成。InnoDB从MySQL 5.6之后也开始支持全文索引。
示例：

SELECT * FROM testlog WHERE 
MATCH(name) AGAINST ('ng20423' IN NATURAL LANGUAGE MODE)

聚簇和非聚簇索引

聚簇和非聚簇索引，主键和二级索引

冗余和重复索引：

冗余索引：（A），（A，B）
重复索引：已经有索引，再次建立索引

管理索引

创建索引：

CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);

#通过修改表创建索引：
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);

帮助：

help CREATE INDEX;

删除索引：

DROP INDEX index_name ON tbl_name;

#或
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引：

SHOW INDEXES FROM [db_name.]tbl_name;

优化表空间：

OPTIMIZE TABLE tb_name;

查看索引的使用

SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;

索引的实例

EXPLAIN命令在下面有介绍，这里先了解一下

简单索引

创建简单索引

MariaDB [hellodb]> create index idx_name on students(name(10));
Query OK, 0 rows affected (0.033 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看创建的索引

MariaDB [hellodb]> show indexes from students\G;
*************************** 1. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: Name
    Collation: A
  Cardinality: 25
     Sub_part: 10
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
3 rows in set (0.000 sec)

使用EXPLAIN来查看是否利用了索引

#使用了idx_name这个索引
MariaDB [hellodb]> explain select * from students where name like 's%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: range
possible_keys: idx_name
          key: idx_name
      key_len: 32
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.000 sec)

#可能的索引是idx_name但实际是NULL，这是因为“x”开头的行有很多，所以就用不了索引
MariaDB [hellodb]> explain select * from students where name like 'x%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: idx_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
        Extra: Using where
1 row in set (0.001 sec)

复合索引

创建复合索引

MariaDB [hellodb]> create index idx_name_age on students(name,age);
Query OK, 0 rows affected (0.031 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看创建的索引

MariaDB [hellodb]> show indexes from students\G
*************************** 2. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_name_age
 Seq_in_index: 1
  Column_name: Name
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_name_age
 Seq_in_index: 2
  Column_name: Age
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
3 rows in set (0.000 sec)

"name"为复合索引的第一个，所以可以利用索引

MariaDB [hellodb]> explain select * from students where name like 's%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: range
possible_keys: idx_name_age
          key: idx_name_age
      key_len: 152
          ref: NULL
         rows: 4
        Extra: Using index condition
1 row in set (0.000 sec)

而"age"是复合索引的第二个字段，所以无法使用索引

MariaDB [hellodb]> explain select * from students where age = 20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
        Extra: Using where
1 row in set (0.000 sec)

不要在查询的列上做运算

#不做运算可以看到使用了primary这个索引(主键本身就是一个索引)
MariaDB [hellodb]> explain select * from students where stuid > 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 15
        Extra: Using where
1 row in set (0.000 sec)

#做运算可以看到实际使用的索引为null
MariaDB [hellodb]> explain select * from students where stuid + 10 > 20 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
        Extra: Using where

索引优化策略：

独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧
左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估
索引选择性：不重复的索引值和数据表的记录总数的比值
多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
尽量使用短索引，如果可以，应该制定一个前缀长度
对于经常在where子句使用的列，最好设置索引
对于有多个列where或者order by子句，应该建立复合索引
对于like语句，以%或者-开头的不会使用索引，以%结尾会使用索引
尽量不要在列上进行运算（函数操作和表达式操作）
尽量不要使用not in和<>操作

SQL语句性能优化

查询时，能不要*就不用*，尽量写全字段名
大部分情况连接效率远大于子查询
多表连接时，尽量小表驱动大表，即小表 join 大表
在有大量记录的表分页时使用limit
对于经常使用的查询，可以开启缓存
多使用explain和profile分析查询语句
查看慢查询日志，找出执行时间长的sql语句优化

EXPLAIN

通过EXPLAIN来分析索引的有效性
参考:官方文档

EXPLAIN SELECT clause

获取查询执行计划信息，用来查看查询优化器如何执行查询

EXPLAIN语法

EXPLAIN tbl_name [col_name | wild]
OR
EXPLAIN [explain_type] {SQL_statement | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

SQL_statement: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

EXPLAIN的输出有这几种

类型	JSON名	用途
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

举个例子

#简单查询
MariaDB [(none)]> explain select * from hellodb.students\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
        Extra:

#union查询
MariaDB [hellodb]> explain select stuid,name from hellodb.students union select stuid,score from scores\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: students
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
        Extra:
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: scores
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
        Extra:
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
3 rows in set (0.012 sec)

EXPLAIN的各种输出细说

id: 当前查询语句中，每个SELECT语句的编号
复杂类型的查询有三种：
简单子查询
用于FROM中的子查询
联合查询：UNION
注意：UNION查询的分析结果会出现一个额外匿名临时表
select_type：查询的类型类型值JSON名意义SIMPLENone简单查询PRIMARYNone最外层查询UNIONNone联合中的第二个或多个SELECT语句DEPENDENT UNIONdependent (true)联合中的第二个或多个SELECT语句，依赖于外部查询UNION RESULTunion_result联合查询的结果SUBQUERYNone子查询的第一个SELECTDEPENDENT SUBQUERYdependent (true)子查询的第一个SELECT,依赖于外部查询DERIVEDNone派生表MATERIALIZEDmaterialized_from_subquery实例化子查询UNCACHEABLE SUBQUERYcacheable (false)无法缓存结果的子查询，必须为外部查询的每一行重新评估该子查询UNCACHEABLE UNIONcacheable (false)属于非可缓存子查询的联合中的第二个或后面的选择(参见非可缓存子查询)
table：SELECT语句关联到的表
可以是表名、<unionM,N>、<derivedN>、<subqueryN>
type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以下顺序，性能从低到高

type	意义
system	这是const连接类型的特例
const	该表最多只有一个匹配行，在查询开头读取
eq_ref	仅返回一个行，但与需要额外与某个参考值做比较
ref	根据索引返回表中匹配某单个值的所有行
fulltext	使用全文索引执行连接
ref_or_null	类似ref，但附加的是MySQL对包含NULL值的行进行额外搜索
index_merge	此连接类型表示使用了索引合并优化。
unique_subquery	此类型替换以下形式的某些IN子查询的eq_ref
index_subquery	此连接类型类似于unique_subquery，它取代了IN子查询
range	有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点
index	根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描
ALL	全表扫描

possible_keys：查询可能会用到的索引
key: 查询中使用到的索引
key_len: 在索引使用的字节数
ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值
rows：MySQL估计为找所有的目标行而需要读取的行数
Extra：额外信息
Using index：MySQL将会使用覆盖索引，以避免访问表
Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤
Using temporary：MySQL对结果排序时会使用临时表
Using filesort：对结果使用一个外部索引排序

MySQL数据库(十二)_索引

索引

B+TREE索引

Hash索引

空间数据索引R-Tree（ Geospatial indexing ）

全文索引(FULLTEXT)

聚簇和非聚簇索引

冗余和重复索引：

管理索引

索引的实例

索引优化策略：

SQL语句性能优化

EXPLAIN

EXPLAIN SELECT clause

EXPLAIN的各种输出细说

发表回复取消回复

索引

B+TREE索引

Hash索引

空间数据索引R-Tree（ Geospatial indexing ）

全文索引(FULLTEXT)

聚簇和非聚簇索引

冗余和重复索引：

管理索引

索引的实例

索引优化策略：

SQL语句性能优化

EXPLAIN

EXPLAIN SELECT clause

EXPLAIN的各种输出细说

发表回复 取消回复

发表回复取消回复