数据库和SQL

本系列目录

什么是索引？MySQL单个索引最多包含多少列？

索引（Index）是帮助MySQL高效获取数据的数据结构。索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。跟查字典一样，通过目录直接找到章节
单个索引最多包含16列。

SQL执行计划

key_len的长度计算公式

varchr(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许NULL = 10 *( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)
explain之key_len计算

说下explain中type含义？性能由好到差怎么区分？

type:表示MySQL在表中找到所需行的方式，或者叫访问类型。常见访问类型如下，从上到下，性能由差到最好：

类型	说明
ALL	全表扫描
index	索引全扫描
range	索引范围扫描
ref	非唯一索引扫描
eq_ref	唯一索引扫描
const,system	单表最多有一个匹配行
NULL	不用扫描表或索引

在不同的 MySQL 引擎中，count(*) 有不同的实现方式

MyISAM 引擎：把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的。
InnoDB 引擎：它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

你知道哪些场景下索引会失效？

1. 使用不等于查询
2. NULL值
3. 列参与了数学运算或者函数
4. 在字符串like时左边是通配符.比如％xxx
5. 当mysql分析全表扫描比使用索引快的时候不使用索引.
6. 当使用联合索引，前面一个条件为范围查询，后面的即使符合最左前缀原则，也无法使用索引、
7. 有or必全有索引

数据库事务ACID

1. 原子性(Atomicity):事务中的所有操作作为一个整体像原子一样不可分割，要么全部成功,要么全部失败。
2. 一致性(Consistency):事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。

   一致性状态是指: a. 系统的状态满足数据的完整性约束。b. 系统的状态反应数据库本应描述的现实世界的真实状态,比如转账前后两个账户的金额总和应该保持不变。

3. 隔离性(Isolation):并发执行的事务不会相互影响,其对数据库的影响和它们串行执行时一样。比如多个用户同时往一个账户转账,最后账户的结果应该和他们按先后次序转账的结果一样。
4. 持久性(Durability):事务一旦提交,其对数据库的更新就是持久的。任何事务或系统故障都不会导致数据丢失。
   参考数据库事务的概念及其实现原理

数据库常见的并发异常

1. 脏读：指一个事务读取了另一个事务未提交的数据
2. 不可重复读：指一个事务对同一数据的读取结果前后不一致。脏读和不可重复读的区别在于:前者读取的是事务未提交的脏数据,后者读取的是事务已经提交的数据,只不过因为数据被其他事务修改过导致前后两次读取的结果不一样
3. 幻读：事务读取某个范围的数据时，因为其他事务的操作导致前后两次读取的结果不一致，针对数据新增删除。幻读和不可重复读的区别在于,不可重复读是针对确定的某一行数据而言,而幻读是针对不确定的多行数据。因而幻读通常出现在带有查询条件的范围查询中
4. 第一类丢失更新（回滚丢失）：指事务回滚了其他事务对数据项的已提交修改
5. 第二类丢失更新（提交覆盖丢失）：指事务覆盖了其他事务对数据的已提交修改,导致这些修改好像丢失了一样

参考数据库事务的概念及其实现原理

SQL标准为事务定义了不同的隔离级别,从低到高依次是

1. 读未提交(READ UNCOMMITTED)
2. 读已提交(READ COMMITTED)
3. 可重复读(REPEATABLE READ)（行锁）
4. 串行化(SERIALIZABLE)（表锁）
   

大多数数据库隔离级别为READ COMMITED，ruSql Server、Oracle，MySql为REPEATABLE READ

谈谈对数据库索引的理解

数据库索引设计的初衷是可以通过索引快速查找表中数据。索引是建立某种数据结构和表中数据一种关系，这种数据结构必须能够快速查找到目标值，然后通过这种关系定位到所需的数据行。索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。以MySQL的MyISAM为例，每个表对应的数据库文件有三个，一个保存表信息，一个保存索引信息，一个保存数据。保存索引的数据结构是B+Tree，如果根据某个字段获取数据MySQL首先判断该字段是否建立了索引，如果有索引，就先通过在B+Tree上快速查找目标值，如果找到目标值，则会通过该索引对应的物理地址定位到数据文件中的数据，获得查询结果。

谈谈聚集索引和非聚集索引

聚集索引就是将索引和数据存在一个文件中，查找数据时找到索引值后直接能获取到数据。非聚集索引是将索引和数据分开储存，索引文件储存的时索引值和对应数据的物理地址，找到索引后还需要根据物理地址找到对应数据。对比聚集索引比非聚集索引效率要高。

SQL优化

1. 首先应考虑在 where 及 order by 涉及的列上建立索引
2. 避免放弃索引全表扫描。例如以下情况：

   ① where 子句中对字段进行 null 值判断。解决办法把null值用字符代替，比如0，查询：where num=0
   ② where 子句中使用!=或<>操作符
   ③ where 子句中使用 or 来连接条件。如：select id from t where num=10 or num=20，可以这样查询：select id from t where num=10 union all select id from t where num=20
   ④ where 子句使用in或者not in。如：select id from t where num in(1,2,3)，对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3
   ⑤ where 子句使用like前模糊查询，如where name like ‘%abc%’或者where name like ‘%abc’
   ⑥ where 子句中的“=”左边进行函数、算术运算或其他表达式运算。如：a. select id from t where substring(name,1,3)=‘abc’–name以abc开头的id，应改为:select id from t where name like ‘abc%’
   b. select id from t where num/2=100，应改为:select id from t where num=100*2
   ⑦ 在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算。如：select id from t where substring(name,1,3)=‘abc’–name以abc开头的id，应改为:select id from t where name like ‘abc%’

3. 很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b),用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)
4. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
5. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，
   因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
   一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
6. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
   这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
7. 固定程度字符使用char可变长度字符使用varchar，char使用空间换时间，所以varchar存取速度较之低
8. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，
   以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
9. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
10. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
11. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理