数据库---数据库索引及优化

索引（Index）

提高效率

如果没有索引，那么需要进行全表扫描，会非常慢，当数据量少的时候适用，数据量大的时候不适用。
使用索引我们可以简单理解为：排好序的快速查找结构。

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

主键就是一种索引，MySQL底层的实现就是为主键建立一个二叉排序树。
但是当一个表里的数据真的很多很多时索引也会很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

索引的优缺点：

优点

1、类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）。
2、通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

缺点

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

MySQL索引

几种索引类型：

1.普通索引 key
2.唯一索引 unique key
3.主键索引 primart key
4.组合索引 index(name,age,gender)
5.全文索引

MySQL的索引结构

BTree索引
Hash索引
full-text全文索引
R-Tree索引
我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引，后面三个只要了解就可以。
InnoDB 存储引擎中的 B+ 树索引。

B+ 树就是从二叉查找树，平衡二叉树和 B 树这三种数据结构演化来的。（详见二叉排序树）

B 树

因为二叉树的节点将会非常多，高度也会极其高，我们查找数据时也会进行很多次磁盘 IO，我们查找数据的效率将会极低。
为了简化这个问题，B树出现了。

B 树（Balance Tree）：单个节点可以存储多个键值和数据的平衡树。
（已经不再是二叉树了，但是是平衡树）

根节点的p1指向小于17的节点，p2指向17到23之间的节点，p3指向大于35节点。
三层只需要三次IO

B+ 树

B+ 树是对 B 树的进一步优化。B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。

B+ 树和 B 树有什么不同

①B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。
之所以这么做是因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。
如果不存储数据，那么就会存储更多的键值树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。
另外，B+ 树的阶数是等于键值的数量的，如果我们的 B+ 树一个节点可以存储 1000 个键值，那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据。
一般根节点是常驻内存的，所以一般我们查找 10 亿数据，只需要 2 次磁盘 IO（3层）。

②因为 B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。
通过上图可以看到，在 InnoDB 中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

哈希索引（Hash索引）

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

聚集索引 VS 非聚集索引：

在 MySQL 中，B+ 树索引按照存储方式的不同分为聚集索引和非聚集索引。

①聚集索引（聚簇索引）：以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。
这是因为 InnoDB 是把数据存放在 B+ 树中的，而 B+ 树的键值就是主键，在 B+ 树的叶子节点中，存储了表中所有的数据。
这种以主键作为 B+ 树索引的键值而构建的 B+ 树索引，我们称之为聚集索引。

②非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。

覆盖索引（Covering Index，索引覆盖）

就是select的数据列只用从索引中就能够取得，不必读取数据行，就是查询列要被所建的索引覆盖。
建立了索引index(col1,col2,clo3)，查询时候没有使用select *，也没有用select col1,col2,clo3,col4,col5，
而是使用select col1,col2,col3，查询列要被所建的索引覆盖。

索引的创建、删除和查看

1、ALTER TABLE
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

2、CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

不能用CREATE INDEX语句创建PRIMARY KEY索引。

3、删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引。

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

4、查看索引

mysql> show index from tblname;
mysql> show keys from tblname;

索引的使用场景

需要创建索引的情况

1、主键自动建立唯一索引 Primary Key = Unique Key + Not Null；
2、频繁作为查询条件的字段应该创建索引；
3、查询中与其它表关联的字段，外键关系建立索引；
4、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度；
5、查询中统计或者分组字段；
6、单值/组合索引的选择问题：在高并发下倾向创建组合索引 index(name,age,gender)。

不需要创建索引的情况

1、表记录太少；
2、经常增删改的表；
3、频繁更新的字段的内容；
4、Where条件里用不到的字段不创建索引，如果根据银行卡号查找就要建立索引
5、数据重复且分布平均的表字段（国籍，男女）

索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高

SQL中的逻辑删除和物理删除

删除又分为逻辑删除和物理删除。

二者的区别：

一、所谓的逻辑删除其实并不是真正的删除，而是在表中将对应的是否删除标识（is_del）或者说是状态字段（status）做修改操作。比如0是删除，1是未删除。
在逻辑上数据是被删除的，但数据本身依然存在库中。
对应的sql语句一般是这样的：

update... set status/is_del=...;

这样在做查询操作的时候，就可根据此字段进行查询，有删除标识的即可不显示。

二、物理删除就是真正的从数据库中做删除操作了，对应的sql语句为

delete ... where ...

做物理删除操作的数据将会不在库中了。

逻辑删除的目的

1、为了大数据分析，直接删除就没有数据了 ；
2、删除后索引维护成本高。

索引优化、Sql优化

索引已经大大提高了效率，那么sql的优化一定程度上就是避免索引失效。
1、尽量全值匹配：
当建立了索引列后，能在where条件中使用索引的尽量所用
2、遵循最佳左前缀法则：
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中间列。
index(name,age,gender) 组合索引，where后面是name才会使用索引，name是火车头，age和gender是车厢（车厢谁许也不能乱）。

where name='zhangsan' and age=23;//可以
where name='zhangsan';//name作为开头上面的索引是有效的，可以
where age=23, gender='男';//name不作为开头索引无效，全表扫描
where name='zhansgan' and gender='男' ;//中间不能断，只用了索引的一部分name

3、不在索引列上做任何操作（计算、函数、(自动or手动)类型转换）
会导致索引失效而转向全表扫描
4、尽量使用覆盖索引(只访问索引的查询,索引列和查询列一致)
减少使用 select * ，使用select name,age,gender
5、不使用不等于(!= 或者<>)
无法使用索引会导致全表扫描
6、不使用is null，is not null
7、like 不以通配符开头(’%abc…’)
会导致mysql索引失效，而变成全表扫描的操作
8、少用or
用它来连接时会索引失效