MySQL 进阶篇
存储引擎
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，不是基于库的 -- 默认innodb
mysql server 结构
1、连接层
2、服务层
3、引擎层
4、存储层
innodb 逻辑存储结构
分为四个组成
1、表空间: TableSapce
2、Segment: 段
3、Extent: 区
4、Page: 页
5、Row: 行
特点：支持事务 事务安全,支持行锁,外键
应用场景：对事务的完整性有比较高的要求
MyISAM引擎
特点：不支持事务，支持表锁，不支持外键
memory
特点： 内存存放数据 受断电影响
使用的是hash索引
索引
索引概述
索引: 是帮助mysql高效获取数据的数据结构(有序)
特点
1、提高数据检索效率,减少io成本
2、索引需要占用空间
3、提高查询的效率,但会降低增删改的效率
索引结构类型
1、B+tree索引 重点
2、hash索引
索引结构
二叉树
1、小于父节点的存放在左侧,大于就放右侧
2、 按大小顺序插入数据,就会形成一条链表,导致层级较度,查询速度慢
B-tree
1、解决了二叉树的层级深度问题,查询慢
2、一个节点可以有多个子节点,度数就是一个节点的子节点个数, 度数为5,可以存4个key,5个指针
3、每个节点上存放key和数据
4、演变过程 -> 插入数据时候，如果超过节点key数，中间节点上移
B+tree
与B-tree区别
1、所有数据都会出现在叶子节点
2、叶子节点形成一个单向链表
innodb使用的是B+tree结构索引做了优化
1、叶子节点形成一个双向循环链表
为什么选择B+tree?
1、相对于二叉树,层级更少,搜索效率更高
2、父节点不存放数据,全部数据存放在叶子节点,所以节点可以存放更多的key和指针,这样也使得层级深度变浅
3、但是B+tree需要到叶子节点才能拿到数据,而B-tree不需要
为什么使用双向链表?
1、范围搜索、排序
为什么不使用hash索引?
B+tree支持范围匹配, 排序操作
索引分类
1、主键索引 默认只能有一个
2、唯一索引
3、常规索引
4、全文索引
根据索引的存储形式分为两类
1、聚焦索引 叶子节点存放行数据 特点: 必须有且只有一个
· 有主键，主键索引就是聚集索引
· 不存在主键,将使用第一个唯一(unique)索引作为聚焦索引
· 如果没有主键,如果没有合适唯一索引,会自动生成rowid的隐藏索引
2、二级索引 叶子节点存放关联的主键 特点: 可存在多个
案例：
条件: 如果id为主键,name 为二级索引
1、执行sql: select * from xxx where id=11;
-> 这种就会去遍历聚焦索引的B+tree
2、执行sql: select id from xxx where name="xxx";
-> 这种就会去遍历二级索引的B+tree
3、执行sql: select * from xxx where name="xxx";
-> 这种就会去遍历二级索引的B+tree,然后拿到id去回表查询,到聚焦索引中遍历B+tree
索引语法
创建索引
create [unique | fulletext] index index_name on 表名(列名,...);
create index idxxxxx on xxx表(name); -> 为name创建一个索引
create unique index idxxxxx on xxx表(phone); -> 为phone创建一个唯一索引
create index ixxxx on xxx表(xxx,sss,ddd) -> 为xxx,sss,ddd创建联合索引,顺序有讲究
创建原则
1、针对于数据量较大,且查询比较频繁的表建立索引
2、针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
3、尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高
4、如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引
5、尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率
6、要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率
7、 如果索引列不能存储 NULL 值,请在创建表时使用 NOT NULL 约束它.当优化器知道每列是否包含NULL值时,
它可以更好地确定哪个索引最有效地用于查询。
查看索引
show index from 表名;
删除索引
drop index index_name on 表名;
索引失效
为什么索引会失效？
案例：范围查询右边失效原理
举例 select * from testTable where a>1 and b=2; a、b建立联合索引
首先: innodb使用B+tree索引结构,当联合索引时,键值对就大于1个,且当a字段确定的情况下,b才是有序的
因为B+tree只会选取一个字段来构建有序树,a是有序的。
并且B+tree查询数据是使用了二分查找的算法,其算法前提就是链表是有序的
由于a>1是可以走索引,但是他的取值不确定,这也导致b是无序的,无序的b无法使用二分法进行查找
索引的使用原则?
1、写sql时如果想用联合索引必须复合最左前缀原则
给a、b、c创建联合索引
where a=xx and b=xx and c=xx; 能使用联合索引
where a=xx and b=xx; 能使用联合索引
where a=xx and c=xx; 【失效】不能使用联合索引 中间跳过了b
where b=xx and c=xx; 【失效】不能使用联合索引
2、联合索引的范围查询 > 或 <
where a=xx and b>xx and c=xx; 【失效】不使用,使用了> 使得右侧的查询失效
如何解决这个问题呢？尽量使用 >= 或 <=
3、索引列运算
不要再索引的列上进行运算操作,索引将失效
select x from xxx表 where substring(a,10,2) = "15";【失效】 使用了函数运算，不使用索引
4、字符串不加引号 索引会失效
select x from xxx表 where a=xx; 【失效】不使用,a是字符串类型,但没有加上双引号,索引会失效
5、模糊查询 仅仅是尾部模糊匹配,索引不会失效,如果是头部进行模糊匹配,索引会失效
select * from where a like "%工程"; 【失效】
select * from where a like "软件%";
6、or 连接的条件
用or分割的条件,如果前面的条件列中有索引,而后面的列中没有索引,那么设计的索引都不会被使用
select * from xxx where a="xxx" or g="xxx";【失效】 因为g这个字段是没有索引的,所以整个都会失效
解决方法: 给g创建索引
7、数据分布影响
如果MySQL评估索引比全表查询更慢,则不使用索引,这是MySQL自己决策的
8、sql提示 【优化sql手段】
是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的
use index(index_name) 建议MySQL执行哪个索引
force index(index_name) 强制让MySQL执行这个索引
ignore index(index_name) 忽略哪个索引
select * from xxx表 use index(xxx) where ...;
9、覆盖索引 【优化sql手段】
尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到),减少使用select *
如果发生回表查询
我们需要查看explain select ...;中的extra
-> using index condition 这个就是回表了
-> using where ,using index 这个是没有回表
案例: 一张表,有四个字段(id,username,pwd,status)由于数据量大,需要对一下SQL语句进行优化,
该如何进行才是最优方案
select id,username,pwd from usertable where username="xxx";
最优解: username,pwd建立联合索引 -> 二级索引,id为聚焦索引
这样就是不需要回表查询,直接就覆盖索引
10、前缀索引 【优化sql手段】
创建前缀索引的语法: create index index_name on table_name(col(n)); col(5)取这个字段的前面5个字符作为索引
前缀长度决定原则
根据索引选择性来决定,选择性是指不重复的索引值和数据表的记录总数的比值,
索引选择性越高则查询效率就越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的
如何计算选择性:
select count(distinct email)/count(*) from table_name;
-> 根据email字段去重之后的数量/表中的数量,可以知道email重复率高不高,等于1最适合
select count(distinct substring(email,1,10))/count(*) from table_name; 取email前面10个字符来看看选择性好不好
sql性能分析 mysql解释器中运行
show global status like "Com_______"; 列出数据库的所有操作频次
1、mysql> show global status like "Com_______";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 0 |
| Com_delete | 1 |
| Com_insert | 3 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 25 |
| Com_signal | 0 |
| Com_update | 7 |
| Com_xa_end | 0 |
+---------------+-------+
10 rows in set (0.01 sec)
默认情况下慢查询日志是不开启的! 在配置文件路径: /etc/my.conf
2、在配置文件后加
slow_query_log = 1
long_query_time = 2 -> 超过2秒就会被认定为慢查询,并记录下来
3、日志存放路径: /var/lib/mysql/localhost_slow.log
性能分析2 profile详情
1、select @@profiling 查看是否开启profile
2、set profiling=1; 开启profile
3、然后去执行sql。。。
4、show profiles; 展示所有的sql指令耗时情况
5、show profile for query 15; 查看query_id=15的sql指令具体在各阶段的耗时情况
性能分析3 explain、desc关键字 显示sql的执行计划
explain select * from xxx; 显示这条查询语句的执行中表如何连接和连接的顺序
mysql> explain select * from demo;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | demo | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
各字段的含义:
1、id表示执行顺序 id相同的从上到下执行 id不同,越大越先执行
2、type 表示连接类型,性能由好到差: NULL,system,const,eq_ref,ref,range,index,ALL
3、possible_key 可能应用到这张表的索引,一个或者多个
4、key 代表实际应用的索引 可以用来判断是否用了索引
5、key_len 索引值 可以用来判断是否用了索引
sql 优化
insert 优化
推荐批量插入,不建议单条插入
批量插入: insert into table_name values(..),(...),(...),(...)...
一条条插入: insert into table_name values(...);insert into table_name values(...);insert into table_name values(...)
因为一条条插入每次插入都需要与MySQL建立连接,效率较低
如果数据量很大
不推荐insert into
推荐使用load
手动提交事务
默认是自动提交事务,一步操作就会提交一个事务,这样多个操作会提交多个事务,导致效率低
手动提交事务的话,我们可以多次操作之后,统一提交事务
主键顺序插入性能高于乱序插入
原理:innodb存储引擎中,表数据都是根据主键顺序组织存放,这种存放方式叫做索引组织表
1、乱序导致页分裂 2、如果删除其中一行数据，会出现页合并的现象
主键设计原则
1、满足业务需求的情况下，尽量降低主键的长度。
(聚集索引只有一个，但是二级索引有多个，而二级索引叶子节点存放的值就是主键，若主键很长，则会浪费大量的空间)
2、插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT自增主键。
（若为顺序插入，则页数据是一页一页的顺序增加，但是若为乱序插入，则可能会出现 页分裂）
3、尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
（这种设置的主键为乱序，所以在插入时可能会出现 页分裂，并且主键长度相对较长，在检索时会耗费大量的磁盘空间）
4、业务操作时，避免对主键的修改。
（主键作为唯一的标识，若修改主键，还需要修改索引结构，代价大）
order by 优化
1、Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，
不是通过索引直接返回排序结果的排序 都叫 FileSort 排序
2、Using index：通过有序索引顺序扫描直接返回有序数据
create index index_name on table_name(age desc, name asc);
优化细节：
1、根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
2、尽量使用覆盖索引
3、多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC / DESC）
4、如果不可避免的出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k)
group by优化
在分组操作时，可以通过索引来提高效率
分组操作时，索引的使用也是满足最左前缀法则的
limit 优化
优化思路：一般分页查询时，通过创建 覆盖索引 能购比较好地提高性能，可以通过覆盖索引加子查询形式进行优化
select * from user limit 500000,10; 未优化
select u.* from user u,(select id from user order by id limit 500000,10) a where u.id = a.id; 优化
update
后面的条件一定要根据索引来更新某条数据，不然会升级为表锁
