索引

索引

概念:它是一种特殊的文件，它包含着对数据表里所有记录的引用指针，它是对数据库 一列或多列 的值进行排序的一种结构。索引排序形成B+树。

作用:加快数据库的查询速度，提高表的搜查效率而对某些字段中的值建立目录。

索引添加位置: 主键，外键，排序列 / (在表中的字段上添加索引)

索引的分类:

1. 普通索引

   基本的索引，他没有限制。

   1. 单列索引

      一个索引只包含单个列，一个表可以有多个单列索引。

   2. 多列索引

      一个索引包含多个列，一个表只能有一个多列索引

2. 唯一索引

   唯一索引关键字为 unique index , 创建唯一约束时自动创建唯一索引

3. 主键索引

   创建主键时，自动创建全文索引。

4. 全文索引

   全文索引关键字为 fulltextindex,不支持中文

另外 还有分为 聚集索引(索引、数据在一起)、非聚集索引(索引、数据分开)。

索引操作

--创建索引有三种方式
--多列索引的创建，与单列索引相同。指示列名处有多处列名 逗号隔开
--唯一索引关键字为 unique index , 创建唯一约束时自动创建唯一索引

--1
create index #索引名 on #表名(表中的列名);
    --索引名，为了方便管理。
    --idx_ / 索引前标
    
--2
alter table #表名 add index #索引名(列名);

--3 创建表时创建索引
create table #表名(
    id int primary key auto_increment, -- 主键 ， 自增长
    index #索引名 (列名) -- 在创建表的时候创建
)engine=myIsam; -- 数据库引擎

--查看索引
show index from #表名 ;
    --创建主键的时候自动创建主键索引
    
--删除索引
drop index #索引名 on #表名;

多列索引适应场景

最左原则 创建的多列索引的最左边的那一列

1. 匹配全职，对索引中的所有列都具体制定

2. 匹配最左列

3. 匹配部分最左列

4. 匹配第一列范围查询 (like a% ok like %a no like %a% no)

索引不会使用的场景

1. 多列索引，查询不符合最左原则

2. 模糊查询时 查询条件 为 %XXX 或者 %XXX%

3. 条件中有or (OR两边都包含索引 ，还是会使用索引)

4. 如果mysql估计使用全表扫描比索引快

索引的不利

1. 创建索引、维护索引耗费时间

2. 索引需要占用物理空间

3. 对数据库进行增、删、改时，索引也会动态改变。

索引设计原则

应当

1. 经常所搜的列上

2. 作为主键的列上

3. 经常用在链接的列上

4. 需要根据范围进行搜索的列上

5. 经常需要进行排序的列上

6. 经常使用在 where 句子上的列

不应当

1. 很少使用的列

2. 数值很少的列

3. 图片，地址，二进制列

4. 修改性能大于索引性能的列

数据库引擎

数据引擎有10种;

常见的引擎特点:

1. MyISAM / 不支持事务

2. InnoDB / 安装时，默认数据库引擎

3. FEDERATED / 支持数据库分布

engine=myIsam / 使用某种引擎

 

 

sql语句优化

explain select语句 / 查看是否使用索引

where子句的优化

1. like语句的优化 : 不要在关键字前边加 %

2. 使用nuion all 替换 or

3. 不要使用 != 、<> / 使用 uion all 来查询

4. between 替换 in (in的数据是连续的) 、 exists 替换 in ，not exists 替换 not in 、left join 替换 in

5. 避免使用 is null 或 is not null / MySQL可以结合col_name（列名）= constant_value（常量）的使用 来对 col_name（列名） IS NULL进行相同的优化 / 列名默认值

6. 尽量不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统可能无法正确使用索引

7. 在使用索引字段作为条件时，如果该索引是联合索引 尽量满足最左原则

8. 不要 使用 * 替换列名

表连接优化

表连接定义 : 多个表通过笛卡尔乘积(笛卡尔乘积:两张表每条数据一一组合, 需要通过条件筛选)联合在一起实现查询

数据库表连接 : 内连接(inner join) ; 外连接 (左外 / left join ; 右外连接 / right join ; 全外连接 / full join mysql不支持)

MySQL的JOIN实现原理 : Nested Loop Join (嵌套循环连接) ，Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。

优化 :

1. 在外键上添加索引

2. 驱动表的选择非常重要,驱动表的数据小可以显著降低扫描的行数,条件中尽量能够过滤一些行将驱动表变得小一点，用小表去驱动大表

3. left join/right join和inner join时,尽量用inner join避免外联结和null,如果where条件中含有右表的非空条件(除开is null),则left join语句等同于join语句,可直接改写成join语句。

4. 在mysql的 left join 中条件放在 on 后面和在 where 后面是不同的

 

其他

1. 主键约束 与 唯一约束 的区别 主键约束不允许为空。

2. 数据参考完整性 -> 外键约束

3. 在列前边 加上 binary 就支持大小写 / where binary name like %A%

4. 衍生表(放在from后边作为一张表)必须有自己的别名