存储引擎
体系结构
存储引擎
- 增删改查，索引的实现方式
- 基于表的，不是基于库的
| 存储引擎 | 文件 |
|---|---|
| innodb | xxx.idb |
| myisam | xxx.sdi（表结构），xxx.MYD（数据），xxx.MYI（索引） |
| memory | xxx.sdi |
索引
分类
-
按字段特性分
-
按物理存储结构分
回表查询
SQL性能分析
查询操作频次
SHOW GLOBAL STATUS LIKE "Com_______"; (7个下划线)
慢查询日志
-- 查看是否开启
SHOW VARIABLES LIKE "slow_query_log";
-- 配置文件中
slow_query_log = 1 --开启慢查询日志
slow_query_log_file = /var/lib/mysql --慢查询日志的存放路径
long_query_time = 2 --超过两秒的雨具会被记录
profile详情
-- 是否支持
SELECT @@profiling
-- 是否开启
SELECT @@have_profiling;
-- 开启profile
SET profile = 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执行计划
| 字段名 | 含义 |
|---|---|
| id | id相同，执行顺序从上到下；id不同，值越大，越先执行 |
| type | NULL、system、const、eq_ref、ref、range、index、all |
| key | 使用的索引 |
使用
- 最左前缀法则：如果跳跃某一列，索引将部分失效（后面的字段索引失效)。【联合索引】
- 出现范围查询（>,<），范围查询右侧的列索引失效。【联合索引】
explain select * from tb_user where profession='软件工程' and age >30 and status ='0';
explain select * from tb_user where profession='软件工程'and age >=30 and status='O';
-
索引列上运算，索引失效
-
字符串不加引号，索引失效
-
后模糊走索引，前模糊不走索引
-
or条件两侧都有索引才会走索引，否则索引失效
-
数据分布影响，如果mysql评估走索引更慢，就不走索引
-
SQL提示
-- use index() 建议使用索引
explain select * from tb_user use index(idx_user_pro) where profession ='软件工程';
-- ignore index() 忽略某个索引
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
-- force index() 强制使用索引
explain select * from tb_user force index(idx_user_pro) where profession = "软件工程';
- 索引覆盖，避免回表查询 （select xxx）
- 前缀索引
-- 截取多长合适：计算选择性来权衡
select count(distinct substring(email,1,5) / count(*) from tb_user ;
- 尽量使用联合索引（而非单列索引），进行索引覆盖，减少回表查询
索引设计原则
-
针对于数据量较大，且查询比较频繁的表建立索引。
-
针对于常作为查询条件（where）、排序（orderby）、分组（groupby）操作的字段建立索引。
-
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
-
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
-
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
-
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
-
如果索引列不能存储NULL值，请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
SQL优化【加索引】
插入数据
- 批量插入
- 手动提交事务
- 主键数据插入
- 大批量查数据：load指令
-- 客户端连接服务端时，加上参数 --Local-infile
-- 设置全局参数localinfile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sqll.log' into table 'tb_user fields terminated by', lines terminated by "\n';
主键优化
- 满足业务需求的情况下，尽量降低主键的长度。【减少占用】
- 插入数据时，尽量选择顺序插入，选择使用
AUTO_INCREMENT自增主键。【减少数据页的分裂与合并】
order by优化
Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。Using index：通过有序索引l顺序扫描直接返回有序数据【覆盖索引】，这种情况即为using index，不需要额外排序，操作效率高。
group by优化
满足最左前缀法则即可
limit 优化
覆盖索引+子查询
-- 全表扫描
select * from tb_sku limit 5000000,10;
-- 子查询走索引
select s.* from tb_sku s，(select id from tb sku order by id limit 9000000,10) a where s.id = a.id;
count优化
updata优化
根据索引字段进行更新，否则行锁升级为表锁
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。
锁
全局锁
全库的数据备份
-- 加全局锁
flush tables with read lock;
-- 导出数据库
mysqldump -uroot -p1234 itcast > itcast.sql;
-- 释放锁
unlock tables；
-- innodb快照读实现备份，不加锁
mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql
表级锁
表锁
共享读锁，独占写锁
元数据锁（meta data lock, MDL）
避免在操作数据时对表结构（元数据）进行修改。
意向锁
提高行锁表锁冲突时的判断速度。
- 意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁写锁（write）互斥。
- 意向排他锁（Ix）：与表锁共享锁（read）及写锁（write）都互斥。意向锁之间不会互斥。
行级锁
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁
- 行锁（RecordLock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
- 间隙锁（GapLock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
- 临键锁（Next-KeyLock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。
行锁
默认情况下，InnoDB在 REPEATABLE READ 事务隔离级别运行，InnoDB使用临键锁进行搜索和索引扫描，以防止幻读。
-
索引上的等值查询（唯一索引）
- 对已存在的记录，优化为行锁。
- 不存在的记录，优化为间隙锁。
-
索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，优化为间隙锁
-
索引上的范围查询（唯一索引），会锁到不满足条件的第一个值为止。
-
InnoDB的行锁是针对于 索引 加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。