MYSQL数据库进阶篇
1. 存储引擎1.1 MySQL体系结构
https://i-blog.csdnimg.cn/direct/674d72d1581741a58a8164d210022743.png
1.2 存储引擎
存储引擎就是存储数据、建立索引、更新/查询数据等技能的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表范例。
存储引擎是用来控制数据库如何存，如何取，如何组织的。
不同的存储引擎，索引结构不同。
1.2.1 创建存储引擎
CREATE TABLE 表名 (
字段1 字段1类型,
字段2 字段2类型,
字段3 字段3类型,
......
字段6 字段6类型# 最后没逗号
) ENFINE = INNODB ;
1.2.2 查察数据库支持的存储引擎
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
https://i-blog.csdnimg.cn/direct/3939854b2f064cae89df664102f4975e.png
这个文件是二进制文件，是看不懂的，想要看懂这个文件，要执行
ibd2sdi table01.ibd
https://i-blog.csdnimg.cn/direct/6cb2a56677a54e08bbb43752fe3a2b2e.png
InnoDB结构特点
https://i-blog.csdnimg.cn/direct/06134c2b17744c46b2ee5643899784fa.png
1.3.2 MyISAM
[*]先容：
MyISAM是MySQL早期的默认存储引擎。
[*]特点：
不支持事务，不支持外键。
支持表锁，不支持行锁。
访问速率快。
[*]文件
xxx.sdi: 存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引
https://i-blog.csdnimg.cn/direct/3819182018b945b0b948b1e992624441.png
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高效获取数据的数据库结构（有序），在数据之外，数据库体系还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，如许就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
https://i-blog.csdnimg.cn/direct/b09bf3c3bcfe437d858c8ac332952a0e.png
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 索引语法
创建索引
CREATE INDEX 索引名 ON 表名(字段名);
查询索引
SHOW INDEX FROM 表名;
删除索引
DROP INDEX 索引名 ON 表名;
例子：
# 给员工表的name创建索引
CREATE INDEX idx_emp_name ON emp(name);
## 查询员工表的全部索引
SHOW INDEX FROM emp;
# 给员工表的age和salary创建联合索引
CREATE INDEX idx_emp_age_salary ON emp(age, salary);
# 给员工表的name删除索引
DROP INDEX idx_emp_name ON emp;
2.4 SQL性能分析
2.4.1 SQL执行频率
MySQL数据库毗连成功后，通过show status命令可以提供服务器状态信息，通过如下指令，可以查察当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频率：
SHOW GLOBAL STATUS LIKE 'Com_______';
图片仅为测试数据库
https://i-blog.csdnimg.cn/direct/0f8174dbcc04467aac1d7893b3f6bd0d.png
2.4.2 慢查询日记
慢查询日记记载了全部执行时间凌驾指定参数(long_query_time，单位：秒，默认10s)的全部SQL语句的日记。
MySQL的慢查询日记默认没有开启，必要在MySQL的设置文件(/etc/my.cnf) （linux下）中设置。
windows下可以查找my.cnf文件
mysql --help | findstr /i "my.cnf"
#服务端基本设置
#开启MySQL慢查询开关
slow_query_log = 1
#慢日志查询文件路径
slow_query_log_file = D:\dev\mysql-8.1.0-winx64\mysql-8.1.0-winx64\lib/mysql/log-file
# 设置慢查询日志的时间为2s，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time = 2
设置完成之后，通过以下指令重新启动MySQL服务器进行测试，查察日记文件中记载的信息/var/lib/mysql/localhost-slow.log（linux默认位置）。
查询
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
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操作：
SELECT @@HAVE_PROFILING;
默认profiling是关闭的
profile详情：
执行一系列的业务SQL操作，然后通过如下指令查察指令的执行耗时：
# 查看每一条的SQL的耗时基本情况
SHOW PROFILES;
# 查看指定Query_ID的SQL语句各个阶段的耗时情况
SHOW PROFILE FOR QUERY Query_ID;
# 查看指定Query_ID的SQL语句各CPU的使用情况
SHOW PROFILE CPU FOR QUERY Query_ID;
https://i-blog.csdnimg.cn/direct/864fa3528d9646a18647cbb5bdbfbdfa.png
https://i-blog.csdnimg.cn/direct/d83377134d154b578025ab69ca33bb98.png
https://i-blog.csdnimg.cn/direct/40455657a9a14c1aba72621b1c952dfe.png
2.5 SQL性能分析（explain执行计划）
EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包罗在SELECT语句执行过程中表如何毗连和毗连的顺序。
语法：
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
例如：
# 1. 查询研发部和财务部的所有员工信息
EXPLAIN SELECT e.* FROM emp e WHERE e.dept_id IN (SELECT d.id FROM dept d WHERE d.name = '研发部' OR d.name = '财务部');
https://i-blog.csdnimg.cn/direct/44bee1d7ce924592ae194836590955ea.png#pic_center
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，则没有使用索引。
例子：
EXPLAIN SELECT * FROM emp WHERE name = '黎明';
https://i-blog.csdnimg.cn/direct/d240988ddbe049ce93bde3c1489955b0.png
[*]key_len：表示索引中的使用字段，该值为索引最大大概长度，并非实际使用长度，在不损失精确性的条件下，长度越短越好。
[*]rows：MySQL认为必须要执行查询的行数，在innodb引擎中，是一个估计值，大概并不是准确的。
[*]filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。
2.6 索引失效情况
2.6.1 最左前缀法则
CREATE INDEX idx_emp_name_age_salary ON emp(name, age, salary);
SHOW INDEX FROM emp;
EXPLAIN SELECT * FROM emp WHERE name = '黎明1' AND age = 66 AND salary = 50000; # key（实际用到的索引）为idx_emp_name_age_salary key_len为92
EXPLAIN SELECT * FROM emp WHERE name = '黎明1' AND age = 66; # key（实际用到的索引）为idx_emp_name_age_salary key_len为87
EXPLAIN SELECT * FROM emp WHERE name = '黎明1'; # key（实际用到的索引）为idx_emp_name_age_salary key_len为82
EXPLAIN SELECT * FROM emp WHERE age = 66 AND salary = 50000;# key（实际用到的索引）NULL
EXPLAIN SELECT * FROM emp WHERE salary = 50000; # key（实际用到的索引）NULL
EXPLAIN SELECT * FROM emp WHERE name = '黎明1' AND salary = 50000; # key（实际用到的索引）为idx_emp_name_age_salary key_len为82, name后的索引失效，所以为82
如果索引了多列（团结索引），要遵守最左前缀法则。最左前缀法则值得是查询从索引的最左列开始，并且不跳过索引中的列。
如果条约某一列，索引将部分失效（背面的字段索引失效）。
2.6.2 范围查询
EXPLAIN SELECT * FROM emp WHERE name = '黎明1' AND age = 66 AND salary = 50000; # key（实际用到的索引）为idx_emp_name_age_salary key_len为92
EXPLAIN SELECT * FROM emp WHERE name = '黎明1' AND age > 66 AND salary = 50000; # key（实际用到的索引）为idx_emp_name_age_salary key_len为87
# age后面的列失效，索引索引长度职位name和age的，没有salary
EXPLAIN SELECT * FROM emp WHERE name = '黎明1' AND age >= 66 AND salary = 50000; # key（实际用到的索引）为idx_emp_name_age_salary key_len为92
# 针对非联合索引
团结索引中，出现范围查询(>,<)，范围查询右侧的索引列失效。
所以一般为>=, <=
2.6.3 索引列盘算
CREATE INDEX idx_emp_name ON emp(name);
DROP INDEX idx_emp_name_age_salary ON emp;
EXPLAIN SELECT * FROM emp WHERE name = 'XXX'; # key（实际用到的索引）为idx_emp_name key_len为82
EXPLAIN SELECT * FROM emp WHERE SUBSTRING(name, 1, 6) = 'XXX'; # key（实际用到的索引）为NULL 针对非联合索引
不要在索引列上进行运算操作，索引将失效。
2.6.4 非单
CREATE INDEX idx_emp_name ON emp(name);
EXPLAIN SELECT * FROM emp WHERE name = 'XXX'; # key（实际用到的索引）为idx_emp_name key_len为82
EXPLAIN SELECT * FROM emp WHERE name = 111; # key（实际用到的索引）为NULL 针对非联合索引
字符串不加单引号,造成索引失效。
2.6.4 前模糊
CREATE INDEX idx_emp_name ON emp(name);
EXPLAIN SELECT * FROM emp WHERE name LIKE 'XXX%'; # key（实际用到的索引）为idx_emp_name key_len为82
EXPLAIN SELECT * FROM emp WHERE name LIKE '%XXX'; # key（实际用到的索引）为NULL 针对非联合索引
前模糊，索引会失效。
2.6.4 or毗连的条件
CREATE INDEX idx_emp_name ON emp(name);
SHOW INDEX FROM emp;
EXPLAIN SELECT * FROM emp WHERE name = 'XXX' OR id = 1; # key（实际用到的索引）为idx_emp_name,PRIMARY key_len为82,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语句中参加一些人为的提示来到达优化操作的目的。
# USE INDEX
EXPLAIN SELECT * FROM emp USE INDEX (idx_emp_name) WHERE name = '黎明1';
# FORCE INDEX
EXPLAIN SELECT * FROM emp FORCE INDEX (idx_emp_name) WHERE name = '黎明1';
# IGNORE INDEX
EXPLAIN SELECT * FROM emp IGNORE INDEX (idx_emp_name) WHERE name = '黎明1';
2.8 覆盖索引
只管使用覆盖索引（查询使用了索引，并且必要返回的列，在该索引中已经全部能够找到），淘汰select;
CREATE INDEX idx_emp_name_job_salary ON emp(name, job, salary);
EXPLAIN SELECT name, job, salary FROM emp WHERE name = '黎明1' AND job = 'xxx' AND salary = 50000; # 不需回表
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。
例子：
https://i-blog.csdnimg.cn/direct/30419941b9fa48f1a6939584d65c609f.png
一张表，有四个字段(id, username, password, status)，由于数据量大，必要对SQL语句进行优化，该如何进行才是最优方案？
CREATE INDEX idx_表_username_password ON 表(username, password);
建立团结索引。
2.9 索引使用
2.9.1 前缀索引
当字段范例为字符串（varchar、text等）时，有时候必要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询的效率。此时可以只将字符串的一部分前缀，建立索引，如允许以大大节约索引空间，从而进步索引效率。
语法：
CREATE INDEX idx_xxx ON table_name(column(n));
索引长度：
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记载总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
SELECT COUNT(DISTINCT email) / COUNT(*) FROM email_test;
SELECT COUNT(DISTINCT SUBSTRING(email, 1, 6)) / COUNT(*) FROM email_test;
例子：
CREATE TABLE email_test(
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(20)
);
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');
SELECT COUNT(DISTINCT email) / COUNT(*) FROM email_test;
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
CREATE INDEX idx_email_test_2 ON email_test(email(2));
https://i-blog.csdnimg.cn/direct/90312908fe714cf9a1138cbfa94886dd.png
2.9.2 单列索引和团结索引
单列索引：即一个索引只包含了一个列。
团结索引： 即一个索引包含了多个列。
在业务场景中，如果存在多个查询条件，思量针对查询字段建立索引时，建议建立团结索引，而非单列索引。
2.9.3 索引计划原则
[*]针对数据量较大，且查询比力频仍的表建立索引。
[*]针对常作为查询条件(where)、排序(order by)、分组(group by)的字段建立索引。
[*]只管选择区分度高的列作为索引，只管建立唯一索引，区分度越高，使用索引的效率越高。
[*]如果是字符串范例的字段，字段的长度较长，可以针对字段的特点，建立前缀索引。
[*]只管使用团结索引，淘汰单列索引，查询时，团结索引很多时候可以覆盖索引，节省存储空间，避免回表，进步查询效率。
[*]要控制索引的数量，索引并不是多多益善，索引越多，维护索引的代价也就越大，会影响增编削的效率。
[*]如果索引不能存储NULL值，请在创建表时使用NOT NULL束缚它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地应用于查询。
3. SQL优化
3.1 insert插入
[*]批量插入：
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');
[*]手动提交事务
START TRANSACTION;
INSERT INTO email_test VALUES(11, 'xxx1@qq.com');
INSERT INTO email_test VALUES(12, 'xxx2@qq.com');
INSERT INTO email_test VALUES(13, 'xxx3@qq.com');
COMMIT;
[*]主键顺序插入
主键乱序插入：8 1 9 21 88 2 6 89 7 10
主键顺序插入：1 2 8 10 28 30 31 60 70 90
大批量插入数据： 如果要一次性插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的Load指令进行插入。
https://i-blog.csdnimg.cn/direct/811d0232b3f34274a6b270297fa0662a.png
# 客户端连接服务器时， 加上参数--local-infile
mysql --local-infile -uroot -p
# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
SET GLOBAL LOCAL_INFILE = 1;
# 指令load指令将准备好的数据，加载到表结构中
LOAD DATA LOCAL INFILE 'D:\\aaa.log' INTO TABLE email_test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
SELECT @@LOCAL_INFILE;
3.2 主键插入
https://i-blog.csdnimg.cn/direct/37d570796f84478ba2d1018569c92772.png
主键计划原则：
[*]满足业务需求的条件下，只管降低主键的长度。
[*]插入数据时，只管选择顺序插入，使用AUTO_INCREMENT自增主键。
[*]只管不要使用UUID做主键或者是其他自然主键，比如身份证号。
[*]业务操作时，避免对主键的修改。
3.3 order by优化
[*]Using filesort：通过表的索引或者全表扫描，读取满足的数据行，然后在排序缓冲区sort buffer中完成排序操作，全部不是通过索引直接返回排序结果的排序都叫FileSort排序。
[*]Using index：通过有序索引顺序扫描直接返回数据，这种情况叫using index，不必要额外排序，操作效率高。
# order by优化
# 没有创建索引时，根据age,job进行排序
EXPLAIN SELECT id, age, job FROM emp ORDER BY age, job; #Using filesort
# 创建索引
CREATE INDEX idx_age_job ON emp(age,job);
# 创建索引后，根据age,job进行升序排序
EXPLAIN SELECT id, age, job FROM emp ORDER BY age, job; #Using index
# 创建索引后，根据age,job进行降序排序
EXPLAIN SELECT id, age, job FROM emp ORDER BY age DESC, job DESC; #Backward index scan; Using index
EXPLAIN SELECT id, age, job FROM emp ORDER BY job, age; #Using index; Using filesort job在age前
EXPLAIN SELECT id, age, job FROM emp ORDER BY age ASC , job DESC; #Using index; Using filesort
优化根据age，job一个升序，一个降序
CREATE INDEX idx_emp_age_job_ad ON emp(age ASC , job DESC);
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. 根据age进行分组
EXPLAIN SELECT age, COUNT(*) FROM emp GROUP BY age; #Using temporary
# 2. 创建索引
CREATE INDEX idx_age_job_salary ON emp(age, job, salary);
# 有索引后，根据age进行分组
EXPLAIN SELECT age, COUNT(*) FROM emp GROUP BY age; #Using index
# 有索引后，根据age,job 分组
EXPLAIN SELECT age, job, COUNT(*) FROM emp GROUP BY age, job; #Using index
EXPLAIN SELECT job, COUNT(*) FROM emp GROUP BY job ; # Using index; Using temporary
EXPLAIN SELECT job, COUNT(*) FROM emp WHERE age = 22 GROUP BY job; # Using index
[*]在分组操作时，可以通过索引来进步效率。
[*]分组操作时，索引的使用也是满足最左前缀法则。
3.5 limit优化
一个常见又头疼的标题就是limit 2000000, 10，此时必要MySQL排序前2000010条数据，仅仅返回2000000-2000010的记载，其他记载扬弃，查询排序的代价非常大。
SELECT * FROM emp ORDER BY id LIMIT 2000000, 10;
SELECT * FROM emp WHERE id IN (SELECT * FROM emp ORDER BY id LIMIT 2000000, 10); # This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
# 上面的不行，所以只能使用内连接了
SELECT e1.* FROM emp e1, (SELECT * FROM emp ORDER BY id LIMIT 2000000, 10) e2 WHERE e1.id = e2.id;
优化思路：一般分页查询时，通过创建覆盖索引能够比力地进步性能，可以通过覆盖索引加子查询形式进行优化。
3.6 count优化
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企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]