mysql 数据库索引

D# 索引

索引

1.表扫描

当我们向表中插入一个数据时,数据库服务器是不会试图将数据放到表中的任何特定的地方,相反,服务器只是简单的将数据存放在文件中下一个可存放位置(服务器为每一个表都预留了一系列的空间). 当我们查询一个记录,服务器只是简单地遍历表中的每一行并检查相关列的内容,将匹配的行,加入到结果集并输出这样的查询流程就为"表扫描".

1.缺点
对于表中含有大量数据而言,例如:表中有几千万的数据,在没有其他的帮助下服务器就无法在合适的时间内完成查询.

2.索引介绍

​ 索引就像书的目录一样,就像我们要查找书中"索引简介"的时候我们就需要将在目录中到索引的章节,在章节下找到索引的子目录"索引简介".

​ 数据库服务器也是使用索引定位表中的行,与普通索引表不同,索引是一种以特定顺序保存的专有表.

什么是专有表呢?

​ 首先是索引是一张表,该表呢,并不包含实体的所有数据,包含了用于定位表中专有行的列和描述这些行的物理位置的信息.

3.索引的作用

索引的作用就是便于检索表中行和列的子集,而不需要检查表中的每行.
​

4.创建索引

alter table  t_name  add  (primary key  / unique/ fulltext ...)  index  索引名   (字段名1,字段名2 ...);

创建索引也就是相当于创建了一个B树,有了索引后,如果索引有利于改善查询,查询优化器就可以选择索引,如果表中的数据就只有几行,查询优化器就会忽略索引检索整张表可能就更合理,表中的索引不止一个,那么优化器必须判断对于特定的sql语句使用那个索引更优

注意:
mysql是将索引当作的表的可选部件,所以必须使用 alter table 来创建和删除索引,其他数据库(sql server , oracle) 是将索引视为独立的模对象,对于sql server 和oracle数据库,使用create index 来生成索引,
对于5.0版本的mysql已经将create index 映射到了alter table 命令,但前者依然可用.

create index    dept_name  on t_table (字段);

5.查看索引

所有的数据库服务器都是允许我们来查看可用索引

show index from  t_name \G;
mysql> show index from  dept \G;
*************************** 1. row ***************************
        Table: dept
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: deptno
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

ERROR:
No query specified
结果显示表dept中一个索引,deptno的索引为primary

6.删除索引

mysql数据库: alter table  t_name drop index 索引名;  mysql 也支持drop index
对于sql server 和oracle 数据库必须使用drop index 命令删除索引
oracle数据库:  drop index 索引名 (字段名);
sql server 数据库: drop index 索引名 on t_name ;

1.唯一索引

1.作用

提供常规索引外,还作为一种机制限制索引列出现重复的值,无论是插入和修改一个索引列,数据库服务器都会检查唯一索引判断值是已存在与表中的某一行,

2. 创建唯一性索引

mysql数据库: 
mysql> alter table dept add unique index unique_dname (dname);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(2)      | NO   | MUL | NULL    |       |
| dname  | varchar(14) | YES  | UNI | NULL    |       |
| loc    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show index from dept \G;
*************************** 1. row ***************************
        Table: dept
   Non_unique: 0
     Key_name: unique_dname
 Seq_in_index: 1
  Column_name: dname
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: dept
   Non_unique: 1
     Key_name: deptno
 Seq_in_index: 1
  Column_name: deptno
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

ERROR:
No query specified

oracle 和sql server 数据库
create unique index 索引名 on t_name (字段名);

同一张表可以创建多个唯一性索引

2.多列索引

多列索引就是创建跨越多列的索引
例如:

mysql> select * from employees limit 1;
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id  | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
|         100 | Steven     | K_ing     | SKING | 515.123.4567 | AD_PRES | 24000.00 |           NULL |       NULL |            90 | 1992-04-03 00:00:00 |
+-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+
1 row in set (0.01 sec)

mysql> alter table employees add index employess_name (last_name ,first_name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employee_id    | int(6)       | NO   | PRI | NULL    | auto_increment |
| first_name     | varchar(20)  | YES  |     | NULL    |                |
| last_name      | varchar(25)  | YES  | MUL | NULL    |                |
| email          | varchar(25)  | YES  |     | NULL    |                |
| phone_number   | varchar(20)  | YES  |     | NULL    |                |
| job_id         | varchar(10)  | YES  | MUL | NULL    |                |
| salary         | double(10,2) | YES  |     | NULL    |                |
| commission_pct | double(4,2)  | YES  |     | NULL    |                |
| manager_id     | int(6)       | YES  |     | NULL    |                |
| department_id  | int(4)       | YES  | MUL | NULL    |                |
| hiredate       | datetime     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
11 rows in set (0.01 sec)

mysql> show index from employees \G;
......
*************************** 4. row ***************************
        Table: employees
   Non_unique: 1
     Key_name: employess_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 107
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 5. row ***************************
        Table: employees
   Non_unique: 1
     Key_name: employess_name
 Seq_in_index: 2
  Column_name: first_name
    Collation: A
  Cardinality: 107
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
5 rows in set (0.00 sec)

ERROR:
No query specified
  		mysql使用的Innodb存储引擎,对于该存储引擎默认使用的是B+Tree的索引类型,B-Tree索引适用于,全键值,键值范围或者键前缀查找,主要就是键前缀查找主要适用于最左前缀查找,简单的理解:就是上例中的 last_neme 和first_name ,使用desc employees; 可以看到索引作用到了last_name ,而不是first_name ,对个
  这个索引只对两种查询有效:
  1.指定的是姓名
  2.指定的是姓氏
 	 就像我们在电话薄中查找某人的电话一样,电话薄根据姓名中的姓按顺序来排序的,如果我们只知道名,那么肯定就得从第一个开始遍历,直到找到相应的名和对应的电话号,如果知道的是姓,根据姓的排序找到相应的区域再找名,就可以找到电话号码了,这样更快速,多列索引原理大致就是这样的,
  我们在创建多类索引时,得注意:
  	1.必须指定哪一列为一个列,哪一列为第二列 ,这样创建的索引才是最有用的
  	2.需要保证充分的相应时间,也可以基于不同的顺序为同一列集创建多列索引

索引的类型

索引是一种强大的工具,但是对于存在多种不同类型的数据,单一的索引策略是不能满足需求的

B树索引

现在展示的都是平衡树索引,也称为B树索引,mysql数据库,sql server 和oracle数据默认使用都是B树索引,如果没有特定的指定,我们谈论的都是平衡树索引.
B树索引以树结构组织,它有一个或者多个分支节点,分支节点又指向叶子点,分支节点用于遍历树,叶节点用于保存正真的值和位置信息.

例如:我们要在表中查找以J开头的姓名.服务器将首先从顶分支节点(根节点)开始向下查找可以看到有区域A-M和N-Z中遍历找到J是属于A-M的区域的,然后根据指针到A-C,D-F....J-M区域中遍历J开头的是属于J-M的,然后就遍历J-M的内容,根据满足开头为J值,查找到记录,根据记录查找到行,将行加入到结果集.

将我们向表中 增加.修改和更新数据的时候,服务器会尽量保持树的平衡,这样就不会出现根节点的某一侧的分支节点或者叶节点比另一个侧的分支节点或者叶子节点多不衡,服务器会通过增加,删除分支节点重新将值分配的均匀,通过保持树的均匀,不需要遍历多层分支节点,就能快速的找到的叶子点的值.

文本索引

如果数据库存储的是文档,而我们需要允许用户在文档中查找单词或者短语, 服务器打开每一个文档,然后遍历所有文本,直到查找出复符合条件的文本,这当然不是我们需要的,但传统的策略又不能满足这种条件,那该怎么办呢?
mysql sql server oracle数据库 为文档提供了专业的索引和搜索机制,
mysql和sql server 称为全文索引(mysql数据只用myISAM的存储引擎才可以使用全文索引)
oracle数据库称为Oracle Text 的强大的工具集,

如何使用索引

数据库服务器使用索引快速的定位特定表中的行,然后在访问相关表再提取出用户请求的信息.

mysql> desc employees ;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employee_id    | int(6)       | NO   | PRI | NULL    | auto_increment |
| first_name     | varchar(20)  | YES  |     | NULL    |                |
| last_name      | varchar(25)  | YES  | MUL | NULL    |                |
| email          | varchar(25)  | YES  |     | NULL    |                |
| phone_number   | varchar(20)  | YES  |     | NULL    |                |
| job_id         | varchar(10)  | YES  | MUL | NULL    |                |
| salary         | double(10,2) | YES  |     | NULL    |                |
| commission_pct | double(4,2)  | YES  |     | NULL    |                |
| manager_id     | int(6)       | YES  |     | NULL    |                |
| department_id  | int(4)       | YES  | MUL | NULL    |                |
| hiredate       | datetime     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
11 rows in set (0.01 sec)
mysql> select employee_id  ,first_name ,last_name from employees where employee_id in (100,120,110,130);
+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         100 | Steven     | K_ing     |
|         110 | John       | Chen      |
|         120 | Matthew    | Weiss     |
|         130 | Mozhe      | Atkinson  |
+-------------+------------+-----------+
4 rows in set (0.01 sec)
例:employees_id为主键,对于这条查询,服务器首先会根据employees_id索引找到的id定位到100,110,120,130的行的位置信息,然后再访问这4行,检索first_name和last_name两列.
不过,索引可以说是一张专有表,是不能满足查询所有内容的,此时就依靠查询优化器使用不同的索引来处理相同的查询了.

可以查看服务器的查询计划
	mysql使用explain来查看查询计划
	sql server 使用 set showplan_text on来查看查询计划
	oracle包含explain plan 语句,他将计划任务写到了一个专用的plan_table表里
mysql> explain select employee_id ,first_name,last_name from employees where  employee_id in (100,110,120,130)  \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)