mysql
2021-02-28 19:24:50 1 举报AI智能生成
mysql面试知识点汇总，持续更新中
作者其他创作
大纲/内容
mysql最多可以存储64T的数据
mysql架构（了解）
CBO
基于成本的优化
RBO
基于规则的优化
Mysql事务
事务的基本要素（ACID）
原子性（Atomicity）
事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位
undo log
跟执行操作相反的操作
一致性非锁定读
一致性锁定读
一致性（Consistency）
事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到
根本
隔离性（Isolation）
同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账
锁
持久性（Durability）
事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚
redo log
子主题
小结
原子性是事务隔离的基础，隔离性和持久性是手段，最终目的是为了保持数据的一致性。
事务的并发问题
脏读
事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读
事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致
幻读
系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读
小结
系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读
MySQL事务隔离级别
MySQL事务隔离级别
默认的隔离级别
可重复读
MVCC
MVCC主要是为了提高并发性的读写性能，不用加锁就能让多个事务并发的读写
mysql语句执行顺序
1.FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
2.ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中
3.JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止
4.WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中
5.GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5
6.CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6
7.HAVING： 对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT7中
8.SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中
9.DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9
10.ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10
11.LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回
总结：from，on，join，where，group by，having，select，distinct，order by，limit
Mysql 引擎比较
mysql 有什么引擎
存储引擎是什么？
不同的存储引擎，存储的数据文件到磁盘上的格式是不同的
InnoDB
.frm
存储表结构
.ibd
存储的数据和索引
MyISAM
.frm
存储表结构
.MYD
存储表数据
.MYI
存储表索引
关于表引擎的命令
show engines; 查看myql所支持的存储引擎
show variables like '%storage_engine'; 查看mysql默认的存储引擎
show create table table_name 查看具体表使用的存储引擎
innoDB
B+tree
MyISAM
B+tree
Memory
哈希索引
关于 innodb
1. 提供事务、回滚、系统奔溃修复能力、多版本并发控制事务
2. 支持自增列
3. 支持外键
4. 支持事务以及事务相关联功能
5. 支持mvcc的行级锁
关于 MyISAM
1. 不支持事务、不支持行级锁，只支持并发插入的表锁，主要用于高负载的select
2. 支持三种不同的存储结构：静态、动态、压缩
比较：innoDB\MyISAM
1.InnoDB聚簇索引，MyISAM非聚簇索引
2.innodb支持事务，myisam不支持事务
3.innodb会有表锁和行锁，myisam表锁
4.innodb支持外键，MyISAM不支持外键
5.count：myisam虽然维护计数器统计每个表的总数，一旦加上条件一样要全表扫描啊
MySQL 索引
前提知识
什么是索引
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引
索引的文件存储形式与存储引擎有关
索引的文件结构
hash
二叉树
B树
B+树
一种是对于主键的范围查找和分页查找
另一种是从根节点开始，进行随机查找
索引
优点
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度
缺点
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
建立索引会占用磁盘空间的索引文件
索引类型
PRIMARY KEY INDEX(主键索引)
主键索引：根据主键建立的索引，不允许重复，不允许空值
ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col');
UNIQUE INDEX(非空索引)唯一索引
唯一索引： 用来建立索引的列的值必须是唯一的，允许空值
ALTER TABLE 'table_name' ADD UNIQUE index_name('col');
INDEX(普通索引、二级索引、辅助索引)
普通索引：用表中的普通列构建的索引，没有任何限制
ALTER TABLE 'table_name' ADD INDEX index_name('col');
全文索引
全文索引：用大文本对象的列构建的索引
ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col');
组合索引
组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');
需要注意的是正确的顺序依赖于该索引查询，同时需要考虑如何更好的满足排序和分组的需要
查看索引
mysql> SHOW INDEX FROM table_name; \G
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
尝试以下实例
尝试以下实例
索引的创建与删除
添加索引
ALTER Table `table_name` ADD PRIMARY KEY（`column`）
ALTER Table `table_name` ADD UNIQUE（`column`）
ALTER Table `table_name` ADD INDEX（`column`）
ALTER Table `table_name` ADD FULLTEXT（`column`）
删除
ALTER Table `table_name` drop index index_name
ALTER Table `table_name` ADD PRIMARY KEY（`column`）
ALTER Table `table_name` ADD UNIQUE（`column`）
ALTER Table `table_name` ADD INDEX（`column`）
ALTER Table `table_name` ADD FULLTEXT（`column`）
删除
ALTER Table `table_name` drop index index_name
索引设计的原则
创建索引
1.适合索引的列是出现在where子句中的列，或者连接（join）子句中指定的列，查询中排序的字段，查询中统计的字段或者分组字段
2.基数较小的类，索引效果较差，没有必要在此列建立索引
3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
无需创建索引
1.数据表记录太少，2000条以下不用
2.频繁更新的字段（如果创建索引，每当更新字段都会更新索引），不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可
3.数据重复且分布平均的字段，不宜创建索引（性别字段，国籍字段等）
删除索引
索引一经创建不能修改，如果要修改索引，只能删除重建。可以使用DROP INDEX index_name ON table_name;删除索引
名词解析
覆盖索引
什么叫做覆盖索引
解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖
解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引
解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）
不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引
覆盖索引的优点
1.可以优化缓存，减少磁盘IO操作
2.可以减少随机IO,随机IO操作变为顺序IO操作
3.可以避免对Innodb主键索引的二次查询
4.可以避免MyISAM表进行系统调用
聚簇索引
什么是聚簇索引
不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键紧凑的存储在一起
聚簇索引的好处
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多 个数据块中提取数据，所以节省了大量的 io 操作
聚簇索引的限制
对于 mysql 数据库目前只有 innodb 数据引擎支持聚簇索引，而 Myisam 并不支持聚簇索引。 由于数据物理存储排序方式只能有一种，所以每个 Mysql 的表只能有一个聚簇索引。一般情况下就是 该表的主键。 为了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主键列尽量选用有序的顺序 id，而不建议用 无序的 id，比如 uuid
非聚簇索引
数据文件跟索引文件分开存放
前缀索引
回表
用二级索引的时候会发生回表
索引下推
最左匹配
索引匹配方式
案例的前提：staffs
sql
全值匹配
全值匹配指的是和索引中的所有列进行匹配
explain select * from staffs where name ='July' and age = '23' and pos = 'dev';
匹配最左前缀
只匹配前面几列
explain select * from staffs where name = 'July' and age = '23'
explain select * from staffs where name = 'July';
匹配列前缀
可以匹配某一列的值的开头部分
explain select * from staffs where name like 'J%';
exlpain select * from staffs where name like '%y'
匹配范围值
可以查找某一个范围数据
explain select * from staffs where name > 'Mary';
精确匹配某一列并范围匹配另一个列
可以查询第一列的全部和第二列的部分
explain select * from staffs where name = 'July' and age > 25;
只访问索引查询
查询的时候只需要方位索引，不需要访问数据行，本质上就是覆盖索引
explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';
口诀
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等非空还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等非空还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！
索引优化小细节
当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层
select actor_id from actor where actor_id = 4
select actor_id from actor where actor_id + 1 = 5;
尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询
自然主键
自然主键是指事物属性中的自然唯一标示（例如身份证号）
代理主键
代理主键是指与业务无关的，无意义的数字序列值
使用前缀索引
前缀索引的优化说明
select
count(distinct(last_name))/count(last_name),
count(distinct(left(last_name,3)))/count(last_name),
count(distinct(left(last_name,4)))/count(last_name),
count(distinct(left(last_name,5)))/count(last_name)
from actor;
count(distinct(last_name))/count(last_name),
count(distinct(left(last_name,3)))/count(last_name),
count(distinct(left(last_name,4)))/count(last_name),
count(distinct(left(last_name,5)))/count(last_name)
from actor;
越接近于全值去重后的比率越优
使用索引扫描来排序
具体情况具体分析（explain）
union all，in，or 都能够使用索引，但是推荐使用in
explain
select * from actor where actor_id = 1
union all
select * from actor where actor_id = 2;
select * from actor where actor_id = 1
union all
select * from actor where actor_id = 2;
explain select * from actor where acotr_id in (1,2);
explain select * from actor where actor_id = 1 or actor_id = 2;
or
如果是单列索引，or会使用索引
如果是组合索引
1.全部的列都是组合索引吗，那么会使用全部列所对应的索引
2.如果部分列是组合索引，那么不会走索引
范围列可以用到索引
范围条件是：<、<=、>、>=、between
范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围内
强制类型转换会全表扫描
explain select * from user where phone = 15503553255;
不会触发索引
explain select * from user where phone = '15503553255';
触发索引
更新十分频繁，数据区分度不高的字段上不宜建立索引
更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
类似性别这类区分不大的属性，建立索引时没有意义的，不能有效的过滤数据
一般区分度在80%以上的时候可以建立索引，区分度可以使用count(distinct(列名))/count(*)来计算
创建索引的列，不允许为null，可能会得到不符合预期的结果
当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致
能使用limit的时候尽量使用limit
单表索引建议控制在5个以内
现在没有太多的限制
单索引字段数不允许超过5个（组合索引）
创建索引的时候应该避免以下错误概念
索引越多越好
过早优化，在不了解系统的情况下进行优化
MySQL锁
锁的认识
锁的解释
计算机协调多个进程或线程并发访问某一资源的机制
锁的重要性
在数据库中，除传统计算资源（CPU、RAM、I\O等）的争抢，数据也是一种供多用户共享的资源。
如何保证数据并发访问的一致性，有效性，是所有数据库必须要解决的问题。
锁冲突也是影响数据库并发访问性能的一个重要因素，因此锁对数据库尤其重要。
如何保证数据并发访问的一致性，有效性，是所有数据库必须要解决的问题。
锁冲突也是影响数据库并发访问性能的一个重要因素，因此锁对数据库尤其重要。
锁的缺点
加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否已解除、释放锁等 ，都会增加系统的开销。
锁的类型
表锁
种类
读锁（read lock），也叫共享锁（shared lock）
针对同一份数据，多个读操作可以同时进行而不会互相影响（select）
写锁（write lock），也叫排他锁（exclusive lock）
当前操作没完成之前，会阻塞其它读和写操作（update、insert、delete）
存储引擎默认锁
MyISAM
特点
1. 对整张表加锁
2. 开销小
3. 加锁快
4. 无死锁
5. 锁粒度大，发生锁冲突概率大，并发性低
结论
1. 读锁会阻塞写操作，不会阻塞读操作
2. 写锁会阻塞读和写操作
建议
MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎，因为写锁以后，
其它线程不能做任何操作，大量的更新使查询很难得到锁，从而造成永远阻塞。
其它线程不能做任何操作，大量的更新使查询很难得到锁，从而造成永远阻塞。
行锁
种类
读锁（read lock），也叫共享锁（shared lock）
允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
写锁（write lock），也叫排他锁（exclusive lock）
允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁
意向共享锁（IS）
一个事务给一个数据行加共享锁时，必须先获得表的IS锁
意向排它锁（IX）
一个事务给一个数据行加排他锁时，必须先获得该表的IX锁
存储引擎默认锁
InnoDB
特点
1. 对一行数据加锁
2. 开销大
3. 加锁慢
4. 会出现死锁
5. 锁粒度小，发生锁冲突概率最低，并发性高
事务并发带来的问题
1. 更新丢失
解决：让事务变成串行操作，而不是并发的操作，即对每个事务开始---对读取记录加排他锁
2. 脏读
解决：隔离级别为Read uncommitted
3. 不可重读
解决：使用Next-Key Lock算法来避免
4. 幻读
解决：间隙锁（Gap Lock）
页锁
开销、加锁时间和锁粒度介于表锁和行锁之间，会出现死锁，并发处理能力一般（此锁不做多介绍）
如何上锁
表锁
隐式上锁（默认，自动加锁自动释放）
select //上读锁
insert、update、delete //上写锁
显式上锁（手动）
lock table tableName read;//读锁
lock table tableName write;//写锁
解锁（手动）
unlock tables;//所有锁表
案例一
InnoDB:给表teacher加上读锁时，加锁的会话更改数据报有读锁不让改；未加锁的会话可读，但是更改会被阻塞等待
案例二
InnoDB:手动加写锁后，加锁的会话可读可写；未加锁的会话读写都会阻塞
行锁
隐式上锁（默认，自动加锁自动释放）
select //不会上锁
insert、update、delete //上写锁
显式上锁（手动）
select * from tableName lock in share mode;//读锁
select * from tableName for update;//写锁
解锁（手动）
1. 提交事务（commit）
2. 回滚事务（rollback）
3. kill 阻塞进程
案例一
案例二
为什么上了写锁，别的事务还可以读操作？
因为InnoDB有MVCC机制（多版本并发控制），可以使用快照读，而不会被阻塞。
因为InnoDB有MVCC机制（多版本并发控制），可以使用快照读，而不会被阻塞。
行锁的实现算法
Record Lock 锁
单个行记录上的锁
Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定
Gap Lock 锁
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引加锁，对于键值在条件范围内但并不存在的记录。
优点：
解决了事务并发的幻读问题
不足：
因为query执行过程中通过范围查找的话，他会锁定争个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成锁定的时候无法插入锁定键值范围内任何数据。在某些场景下这可能会对性能造成很大的危害。
间隙锁有一个致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成锁定的时候无法插入锁定键值范围内任何数据。在某些场景下这可能会对性能造成很大的危害。
Next-key Lock 锁
同时锁住数据+间隙锁
在Repeatable Read隔离级别下，Next-key Lock 算法是默认的行记录锁定算法。
行锁的注意点
1. 只有通过索引条件检索数据时，InnoDB才会使用行级锁，否则会使用表级锁(索引失效，行锁变表锁)
2. 即使是访问不同行的记录，如果使用的是相同的索引键，会发生锁冲突
3. 如果数据表建有多个索引时，可以通过不同的索引锁定不同的行
如何排查锁
表锁
查看表锁情况
show open tables;
表锁分析
show status like 'table%';
1. table_locks_waited
出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次值加1），此值高说明存在着较严重的表级锁争用情况
2. table_locks_immediate
产生表级锁定次数，不是可以立即获取锁的查询次数，每立即获取锁加1
行锁
行锁分析
show status like 'innodb_row_lock%';
1. innodb_row_lock_current_waits //当前正在等待锁定的数量
2. innodb_row_lock_time //从系统启动到现在锁定总时间长度
3. innodb_row_lock_time_avg //每次等待所花平均时间
4. innodb_row_lock_time_max //从系统启动到现在等待最长的一次所花时间
5. innodb_row_lock_waits //系统启动后到现在总共等待的次数
information_schema 库
1. innodb_lock_waits表
2. innodb_locks表
3. innodb_trx表
优化建议
1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
2. 合理设计索引，尽量缩小锁的范围
3. 尽可能较少检索条件，避免间隙锁
4. 尽量控制事务大小，减少锁定资源量和时间长度
5. 尽可能低级别事务隔离
死锁
解释
指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象
产生的条件
1. 互斥条件：一个资源每次只能被一个进程使用
2. 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放
3. 不剥夺条件：进程已获得的资源，在没有使用完之前，不能强行剥夺
4. 循环等待条件：多个进程之间形成的一种互相循环等待的资源的关系
解决
1. 查看死锁：show engine innodb status \G
2. 自动检测机制，超时自动回滚代价较小的事务（innodb_lock_wait_timeout 默认50s）
3. 人为解决，kill阻塞进程（show processlist）
4. wait for graph 等待图（主动检测）
如何避免
1. 加锁顺序一致，尽可能一次性锁定所需的数据行
2. 尽量基于primary（主键）或unique key更新数据
3. 单次操作数据量不宜过多，涉及表尽量少
4. 减少表上索引，减少锁定资源
5. 尽量使用较低的隔离级别
6. 尽量使用相同条件访问数据，这样可以避免间隙锁对并发的插入影响
7. 精心设计索引，尽量使用索引访问数据
8. 借助相关工具：pt-deadlock-logger
乐观锁与悲观锁
区别
悲观锁
解释
假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
实现机制
表锁、行锁等
实现层面
数据库本身
适用场景
并发量大
乐观锁
解释
假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性
实现机制
提交更新时检查版本号或者时间戳是否符合
实现层面
业务代码
适用场景
并发量小
mysql面试
调优
索引
MVCC
存储引擎
事务
主从复制
读写分离
分库分表
锁
日志系统
规约
表的规约
表名
字段名
小数类型为 decimal，禁止使用 float和 double
如果存储的字符串长度几乎相等，使用 char定长字符串类型
varchar是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度
大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效
率
大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效
率
表必备三字段：id, create_time, update_time
主键名
主键索引名为pk_字段名
唯一索引名为 uk_字段名
普通索引名则为 idx_字段名
冗余字段
字段允许适当冗余，以提高查询性能，但必须考虑数据一致
注意
不是频繁修改的字段
不是唯一索引的字段
不是 varchar 超长字段，更不能是text 字段
单表行数超过 500万行或者单表容量超过 2GB，才推荐进行分库分表
不得使用外键与级联，一切外键概念必须在应用层解决
索引规约
业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引
超过三个表禁止 join。需要 join的字段，数据类型保持绝对一致；多表关联查询时，
保证被关联的字段需要有索引
保证被关联的字段需要有索引
在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据
实际文本区分度决定索引长度
实际文本区分度决定索引长度
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%
以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定
以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定
count(distinct left(列名, 索引长度))/count(*)
页面搜索严禁左模糊或者全模糊
需要请走搜索引擎来解决
order by的场景，请注意利用索引的有序性
利用延迟关联或者子查询优化超多分页场景
正例：先快速定位需要获取的 id 段，然后再关联：
SELECT t1.* FROM 表1 as t1, (select id from 表1 where 条件 LIMIT 100000,20 ) as t2 where t1.id=t2.id
SELECT t1.* FROM 表1 as t1, (select id from 表1 where 条件 LIMIT 100000,20 ) as t2 where t1.id=t2.id
至少要达到 range 级别，要求是 ref级别，如果可以是 consts
最好
最好
建组合索引的时候，区分度最高的在最左边
防止因字段类型不同造成的隐式转换，导致索引失效
SQL 语句
不要使用 count(列名)或count(常量)来替代 count(*)，count(*)是SQL92定义的标
准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关
准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
count(distinct col) 计算该列除NULL之外的不重复行数， 注意 count(distinct col1,
col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
当某一列的值全是 NULL 时，count(col)的返回结果为 0，但sum(col)的返回结果为
NULL，因此使用 sum()时需注意 NPE问题。
NULL，因此使用 sum()时需注意 NPE问题。
正例：可以使用如下方式来避免 sum 的 NPE 问题：SELECT IFNULL(SUM(column), 0) FROM table;
使用 ISNULL()来判断是否为 NULL值
in操作能避免则避免，若实在避免不了，需要仔细评估 in后边的集合元素数量，控
制在 1000个之内
制在 1000个之内
ORM映射
在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明
说明
1）增加查询分析器解析成本
2）增减字段容易与 resultMap 配置不一致
3）无用字段增加网络
消耗，尤其是 text 类型的字段
sql.xml配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现 SQL注入
不允许直接拿 HashMap与Hashtable作为查询结果集的输出
反例：某同学为避免写一个<resultMap>xxx</resultMap>，直接使用 HashTable 来接收数据库返回结
果，结果出现日常是把 bigint 转成 Long 值，而线上由于数据库版本不一样，解析成 BigInteger，导致线
上问题。
果，结果出现日常是把 bigint 转成 Long 值，而线上由于数据库版本不一样，解析成 BigInteger，导致线
上问题。
更新数据表记录时，必须同时更新记录对应的 update_time 字段值为当前时间
优化总结
表优化
优化表中字段数据类型
表拆分（水平、垂直）
垂直拆分
针对某些列常用、不常用
水平拆分
表很大
表中的数据有独立性，能简单分类
需要在表存放多种介质
反范式
增加冗余列、增加派生列、重新组表和分割表
使用中间表
数据查询量大
数据统计、分析场景
优化 mysql server
mysql 内存管理优化
MyISAM 内存优化
#修改相应服务器位置的配置文件 my.cnf
key_buffer_size
决定myisam索引块缓存区的大小，直接影响表的存取效率，建议1/4可用内存
read_buffer 读缓存
write_buffer 写缓存
key_buffer_size
决定myisam索引块缓存区的大小，直接影响表的存取效率，建议1/4可用内存
read_buffer 读缓存
write_buffer 写缓存
InnoDB 内存优化
innodb_buffer_pool_size 存储引擎表数据和索引数据的最大缓存区大小
innodb_old_blocks_pct LRU算法 决定old sublist的比例
innodb_old_blocks_time LRU算法 数据转移间隔时间
innodb_old_blocks_pct LRU算法 决定old sublist的比例
innodb_old_blocks_time LRU算法 数据转移间隔时间
log 机制及优化
调整 mysql 并发参数
max_connections 最大连接数，默认151
back_log 短时间内处理大量连接，可适当增大
table_open_cache 控制所有SQL执行线程可打开表缓存的数量，受其他参数制约
thread_cache_size 控制缓存客户服务线程数量，加快数据库连接速度，根据threads_created/connections来衡量是否合适
innodb_lock_wait_timeout 控制事务等待行锁时间，默认50ms
应用优化
为什么要做应用优化
数据的重要性
mysql 服务及自身性能瓶颈
保证大型系统稳定可靠运行
mysql 服务及自身性能瓶颈
保证大型系统稳定可靠运行
数据库连接池
使用缓存减少压力
负载均衡建立集群
主主同步、主从复制
索引优化
Mysql 中能使用索引的情况
匹配全值
匹配值范围查询
匹配最左前缀
仅仅对索引进行查询（覆盖查询）
匹配列前缀 （添加前缀索引）
部分精确+部分范围
不能使用索引的场景
以%开关的like查询
数据类型出现隐式转换
复合索引查询条件不包含最左部分
使用索引仍比全表扫描慢
用or分割开的条件
mysql 语句优化
定期优化表
optimize table table_name 合并表空间碎片，对MyISAM、BDB、INNODB有效
如果提示不支持，可以用 mysql --skip-new 或者 mysql --safe-mode 来重启，以便让其他引擎支持
如果提示不支持，可以用 mysql --skip-new 或者 mysql --safe-mode 来重启，以便让其他引擎支持
常用优化
尽量避免全表扫描，对where及orderby的列建立索引
尽量避免where使用 != 或 <>
尽量避免where子句用 or 连接条件
乱用%导致全表扫描
尽量避免where子句对字段进行表达式操作
尽量避免where子句对字段进行函数操作
覆盖查询，返回需要的字段
优化嵌套查询，关联查询优于子查询
组合索引或复合索引，最左索引原则
用exist代替in
当索引列有大量重复数据时，SQL查询可能不会去利用索引
尽量避免where使用 != 或 <>
尽量避免where子句用 or 连接条件
乱用%导致全表扫描
尽量避免where子句对字段进行表达式操作
尽量避免where子句对字段进行函数操作
覆盖查询，返回需要的字段
优化嵌套查询，关联查询优于子查询
组合索引或复合索引，最左索引原则
用exist代替in
当索引列有大量重复数据时，SQL查询可能不会去利用索引
Mysql 优化问题分析定位
分析 SQL 执行频率
show status
例如：分析读为主，还是写为主
例如：分析读为主，还是写为主
定位执行效率低的 SQl
慢查询日志定位
-log-slow-queries = xxx（指定文件名）
定位慢的sql
1.--查询变量
show variables like '%quer%';
2.-- 慢查询的数量
show status like '%slow_queries%';
3.-- 打开慢查询
set global slow_query_log = on;
4.-- 设置慢查询的时间阈值为:1s(设置完之后需要重新连接数据库，才能够生效)
set global long_query_time = 1;
show variables like '%quer%';
show variables like '%quer%';
分析慢的sql
分析 SQL 执行计划
explain "your sql"
EXPLAIN字段解析
1.id:SQL执行的顺序的标识,SQL从大到小的执行（重要）
1. id相同时，执行顺序由上至下
2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3.id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行.
2.select_type:查询中每个select子句的类型（重要）
1.SIMPLE(简单SELECT,不使用UNION或子查询等)
2.PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
3.UNION(UNION中的第二个或后面的SELECT语句)
4.DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
5.UNION RESULT(UNION的结果)
6.SUBQUERY(子查询中的第一个SELECT)
7.DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
8.DERIVED(派生表的SELECT, FROM子句的子查询)
9.UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
3.table:显示这一行的数据是关于哪张表的
4.partitions:表示sql中涉及的分区，如果表中没有分区，则为NULL
5.type：这是最重要的字段之一，显示查询使用了何种类型。（重要）
从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL，一般来说，得保证查询至少达到range级别，最好能达到ref。
type中包含的值：
system、const： 可以将查询的变量转为常量. 如id=1; id为 主键或唯一键
eq_ref： 访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或惟一键)
ref： 访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生
range： 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况(注:不一定好于index)
index： 以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描
ALL： 全表扫描，应该尽量避免
NULL 不用访问表或者索引，直接得到结果
6.possible_keys：显示可能应用在这张表中的索引。如果为空，表示没有可能应用的索引。
7.key：实际使用的索引。如果为NULL，则没有使用索引。
MySQL很少会选择优化不足的索引此时可以在SELECT语句中使用FORCE INDEX(index_name)来强制使用一个索引或者用IGNORE INDEX(index_name)来强制忽略索引
8.key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
9.ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
10.rows：MySQL认为必须检索的用来返回请求数据的行数
11.Extra：关于MySQL如何解析查询的额外信息，主要有以下几种（重要）
Extra中包含的值：
using index： 只用到索引,可以避免访问表，性能很高
using where： 使用到where来过滤数据， 不是所有的where clause都要显示using where. 如以下方式访问索引
using tmporary： 用到临时表去处理当前的查询
using filesort： 用到额外的排序，此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行。(当使用order by v1,而没用到索引时,就会使用额外的排序)
range checked for eache record(index map:N)： 没有好的索引可以使用
Using index for group-by：表明可以在索引中找到分组所需的所有数据，不需要查询实际的表
desc "your sql"
show profile 分析 SQL
select @@have_profiling 是否支持
select @@profiling 是否开启
执行 "your sql"
show profiles
show profile block io for QUERY 17
select @@profiling 是否开启
执行 "your sql"
show profiles
show profile block io for QUERY 17
SHOW PROCESSLIST
查看当前正在进行的线程，包括线程状态、是否锁表
查看当前正在进行的线程，包括线程状态、是否锁表
职业：本科
0 条评论
下一页
