在做sql优化之前，数据库里面的表要设计好，这个是决定sql性能的上限的前提，不然后期无论怎么优化，优化空间都不大。
谈谈数据库表的设计，谈表设计之前简单了解下数据库里面2个重要的概念数据库锁和事务
1：锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
锁对数据库而言显得尤其重要，也更加复杂。
MySQL的锁机制比较简单
其最显著的特点是不同的存储引擎支持不同的锁机制
比如：
MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低
表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如OLAP系统
InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高
行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
这里补充下没有最好的锁，只能最适合的锁，没有最强英雄但是有最强玩家，玩的转才行，当然要适应版本，逆版本注定被吊锤
Mysql的表级锁有2种模式：
表共享读锁(Table Read Lock)
表独占写锁 (Table Write Lock)
MyISAM的表锁-共享读锁
加共享读锁 ：lock table 'aa' read 加独占写锁：lock table 'bb' write
效果总结 --读共享，写独占的业务场景如下
对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
对MyISAM表的读操作，不会阻塞当前session对表读，当对表进行修改会保存
一个session使用LOCK TABLE命令给表f加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误；另外一个session可以查询表中的记录，但更新就会出现锁等待
对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；对 MyISAM表的写操作，当前session可以对本表做CRUD,但对其他表进行操作会报错
InnoDB引擎支持行锁--读共享，写独占的业务场景如下
共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁
排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁
上共享锁的写法：lock in share mode
例如： select * from 表 where 条件 lock in share mode
上排它锁的写法：for update
例如：select * from 表 where 条件 for update；
注意如下：
1.两个事务不能锁同一个索引。
2.insert ，delete ， update在事务中都会自动默认加上排它锁。
3.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
总结：InnoDB表锁和MyISAM差别不大；开启一个新事务解锁表，调用了unlock()
面试题：系统运行一段时间，数据量已经很大，这时候系统升级，有张表A需要增加个字段，并发量白天晚上都很大，请问怎么修改表结构？补充个不知道的知识点（触发器（TRIGGER）是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时，会激活促发其执行相应的操作。）
1. 首先创建一个和你要执行的alter操作的表一样的空的表结构。
2. 执行我们赋予的表结构的修改，然后copy原表中的数据到新表里面。
3. 在原表上创建一个触发器在数据copy的过程中，将原表的更新数据的操作全部更新到新的表中来。
4. copy完成之后，用rename table 新表代替原表，默认删除原表。:
2：事务
为什么需要事务呢？事务可以做到代码层面不能做到的事情
如
现在的很多软件都是多用户，多程序，多线程的，对同一个表可能同时有很多人在用，为保持数据的一致性，所以提出了事务的概念。
A 给B 要划钱，A 的账户-1000元， B 的账户就要+1000元，这两个update 语句必须作为一个整体来执行，不然A 扣钱了，B 没有加钱这种情况很难处理。
查询数据库存储引擎支持的哪些事务？
1.查看数据库下面是否支持事务（InnoDB支持）？
show engines;
2.查看mysql当前默认的存储引擎？
show variables like '%storage_engine%';
3.查看某张表的存储引擎？
show create table 表名 ;
4.对于表的存储结构的修改？
建立InnoDB 表：Create table .... type=InnoDB； Alter table table_name type=InnoDB;
四大特性：ACID
原子性：即被事务包围的操作要么都成功， 要么都失败，没有中间态
一致性：事务必须是数据库从一个一致性状态到另外一个一致性状态，简而言之就是A与B之间转账，不了发生了多少次转账，A与B的账户总额还是不变即事务开始前和结束后，数据完整性没有被破坏（个人觉得他这个抽象层次有问题，既然保证了原子性，一致性必然也存在的呀）
隔离性：隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响（并不是数据库的角度完全能解决，会有一些硬件层面的知识）
说完锁和事务就谈数据库的设计
数据库设计的三大范式
第一大范式：1: 数据库表中的所有字段都只具有单一属性--一个列只有一个值
2:单一属性的列是由基本数据类型所构成的
3 : 设计出来的表都是简单的二维表
第二大范式：要求表中只具有一个业务主键，也就是说符合第二范式的表不能存在非主键列只对部分主键的依赖关系，这个主键值可以唯一确定这条记录如订单表里用户姓名为主键，然后产生多了订单列这也是不合理的
第三大范式：指每一个非非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上相处了非主键对主键的传递依赖如订单表里面除了订单号主键列，还有客户编号和客户姓名，这样就是不合理的
但是如果完全满足范式设计也不太能满足优良的SQL查询性能，所以提出了反范式设计
允许冗余，用空间换取时间，具体如何设计表需要更深入的理解业务
下面我们进入正式优化sql阶段，大概流程就是通过工具分析sql日志，分析执行计划，然后分步骤优化
ü慢查询日志，顾名思义，就是查询慢的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。
常见的命令
slow_query_log 启动停止技术慢查询日志
slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
long_query_time 指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
log_queries_not_using_indexes 是否记录未使用索引的SQL
log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE]
分析日志文件的2大工具--mysqldumpslow/pt_query_digest
mysqldumpslow.pl -s t -t 10 D:\DESKTOP-2EKGEE5-slow.log
pt-query-digest --explain h=127.0.0.1, u=root,p=password slow-mysql.log
通过工具分析得到哪些sql需要优化，把他一一整理出来，然后进行优化
索引的本质：索引的本质就是数据结构
索引分类：（这块我也不太清楚他这个分类的标准）
普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列
聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。
非聚簇索引：不是聚簇索引，就是非聚簇索引
show global variables like "%datadir%";
常见的语法：
查看索引
SHOW INDEX FROM table_name\G
创建索引
CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));
ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
删除索引
DROP INDEX [indexName] ON mytable;
执行计划是什么
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
语法
Explain + SQL语句 会有好多列展示出来，查看每一列的含义：
执行计划--id
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
执行计划--select_type
SIMPLE 简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为
SUBQUERY 在SELECT或WHERE列表中包含了子查询
DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询, 把结果放在临时表里。
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT 从UNION表获取结果的SELECT
执行计划--type
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
需要记忆的
system（一行记录）>const(表示通过索引一次就找到了)>eq_ref(唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描)>ref(本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体)>range(本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体)>index()>ALL(全表扫描)
执行计划--possible_keys
实际使用的索引。如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引和查询的select字段重叠
执行计划--possible_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
执行计划-ref 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
执行计划-rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
执行计划 -Extra 包含不适合在其他列中显示但十分重要的额外信息
1：Using filesort
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为“文件排序
2：Using temporary
使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
3：USING index
表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
4：Using where
表明使用了where过滤
5：using join buffer
使用了连接缓存：
6：impossible where
where子句的值总是false，不能用来获取任何元组
常见的优化标准：
全职匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断，
索引上面少计算，范围之后全失效，、
Like百分写最后，覆盖索引不写*，
不等空值还有OR,索引影响要注意，
varchar引号不可丢，SQL优化有诀窍
用union代替or查询更加有效
sql优化今天就到这里，实操还有很多东西要注意！
对于order by的查询字段，需要加索引
746
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
