SQL&SQLite
目录
高难度SQL查询
自联接
SELECT prod_id, prod_name FROM products WHERE vend_id = ( SELECT vend_id FROM products WHERE prod_id = 'DTNTR')
关联子查询
- 定义
- 引用外部的一列或多列
- 将外部查询的每一行都传递给子查询，子查询依次读取传递过来的每一行的值，并将其使用到子查询上，直到外部查询的所有行都处理完为止，然后返回子查询的结果
- 按课程号分组取成绩最大值所在行的数据，我们可以使用分组（group by）和汇总函数得到每个组里的一个值（最大值，最小值，平均值等）。但是无法得到成绩最大值所在行的数据。sql语句如下，具体执行逻辑查看关联子查询
select *
from score as a
where 成绩 = (
select max(成绩)
from score as b
where b.课程号 = a.课程号);
// 虽然表中有重复的课程号，但不会重复计算，sql已经对此进行了优化，详见上面的网页链接。
等价于select score.x, score.y(所有列依次列出来) from score AS a,
(select 课程号, max(成绩) AS maxScore from score ) AS b where b.课程号=a.课程号 AND a.socre = b.maxScore
每组最大的N条记录
Select * From StudentGrade A
Where (Select Count(*) From StudentGrade Where stuid= A.stuid And grade> A.grade) < 2
Order By stuid, grade
联结（多表查询）
# 查询所有学生的学号、姓名、选课数、总成绩
selecta.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号;
# 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select a.学号,a.姓名, avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号
having avg(b.成绩)>85;
# 查询学生的选课情况：学号，姓名，课程号，课程名称
select a.学号, a.姓名, c.课程号,c.课程名称
from student a inner join score b on a.学号=b.学号
inner join course c on b.课程号=c.课程号;
# 查询出每门课程的及格人数和不及格人数
-- 考察case表达式
select 课程号,
sum(case when 成绩>=60 then 1
else 0
end) as 及格人数,
sum(case when 成绩 < 60 then 1
else 0
end) as 不及格人数
from score
group by 课程号;
关系型数据库基础知识
- 关系型数据库就是以表（可以理解为excel中的表）的集合来表示数据和数据间的关系
- 表的每一列称为属性，每一行称为一个元组
- sql就是实现数据库创建、删除、提取表和内容等的语言
- 来自应用程序的数据库访问：SQL功能有限，SQL不支持从用户那儿输入、输出到显示器，或者通过网络通信这样的动作。这样的计算和动作必须用一种宿主语言来写，比如C、C++、Java，在其中使用嵌入式的SQL查询来访问数据库中的数据。
SCHEMA设计
设计范式与反范
- 第一范式：每一列属性都是不可再分的属性值，确保每一列的原子性；两列的属性相近或相似或一样，尽量合并属性一样的列。
- 第二范式：每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。
- 第三范式：数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系。
- 反范式化就是所有数据都在一张表中
- 范式化设计的schema的缺点：通常需要关联（主要由第二范式决定），稍微复杂一点的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多，这不仅代价昂贵，也可能使一些索引策略失效。
- 范式化设计的优点：范式化的表通常更小，可以更好地放在内存里，所以执行操作更快；当数据较好范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据；
数据类型优化
- 更小的通常更好
- 简单就好，使用内建类型而不是字符串来存储日期
- 尽量避免NULL
- 使用枚举代替字符串
- 尽量使用TIMESTAMP, 而非DATATIME, 前者更有效率且提供的值跟时区有关，而后者仅仅保留文本表示的日期与时间
创建高性能索引
- 只能高效使用索引的最左前缀列，或者当最左前缀列固定时的第二左前缀列
- 当被索引的数据很长时，如一个url，这是可以将url映射为一个crc32校验值并且新增列url_crc来存储这个值，然后查询时这样来写：SELECT id FROM urlTable where url = “http://www.syl.com” AND url_crc = url_crc32(“http://www.syl.com”)
- 索引不能是表达式的部分，如select actorId from actor where actorId + 1 = 5; // 这里应该直接写成actorId = 4
- 前缀索引（只拿前一部分作为索引），缺点是无法使用GROUP BY和ORDER BY， 并且无法使用覆盖查询
- 多列索引 略
- 聚簇索引或者聚集索引，不是一种索引而是一种数据存储方式。 这也导致了通过非聚集索引查找数据时，要先在非聚集索引里找到主键，然后通过主键在聚集索引里找数据(总IO次数等于两次相加)。利用这个特性，可以通过非聚集索引同时查找被索引的列和主键列。即select primaryKey, index from tableName where index = blalba;
- 覆盖索引：如果一个索引包含了要查找的值，我们就称之为覆盖索引，用EXPALAIN,可以看到Extra列看到Using Index字样， 可以理解为用了这个索引来查找数据。
- 如何设计索引？
- 查询用的多的，创建索引，范围如age，放后面，而sex，放前面，使用是SEX = ‘F’ 或 SEX IN(‘F’ , ‘M’)
- 其它
- 只有Memory引擎 显式 支持哈希索引
- InnoDB中哈希索引是自适应的，由引擎觉得有必要时（某些索引值用的非常频繁时）才会生成
- 全文索引：不懂
查询性能优化
- 当不能使用索引排序时，mysql需要自己排序（filesort），排序成本高，尽可能避免
- UNION的限制推到里层
- 分解关联查询，在应用中再组合
- 优化LIMIT
- LIMIT 10000, 20 需要查询10020条记录，然后返回最后20条，之前10000都被抛弃
- 优化方法为尽可能使用索引覆盖扫描，然后根据需要做一次关联操作再返回所需的列。
- 示例
SELECT film_id , desp FROM sakila.film ORDER BY title LIMIT 50,5;改成这样SELECT film_id , desp FROM sakila.film INNER JOIN ( select film_id from sakila.film ORDER BY title LIMIT 50,5)AS lim USING(film_id);
SQL语法
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否
新建、删除、选择数据库
- SQLITE
- 创建数据库，命令行下是
$sqlite3 DatabaseName.db就可以了，实际编程中，则调用sqlite3_open(const char *zFilename, sqlite3 **ppDb )就可以了。感觉其实就是一个文件啦 - 附加数据库，假设这样一种情况，当在同一时间有多个数据库可用，您想使用其中的任何一个。SQLite 的 ATTACH DATABASE 语句是用来选择一个特定的数据库。
ATTACH DATABASE file_name AS database_name; ATTACH DATABASE 'testDB.db' as 'TEST'; - 分离数据库, DETACH DATABASE ‘Alias-Name’;
- 创建数据库，命令行下是
- MySql
- 创建数据库
CREATE DATABASE 数据库名; - 删除数据库则为
drop database <数据库名>; - 选择数据库为 use <数据库名>
- 创建数据库
创建表
- SQLITE
示例一
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL UNIQUE,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL CHECK(SALARY > 0),
COLOR VARCHAR(16)
);
示例二
CREATE TABLE COMPANY(
ID INT NOT NULL,
NAME TEXT NOT NULL UNIQUE,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL CHECK(SALARY > 0),
COLOR VARCHAR(16),
PRIMARY KEY ( ID, NAME)
);
1. 在这里，NAME列设置为 UNIQUE，所以不能有两个相同名字的记录
2. CHECK 约束启用输入一条记录要检查值的条件。如果条件值为 false，则记录违反了约束，且不能输入到表，在这里，我们为 SALARY 列添加 CHECK，所以工资必须大于零
3. 只能有一个主键（可以不指定，但一般推荐指定），主键是表中的一个字段，唯一标识数据库表中的各行/记录。主键必须包含唯一值。主键列不能有 NULL 值。一个表只能有一个主键，它可以由一个或多个字段组成。当多个字段作为主键，它们被称为复合键（见示例二）。如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值
sqlite> CREATE TABLE COMPANY(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
NAME TEXT NOT NULL,
AGE INT NOT NULL DEFAULT 18,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 50000.00
);
SQLite 的 AUTOINCREMENT 是一个关键字，用于表中的字段值自动递增。我们可以在创建表时在特定的列名称上使用 AUTOINCREMENT 关键字实现该字段值的自动增加。
关键字 AUTOINCREMENT 只能用于整型（INTEGER）字段
SQLITE还有BLOB 值是一个 blob 数据，完全根据它的输入存储。
SQLITE中没有专门的时间类型，但可以通过内置函数得到
- MySql
- MySql支持的数据类型差不多，无非INT、FLOAT、DOUBLE、CHAR、VARCHAR、TEXT、BLOB
- MySql还有专门的时间类型，包括DATA、TIME、YEAR、TIMESTAMP、DATATIME
- 示例
CREATE TABLE runoob_tbl(
runoob_id INT NOT NULL AUTO_INCREMENT,
runoob_title VARCHAR(100) NOT NULL,
runoob_author VARCHAR(40) NOT NULL,
submission_date DATE,
PRIMARY KEY ( runoob_id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP 删除表
- **MySql: **
DROP TABLE table_name; - **Sqlite: **
DROP TABLE database_name.table_name;
INSERT
- 插入部分行 (mySql 和 sqlite均支持)
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN);INSERT INTO runoob_tbl (title, author, date) VALUES ("学习 PHP", "菜鸟教程", NOW()); - 插入完整行 (mySql 和 sqlite均支持)
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN); 添加所有列 - 插入检索行 （sqlite）
const char *SQL_COMMAND_DATATRANSFER = "INSERT OR IGNORE INTO dav.vehicleinfo SELECT * FROM vehicleinfo WHERE id IN (SELECT id FROM StorDiskMap WHERE diskName = 'dav');
从一个表复制到另外一个表
CREATE TABLE CustCopy AS SELECT * FROM Customer
SELECT所有语法（查询所有、其中几列、）
select avg(salary) from COMPANY- 嵌套子查询 , P51, exist 确定子查询是否存在一个元祖， unique是否唯一元祖
select id form courseTable where unqiue（select id form section where id = 100 and year = 2009）SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ;- 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较
- 多关系查询（两个表中查询），natural join， P40
- from子句中也可以创建查询语句
select id from(select id from tablename) where id > 500;
- 标量子查询
- 拼接字段
SELECT vend_name || ' (' || vend_country || ')' FROM Vendors ORDER BY vend_name;输出结果为Bear Emporiu(USA)
-
执行算术计算
```sql SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008; ``` SELECT order_num, COUNT(*) AS items FROM OrderItems WHERE prod_price >= 4 GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;
UPDATE
UPDATE COMPANY SET NAME = 'Test1' WHERE NAME = 'Test'UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 ); 把 COMPANY 表中所有 AGE 大于或等于 27 的客户的 SALARY 更新为原来的 0.50 倍- 与ALTER的区别是ALTER是修改表的（包括增减属性，增加列，修改表名等）
删除DELETE
- 只能删除整个元祖
删除所有记录 DELETE FROM COMPANY;DELETE FROM table_name WHERE [condition];DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 ); 删除 COMPANY 表中所有 AGE 大于或等于 27 的客户记录
运算符
- 比较运算符和C一样，但增加了几种有同样作用的运算符
- 算术运算符和C一样，位运算没有异或
- 逻辑运算符（重点），
- MySql只有LIKE
- LIKE 和 GLOB模式语法，
%匹配任意字符串，_匹配单一字符, like大小写敏感， - GLOB大小写敏感,
*匹配任意字符串，?匹配单一字符 - 用于组装语句，注意，sql没有&&和|| ，取而代之的是 AND 和 OR等, SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。
- 字符串用单引号，如果单引号是字符串的组成部分，那就用两个单引号来表示
GROUP BY 和 Having
- 用于对一组元组集进行操作。
- 获取进出最频繁的车辆的车牌
CREATE VIEW PLATE_TIMES AS SELECT PLATE_NUM , COUNT(*) AS TIME FROM COMPANY GROUP BY PLATE_NUM ORDER BY TIME DESC LIMIT 1;- 先建立一个视图
CREATE VIEW PLATE_TIMES AS SELECT PLATE_NUM , COUNT(*) AS TIME FROM COMPANY GROUP BY PLATE_NUM ORDER BY TIME DESC; - 然后从排好序的表里，显示第一个的车牌，就是最频繁的车牌
SELECT PLATE_NUM from PLATE_TIMES LIMIT 1
select departName , avg(salary) from departTable group by departName这里会得到每一组的平均薪资。- 示例
select departName , avg(salary) from departTable group by departName select departName , ID, avg(salary) from departTable group by departName是错误的，因为ID必须出现在groupby中，或者ID由聚集函数包裹，像salary那样，见书P49- Group By 是对Where的结果起作用的，Having子句对Group By形成后的每个分组起作用， 同样的，Having中的没有被聚集的属性必须出现在group by 子句中
select dept_mname, avg(salary) as avg_salary from instructor group by dept_name having avg(salary) > 42000
其它
LIMIT限制数量ORDERORDER BY name DESCORDER BY name ASCSELECT id ,salary FROM company order by id DESC, salary ASC;
DISTINCT关键字用于消除重复记录(一模一样的) , 示例SELECT DISTINCT name FROM COMPANY- 约束
- DEFAULT 约束在 INSERT INTO 语句没有提供一个特定的值时，为列提供一个默认值
- NOT NULL , UNIQUE , PRIMARY KEY , CHECK
- ALTER 改变表的名字或在已有的表中添加列,
ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);
高级
- PRAGMA
- journal_mode
- page size
连接 JOIN
- 连接的目的，是将多个表根据相同属性补充成一个更大的表， 比如表A ,B，表A中两列1122 TOM, 表B中1122 SALARY, 表A，B第一列属性都是ID， 则连接后就是 1122 TOM SALARY 变成了三列。
select name, course_id from instructor, teachers将产生笛卡尔积结果，即所有可能结果都输出
select name, course_id from instructor, teachers where instrutor.ID = teachers.ID, 则只会将两者相等的输出， 等价于select name, course_id from instructor natural join teachers, 即natural join 只输出多个表相同部分组合得到的新表，- natutal join 根据属性来，如果有两个属性名字相同，则必须两个都相同的才会组进来， 有时候可能只需要其中某个属性相同就可以组，有必要允许用户指定哪些属性相等(有多个属性相同情况下，只用几个或者只用哪几个)，语法为
select name, title from (instructor natural join teaches) join course course using (course_id); join .. on可以指定任意的连接条件，select * from student join takes on student.ID = takes.ID;
- natutal join 根据属性来，如果有两个属性名字相同，则必须两个都相同的才会组进来， 有时候可能只需要其中某个属性相同就可以组，有必要允许用户指定哪些属性相等(有多个属性相同情况下，只用几个或者只用哪几个)，语法为
- 外连接
- [SQL 连接（内连接，外连接)]
- 假定我们要显示一个学生的列表，显示他们的ID, name（在表student中）， 以及他们选修的课程（在表takes中）,这两个表有相同的叫ID的熟性, 使用
select * from students natural join takes得到的结果与想要的是不同的。假定有一些学生，他们没有选修任何课程，那么这些学生在students关系中所对应的元祖与takes关系中的任何元祖配对，都不会满足自然连接的条件，从而这些学生的数据就不会出现在结果中。为此需要外连接，没有选修课程的学生对应的课程为空，但会在结果中显示出来。 - 左外连接，只保留出现在左外连接运算之前（左边）的关系中的元祖， 右外连接时右边，全外连接(full outer join)时都保留。示例
select * from students LEFT OUTER JOIN takes -
SELECT vehicleInfo.*, cardInfo.*, payRule.ruleName FROM vehicleInfo LEFT JOIN cardInfo ON vehicleinfo.cardNum=cardInfo.cardNum LEFT JOIN payRule ON cardInfo.payRuleId=payRule.payRuleId WHERE vehicleInfo.vehicleId=%d AND cardInfo.cardId=%d;
UNION
- SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; - UNION ALL 运算符用于结合两个 SELECT 语句的结果，包括重复行
事务
- 每条SQL语句其实都是一个事务，单条的是隐式的事务，声明BEGIN和COMMIT的是显式的事务, ROLLBACK 事务回滚
- ACID
- 原子性（Atomicity）：确保工作单位内的所有操作都成功完成，否则，事务会在出现故障时终止，之前的操作也会回滚到以前的状态。
- 一致性（Consistency)：确保数据库在成功提交的事务上正确地改变状态。
- 隔离性（Isolation）：使事务操作相互独立和透明。事务之间互不影响，假定有两个事务A和B, 则在A看来，B或者在A之前执行完，或者在A之后再执行。
- 持久性（Durability）：确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。
- 没有COMMIT情况下，才能ROLLBACK, 一旦COMMIT就不能ROLLBACK
-
现在，让我们开始一个事务，并从表中删除 age = 25 的记录，最后，我们使用 ROLLBACK 命令撤消所有的更改 sqlite> BEGIN; sqlite> DELETE FROM COMPANY WHERE AGE = 25; sqlite> ROLLBACK; 检查 COMPANY 表，仍然有之前的记录- 加锁，饿死， P319
视图
- 视图（View）只不过是通过相关的名称存储在数据库中的一个 SQLite 语句。视图（View）实际上是一个以预定义的 SQLite 查询形式存在的表的组合。
- 为什么要使用试图？ 重用SQL语句。简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
- 当视图关系被访问时，其中的元祖通过计算查询结果而被计算出来。视图关系是在需要的时候才被创建
- SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句， 一般视图库的视图都是可读的。
-
CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition]; create view departments_total_salary(dept_name, total_salary) as select dept_name , sum(salary) from instructor group by dept_name
存储过程
- Mysql中才有，类似于函数或方法
NULL , IS NOT NULL 运算符
SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NOT NULL;
别名
sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
FROM COMPANY AS C, DEPARTMENT AS D
WHERE C.ID = D.EMP_ID;
表别名与列别名 ， C和D是表别名， COMPANY_ID 和 COMPANY_NAME是列别名
触发器
- 触发器（Trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用
-
CREATE TRIGGER audit_log AFTER INSERT ON COMPANY BEGIN INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now')); END; 现在，我们将开始在 COMPANY 表中插入记录，这将导致在 AUDIT 表中创建一个审计日志记录。因此，让我们在 COMPANY 表中创建一个记录，如下所示： sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); 这将在 COMPANY 表中创建如下一个记录： ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0
索引
CREATE INDEX flag1Idx ON vehicleinfo(sendFlag1)- 数据库中的索引和书本的索引一样，不然只能一页一页对比查找
- 顺序索引
- 稠密索引、稀疏索引、多级索引（减少查找索引的烦恼）
- B + B^+ B+树索引
- 复杂度
O(log n)- 顺序索引和B+树索引将文件组织成顺序文件， 或者用散列来组织文件
- HASH/散列 索引
- 如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
- 如果是范围，那就使用顺序索引或B+树更好，因为如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
- 同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）
- 在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题（如果不同的输入得到了同一个哈希值，就发生了"哈希碰撞"）
- **索引归纳 **
- 索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。
- 什么情况下要避免使用索引？
- 索引不应该使用在较小的表上，对小表进行索引可能不能产生优化效果，因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长，设计索引时需要考虑表的大小。记录数不大于100的表不要建立索引。频繁操作的小数量表不建议建立索引（记录数不大于5000条）。
- 索引不应该使用在有频繁的大批量的更新或插入操作的表上。表中如果建有大量索引将会影响INSERT、UPDATE和DELETE语句的性能，因为在表中的数据更改时，所有的索引都将必须进行适当的调整。需要避免对经常更新的表进行过多的索引，并且索引应保持较窄，就是说：列要尽可能的少。
- 当一个索引有多个列构成时，应注意将选择性强的列放在前面。仅仅前后次序的不同，性能上就可能出现数量级的差异。
- 索引不应该使用在含有大量的 NULL 值的列上，对于内容基本重复的列，比如只有1和0，禁止建立索引，因为该索引选择性极差，在特定的情况下会误导优化器做出错误的选择，导致查询速度极大下降。
- 为经常用于查询的谓词创建索引，如用于下拉参照快速查找的code、name等。在平台现有下拉参照的查询sql语句中的like条件语句要改成不带前置通配符。还有需要关注Order By和Group By谓词的索引设计，Order By和Group By的谓词是需要排序的，某些情况下为Order By和Group By的谓词建立索引，会避免查询时的排序动作。
- 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处
- SQLite索引
- Sqlite中数据存储方式表(table)和索引(Index)都是带顺序访问指针的B+树。table对应的B+树中，key是rowid，data是这一行其他列数据（sqlite为每一行分配了一个rowid）。index对应的B+树种，key是需要索引的列，data是rowid
- 数据库中主键与索引的区别
- 主键是为了标识数据库记录唯一性,不允许记录重复,且键值不能为空,主键也是一个特殊索引.
- 数据表中只允许有一个主键,但是可以有多个索引.
- 使用主键会数据库会自动创建主索引,也可以在非主键上创建索引,方便查询效率.
- 索引可以提高查询速度,它就相当于字典的目录,可以通过它很快查询到想要的结果,而不需要进行全表扫描.
- 主键索引除外的其它索引的值可以为空.
- 主键也可以由多个字段组成,组成复合主键,同时主键肯定也是唯一索引.
- 唯一索引则表示该索引值唯一,可以由一个或几个字段组成,一个表可以有多个唯一索引.
MySql
- mysql -u root -p
全文本搜索
- MyISAM才包含全文本搜索，INNODB不包含该功能
- 明确控制——使用通配符和正则表达式匹配，很难（而且并不总是能）明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配。
- 智能化的结果——虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能是更好的匹配来排列它们）。类似，一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。
SQLite深入
基本架构
-
SQLite的基本架构如下图所示：
-
SQLite works by compiling SQL text into bytecode, then running that bytecode using a virtual machine. The
sqlite3_prepare_v2()and related interfaces act as a compiler for converting SQL text into bytecode. Thesqlite3_stmtobject is a container for a single bytecode program using to implement a single SQL statement. Thesqlite3_step()interface passes a bytecode program into the virtual machine, and runs the program until it either completes, or forms a row of result to be returned, or hits a fatal error, or is interrupted.
SQLite内存分配
一些基本概念
- 连接数据库指的就是
rc = sqlite3_open("test.db", &db);
附加的含义
- 附加的意思，就是通过将另一个数据库附加到当前数据库，使得当前的数据库可以访问另一个数据库，5012—D中用到了文件数据库和内存数据库，为了避免频繁读写 文件数据库带来的性能损耗，采用操作sqlite内存数据库，并将内存数据库定时同步到文件数据库中的方法。
注意事项
- 更改数据库注意事项
- 为了保证向后的兼容性，数据库中只能增加表或者字段，不允许删除表或字段
- 对数据库进行修改，必须修改版本号，且增加根据版本号打补丁程序
- 因为sqlite是轻型数据库，不能进行频繁大量的select、insert、update、delete等操作，每次操作都要进行慎重，将sql语句优化至最小。
- sqlite3只支持每秒几十次的事务，所以为了提高插入的效率，需要将一组SQL命令放在事务里，这样多条语句都通过一次事务完成，而不是每条语句都是一次默认的autocommit.这样可能达到每秒万次数量级的插入操作。
SQLite是否线程安全
- Is SQLite threadsafe?
- Sqlite是线程安全的，需要提前开启这个功能， 但操作数据库可能返回失败，因为对应的锁没有获取到，导致返回SQLITE_BUSY。也可以编译成单线程
- SQLite supports three different threading modes:
- Single-thread. In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once. 单线程：禁用所有的mutex锁，并发使用时会出错，只能在一个线程中使用。当SQLite编译时加了SQLITE_THREADSAFE=0参数，或者在初始化SQLite前调用sqlite3_config(SQLITE_CONFIG_SINGLETHREAD)时启用。
- Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.多线程：只要一个数据库连接不被多个线程同时使用就是安全的。源码中是启用bCoreMutex，禁用bFullMutex。实际上就是禁用数据库连接和prepared statement（准备好的语句）上的锁，因此不能在多个线程中并发使用同一个数据库连接或prepared statement。当SQLite编译时加了SQLITE_THREADSAFE=2参数时默认启用。若SQLITE_THREADSAFE不为0，可以在初始化SQLite前，调用sqlite3_config(SQLITE_CONFIG_MULTITHREAD)启用；或者在创建数据库连接时，设置SQLITE_OPEN_NOMUTEX flag。
- Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction. 串行：启用所有的锁，包括bCoreMutex和bFullMutex。因为数据库连接和prepared statement都已加锁，所以多线程使用这些对象时没法并发，也就变成串行了。当SQLite编译时加了SQLITE_THREADSAFE=1参数时默认启用。若SQLITE_THREADSAFE不为0，可以在初始化SQLite前，调用sqlite3_config(SQLITE_CONFIG_SERIALIZED)启用；或者在创建数据库连接时，设置SQLITE_OPEN_FULLMUTEX flag， 5012-D中选择了该模式，并且还加了锁，其实锁完全没有必要加。
- SQLITE要想保证线程安全的话，可以有这4种方式, 经测试最好的是第二种加多线程，参考自SQLite在多线程环境下的应用：
- SQLite使用单线程模式，用一个专门的线程访问数据库。
- SQLite使用单线程模式，用一个线程队列来访问数据库，队列一次只允许一个线程执行，队列里的线程共用一个数据库连接。
- SQLite使用多线程模式，每个线程创建自己的数据库连接。
- SQLite使用串行模式，所有线程共用全局的数据库连接。
WAL日志
Write-Ahead Logging https://www.sqlite.org/wal.html
- 特性
- WAL使用共享内存技术，因此所有读写进程必须在同一个机器上。开启WAL模式的方法：
sqlite3_exec(db, "PRAGMA journal_mode=WAL; ", 0,0,0);
- WAL使用共享内存技术，因此所有读写进程必须在同一个机器上。开启WAL模式的方法：
- WAL日志主要有以下优点：
- 读写可以完全并发进行，不会互相阻塞（但是写之间仍然不能并发）。回滚日志下，读写存在阻塞，因为互斥，但读写是可以正常进行的，即不需要额外的同步手段。但写与写仍旧不能并发
- WAL在大多情况下，拥有更好的性能（因为无需每次写入时都要写两个文件）
- 缺点：
- 由于使用了共享内存，只能在单台主机上运行，不能在网络系统中运行（不然不能使用共享内存））
Rollback journal回滚日志和WAL比较
- 在修改数据库文件中的数据前，先将修改所在分页中的数据备份在另一个地方，然后再将修改写入到数据中；如果事务失败，则将备份数据拷贝回来，撤销修改；如果事务成功，则删除备份，提交修改。WAL机制原理：修改并不直接写入到数据库文件中，而是写入到另外一个称为WAL的文件中，如果事务失败，wal中的文件会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中(即checkpoint操作)，提交修改。
- 性能差异主要源于每次事务提交，回滚日志模式首先要将原始数据页拷贝到日志文件中，并进行fsync，然后将修改页写入磁盘，同时也需要fsync，确保数据落盘，并且还要清除日志文件。wal模式下，和回滚日志一样，在开始一个写事务之前，首先要有一个读事务将要修改的页读到内存中。读数据时，最新修改的还没同步到数据库的页从WAL日志读取，其他的页在数据库中读取。然后将修改后的页写入日志中就可以了。无需每次写入时都要写两个文件。
- 回滚日志中包含两种操作，即读和写，而WAL包含三种操作：读、写、Checkpoint
WAL的读、写、回填操作：
- Reader: 当事务要读取数据库内容时，先在WAL文件中找找有没有要读的那一页的最新版本，有的话就从WAL中读取。没有的话，就从数据库中读取。由于写事务只是在WAL文件之后追加一些内容，而读事务只是在WAL文件中查找一些内容。所以，读事务在开始的时候，记录WAL文件的最后一个有效帧（read mark），并在整个事务中忽略之后添加的新内容。这样可以做到读事务和写事务同时进行，提高了速度。由于每个读事务都需要搜索整个WAL文件导致效率低下，在共享内存中建立了wal-index文件。每个读事务都利用wal-index来进行页面的快速查找，避免了对整个WAL文件的搜索。但是由于使用了共享内存，所以所有的数据库连接都必须在同一台机器上（不然不能使用共享内存）.
- Writers: 写数据库只是把新内容加到WAL文件的末尾，和读操作没有关系。由于只有一个WAL文件，因此同时只能有一个写操作。
- Checkpoint:
- WAL模式下，读操作的性能有所下降，因为需要从WAL文件中查找内容，花费的时间和WAL文件的大小有关。wal-index可以缩短这个时间，但是也不能完全避免。因此需要保证WAL文件的不会太大。程序会定期将WAL文件中的页面写入数据库，这个操作叫做Checkpoint,也叫backfill回填。
- 默认策略是很多线程可以增长WAL文件。把WAL文件大小变得比1000page大的那个线程要负责进行checkpoint。会导致绝大部分读写操作都是很快的，随机有一个写操作非常慢。也可以禁用自动checkpoint的策略，定期在一个线程或进程中进行checkpoint操作。
- WAL定期将WAL的页面写入数据库的一个好处就是，假如WAL文件中包含某一页的多个副本，只需要将最新的那个版本写入数据库就行了。另一个好处是：这样可以按照页号从小到大写入数据库，按顺序访问磁盘，加快了速度。为此程序中设立了一个迭代器，按页号从小到大返回每一页的最新版本的所在的帧号。写事务在开始的时候，如果发现WAL全部被回填完毕了，就会从第一帧开始写入数据，而不是追加帧。
- 回填的时候不会超过任何一个读者的read mark, 即并发的读操作之后的修改不会回填。
WAL为什么要用共享内存？
- 因为wal-indx需要让所有的进程都能看到。并且更改要让大家实时看到，那共享内存是最快最好的方式（如果都从磁盘读取，那修改后还要通知其它进程，其它进程知道后还要重新读取一次磁盘）
- 映射可以是mmap匿名共享内存、POSIX共享内存，这两种方式实现本质上其实都是tmpfs。Linux提供一种“临时”文件系统叫做tmpfs，它可以将内存的一部分空间拿来当做文件系统使用，使内存空间可以当做目录文件来用。
- 上述方法的优点是，由于在内存中，可以避免磁盘IO。但由于不能跨系统，上述方法不能使用。
- 为此只能是在磁盘中创建一个文件，然后各自进程的将其映射到内存空间
注意事项
- 如何避免checkpoint饥饿导致太大的WAL文件 So in the vast majority of cases, applications need not worry about the WAL file at all. SQLite will automatically take care of it. But it is possible to get SQLite into a state where the WAL file will grow without bound, causing excess disk space usage and slow queries speeds. The following bullets enumerate some of the ways that this can happen and how to avoid them.
- Disabling the automatic checkpoint mechanism. In its default configuration, SQLite will checkpoint the WAL file at the conclusion of any transaction when the WAL file is more than 1000 pages long. However, compile-time and run-time options exist that can disable or defer this automatic checkpoint. If an application disables the automatic checkpoint, then there is nothing to prevent the WAL file from growing excessively.
- Checkpoint starvation. A checkpoint is only able to run to completion, and reset the WAL file, if there are no other database connections using the WAL file. If another connection has a read transaction open, then the checkpoint cannot reset the WAL file because doing so might delete content out from under the reader. The checkpoint will do as much work as it can without upsetting the reader, but it cannot run to completion. The checkpoint will start up again where it left off after the next write transaction. This repeats until some checkpoint is able to complete.However, if a database has many concurrent overlapping readers and there is always at least one active reader, then no checkpoints will be able to complete and hence the WAL file will grow without bound. This scenario can be avoided by ensuring that there are “reader gaps”: times when no processes are reading from the database and that checkpoints are attempted during those times. In applications with many concurrent readers, one might also consider running manual checkpoints with the SQLITE_CHECKPOINT_RESTART or SQLITE_CHECKPOINT_TRUNCATE option which will ensure that the checkpoint runs to completion before returning. The disadvantage of using SQLITE_CHECKPOINT_RESTART and SQLITE_CHECKPOINT_TRUNCATE is that readers might block while the checkpoint is running.
- Very large write transactions. A checkpoint can only complete when no other transactions are running, which means the WAL file cannot be reset in the middle of a write transaction. So a large change to a large database might result in a large WAL file. The WAL file will be checkpointed once the write transaction completes (assuming there are no other readers blocking it) but in the meantime, the file can grow very big.
SQLite中的锁
回滚日志锁
- SQLite使用锁逐步上升机制， SQLITE的锁状态共有5种（未加锁(UNLOCKED)、共享(SHARED)、保留(RESERVED)、未决(PENDING)和排它(EXCLUSIVE)），上升到高级别需要低级别的锁。
- 在SQLite2中，如果多个进程正在从数据库中读取数据，也就是说该数据库始终都有读操作发生，即在每一时刻该数据库都持有至少一把共享锁，这样将会导致没有任何进程可以执行写操作，因为在数据库持有读锁的时候是无法获取写锁的，我们将这种情形称为"写饥饿"。在SQLite3中，通过使用PENDING锁则有效的避免了"写饥饿"情形的发生。
- 对于一个读事务会经过以下过程：
- UNLOCKED到PENDING；获取PENDING锁只是暂时的，获取PENDING锁是获取SHARED锁的第一步，因为若有其它事务已获取PENDING锁，则此事务不能再获取SHARED锁了。
- 如果获取PENDING锁成功，则此事务可以继续获取SHARED锁，并将之间获取的PENDING释放。
- 对于一个写事务会以下过程
- 第一步和读事务一样，获取SHARED锁。
- 获取RESERVED锁，一旦事务要进行写操作，首先就要获取此锁。
- 获取EXCLUSIVE锁，实际上此时要先获取PENDING锁，以阻止其它事务继续获取SHARED锁（因为前面说过获取PENDING锁是获取SHARED锁的第一步），进而防止写饿死。
- 获取PENDING锁后，才真去获取EXCLUSIVE锁；如果获取EXCLUSIVE锁成功，则事务就可以进行写磁盘操作了。
- 在进行事务时可能发生死锁, 见SQLite在多线程环境下的应用 , 发生死锁需要一直请求加锁才会（申请不到也不返回错误，就一直申请），默认情况下请求不到对应锁就返回SQLITE_BUSY, PS:SQLite作为一款小型的嵌入式数据库，本身没有提供复杂的锁定机制，无法内部管理多路并发下的数据操作同步问题，更谈不上优化，所以涉及到多路并发的情况，需要外部进行读写锁控制，否则SQLite会返回SQLITE_BUSY错误，以驳回相关请求，这是由于使用当前连接访问数据时，要申请相应级别的锁，而各个级别的锁有些是互斥的，当申请不到锁时就会返回这个错误。这时只要稍等片刻，等其它连接的操作处理完，释放了相斥的锁之后就可以取得锁并进行操作了。
- SQLITE用锁超时的机制处理死锁。当某个事务陷入死锁后，sqlite尝试一定次数，几次之后依然无法获取锁的话，就返回错误信息，把如何处理错误的权利交给用户程序。这一步需要通过
sqlite3_busy_handler() 函数当检测到当前连接的数据库处于SQLITE_BUSY状态下，会调用回调函数，在回调函数内可以定义一些行为处理来实现或者参考sqlite多进程并发读写模式下，返回SQLITE_BUSY错误的处理方法 和文档 sqlite遇到database is locked问题的完美解决 - 如何处理上述的问题？ 答案：SQLite提供了三种不同的事务类型共用户选用： DEFERRED，IMMEDIATE，EXCLUSIVE， 三种的区别在于获取锁的起点不同。基本的使用准则是：如果你在使用的数据库没有其它的连接，用BEGIN就足够了。但是，如果有其它的连接也要对数据库进行写操作，就得使用BEGIN IMMEDIATE或BEGIN EXCLUSIVE开始事务。最好用BEGIN IMMEDIATE
- SQL级别的事务控制：
- SQLite3在实现上确实针对锁和并发控制做出了一些精巧的变化，特别是对于事务这一SQL语言级别的特征。在缺省情况下，SQLite3会将所有的SQL操作置于antocommit模式下，这样所有针对数据库的修改操作都会在SQL命令执行结束后被自动提交。在SQLite中，SQL命令"BEGIN TRANSACTION"用于显式的声明一个事务，即其后的SQL语句在执行后都不会自动提交，而是需要等到SQL命令"COMMIT"或"ROLLBACK"被执行时，才考虑提交还是回滚。由此可以推断出，在BEGIN命令被执行后并没有立即获得任何类型的锁，而是在执行第一个SELECT语句时才得到一个共享锁，或者是在执行第一个DML语句时才获得一个保留锁。至于排它锁，只有在数据从内存写入磁盘时开始，直到事务提交或回滚之前才能持有排它锁。
- 如果多个SQL命令在同一个时刻同一个数据库连接中被执行，autocommit将会被延迟执行，直到最后一个命令完成。比如，如果一个SELECT语句正在被执行，在这个命令执行期间，需要返回所有检索出来的行记录，如果此时处理结果集的线程因为业务逻辑的需要被暂时挂起并处于等待状态，而其它的线程此时或许正在该连接上对该数据库执行INSERT、UPDATE或DELETE命令，那么所有这些命令作出的数据修改都必须等到SELECT检索结束后才能被提交。
- sqlite3只支持每秒几十次的事务，所以为了提高插入的效率，需要将一组SQL命令放在事务里，这样多条语句都通过一次事务完成，而不是每条语句都是一次默认的autocommit. 这样可能达到每秒万次数量级的插入操作。
WAL模式下的锁
- WAL读写不互斥，写与写可能阻塞，注意处理checkoutpoint饥饿就行
- WAL模式的锁，见2.3节
- 在WAL日志模式中也会用到回滚日志的共享锁和独占锁，WAL模式下，所有连接始终都持有回滚日志的共享锁，在退出WAL日志模式时，会先获取回滚日志的独占锁，如果获取失败，说明还有其他连接在使用数据库，那么就不能退出。只有当前一个连接在使用数据库时才能退出，退出时会把WAL日志文件和WAL-index文件删除。下面来分析每一种WAL锁的应用：
- WAL_WRITE_LOCK
- 这个锁为独占锁，每一个写事务开始时都需要该锁，同一个时间只能有一个写事务持有该锁，写事务和写事务不能并发进行。
- 在事务异常中断，WAL-index文件头部遭破坏后恢复这段时间也会持有该锁，因为写事务提交时也会修改WAL-index文件。
- WAL_CKPT_LOCK
- 在将WAL日志更新到数据库文件时会使用该锁，同一时间只能有一个连接进行checkpoint操作，所以该锁为独占锁。在wal_checkpoint模式为PASSIVE时，WAL同步数据的内容不超过每个线程的read-mark记录的帧，后面的帧不会同步，读事务只读WAL日志中在read-mark之前的帧，后面的帧在数据库中读取，所以同步数据库时并不会影响到读事务。写事务是向WAL文件追加内容，也不影响checkpoint操作。
- checkpoint操作必须持有WAL_READ_LOCK(0)独占锁，因为持有WAL_READ_LOCK(0)共享锁的读事务只在数据库中读取数据。
- 如果wal_checkpoint模式为其他模式时，需要截断WAL日志，或者把写事务的起始地址恢复到WAL文件开头，此时需要持有WAL_WRITE_LOCK，防止其他连接开始写事务。同时也要持有WAL_READ_LOCK(i)锁，此时读事务已经不能在WAL日志中读取数据。
- WAL_RECOVER_LOCK
- 在恢复WAL-index时会加该锁，该锁是独占锁。同时在恢复期间，所有的锁都会加上，此时不能进行任何读写操作。但是如果恢复前发现有其他进程在进行checkpoint操作来同步数据库时，并不会等待其完成，而是直接同步进行。
- 另外有一点需要注意的是，检查点操作和恢复操作肯定不能同时进行，否则可能把未提交的页同步到数据库中，那为什么当其他线程持有WAL_CKPT_LOCK锁时，还要继续执行恢复操作？个人的理解是如果检查点操作在同步数据库时处在关键操作的地方，必然会占有读锁或写锁的独占锁，此时恢复操作肯定不能加锁成功，而如果恢复操作加锁成功，那么检查点操作并不是在关键地方，在恢复操作完成之前，下一次检查点操作可以依然正常获取WAL_CKPT_LOCK锁，但是其他写锁和读锁都获取失败，所以不可能对数据库进行同步。
- 另外别的进程可以通过获取WAL_RECOVER_LOCK的共享锁是否成功，来判断是否有进程在进行恢复操作。
- WAL_READ_LOCK(0)
- 0表示只从数据库读取页
- 读事务申请的共享锁，和WAL_WRITE_LOCK不冲突，读写可以完全并发进行，互不影响。但是不能和数据库同步操作和WAL-index文件恢复并发进行。
- WAL_READ_LOCK(i) i=1~4
- 读事务在开始的时候，记录WAL文件的最后一个有效帧（read mark。即ReadMark[i]的值），并在整个事务中忽略之后添加的新内容。
- 如果不在WAL日志中，则从数据库中读取数据，则直接去数据库读取，并修改ReadMark[i]的值，这时需要获取WAL_READ_LOCK(i)的独占锁，以确保此时没有相关的读事务。
- 如果在，则直接读就可以了。
- 为什么有多个？ 猜测是为了提高并发度，多个锁，可以并行执行，避免都通过一个锁操作，因为更新read mark时是排它的。
- 为什么去数据库读取时需要修改ReadMark[i]？ 因为mxFrame在写事务结束时是会改变的，检查点操作时不知道读事务的最大帧，所以需要记录在ReadMark[i]里面。举个例子：假如读事务A开始的时候，WAL文件中有5个帧(Frame)，且都不包含数据库第3页的内容。A在开始的时候记下read mark，表示只能看到WAL文件的前5帧，之后的不管，然后搜索wal-index，发现前5帧里都没有第3页，于是从数据库文件中去读取第3页的内容。如果这时，有一个写事务B，修改了第3页的内容，这时WAL文件的第六帧就是第3页的新内容，如果这时候，回填程序超过了A的read mark，把第六帧（第三页）也同步到了数据库，那么读写可能冲突，导致读到错误的数据。本质上就是，让读数据库和写数据库操作（check point操作）不冲突，即读写不冲突。
- WAL_WRITE_LOCK
扫一扫
379
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
