数据库基础

1.mysql 索引方法

1.1 mysql 索引类型

普通索引（normal）、唯一索引（unique）、主键索引（primary key）、全文索引（fulltext）和组合索引

使用方法：

（1）create(不能用CREATE INDEX语句创建PRIMARY KEY索引)

CREATE （FULLTEXT/UNIQUE/不加为普通索引） INDEX index_name ON table(column(length))

（2）alter

ALTER TABLE table_name ADD  （primary key/FULLTEXT/UNIQUE/不加为普通索引）INDEX index_name ON (column(length))

（3）在创建表时

CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))/UNIQUE indexName (title(length))/FULLTEXT (content)

);

普通索引：基本索引，没有任何限制，MyIASM中默认的BTREE类型的索引

唯一索引：索引列的值必须唯一，但允许有空值（注意和主键不同）

主键索引：它是一种特殊的唯一索引，不允许有空值。 

全文索引：从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建（用于搜索文本类列的全文内容比较方便），针对较大的数据，生成全文索引很耗时好空间。

组合索引：最左开始组合

聚集索引（又叫聚簇索引）：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引，但是可以有多个非聚集索引。SQL的主流索引结构有B+树以及Hash结构，聚集索引以及非聚集索引用的是B+树索引。主键一般是聚集索引。
聚集索引的叶子节点就是对应的数据节点。
非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块（即只包含该索引列对应的数据），而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据

1.2 索引方式：FULLTEXT，HASH，BTREE，RTREE

（1）FULLTEXT

在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE INDEX创建FULLTEXT索引，要比先为一张表建立FULLTEXT然后再将数据写入的速度快很多。

它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。在没有全文索引之前，这样一个查询语句是要进行遍历数据表操作的，

ALTER TABLE table ADD INDEX `FULLINDEX` USING FULLTEXT(`cname1`[,cname2…]);

（2）HASH

hash就是一种（key=>value）形式的键值对，如数学中的函数映射，允许多个key对应相同的value，但不允许一个key对应多个value。

Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。
Hash 索引无法被用来避免数据的排序操作
Hash 索引不能利用部分索引键查询（对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用） 
Hash 索引在任何时候都不能避免表扫描（Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果）
Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高

（3）BTREE（B-Tree的数据在叶子结点模式，叶子结点往兄弟叶子节点有指针）

B-Tree索引可以被用在像=,>,>=,<,<=和BETWEEN这些比较操作符上。而且还可以用于LIKE操作符，只要它的查询条件是一个不以通配符开头的常量。

单列索引的列不能包含null的记录，复合索引的各个列不能包含同时为null的记录，否则会全表扫描；
不适合键值较少的列（重复数据较多的列）；
前导模糊查询不能利用索引(like '%XX'或者like '%XX%')

（4）RTREE

高维度的BTREE，二维三维甚至更好维

（5）bitmap位图索引

适合：
适合决策支持系统；
当select count(XX) 时,可以直接访问索引中一个位图就快速得出统计数据；
当根据键值做and，or或 in(x,y,..)查询时，直接用索引的位图进行或运算,快速得出结果行数据。
不适合：
不适合键值较多的列（重复值较少的列）；

不适合update、insert、delete频繁的列，代价很高（即适合静态数据）。

2.索引的优化

2.1 在哪里建索引

需要搜索的列上，可以加快搜索的速度；作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度

2.2 不需要建索引

很少使用的列；取值很少的列；定义为text,image,bit数据类型，数据量要么取值大，要么取值少；更需要修改性能时，修改性能和检索性能是互相矛盾的

2.3 索引优化

主键外键一定要建索引
选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0
索引列不能是表达式的一部分，也不能作为函数的参数，否则无法使用索引查询（索引不能做运算，否则需要读取每列做运算）
为较长的字符串使用前缀索引（选择字符列的前n个字符作为索引），节约索引空间
最左前缀匹配原则，组合索引的创建中索引列的顺序非常重要，将选择性最高的列放到索引最前列
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
对于like查询，”%”不要放在前面。 

查询where条件数据类型不匹配也无法使用索引 

查询中有列范围查询，则其右边的所有列都无法使用索引优化

3.事务的ACID

事务：主要用于处理操作量大，复杂度高的数据。事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。事务用来管理 insert,update,delete 语句。

原子性（Atomicity，或称不可分割性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到（数据的准确性）。
隔离性（Isolation，又称独立性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

4.事务的四个隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

不带SESSION、GLOBAL的SET命令，只对下一个事务有效；
SET SESSION，为当前会话设置隔离模式；
SET GLOBAL，为以后新建的所有MYSQL连接设置隔离模式（当前连接不包括在内）

4.1事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3、幻读：第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
·提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
·可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

·串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

5. InnoDB与MyISAM区别

之前，MySQL默认采用的是MyISAM，从MySQL5.5.5以后，InnoDB是默认引擎

（1）事务处理：

MyISAM是非事务安全型的，而InnoDB是事务安全型的（支持事务处理等高级处理）；

InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。

（2）锁机制不同：
MyISAM是表级锁；而InnoDB是行级锁，提供与 Oracle 类型一致的不加锁读取(non-locking read inSELECTs)，另外，InnoDB表的行锁也不是绝对的，如果在执 行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”；
（3）select ,update ,insert ,delete 操作：
MyISAM：如果执行大量的SELECT，MyISAM是更好的选择
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表
（4）查询表的行数不同：
MyISAM：select count(*) from table,MyISAM只要简单的读出保存好的行数，注意的是，当count(*)语句包含   where条件时，两种表的操作是一样的
InnoDB ： InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行
（5）外键支持：

mysiam表不支持外键，而InnoDB支持

（6）fulltext：

mysiam表支持，而InnoDB不支持

MyISAM支持GIS数据，InnoDB不支持。即MyISAM支持以下空间数据对象：Point,Line,Polygon,Surface等

(7) 构成：每个MyISAM在磁盘上存储成三个文件。第一个 文件的名字以表的名字开始，扩展名指出文件类型。

.frm文件存储表定义。数据文件的扩 展名为.MYD (MYData)。索引文件的扩 展名是.MYI (MYIndex)；基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的 大小只受限于操作系统文件的大小，一般为 2GB

（8）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引
（9）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表

总结：
MyISAM适合：(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。

InnoDB适合：(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且行锁定的机会比较大的情况。

6.查询优化

6.1 从索引上优化

在 where 及 order by 涉及的列上建立索引
避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
避免在 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
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
like %李%导致全表扫描，考虑全文索引
where 子句中使用参数（即变量），也会导致全表扫描
避免在 where 子句中对字段进行表达式操作（num/2）和函数操作（substring(num)）

最左边索引

不要在选择的栏位（select后面）上放置索引，这是无意义的

6.2 在SQL语言上优化

判断执行时，用 exists 代替 in
应尽可能的避免更新聚集( clustered) 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
使用具体的字段列表代替“*” （select * from t ）
如果一次性对同一个表插入多条数据，把它拼成一条语句执行效率会更高. insert into person(name,age) values(‘xboy’, 14), (‘xgirl’, 15),(‘nia’, 19);
虽然用varchar比用char会更省空间，但优先使用定长型
能用UNION ALL（允许重复，不会排序）就不要用UNION（会排序，不允许重复）
update语句避免修改主键、在where中的列、有索引的列以及会被复制的列；当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。
尽量不要用SELECT INTO语句。SELECT INTO 语句会导致表锁定，阻止其他用户访问该表。
能够用DISTINCT的就不用GROUP BY
能够用BETWEEN的就不要用IN
不要在应用中使用数据库游标，游标是非常有用的工具，但比使用常规的、面向集的SQL语句需要更大的开销；
尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
避免频繁创建和删除临时表，以减少系统表资源的消耗。
在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

7.Mysql 的联合索引

又名复合索引，由两个或多个列的索引。它规定了mysql从左到右地使用索引字段，对字段的顺序有一定要求。一个查询可以只使用索引中的一部分，更准确地说是最左侧部分（最左优先）。

前缀索引：对于列的值较长，比如BLOB、TEXT、VARCHAR，就必须建立前缀索引，即将值的前一部分作为索引。这样既可以节约空间，又可以提高查询效率。但无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。

覆盖索引：查询的时候只用去读取索引而取得数据，无需进行二次查询相关表。这样的索引的叶子节点上面也包含了他们索引的数据。

组合索引的生效原则是  从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；

如果where条件中是OR关系，加索引不起作用

8.数据库分库分表

MySQL单表太大后有一个问题是不好解决: 表结构调整相关的操作基 本不在可能.所以大项在使用中都会面监着分库分表的应用. 

分表（水平切分）提升查询能力：分为多个表

分库（垂直切分）提升并发能力：按模块功能不同分为多个数据库