mysql学习：添加索引

索引常用的有B树索引和哈希索引

哈希表索引是怎么工作的？

　　哈希表是另外一种你可能看到用作索引的数据结构-这些索引通常被称为哈希索引。使用哈希索引的原因是，在寻找值时哈希表效率极高。所以，如果使用哈希索引，对于比较字符串是否相等的查询能够极快的检索出的值。

哈希索引的缺点是什么呢？

　　哈希表是无顺的数据结构，对于很多类型的查询语句哈希索引都无能为力。举例来说，假如你想要找出所有小于40岁的员工。你怎么使用使用哈希索引进行查询？这不可行，因为哈希表只适合查询键值对-也就是说查询相等的查询（例：like “WHERE name = ‘Jesus’）。哈希表的键值映射也暗示其键的存储是无序的。这就是为什么哈希索引通常不是数据库索引的默认数据结构-因为在作为索引的数据结构时，其不像B-Tree那么灵活

mysql常用索引种类

普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有null）
        唯一索引是不允许其中任何两行具有相同索引值的索引，当现有数据存在大量的重复的键值的时候，
        大多数数据库不允许唯一索引与表一起保存，数据库还可能防止添加将表中创建重复键值的新数据。
主键索引：加速查询 + 列值唯一 +　表中只有一个（不可以有null）
        数据库表经常有一列或者多列组合，其值唯一标识表中的每一行，每一列称为表的主键，在数据库
        关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型，该索引要求主键索引的每个值都唯一。
组合索引：多列值组成一个索引，
        专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索

普通索引

1、创建表 + 索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2、创建表索引

create index index_name on table_name(column_name)

3、删除表索引

drop index_name on table_name;

4、查看索引

show index from table_name;

注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。

唯一索引

1、创建表 + 唯一索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

2、创建唯一索引

create unique index 索引名 on 表名(列名)

3、删除唯一索引

drop unique index 索引名 on 表名

主键索引

1、创建主键

alter table 表名 add primary key(列名);

2、删除主键

alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;

创建组合索引

create index ix_name_email on in3(name,email);
如上创建组合索引之后，查询：

name and email  -- 使用索引
name                 -- 使用索引
email                 -- 不使用索引
注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。

explain命令字段说明

没有使用索引
mysql> explain select * from students where name="Xu Xian";
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

使用索引
mysql> explain select * from students where name="Xu Xian";
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | students | ref  | name_index    | name_index | 152     | const |    1 | Using where |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

下面是具体说明

id
    索引顺序标识

select_type
    查询类型
        SIMPLE          简单查询
        PRIMARY         最外层查询
        SUBQUERY        映射为子查询
        DERIVED         子查询
        UNION           联合
        UNION RESULT    使用联合的结果
        ...

table
    正在访问的表名


type
    查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
        ALL             全表扫描，对于数据表从头到尾找一遍
                        select * from tb1;
                        特别的：如果有limit限制，则找到之后就不在继续向下扫描
                               select * from tb1 where email = 'seven@live.com'
                               select * from tb1 where email = 'seven@live.com' limit 1;
                               虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。

        INDEX           全索引扫描，对索引从头到尾找一遍
                        select nid from tb1;

        RANGE          对索引列进行范围查找
                        select *  from tb1 where name < 'alex';
                        PS:
                            between and
                            in
                            >   >=  <   <=  操作
                            注意：!= 和 > 符号


        INDEX_MERGE     合并索引，使用多个单列索引搜索
                        select *  from tb1 where name = 'alex' or nid in (11,22,33);

        REF             根据索引查找一个或多个值
                        select *  from tb1 where name = 'seven';

        EQ_REF          连接时使用primary key 或 unique类型
                        select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;

        CONST           常量
                        表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
                        select nid from tb1 where nid = 2 ;

        SYSTEM          系统
                        表仅有一行(=系统表)。这是const联接类型的一个特例。
                        select * from (select nid from tb1 where nid = 1) as A;
possible_keys
    可能使用的索引

key
    真实使用的

key_len
    MySQL中使用索引字节长度

rows
    mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值

extra
    该列包含MySQL解决查询的详细信息
    “Using index”
        此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
    “Using where”
        这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，
        因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
    “Using temporary”
        这意味着mysql在对查询结果排序时会使用一个临时表。
    “Using filesort”
        这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者
        磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
    “Range checked for each record(index map: N)”
        这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。

索引的优缺点（使用索引和不使用索引）

通过建立索引可以极大地提高在数据库中获取所需信息的速度，同时还能提高服务器处理相关搜索请求的效率，从这个方面来看它具有以下优点：

在设计数据库时，通过创建一个惟一的索引，能够在索引和信息之间形成一对一的映射式的对应关系，增加数据的惟一性特点。
能提高数据的搜索及检索速度，符合数据库建立的初衷。
能够加快表与表之间的连接速度，这对于提高数据的参考完整性方面具有重要作用。
在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。
建立索引之后，在信息查询过程中可以使用优化隐藏器，这对于提高整个信息检索系统的性能具有重要意义。

虽然索引的建立在提高检索效率方面具有诸多积极的作用，但还是存在下列缺点  ：

在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间，
    如果有必要建立起聚簇索引，所占用的空间还将进一步的增加
在对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦。

使用索引注意事项

索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引：

在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。
    相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行
    占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降
    低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。当你在表中添加、删除或者
    更新行数据的时候， 在索引中也会有相同的操作。

下列情况不会使用到索引，要注意

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。

即使建立索引，索引也不会生效：

- like '%xx'
    select * from tb1 where name like '%cn';
- 使用函数
    select * from tb1 where reverse(name) = 'wupeiqi';
- or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;
- !=
    select * from tb1 where name != 'alex'
    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123
- >
    select * from tb1 where name > 'alex'
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
- order by
    select email from tb1 order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;
  
- 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

备份

下面命令可以看到创建表语句，里面有索引的创建语句

mysql> show create table students\G;
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Age` tinyint(3) unsigned NOT NULL,
  `Gender` enum('F','M') NOT NULL,
  `ClassID` tinyint(3) unsigned DEFAULT NULL,
  `TeacherID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`StuID`),
  KEY `name_index` (`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

当使用mysqldump命令进行备份的时候，导出的sql语句中有创建索引的语句。指定主键的时候，主键索引会自动创建。

–

参考文章：https://www.cnblogs.com/wj-1314/p/8353262.html

–

–

–