索引原理与数据库优化
目录
为什么要有索引?
1.一般的应用系统，读写比例在10:1左右
2.加速查询速度的优化
索引原理
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
建立数型结构
索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据
有舍必有得 树形结构越复杂 查询速度越快 但是操作越慢
磁盘IO与预读
因为磁盘读取数据io时间过大 所以计算机操作系统做了一些优化，**当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，
简称 磁盘预读
索引的数据结构
常见的MySQL主要有两种结构：Hash索引和B+ Tree索引，我们使用的是InnoDB引擎，默认的是B+树
1. 平衡树 b树
树状图是一种数据结构，它是由n（n>=1）个有限结点组成一个具有层次关系的集合。把它叫做“树”是因为它看起来像一棵倒挂的树，也就是说它是根朝上，而叶朝下的。
特点：每个结点有零个或多个子结点；没有父结点的结点称为根结点；每一个非根结点有且只有一个父结点；除了根结点外，每个子结点可以分为多个不相交的子树
2. b+树
B+树是通过二叉查找树，再由平衡二叉树，B树演化而来
特性：
- 索引字段要尽量的小
- 索引的最左匹配特性
数据库中的B+树索引
分为聚集索引（clustered index）和辅助索引（secondary index），
聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。
聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息
聚集索引
聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录
聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可
辅助索引
1.表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。
2.叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。
3.每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。
区别
聚集索引
1.纪录的索引顺序与无力顺序相同
因此更适合between and和order by操作
2.叶子结点直接对应数据
从中间级的索引页的索引行直接对应数据页
3.每张表只能创建一个聚集索引
非聚集索引
1.索引顺序和物理顺序无关
2.叶子结点不直接指向数据页
3.每张表可以有多个非聚集索引，需要更多磁盘和内容
多个索引会影响insert和update的速度
覆盖索引
覆盖索引？
覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。
当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。
如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。
当我们通过SQL语句：select key2 from covering_index_sample where key1 = 'keytest';的时候，就可以通过覆盖索引查询，无需回表。
MySQL常用的索引
普通索引INDEX：加速查找
唯一索引：
-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
-唯一索引UNIQUE:加速查找+约束（不能重复）
联合索引：
-PRIMARY KEY(id,name):联合主键索引
-UNIQUE(id,name):联合唯一索引
-INDEX(id,name):联合普通索引
索引的两大类型hash与btree
#我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
B+ Tree索引和Hash索引区别？
哈希索引适合等值查询，但是无法进行范围查询
哈希索引没办法利用索引完成排序
哈希索引不支持多列联合索引的最左匹配规则
如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题
##
创建/删除索引的语法
#方法一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引：DROP INDEX 索引名 ON 表名字;
#方式一
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index ix_name(name) #index没有key
);
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index(name) #index没有key
);
#方式二
create index ix_age on t1(age);
#方式三
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);
#查看
mysql> show create table t1;
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(1) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
UNIQUE KEY `uni_id` (`id`),
KEY `ix_name` (`name`),
KEY `ix_age` (`age`),
KEY `ix_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
如何命中索引
在条件中不能带运算或者函数,必须是"字段 = 值"
4.数据对应的范围小一点
# between and > < >= <= != not in
6.多条件的情况
# and 只要有一个条件列是索引列就可以命中索引
# or 只有所有的条件列都是索引才能命中索引
# 字段 能够尽量的固定长度 就固定长度
不能命中索引条件
# 1.所查询的列不是创建了索引的列
# 2.在条件中带运算或者函数 不能命中,必须是"字段 = 值"
# 3.如果创建索引的列的内容重复率高也不能有效利用索引
# 重复率不超过10%的列比较适合做索引
# 4.数据对应的范围如果太大的话,也不能有效利用索引
# between and > < >= <= != not in
# 5.like如果把%放在最前面也不能命中索引
# 6.多条件的情况
# and 只要有一个条件列是索引列就可以命中索引
# or 只有所有的条件列都是索引才能命中索引
什么是联合索引
什么是联合索引
联合主键 联合唯一
# 7.联合索引
# 在多个条件相连的情况下,使用联合索引的效率要高于使用单字段的索引
# where a=xx and b=xxx;
创建联合索引
# 对a和b都创建索引 - 联合索引
# create index 索引名 on 表名(字段1,字段2)
# create index ind_mix on s1(id,email)
# 1.创建索引的顺序id,email 条件中从哪一个字段开始出现了范围,索引就失效了
# select * from s1 where id=1000000 and email like 'eva10000%' 命中索引
# select count(*) from s1 where id > 2000000 and email = 'eva2000000' 不能命中索引
# 2.联合索引在使用的时候遵循最左前缀原则
# select count(*) from s1 where email = 'eva2000000@oldboy';
# 3.联合索引中只有使用and能生效,使用or失效
# 字段 能够尽量的固定长度 就固定长度
# varchar 尽量往后面放
mysql 神器 explain
# 查看sql语句的执行计划
# explain select * from s1 where id < 1000000;
# 是否命中了索引,命中的索引的类型
关于explain，如果大家有兴趣，可以看看这篇博客，他总结的挺好的：http://www.cnblogs.com/yycc/p/7338894.html
查询优化器？
一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。
在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。
这个成本最低的方案就是所谓的执行计划。优化过程大致如下：
1、根据搜索条件，找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价，找出成本最低的那一个
开启慢日志
知道mysql可以开启慢日志
# 慢日志是通过配置文件开启
# 如果数据库在你手里 你自己开
# 如果不在你手里 你也可以要求DBA帮你开
数据表\库的导入导出
# 备份表 :homwork库中的所有表和数据
# mysqldump -uroot -p123 homework > D:\s23\day42\a.sql
# 备份单表
# mysqldump -uroot -p123 homework course > D:\s23\day42\a.sql
# 备份库 :
# mysqldump -uroot -p123 --databases homework > D:\s23\day42\db.sql
# 恢复数据:
# 进入mysql 切换到要恢复数据的库下
# sourse D:\s23\day42\a.sql
mysqldump
语法:mysqldump -u用户名 -p -B（又不用自己创库） -d 库名>路径(g:\av\av.sql)
备份:mysqldump -uroot -p -B -d 库名>(g:\av\av.sql)
语法mysql -uroot -p < (g:\av\av.sql)
还原:mysql -uroot -p < 路径(g:\av\av.sql)
7表联查速度慢怎么办?52
# 1.表结构
# 尽量用固定长度的数据类型代替可变长数据类型
# 把固定长度的字段放在前面
# 2.数据的角度上来说
# 如果表中的数据越多 查询效率越慢
# 列多 : 垂直分表
# 行多 : 水平分表
# 3.从sql的角度来说
# 1.尽量把条件写的细致点儿 where条件就多做筛选
# 2.多表尽量连表代替子查询
# 3.创建有效的索引,而规避无效的索引
# 4.配置角度上来说
# 开启慢日志查询 确认具体的有问题的sql
# 5.数据库
# 读写分离
# 解决数据库读的瓶颈
非学，无以致疑；非问，无以广识
