1.索引简介
①影响性能下降SQL慢，在于执行SQL时间长或者等待时间长
②影响SQL性能的常见情况
--数据过多：分库分表（根据微服务划分库，按照地域或时间分表存储，按照数据的特定字 段对分库数量求余）
--关联了太多的表，太多join，允许表出现冗余字段减少联查（SQL优化）
--没有充分利用到索引，可以通过建立索引来提高SQL执行效率
--服务器调优及各个参数的设置，可以通过调整my.cnf
2.索引的理解和用法
① 索引是帮助MySQL高效获取数据的数据结构
② 索引的目的在于，提高查询效率
③ 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引
④ 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘
3.1 AVLTree 高度平衡树
增加和删除可能需要通过一次或多次树旋转来重新平衡这个树.
具有以下特点：
它是一棵空树或它的左右两个子树的高度差的绝对值不超过1
并且左右两个子树都是一棵平衡二叉树。
3.2 多叉树
多叉树（multiway tree）允许
每个节点可以有更多的数据项和更多的子节点。2-3树，2-3-4树就是多叉树，多叉树通过重新组织节点，减少节点数量，增加分叉，减少树的高度，能对二叉树进行优化。
4.索引的优劣势
优势：
类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序或分组，降低数据排序的成本，降低了CPU的消耗。
劣势：
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
5.MySQL的索引结构
5.1 B-Tree 索引
B-Tree即B树，Balance Tree，平衡树,它的高度远小于平衡二叉树的高度。2-3树是最简单的B树结构。
B树的阶：节点的最多子节点个数。比如2-3树的阶是3，2-3-4树的阶是4。
【初始化介绍】
一颗b树，浅蓝色的块我们称之为一个磁盘块(innodb默认16kb一个磁盘块)，可以看到每个磁盘块包含几个数据项（深蓝色所示)、指向关键字具体信息的指针(红色）和指向其他磁盘块的指针（黄色所示)
如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块【查找过程】 如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
真实的情况是，3层的b树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
5.2 B+Tree 索引
①数据结构
② B+Tree与B-Tree 的区别
1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息;
B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。树的高度会更矮胖，IO次数也会更少。
2) 在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在;而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等)，这也是很多数据库和文件系统使用B+树的缘故。
3) 思考:为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引?
① B+树的磁盘读写代价更低，B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B-树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
②B+树的查询效率更加稳定―由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。|
5.3 查看数据页大小
SHOW GLOBAL STATUS LIKE '%page_size%'
为什么mysql页文件默认16K？ 假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B（字节），指针大小在Innodb源码中为6B（字节），一共就是14B（字节），那么一页里就可以存储16K/14B=1170个(主键+指针)
一颗高度为2的B+树能存储的数据为: 1170*16=18720条，
一颗高度为3的B+树能存储的数据为：1170*1170*16=21902400（千万级）
5.4 聚簇索引与非聚簇索引
聚簇索引：
-- 将数据存储与索引放到了一块，找到索引也就找到了数据。
-- 页内的记录是按照主键的大小顺序排成一个单向链表。
-- 页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表。
-- 非叶子节点存储的是记录的主键+页号。叶子节点存储的是完整的用户记录。非聚簇索引：
将数据存储与索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
澄清一个概念: innodb中，非聚簇索引又称辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。
InnoDB使用聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）
聚簇索引的好处：
由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
聚簇索引适合用在排序的场合，非聚簇索引不适合（下面有单独解释）
取出一定范围数据的时候，使用聚簇索引（下面有单独解释）
二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据
聚簇索引的限制：
对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。
一般情况下就是该表的主键。
如果没有primary key,会以(not null unique key)非空的唯一索引保存数据
内部自己生成一个字段保存数据
为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序非空的字段，而不建议用无序的id，比如uuid这种。
为什么聚簇索引适合用在排序的场合，非聚簇索引不适合？
这与索引数据的物理存储有关。聚簇索引意味着数据表的行数据是按索引键值的顺序物理存储的。这使得查询索引键范围内的行变得很高效,因为这些行数据已经排序好了。非聚簇索引的数据行不是按索引顺序存储的。索引只保存了索引键值和行指针的映射。所以查询索引键范围内的行时,仍然需要去数据表中寻找这些行,效率较低。举个例子:聚簇索引:
Index: A -> Row 1 B -> Row 2 C -> Row 3 Data Table: Row 1 Row 2 Row 3查询索引键 B 到 C 范围的行,直接读取中间的行数据即可。非聚簇索引:
Index: A -> Pointer 1 B -> Pointer 2 C -> Pointer 3 Data Table: Row 3 Row 1 Row 2查询 B 到 C 范围的行,需要先找到 Pointer 2 和 Pointer 3,然后再在数据表中查找这两行数据,效率较低。所以,聚簇索引对范围查询和排序查询有较高的效率,非聚簇索引的效率较低。但非聚簇索引更新成本较低,并且可以覆盖更多索引键。两者各有优点,实际使用时需要根据实际场景选择
为什么取出一定范围数据的时候，使用聚簇索引？
聚簇索引的数据行是按索引键值的顺序物理存储的。这使得取出索引键范围内的数据变得非常高效。具体原因有:1. 数据已经排序。取出索引键连续范围内的行数据时,由于数据已经排序,所以可以直接定位到起始行,然后顺序扫描读取需要的行,不需要再去排序或查找其他行位置,效率很高。2. 数据局部性。由于索引键相近的数据存储在一起,所以取出索引键范围内的数据时,这些数据可能已经在缓存中,不需要再读盘,直接从缓存中读取,效率也会提高很多。3. 避免随机 IO。如果不是使用聚簇索引,那么需要先在索引中查找所有符合条件的行指针,然后再在数据文件中随机读取这些指针指向的行,这样的随机 IO 会显著降低效率。因此,当需要取出索引键范围内的全部数据时(比如分页查询),使用聚簇索引可以最大限度地利用索引数据的局部性和顺序扫描来提高查询效率,避免昂贵的随机 IO 操作。举个例子,在员工表中按薪资范围取出数据:
sql SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;如果salary列上有聚簇索引,那么查询只需要定位到起始薪资行,然后顺序扫描读取中间的连续行,效率很高。如果没有聚簇索引,需要先在普通索引中查找所有符合条件的行指针,然后再随机读取这些行,效率较低。所以,对于范围扫描和排序相关的数据查询,聚簇索引通常可以带来查询性能的提升
5.5 回表
通过非聚簇索引查找到主键值之后仍然需要到聚簇索引中再查询一遍，这个过程称为回表
问题：为什么我们还需要一次
回表操作呢？直接把完整的用户记录放到叶子节点不OK吗？回答： 如果把完整的用户记录放到叶子节点是可以不用回表。但是
太占地方了，相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。
5.6 联合索引
基于多个字段创建的索引就是联合索引，也称为复合索引，比如我们创建索引create index idx on table(a,b,c) 我们称在字段a,b,c上创建了一个联合索引。同时以这三个列的大小作为排序规则。
记录先按照a列排序
a列值相同时使用b列排序
b列值相同时使用c列排序
然后将排好序的abc三列的值组织到非聚簇索引索引结构中。
1. 索引键值: 由多个字段的值组成,如(last_name, first_name, age)。2. 行指针: 每个索引键值对应的数据行指针,指向数据表中该行的物理存储位置。
3. 排序: 索引键值是按字段顺序排序的,这是联合索引的基础,可以在多个字段上高效搜索数据。
4. 索引复用: 查询可以只使用索引的前几个字段,而不必搜索所有字段,这称为索引复用,可以提高效率。
5. 最左前缀: 构建联合索引时,最常用的字段在最前,可以最大化索引复用,这就是最左前缀原则。
举例: 一个联合索引(last_name, first_name, age)- 查询last_name = 'Smith' 只需扫描last_name字段,可以复用索引。
查询 last_name = 'Smith' AND first_name = 'John' 只需扫描前两个字段,也可以复用索引。
查询 age = 30 不能复用该索引,因为age不是最左前缀。
联合索引之所以强大,是因为它可以在多个字段上进行范围扫描;它之所以高效,是因为可以通过索引复用避免不必要的磁盘IO。
6. MySQL索引分类
主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引
单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列
CREATE TABLE customer ( id INT (10) UNSIGNED AUTO_INCREMENT, customer_no VARCHAR (200), customer_name VARCHAR (200), PRIMARY KEY (id), # 主键索引 KEY idx_name (customer_name), # 单值索引 UNIQUE KEY uk_name (customer_name), # 唯一索引 KEY idx_no_name (customer_no, customer_name) # 复合索引 );
单独建索引 :
# 使用CREATE语句：CREATE [UNIQUE ] INDEX [indexName] ON table_name(column)) CREATE INDEX idx_customer_name ON customer(customer_name); # 单值索引 CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); # 唯一索引 CREATE INDEX idx_no_name ON customer(customer_no,customer_name); # 复合索引 # 使用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 ，用于全文索引。
删除索引：DROP INDEX [indexName] ON mytable;
ALTER TABLE customer drop PRIMARY KEY; # 删除主键索引 DROP INDEX idx_customer_name on customer; # 删除单值、唯一、复合索引
7. 索引的使用场景
哪些情况需要创建索引：
主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题， 组合索引性价比更高
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段
哪些情况不要创建索引：
表记录太少
经常增删改的表或者字段。
Why：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
Where条件里用不到的字段不创建索引
过滤性不好的不适合建索引
有大量重复数据的列上。
8. 索引优化的方式
四种：1.主键 2.单值 3.唯一 4.复合
8.1 性能分析（explain）
mysql5.6以后优化器做了很多改进，执行时会自动进行大量的优化，很多现象需要在5.5才能演示成功。
8.2 explain是什么?
模拟优化器查看执行计划
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
8.3 explain能干什么？
表的读取顺序
那些索引可以使用
数据读取操作的操作类型
那些索引被实际使用
表之间的引用
每张表有多少行被物理查询
8.4 explain怎么玩？（explain + SQL语句）
① 数据准备
CREATE TABLE t1(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id)); CREATE TABLE t2(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id)); CREATE TABLE t3(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id)); CREATE TABLE t4(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id)); # 以下新增sql多执行几次，以便演示 INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000))); INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000))); INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000))); INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));
② 演示：
explain select * from t1, t2, t3 where t1.id=t2.id and t2.id=t3.id;
8.5 各字段解释
① id（重要）select查询的序列号，表示查询中执行select子句或操作表的顺序
三种情况：
id相同，执行顺序由上至下。例如上图
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
执行下列语句
EXPLAIN SELECT * FROM t1 WHERE t1.content =( SELECT t2.content FROM t2 WHERE t2.content=( SELECT t3.content FROM t3 WHERE t3.content="" ) );
id为NULL最后执行。
关注点：每个id号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。
② select_type(不会用于优化)
查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
③ table
显示这一行的数据是关于哪张表的
④ partitions
代表分区表中的命中情况，非分区表，该项为null
⑤ type（重要）
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
null>system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
常见：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
null: MySQL不访问任何表或索引，直接返回结果
system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。（select * from t1 where t1.id=1）
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。（select * from t1,t2 where t1.id=t2.id）
ref: 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。（select * from t1 where t1.id<10）
index：出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。（explain select id from t1）
all：Full Table Scan，将遍历全表以找到匹配的行。（explain select * from t1）
index_merge：在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中。
ref_or_null：对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。
index_subquery：利用索引来关联子查询，不再全表扫描。
unique_subquery：该联接类型类似于index_subquery。 子查询中的唯一索引。
⑥ possible_keys
显示可能应用在这张表中的索引，一个或多个。 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
⑦ key(优化重要指标)
实际使用的索引。如果为NULL，则没有使用索引。
查询中若使用了覆盖索引，则该索引和查询的select字段重叠
⑧ key_len(重要)
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
key_len字段能够帮你检查是否充分的利用上了索引。
如何计算 1 、先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20
2 、如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf8mb3要乘 3(MySQL5.7)，如果是utf8mb4要乘4，,GBK要乘2 3 、varchar这种动态字符串要加2个字节 4、 允许为空的字段要加1个字节
索引字段最好不要为NULL，因为NULL让统计更加复杂，并且需要额外一个字节的存储空间
⑨ ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
⑩ rows
rows列显示MySQL认为它执行查询时必须检查的行数。越少越好
最后一个：extra(重要)
包含不适合在其他列中显示但十分重要的额外信息，通过这些额外信息来
理解MySQL到底将如何执行当前的查询语句。MySQL提供的额外信息有好几十个，这里只挑比较重要的介绍。Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”
这类SQL语句性能极差，需要进行优化。
在一个非索引列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序(只查询索引列的值)。
Using temporary：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。
USING index：利用索引进行了排序或分组。表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！（EXPLAIN select * from t_emp where age=30 ORDER BY name） 如果同时出现using where，表明索引被用来执行索引键值的查找; 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
Using where：表明使用了where过滤
using join buffer：使用了连接缓存，非主键关联(mysql8
Using join buffer (hash join)速度要好于 mysql5.7Using join buffer (Block Nested Loop))impossible where：where子句的值总是false，不能用来获取任何元组。（EXPLAIN select * from t_emp where false;）
select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者 对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
8.6 小结
表的读取顺序：id
id趟数越少越好，id相同执行顺序由上至下，id不同 大的先执行
查询方式：select_type
那些索引可以使用：possible_keys
数据读取操作的操作类型：type
range index all
那些索引被实际使用：key
创建的索引是否能够被实际应用
使用索引的长度：key_len
命中的索引匹配的长度(用来判断索引是否被完全利用)
计算索引长度：
utf-8
varchar(len) =使用len*3+2 (如果字段可以为null，再+1)
char(len) =使用len*3 (如果字段可以为null，再+1)
int(len) = 4 (如果字段可以为null，再+1)
表之间的引用：table
每张表有多少行被物理查询：rows
行数越少越好(多表联查时 被驱动表的rows如果使用索引了一般非常小)
额外优化信息：extra
using join buffer(多表联查)、using filesort(排序)和 using temporary(分组) 需要考虑优化
其他情况性能都可以无需优化
8.6 json格式的执行计划
EXPLAIN语句输出中缺少了一个衡量执行计划好坏的重要属性 —执行计划花费的成本，在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON。EXPLAIN FORMAT=json SELECT * FROM t_emp;
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "1.25" // 查询耗时：单位毫秒 }, "table": { "table_name": "t_emp", "access_type": "ALL", "rows_examined_per_scan": 10, "rows_produced_per_join": 10, "filtered": "100.00", "cost_info": { "read_cost": "0.25",//io耗时 "eval_cost": "1.00",//获取处理返回结果耗时 "prefix_cost": "1.25", "data_read_per_join": "800"//读取的数据量 }, "used_columns": [//投影列 "id", "name", "age", "deptId", "empno" ] } } }
9. SQL 优化数据准备
在做优化之前，要准备大量数据。接下来创建两张表，并往员工表里插入50W数据，部门表中插入1W条数据。
① 执行-建表SQL
CREATE TABLE `dept` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, ceo INT NULL , PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `emp` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `empno` INT NOT NULL , `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, PRIMARY KEY (`id`) #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;怎么快速插入50w条数据呢？ 存储过程
怎么保证插入的数据不重复？函数
以部门表分析：
id：自增长
deptName：随机字符串，允许重复
address：随机字符串，允许重复
CEO：1-50w之间的任意数字
以员工表分析：
id：自增长
empno：可以使用随机数字，或者从1开始的自增数字，不允许重复
name：随机生成，允许姓名重复
age：区间随机数
deptId：1-1w之间随机数
总结：需要产生随机字符串和区间随机数的函数。
② 执行-创建函数
set global log_bin_trust_function_creators=1; # 随机产生字符串 DELIMITER $$ CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) BEGIN DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; DECLARE return_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); SET i = i + 1; END WHILE; RETURN return_str; END $$ #用于随机产生区间数字 DELIMITER $$ CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11) BEGIN DECLARE i INT DEFAULT 0; SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)); RETURN i; END$$ #假如要删除 #drop function rand_string; #drop function rand_num;
③ 执行-存储过程
# 插入员工存储过程 DELIMITER $$ CREATE PROCEDURE insert_emp(START INT, max_num INT) BEGIN DECLARE i INT DEFAULT 0; #set autocommit =0 把autocommit设置成0 SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO emp (empno, NAME, age, deptid ) VALUES ((START+i) ,rand_string(6), rand_num(30,50), rand_num(1,10000)); UNTIL i = max_num END REPEAT; COMMIT; END$$ #删除 # DELIMITER ; # drop PROCEDURE insert_emp; #往dept表添加随机数据 DELIMITER $$ CREATE PROCEDURE `insert_dept`(max_num INT) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000)); UNTIL i = max_num END REPEAT; COMMIT; END$$ #删除 # DELIMITER ; # drop PROCEDURE insert_dept;
④ 执行-调用存储过程
#执行存储过程，往dept表添加1万条数据 DELIMITER ; CALL insert_dept(10000); #执行存储过程，往emp表添加50万条数据 DELIMITER ; CALL insert_emp(100000,500000);
⑤ 执行-批量删除表索引
批量删除某个表上的所有索引
DELIMITER $$ CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200)) BEGIN DECLARE done INT DEFAULT 0; DECLARE ct INT DEFAULT 0; DECLARE _index VARCHAR(200) DEFAULT ''; DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY' ; DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ; OPEN _cur; FETCH _cur INTO _index; WHILE _index<>'' DO SET @str = CONCAT("drop index ",_index," on ",tablename ); PREPARE sql_str FROM @str ; EXECUTE sql_str; DEALLOCATE PREPARE sql_str; SET _index=''; FETCH _cur INTO _index; END WHILE; CLOSE _cur; END$$执行批量删除（当想要删除某个表的索引时执行此SQL）
CALL proc_drop_index("dbname","tablename"); # ‘dbname ’库名称和‘tablename’表名称
10.1 单表优化
①索引优化原则
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
mysql 在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
is not null 也无法使用索引，但是is null是可以使用索引的
字符串不加单引号索引失效
② 以下两个sql，那个写法更好：
案例：
#1.1 查找姓名以"abc"开头的员工信息
#1.2 查找姓名含有"abc"的员工信息
#1.3 查找年龄不等于25的员工
#1.4 查找姓名不为空的员工信息
#1.5 查找姓名等于"123"的员工信息
CREATE INDEX idx_emp_age ON emp(age); -- 创建表字段age的索引 CREATE INDEX idx_emp_name ON emp(name); -- 创建表字段name的索引 CALL proc_drop_index('mydb','emp'); -- 删除表索引 #1.1 查找姓名以"abc"开头的员工信息 EXPLAIN SELECT * FROM emp WHERE emp.`name` LIKE 'abc%'; -- 优化前 0.030s 优化后 0.000s EXPLAIN SELECT * FROM emp WHERE emp.`name` LIKE '%abc'; -- 优化前 0.028s 优化后 0.031s EXPLAIN SELECT * FROM emp WHERE LEFT(emp.`name`,3)='abc'; -- 优化前 0.026s 优化后 0.029s #1.2 查找姓名含有"abc"的员工信息 EXPLAIN SELECT * FROM emp WHERE emp.`name` LIKE '%abc%'; -- 优化前 0.030s 优化后 0.029s #1.3 查找年龄不等于25的员工 EXPLAIN SELECT * FROM emp WHERE emp.age !=25 ; -- 优化前0.027s 优化后 0.031s #1.4 查找姓名不为空的员工信息 EXPLAIN SELECT * FROM emp WHERE emp.`name` IS NOT NULL; -- 优化前0.031s 优化后 0.030s EXPLAIN SELECT * FROM emp WHERE emp.`name` IS NULL; -- 优化前0.028s 优化后 0.010s #1.5 查找姓名等于"123"的员工信息 EXPLAIN SELECT * FROM emp WHERE emp.`name` = "123"; -- 优化前0.030s 优化后 0.010s EXPLAIN SELECT * FROM emp WHERE emp.`name` = 123; -- 优化前0.030s 优化后 0.030s结论：一一验证了优化原则的结论
10.2 组合索引原则
全值匹配我最爱
符合最左原则：不跳过索引中的列。
如果where条件中是OR关系，加索引不起作用
存储引擎不能使用索引中范围条件右边的列
首先删除之前创建的索引：
CALL proc_drop_index("mydb","emp");
CREATE INDEX idx_age_name_deptId ON emp(age,name,deptId); -- 等值查询 EXPLAIN SELECT * FROM emp WHERE emp.age=25 AND emp.`name`='abc' AND emp.deptId=1001 ; -- 全有效 -- 最左匹配原则 EXPLAIN SELECT * FROM emp WHERE emp.age=25; -- 全有效 EXPLAIN SELECT * FROM emp WHERE emp.age=25 AND emp.`name`='abc'; -- 全有效 EXPLAIN SELECT * FROM emp WHERE emp.age=25 AND emp.`name`='abc' AND emp.deptId=1001 ; -- 全有效 EXPLAIN SELECT * FROM emp WHERE emp.`name`='abc' AND emp.deptId=1001 ; -- 全无效 EXPLAIN SELECT * FROM emp WHERE emp.deptId=1001 ; -- 全无效 -- 使用or 连接 EXPLAIN SELECT * FROM emp WHERE emp.age=25 OR emp.`name`='abc' OR emp.deptId=1001 ; -- 全无效 EXPLAIN SELECT * FROM emp WHERE emp.age=25 AND emp.`name`='abc' OR emp.deptId=1001 ; -- 全无效 EXPLAIN SELECT * FROM emp WHERE emp.age=25 OR emp.`name`='abc' AND emp.deptId=1001 ; -- 全无效 -- 范围条件右边的列 EXPLAIN SELECT * FROM emp WHERE emp.age=25 AND emp.`name`LIKE '%abc' AND emp.deptId=1001 ; -- age有效 EXPLAIN SELECT * FROM emp WHERE emp.age=25 AND emp.`name`LIKE 'abc%' AND emp.deptId=1001 ; -- 全有效
小结：
一般性建议：
对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
书写sql语句时，尽量避免造成索引失效的情况
面试题：
假设index(a,b,c) 重要
Where语句 索引是否被使用 where a = 3 Y,使用到a where a = 3 and b = 5 Y,使用到a，b where a = 3 and b = 5 and c = 4 Y,使用到a,b,c where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 N where a = 3 and c = 5 使用到a， 但是c不可以，b中间断了 where a = 3 and b > 4 and c = 5 使用到a和b， c不能用在范围之后，b断了 where a is null and b is not null is null 支持索引 is not null 类似范围查询，ab能使用，b右边的会失效 where a <> 3 不能使用索引 where abs(a) =3 不能使用 索引 where a = 3 and b like 'kk%' and c = 4 Y,使用到a,b,c where a = 3 and b like '%kk' and c = 4 Y,只用到a where a = 3 and b like '%kk%' and c = 4 Y,只用到a where a = 3 and b like 'k%kk%' and c = 4 Y,使用到a,b,c
10.3 关联查询优化
① 接下来再次创建两张表，并分别导入20条数据：
CREATE TABLE IF NOT EXISTS `class` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE IF NOT EXISTS `book` ( `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`bookid`) ); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
② explain分析一下sql：
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card; EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; -- 优化前 先全查class，20行，后查book，16行 0.027s -- 优化后 （给book表添加card索引，删除class索引） 先全查class，20行，后查book，1行 0.007s 用到索引，效率提高 -- 优化后（给class表添加card索引，删除book索引） 先全查驱动表class，20行，后查被驱动表book，16行 0.028s 用到索引，但是索引并没有起作用（用不用都需要全查class表） -- 优化后（给两张表都添加索引） 先全查驱动表class，20行，后查被驱动表book，1行 0.004s 用到索引，但是class索引并没有起作用（用不用都需要全查class表） EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card; -- 优化前 先全查驱动表book，16行，后查被驱动表class，20行 0.028s -- 优化后（给book表添加card索引，删除class索引） 先全查驱动表book，16行，后查被驱动表class，20行 0.028s 用到索引，但是索引并没有起作用（用不用都需要全查book表） -- 优化后 （给class表添加card索引，删除book索引） 先全查book，16行，后查class，1行 0.007s 用到索引，效率提高 -- 优化后（给两张表都添加索引） 先全查驱动表book，16行，后查被驱动表class，1行 0.007s 用到索引，但是book表索引并没有起作用（用不用都需要全查book表） EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card; -- 优化前 先全查book，16行，后全查class，20行 0.031s -- 优化后（给book表添加card索引，删除class索引） 先全查class，20行，后查book，1行 0.004s 用到索引，效率提高 -- 优化后（给class表添加card索引，删除book索引） 先全查book，16行，后查class，1行 0.004s 用到索引，效率提高，内连接时mysql会根据最优方案执行效率最快的那套方案（将小结果集的表作为驱动表，大表作为被驱动表加上索引），所以两张表查询先后顺序也是根据方案在自动变化 -- 优化后（给两张表都添加索引） 先全查book，16行，后查class，1行 0.004s 用到索引，但是book表索引并没有起作用（用不用都需要全查book表） ALTER TABLE `book` ADD INDEX idx_card ( `card`); ALTER TABLE `class` ADD INDEX idx_card ( `card`); CALL proc_drop_index("mydb","book" ); CALL proc_drop_index("mydb","class" );SQL优化方案建议：
保证被驱动表的join字段已经创建了索引
left/right join 时，选择小表作为驱动表，大表作为被驱动表。
inner join 时，mysql会自己帮你把小结果集的表选为驱动表,对被驱动表连接字段创建索引。(5.6已经优化掉了，5.5需要手动编写)
子查询尽量不要放在被驱动表，有可能使用不到索引。
能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
10.4 子查询优化建议
① 尽量不要使用not in 或者 not exists
② 尽量不要使用子查询（子查询会先执行,将结果存入临时表中。主查询在使用这个临时表时,无法利用索引。）
10.5 排序优化
以下三种情况不走索引：
无过滤，不索引
顺序错，不索引
方向反，不索引
-- 排序优化 -- (主要观察Extra中有没有using filesort，key_len不作为排序优化的主要观察和判断对象) CALL proc_drop_index("mydb", "emp"); create index idx_age_deptid_name on emp(age,deptid,name); -- 能否用到索引 explain select * from emp order by age,deptid; -- 否 EXTRACT(Using filesort) -- 过滤有两种方式 -- 第一种使用limit,当limit截取的值较少时,优化器会做出判断会走二级索引查询到主键值然后回表通过主键值查询行数据，这种效率相对较高,但是一旦limit截取的数据过多时，优化器会认为回表查询太影响性能,就不会走索引直接去表中读截取到的数据 -- 第二种使用where 加过滤条件查询，这时需要注意查询方式，比如最左原则，不能起始使用范围查询等会使索引失效的查询条件 explain select * from emp order by age,deptid limit 10 -- 可以 # 无过滤 不索引 观察extra的值 explain select * from emp where age=45 order by deptid ; -- Extra NULL explain select * from emp where age=45 order by deptid,name; -- Extra NULL explain select * from emp where age=45 order by deptid,empno; -- empNO没有建立索引 Using filesort explain select * from emp where age=45 order by name,deptid; -- Using filesort 索引顺序反了（与复合索引建立顺序不同）也会出现 explain select * from emp where deptid=45 order by age; -- 不满足最左原则,索引失效 # 顺序错，不索引 explain select * from emp where age=45 order by deptid desc, name desc ; -- Extra Backward index scan表示 MySQL 使用了倒序索引扫描来执行查询。 -- 什么是倒序索引扫描 -- 正常情况下,MySQL 会从索引的首列开始匹配查询条件,这叫作正序索引扫描。 -- 而在某些情况下,MySQL 会从索引的末尾列开始匹配,这种扫描方式称为倒序索引扫描 # 方向反 不索引 explain select * from emp where age=45 order by deptid asc, name desc ; -- Using filesort 方向反 指的是几个排序字段升降序不同
10.6 了解filesort算法
如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序
双路排序
MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
也就是：从磁盘取排序字段，再buffer进行排序，再从磁盘取其他字段。
取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。
单路排序
从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
结论及引申出的问题：由于单路是后出的，总体而言好过双路。
但是用单路有问题：在sort_buffer中，比双路排序要多占用很多空间，因为单路排序是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。
本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。
10.7 优化策略
Order by时select * 是一个大忌，只Query需要的字段， 这点非常重要。在这里的影响是
当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。
两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。
尝试提高 sort_buffer_size：不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M之间调整
尝试提高 max_length_for_sort_data：提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。1024-8192之间调整
10.8 分组优化
group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。
group by 先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置
where高于having, 能写在where限定的条件就不要写在having中了只要对分组列创建索引即可
10.9 覆盖索引
覆盖索引(Covering Index)是一种特殊的索引,包含所有需要查询的数据字段,因此查询可以仅凭索引就可以返回结果,而不需要回表查询数据。举个简单例子:
sql CREATE TABLE products ( id INT NOT NULL PRIMARY KEY, name VARCHAR(50), price INT ); CREATE INDEX idx_name_price ON products (name, price);这里创建的 idx_name_price 索引包含 name 和 price 两个字段。如果执行以下查询:
sql SELECT name, price FROM products WHERE name = 'Apple';MySQL 可以直接从 idx_name_price 索引中获取 name 和 price 信息,而不需要访问表的数据行。这种仅需要使用索引就可以返回全部查询结果的索引,就是覆盖索引。
覆盖索引查询的主要优点有:
1. 避免回表查询,减少 I/O 次数,提高查询效率。
2. 避免锁定表的数据行,提高并发性能。
3. 利用索引本身的排序完成查询排序,避免额外排序操作。
4. 当索引中包含大字段时,可以避免访问大字段,提高查询效率。所以,在设计索引时,如果索引能够覆盖常见查询的所有字段,那么就可以通过覆盖索引来提高对应查询的性能,这是索引设计的重要考量因素之一。
但是,覆盖索引也有一定弊端:
1. 索引文件会更大,占用更多空间。
2. 索引更新性能会降低,因为要同时更新更多字段。
3. 当字段较多时,在同一个索引中全部包含会使得索引键值变长,查询性能会降低。
索引无效说明
创建索引后，用不用索引，最终是优化器说了算。
优化器会基于开销选择索引，怎么开销小就怎么来。不是基于规则，也不是基于语义。另外SQL语句是否使用索引，和数据库的版本、数据量、数据选择度（查询中选择的列数）运行环境都有关系。
所有创建索引后需要结合explain进行分析索引是否有效
11. SQL实战
11.1 阿里开发手册：
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区
分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名，索引长度)) / count(*) 的区分度来确定。-- 强制使用索引 CALL proc_drop_index("mydb","emp" ); EXPLAIN SELECT * FROM emp ORDER BY deptId,`name`; -- 无索引 2.287s CREATE INDEX idx_age_deptId_name on emp(age,deptId,NAME); EXPLAIN SELECT * FROM emp ORDER BY deptId,`name`; -- 1.294s 没有走索引 -- 强制使用索引 EXPLAIN SELECT * FROM emp FORCE INDEX (idx_age_deptId_name) ORDER BY deptId,`name`; -- 1.204S11.2 自定义长度创建索引
-- 自定义索引长度 -- 1.删除表索引 CALL proc_drop_index("mydb","dept"); -- 2.给dept表的adrress创建索引，先不指定长度 ALTER TABLE dept ADD INDEX idx_address(address); -- 从information表的tables中查询此全字段索引长度为22593536；没有必要创建全字段索引，应该根据实际文本区创建自定义长度索引 -- 删除索引 -- 自定义长度 ALTER TABLE dept ADD INDEX idx_address(address(5)); -- 选择字段前5个字符串作为索引自定义长度数创建的索引大小为540672 -- 通过自定义长度创建的索引存在问题，如果数值较小造成数据表的区分度较低，比如字符串都是以“abcdf”开头的，那么查询效率依然很低，那么就需要计算最佳长度 -- 计算区分度:需要创建索引的列指定长度截取字符串去重除以所有数据行数 SELECT COUNT(DISTINCT LEFT(address,5))/COUNT(1) FROM dept -- LEFT(address,3) 截取address字段的前三位字符 -- DISTINCT 去重 -- COUNT 统计截取再去重的数量 -- COUNT(1) 中长度 -- 最后不断改变截取长度，结果大于90%就是适合的最佳长度
11.3 实现并优化8个SQL
#删除两个表的所有索引
#1、列出自己的掌门比自己年龄小的人员
EXPLAIN SELECT * FROM t_emp c
INNER JOIN (SELECT a.id, a.deptName, b.age FROM t_dept a INNER JOIN t_emp b ON a.CEO=b.id) v ON c.deptId=v.id
WHERE c.age > v.age;
EXPLAIN SELECT SQL_NO_CACHE * FROM dept a
INNER JOIN t_emp b ON a.CEO=b.id
INNER JOIN t_emp c ON c.deptId=a.id
WHERE c.age > b.age;
create index idx_deptId on t_emp(deptId);
#2、列出所有年龄低于自己门派平均年龄的人员
EXPLAIN select SQL_NO_CACHE * from emp c
LEFT JOIN (select b.deptId,AVG(b.age) avgage from emp b GROUP BY b.deptId
) v on c.deptId=v.deptId
where c.age < v.avgage;
EXPLAIN select * from (select b.deptId, AVG(b.age) avgage from emp b GROUP BY b.deptId) v
LEFT JOIN emp c on c.deptId=v.deptId
where c.age < v.avgage;
CREATE INDEX idx_deptId on emp(deptId);
#3、列出至少有2个年龄大于40岁的成员的门派
explain select SQL_NO_CACHE a.deptId, b.deptName, count(*) cou
from t_emp a
INNER JOIN t_dept b on a.deptId=b.id
where a.age>40
GROUP BY a.deptId
HAVING cou >= 2;
# 使用数据量少的表的字段分组
explain select SQL_NO_CACHE b.id, b.deptName, count(*) cou
from t_dept b
STRAIGHT_JOIN t_emp a on a.deptId=b.id
where a.age>40
GROUP BY b.id
HAVING cou >= 2;
create INDEX idx_dept on t_emp(deptId, age);
#4、至少有2位非掌门人成员的门派
EXPLAIN select SQL_NO_CACHE a.deptId,b.deptName,count(*) cou
from t_emp a
INNER JOIN t_dept b on a.deptId=b.id
where a.id!=b.CEO
GROUP BY a.deptId
HAVING cou >= 2;
explain select a.deptId,c.deptName,count(*) cou from t_emp a LEFT JOIN t_dept b on a.id=b.ceo INNER JOIN t_dept c on a.deptId=c.id
where b.ceo is null
GROUP BY a.deptId
HAVING cou>=2
CREATE INDEX idx_deptId on t_emp(deptId);
CREATE INDEX idx_ceo on t_dept(ceo);
#5、列出全部人员，并增加一列备注“是否为掌门”，如果是掌门人显示是，不是掌门人显示否
select a.id, a.`name`, CASE WHEN b.CEO IS NULL THEN '是' ELSE '否' END '是否掌门人'
from t_emp a LEFT JOIN t_dept b on a.id=b.CEO;
#6、列出全部门派，并增加一列备注“老鸟or菜鸟”，若门派的平均值年龄>50显示“老鸟”，否则显示“菜鸟”
select a.id,a.deptName,AVG(b.age),IF(AVG(b.age)>50, '老鸟', '菜鸟') '老鸟or菜鸟'
from t_dept a INNER JOIN t_emp b on a.id=b.deptId GROUP BY a.id;
#7、显示每个门派年龄最大的人
CREATE INDEX idx_deptId on t_emp(deptId);
CREATE INDEX idx_age on t_emp(age);
#8、显示每个门派年龄第二大的人·
SET @last_deptid=0;
SELECT a.id,a.deptid,a.name,a.age,a.rk
FROM(
SELECT t.*,
IF(@last_deptid=deptid,@rank:=@rank+1,@rank:=1) AS rk,
@last_deptid:=deptid AS last_deptid
FROM t_emp t
ORDER BY deptid,age DESC
)a WHERE a.rk=2;
UPDATE t_emp SET age=100 WHERE id = 2
SET @rank=0;
SET @last_deptid=0;
SET @last_age=0;
SELECT t.*,
IF(@last_deptid=deptid, IF(@last_age = age, @rank, @rank:=@rank+1),@rank:=1) AS rk,
@last_deptid:=deptid AS last_deptid,
@last_age :=age AS last_age
FROM t_emp t
ORDER BY deptid,age DESC
CALL proc_drop_index('mydb', 't_emp');
CALL proc_drop_index('mydb', 't_dept');
CALL proc_drop_index('mydb', 'emp');
CALL proc_drop_index('mydb', 'dept');11.4 时间日期处理
11.5 行转列
测试表： CREATE TABLE t_score( id INT(11) NOT NULL auto_increment, stuid VARCHAR(20) NOT NULL COMMENT 'id', subject VARCHAR(20) COMMENT '科目', score DOUBLE COMMENT '成绩', PRIMARY KEY(id) ) 测试数据： INSERT INTO t_score(stuid,subject,score) VALUES ('001','Java基础',90); INSERT INTO t_score(stuid,subject,score) VALUES ('001','mysql',92); INSERT INTO t_score(stuid,subject,score) VALUES ('001','Javaweb',80); INSERT INTO t_score(stuid,subject,score) VALUES ('002','Java基础',88); INSERT INTO t_score(stuid,subject,score) VALUES ('002','mysql',90); INSERT INTO t_score(stuid,subject,score) VALUES ('002','Javaweb',75.5); INSERT INTO t_score(stuid,subject,score) VALUES ('002','ssm',100); INSERT INTO t_score(stuid,subject,score) VALUES ('003','Java基础',70); INSERT INTO t_score(stuid,subject,score) VALUES ('003','mysql',85); INSERT INTO t_score(stuid,subject,score) VALUES ('003','Javaweb',90); INSERT INTO t_score(stuid,subject,score) VALUES ('003','ssm',82);
需求：行转列显示学生直观显示学生各科成绩
-- 1.查询表 SELECT * FROM t_score; -- 2.按照学生id分组 SELECT stuid FROM t_score GROUP BY stuid; -- 3.使用分组函数处理（使用MAX/MIN/SUM）都可以 SELECT stuid, SUM(IF(subject='java基础',score,0))'Java基础', SUM(IF(subject='mysql',score,0))'mysql', SUM(IF(subject='javaweb',score,0))'javaweb', SUM(IF(subject='ssm',score,0))'ssm' FROM t_score GROUP BY stuid;-- 使用分组函数，如果字段的值和java基础一样，就按照分组相加输出出结果，如果没有给个默认值0
11.6 删除重复行
插入重复数据 INSERT INTO t_score(stuid,SUBJECT,score) VALUES ('001','Java基础',5); INSERT INTO t_score(stuid,SUBJECT,score) VALUES ('001','mysql',90); INSERT INTO t_score(stuid,SUBJECT,score) VALUES ('001','Javaweb',1); INSERT INTO t_score(stuid,SUBJECT,score) VALUES ('002','Java基础',22); INSERT INTO t_score(stuid,SUBJECT,score) VALUES ('002','mysql',55); INSERT INTO t_score(stuid,SUBJECT,score) VALUES ('002','Javaweb',1.5); INSERT INTO t_score(stuid,SUBJECT,score) VALUES ('002','ssm',2);需求：每个学生同一学科有多个成绩的，保留分数高的
-- 上一行的学生id SET @lstuid:=0; -- 上一行的学科id SET @lsubject:=''; -- 记录行号 SET @rank:=1; DELETE FROM t_score WHERE id IN( SELECT id FROM( SELECT *,IF(stuid=@lstuid,IF(subject=@lsubject,@rank:=@rank+1,@rank:=1),@rank:=1)'rank', @lsubject:=subject,@lstuid:=stuid FROM t_score ORDER BY stuid,subject,score DESC)v WHERE v.rank !=1)
11.7 窗口函数
窗口函数和普通聚合函数很容易混淆，二者区别如下：
Ø 聚合函数是将多条记录聚合为一条
Ø 窗口函数是每条记录都会执行，有几条记录执行完还是几条
按照功能划分，可以把MySQL支持的窗口函数分为如下几类:
# 序号函数：没有参数
row_number()/rank()/dense_rank()
# 分布函数：没有参数
percent_rank()：所在行数/总行数的百分比
cume_dist()：累积分布值
# 前后函数:参数有3个(expr：列名；n：偏移量；default_value：超出记录窗口的默认值)
lag(): 从当前行开始往前获取第N行，缺失则使用默认值
lead():从当前行开始往后获取第N行，缺失则使用默认值
# 头尾函数： 参数1个(expr：列名)
first_value():返回分组内截止当前行的第一个值
last_value():返回分组内截止当前行的最后一个值
# 其他函数:
-- 参数有2个(expr：列名；n：偏移量)
nth_value():返回分组内截止当前行的第N行
-- 参数有1个(expr：列名；)
ntile():返回当前行在分组内的分桶号
/*
语法结构：
window_function ( expr ) OVER (
PARTITION BY ...
ORDER BY ...
)其中，window_function 是窗口函数的名称；
expr 是参数，有些函数不需要参数；OVER子句包含三个选项：
1、分区（PARTITION BY）
PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算
2、排序（ORDER BY）
OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似OVER后面括号中的内容可以抽取：
WINDOW w AS (
PARTITION BY ...
ORDER BY ...
)
*/
测试窗口函数的使用：
-- 1、查询员工信息和他部门年龄升序排列前一名员工的年龄 SELECT * , lead(age , 1,-1) over( PARTITION BY deptId ) last_emp_age FROM t_emp; -- 2、查询每个员工在自己部门由大到小的年龄排名 select * , row_number() over(PARTITION BY deptid ORDER BY age DESC) as row_num, from t_emp; # 或者 SELECT * , row_number() over w AS row_num # w代表使用的 FROM t_emp WINDOW w AS(PARTITION BY deptid ORDER BY age DESC); -- 查询每个员工所在部门的其他员工 如果年龄大于等于自己的小于等于两个，则保留自己的数据 -- 方式一 SELECT * FROM t_emp t1 WHERE (SELECT COUNT(1) FROM t_emp t2 WHERE t2.`deptId`=t1.`deptId` AND t2.age>=t1.`age`)<=2 ORDER BY t1.`deptId` DESC, t1.age DESC; --方式二 select * from( select row_number() over(partition by deptid order by age desc) as row_num, id,name,sal,deptid from t_emp ) t where row_num <= 2
12. View视图
视图是将一段查询sql封装为一个虚拟的表。 这个虚拟表只保存了sql逻辑，不会保存任何查询结果。
主要作用:
封装复杂sql语句，提高复用性
逻辑放在数据库上面，更新不需要发布程序，面对频繁的需求变更更灵活
常用场景:
共用查询结果
报表
-- 创建 CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition -- 使用 #查询 select * from view_name #更新 CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition #删除 DROP VIEW view_name;
13. MySQL锁
13.1 介绍
锁是协调多个进程或线程并发访问某一资源的机制。数据库中的数据是需要用户共享的资源。我们需要保证数据并发访问的一致性，合理使用mysql锁可以解决此问题。
mysql支持读锁和写锁(都属于悲观锁)
读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分，分为表锁和行锁
13.2 表锁
每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
一般用在整表数据迁移的场景。
-- 加锁b lock table 表1 read(w*-+ rite),表2 read(write); -- 查看表锁 show open tables; -- 删除本次session的表锁 unlock tables;案例：一个session添加A表的写锁，另一个session添加B表的写锁，第一个session测试A、B表数据读取
13.3 行锁
每次操作锁住一行数据，锁定粒度最小，发生锁冲突的概率最低，并发能力强。但是开销大、加锁慢、可能会出现死锁。InnoDB存储引擎在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
-- 查看行锁： show status like 'innodb_row_lock%'; -- 对各个状态量的说明如下 Innodb_row_lock_current_waits: 当前正在等待锁定的数量 Innodb_row_lock_time: 从系统启动到现在锁定总时间长度 Innodb_row_lock_time_avg: 每次等待所花平均时间(*) Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间Innodb_row_lock_waits:系统启动后到现在总共等待的次数(*) Innodb_row_lock_waits: 等待总次数(*) -- 测试行锁 Session_1执行：select * from account where id=1 for update; Session_2执行：select * from account where id=2 for update; Session_1执行：select * from account where id=2 for update; Session_2执行：select * from account where id=1 for update; -- 查看近期死锁日志信息： show engine innodb status\G;13.4 总结
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
尽可能低级别事务隔离
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
