数据库总结

Sql语句
数据定义语言（DDL）
是一组SQL命令，用于创建和定义数据库对象，并且将对这些对象的定义保存到数据字典中。通过DDL语句可以创建数据库对象，修改数据库对象和删除数据库对象等。

1. create database 数据库名；//创建数据库对象

2. drop database 数据库名；//删除数据库对象

3. use 数据库名；//选择数据库

4. CREATE TABLE mytable (//创建表和字段及约束
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   col1 INT NOT NULL,
   col2 VARCHAR(45) NOT NULL,
   col3 DATE NOT NULL,
   );
   在创建表时注意有些时候需要创建约束：
   • NOT NULL 不为空
   • UNIQUE 约束唯一标识数据库表中的每条记录。可以有多个。
   • PRIMARY KEY 主键：约束唯一标识数据库表中的每条记录。不能包含null,每个表都应该有且只有一个主键。
   • FOREIGN KEY 外键：指向另一个表的PRIMARY KEY。
   • CHECK 限制列中的值的范围。
   • DEFAULT 设置默认值。
   例如设置性别男女：
   sex char(1) not null check (sex='男’or sex=‘女’）。

5. Alter table mytable add 列名char(20);//添加列

6. ALTER TABLE mytable DROP COLUMN 列名;//删除列

7. DROP TABLE mytable;//删除表

数据查询语言（DQL）
通用模板
Select 列名1 , 列名2
from 表名
where 条件
limit n offset m

1. 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
2. SELECT 命令可以读取一条或者多条记录。用*就是所有的字段
3. LIMIT 属性来设定返回的记录数。OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
   单表查询
4. Select *from 表名;//查询表的所有行和列
5. Select Sno as 学号 from student //将Sno列标题换成 学号
6. SELECT * FROM student limit 2 OFFSET 3，//从第三条数据开始查询两条数据
7. SELECT * FROM student WHERE name<2 and age>20；//在where中使用and or来连接条件。
   算术运算符：

• 加法 -添加任一侧上的操作符的值

• 减法 - 从左边减去右边操作数的操作

• 乘法 - 操作符两侧的值相乘
  / 除 - 将左边除右边的操作数（取模）
  % 模量 - 将左边用右手操作，并返回操作数的余数
  比较操作符：
  = 检查如果两个操作数的值是相等或不相等，如果是的话，条件为真。
  != 检查如果两个操作数的值是否相等，如果值不相等，则条件为真。
  <> 检查如果两个操作数的值是否相等，如果值不相等，则条件为真。

检查值，如果左操作数大于右操作数的值，如果是的话，条件为真。
< 检查如果左操作数的值小于右操作数的值，如果是的话，条件为真。
= 检查如果左操作数的值大于或等于右操作数的值，如果是的话，条件为真。
<= 检查如果左操作数的值小于或等于右操作数的值，如果是的话，条件为真。
!< 检查如果左操作数的值不小于右操作数的值，如果是的话，条件为真。
!> 检查如果左操作数的值是不大于右操作数的值，如果是的话，条件为真。
逻辑运算符：
ALL 所有运算符用于比较的值到另一个值组中的所有值。
AND AND运算符允许多个条件的存在，在一个SQL语句中的WHERE子句。
ANY ANY运算符用于比较的值在列表中根据任何适用的条件。
BETWEEN BETWEEN运算符用于搜索一组值的范围内的值，给定的最小值和最大值。
EXISTS 存在指定的表中符合特定条件的运算符用于搜索存在的一行记录。
IN IN操作符用来比较的文字值已指定一个值的列表。
LIKE LIKE运算符用于比较相似的值，使用通配符的值。
NOT NOT运算符的含义相反的逻辑运算符，它被使用如 NOT EXISTS, NOT BETWEEN, NOT IN 等，这是一个相反的运算符。
OR 使用OR运算符结合SQL语句的WHERE子句中的多个条件。
IS NULL NULL操作符用来比较NULL的值。
UNIQUE 独特的操作符搜索指定的表中的每一行的唯一性（不重复）。

5. SELECT DISTINCT name FROM student //检索时显示不重复的信息
6. SELECT * FROM student where name like ‘曾%’ //like 使用通配符。模糊查询
   通配符 描述
   % 替代 0 个或多个字符
   _ 替代一个字符
   [charlist] 字符列中的任何单一字符
   [^charlist]
   或
   [!charlist] 不在字符列中的任何单一字符
   我们在数据库中无法通过比较运算符来测试NULL值。
   所以需用到IS NULL 和 IN NOT NULL。
   7．SELECT * FROM student where name IS NULL
7. SELECT * FROM student ORDER BY age DESC//按照age降序
   ORDER BY ageDESC 是降序。升序是ASC。可以省略。
   多个排序按照从左往右的优先级。没有排序默认按照在数据库中的位置。
8. SELECT SUM(id) COUNT(id),MAX(id),MIN(age),AVG(age) from student //count(列名)返回指定列的值的数目。Max（列名）列的最大值
   Min(列名):列的最小值 avg(列名)：平均值。SUM(列名)：求和。
9. select NAME ,COUNT() from student GROUP BY name HAVING COUNT()>1//分组。将name一样的划分为一个组，计算数量。
   使用having是对分组进行筛选。Where是对原始数据进行筛选。
   多表查询：
10. 多表连接查询：内连接。外连接
    内连接：他根据两个表中有共同的列来进行匹配。查出满足匹配关系的数据。通常两个表存在主外键关系。就是两个表的交集
    例如：查询学生的学号，姓名，课程，分数。
    Select student.sno,student.sname,sc.cno,sc.grade
    From sc join student
    On sc.sno=student.sno
    或者：
    Select student.sno,student.sname,sc.cno,sc.grade
    From sc,student
    Where sc.sno=student.sno
    两种方法都可以。

外连接：左连接（left join），右连接(right join)。
外连接不但返回符合连接和查询条件的数据行，还返回不符合连接条件但符合查询条件的一些行。
①左外连接还返回左表中不符合连接条件，但符合查询条件的数据行。(所谓左表，就是写在left join关键字左边的表)
②右外连接还返回右表中不符合连接条件，但符合查询条件的数据行。(所谓右表，就是写在right join关键字右边的表)
交叉连接-笛卡尔积：
因为没有连接条件，所进行的表与表间的所有行的连接。
特点①连接查询没有写任何连接条件
　　②结果集中的总行数就是两张表中总行数的乘积(笛卡尔积)

2. 嵌套查询
   使一个查询的结果是并一个查询的条件。一个查询嵌套另一个查询。使用IN ,ANY,ALL,EXISTS等连接。
   例如：查询选修了B004课程的学生的基本信息。
   Select * from Student
   Where exists
   (select *from SC where Sno=Student.Sno and Cno=’B004’)
3. 集合查询
   SELECT语句查询的结果是元组的集合，多个select语句的查询结果可进行集合操作，包括并（Union 去掉重复元组。Union ALL 保留重复元组），交（Intersect），差（Except）前提条件是select语句必须拥有相同数量的列，且类型兼容。
   UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
   请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
   例如：将两张表中的所有的name字段都查出来放在一个结果集里
   select NAME FROM student
   UNION//使用union会删除重复的数据。使用UNION ALL 不会。
   SELECT username from login

数据操作语言（DML）：
插入：insert
INSERT INTO table_name ( field1, field2,…fieldN )
VALUES
( value1, value2,…valueN ),
(valueA1,valueA2,…valueAN);
插入的模板。可一次插入多条数据。
例如：
insert into student (name,age)
VALUES
(“曾1帅”,20),
(“曾2帅”,20),
(“曾3帅”,20);
修改：update
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
例如：
UPDATE student SET name=“曾大伟” ,age=88
WHERE id=1
删除：delete
DELETE FROM table_name [WHERE Clause]
例如：
DELETE FROM student
where id=8
数据控制语言（DCL）
数据控制语言用于修改数据库结构的操作权限。
两种：用的很少
CRANT 授予其他用户对数据库结构的访问权限
REVOKE 收回用户访问数据库结构的权限

Mysql解读
Mysql的架构和流程
架构：

流程：

事物和隔离级别
事务是由一组SQL语句组成的逻辑处理单元，是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。事务具有以下4个属性，通常简称为事务的ACID属性:
原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
一致性（Consistent）（目的）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。 以转账为例子，A向B转账，假设转账之前这两个用户的钱加起来总共是2000，那么A向B转账之后，不管这两个账户怎么转，A用户的钱和B用户的钱加起来的总额还是2000，这个就是事务的一致性。
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。 　可以通过数据库备份和恢复来实现，在系统发生奔溃时，使用备份的数据库进行数据恢复。
MySQL 的事务默认采用自动提交模式。
并发操作引发的问题：

1. 丢失更新问题
   T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
   例如：a=18
   时间 事务T1 事务T2
   1 读a=18
   2 读a=18
   3 a=a-2
   4 写回a=16
   5 a=a-2
   6 写回a=16
   如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。
2. 读“脏“数据
   事务T2读取到了事务T1已修改但尚未提交的数据，
   例如：
   例如：a=18
   时间 事务T1 事务T2
   1 读a=18
   2 a=a-2
   3 写回a=16 读a=16
   4
   5 Rollback(恢复c=15)
   解决办法: 把数据库的事务隔离级别调整到 READ_COMMITTED
3. 不可重复读
   事务T1读取数据A后，事务T2更新了A。如果T1再次读A。两次读的而结果不同。
   例如：
   时间 事务T1 事务T2
   1 读a=18
   2 读a18
   3 a=a-2
   4 写回a=16
   5 读a=16(验算不对)
   如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。把数据库的事务隔离级别调整到REPEATABLE_READ
4. 幻读
   一个事务T1按相同的查询条件重新读取以前检索过的数据，却发现其他事务T2插入了满足其查询条件的新数据，这种现象就称为“幻读”。（和可重复读类似，但是事务 T2 的数据操作仅仅是插入和删除，不是修改数据，读取的记录数量前后不一致）
   一句话：事务A 读取到了事务B提交的新增数据，不符合隔离性。
   解决办法: 如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题。把数据库的事务隔离级别调整到 SERIALIZABLE_READ。
   事务的隔离级别：

1、 读未提交 (Read Uncommitted)
最低的隔离等级，允许其他事务看到没有提交的数据，会导致脏读。
2、读已提交 (Read Committed)
一个事务只能看见已经提交事务所做的改变。不能读取没有提交事务的中间脏数据。该等级也是 SQL Server 默认的隔离等级。
3、可重复读(Repeatable Read)
所有被 Select 获取的数据都不能被修改，这样就可以避免一个事务前后读取数据不一致的情况。但是却没有办法控制幻读，因为这个时候其他事务不能更改所选的数据，但是可以增加数据，即前一个事务有读锁但是没有范围锁，为什么叫做可重复读等级呢？那是因为该等级解决了下面的不可重复读问题。(引申：现在主流数据库都使用 MVCC 并发控制，使用之后RR（可重复读）隔离级别下是不会出现幻读的现象。)
MYSQL默认是REPEATABLE-READ 。
4、串行化(Serializable)
所有事务一个接着一个的执行，这样可以避免幻读 (phantom read)，对于基于锁来实现并发控制的数据库来说，串行化要求在执行范围查询的时候，需要获取范围锁，如果不是基于锁实现并发控制的数据库，则检查到有违反串行操作的事务时，需回滚该事务。
5、总结
事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
读已提交（read-committed） 否 是 是
可重复读（repeatable-read） 否 否 是
串行化（serializable） 否 否 否

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
锁机制
InnoDB支持事务，支持行锁和表锁用的比较多，Myisam不支持事务，只支持表锁。
MySQL表级锁的锁模式（MyISAM)：
表锁：偏向MyISM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

表共享读锁（Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
表独占写锁（Table Write Lock）：会阻塞其他用户对同一表的读和写操作；当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。
简而言之，就是读锁会阻塞写，但是不会堵塞读，而写锁会把读和写都堵塞。
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

MySQL行级锁的锁模式（InnDB)：
行级锁：偏向于InnDB存储引擎。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB的行锁有两种：共享锁（S）和排他锁（X）。为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁：意向共享锁和意向排他锁，这两种意向锁都是表锁。一个事务在给数据行加锁之前必须先取得对应表对应的意向锁。
这里的共享锁和排他锁都是基于行的：
共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
这里的意向锁是基于表的：
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE 和INSERT 语句，InnoDB会自动给涉及数据集加排他锁（X）；对于普通SELECT语句，InnoDB 不会加任何锁；

InnoDB行锁是通过给索引上的索引项加锁来实现的，InnoDB 这种行锁实现特点意味着：（1）只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB 将使用表锁。（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
InnoDB避免死锁：
（1）在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
（2）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
（3）在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
（4）在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
（5）当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。

3.间隙锁（Next-Key锁）
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；
对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
例：
假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：
mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
InnoDB使用间隙锁的目的：
（1）防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；
（2）为了满足其恢复和复制的需要。
很显然，在使用范围条件检索并锁定记录时，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
除了间隙锁给InnoDB带来性能的负面影响之外，通过索引实现锁定的方式还存在其他几个较大的性能隐患：
（1）当Query无法利用索引的时候，InnoDB会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；
（2）当Query使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所只想的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，
　　 而不是具体的索引键；
（3）当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定。
　 　因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
　　 还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。
索引
索引的定义（索引别称index，key，键）
在关系数据库中，索引是对表中一列或多列的值进行排序的一种存储结构，它是表中一列或多列的值的集合，而且其中包含了对应表中记录的引用指针。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
要注意的是，索引也是表的组成部分，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立索引。
举个例子：首先，先假设有一张表，表有10W个记录，其中有一条记录我们已知a=‘1’，如果想要拿到对应记录的话，需要的sql语句是 SELECT * FROM xxx WHERE a=‘1’.
一般情况下，对于查询语句，在没有建立索引的时候，mysql会进行全表扫描，而且不扫描完10W个记录不会停止，如果我在nickname上建立索引，那么mysql相当于只扫描nickname这一列即可，而且因为这一列已排好序，找到对应结果或结果集可以直接返回。
mysql的索引分为单列索引(全文索引，主键索引，唯一索引，普通索引)和组合索引。
单列索引:一个索引只包含一个列，一个表可以有多个单列索引。
组合索引:一个组合索引包含两个或两个以上的列，
(一)索引的创建
1.单列索引
1-1) 普通索引（这个是最基本的索引）
建表时：INDEX IndexName(字段名(length))
建表后：CREATE INDEX IndexName ON TableName(字段名(length))
或ALTER TABLE TableName ADD INDEX IndexName(字段名(length)
注意：如果字段数据是CHAR，VARCHAR类型，可以指定length，其值小于字段的实际长度，如果是BLOB和TEXT类型就必须指定length。
这个length的用处是什么?
有时候需要在长文本字段上建立索引，但这种索引会增加索引的存储空间以及降低索引的效率，这时就可以用到length，创建索引时用到length的索引，我们叫做前缀索引，前缀索引是选择字段数据的前n个字符作为索引，这样可以大大节约索引空间，从而提高索引效率。
此处展示的语句用于创建一个索引，索引使用字段数据的前10个字符。
CREATE INDEX part_of_name ON customer (name(10));
使用字段数据的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。
前缀索引是一种能使索引更小，更快的有效办法，但是MySql无法使用前缀索引做ORDER BY 和 GROUP BY以及使用前缀索引做覆盖扫描。
这里又引出了一个新概念，覆盖扫描！
如果一个索引（如：组合索引）中包含所有要查询的字段的值，那么就称之为覆盖索引，如：
SELECT user_name, city, age FROM user_test WHERE user_name = ‘feinik’ AND age > 25;
因为要查询的字段（user_name, city, age）都包含在组合索引的索引列中，所以就使用了覆盖索引查询，查看是否使用了覆盖索引可以通过执行计划中的Extra中的值为Using index则证明使用了覆盖索引，覆盖索引可以极大的提高访问性能。
1-2) 唯一索引，要求字段所有的值是唯一的，这一点和主键索引一样，但是允许有空值。
建表时：UNIQUE INDEX IndexName(字段名(length))
建表后：CREATE UNIQUE INDEX IndexName ON TableName(字段名(length))
或ALTER TABLE TableName ADD UNIQUE INDEX IndexName(字段名(length)）
1-3) 主键索引，不允许有空值
一般在建表的时候自动创建，主键一般会设为 int 而且是 AUTO_INCREMENT自增类型的
1-4)全文索引
假设字段的数据类型是长文本，文本字段上(text等)建立了普通索引，我们需要查找关键字的话，那么其条件只能是where column like ‘%xxxx%’ ，但是，这样做就会让索引失效，这时就需要全文索引了。
建表时：FULLTEXT INDEX IndexName(字段名(length))
建表后：CREATE FULLTEXT INDEX IndexName ON TableName(字段名(length))
或ALTER TABLE TableName ADD FULLTEXT INDEX IndexName(字段名(length)）
使用：
SELECT * FROM TableName
WHERE MATCH(column1， column2) AGAINST(‘xxx′， ‘sss′， ‘ddd′)
这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来。
下面我们来举个例子：
假设有一个书籍表，结构如下，文章内容字段的数据类型是text
文章id 文章标题 文章内容
1 超级塞亚人 我是超级塞亚人我喜欢吃苹果，我不是天朝的人，也不是地球人
2 天朝大国 我大天朝威武，我大天朝13亿人，我大天朝
3 我喜欢游泳 游泳有很多好方法
4 动画片 我儿子喜欢看动画片，尤其是七龙珠，因为里面有塞亚人，而且塞亚人喜欢吃苹果,他们不是地球人
5 运动 我喜欢运动，喜欢跑步，喜欢游泳，喜欢健身，喜欢xxoo
6 打炮 我是一个二战的老兵，这是我的回忆录，我最幸福的时光就是在天朝吃着苹果打炮

我想在茫茫多书籍的内容里搜索关键词，如果用%xxx%搜索，那效率就太低了。

我们在文章内容字段上建立全文索引，下面是索引文件
关键词 文章id（引用指针）
塞亚人 1,4
苹果 1,4,6
天朝 1,2,6
地球 1,4
游泳 3,5
七龙珠 4
喜欢 1,4,5,6
那么当我想搜索 “塞亚人”的时候，这个索引文件直接告诉我在文章id为1和4的文章里有这个词。
可是这些关键词是如何提取出来的呢？这就是要提到一个新概念，“分词”！分词就是提取关键词，但是MYSQL的FULLTEXT对分词不够智能，对中文也不是很支持，所以我们一般不用全文索引。取而代之的是：
coreseek=sphinx+mmesg 这个程序就可以解决这个问题的啦。
sphinx就是索引程序。
mmseg就是分词程序。
国内有人修改了sphinx源码，内建和mmseg配合，整合到一起就是coreseek啦（中文版sphinx）！
2.组合索引
假设字段a，b都有索引，我们的查询条件是a=1，b=2查询过程是mysql会先挑选出符合a=1的结果集，再在这些结果集中挑选b=2的结果集，但是mysql并不会在查询a，b时都用到索引，只会用其中一个，这和我们的预期不一样，所以，我们要使用组合索引
建表时：INDEX IndexName(字段名(length)，字段名(length)，…)
建表后：CREATE INDEX IndexName ON TableName(字段名(length)，字段名(length)，…)
或ALTER TABLE TableName ADD INDEX IndexName(字段名(length)，字段名(length)，…)
(二)索引的删除
DORP INDEX IndexName ON TableName
（三）索引失效的情况　
1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2.使用查询的时候遵循mysql组合索引的"最左前缀"规则，假设现在有组合索引（a，b，c），查询语句就只能是a=1或a=1。and b=1或a=1 and b=1 and c=1。这里有两点需要注意①a=1 and b=1和b=1 and a=1一样，没有区别，都会使用索引②组合索引（a，b，c）的最左前缀是a；组合索引（c，b，a）的最左前缀是c，最左前缀和表字段顺序无关
在组合索引中，如果where查询条件中某个列使用了范围查询（不管%在哪），则其右边的所有列都无法使用索引优化查询
3.like查询以%开头
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
6.索引列不能是表达式的一部分，也不能作为函数的参数，否则无法使用索引查询。下面是例子：
SELECT * FROM user_test WHERE user_name = concat(user_name, ‘fei’);

Hash Hash索引，等值查询效率高，不能排序,不能进行范围查询
B+ 数据有序,范围查询

聚集索引 数据按索引顺序存储，中子结点存储真实的物理数据
非聚集索引 存储指向真正数据行的指针

·存储引擎
1、存储引擎通俗的说就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
InnoDB：

1. 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
2. 必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
3. 支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
4. 不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
5. 如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
6. 支持外键约束
7. 查表总行数时，需要全表扫描
   InnoDB是一个健壮的事务型存储引擎
8. 使用场景：
   1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求
   2.事务.InnoDB存储引擎是支持事务的标准mysql存储引擎
   3.自动灾难恢复。与其他存储引擎不同，InnoDB表能够自动从灾难中恢复
   4.外键约束。mysql支持外键的存储引擎只有InnoDB
   5.支持自动增加列AUTO_INCREMENT属性

MyISAM：

1. 强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
2. 可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
3. 只支持表级锁，用户在操作myisam表时，select（读锁），update（写锁），delete（写锁），insert（写锁）语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
4. 支持 FULLTEXT类型的全文索引
5. 如果执行大量的SELECT和INSERT，MyISAM是更好的选择。
6. 不支持外键约束
7. 查表总行数时，不需要全表扫描
8. MyISAM的表还支持3种不同的存储格式：静态(固定长度)表。动态表。压缩表
9. 每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表名。
   后缀名：.frm:存储表定义; .MYD:存储数据；.MYI:存储索引;

MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。
范式
范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。目前有迹可寻的共有8种范式，依次是：1NF，2NF，3NF，BCNF，4NF，5NF，DKNF，6NF。通常所用到的只是前三个范式，即：第一范式（1NF），第二范式（2NF），第三范式（3NF）。
第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。
考虑这样一个表：【联系人】（姓名，性别，电话）
如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列（电话）拆分，即：【联系人】（姓名，性别，家庭电话，公司电话）。1NF 很好辨别，但是 2NF 和 3NF 就容易搞混淆。

第二范式（2NF）（）：首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
考虑一个订单明细表：【订单详情】（订单号，产品号，价格，折扣，数量，产品名称）。
因为我们知道在一个订单中可以订购多种产品，所以单单一个 订单号是不足以成为主键的，主键应该是（订单号，产品号）。显而易见折扣，数量完全依赖（取决）于主键（订单号，产品号），而价格，产品名只依赖于 产品号。所以 订单详情表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。
可以把【订单详情表】表拆分为【订单详情表】（订单号，产品号，折扣，数量）和【产品表】（产品号，价格，产品名）来消除原订单表中价格，产品名多次重复的情况。

第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。考虑一个订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID）。
其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键（OrderID），所以符合 2NF。不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。
通过拆分【Order】为【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）从而达到 3NF。

第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。
数据库优化：

1. 数据库方面的优化
   a) 表的设计尽量满足范式要求。一般到三范式。
   i. 范式的优点：可以尽量的减少数据冗余，更新更快，表更小。
   ii. 范式的缺点：更多的表关联，可能使一些索引策略失效。
   b) 采用数据库连接池，避免频繁建立连接耗时。
   c) 索引的设计：索引并不是越多越好。索引会提高select的效率。但是会降低insert和update的效率（因为会重建索引）。
   d) 能使用数字型字段就不要使用字符型字段。会降低查询和连接的性能，增加存储的开销。引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
   e) .尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
   f) 对于一些频繁的查询操作使用缓存。
   g) 使用读写分离:因为大部分业务读多写少，将两个数据库分开，访问不同的数据库，并且同步两个数据库。
   h) CQRS：Command（命令） Query（查询） Responsibility（职责） Segregation分离。
   i) 数据的垂直拆分：根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统。分库。
   j) 数据的水平拆分：将某个访问极其频繁的表再按照某个字段的某种规则来分散到多个表之中，每个表中包含一部分数据。分表
2. Sql级的优化：
   a) 应尽量避免全表/大表扫描。
   i. 首先考虑在where和order by涉及的列上建立索引。
   ii. Where中避免对null进行判断。数据库中不留null字段。用not null填充。
   iii. Where中避免使用 != 和<>.放弃索引全表扫描。
   iv. 避免使用OR来连接条件，如果一个字段有索引，一个字段没有。那么就会放弃索引而全表扫描。例如：select id from t where num=10 or Name = ‘admin’。
   改为：
   select id from t where num = 10
   union all
   select id from t where Name = ‘admin’
   v. IN和NOT IN也要慎用。可能导致全表扫描。可以使用exists替代in.
   vi.
   b) 在where中设置参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num = @num
   改为强制使用索引：select id from t with(index(索引名)) where num = @num。
   也避免在sql中使用表达式操作/函数操作。会放弃索引全表扫描
   c) 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。索引的最左匹配原则。
   d) 任何地方都不要使用 select * from t ，用具体的字段列表代替“”，不要返回用不到的任何字段。
   e) Select count() 会全表扫描，杜绝。
   f) 尽量避免大事务操作，提高并发性。
   g) 避免向客户端返回大量的数据。数据量过大，应该改需求。对于大数据量查询，应先分页，再join,直接join 会损耗集群资源。
   h) 模糊查询效率低。
   i) Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
   j) MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。 可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。例如，“SELECT id FROM t WHERE col LIKE ‘Mich%’;”这个查询将使用索引，但“SELECT id FROM t WHERE col LIKE ‘%ike’;”这个查询不会使用索引。
   k) 使用explain对查询的sql进行优化。