知识笔记--sql高级2.索引

3. 索引

1.性能下降SQL慢 执行时间长 等待时间长

1.查询数据过多
2.关联了太多的表，太多join
3.没有利用到索引

2.索引简介

1.索引是什么(重点)

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

• 索引的本质：索引是数据结构
• 索引的目的：在于提高查询效率
• 索引是排好序的快速查找数据结构
  
  二叉树弊端之一：二叉树很可能会发生两边不*衡的情况。
  B-TREE：(B:balance) 会自动根据两边的情况自动调节，使两端无限趋*于*衡状态。可以使性能最稳定。(myisam使用的方式)
  B-TREE弊端：(插入/修改操作多时，B-TREE会不断调整*衡，消耗性能)从侧面说明了索引不是越多越好。
  B+TREE：Innodb 所使用的索引
  我们*常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

2.索引的优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

3.索引的劣势

1. 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
2. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
3. 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

4.索引的分类

1.单值索引
即一个索引只包含单个列，一个表可以有多个单列索引
2.唯一索引
索引列的值必须唯一，但允许有空值
3. 复合索引
即一个索引包含多个列
4. 主键索引
设定为主键后数据库会自动建立索引，innodb为聚簇（聚集）索引
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

5.基本语法

# 1. 创建
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length))
ALTER mytable ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length)) 

# 2. 删除
DROP INDEX [indexName] ON mytable

# 3. 查看
SHOW INDEX FROM table_name\G

# 4. 使用ALTER命令
# 有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) # 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) # 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次
ALTER TABLE tbl_name ADD INDEX index_name (column_list) # 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) #该语句指定了索引为 FULLTEXT ，用于全文索引。 

6.mysql索引结构

B树，B+树，聚簇索引与非聚簇索引，全文索引，hash索引，R树索引