Mysql的sql优化⽅法
Mysql的sql优化⽅法
1、选择最合适的字段属性
Mysql是⼀种关系型数据库，可以很好地⽀持⼤数据量的存储，但是⼀般来说，数据库中的表越⼩，在它上⾯执⾏的查询也就越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度舍得尽可能⼩。
例如：在定义这个字段时，如果将其设置为char(255)，显然给数据库增加了不必要的空间，甚⾄使⽤varchar这种类型也是多余的，因为char(6)就可以很好地完成了任务。同样的如果可以的话，我们应该是⽤MEDIUMINT⽽不是BIGINT来定义整形字段。
2、尽量把字段设置为NOT NULL
在可能的情况下，尽量把字段设置为NOT NULL，这样在将来执⾏查询的时候，数据库不⽤去⽐较NULL值。对于某些⽂本字段来说，例
如“省份”或者“性别”，我们可以将他们定义为ENUM(枚举)类型。因为在MySQL中，ENUM类型被当做数
值型数据来处理，⽽数值型数据被处理起来的速度要⽐⽂本类型要快得多。这样我们⼜可以提⾼数据库的性能。
3、使⽤连接(JOIN)来代替⼦查询(Sub-Queries)
MySQL从4.1开始⽀持SQL的⼦查询。这个技术可以使⽤select语句来创建⼀个单例的查询结果，然后把这个结果作为过滤条件⽤在另⼀个查询中。
如果使⽤连接(JOIN)来完成这个⼯作，速度将会快很多，尤其是当表中建有索引的话，性能将会更好。
连接(JOIN)之所以更有效率⼀些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上 需要两个步骤的查询⼯作。
另外：如果你的应⽤程序有很多JOIN查询，你应该确认两个表中JOIN的字段是被建⽴过索引的。这样MySQL内部 会启动为你优化JOIN的SQL语句的机制。⽽且这些被⽤来JOIN的字段，应该是相同的类型的。例如：如果你要把DECIMAL字段和⼀个INT字段JOIN在⼀
起，MySQL就⽆法使⽤他们的索引。对于那些STRING类型，还需要有相同的字符集才⾏。(两个表的字符集可能不相同)。
inner join内连接也叫做等值连接，left/right join是外链接。
SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id=B.id;
SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;
SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;
经过多⽅⾯的证实inner join性能⽐较快，因为inner join是等值连接，或许返回的⾏数⽐较少。但是我们要记得有些语句隐形的⽤到了等值连接，如：
SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;
推荐：能⽤inner join连接诶就⽤inner join连接。
sql中的连接查询有inner join(内连接）、left join(左连接)、right join（右连接）、full join（全连接(mysql 不⽀持)）四种⽅式，它们之间其实并没有太⼤区别，仅仅是查询出来的结果有所不同
4、使⽤联合(UNION)来代替⼿动创建的临时表
MySQL从4.0版本开始⽀持union查询，他可以把需要使⽤临时表的两条或更多的select查询合在⼀个
查询中。在客户端查询会话结束的时候，临时表会被⾃动删除，从⽽保证数据库整齐、⾼效。使⽤union来创建查询的时候，我们只需要⽤union作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数⽬要相同
shellifl当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候尽量使⽤union all⽽不是union，因为union和union all的差异主要是前者需要将两个或者多个结果集合并后再进⾏唯⼀性过滤操作，这就会涉及到排序，增加⼤量的CPU运算，增⼤资源消耗及延迟。
5、事务
尽管我们可以使⽤⼦查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询，但不是所有的数据库操作，都可以只⽤⼀条或少数⼏条就可以完成的。更多的时候是需要⽤⼀系列的语句来完成某种⼯作。但是在这种情况下，当这个语句块中的某⼀条语句运⾏出错的时候，整个语句块的操作就会变得不确定起来。
设想⼀下，要把某个数据同时插⼊两个相关联的表中，可能会出现这样的情况：第⼀个表中成功更新后，数据库突然出现意外状况，造成第⼆个表中的操作没有完成，这样就会造成数据的不完整，甚⾄会破坏数据库中的数据。要避免这种情况，就应该使⽤事务，它的作⽤是要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中的数据的⼀致性和完整性。事务以BEGIN关
键字开始，COMMIT关键字结束。在这之间的⼀条SQL语句操作失败，那么R ollback命令就可以把数据库恢复到begin开始之前的状态。
BEGIN; INSERTINTOsalesinfoSETCustomerID=14;UPDATEinventorySETQuantity=11WHEREitem=‘book’;COMMIT;
事务的另⼀个作⽤是当多个⽤户同时使⽤相同的数据源时，他可以使⽤锁定数据库的⽅式来为⽤户提供⼀种安全的访问机制，这样可以保证⽤户的操作不被其它的⽤户所⼲扰。
js替换函数
⼀般来说，事务必须满⾜四个条件(ACID)：原⼦性(Atomicity，或称不可分割性)、⼀致性(Consistency)、隔离性(Isolation,⼜称独⽴性)、持久性(Durability).
原⼦性：⼀个事物(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执⾏过程中发⽣错误，会被回滚(Rollback)到事务开始的状态，就像这个事务从来没有执⾏过⼀样。
⼀致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。这表⽰写⼊的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以⾃发性地完成预定的⼯作。
隔离性：数据库允许多个事务同时对其数据进⾏读写和修改的能⼒，隔离性可以防⽌多个事务并发执
⾏时由于交叉执⾏⽽导致数据的不⼀致。事务隔离分为不同的级别，包括读未提交(Read uncommitted)、读已提交(Read committed)、可重复读(repeateable read)和串⾏化(Serializable).
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
事务的并发问题：
1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据就是脏数据
2、不可重复读：事务A多次读取同⼀事物，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同⼀数据时，结果不⼀致。
3、幻读：系统管理员A将数据库中的所有学⽣的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插⼊了⼀条具体分数的记录，当系统管理员A改结束后发现还有⼀条记录没有改过来，就好像发⽣了幻觉⼀样，这就叫幻读。
⼩结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满⾜条件的⾏，解决幻读需要锁表
MySQL事务隔离级别
事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
不可重复读（read-committed） 否 是 是
可重复读（repeatable-read） 否 否 是
串⾏化（serializable） 否 否 否
事务控制语句：
BEGIN或START TRANSACTION：显式的开启⼀个事物。
COMMIT：也可以使⽤COMMIT WORK，不过⼆者是等价的。COMMIT会提交事务，并使已对数据库进⾏的所有修改成为永久性的。
Rollback：也可以使⽤Rollback work，不过⼆者是等价的。回滚会结束⽤户的事务，并撤销正在进⾏的所有未提交的修改。
SAVEPOINT identifier：SAVEPOINT允许在事务中创建⼀个保存点，⼀个事务中可以有很多个SAVEP
OINT；
RELEASE SAVEPOINT identifier：删除⼀个事物的保存点，当没有指定的保存点时，执⾏该语句会抛出⼀个异常。
ROLLBACK TO inditifier：把事务回滚到标记点。
SET TRANSACTION:⽤来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE RE AD和SERLALIZABLE。
6、使⽤索引
索引是提⾼数据库性能的常⽤⽅法，他可以令数据库服务器⽐没有索引快得多的速度检索特定的⾏，尤其是在查询语句当中包含有
MAX(),MIN()和ORDERBY这些命令的时候，性能提⾼更为明显。
那该对那些字段进⾏索引呢？
⼀般来说，索引应该建⽴在那些将⽤于join，where判断和orderby排序的字段上。尽量不要对数据库中
某个含有⼤量重复的值的字段建⽴索引，对于⼀个ENUM类型的字段来说，出现⼤量重复值是很有可能的情况。
例如customerinfo中的“province”…字段，在这样的字段上建⽴索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使⽤ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始⽀持全⽂索引和搜索。全⽂索引在MySQL中是⼀个FULLTEXT类型索引，但仅能⽤于MyISAM类型的表。对于⼀个⼤的数据库，将数据装载到⼀个没有FULLTEXT索引的表中，然后再使⽤ALTERTABLE或CREATEINDEX创建索引，将是⾮常快的。但如果将数据装载到⼀个已经有FULLTEXT索引的表中，执⾏过程将会⾮常慢。
7、优化de的查询语句
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⽀持全⽂索引（⽀持中⽂）
使⽤不等于查询,
列参与了数学运算或者函数
在字符串like时左边是通配符.类似于’%aaa’.
当mysql分析全表扫描⽐使⽤索引快的时候不使⽤索引.
当使⽤联合索引，前⾯⼀个条件为范围查询，后⾯的即使符合最左前缀原则，也⽆法使⽤索引.
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 避免随机取记录
alter用法及搭配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 ate_time DESC;—–>
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;
10 批量INSERT插⼊
mysql怎么读英语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’);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。