1.适合的字段数据类型
2.join代替子查询
3.union代替临时表
4.事务
5.锁
6.外键
7.索引
8.优化查询
1.适合的字段数据类型
char与varchar的选择
------char是固定长度的，查询速度比varchar速度快的多。char的缺点是浪费存储空间。
检索char列时，返回的结果会删除尾部空格，所以程序需要对为空格进行处理。
对于长度变化不大且对查询速度有较高要求的数据可以考虑使用char。
随着MySQL的不断升级，varchar的性能不断改进并提高。
------存储引擎使用原则：
MyISAM：建议使用固定长度列代替可变长度列。
InnoDB：建议使用varchar类型
text与blob的选择
------在保存大文本时，通常选择text或者blob。二者的差别是blob可以保存二进制数据，比如照片。
------text和blob又包括text、mediumtext、longtext和blob、mediumblob、longblob
他们之间的区别是存储文本长度不同和存储字节不同。
------删除数据时,容易产生数据空洞,应对表优化,进行optimize(优化)操作：
optimize table tablename;
浮点型 与 定点型
------MySQL中使用浮点数类型和定点数类型来表示小数
MySQL中使用浮点数类型和定点数类型来表示小数,
------Decimal型的取值范围和double相同。但是decimal的有效取值范围由M和D决定，而且Decimal型的字节数是M+2。也就是说，定点数的存储空间是根据其精度决定的。
------float(6，2)的含义数据是float型，数据长度是6，小数点后保留2位。所以，1234.56是符合要求的。
------如果插入值的精度高于实际定义的精度，系统会自动进行四舍五入处理，使值的精度达到要求。
------浮点数和定点数有其默认的精度，float和double默认会保存实际精度，但这与操作系统和硬件的精度有关。decimal型的默认整数位为10，小数位为0，即默认为整数。
------在MySQL中，定点数以字符串形式存储，因此，其精度比浮点数要高，而且浮点数会出现误差，这是浮点数一直存在的缺陷。如果要对数据的精度要求比较高，还是选择定点数decimal比较安全。
2.join代替子查询
join的用法:
join.jpg
有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
如果使用连接（JOIN）.. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：
SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.
CustomerID
WHERE salesinfo.CustomerID IS NULL
3.union代替临时表
如果想使用ORDER BY或LIMIT子句来对全部UNION结果进行分类或限制，则应对单个地SELECT语句加圆括号，并把ORDER BY或LIMIT放到最后一个的后面：
(SELECT uid,umobile,realname FROM users WHERE vip IN (8, 9))
UNION
(SELECT uid,umobile,realname FROM users WHERE vip NOT IN (8, 9) AND amount > 0 )
ORDER BY uid desc limit 10
使用Union，则所有返回的行都是唯一的，如同您已经对整个结果集合使用了DISTINCT
使用Union all，则不会排重，返回所有的行。
从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL。
4.事务
A、原子性（Atomicity）
表示组成一个事务的多个数据库操作是一个不可分隔的原子单元，只有所有的操作执行成功，整个事务才提交，事务中任何一个数据库操作失败，已经执行的任何操作都必须撤销，让数据库返回到初始状态。
B、一致性（Consistency）
事务操作成功后，数据库所处的状态和它的业务规则是一致的，即数据不会被破坏。
C、隔离性（Isolation）
在并发数据操作时，不同的事务拥有各自数据空间，它们的操作不会对对方产生干扰。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性越好，但并发性越弱。
D、持久性（Durabiliy）
一旦事务提交成功后，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证能够通过某种机制恢复数据。
A、自动提交事务
系统默认每个TRANSACT－SQL命令都是一个事务处理，由系统自动开始并提交。
B、隐式事务
不需要显示开始事务，需要显示提交，隐式事务是任何单独的INSERT、UPDATE 或者DELETE语句构成。当有大量的DDL和DML命令执行时会自动开始，并一直保持到用户明确提交为止。
SHOW VARIABLES 查看变量。
SET AUTOCOMMIT=0，关闭自动提交功能。
需要显示提交或者回滚。
update tablename set sname='孙悟空' where studentid='000000000000003';
commit;
或
rollback;
C、显示事务
显示事务是用户自定义事务，以START TRANSACTION（事务开始）开头，以 COMMIT（事务提交）或者 ROLLBACK（回滚事务）语句结束。
start transaction
update tablename set sname='孙悟空' where studentid='000000000000003';
commit
或
rollback
D、事务并发带来的问题
1.脏读（Dirty Read）是指某个事务（A）读取另外事务（B）尚未提交的更改数据，并在读取的数据的基础上操作。如果恰巧 B事务回滚，那么 A事务读到的数据根本是不被承认的。
2.不可重复读（Unrepeatable Read） 是指事务A读取的时候,事务（B）还在未提交状态,读取不了,还需要等事务（B）执行提交后,事务（A）才能读. (不允许读取未提交的数据)
3.幻象读（Phantom Read）
A事务读取B事务提交的新增数据，这时A事务将出现幻象读的问题。
E、不同会话的隔离级别
1.READ UNCOMMITTED (未提交读)
会话1(设置级别)
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
会话2(查看级别)
select @@tx_isolation
start TRANSACTION; (开启事务,更新ID为1的记录的age为1000)
update ta set age=1000 where id =1;
会话1
select * from ta;(获得age为1000)
会话2
ROLLBACK;(会话1与会话2恢复500)
2.READ COMMITTED (提交读)
会话1(设置级别)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
会话2(查看级别)
select @@tx_isolation
select * from ta; (获取age为500)
start TRANSACTION; (开启事务,更新ID为1的记录的age为1000)
update ta set age=1000 where id =1;
会话1
select * from ta;(获取不了数据,会话2还没提交,出现不可重复读的情况)
会话2
ROLLBACK;(会话1可读 age为500) / COMMIT;(会话1可读 age为1000)
3.REPEATABLE READ (重复读)
会话1(设置级别)
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
会话2(查看级别)
select @@tx_isolation
select * from ta; (获取age为500)
start TRANSACTION; (开启事务,更新ID为1的记录的age为1000)
update ta set age=1000 where id =1;
会话1
select * from ta; (得到age为500，此时会话1能新增行,有几率出现幻象读)
会话2
ROLLBACK;(会话1 age为500) / COMMIT;(会话1 age为1000)
4.SERIALIZABLE (可串行化)
会话1(设置级别)
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
会话2(查看级别)
select @@tx_isolation
select * from ta; (获取age为500)
start TRANSACTION; (开启事务,更新ID为1的记录的age为1000,并开启读锁,此时会话2不能新增行,杜绝了幻象读的状态)
update ta set age=1000 where id =1;
会话1
start TRANSACTION;
select * from ta;(开启事务,处于等待状态，并开启读锁,此时会话1不能新增行,杜绝了幻象读的状态)
会话2
COMMIT;(会话1SQL执行完毕,获得age为1000)
5.锁
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。
锁的级别:
1.读锁（共享锁）
2.写锁（排他锁）
锁的粒度:
1.行级锁
2.表级锁
3.页面锁
锁的功能:
1.乐观锁
2.悲观锁
行级锁
1.快照读
会话1
start transaction;
在会话1查看ID为1的age，为500。
select * from td where id =1;
会话2
更新ID为1的age为1000
update td set age=1000 where id=1;
在会话2查看ID为1的age已经更新为1000。
select * from td where id =1;
会话1
在会话1查看ID为1的age，仍然为500。
select * from td where id =1;
在会话1提交事务
COMMIT;
在会话1查看ID为1的age，已经为1000。
2.当前读
会话1
start transaction;
给select语句添加共享锁。
select * from td where id=1 lock in share mode;
会话2
更新ID为1的age的值为100，进入锁等待
update td set age=100 where id=1;
会话1
提交事务
COMMIT;
会话2的更新操作成功。
表级锁
1.表级读锁
对表加READ锁
lock tables tc read;
加锁后只可以查询已经加锁的表，
select * from tc;
查询没有加锁的表将失败
select * from ta;
打开会话2，对已经加锁的表进行查询，成功。
select * from tc;
对加锁的表tc进行更新操作，将失败
update tc set age=100 where id=1;
会话1中使用LOCK TABLE命令给表加了读锁，会话1可以查询锁定表中的记录，但更新或访问其他表都会提示错误；会话2可以查询表中的记录，但更新就会出现锁等待。
在会话1对表进行解锁，会话2的更新操作成功。
unlock tables;
在会话1，再次锁定表tc，后面带local参数。
lock tables tc read local;
Local参数允许在表尾并发插入，只锁定表中当前记录，其他会话可以插入新的记录
在会话2插入一条记录
insert into tc values(2, '唐僧', 20);
在会话1查看tc表的记录，无插入记录
select * from tc;
2.表级读锁并发性
READ锁是共享锁，不影响其他会话的读取，但不能更新已经加READ锁的数据。MyISAM表的读写是串行的，但是总体而言的，在一定条件下，MyISAM表也支持查询和插入操作的并发进行。
MyISAM存储引擎有一个系统变量concurrent_insert，用以控制其并发插入的行为，其值分别可以为0、1或2。
0：不允许并发操作
1：如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录，是MySQL的默认设置。
2：无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
在MySQL配置文件添加，concurrent_insert=2，重启mySQL服务设置生效。
设置concurrent_insert为0
在会话1对表tc加锁
lock tables tc read local;
在会话2插入一条记录，此时tc表被锁定，进入等待
insert into tc values(4, '沙悟净', 30);
在会话1解锁表tc，此时会话2插入成功
unlock tables;
设置concurrent_insert为1
在会话1删除ID为3的记录
delete from tc where id=3;
在会话1对表tc加锁
lock tables tc read local;
在会话2插入一条记录，此时tc表被锁定，并且表中有空洞，进入等待
insert into tc values(5, '白骨精', 1000);
在会话1解锁表tc，此时会话2插入成功，此时表中已经没有空洞
unlock tables;
在会话1对表tc加锁
lock tables tc read local;
在会话2插入一条记录，插入成功，支持有条件并发插入
insert into tc values(6, '白骨精', 1000);
在会话1解锁表tc
unlock tables;
设置concurrent_insert为2
在会话1删除ID为5的记录，创造一个空洞
delete from tc where id=5;
在会话1对表tc加锁
lock tables tc read local;
在会话2插入一条记录，插入成功，支持无条件并发插入
insert into tc values(7, '蜘蛛精', 1000);
在会话1解锁表tc
unlock tables;
乐观锁
乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。
通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。
1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods
set status=2,version=version+1
where id=#{id} and version=#{version};
悲观锁
与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作.
6.外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
创建表customerinfo
CREATE TABLE customerinfo(
CustomerIDINT NOT NULL,
PRIMARYKEY(CustomerID)
)TYPE=INNODB;
创建表salesinfo
CREATE TABLE salesinfo(
SalesIDNT NOT NULL,CustomerIDINT NOT NULL,
PRIMARYKEY(CustomerID,SalesID),
FOREIGNKEY(CustomerID) REFERENCES customerinfo(CustomerID) ON DELETE CASCADE
)TYPE=INNODB;
注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，
salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表
的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREA
TETABLE语句中加上TYPE=INNODB。如例中所示
7.索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。
那该对哪些字段建立索引呢？
一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况
例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。
8、优化的查询语句
1 不使用子查询
例：SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);
子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。
在MariaDB10/MySQL5.6版本里，采用join关联方式对其进行了优化，这条SQL会自动转换为
SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;
但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，固生产环境应避免使用子查询
2 避免函数索引
低效查询
SELECT * FROM t WHERE YEAR(d) >= 2016;
由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描。
高效查询
SELECT * FROM t WHERE d >= ‘2016-01-01’;
3 用IN来替换OR
低效查询
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
高效查询
SELECT * FROM t WHERE LOC_IN IN (10,20,30);
4 LIKE双百分号无法使用到索引
低效查询
SELECT * FROM t WHERE name LIKE ‘%de%’;
高效查询
SELECT * FROM t WHERE name LIKE ‘de%’;
目前只有MySQL5.7支持全文索引（支持中文）
5 读取适当的记录LIMIT M,N
低效查询
SELECT * FROM t WHERE 1;
高效查询
SELECT * FROM t WHERE 1 LIMIT 10;
6 避免数据类型不一致
低效查询
SELECT * FROM t WHERE id = ’19’;
高效查询
SELECT * FROM t WHERE id = 19;
7 分组统计可以禁止排序
低效查询
SELECT goods_id,count(*) FROM t GROUP BY goods_id;
默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。
高效查询
SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;
8 避免随机取记录
低效查询
SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;
MySQL不支持函数索引，会导致全表扫描
高效查询
SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;
9 禁止不必要的ORDER BY排序
低效查询
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;
高效查询
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;
10 批量INSERT插入
低效查询
INSERT INTO t (id, name) VALUES(1,’Bea’);
INSERT INTO t (id, name) VALUES(2,’Belle’);
INSERT INTO t (id, name) VALUES(3,’Bernice’);
高效查询
INSERT INTO t (id, name) VALUES(1,’Bea’), (2,’Belle’),(3,’Bernice’);
网友评论