MySQL视图与索引

#语法：
如：
CREATE [OR REPLACE] VIEW 视图名
[别名1，别名2，.....]
AS 必要的查询语句
[额外的声明 ===>  WITH READ ONLY];

又如：
CREATE [OR REPLACE] VIEW 视图名 AS  
SELECT 列1 [AS 别名1], 列2 [AS 别名2], ...  
FROM 表名  
[WHERE 条件]  
[GROUP BY 列名]  
[HAVING 条件]  
[ORDER BY 列名];  

#如：创建视图，Student_V_1 包含了一号班级编号的所有学生的学号 姓名 班级名称 生日 住址
CREATE VIEW Student_V_1
AS SELECT student.StudentNo,student.StudentName,grade.GradeName,student.BornDate,student.Address
FROM student INNER JOIN grade ON student.GradeID = grade.GradeID
WHERE student.GradeID = 1;

CREATE VIEW Student_V_2
(学号,姓名,班级名,生日,邮箱)
AS SELECT student.StudentNo,student.StudentName,grade.GradeName,student.BornDate,student.Emial
FROM student INNER JOIN grade ON student.GradeID = grade.GradeID
WHERE student.GradeID = 1;

#查询当前数据库内所有的视图
SHOW FULL TABLES WHERE Table_type = 'VIEW'; 

#查看创建视图伪表
SELECT * FROM Student_V_1;
+--------+--------------+--------+---------------------+------------------------------------+
| 学号   | 学生姓名     | 年级   | 生日                | 地址                               |
+--------+--------------+--------+---------------------+------------------------------------+
|   1000 | 郭靖         | 大一   | 1986-12-11 00:00:00 | 北京海淀区中关村大街1号            |
|   1002 | 李斯文       | 大一   | 1986-11-30 00:00:00 | 天津市和平区                       |
|   1005 | 张秋丽       | 大一   | 1986-12-31 00:00:00 | 广西桂林市灵川                     |
|   1007 | 欧阳峻峰     | 大一   | 1986-12-31 00:00:00 | 北京东城区                         |
|   1008 | 梅超风       | 大一   | 1986-12-31 00:00:00 | 河南洛阳                           |
|   8080 | 李寻欢       | 大一   | 2005-05-01 00:00:00 | 西安市碑林区                       |
|   9527 | 赵尧林       | 大一   | 2000-01-01 00:00:00 | 西安市雁塔区新家坡3号楼            |
+--------+--------------+--------+---------------------+------------------------------------+

在 SQL 中，OR REPLACE 在创建视图（CREATE VIEW）时是一个可选的关键字组合，它的作用是如果指定的视图已经存在，则替换该视图（即删除旧视图并创建新视图）。如果视图不存在，则正常创建新视图。

具体来说，当你在执行一个带有 OR REPLACE 的 CREATE VIEW 语句时：

如果该视图名在数据库中已经存在，那么旧视图会被删除，然后创建一个新的视图。
如果该视图名在数据库中不存在，那么会正常创建一个新视图。

使用 OR REPLACE 可以避免在创建视图之前先检查视图是否存在的麻烦，并允许你方便地更新一个已经存在的视图定义。

例如：

CREATE OR REPLACE VIEW my_view AS  
SELECT column1, column2  
FROM my_table  
WHERE some_condition;

如果再次执行上述语句（假设 my_view 已经存在），那么旧的 my_view 会被删除，并根据新的查询语句重新创建。

6.1.5 视图的使用规则

1、视图必须有唯一命名

2、mysql中对于视图的数量没有限制

3、创建视图的权限，必须要进行授权

4、视图支持嵌套,也就是说可以利用其他视图检索出来的数据创建新视图

5、视图内支持分组查和排序查，但是会覆盖掉原有的排序或分组机制

6、视图不能创建索引，也不能关联触发器

7、视图和表可以出现在同一查询语境内

6.1.6 视图的修改

#如替换原有视图信息
CREATE OR REPLACE Student_V_1
.......................
#比如XXX人创建视图时Email赋值赋的是生日 通过ALTER关键字可以实现视图中字段值的重新赋予
ALTER VIEW Student_V_1
(Email)
AS SELECT Email 
FROM student;


#注意一下
ALTER VIEW Student_V_1
(Email)
AS SELECT Email 
FROM student;


ALTER VIEW Student_V_2
(邮箱)
AS SELECT student.Email
FROM student INNER JOIN grade ON student.GradeID = grade.GradeID
WHERE student.GradeID = 1;

OR REPLACE 或者说替换

6.1.7 视图的删除

删除视图不会导致数据的丢失，因为视图是基于数据库的表上的一个查询定义.
DROP VIEW 视图名;

6.2 索引

索引就是MySQL中可以高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

从理论上分析，在MySql中，所有的数据类型，都可以被索引。常见的索引:普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。

6.2.1 什么是索引

模式(schema)中的一个数据库对象
在数据库中用来加速对表的查询
通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O；
与表独立存放，但不能独立存在，必须属于某个表；
由数据库自动维护，表被删除时，该表上的索引自动被删除。；
索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引。

从理论上分析，在MySql中，所有的数据类型，都可以被索引。常见的索引:普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。

索引的原理

将无序的数据变成有序的查询

1：把创建了索引的列的内容进行排序

2：对排序的结果生成相应的倒排表

3：在倒排表的内容上拼上数据地址链

4：在进行查询的时候，先拿到倒排表内容，再取出数据地址链，从而得到具体的数据

6.2.2 索引的优缺点

索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因。对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度。使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

索引的缺点是创建和维护索引需要耗费时间；耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。

6.2.3 索引分类

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了。聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快

主键与聚簇索引：如果表定义了主键（PK），那么主键就是聚簇索引。如果表没有定义主键，那么第一个非空唯一列可能会成为聚簇索引，否则InnoDB会创建一个隐藏的row-id作为聚簇索引。

InnoDB：要判断一个索引是否是非聚簇索引（实际上，MySQL 使用 InnoDB 存储引擎时，主键索引是聚簇索引，而其他索引都是非聚簇索引，也称为二级索引或辅助索引）

MyISAM：MyISAM 存储引擎将数据和索引分开存储在不同的文件中（通常以 .MYD 结尾的数据文件和以 .MYI 结尾的索引文件）。它没有聚簇索引的概念，但你可以为 MyISAM 表创建多个非聚簇索引。

常见的索引:普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。

普通索引：最基本的索引，没有任何限制
唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引：它 是一种特殊的唯一索引，不允许有空值。 
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件        的列放在最左边，依次递减。

单列索引概念

单列索引是指只包含一个列的索引，也就是说，一个索引只针对一个列进行构建。无论是普通索引、唯一索引还是主键索引，只要它们只针对一个列，都可以被称为单列索引。

多列索引概念

多列索引是指在数据库表中基于两个或多个列创建的索引。它允许数据库系统使用多个列的组合值来优化查询性能。在MySQL中，可以使用CREATE INDEX语句来创建一个多列索引，指定要包含在多列索引中的列。

用简单的话来说用简单的话来说就是创建一个索引包含多个列

多列索引（也称为复合索引或组合索引）就是在创建索引时指定包含表中的多个列。这样，当查询条件涉及到这些列时，数据库可以利用这个多列索引来更快地定位数据，从而提高查询性能。在创建多列索引时，需要指定索引中包含的列的顺序，这个顺序会影响到索引的使用效率。

6.2.4 索引设计原则

为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。

1、尽量去选择唯一性索引。

2、为经常需要去排序、分组、联合操作的字段建立索引。

3、为经常作为查询条件的字段建立索引。

4、索引并不是越多越好，严格限制索引的数目。

5、尽量使用数据量小的索引。

6、及时的去删除一些使用很少或不再使用的索引。

6.2.5 创建索引

创建索引是指在某个表的一列或多列上建立索引的行为。有三种方式创建索引：创建表的时候（声明字段）时创建索引；在已经存在的表上创建索引；在使用ALTER TABLE语句来创建索引。

创建表的时候可以直接创建索引,这种方式是最为简单和方便的，语法如下:
CREATE TABLE 表名(
  字段名  字段类型 [约束条件]
  字段名  字段类型 [约束条件]
  字段名  字段类型 [约束条件]    
  [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
  [索引别名](属性名1 [(长度)] [ASC（升） | DESC（降）])
);
#示例1：创建表的时候创建索引
CREATE TABLE index1(
    id INT,
    name VARCHAR(20),
    Sex BOOLEAN,
    INDEX(id)
);
#修改表结构的方式添加索引
ALTER TABLE 表名 ADD INDEX 索引名(字段名);
#查询某表内索引的使用情况，结果中的 Key_name 字段代表索引的名称
SHOW INDEX FROM 表名;

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |  
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| index1 |          0 | PRIMARY  |            1 | id          | A         |      123456 |     NULL | NULL   |      | BTREE      |         |               |  
| index1 |          1 | idx_name |            1 | name        | A         |       65432 |     NULL | NULL   | YES  | BTREE      |         |               |  
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

在这个例子中：
#删除索引
DROP INDEX 索引名 ON 表名;

mysql> DROP INDEX id ON index1;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0


#示例2：创建唯一性索引
CREATE TABLE index2(
    id INT unique,            #通过申明约束去创建===>自动来的
    name VARCHAR(20),
    Unique INDEX index2_id(id asc)    #通过专门去做索引创建===>
);

注意:

id列同时指定了UNIQUE约束和一个名为index2_id的UNIQUE INDEX，
这是不必要的，因为UNIQUE约束本身就会为该列创建一个唯一索引。

正确写法：
CREATE TABLE index2(  
    id INT UNIQUE,            # 通过声明约束来创建唯一索引  
    name VARCHAR(20)  
);
如果确实想要为id列单独创建一个唯一索引（尽管这是多余的，因为UNIQUE约束已经做了同样的事情），
那么应该去掉列定义中的UNIQUE约束，并只保留UNIQUE INDEX定义。
但是，请注意，这通常是不必要的，因为UNIQUE约束已经足够：

或者：
CREATE TABLE index2(  
    id INT,  
    name VARCHAR(20),  
    UNIQUE INDEX index2_id(id)    # 通过创建索引来确保唯一性（这通常是冗余的）  
);

mysql> SHOW INDEX FROM index2;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| index2 |          0 | index2_id |            1 | id          | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)



ALTER TABLE index2 ADD INDEX index2_name(name);
mysql> SHOW INDEX FROM index2;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| index2 |          0 | index2_id   |            1 | id          | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| index2 |          1 | index2_name |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+



#示例3：创建单列索引
CREATE TABLE index3(  
    id int,  
    Subject varchar(30),  
    INDEX index3_st(Subject(10)) -- 正确的前缀索引定义  
);
INDEX index3_st(Subject(10))定义了一个名为index3_st的索引，
该索引只包含Subject列前10个字符的值。这意味着，
任何基于Subject列前10个字符的查询都可以使用这个索引来加速查询，
但是如果查询涉及到Subject列超过前10个字符的部分，那么这个索引将不会被使用。


#示例4：创建多列索引
CREATE TABLE index_4(
    id int,
    name VARCHAR(20),
    sex CHAR(4),
    INDEX index4_ns(name,sex)
);

查表发现在Key_name 字段这两个索引的名称都为index4_ns,说明他两使用同一个索引index4_ns
mysql> show index from index_4;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| index_4 |          1 | index4_ns |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| index_4 |          1 | index4_ns |            2 | sex         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+


使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。
如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。
也就是说多个单列索引与单个多列索引的查询效果不同，因为执行查询时，
MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

#还可以通过 CREATE INDEX 去创建索引
首先要保证已经存在表，才能够使用这个命令去创建索引.
在已经存在的表上，可以直接为表上的一个或几个字段创建索引。
CREATE [ UNIQUE（唯一） | FULLTEXT（全文） | SPATIAL（空间的） ]  INDEX 索引名 ON 表名 (属性名 [ (长度) ] [  ASC | DESC] );

CREATE TABLE index5(
    id int,
    name VARCHAR(20),
    sex CHAR(4)
);
#为 name 字段创建普通索引
CREATE INDEX index5_name ON index5 (name(10));
CREATE [ UNIQUE（唯一） | FULLTEXT（全文） ] INDEX index5_name ON index5 (name(10));

mysql> CREATE INDEX index_5 ON index5 (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE UNIQUE INDEX index_5_id ON index5(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from index5;
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| index5 |          0 | index_5_id |            1 | id          | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| index5 |          1 | index_5    |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)


#还可以通过ALTER TABLE语句创建索引
首先要保证已经存在表，才能够使用这个命令去创建索引.
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (属性名 [ (长度) ] [  ASC | DESC] );

CREATE TABLE index6(
    id int,
    name VARCHAR(20),
    sex CHAR(4)
);

ALTER TABLE index6 ADD INDEX index6_name (name(10));

查看创建索引

mysql> show index from student\G;
*************************** 1. row ***************************
        Table: student
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: StudentNo
    Collation: A
  Cardinality: 11
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: student
   Non_unique: 0
     Key_name: IdentityCard
 Seq_in_index: 1
  Column_name: IdentityCard
    Collation: A
  Cardinality: 11
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: student
   Non_unique: 1
     Key_name: Email
 Seq_in_index: 1
  Column_name: Email
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
3 rows in set (0.01 sec)


mysql> show create table grade;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| grade | CREATE TABLE `grade` (
  `GradeID` int NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `GradeName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`GradeID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1、示例1：创建表时建立索引

2、示例2：建立唯一性索引

3.示例3：建立单列索引

4、示例4：建立多列索引

6.2.6 索引命中分析

explain 命令

EXPLAIN分析结果的含义:

table：这是表的名字。

type：连接操作的类型，ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

possible_keys：可能可以利用的索引的名字

Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。

key_len：索引中被使用部分的长度，以字节计。

ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行

rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1

Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响

示例：

mysql> SELECT * FROM emp WHERE id = 1;
+----+--------+------+-------------+----------------------+-------------+---------+
| id | name   | age  | tel         | email                | password    | dept_id |
+----+--------+------+-------------+----------------------+-------------+---------+
|  1 | 张三   |   25 | 13800138000 | zhangsan@example.com | Password123 |       1 |
+----+--------+------+-------------+----------------------+-------------+---------+
1 row in set (0.00 sec)


mysql> EXPLAIN SELECT * FROM emp WHERE id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+--
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | r
+----+-------------+-------+------------+-------+---------------+---------+---------+--
|  1 | SIMPLE      | emp   | NULL       | const | PRIMARY       | PRIMARY | 4       | c
+----+-------------+-------+------------+-------+---------------+---------+---------+--
1 row in set, 1 warning (0.00 sec)

MySQL索引的优化

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。

何时使用聚集索引或非聚集索引

B-Tree可被用于sql中对列做比较的表达式，如=, >, >=, <, <=及between操作

若like语句的条件是不以通配符开头的常量串，MySQL也会使用索引。比如，SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'或SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'可以利用索引，而SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'（以通配符开头）和SELECT * FROM tbl_name WHERE key_col LIKE other_col（like条件不是常量串）无法利用索引。对于形如LIKE '%string%'的sql语句，若通配符后面的string长度大于3，则MySQL会利用Turbo Boyer-Moore algorithm算法进行查找.

若已对名为col_name的列建了索引，则形如"col_name is null"的SQL会用到索引。

对于联合索引，sql条件中的最左前缀匹配字段会用到索引。

若sql语句中的where条件不只1个条件，则MySQL会进行Index Merge优化来缩小候选集范围

MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引（版本不同，可能会有变化）。

6.2.7 删除索引

删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。

DROP INDEX 索引名 ON 表名;

6.2.8 索引的使用场景和索引的优化

1) 快速查找符合where条件的记录。  
2) 快速确定候选集。若where条件使用了多个索引字段，则MySQL会优先使用能使候选记录集规模最小的那 个索引，以便尽快淘汰不符合条件的记录。  
3) 如果表中存在几个字段构成的联合索引，则查找记录时，这个联合索引的最左前缀匹配字段也会被自动作 为索引来加速查找。 例如:若为某表创建了3个字段(c1, c2, c3)构成的联合索引，则(c1), (c1, c2), (c1, c2, c3)均 会作为索引，(c2, c3)就不会被作为索引，而(c1, c3)其实只利用到c1索引。  
4) 多表做join操作时会使用索引（如果参与join的字段在这些表中均建立了索引的话）。  
5) 若某字段已建立索引，求该字段的min()或max()时，MySQL会使用索引。  
6) 对建立了索引的字段做sort或group操作时，MySQL会使用索引。

索引的优化

动作描述	使用聚簇索引	使用非聚簇索引
列经常被分组查询	1	1
返回某范围内的数据	1	0
一个或极少的范围值	0	0
小数目的不同值	1	0
大数目的不同值	0	1
频繁更换的列	0	0
外键列	1	1
主键列	1	1
频繁修改的列	0	0.5

6.2.9 聚餐索引和非聚簇索引

1、非聚簇索引（二级索引）

索引节点的叶子页面就好比一片叶子。叶子头便是索引键值

会利用索引，先在索引树中快速检索到 id，但是要想取到id对应行数据，必须找到该行数据在硬盘中的存储位置。

因此MYISAM引擎的索引，叶子页面上不仅存储了主键id 还存储着数据存储的地址信息。

像这样的索引就称为非聚簇索引。它的二级索引与主键索引类似。

2、聚簇索引（一级索引）

对于非聚簇索引来说，每次通过索引检索到所需行号后，还需要通过叶子上的磁盘地址去磁盘内取数据（回行）消耗时间。为了优化这部分回行取数据时间，InnoDB 引擎采用了聚簇索引。

聚簇索引，即将数据存入索引叶子页面上。对于 InnoDB 引擎来说，叶子页面不再存该行对应的地址，而是直接存储数据。

这样就可以避免回行操作所带来的时间消耗。

关于查询时间，一般认为 MyISAM 牺牲了功能换取了性能，查询更快。但事实并不一定如此。多数情况下，MyISAM 确实比 InnoDB 查的快。但是查询时间受多方面因素影响。InnoDB 查询变慢得原因是因为支持事务、回滚等等，使得 InnoDB的叶子页面实际上还包含有事务id（换句话说就是版本号）以及回滚指针。

在二级索引方面 InnoDB与MyISAM 有很大的区别

InnoDB默认对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。

3、索聚集引和非聚集索引两者的区别：

聚簇索引，索引和数据是保存在同一个文件的，非聚簇索引的索引和数据是分开来保存的，所以在查询上，聚簇索引的效率比非聚簇索引高，聚簇索引查找到索引时，因为索引文件和数据文件绑定在一起的，所以找到了索引也代表着查找到数据了，而非聚簇索引需要进行回表操作。

1）聚集索引：聚簇索引是一种数据的存储方式，它的数据行只存放在索引（B+树）的叶子中，内部节点不存放数据。

聚集索引选取规则:（聚簇索引默认是主键）
•   如果存在主键，主键索引就是聚集索引。

•    如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

•    如果表没有主键，且没有合适唯一索引，InnoDB会自动生成一个rowid作为隐藏的聚集索引。

2）非聚集索引：非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。

妍妍的宝贝

关注

21
点赞
踩
18

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL视图与索引

视图通过定制的方式显示来自一个表或多个表的数据。视图的本质是数据库对象，如果成功的创建了视图，用户可以像操作普通表一样查询甚至修改视图内的数据。视图的本身是没有任何的数据的，它只是对表的一个查询结果进行了实体化的反馈。有关于视图所有的定义，保存在数据字典内，创建视图所基于的表称为"基表"。模式(schema)中的一个数据库对象在数据库中用来加速对表的查询通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O；与表独立存放，但不能独立存在，必须属于某个表；
复制链接

扫一扫