目录

mysql体系结构

理解：

存储引擎

前言

查看当前数据库所支持的存储引擎

在创建表的时候指定搜索引擎

InnoDB存储引擎

InnoDB特点

InnoDB存储引擎所涉及的磁盘文件

查看系统变量

InnoDB的逻辑存储结构

MyISAM存储引擎

myisam特点

MyISAM存储引擎涉及的文件

Memory存储引擎

Memory存储引擎特点

涉及的文件结构

存储引擎的特点

存储引擎的选择

索引

举例说明有索引与没索引的区别

索引优缺点

索引的数据结构

索引在不同存储引擎的支持情况

索引的数据结构

二叉树

红黑树

B-tree（多路平衡查找树）

B树的构建过程

B+Tree

B+Tree特点

B+Tree的构建过程

mysql中索引的B+Tree 

Hash索引

为name字段创建hash索引

hash索引的特点

注意：

innoDB中B+Tree相对于B-Tree的优势

索引的分类

聚集索引的选取规则

索引的使用过程 

查询例子理解

索引语法

表用例（tb_user）

sql性能分析

sql执行频次

慢查询日志

profile详情

explain执行计划

explain执行计划各字段的含义

索引的使用

验证索引的效率（太夸张了）

索引的使用原则

表用例（tb_user）

最左前缀法则

范围查询

注意：

索引列运算

字符串不加引号

模糊查询

or连接的条件

数据分布影响

sql提示

覆盖索引

在执行计划中的extra属性

前缀索引

前缀长度的选择

单列索引与联合索引

联合索引情况

索引的设计原则

sql优化

插入数据的优化

insert优化

大批量插入数据

主键优化

数据的组织方式

页分裂

页合并

主键的设计原则

order by优化

总结：

group by优化

注意：

limit分页查询的优化

count优化

count的几种方法

update优化

注意：

举个例子-tb_user表

mysql体系结构

理解：

连接层：（连接池）主要接收客户端的连接，然后完成一些连接的处理以及认证授权的相关操作以及相关的一些安全方案，服务器也会为安全接入的每个客户端验证他所具有的权限

服务层：绝大部分的核心功能都是在服务层完成的，像sql接口，并完成缓存的查询，sql的分析和优化，部分内置函数的执行，所有跨存储引擎的实现也都是在服务层实现的，比如DML语句，DDL语句的封装，存储过程，视图，触发器，

引擎层：存储引擎真正的负责了mysql中数据的存储和提取，服务器通过API和存储引擎进行通信，不同的存储引擎具有不同的功能，这样我们可以根据自己的需要来选取合适的存储引擎，innoDB引擎是mysql5.5版本之后默认的存储引擎（索引的实现就在此层）

存储层：主要是将数据存储到文件系统之上，并完成与存储引擎的交互

存储引擎

前言

含义：存储引擎就是存储数据，建立索引，更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型

查看当前数据库所支持的存储引擎

语法：show engines;

在创建表的时候指定搜索引擎

#创建表my_myisam,并指定具体存储引擎
create table my_myisam(
id int,
name varchar(10)
)engine=myisam;

注意：创建表的时候是可以指定存储引擎的，如果不指定存储引擎，那么默认就选择innoDB引擎

InnoDB存储引擎

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎

InnoDB特点

1. DML操作遵循ACID原则，支持事务；
2. 行级锁，提高并发访问性能
3. 支持外键foreign key约束，保证数据的完整性和正确性

InnoDB存储引擎所涉及的磁盘文件

表名.ibd文件，InnoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。当然，还有一个参数：innodb_file_per_table，此参数表示到底是多张表共用一个共享表空间还是说每一张表都对应一个表空间文件，mysql8.0中此开关默认打开的就代表每一张表都对应一个表空间

查看系统变量

语法：show variables like '变量名'; 

InnoDB的逻辑存储结构

理解：一个表空间中会存储若干个段一个段中又会包含若干个区，，针对一个区来说又会包含若干个页（页包含索引页、数据页）一个页中又会包含若干个行（表结构中存储的一行一行的数据）行内又会包含操作事务的id、指针、一个一个的字段。而在InnoDB逻辑结构当中Page页是磁盘操作的最小单元，一个区的大小是固定的，为1m，一个页大小是16k，所以一个区中可以包含64个页

MyISAM存储引擎

注意：myisam为mysql早期的默认存储引擎

myisam特点

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

MyISAM存储引擎涉及的文件

• xxx.sdi：里面存储的是表结构的信息
• xxx.MYD：存放的是表中数据
• xxx.MYI：存储索引

Memory存储引擎

Memory引擎的表数据是存储在内存中的，由于受到硬件问题，或是断电问题的影响，只能将这些表作为临时表或缓存使用

Memory存储引擎特点

• 内存存放，访问速度快
• hash索引（默认）

涉及的文件结构

• xxx.sdi：存储表结构的信息

存储引擎的特点

存储引擎的选择

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

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

索引

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

举例说明有索引与没索引的区别

案例：select * from user where age=45;

理解：没有索引的情况下会对全表从上到下逐个扫瞄，进而找到符合条件的数据，这样效率极低，而有索引的化，就会帮你排好序进而实现快速查找，效率高

索引优缺点

索引的数据结构

mysql的索引是在存储引擎层实现的，不同存储引擎有不同的结构，主要包含以下几种

索引在不同存储引擎的支持情况

注意：我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引

索引的数据结构

二叉树

一个节点下面最多包含2个子节点，那么在上图中可见36是根节点，左侧的子树当中所有的节点比36要小，右侧的所有的节点比36要大，最终形成了如图

查找方式：从根节点开始，根据比较结果选择左右分支进而精确定位数据

二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量的情况下，层级较深，检索速度慢。

红黑树

红黑树缺点：大数据量的情况下，层级较深，检索速度慢。

B-tree（多路平衡查找树）

多路：一个节点下面可以包含多个子节点

树的度：一个节点的子节点个数

以一颗最大度数为5的b-tree为例（每个节点最多储存4个key，5个指针）

B树的构建过程

1. 度数为5则有4key和5个指针
2. 插入数据时从底部开始进行比较插入特定位置
3. 逐个插入数据，当有节点的度数大于5时，中间元素向上分裂，左右边数据为2个单体，分别处于左右边
4. 当第2层有节点大于5时中间元素向上分裂，与之前已经分裂的元素合并，左右边数据为2个单体，分别处于左右边

实验网址：B-Tree Visualization (usfca.edu)

B+Tree

以一颗最大度数为4的B+Tree为例

B+Tree特点

• 所有的元素都会出现在叶子节点，上面的非叶子节点主要起到了索引的作用，叶子节点是用来存放数据的
• 在B+Tree的数据结构当中，叶子节点形成了一颗单向链表，每一个节点都会通过一个指针指向下一个节点元素

B+Tree的构建过程

1. 插入数据时从底部开始进行比较插入特定位置
2. 逐个插入数据，当有节点的度数大于5时，中间元素向上分裂并将中间元素移到右边数据部分，左右边数据为2个单体，分别处于左右边，左边指针指向右边，叶子节点形成一颗单向链表
3. 当第2层有节点大于5时中间元素向上分裂，与之前已经分裂的元素合并，并且该向上分裂的数据依然合并到右边部分
4. 除了最后一行数据。以上的数据分裂过程不会伴随着数据向右合并

mysql中索引的B+Tree 

mysql索引数据结构对经典的B+Tree进行了优化，在原B+Tree的基础上，增加一个指向相邻叶子结点的链表指针，就形成了带有顺序指针的B+Tree，提高区间的访问性能

Hash索引

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中

为name字段创建hash索引

操作时首先为每一行算出hash值，然后再拿出name字段的所有值，然后根据name字段的所有值通过它内部的hash函数去计算每一个name值它应该落在哪个hash的槽位上

注意：如果两个或多个键值映射到一个相同的槽位上，他们就产生了hash冲突（也称hash碰撞），可以通过链表来解决

hash索引的特点

• hash索引只能用于对等比较（=，in），不支持范围查询（between,>,<,……）
• 无法利用索引完成排序操作
• 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

注意：

• 再mysql中，支持hash索引的是memory引擎，而InnoDB中具有自适应的Hsah功能，hash索引是存储引擎根据B+Tree在指定条件下自动构建的
• 自适应hash功能指mysql会根据我们得查询条件在指定的条件下会自动的将B+Tree索引构建成hash索引

innoDB中B+Tree相对于B-Tree的优势

对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样就导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只有增加树的高度，导致性能降低（原因：树的度减少）

索引的分类

在InnoDB存储引擎中，根据索引的储存形式，又可以分为以下两种

聚集索引的选取规则

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用唯一索引来作为聚集索引
• 如果表中没主键，也没唯一索引，则innoDB会自动生成一个rowid作为隐藏的聚集索引

索引的使用过程 

查询例子理解

例子：select * from user where name="Arm";

理解：因为是根据name（name已经添加了二级索引）字段查找，所以是二级索引。然后根据字母ASCII大小进行比较进而找到特定的位置，进而找到了主键，然后根据主键通过聚集索引进而找到了整行的数据。由二级所引到聚集索引的过程也称回表查询。

回表查询：先走二级索引找到对应的主键值在根据主键值到聚集索引当中拿到这一行的行数据（见上面我的理解）

索引语法

创建索引：create [unique|fulltext] index 索引名 on 表名(索引列1，索引列2);

注意：如果不写索引类型，默认创建常规索引

查看索引：show index from 表名;

删除索引：drop index 索引名 on 表名;

表用例（tb_user）

#查看tb_user的索引
show index from tb_user;
 
#为name字段创建常规索引
create index idx_user_name on tb_user(name);
 
#为age字段创建唯一索引
create unique index idx_user_age on tb_user(age);
 
#删除age字段的索引
drop index idx_user_age on tb_user;
 
#创建联合索引为age和phone（也可以创建联合唯一索引）
create index idx_user_all on tb_user(age,phone);

sql性能分析

sql执行频次

查看提供服务器状态信息：show [session|global] status like 'Com_______';

注意：Com后面7个下划线可以看到增删改查的执行次数

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认10秒）的所有sql语句的日志

查询慢查询日志的开启情况：show variables like 'slow_query_log';

注意：mysql的慢查询日志默认没有开启，需要在mysql的配置文件（etc/my.cnf）中添加配置如下

配置完毕之后，通过以下指令重新启动mysql服务器进行测试，查看慢日志中记录的信息/var/lib/mysql/localhost-slow.log

profile详情

show profiles能够在做sql优化时帮助我们了解时间都耗费到哪了，通过have_profiling参数，能够看到当前mysql是否支持profile操作

查看数据库是否支持profile操作：select @@have_profiling;

注意：默认profiling是关闭的，可以通过set语句在session/global级别开启profiling

查看profiling开关是否开启：select @@profiling;

开启profiling开关：set profiling=1;

查看每一条sql语句的耗时情况：show profiles;

查看指定query_id的sql语句各个阶段的耗时情况：show profile for query query_id;

查看指定query_id的sql语句cpu的使用情况：show profile cpu for query query_id;

explain执行计划

explain或者desc命令获取mysql如何执行select语句的信息，包括select语句执行过程中表现如何连接和连接的顺序

语法：explain select 字段列表 from 表名 where 条件;

#直接在select语句之前加上关键字explain/desc

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，则没有使用索引
• key_len：表示索引中使用到的字节数，该值为索引字段最大可能长度，并非使用的长度，在不损失精确性的前提下，长度越短越好
• rows：mysql认为必须要执行查询的行数，在InnoDB引擎当中，是一个估计值，可能并不总是准确的
• filtered：表示返回结果的行数占需读取行数的百分比，filtered值越大越好
• extra：额外的信息

索引的使用

验证索引的效率（太夸张了）

• 未建立索引之前，执行sql语句查看sql的耗时
• 针对字段创建索引
• 然后再次执行相同的sql语句，再次查看sql的耗时

索引的使用原则

表用例（tb_user）

最左前缀法则

含义：如果索引了多个列（联合索引），要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃了某一列，索引将部分失效（跳过的字段后面的字段索引失效） 

原因：mysql创建复合索引的规则是首先会对复合索引的最左边，也就是第一个字段进行排序，然后在第一个字段的基础上对第二个字段进行排序

#展示tb_user表的索引
show index from tb_user;
 
#创建联合索引分别作用于name,age,phone字段
create index idx_user_all on tb_user(name,age,phone);
 
#索引长度72，索引生效，并且和条件顺序没关系
explain select * from tb_user where name="码云" and age=55 and phone="18800008888";
explain select * from tb_user where phone="18800008888" and name="码云" and age=55;
 
#索引长度38，索引生效
explain select * from tb_user where name="码云" and age=55;
 
#索引长度33，索引生效
explain select * from tb_user where name="码云";
 
#索引为null，没用索引
explain select * from tb_user where age=55 and phone="18800008888";
 
#索引长度为33，name索引生效，phone索引没生效
explain select * from tb_user where name="码云" and phone="18800008888";

范围查询

含义：联合索引中，出现范围查询（>,<），范围右侧的列索引失效（使用范围查询的索引生效）

#创建联合索引分别作用于name,age,phone字段
create index idx_user_all on tb_user(name,age,phone);
 
#索引长度38，name、age索引生效，phone索引失效,由此观之这里的顺序是指创建索引的顺序
explain select * from tb_user where name="码云" and phone="18800008888" and age>45;
explain select * from tb_user where name="码云" and age>45 and phone="18800008888";
 
#索引长度72，索引全部生效
explain select * from tb_user where name="码云" and age>=45 and phone="18800008888";

注意：

• 在使用复合索引时，如果业务允许的情况下尽量使用>=或=<这样的运算符
• 范围右侧是指创建索引中（排好序的）字段的右侧，而不是条件的右侧

索引列运算

不要在索引列上进行运算，否则索引将会失效

#创建phone的索引
create index idx_user_phone on tb_user(phone);
 
#索引生效
desc select * from tb_user where phone="18800008888";
 
#索引为空，没生效
desc select * from tb_user where substring(phone,10,2)="88";
 
#创建age的索引
create index idx_user_age on tb_user(age);
 
#索引生效
desc select * from tb_user where age=35+10;

字符串不加引号

注意：字符串类型字段使用时，不加“”，索引将失效

#查看我的建表语句得出phone字段为字符串类型
show create table tb_user;
 
#不加单引号也可以查询
select * from tb_user where phone=18800008888;
 
#不加单引号索引没生效
explain select * from tb_user where phone=18800008888;
 
#加了单引号索引生效
desc select * from tb_user where phone="18800008888";

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，则索引失效

#创建name的索引
create index idx_user_name on tb_user(name);
 
#尾部模糊，索引生效
desc select * from tb_user where name like "李%";
desc select * from tb_user where name like "李__";
 
#头部模糊。索引失效
desc select * from tb_user where name like "%宏";
desc select * from tb_user where name like "__宏";
 
#只要头部模糊索引失效
desc select * from tb_user where name like "%彦%";
desc select * from tb_user where name like "_彦_";

or连接的条件

用or分割开的条件，如果仅仅一侧有索引，那么涉及的索引都不会被用到，若两边都有索引才会用到

#创建age索引
create index idx_user_age on student(age);
 
#查看索引结构，发现有主键和age索引
show index from tb_user;
 
#数据量大的情况下测试
desc select * from tb_user where id=1 or age=35;

数据分布影响

如果mysql评估使用索引比全表扫描更慢，则不使用索引

#为phone创建索引
create index idx_user_phone on tb_user(phone);
 
#走了索引（字符串形式可以使用>,<)
desc select * from tb_user where phone>"18800009991";
 
#没走索引，直接全表扫瞄了
desc select * from tb_user where phone>"18800001111";

sql提示

sql提示，是优化数据库的一个重要的手段，简单来说，就是在sql语句中加入一些人为的提示来达到优化操作的目的

• 告诉数据库用哪个索引：use index( 索引名)——这其实只是给mysql一个建议
• 告诉数据库不用哪个索引：ignore index(索引名)
• 告诉数据库必须使用这个索引：force index(索引名)

#提前创建好索引
create index idx_user_age on tb_user(age);
create index idx_user_all on tb_user(age,name);
 
#走复合索引
desc select * from tb_user where age=35;
 
#走的是idx_user_age索引
desc select * from tb_user ignore index(idx_user_all) where age=35;

覆盖索引

覆盖索引：一条select查询语句在查询过程中使用到了索引，并且查询需要返回的列在该索引当中都已经全部找到了对应的字段值

注意：尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少使用select *（因为很容易回表查询）。

在执行计划中的extra属性

• usering index condition：使用了索引，但是需要回表查询
• usering where；usering index：使用了索引，数据直接索引覆盖

#查看索引
show index from tb_user;
 
#提前创建好索引
create index idx_user_age on tb_user(age);
create index idx_user_all on tb_user(name,age);
create index idx_user_phone on tb_user(phone);
 
#未走回表查询，直接索引覆盖，因为走复合索引name，age，有索引，id能找到因为就在下面
explain select id,name,age from tb_user where name='码云';
 
#用的复合索引,所以phone不可被索引覆盖走了回表查询
explain select id,name,age,phone from tb_user where name='码云';

注意：回表查询走不走的原因见上文

前缀索引

含义：当字段类型未字符串时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘io，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

语法：create index 索引名 on 表名(索引列名(n));

解释：后面的索引列名(n)标识截取字符串的前n个字符建立索引。

前缀长度的选择

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

计算整个字段选择性：select count(distinct 字段)/count(*) from 表名;

计算截取字段选择性：select count(distinct substring(字段,1,5))/count(*) from 表名;

#创建前缀索引
create index idx_user_name_1 on tb_user(name(1));
 
#计算前缀索引的选择性,观之为1，nice
select count(distinct substring(name,1,1))/count(*) from tb_user;
 
#用到了前缀索引
desc select * from tb_user where name="码云";

单列索引与联合索引

• 单列索引：即一个索引只包含单个列
• 联合索引：一个索引包含多个列

在业务场景中，如果存在多个查询条件，考虑针对字段建立索引时，建议建立联合索引，而非单列索引

#提前创建好索引
create index idx_user_age on tb_user(age);
create index idx_user_name on tb_user(name);
 
#用到了age索引即使name和age都有索引mysql会评估选择执行哪个索引效率会更高，然后选择该索引完成本次查询
#如果创建联合索引，mysql就会选择用联合索引
desc select * from tb_user where name="码云" and age=55;

联合索引情况

索引的设计原则

sql优化

插入数据的优化

insert优化

• 批量插入（每一次执行insert都需要与数据库进行建立连接，进行网络传输），但是一次插入的数据不建议超过1000条
• 手动事务提交（避免了频繁的事务开启与事务提交）
• 主键顺序插入（mysql的数据组织结构）

大批量插入数据

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用mysql数据库提供的load指令进行插入

查看开关是否开启：select @@local_infile;

注意：主键顺序插入的性能高于乱序插入

主键优化

数据的组织方式

索引组织表：在innoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（iot）

注意：在B+Tree的数据结构中，所有的数据都会出现在叶子节点，而非叶子节点仅仅起到了索引数据的作用，而非叶子节点的索引以及叶子节点的数据都是存放在一个逻辑结构——页：Page当中的

主键顺序插入：数据一页一页的顺序插入，一页满了，再开下一页。

页分裂

页可以为空，也可以填充一半，页也可以填充100%，每个页包含2-N行数据（如果一行数据过大，会行溢出），根据主键排序；以下面为例

主键乱序插入：

两张页已经写满了，如今要插入一个主键为50的数据，如下

由此观之id为50的数据应该存放在47之后，但是两张页已经满了，所以便会开辟一个新的数据页，将第一个数据页找到50%的位置，将（23、47）移动到新的数据页，然后再将50插入到该数据页

将链表指针再进行重新的设置，如下

页合并

当删除一行数据时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且他的空间变得允许被其他记录声明使用。当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），innoDB会开始寻找最靠近的页（前/后）看看是否可以将两个页合并以优化空间使用。

注意：MERGE_THRESHOLD为合并页的阈值（默认为50%），可以自己设置，在创建表或者创建索引时指定。

主键的设计原则

• 满足业务需求的情况下，尽量降低主键的长度。（联合索引挂的就是主键）
• 插入数据时尽量选择顺序插入，选择使用auto_increment这样的自增主键
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号（因为乱序）
• 业务操作时，避免对主键的修改

order by优化

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

注意：order by排序使用覆盖索引才能使索引生效

#查看索引
show index from tb_user;
 
#using filesort,没索引
desc select age,gender from tb_user order by gender,age;
 
#提前创建好索引
create index idx_user_all on tb_user(gender,age);
 
#使用索引覆盖了
desc select gender,age from tb_user order by gender,age;
desc select gender,age from tb_user order by gender desc,age desc;
 
#Using index; Using filesort
desc select gender,age from tb_user order by gender desc,age;

不同方式创建索引：create index idx_user_age_phone on tb_user(age asc,phone desc);

不同排序方式下的叶子节点

总结：

• 根据排序字段建立合适的索引，多字段排序时也遵循最左前缀法则
• 尽量使用覆盖索引
• 多字段排序时，一个升序一个降序，此时需要注意联合索引在创建时的规则（asc/desc）
• 如果不可避免的出现filesort，大数据量排序时，可以适当的增大排序缓冲区的大小sort_buffer_size(默认256k）——如果排序的时候这256k沾满了，这时候就涉及到了磁盘文件它会在磁盘文件当中进行排序，此时性能极低

group by优化

#查看索引
show index from tb_user;
 
#没索引，全表扫描，效率低
desc select gender,count(*) from tb_user group by gender;
 
create index idx_user_all on tb_user(gender,age);
 
#用到了索引
desc select gender,count(*) from tb_user group by gender;
 
#用到索引，Using index; Using temporary; Using filesort效率低
desc select age,count(*) from tb_user group by age;
 
#用到索引Using index，效率高
desc select gender,age,count(*) from tb_user group by gender,age;
 
#用到索引Using where; Using index，效率高
desc select age,count(*) from tb_user where gender="1" group by age;

注意：

• 在分组操作时可以创建适当索引来提高效率
• 分组操作时，索引的使用也满足最左前缀法则

limit分页查询的优化

limit分页中一个常见又头痛的问题就是limit 2000000,10，此时需要mysql排序2000001记录，仅仅返回之后的10条，其他记录丢弃，查询排序代价非常大

优化思路：一般分页查询时，通过创建覆盖索引能够比较好的提升性能，可以通过覆盖索引和子查询的形式进行优化

原理：两次聚集索引查询——防止回表

count优化

• mysim引擎会把一个表的总行数记录在磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高，但是前提是在查询的时候，后面没有where条件，直接查询这张表的总数据量
• innoDB引擎就麻烦了，即使没有查询条件，那么他在执行count(*)时也需要把数据一行一行的从引擎里面读出来，然后进行累计计数——优化思路，自己计数（插入数据时直接把某一个计数加一，删除数据时把某一计数减一）

count的几种方法

count()是一个聚合函数，对于返回的结果集，一行一行的判断，如果count函数的参数不是null，累计值就加一，否则不加，最后返回累计值

用法：count(*)、count(主键)、count(字段)、count(1)

count（主键）：innoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）

count（字段）：

• 没有not null约束：innoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加
• 有not null约束：innoDB引擎会遍历整张表，把每一行的字段值都取出来，返回个服务层，直接按行进行累加

count（1）：innoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加

count（*）：innoDB引擎并不会把全部的字段都取出来，而是专门做了优化，不取值，服务层直接按行进行累加

按照效率排序的话：count（字段）

update优化

注意：

• 多客户端在执行update的语句时候我们一定要根据索引字段进行更新，这样就会锁住一行，不然的话就会锁住整张表，一旦锁住整张表，那么并发性能就会降低
• innoDB的行锁是针对于索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则就会从行锁升级为表锁

举个例子-tb_user表

前提：打开手动提交事务 

情况一（name字段没加索引）

客户端一执行语句：update tb_user set age=8 where name="冰冰";（没有提交事务）

因为name没有索引，所以直接把表锁住了，客户端2根本不能操作表

情况二（name字段加索引）

客户端一执行语句：update tb_user set age=8 where name="冰冰";（没有提交事务）

因为name加了索引，所以直接找到name=“冰冰”；这行，把整行锁住，在没有提交事务前客户端二不能操作该行，但是可以操作该表中的其他行