MySQL学习笔记——索引和视图

1. 索引（index）和管理索引
2. 常见索引的种类
   1. 1、主键索引
   2. 2、唯一索引
   3. 3、普通索引
   4. 4、全文索引
   5. 5、空间索引 SPATIAL
3. 视图与管理视图

索引（index）和管理索引#

模式中的一个数据库对象

作用：在数据库中用来加速对表的查询

创建：自动在主键和唯一键上面创建索引

 通过使用快速路径访问方法快速定位数据，减少了磁盘的I/O

 与表独立存放，但不能独立存在，必须属于某个表

 由数据库自动维护，表被删除时，该表上的索引自动被删除

 索引的作用类似于书上的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引

 

索引：在经常查询的字段上面建立索引，利用index对查询进行优化，（index可以避免对表的一个全面扫描）

原理：当以某个字段建立一个索引的时候，数据库就会生成一个索引页，索引页不单单保存索引的数据，还保存了索引在数据库的具体的物理地址

注意：如果表的列很少，不适合建立索引。当执行过很多次的insert、delete、update后，会出现索引碎片。影响查询速度，我们应该对索引进行重组

重组方法：drop index index_name;

　　　　　create index index_name on table(column)

 

# 手动创建索引
CREATE INDEX index_tb_dept_name
ON tb_dept(NAME);

# 使用索引，在where之后加上索引，提高查询效率
SELECT * FROM tb_dept WHERE NAME='Tom'

 

常见索引的种类#

主键索引 PRIMARY、唯一索引 UNIQUE、普通索引 INDEX（多字段为组合索引）、全文索引 FULLTEXT、空间索引 SPATIAL

参考：深入理解MySQL索引原理和实现——为什么索引可以加速查询？

1、主键索引#

即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；

比如user的id上的主键索引，以PRIMARY命名

主键也可以是复合主键，即有多个字段，比如

2、唯一索引#

用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；

比如urn和modified_date字段需要唯一，命名为uix_表名_字段1_字段2

3、普通索引#

用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col')；

比如user表的username上加普通索引，命名为idx_表名_字段名

组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

组合索引，注意字段的顺序，遵循最左匹配原则，参考：Mysql联合索引最左匹配原则

4、全文索引#

用大文本对象的列构建的索引

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

比如，命名为fti_表名_all

5、空间索引 SPATIAL#

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。

创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。

参考：详细介绍mysql索引类型：FULLTEXT、NORMAL、SPATIAL、UNIQUE

 

视图与管理视图#

视图的好处：可以限制对数据的访问、可以是复杂的查询变得简单、提供了数据的独立性、提供了对相同数据的不同显式

# 创建视图
CREATE VIEW emo_v_10 AS
SELECT NAME AS '名字',sex '性别',age '年龄'
FROM tb_emp
WHERE dept_id=2;

# 使用视图
SELECT * FROM emo_v_10