mysql进阶 索引基础知识详解

1 sql 性能下降原因

• 查询语句写的烂

• 索引失效

• 关联查询太多join

• 服务器调优及各个参数设置（缓冲，线程数）

2 sql语句执行顺序

正常的语句

SELECT DISTINCT
   
FROM
  
      ON

WHERE

GROUP BY

Having

ORDER BY

LIMIT

sql 语句的执行

FROM 

ON

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

ORDER BY

LIMIT

• from

from后面是笛卡尔积

• on

on条件（无论条件是否为true）是在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户

• join

左连接：返回左表所有记录以及右表中与之相等的字段
右连接：返回右表中所有记录以及左表中连接字段相等的记录

• where

where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

3 七种join理论

• 表中本来的数据

• 内连接

SELCET 
FROM A
INNER JOIN B
ON A.key=B.key

• 左连接

SELECT 
FROM A
LEFT JOIN B
ON A.key=B.key

• 右连接

SELECT 
FROM A
RIGHT JOIN B
ON A.key=B.key

• 左连接+where

SELECT 
FROM A
LEFT JOIN B
ON A.key=B.key
WHERE B.key IS NULL

• 右连接 + Where
  

SELECT 
FROM  A
RIGHT JOIN B
ON A.key = B.key
WHERE A.key IS NULL

• 全连接
  

SELECT 
FROM A
left  JOIN B
ON A.key=B.key

# 链接两个select 操作并对结果进行去重
UNION

SELECT 
FROM A
RIGHT JOIN  B
ON A.Key=B.key

• 全连接+where
  

SELECT 
FROM A
LEFT JOIN B
ON A.key=B.key
WHERE B.key IS NULL 

UNION

SELECT 
FROM B
RIGHT JOIN A
ON B.Key = A.Key
WHERE A.key IS NULL

4 索引是什么

定义：索引是帮助mysql高效获取数据的数据结构即排好序的快速查找数据结构

4.1 详解

数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构的基础上实现高级查找算法。这种数据=结构就是索引

其中之一的实现方式

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上

索引，如果没有特别指明都是指B数(多路搜索树)结构组织的索引，其中的聚焦，次要，复合，前缀，唯一索引都是默认使用B+数索引

4.2 索引的优势

• 类似大学图书馆建书目索引，提高了数据检索的效率，降低数据库的io成本（提高数据的检索能力）

• 通过索引列对数据进行排序，降低数据排序的成本，降低了cpu的消耗（降低数据的排序成本）

4.3 索引的劣势

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录 因此索引列也是占用空间

• 索引大大提高了查询速度，但是却降低了更新表的速度，如对表进行insert，update和delete，由于更新表mysql不仅要保存数据，还要调整因为更新所带来键值变化后的索引信息。

4.4 索引分类

• 单值索引

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

• 唯一索引

索引列的值必须唯一，但允许有空值

• 复合索引

一个索引包含多个列

4.5 索引的基本语法

• 创建

CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))

• 删除

DROP INDEX [indexName] ON mytable;

• 查看

SHOW INDEX FROM table_name\G

• 添加数据表的索引

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,用于全文索引

4.6 索引结构和索引分析

真实的数据存在于叶子节点即3,5,9…

非叶子节点不存储真实数据，只存储索引搜索方向的数据项如17,35并不真实存在于数据项中。

4.6.1 查找数据项

查找数据项29，先把磁块1加载到内存 发生一次io，在内存中确定29在17和35间锁定p2，并将磁块3加载到内存，发生io，同理锁定磁块3的p2并将磁块8进行加载，发生第三次io

4.7 需要创建索引的情况

• 主键自动建立唯一索引

• 频繁作为查询条件的字段应该创建索引

• 查询中与其它表关联的字段，外键关系建立索引

• 索引的创建一般选择组合索引

• 查询中排序的字段，排序的字段通过索引访问会提高排序速度

• 查询中统计或分组的字段

4.8 不需要建立索引的情况

• 表记录太少

• 经常增删改的表

• where 条件用不到的字段不需要创建索引

• 某个数据列包含许多重复的内容，则其建立索引没有太大的实际效果

索引的选择性是指索引列中不同值的数目与表中记录的比，一个索引的选择性越接近1，这个索引的效率就越高