MYSQL数据库进阶篇

1. 存储引擎

1.1 MySQL体系结构

1.2 存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技能的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表范例。
存储引擎是用来控制数据库如何存，如何取，如何组织的。
不同的存储引擎，索引结构不同。
1.2.1 创建存储引擎

1. CREATE TABLE 表名 (
2.         字段1 字段1类型[COMMENT 字段1注释],
3.         字段2 字段2类型[COMMENT 字段2注释],
4.         字段3 字段3类型[COMMENT 字段3注释],
5.         ......
6.         字段6 字段6类型[COMMENT 字段6注释]  # 最后没逗号
7. ) ENFINE = INNODB [COMMENT 表注释];

复制代码

1.2.2 查察数据库支持的存储引擎

1. SHOW ENGINES;

复制代码

1.3 存储引擎特点

1.3.1 InnoDB

• 先容：
  InnoDB是一种分身高可靠性和高性能的通用存储引擎，在MySQL 5.5之后，InnoDB是默认的MySQL存储引擎。
  
• 特点：
  DML操作遵循ACID模子，支持事务；
  行级锁，进步并发访问性能；
  支持外键FOREIGN KEY束缚，保证数据的完整性和精确性。
  
• 文件：
  xxx.ibd: xxx代表的是文件名，innoDB引擎的每张表都会对应一个如许的表空间文件，存储该表的结构（frm、sdi）、数据和索引。
  参数：innodb_file_per_table
  
  
  
  这个文件是二进制文件，是看不懂的，想要看懂这个文件，要执行
  

1. ibd2sdi table01.ibd

复制代码

InnoDB结构特点

1.3.2 MyISAM

• 先容：
  MyISAM是MySQL早期的默认存储引擎。
  
• 特点：
  不支持事务，不支持外键。
  支持表锁，不支持行锁。
  访问速率快。
  
• 文件
  xxx.sdi: 存储表结构信息
  xxx.MYD: 存储数据
  xxx.MYI: 存储索引
  
  
  
  

1.3.2 MEMORY

先容：
MEMORY引擎的表数据时存储在内存中的，由于受到硬件标题，或断电标题的影响，只能将这些表作为临时表或缓存使用。
特点：
内存存放
hash索引（默认）
文件：
xxx.sdi： 存储表结构信息。
1.3.3 对比

特点InnoDBMyISAMMEMORY存储限定64TB有有事务安全支持--锁机制行锁表锁表锁B+tree索引支持支持支持Hash索引--支持全文索引支持（5.6版本后）支持-空间使用高低N/A内存使用高低中等批量插入速率低高高支持外键支持-- 1.3.4 存储引擎选择

在选择存储引擎的时候，应该根据应用体系的特点选择符合的存储引擎。对于复杂的应用体系，还可以根据实际情况选择多种存储引擎进行组合。

• InnoDB:是MySQL的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比力高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB是比力符合的选择。
  
• MyISAM:如果应用是以读操作和插入操作为主，只有少量的更新和删除，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎好坏常得当的。
  
• MEMORY: 将全部数据生存在内存中，访问速率快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限定，太大的表无法缓存在内存中，而且无法保障数据的安全性。
  

1.3.5 总结

• 体系结构
  毗连层、服务层、引擎层、存储层
  
• 存储引擎特点：
  InnoDB和MyISAM： 事务、外键、行锁
  
• 存储引擎应用
  InnoDB: 存储业务体系对于事务、数据完整性要求较高的焦点数据。
  MyISAM： 存储业务体系的非焦点业务。
  

2. 索引

先容：
索引（index）是帮助MySQL高效获取数据的数据库结构（有序），在数据之外，数据库体系还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，如许就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

2.1 索引优缺点

优势劣势进步数据检索的效率，降低数据库的IO本钱索引列也是要占据空间的通过索引对数据进行排序，降低数据排序的本钱，降低CPU的消耗索引大大进步了查询效率，同时也降低了更新表的速率，如对表进行INSERT、UPDATE、DELETE时、效率降低 2.2 索引结构

索引结构描述B+tree树最常见的索引范例，大部门引擎都支持B+树索引Hash索引底层数据结构使用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询R-tree索引（空间索引）空间索引是MyISAM引擎的一个特殊索引范例，主要用于地理空间数据范例，通常使用较少Full-text（全文索引）是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene,Solr，ES 索引InnoDBMyISAMMEMORYB+tree索引支持支持支持Hash索引不支持不支持支持R-tree索引不支持支持不支持Full-text5.6版本之后又支持支持不支持 我们寻常说的索引，如果没有特殊指明，都是B+树结构组织的索引。
2.3 索引语法

创建索引

1. CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名);

复制代码

查询索引

1. SHOW INDEX FROM 表名;

复制代码

删除索引

1. DROP INDEX 索引名 ON 表名;

复制代码

例子：

1. # 给员工表的name创建索引
2. CREATE INDEX idx_emp_name ON emp(name);
3. ## 查询员工表的全部索引
4. SHOW INDEX FROM emp;
5. # 给员工表的age和salary创建联合索引
6. CREATE INDEX idx_emp_age_salary ON emp(age, salary);
7. # 给员工表的name删除索引
8. DROP INDEX idx_emp_name ON emp;

复制代码

2.4 SQL性能分析

2.4.1 SQL执行频率

MySQL数据库毗连成功后，通过show [global|session] status命令可以提供服务器状态信息，通过如下指令，可以查察当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频率：

1. SHOW GLOBAL STATUS LIKE 'Com_______';

复制代码

图片仅为测试数据库

2.4.2 慢查询日记

慢查询日记记载了全部执行时间凌驾指定参数(long_query_time，单位：秒，默认10s)的全部SQL语句的日记。
MySQL的慢查询日记默认没有开启，必要在MySQL的设置文件(/etc/my.cnf) （linux下）中设置。
windows下可以查找my.cnf文件

1. mysql --help | findstr /i "my.cnf"

复制代码

1. #服务端基本设置
2. [mysqld]
4. #开启MySQL慢查询开关
5. slow_query_log = 1
7. #慢日志查询文件路径
8. slow_query_log_file = D:\dev\mysql-8.1.0-winx64\mysql-8.1.0-winx64\lib/mysql/log-file
10. # 设置慢查询日志的时间为2s，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
11. long_query_time = 2

复制代码

设置完成之后，通过以下指令重新启动MySQL服务器进行测试，查察日记文件中记载的信息/var/lib/mysql/localhost-slow.log（linux默认位置）。
查询

1. SHOW VARIABLES LIKE 'slow_query_log';
2. SHOW VARIABLES LIKE 'slow_query_log_file';
3. SHOW VARIABLES LIKE 'long_query_time';

复制代码

注意：
linux实用知识
tail 命令可用于查察文件的内容，有一个常用的参数 -f 常用于查阅正在改变的日记文件。
tail -f filename 会把 filename 文件里的最尾部的内容显示在屏幕上，并且不断刷新，只要 filename 更新就可以看到最新的文件内容。
2.4.3 SQL性能分析（profile）

show profiles能够在做SQL优化时帮助我们了解时间都小号到哪里去了。通过have_profileing参数，能否看到当前MySQL是否支持profile操作：

1. SELECT @@HAVE_PROFILING;

复制代码

默认profiling是关闭的
profile详情：
执行一系列的业务SQL操作，然后通过如下指令查察指令的执行耗时：

1. # 查看每一条的SQL的耗时基本情况
2. SHOW PROFILES;
3. # 查看指定Query_ID的SQL语句各个阶段的耗时情况
4. SHOW PROFILE FOR QUERY Query_ID;
5. # 查看指定Query_ID的SQL语句各CPU的使用情况
6. SHOW PROFILE CPU FOR QUERY Query_ID;

复制代码

2.5 SQL性能分析（explain执行计划）

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包罗在SELECT语句执行过程中表如何毗连和毗连的顺序。
语法：

1. EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

复制代码

例如：

1. # 1. 查询研发部和财务部的所有员工信息
2. EXPLAIN SELECT e.* FROM emp e WHERE e.dept_id IN (SELECT d.id FROM dept d WHERE d.name = '研发部' OR d.name = '财务部');

复制代码

explain执行计划各字段寄义：

• id：select查询的序号，标识查询中执行select’子句或者操作表的顺序（id雷同，执行顺序从上到下；id不同，值越大，越先执行）。
  
• select_type:：表示SELECT的范例，常见的取值有SIMPLE（简单表，即不使用表毗连或子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者背面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等。
  
• type:：表示毗连范例，性能由好到差的毗连范例为NULL、system、const、eq_ref、ref、range、index、all。
  
• possible_key：表示大概应用在这张表上的索引，一个或多个。
  
• key：实际使用的索引，如果为NULL，则没有使用索引。
  

例子：

1. EXPLAIN SELECT * FROM emp WHERE name = '黎明';

复制代码

• key_len：表示索引中的使用字段，该值为索引最大大概长度，并非实际使用长度，在不损失精确性的条件下，长度越短越好。
  
• rows：MySQL认为必须要执行查询的行数，在innodb引擎中，是一个估计值，大概并不是准确的。
  
• filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。
  

2.6 索引失效情况

2.6.1 最左前缀法则

1. CREATE INDEX idx_emp_name_age_salary ON emp(name, age, salary);
2. SHOW INDEX FROM emp;
4. EXPLAIN SELECT * FROM emp WHERE name = '黎明1' AND age = 66 AND salary = 50000; # key（实际用到的索引）为idx_emp_name_age_salary key_len为92
5. EXPLAIN SELECT * FROM emp WHERE name = '黎明1' AND age = 66; # key（实际用到的索引）为idx_emp_name_age_salary key_len为87
6. EXPLAIN SELECT * FROM emp WHERE name = '黎明1'; # key（实际用到的索引）为idx_emp_name_age_salary key_len为82
8. EXPLAIN SELECT * FROM emp WHERE age = 66 AND salary = 50000;  # key（实际用到的索引）NULL
9. EXPLAIN SELECT * FROM emp WHERE salary = 50000; # key（实际用到的索引）NULL
11. EXPLAIN SELECT * FROM emp WHERE name = '黎明1' AND salary = 50000; # key（实际用到的索引）为idx_emp_name_age_salary key_len为82, name后的索引失效，所以为82

复制代码

如果索引了多列（团结索引），要遵守最左前缀法则。最左前缀法则值得是查询从索引的最左列开始，并且不跳过索引中的列。
如果条约某一列，索引将部分失效（背面的字段索引失效）。
2.6.2 范围查询

1. EXPLAIN SELECT * FROM emp WHERE name = '黎明1' AND age = 66 AND salary = 50000; # key（实际用到的索引）为idx_emp_name_age_salary key_len为92
2. EXPLAIN SELECT * FROM emp WHERE name = '黎明1' AND age > 66 AND salary = 50000; # key（实际用到的索引）为idx_emp_name_age_salary key_len为87
3. # age后面的列失效，索引索引长度职位name和age的，没有salary
4. EXPLAIN SELECT * FROM emp WHERE name = '黎明1' AND age >= 66 AND salary = 50000; # key（实际用到的索引）为idx_emp_name_age_salary key_len为92
5. # 针对非联合索引

复制代码

团结索引中，出现范围查询(>,<)，范围查询右侧的索引列失效。
所以一般为>=, <=
2.6.3 索引列盘算

1. CREATE INDEX idx_emp_name ON emp(name);
2. DROP INDEX idx_emp_name_age_salary ON emp;
3. EXPLAIN SELECT * FROM emp WHERE name = 'XXX'; # key（实际用到的索引）为idx_emp_name key_len为82
4. EXPLAIN SELECT * FROM emp WHERE SUBSTRING(name, 1, 6) = 'XXX'; # key（实际用到的索引）为NULL 针对非联合索引

复制代码

不要在索引列上进行运算操作，索引将失效。
2.6.4 非单

1. CREATE INDEX idx_emp_name ON emp(name);
2. EXPLAIN SELECT * FROM emp WHERE name = 'XXX'; # key（实际用到的索引）为idx_emp_name key_len为82
3. EXPLAIN SELECT * FROM emp WHERE name = 111; # key（实际用到的索引）为NULL 针对非联合索引

复制代码

字符串不加单引号,造成索引失效。
2.6.4 前模糊

1. CREATE INDEX idx_emp_name ON emp(name);
2. EXPLAIN SELECT * FROM emp WHERE name LIKE 'XXX%'; # key（实际用到的索引）为idx_emp_name key_len为82
3. EXPLAIN SELECT * FROM emp WHERE name LIKE '%XXX'; # key（实际用到的索引）为NULL 针对非联合索引

复制代码

前模糊，索引会失效。
2.6.4 or毗连的条件

1. CREATE INDEX idx_emp_name ON emp(name);
2. SHOW INDEX FROM emp;
3. EXPLAIN SELECT * FROM emp WHERE name = 'XXX' OR id = 1; # key（实际用到的索引）为idx_emp_name,PRIMARY key_len为82,4
4. EXPLAIN SELECT * FROM emp WHERE name = 'XXX' OR salary = 50000; # key（实际用到的索引）为NULL OR针对非联合索引

复制代码

用or分割开的条件，如果or前的条件中的列有索引，而背面的列没有索引，那么涉及到的索引都不会被用到。
2.6.5 数据分布

如果MySQL评估使用索引比全表更慢，则不使用索引。
2.7 SQL提示

SQL提示：是优化数据库的一个重要本领，简单来说，就是在SQL语句中参加一些人为的提示来到达优化操作的目的。

1. # USE INDEX
2. EXPLAIN SELECT * FROM emp USE INDEX (idx_emp_name) WHERE name = '黎明1';
3. # FORCE INDEX
4. EXPLAIN SELECT * FROM emp FORCE INDEX (idx_emp_name) WHERE name = '黎明1';
5. # IGNORE INDEX
6. EXPLAIN SELECT * FROM emp IGNORE INDEX (idx_emp_name) WHERE name = '黎明1';

复制代码

2.8 覆盖索引

只管使用覆盖索引（查询使用了索引，并且必要返回的列，在该索引中已经全部能够找到），淘汰select;

1. CREATE INDEX idx_emp_name_job_salary ON emp(name, job, salary);
2. EXPLAIN SELECT name, job, salary FROM emp WHERE name = '黎明1' AND job = 'xxx' AND salary = 50000; # 不需回表
3. EXPLAIN SELECT name, job, salary, entrydate FROM emp WHERE name = '黎明1' AND job = 'xxx' AND salary = 50000;  #需回

复制代码

using index condition：查找使用了索引，但是必要回表查询数据
using where; using index; ：查找使用了索引，但是必要的数据都在索引列中能找到，所以不必要回表查询数据。
聚集索引叶子结点挂的是row，非聚集索引挂的是id。
例子：

   一张表，有四个字段(id, username, password, status)，由于数据量大，必要对SQL语句进行优化，该如何进行才是最优方案？

1. CREATE INDEX idx_表_username_password ON 表(username, password);

复制代码

建立团结索引。
2.9 索引使用

2.9.1 前缀索引

当字段范例为字符串（varchar、text等）时，有时候必要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询的效率。此时可以只将字符串的一部分前缀，建立索引，如允许以大大节约索引空间，从而进步索引效率。
语法：

1. CREATE INDEX idx_xxx ON table_name(column(n));

复制代码

索引长度：
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记载总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

1. SELECT COUNT(DISTINCT email) / COUNT(*) FROM email_test;
3. SELECT COUNT(DISTINCT SUBSTRING(email, 1, 6)) / COUNT(*) FROM email_test;

复制代码

例子：

1. CREATE TABLE email_test(
2.     id INT PRIMARY KEY AUTO_INCREMENT,
3.     email VARCHAR(20)
4. );
6. INSERT INTO email_test VALUES (1, 'caocao@qq.com'), (2, '1777999@qq.com'), (3, '1777990@qq.com'),(4, 'lisi@qq.com'),(5, 'wangwu@163.com'),(6, '1777999@q163.com'),(7, '657389530@qq.com'),(8, '5353636363@qq.com'),(9, 'gbfbfd@163.com'),(10, '177799559@qq.com');
9. SELECT COUNT(DISTINCT email) / COUNT(*) FROM email_test;
11. SELECT COUNT(DISTINCT SUBSTRING(email, 1, 2)) / COUNT(*) FROM email_test;

复制代码

COUNT(DISTINCT email) / COUNT(*)为1
COUNT(DISTINCT SUBSTRING(email, 1, 2)) 为0.7
COUNT(DISTINCT SUBSTRING(email, 1, 3)) 为0.7
COUNT(DISTINCT SUBSTRING(email, 1, 4)) 为0.7
所以选择前缀索引长度为2

1. CREATE INDEX idx_email_test_2 ON email_test(email(2));

复制代码

2.9.2 单列索引和团结索引

单列索引：即一个索引只包含了一个列。
团结索引： 即一个索引包含了多个列。
在业务场景中，如果存在多个查询条件，思量针对查询字段建立索引时，建议建立团结索引，而非单列索引。
2.9.3 索引计划原则

• 针对数据量较大，且查询比力频仍的表建立索引。
  
• 针对常作为查询条件(where)、排序(order by)、分组(group by)的字段建立索引。
  
• 只管选择区分度高的列作为索引，只管建立唯一索引，区分度越高，使用索引的效率越高。
  
• 如果是字符串范例的字段，字段的长度较长，可以针对字段的特点，建立前缀索引。
  
• 只管使用团结索引，淘汰单列索引，查询时，团结索引很多时候可以覆盖索引，节省存储空间，避免回表，进步查询效率。
  
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引的代价也就越大，会影响增编削的效率。
  
• 如果索引不能存储NULL值，请在创建表时使用NOT NULL束缚它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地应用于查询。
  

3. SQL优化

3.1 insert插入

• 批量插入：
  

1. INSERT INTO email_test VALUES (1, 'caocao@qq.com'), (2, '1777999@qq.com'), (3, '1777990@qq.com'),(4, 'lisi@qq.com'),(5, 'wangwu@163.com'),(6, '1777999@q163.com'),(7, '657389530@qq.com'),(8, '5353636363@qq.com'),(9, 'gbfbfd@163.com'),(10, '177799559@qq.com');

复制代码

• 手动提交事务
  

1. START TRANSACTION;
2. INSERT INTO email_test VALUES(11, 'xxx1@qq.com');
3. INSERT INTO email_test VALUES(12, 'xxx2@qq.com');
4. INSERT INTO email_test VALUES(13, 'xxx3@qq.com');
5. COMMIT;

复制代码

• 主键顺序插入
  

主键乱序插入：8 1 9 21 88 2 6 89 7 10
主键顺序插入：1 2 8 10 28 30 31 60 70 90
大批量插入数据： 如果要一次性插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的Load指令进行插入。

1. # 客户端连接服务器时， 加上参数--local-infile
2. mysql --local-infile -uroot -p
3. # 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
4. SET GLOBAL LOCAL_INFILE = 1;
5. # 指令load指令将准备好的数据，加载到表结构中
6. LOAD DATA LOCAL INFILE 'D:\\aaa.log' INTO TABLE email_test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
8. SELECT @@LOCAL_INFILE;

复制代码

3.2 主键插入

主键计划原则：

• 满足业务需求的条件下，只管降低主键的长度。
  
• 插入数据时，只管选择顺序插入，使用AUTO_INCREMENT自增主键。
  
• 只管不要使用UUID做主键或者是其他自然主键，比如身份证号。
  
• 业务操作时，避免对主键的修改。
  

3.3 order by优化

• Using filesort：通过表的索引或者全表扫描，读取满足的数据行，然后在排序缓冲区sort buffer中完成排序操作，全部不是通过索引直接返回排序结果的排序都叫FileSort排序。
  
• Using index：通过有序索引顺序扫描直接返回数据，这种情况叫using index，不必要额外排序，操作效率高。
  

1. # order by优化
2. # 没有创建索引时，根据age,job进行排序
3. EXPLAIN SELECT id, age, job FROM emp ORDER BY age, job; #Using filesort
5. # 创建索引
6. CREATE INDEX idx_age_job ON emp(age,job);
8. # 创建索引后，根据age,job进行升序排序
9. EXPLAIN SELECT id, age, job FROM emp ORDER BY age, job; #Using index
11. # 创建索引后，根据age,job进行降序排序
12. EXPLAIN SELECT id, age, job FROM emp ORDER BY age DESC, job DESC; #Backward index scan; Using index
14. EXPLAIN SELECT id, age, job FROM emp ORDER BY job, age; #Using index; Using filesort job在age前
16. EXPLAIN SELECT id, age, job FROM emp ORDER BY age ASC , job DESC; #Using index; Using filesort

复制代码

优化根据age，job一个升序，一个降序

1. CREATE INDEX idx_emp_age_job_ad ON emp(age ASC , job DESC);
3. EXPLAIN SELECT id, age, job FROM emp ORDER BY age ASC , job DESC; #Using index

复制代码

优化准则：

• 根据排序字段建立符合的索引，多字段排序时，也遵循最左前缀法则。
  
• 只管使用覆盖索引。
  
• 多字段排序，一个升序一个降序，此时必要注意团结索引在创建时的规则（ASC/DESC）。
  
• 如果不可避免的出现filesort，大数据量排序时，可以得当增加缓冲区大小sort_buffer_size(默认256k)。
  

3.4 group by优化

1. # 删除不必要的索引
2. # 1. 根据age进行分组
3. EXPLAIN SELECT age, COUNT(*) FROM emp GROUP BY age; #Using temporary
4. # 2. 创建索引
5. CREATE INDEX idx_age_job_salary ON emp(age, job, salary);
6. # 有索引后，根据age进行分组
7. EXPLAIN SELECT age, COUNT(*) FROM emp GROUP BY age; #Using index
8. # 有索引后，根据age,job 分组
9. EXPLAIN SELECT age, job, COUNT(*) FROM emp GROUP BY age, job; #Using index
11. EXPLAIN SELECT job, COUNT(*) FROM emp GROUP BY job ; # Using index; Using temporary
13. EXPLAIN SELECT job, COUNT(*) FROM emp WHERE age = 22 GROUP BY job; # Using index

复制代码

• 在分组操作时，可以通过索引来进步效率。
  
• 分组操作时，索引的使用也是满足最左前缀法则。
  

3.5 limit优化

一个常见又头疼的标题就是limit 2000000, 10，此时必要MySQL排序前2000010条数据，仅仅返回2000000-2000010的记载，其他记载扬弃，查询排序的代价非常大。

1. SELECT * FROM emp ORDER BY id LIMIT 2000000, 10;
2. SELECT * FROM emp WHERE id IN (SELECT * FROM emp ORDER BY id LIMIT 2000000, 10); #[42000][1235] This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
3. # 上面的不行，所以只能使用内连接了
4. SELECT e1.* FROM emp e1, (SELECT * FROM emp ORDER BY id LIMIT 2000000, 10) e2 WHERE e1.id = e2.id;

复制代码

优化思路：一般分页查询时，通过创建覆盖索引能够比力地进步性能，可以通过覆盖索引加子查询形式进行优化。
3.6 count优化

1. EXPLAIN SELECT COUNT(*) FROM emp;

复制代码

MyISAM：引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高。
InnoDB：引擎就贫困了，它执行count(*)的时候，必要把数据一行一行地从引擎里读出来，然后累积计数。
优化思路：本身计数
count的几种用法：

• count(主键)
  InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不大概为null）。
  
• count(字段)
  没有not null束缚： InnoDB引擎会遍历整张表把每一行的字段值取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
  有not null束缚：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
  
• count(1)
  InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
  
• count(*)
  InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
  

按照效率排序的话， count(字段) < count(主键id) < count(1) < count(*)。所以进利用用count(*)

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。