MySQL进阶

一、架构介绍篇

1. Mysql逻辑架构介绍

1. 连接层

2. 服务层

3. 引擎层

4. 存储层

2. Mysql存储引擎

查看命令

# 查看你的mysql现在提供的存储引擎
show engines;

# 查看你的mysql当前默认的存储引擎
show varibales like '%storage_engine%';

主流存储引擎区别

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	支持	不支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作
表空间	小	大
关注点	性能	事务

二、索引优化分析

1. 性能下降的原因

应该关注：

1. 查询语句是否合理
2. 索引失效问题
3. 关联太多join
4. 服务器调优和各个参数的设置（缓冲、线程等）

2.常见通用的join查询

机器理解的执行顺序

七种join理论

3.索引

什么是索引

帮助MySQL排好序的快速查找的数据结构。

以文件形式存储，InnoDB默认使用B+树数据结构实现。

优势

1. 提高检索效率，减少io
2. 通过索引对数据进行排序，减少cpu消耗

劣势

1. 占用空间
2. 使得增删改操作的速度降低，由于需要重建索引。

1.索引使用

分类

• 普通索引
• 唯一索引

语法

# 创建索引
CREATE INDEX [UNIQUE]indexName ON table_name (column_name(length));
ALTER table tableName ADD INDEX indexName(columnName);

# 删除
DROP INDEX [indexName] ON mytable;

# 查看
SHOW INDEX FROM table_name;

2.BTree

初始化介绍

一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示),如磁盘块1包含数据项17和35，包含指针P1、P2、P3,
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

查找过程

如果要查找数据项29，

• 那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO)可以忽略不计，
• 通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次I0，29在26和30之间，锁定磁盘块3的P2指针，
• 通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，

结束查询，总计三次IO。

真实的情况是，3层的b+树页以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次I0，那么总共需要百万次的IO，显然成本非常非常高。

3.哪些情况需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 查询中排序的字段，排序字段若通过索引访问将大大提高排序速度
5. 查询中统计或分组字段

4.那些情况不要建索引

1. 表记录太少
2. 经常增删改的表
3. 字段的数据内容比较重复。

4. 性能分析

MySQL Query Optimizer

MySQL中有专门负责优化Select语句的优化器模块

MySQL常见瓶颈

• cpu：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态

1.Explain

是什么？

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

怎么用？

Explain + SQL语句

执行计划包含的信息

能干嘛？

• 表的读取顺序
• 数据读取操作的操作类型哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

2.执行计划各个字段介绍

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况：

• id相同，执行顺序由上至下
• id不同，如果是子查询，序号会递增，id值越大优先级越高，越先被执行
• id相同不同，综上

select_type

查询的类型，主要是用于区别普通查询、联合查询、子查询等复杂查询

• SIMPLE 简单的 select 查询，查询中不包含子查询或者UNION

• PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为

• SUBQUERY 在SELECT或WHERE列表中包含了子查询

• DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。

  EXPLAIN SELECT * FROM (SELECT * FROM city WHERE population > 1*1000) AS big_city WHERE big_city.country='Germany' ;
  +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
  | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
  +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
  |  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 4068 | Using where |
  |  2 | DERIVED     | City       | ALL  | Population    | NULL | NULL    | NULL | 4079 | Using where |
  +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
  

• UNION 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将标记为：DERIVED

• UNION RESULT 从UNION表获取结果的SELECT

type

表示MySQL在表中找到所需行的方式

性能排序

system>const>equ_ref>ref>range>index>ALL

至少要优化到range，最好是ref

• system：表里只有银行记录，这是const类型的特例，平时不会出现，可以忽略
• const：表示通过索引一次就找到了，常出现在比较primary key或者unique索引。因为只匹配一行数据，所以很快将主键置于where列表中，MySQL就能将该查询转换为一个常量
• eq_ref：用于联表查询的情况，按联表的主键或唯一键联合查询。
• ref：非唯一性索引扫描，返回匹配某个单独值的所有行
• range：索引范围扫描，常见于<、<=、>、>=、between等操作符
• index：索引，MySQL遍历
• all：全表扫描，MySQL遍历全表来找到匹配的行

possible_keys

MySQL推测用到的索引

key

实际用到的索引

key_len

使用索引字段的长度。并非定义的长度，而是使用的长度。在不损失精确性的情况下，长度越短越好。

ref

索引所参照的列，可以是const。

rows

根据表统计信息及索引选用情况，大致估算出找到所需读取的行数。

Extra

包含不适合在其他列中显示但十分重要的额外信息。

• Useing filesort：无法利用索引完成的排序操作称为“文件排序”（性能较差）

• Using temporary：新建内部临时表。常见于排序 order by 和分组查询 group by。（性能相当差）

• Using index：覆盖索引。（性能最好）。当select的数据列只用从索引中就能取得，不必根据索引再次读取数据文件。

  # 出现using where的情况
  explain select col1,col2 from t1
  

• Using where：使用了where条件查询

• Using join buffer：使用了连接缓存

• Impossible WHERE：不可能的条件

  explain select * from t where name = 'zhangsan' and name = 'lisi'
  

• select tables optimized away：没有GROUPBY 子句的情况下，基于索引优化MIN/MAX操作

• distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

5. 避免索引失效

建表

CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
pos VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
)CHARSET utf8 COMMENT '员工记录表';

INSERT INTO staffs VALUES(1,'Z3',22,'manager',NOW()),(2,'july',23,'dev',NOW()),(3,'lisi',23,'dev',NOW());

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name,age,pos);

show index from staffs;

案例

1. 全值匹配我最爱

2. 最佳左前缀法则

   ：带头大哥不能死，中间兄弟不能断。

   EXPLAIN SELECT * FROM staffs WHERE name = 'july';
   EXPLAIN SELECT * FROM staffs WHERE name = 'july' AND age = 22;
   EXPLAIN SELECT * FROM staffs WHERE name = 'july' AND age = 22 AND pos = 'dev';
   # 失效 type变成ALL，ref变成Null
   EXPLAIN SELECT * FROM staffs WHERE age = 22 AND pos = 'dev';
   # 失效 type变成ALL，ref变成Null
   EXPLAIN SELECT * FROMFROM staffs WHERE pos = 'dev';
   # 失效 type变成ALL，ref变成Null
   EXPLAIN SELECT * FROM staffs WHERE age = 22;
   

   最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且 不能跳过索引中的列

3. 不在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效而转向全表扫描

4. 存储引擎不能使用索引中范围条件右边的列

   ：范围之后失效

   # 失效
   EXPLAIN SELECT * from staffs WHERE name = 'july' AND age > 22 AND pos = 'dev';
   # 可以通过覆盖索引生效
   

5. 尽量使索引列和查询列保持一致，少select *

6. mysql在使用不等于（!=或<>）的时候无法使用索引会导致全表扫描。

   猜想是mysql的优化机制，这就相当于全查了，所以不走索引。

7. is null, is not null 也无法使用索引（存疑）。测试的过程中发现，is null走了ref，而is not null 走了 range

8. like 以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作

   ：like百分加右边

   解决不被使用的方法？

   # 选择列覆盖索引
   EXPLAIN SELECT name,age,pos FROM staffs WHERE name LIKE '%july%';
   # 失效
   EXPLAIN SELECT * FROM staffs WHERE name LIKE '%july%';
   # 失效
   EXPLAIN SELECT name,age,pos,email FROM staffs WHERE name LIKE '%july%';
   

9. 字符串不加’'，索引失效

   # 失效
   SELECT * FROM staffs WHERE name = 'july' AND age=22
   

10. 少用or，用它来连接时索引会失效

    # 失效
    explain SELECT * FROM staffs WHERE name = 'july' or age= 23 or pos = 'dev'
    # 通过覆盖索引可以使其有效，保证or的都是被索引的
    explain SELECT name,pos FROM staffs WHERE name = 'july' or age= 23 or pos = 'dev'
    

总结：

可以大致思考一下，复合索引如何带入btree中进行查找。接下来的内容就会变得很容易理解。

同时结合mysql对走索引和不走没区别的情况会选择不走索引。

当发生失效时，可以考虑选择条件进行覆盖索引。

6. 一般性建议

• 对于单键索引，尽量选择针对当前查询过滤性更好的索引
• 对于选择组合索引的适合，当前查询中过滤性组好的字段在索引顺序中，越左越好
• 在选择组合索引的时候，尽量选择能够包含当前查询中的where子句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

7. 口诀

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

三、查询截取分析

优化思路

1. 慢查询的开启并捕获
2. explain + 慢SQL分析
3. show profile查询SQL在Mysql服务器里面的执行细节和生命周期
4. SQL数据库服务器的参数调优

1. 永远以小表驱动大表

小表驱动大表的主要目的是通过减少表连接创建的次数,加快查询速度.

// 连接5次，每次连接执行1000次操作
for(int i = 5 ...){
	for(int j=1000 ...){
		
	}
}
// 连接1000次，每次连接执行5次操作
for(int i =1000 ...){
	for(int j=5 ...){
	
	}
}

优化原则：小表驱动大表，即小的数据集驱动大的数据集。

select * from A where id in (select id from B)
等价于
for select id from B
for select * from A where A.id = B.id

当B表的数据集必须小于A表的数据集时，用in优于exists

select * from A where exists (select 1 from B where B.id = A.id)

当A表的数据集系小于B表的数据集时，用exists优于in

2.为排序使用索引

MySql两种排序方式︰

• 文件排序或扫描有序索引排序

• MySql能为排序与查询使用相同的索引

对于索引 KEY a_b_c (a, b, c)

order by 能使用索引最左前缀
-ORDER BY a
-ORDER BY a,b
-ORDER BY a,b,c
-ORDER BY a DESC,b DESC,C DESC

# 如果WHERE使用索引的最左前缀定义为常量，则order by能使用索引
- WHERE a = const ORDER BY b, c
- WHERE a = const AND b = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c

# 不能使用索引进行排序
-ORDER BY a ASC, b DESC, c DESC/*排序不一致*/
- WHERE g = const ORDER BY b,c/*丢失a索引*/
- WHERE a = const ORDER BY c/*丢失b索引*/
- WHERE a = const ORDER BY a,d/*d不是索引的一部分*/
- WHERE a in (..)ORDER BY b, c/*对于排序来说,多个相等条件也是范围查询*/

3. 慢查询日志

使用SET GLOBAL slow_query_log = 1 开启

• 只对当前数据库生效，重启失效
• 永久生效可以修改配置文件

使用SHOW VARIABLES LIKE 'long_query_time%'查看时间阈值

使用SET GLOBAL long_query_time = 3设置时间阈值为3秒

mysqldumpslow工具

自带的。用来分析慢查询日志的工具。

常见参数

得到返回记录集最多的10个SQL
mysqldumpslow -s r-t 10 /var/lib/mysql/atguigu-slow.log

得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t-t 10 -g "left join"/var/lib/mysql/atguigu-slow.log

另外建议在使用这些命令时结合│和more 使用，否则有可能出现爆屏情况mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

4.批量插入脚本

# 新建库
create database bigData;
use bigData;

# 1 建表dept
CREATE TABLE dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT '',
loc VARCHAR(13)NOT NULL DEFAULT ''
)ENGINE=INNODB DEFAULT CHARSET=GBK;

# 2 建表emp
CREATE TABLE emp(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*编号*/
ename VARCHAR(20)NOT NULL DEFAULT'',/*名字*/
job VARCHAR(9) NOT NULL DEFAULT'',/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)NOT NULL,/*薪水*/
comm DECIMAL(7,2)NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0/*部门编号*/
)ENGINE=INNODB DEFAULT CHARSET=GBK;

# 如果创建函数失败
set global log_bin_trust_function_creators=1;

# 3 创建随机字符串生成函数
DELIMITER $$
CREATE FUNCTION rand_string(n INT)RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT'abcdefghjikmnopqrstuwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
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 $$

# 4 创建插入dept的存储过程
CREATE PROCEDURE `insert_dept`(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0 ;
SET autocommit = 0;
REPEAT
SET i = i +1;
INSERT INTO dept (deptno , dname,loc ) VALUES ((START+i) ,rand_string(10) , rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;

# 调用（相当慢，主要用于了解函数和过程，可以尝试用navicat的自动生成）
call insert_dept(100,500000)

5.Show Profile

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。用于对SQL调优的测量。

使用命令

• 展示当前会话最近15次查询show profiles;

• 诊断SQL：show profile cpu,block io for query 【上一步的query_id】

诊断的常用参数

type:
--ALL --显示所有的开销信息
--BLOCK IO --显示块lO相关开销
--CONTEXT SWITCHES --上下文切换相关开销
--CPU --显示CPU相关开销信息
--IPC --显示发送和接收相关开销信息
--MEMORY --显示内存相关开销信息
--PAGE FAULTS--显示页面错误相关开销信息
--SOURCE --显示和Source_function，Source_file，Source_line相关的开销信息
--SWAPS --显示交换次数相关开销的信息

诊断结论

6.全局查询日志

命令

• set global general_log = 1;
• set global log_output = ‘TABLE’;
• select * from mysql.general_log;

四、MySql锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。

需要在事务中进行。

锁的分类

• 从对数据操作的类型（读\写）分
  • 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会相互影响。
  • 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
• 从对数据操作的力度分
  • 表锁（偏读）：开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 行锁（偏写）：有死锁；锁粒度小，锁冲突概率低，并发高。

InnoDB支持表锁和行锁，MyISAM只支持表锁

1. 表锁

表锁有两种模式

• 共享读锁
• 独占写锁

读阻塞写，写阻塞读写

命令

• 加锁 lock table t1 read,t2 write;
• 查看 show open tables
• 解锁 unlock tables

2. 行锁

粒度变成某一行

读阻塞写，写阻塞读写

如何加锁

• 对于常见DML语句，InnoDB 会自动给相应的记录加写锁

• 对于普通的 select 语句，不会加任何锁（可序列化的隔离级别下会加行级读锁）

  SELECT * FROM table_name WHERE ... FOR UPDATE，加行级写锁

  SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE，加行级读锁

3.索引失效行锁变表锁

InnoDB 存储引擎的行级锁是基于索引的（这个下篇文章会详细解释），也就是说当索引失效或者说根本没有用索引的时候，行锁就会升级成表锁。

# a字段是字符类型且加了索引，不带引号的5引起了索引失效，最终会导致表锁
begin;
update t1 set a = 5;

4. 间隙锁

什么是间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP),InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁）。

危害
因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

5.分析行锁定

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺的情况

show status like 'innodb_row_lock%'

6.ACID、分布式事务带来的问题、事务隔离级别

ACID

• 原子性（Atomicity）：一系列的操作整体不可拆分，要么同时成功，要么同时失败
  • 中止出错的事务，并撤销该事务进行的所有变更
• 一致性（Consistency）：数据在事务的前后，数据保持一致
  • 账户A转钱到账户B，那么账户A中减少的钱与账户B中增加的钱必须相等
• 隔离性（Isolation）：事务之间相互隔离
• 持久性（Durability）：一旦事务成功，数据存入数据库

分布式事务带来的问题

---

更新丢失

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题– 最后的更新覆盖了由其他事务所做的更新 。

脏读

脏读就是一个事务修改了一条数据的值，结果还没提交呢，另外一个事务就读到了你修改的值，然后你回滚了，人家事务再次读，就读不到了，即人家事务读到了你修改之后还没提交的值，这就是脏读。

不可重复读

是指在数据库访问中，一个 事务 范围内两个相同的查询却返回了不同数据

幻读

指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的幻行。

事务隔离级别

---

事务隔离会有性能损失，所以数据库往往提供多种隔离级别来防止一部分而不是全部的并发问题，由程序员来进行权衡。
Read Uncommitted（读未提交）：
A事务查询user，B事务修改还未提交，A事务再次查询user，发现改变。
该隔离级别的事务会读到其它未提交事务的数据，此现象也称之为脏读。

Read Committed（读已提交）：
A事务查询user，B事务修改并提交，A事务再次查询user，发现改变。此称之为幻读。
一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性。Oracle 和 SQL Server 的默认隔离级别。
Repeatable Read（可重复读）
A事务查询user，B事务修改并提交，A事务再次查询user，发现未改变。
有可能另一个事务已经插入了数据，而可重复读的情况下，当前事务是查询不到新插入的数据的，就会出现插入重复的情况。mysql默认级别。
Serializable（序列化）
A事务查询user，B事务修改不了，因为被加了锁。此时A提交，B事务修改成功。
在该隔离级别下事务都是串行顺序执行的，MySQL 数据库的 InnoDB 引擎会给读操作隐式
加一把行级读共享锁，从而避免了脏读、不可重读复读和幻读问题。

7.优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁.
• 合理设计索引，尽量缩小锁的范围尽可能较少检索条件,合理设计索引，尽量缩小锁的范围
• 尽可能较少检索条件，避免间隙锁避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别事务隔离尽可能低级别事务隔离

五、主从复制

复制过程

1 master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件，binary log events;

2 slave将master的binary log events拷贝到它的中继日志(relay log） ;
3 slave重做中继日志中的事件，将改变应用到自己的数据库中。Mysql复制是异步的且串行化的

六、案例

1.索引单表优化案例

# 建表
CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);

# 添加数据
INSERT INTO `article`(`author_id`,`category_id`,`views`,`comments`,`title`,`content`) VALUES (1,1,1,1,'1','1'),(2,2,2,2,'2','2'),(3,3,3,3,'3','3');

# 可以利用navicat的生成数据功能随机生成百万条数据

需求：查询category_id为1 且 comments 大于1的情况下,views 最多的article_id

第一步

​ 不加任何索引
​ SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

​ 两百万条数据下

​ 使用explain分析，毫无疑问，差到底了。

第二步

​ 尝试对查询列添加索引(category_id,comments,views)。建立索引的时间和数据量成正比。有两个添加语句，二选一即可。

​ CREATE INDEX idx_article_ccv ON article(category_id,comments,views);

​ 直接执行刚才的语句，发现快了N倍

​ 使用explain分析。

1. 通过category_id覆盖索引将数据锁定在1.9w条左右。

2. 到了comments由于是范围，导致剩余的索引失效，所以无法利用索引完成的排序操作，使用了文件内排序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jCVaX8eQ-1677925802227)(C:\Users\ASUS\AppData\Roaming\Typora\typora-user-images\image-20230301223919712.png)]

最理想的情况。[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cns7X0ET-1677925802227)(C:\Users\ASUS\AppData\Roaming\Typora\typora-user-images\image-20230301224748447.png)]

SELECT id,author_id FROM article WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;

第三步

​ 尝试不对范围查找的字段添加索引。

​ 先删除，再添加

DROP INDEX idx_article_ccv ON article;
CREATE INDEX idx_article_cv ON article(category_id,views);

​ 再次执行，发现[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WR9pUxhA-1677925802228)(C:\Users\ASUS\AppData\Roaming\Typora\typora-user-images\image-20230301225728071.png)]，真快！

​ 使用explain分析，发现和第二步中的理想情况一模一样。检索加排序同时用到。

2.索引两表优化案例

# 给案例1添加一个author表
CREATE TABLE IF NOT EXISTS `author`(
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `author_name` varchar(255),
    PRIMARY KEY(`id`)
);
# 继续使用navicat的随机生成数据功能，10w条

需求：查询文章及对应的作者

第一步

​ 执行EXPLAIN SELECT article.title title,author.author_name author FROM article LEFT JOIN author ON article.author_id = author.id;

​ 直接查询也是相当的慢

第二步

​ 由于author表的id是主键，所以无需再添加索引，从上面的分析图也可以看出来，第二张表的效率是非常高的，所以我们尝试对article的author_id字段添加索引。执行查询和分析。发现使用的仍然是ALL。可以得出结论：左连接反着加。mysql对走索引和没走没有区别的查询就会选择不走，可以强制让它使用，但是意义不大。也可以通过把表对调。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Boq02yp2-1677925802228)(C:\Users\ASUS\AppData\Roaming\Typora\typora-user-images\image-20230302111029840.png)]

第三步

​ 难道说在上步操作中主表加的索引就没有意义了吗？并不是的，当我们需求变成：查询指定作者的所有文章。

explain SELECT article.title title,author.author_name author FROM article LEFT JOIN author ON article.author_id = author.id where article.author_id = 10

就会发现查询依旧是那么潇洒

3.索引三表优化案例

# 建立分类表
CREATE TABLE IF NOT EXISTS `category`(
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `category_name` varchar(255) NOT NULL,
    PRIMARY KEY(`id`)
);

思路和2步是一样的。这里有一个特殊的地方需要明确：参与左连接的表，都是主键，如果不是主键，会出现最差现象，连接多了就会发现join buffer的问题，可以适当调大joinBuffer。需要记住一点：“永远用小结果驱动大结果”。

4.索引面试题分析

# 建表
CREATE TABLE test(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
insert into test(c1,c2,c3,c4,c5) value('a1','a2','a3','a4','a5');
insert into test(c1,c2,c3,c4,c5) value('b1','b2','b3','b4','b5');
insert into test(c1,c2,c3,c4,c5) value('c1','c2','c3','c4','c5');
insert into test(c1,c2,c3,c4,c5) value('d1','d2','d3','d4','d5');

# 建索引
create index idx_test_c1234 on test(c1,c2,c3,c4);
show index from test;

试分析以下，有哪些会有索引失效，失效哪一部分

# 1)
explain select * from test where c1='a1' and c2='a2' and c3='a3' and c4='a4;

# 2)
explain select * from test where c1='a1' and c2='a2' and c4='a4' and c3='a3';

# 3)
explain select * from test where c1='a1' and c2='a2' and c3>'a3' and c4='a4;

# 4)
explain select * from test where c1='a1' and c2='a2' and c4>'a4' and c3='a3';

# 5)
explain select *from test where c1='a1' and c2='a2' and c4='a4' order by c3;

# 6)
explain select * from test where c1='a1' and c2='a2' order by c3;

# 7)
explain select * from test where c1='a1' and c2='a2' order by c4

# 8)
explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;

# 9)
explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;

# 10)
explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
explain select * from test03 where c1='a1' and c2='a2' order by c3,c2;
# 11)
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;
# 12)
explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
# 13)
explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;

答案和分析

• 1—4都正常使用了索引

• 5 c1,c2正常使用，c3使用了索引的排序功能（索引有两大功能，查找和排序）

• 7用了c1,c2然后用剩下的数据进行fileSort

• 8用了c1，c2和c3使用了索引的排序功能

• 9只用了c1，出现了fileSort

• 10 用了c1,c2，c3使用了索引的排序功能，同时order by 后的c2相当于一个常量，此时即使c2,c3调换顺序，也并不影响使用。

• 11和10相同，c5不在索引中，但并不影响

• 12用了1个，两个用来排序

• 13用了1个，出现了文件内排序

总结：

分组之前必排序。排序之后定失效。

5. 案例补充