Mysql总结一

MySQL索引介绍

索引

索引是什么？

索引是一种数据结构，索引是创建在表上，是对数据库表中的一列或者多列的值进行排序的一种结果
好处：提高查询效率，避免全表查询。

索引分类

• 普通索引：没有任何的限制条件，可以给表中的任何字段创建索引
• 唯一性索引：使用UNIQE修饰的值是不能重复的，主键索引就隶属于唯一性索引
• 主键索引：使用primary key修饰的字段会自动的添加主键索引，单列索引，多列索引
• 全文索引： 使用FullText修饰的字段可以设置为全文索引，只支持varchar/text等类型的字段

创建索引SQL的语法

创建表时可以创建索引

create table table_name(属性名 属性类型 [约束性条件]
...
[unique、fulltext ..] index  别名 属性名 
）

实例：

create table test212 (
  id1 int,
  id2 int,
  unique index idx_id(id1) //对id1属性创建一个唯一性索引
);

在已有表上添加索引

通过create语法创建
create [unique|fulltext|spatial] index 索引名 on 表名 (属性名)

create index idx_2 on test212 (id2);

通过alter语法创建
alter table 表名 add [unique|fulltext|spatial] index 索引名(属性名)

alter table test212 add index idx_id12 (id1,id2);

删除索引

drop index 索引名 on 表名

 drop index idx_id12 on test212;

索引的执行过程

通过实例来进行解答
在Student表中，在通过Sname来查询name为17的属性，数据库总数据量为

select * from Student;
+-----+----------+------+-------+
| SID | Sname    | Ssex | Sage  |
+-----+----------+------+-------+
|   1 | 23       | nan  |    23 |
|   2 | qiandian | nan  |    20 |
|   3 | sunfen   | nan  |    21 |
|   4 | wulan    | nv   |    18 |
|   5 | CJ1210   | 1    |    11 |
|   6 | LG1213   | NULL |     2 |
|   7 | CJ1210   | 1    |  NULL |
|   8 | GY1803   | NULL |  NULL |
|  11 | TL       | nan  |    11 |
|  12 | TL12     | nan  |    12 |
|  13 | 13       | 13   |    13 |
|  17 | 17       | man  | 33333 |
|  18 | 23       | man  |    23 |
|  19 | 21       | nan  |    21 |
|  20 | 21       | nan  |    21 |
+-----+----------+------+-------+
15 rows in set (0.00 sec)

CREATE TABLE `Student` (
  `SID` int(11) NOT NULL,
  `Sname` varchar(25) NOT NULL,
  `Ssex` varchar(4) DEFAULT NULL,
  `Sage` int(11) DEFAULT NULL,
  PRIMARY KEY (`SID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

当前Sname是未创建索引：来分析查询过程，分析SQL执行过程是否使用索引是使用explain关键字加载查询语句的前面

mysql> explain select * from Student where Sname='17'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16
        Extra: Using where
1 row in set (0.00 sec)

通过explain来分析当前查询SName=‘17’这条数据时，可以看到，possible_keys和key的值都为null，说明当前没有 命中索引，rows=16表示进行全表的匹配查询

对Sname添加索引

mysql>  explain select * from Student where Sname='17'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
         type: ref
possible_keys: idx_name
          key: 
      key_len: 27
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

添加索引之后，再次执行查询操作时，possible_keys和key使用到了创建的索引idx_name，最终执行匹配的数据的行数1行

索引的底层结构

MYSQL支持两种索引，一种是B树索引，一种是哈希表索引，这两种索引的查询效率是比较高的
MYSQL InnoDB存储引擎，基于B树(实际MYSQL采用的是B+树)的索引结构。

B树特征

B树的特征：

• 根节点至少包含两个孩子
• 树中每个结点最多含有m个孩子(m >= 2)
• 除了根节点和叶结点外，其他每个结点至少含有ceil(m/2)个孩子，ceil为向上取整
• 所有叶子结点位于同一层（高度相同）
• 假设每个非终端结点中包含有n个关键字信息，其中
  Ki(i = 1…n)为关键字，且按顺序升序排列
  关键字的个数n必须满足：[ceil(m / 2) - 1] <= n <= m - 1
  非叶子结点的指针P[1],P[2],…,P[M];其中K[1]指向关键字小于K[1]的子树，P[M - 1] 指向关键字大于K[M - 1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树，比如看图中关键字值为8的这个结点，P1所对应的这个子树，其值均小于8

查找效率为O(logn)，同时因为B-tree的这些上述特征，在增删改动数据时，根据一些策略，其结构可以保持，不会变为线性

B+树结构（优于B树）

B+树是B树的变体，其基本的定义与B树相同,除了：
B+树和B树的区别：

• 非叶子结点的子树指针与关键字个数相同（所以相对于B树，B+树能够存储更多的关键字）
• 非叶子结点的子树指针P[i]，指向关键字值**[K[i], K[i+1])**的子树，注意区间为左开右闭。
• 非叶子结点仅仅用来索引，数据都保存在叶子结点中（B+树所有的检索都是从根部开始，检索到叶子结点才能结束，而且非叶子结点不存储数据的话就能存储更多关键字）
• B+树相对于B树更矮
• 所有叶子结点均有一个链指针指向下一个叶子节点

综合上面的，B+Tree更适合用来做存储索引：

• B+Tree的特点使得磁盘I/O代价更低，B+树的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B 树更小，同样空间可以读入更多的节点，所以B+树的磁盘读写代价更低
• B+Tree的查询效率更加稳定（查询路径均为从根结点到叶子结点，查询效率均为O(logn)）
• B+Tree更有利于对数据库的扫描（只需要遍历叶子结点就可以进行范围查询）

Hash结构

其查询效率高于B+Tree，只需经过一次定位，就可以查询到对应数据区，但是其也存在一些弊端，因而不能成为数据库的主流索引的扛把子：
弊端：

• 仅仅能满足 “=” , “IN”，不能使用范围查询
• 无法被用来避免数据的排序操作
• 不能利用部分索引键查询，B+Tree可以利用组合索引中的部分索引查询
• 不能避免表扫描
• 因为Hash值是经过一定的算法得到的，所以存在相同的情况，当遇到大量Hash值相等的情况后性能不一定比B-Tree索引要高

哈希索引当然是由哈希表实现的，哈希表对数据并不排序，因此不适合做区间查找，效率非常低，需要搜索整个哈希表结构。

主键索引、辅助索引、聚集索引(密集索引)、非聚集索引(稀疏索引)

MySQL两种主流的数据库存储引擎MyISAM和InnoDB

MyISAM存储引擎-主键索引

MyISAM引擎使用B+树作为索引结构、叶节点的data域存放的是数据记录地址

原理图：
MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，辅助索引的key可以重复

MyISAM存储引擎-辅助索引

原理图：

查询过程：首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

结论： MYISAM存储引擎，索引结构叶子节点存储关键字和数据地址，也就是说索引关键字和数据没有在一起存放，体现在磁盘上就是索引在一个文件存储，数据在另一个文件存储。
列如一个user表，会在磁盘上存储三个文件：user.frm(表结构文件) user.MYD(表的数据文件) user.MYI(表的索引文件)
MYISAM的索引方式也叫做非聚集索引，之所以这么称呼为了与InNoDB的聚集索引区分

INNODB存储引擎-主键索引

INNODB存储引擎的主键索引，叶子节点中，索引关键字和数据是在一起存放的，索引关键字和数据一起存储在叶子节点上。

INNODB存储引擎-辅助索引

INNODB的辅助索引，叶子节点上存放的是索引关键字和对应的主键
从索引树上可以看到，INNODB的索引关键字和数据都是在一起存放的，体现在磁盘存储上。
创建一个user表，在磁盘上只存储两种结构，user.frm(存储表的结构),user.idb（存储索引和数据）

INNODB的索引树叶子节点包含了完整的数据记录，这种索引叫做聚集索引。因为INNODB的数据文件本身要按照主键聚集，所以INNODB要求表必须有主键（MYISAM可以没有），如果没有显示指定，则MYISAM系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MYSQL自动为INNODB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型

左前缀原则

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到select * from user where name=xx and city=xx ; ／／可以命中索引这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。
由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。