01.索引是越多越好吗？
索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能，
因为在表中的数据更改时，索引也会进行调整和更新。
02.说出一些数据库优化方面的经验?
有外键约束的话会影响增删改的性能，如果应用程序可以保证数据库的完整性那就去除外键
Sql语句全部大写，特别是列名大写，因为数据库的机制是这样的，sql语句发送到数据库服务器，数据库首先就会把sql编译成大写在执行，如果一开始就编译成大写就不需要了把sql编译成大写这个步骤了
如果应用程序可以保证数据库的完整性，可以不需要按照三大范式来设计数据库
其实可以不必要创建很多索引，索引可以加快查询速度，但是索引会消耗磁盘空间
如果是jdbc的话，使用PreparedStatement不使用Statement，来创建SQl，PreparedStatement的性能比Statement的速度要快，使用PreparedStatement对象SQL语句会预编译在此对象中，PreparedStatement对象可以多次高效的执行
03.怎么优化SQL查询语句
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
用索引可以提高查询
SELECT子句中避免使用*号，尽量全部大写SQL
应尽量避免在 where 子句中对字段进行 is null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，使用 IS NOT NULL
where 子句中使用 or 来连接条件，也会导致引擎放弃使用索引而进行全表扫描
in 和 not in 也要慎用，否则会导致全表扫描
04.你怎么知道SQL语句性能是高还是低
查看SQL的执行时间
使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
-------------------------------------------------------------------------------------------------------------
01.SQL的执行顺序
FROM：将数据从硬盘加载到数据缓冲区，方便对接下来的数据进行操作。
WHERE：从基表或视图中选择满足条件的元组。（不能使用聚合函数）
JOIN（如right left 右连接-------从右边表中读取某个元组，并且找到该元组在左边表中对应的元组或元组集）
ON：join on实现多表连接查询，推荐该种方式进行多表查询，不使用子查询。
GROUP BY：分组，一般和聚合函数一起使用。
HAVING：在元组的基础上进行筛选，选出符合条件的元组。（一般与GROUP BY进行连用）
SELECT：查询到得所有元组需要罗列的哪些列。
DISTINCT：去重的功能。
UNION：将多个查询结果合并（默认去掉重复的记录）。
ORDER BY：进行相应的排序。
LIMIT 1：显示输出一条数据记录（元组）
02.数据库结构优化
一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。
需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。
将字段很多的表分解成多个表
对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。
因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。
通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。
增加冗余字段
设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。
但是，合理的加入冗余字段可以提高查询速度。
表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。
-------------------------------------------------------------------------------------------------------------
01.如何优化查询过程中的数据访问
访问数据太多导致查询性能下降
确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
确认MySQL服务器是否在分析大量不必要的数据行
避免犯如下SQL语句错误
避免查询不需要的数据。解决办法：使用limit解决
多表关联返回全部列。解决办法：指定列名
总是返回全部列。解决办法：避免使用SELECT *
重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存
使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化：
使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。
改变数据库和表的结构，修改数据表范式
重写SQL语句，让优化器可以以更优的方式执行查询。
02.如何优化长难的查询语句
分析是一个复杂查询还是多个简单查询速度快
MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多
使用尽可能小的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。
将一个大的查询分为多个小的相同的查询
一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销。
分解关联查询，让缓存的效率更高。
执行单个查询可以减少锁的竞争。
在应用层做关联更容易对数据库进行拆分。
查询效率会有大幅提升。
较少冗余记录的查询。
03.优化特定类型的查询语句
count(*)会忽略所有的列，直接统计所有列数，不要使用count(列名)
MyISAM中，没有任何where条件的count(*)非常快。
当有where条件时，MyISAM的count统计不一定比其它引擎快。
可以使用explain查询近似值，用近似值替代count(*)
增加汇总表
使用缓存
04.优化关联查询
确定ON或者USING子句中是否有索引。
确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引。
05.优化子查询
用关联查询替代
优化GROUP BY和DISTINCT
这两种查询据可以使用索引来优化，是最有效的优化方法
关联查询中，使用标识列分组的效率更高
如果不需要ORDER BY，进行GROUP BY时加ORDER BY NULL，MySQL不会再进行文件排序。
WITH ROLLUP超级聚合，可以挪到应用程序处理
06.优化LIMIT分页
LIMIT偏移量大的时候，查询效率较低
可以记录上次查询的最大ID，下次查询时直接根据该ID来查询
07.优化UNION查询
UNION ALL的效率高于UNION
08.优化WHERE子句
多数数据库都是从左往右的顺序处理条件的，把能够过滤更多数据的条件放到前面，把过滤少的条件放在后面
-------------------------------------------------------------------------------------------------------------
01.大表数据查询，怎么优化
优化shema、sql语句+索引；
第二加缓存，memcached, redis；
主从复制，读写分离；
垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；
02.大表怎么优化？分库分表了是怎么做的？分表分库了有什么问题？有用到中间件么？他们的原理知道么？
当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：
限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。；
读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；
---------------------------------------------------------------------------------------------------------
还有就是通过分库分表的方式进行优化，主要有垂直分区、垂直分表和水平分区、水平分表
垂直分区
根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，
可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。
简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。
垂直分表
把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中
适用场景
1、如果一个表中某些列常用，另外一些列不常用
2、可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数
水平分区
保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。
水平拆分可以支撑非常大的数据量。
水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。
举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。
水平分表
表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询次数
4.4 创建、删除、查询
文章参考：https://www.cnblogs.com/annsshadow/p/5037667.html1.索引分类 主键索引：不能重复 id不能是null 唯一索引：不能重复 id可以是null 单值索引：单列age 一个表可以多个单值索引 复合索引：多个列构成的索引 相当于二级目录(name,age)、(a,b,c,d,...,n)2.创建索引 方式一：create 索引类型 索引名 on 表（字段） a.主键 create table tb( id int(4) auto_increment, name varchar(5), dept varchar(5), primary key(id) )ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; b.单值 create index dept_index on tb(dept); c.唯一 create unique index name_index on tb(name); d.复合索引 create index dept_name_index on tb(dept,name); 方式二：alter table 表名 索引类型 索引名（字段） a.主键 alter table tb add primary key(id); b.单值 alter table tb add index dept_index(dept); c.唯一 alter table tb add unique index name_index(name); d.复合索引 alter table tb add index dept_name_index(dept,name); 注意：如果一个字段是primary key，则改字段默认就是“主键索引” 3.删除索引 drop index 索引名 on 表名 ; drop index name_index on tb ;4.查询索引 show index from 表名; show index from 表名\G
5 MySQL执行计划
1.创建表create table course( cid int(3), cname varchar(20), tid int(3));create table teacher( tid int(3), tname varchar(20), tcid int(3));create table teacherCard( tcid int(3), tcdesc varchar(200));2.准备数据insert into course values(1,'java',1);insert into course values(2,'html',1);insert into course values(3,'sql',2);insert into course values(4,'web',3);insert into teacher values(1,'tz',1);insert into teacher values(2,'tw',2);insert into teacher values(3,'tl',3);insert into teacherCard values(1,'tzdesc');insert into teacherCard values(2,'twdesc');insert into teacherCard values(3,'tldesc');
5.1 id：编号
5.2 select_type：查询类型
5.3 type：索引类型
5.4 possible_keys / key / key_len
5.5 ref / rows / Extra
6 优化案例
6.1 单表优化
1.创建表create table book( bid int(4) primary key, name varchar(20) not null, authorid int(4) not null, publicid int(4) not null, typeid int(4) not null);2.准备数据insert into book values(1,'tjava',1,1,2) ;insert into book values(2,'tc',2,1,2) ;insert into book values(3,'wx',3,2,1) ;insert into book values(4,'math',4,2,3) ; 3.需要优化的SQL：查询authorid=1且 typeid为2或3的 bidexplain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc ;优化一：按照上述的SQL中出现的字段，依次加上索引alter table book add index idx_bta (bid,typeid,authorid);drop index idx_bta on book; --索引一旦进行升级优化，需要将之前废弃的索引删掉，防止干扰。优化二：根据SQL实际解析的顺序，调整索引的顺序：alter table book add index idx_tab (typeid,authorid,bid); --虽然可以回表查询bid，但是将bid放到索引中 可以提升使用using index ;drop index idx_tab on book; --索引一旦进行 升级优化，需要将之前废弃的索引删掉，防止干扰优化三（之前是index级别），因为范围查询in有时会失效，因此交换索引的顺序，将typeid in(2,3)放到最后alter table book add index idx_atb (authorid,typeid,bid);4.小结：a.(using filesort) 最佳做前缀，保持索引的定义和使用的顺序一致性 b.(index级别) 根据SQL实际解析的顺序，逐步优化索引 c.(ref级别) 将含In的范围查询 放到 where条件的最后，防止失效5.本例中同时出现了Using where（需要回原表）; Using index（不需要回原表）？ a.Using where（需要回原表）： where authorid=1 and typeid in(2,3)中authorid在索引(authorid,typeid,bid)中，因此不需要回原表（直接在索引表中能查到） b.Using index（不需要回原表）： 而typeid虽然也在索引(authorid,typeid,bid)中，但是含in的范围查询已经使该typeid索引失效，因此相当于没有typeid这个索引，所以需要回原表（using where）6.验证本次SQL优化中，一半需要回原表，一半不需要回原表？ 证明一： explain select bid from book where authorid=1 and typeid =3 order by typeid desc ;---使用 typeid=3 代替 typeid in(2,3) ，没有了In，则不会出现using where 证明二： 通过key_len证明In可以使索引失效
6.2 两表优化
1.创建表create table teacher2( tid int(4) primary key, cid int(4) not null);create table course2( cid int(4) , cname varchar(20));2.准备数据insert into teacher2 values(1,2);insert into teacher2 values(2,1);insert into teacher2 values(3,3);insert into course2 values(1,'java');insert into course2 values(2,'python');insert into course2 values(3,'kotlin');3.需要优化的SQLexplain select *from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java4.优化：加索引alter table teacher2 add index index_teacher2_cid(cid) ; alter table course2 add index index_course2_cname(cname);
6.3 三表优化
1.创建表create table test03( a1 int(4) not null, a2 int(4) not null, a3 int(4) not null, a4 int(4) not null);2.增加索引alter table test03 add index idx_a1_a2_a3_4(a1,a2,a3,a4) ;3.测试SQL--推荐写法，因为索引的使用顺序（where后面的顺序）和复合索引的顺序一致explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a3=3 and a4 =4 ; --虽然编写的顺序 和索引顺序不一致，但是 sql在真正执行前经过了SQL优化器的调整，结果与上条SQL一致explain select a1,a2,a3,a4 from test03 where a4=1 and a3=2 and a2=3 and a1 =4 ; --using index：用到了a1 a2两个索引，该两个字段不需要回表查询--using where：因为a4跨列使用，造成了该索引失效，需要回表查询explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=4 order by a3; --using filesort(文件内排序，“多了一次额外的查找/排序”) ：不要跨列使用( where和order by 拼起来，不要跨列使用)explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a3; --无using filesortexplain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a2 , a3;4.总结 a.如果复合索引 (a,b,c,d) 和 使用的顺序全部一致(且不跨列使用)，则复合索引全部使用 如果部分一致(且不跨列使用)，则使用部分索引 b.where和order by 拼起来，不要跨列使用
6.4 避免索引失效的一些原则
1.复合索引 a.复合索引，不要跨列或无序使用（最佳左前缀） b.复合索引，尽量使用全索引匹配2.不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效explain select * from book where authorid = 1 and typeid = 2 ;--用到了a、t 2个索引explain select * from book where authorid = 1 and typeid*2 = 2 ;--用到了a 1个索引explain select * from book where authorid*2 = 1 and typeid*2 = 2 ;--用到了0个索引explain select * from book where authorid*2 = 1 and typeid = 2 ;--用到了0个索引，原因：对于复合索引，如果左边失效，右侧全部失效。例如复合索引(a,b,c)，如果 b失效，则b c同时失效。-- 单值索引drop index idx_atb on book ; --丢弃复合索引alter table book add index idx_authroid (authorid) ; --增加单值索引authoridalter table book add index idx_typeid (typeid) ; --增加单值索引typeidexplain select * from book where authorid*2 = 1 and typeid = 2 ;3.复合索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效。-- SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。explain select * from book where authorid = 1 and typeid =2 ;explain select * from book where authorid != 1 and typeid =2 ;explain select * from book where authorid != 1 and typeid !=2 ;、--体验概率情况(< > =)：原因是服务层中有SQL优化器，可能会影响我们的优化。drop index idx_typeid on book;drop index idx_authroid on book;alter table book add index idx_book_at (authorid,typeid);explain select * from book where authorid = 1 and typeid =2 ;--复合索引at全部使用explain select * from book where authorid > 1 and typeid =2 ; --复合索引中如果有>，则自身和右侧索引全部失效。explain select * from book where authorid = 1 and typeid >2 ;--复合索引at全部使用----明显的概率问题---explain select * from book where authorid < 1 and typeid =2 ;--复合索引at只用到了1个索引explain select * from book where authorid < 4 and typeid =2 ;--复合索引全部失效-- 我们学习索引优化 ，是一个大部分情况适用的结论，但由于SQL优化器等原因，该结论不是100%正确。-- 一般而言， 范围查询（> < in）之后的索引失效4.补救措施。100%概率，尽量使用索引覆盖（using index）select a,b,c from xx..where a= .. and b =.. ; --复合索引（a,b,c）5.like尽量以“常量”开头，不要以'%'开头，否则索引失效explain select * from teacher where tname like '%x%'; --tname索引失效explain select * from teacher where tname like 'x%' --tname索引成功explain select tname from teacher where tname like '%x%'; --如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖挽救一部分。6.尽量不要使用类型转换（显示、隐式），否则索引失效explain select * from teacher where tname = 'abc' ;explain select * from teacher where tname = 123 ; --程序底层将 123 -> '123'，即进行了类型转换，因此索引失效7.尽量不要使用or，否则索引失效explain select * from teacher where tname ='' or tcid >1 ; --将or左侧的tname失效
6.5 一些其他的优化方法
1.exist和in select ..from table where exist (子查询) ; --如果子查询的数据集大，则使用exist，效率高 select ..from table where 字段 in (子查询) ; --如果主查询的数据集大，则使用in，效率高。 a.exist语法 将主查询的结果，放到子查需结果中进行条件校验（看子查询是否有数据，如果有数据，则校验成功）， 如果符合校验，则保留数据； select tname from teacher where exists (select * from teacher) ; select tname from teacher where exists (select * from teacher where tid =9999) ; b.in语法 select ..from table where tid in (1,3,5) ;2.order by：using filesort 有两种算法：双路排序、单路排序（根据IO的次数） a.MySQL4.1之前，默认使用双路排序：扫描2次磁盘（1：从磁盘读取排序字段 ,对排序字段进行排序（在buffer中进行的排序） 2：扫描其他字段 ） b.MySQL4.1之后 默认使用单路排序：只读取一次（全部字段），在buffer中进行排序。但种单路排序 会有一定的隐患 （不一定真的是“单路|1次IO”，有可能多次IO）。原因：如果数据量特别大，则无法 将所有字段的数据 一次性读取完毕，因此 会进行“分片读取、多次读取”。 c.注意：单路排序比双路排序会占用更多的buffer。单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小：set max_length_for_sort_data = 1024 单位byte d.如果max_length_for_sort_data值太低，则mysql会自动从 单路->双路 （太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数） e.提高order by查询的策略： ①选择使用单路、双路 ；调整buffer的容量大小； ②避免select * ... ③复合索引 不要跨列使用 ，避免using filesort ④保证全部的排序字段 排序的一致性（都是升序 或 降序）
6.6 附：常见SQL
01.达梦（DM）
达梦数据库使用 EXPLAIN 和 EXPLAIN PLAN FOR 查看执行计划。以下是一个示例：
-- 示例 SQL 查询
EXPLAIN SELECT * FROM employees WHERE department_id = 10;
输出执行计划将会显示查询计划步骤，包括表扫描方式（如全表扫描或索引扫描）、估算的返回行数等信息。
02.金仓（Kingbase）
金仓数据库的执行计划命令类似 PostgreSQL。
使用 EXPLAIN 和 EXPLAIN ANALYZE 查看执行计划，ANALYZE 会实际执行查询以提供更精确的时间。
-- 示例 SQL 查询
EXPLAIN SELECT * FROM employees WHERE department_id = 10;
-- 使用 EXPLAIN ANALYZE 实际执行查询
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;
执行计划会显示各个操作步骤的成本、行数和扫描方式（如顺序扫描、索引扫描等）。
03.PostgreSQL
PostgreSQL 也使用 EXPLAIN 和 EXPLAIN ANALYZE 查看执行计划。
-- 仅查看执行计划
EXPLAIN SELECT * FROM employees WHERE department_id = 10;
-- 实际执行查询并显示详细执行时间
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;
输出会显示估算的成本、扫描方法、连接方式等。使用 ANALYZE 会提供实际执行的时间和行数。
04.Oracle
在 Oracle 数据库中，EXPLAIN PLAN 可以生成查询的执行计划，并存储在 PLAN_TABLE 表中，方便分析。
-- 将执行计划插入 PLAN_TABLE 表中
EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;
-- 查看 PLAN_TABLE 中的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
输出结果包含查询的步骤、操作类型（如全表扫描、索引扫描等）、估算成本和访问路径。
05.SQL Server
SQL Server 使用 SET SHOWPLAN_ALL 或 SET SHOWPLAN_XML 查看执行计划，不会实际执行查询。以下是一个示例：
-- 查看执行计划
SET SHOWPLAN_ALL ON;
GO
SELECT * FROM employees WHERE department_id = 10;
GO
SET SHOWPLAN_ALL OFF;
执行计划会显示操作步骤、行数估算和成本。可以使用 SHOWPLAN_XML 查看 XML 格式的计划。
06.MySQL
MySQL 中使用 EXPLAIN 或 EXPLAIN ANALYZE 查看执行计划。以下是一个简单的示例：
-- 仅查看执行计划
EXPLAIN SELECT * FROM employees WHERE department_id = 10;
-- 使用 ANALYZE 显示更详细的执行信息
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;
输出结果包括表的访问类型（如全表扫描、索引扫描）、查询的各个步骤和对应的成本估算。
7 SQL排查-慢查询日志
1.SQL排查 - 慢查询日志： MySQL提供的一种日志记录，用于记录MySQL种响应时间超过阀值的SQL语句（long_query_time，默认10秒） 默认关闭，建议：开发调优时打开，而最终部署时关闭2.检查是否开启了慢查询日志 show variables like '%slow_query_log%' ;3.临时在内存中开启 set global slow_query_log = 1 ;4.永久开启： vi /etc/my.cnf [mysqld] slow_query_log=1 slow_query_log_file=/var/lib/mysql/localhost-slow.log5.慢查询阀值： show variables like '%long_query_time%' ;6.临时设置阀值： set global long_query_time = 5 ; --设置完毕后，重新登陆后起效 （不需要重启服务）7.永久设置阀值： vi /etc/my.cnf [mysqld] long_query_time=38.测试 select sleep(4); select sleep(5); select sleep(3); select sleep(3); --查询超过阀值的SQL：show global status like '%slow_queries%' ; a.慢查询的sql被记录在了日志中，因此可以通过日志查看具体的慢SQL。 cat /var/lib/mysql/localhost-slow.log b.通过mysqldumpslow工具查看慢SQL，可以通过一些过滤条件 快速查找出需要定位的慢SQL mysqldumpslow --help c.语法：mysqldumpslow 各种参数 慢查询日志的文件 s：排序方式 r：逆序 l：锁定时间 g：正则匹配模式 --获取返回记录最多的3个SQLmysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log--获取访问次数最多的3个SQLmysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log--按照时间排序，前10条包含left join查询语句的SQLmysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log
8 海量数据
8.1 模拟海量数据
通过存储函数（无return）插入海量数据 / 通过存储过程（有return）插入海量数据1.创建表create database testdata;use testdata;create table dept( dno int(5) primary key default 0, dname varchar(20) not null default '', loc varchar(30) default '')engine=innodb default charset=utf8;create table emp( eid int(5) primary key, ename varchar(20) not null default '', job varchar(20) not null default '', deptno int(5) not null default 0)engine=innodb default charset=utf8;2.randstring(6) -> 用于模拟员工名称：存储函数delimiter $ create function randstring(n int) returns varchar(255) begin declare all_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; declare return_str varchar(255) default '' ; declare i int default 0 ; while i<n do set return_str = concat(return_str, substring(all_str, FLOOR(1+rand()*52),1)); set i=i+1 ; end while ; return return_str ;end $ 3.产生随机整数：存储函数create function ran_num() returns int(5)begin declare i int default 0; set i =floor( rand()*100 ) ; return i ;end $4.插入海量数据到emp表：存储过程create procedure insert_emp( in eid_start int(10),in data_times int(10))begin declare i int default 0; set autocommit = 0 ; repeat insert into emp values(eid_start + i, randstring(5) ,'other' ,ran_num()) ; set i=i+1 ; until i=data_times end repeat ; commit ;end $5.插入海量数据到dept表：存储过程create procedure insert_dept(in dno_start int(10) ,in data_times int(10))begin declare i int default 0; set autocommit = 0 ; repeat insert into dept values(dno_start+i ,randstring(6),randstring(8)) ; set i=i+1 ; until i=data_times end repeat ; commit ;end$6.插入数据delimiter; call insert_emp(1000,800000) ;call insert_dept(10,30) ;-- 更改结束符，SQL默认以;结束 本次修改为$结束delimiter; delimiter$-- 修改为一次性提交set autocommit = 0 ;--报错This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)原因：存储过程/存储函数在创建时 与之前的开启慢查询日志发生冲突解决冲突： 1.临时解决( 开启log_bin_trust_function_creators ) show variables like '%log_bin_trust_function_creators%'; set global log_bin_trust_function_creators = 1; 2.永久解决： /etc/my.cnf [mysqld] log_bin_trust_function_creators = 1
8.2 分析海量数据
1.profilesshow variables like '%profiling%';set profiling = on ; show profiles;--默认关闭，会记录所有profiling打开之后的全部SQL查询语句所花费的时间。缺点：不够精确，只能看到总共消费的时间，不能看到各个硬件消费的时间（cpu、io）2.精确分析：sql诊断show profile all for query 上一步查询的的Query_Idshow profile cpu,block io for query 上一步查询的的Query_Id3.全局查询日志：记录开启之后的全部SQL语句（这次全局的记录操作，仅仅在调优、开发过程中打开即可，在最终的部署实施时，一定会关闭）--开启全局日志show variables like '%general_log%';set global general_log = 1 ;--开启全局日志--方式一：设置将全部的SQL记录在mysql.general_log表set global log_output='table';select * from mysql.general_log ;--方式二：将执行的所有SQL记录在文件中set global log_output='file';set global general_log = on ;set global general_log_file='/tmp/general.log';
锁机制：解决因资源共享 而造成的并发问题1.示例：买最后一件衣服XA: X 买：衣服X加锁 -> 试衣服、下单、付款、打包 -> X解锁B: X 买：发现衣服X已被加锁，等待X解锁， X已售空2.操作类型： a.读锁（共享锁）： 对同一个数据（衣服），多个读操作可以同时进行，互不干扰。 b.写锁（互斥锁）： 如果当前写操作没有完毕（买衣服的一系列操作），则无法进行其他的读操作、写操作3.操作范围： a.表锁 ：一次性对一张表整体加锁。如MyISAM存储引擎使用表锁，开销小、加锁快；无死锁；但锁的范围大，容易发生锁冲突、并发度低 b.行锁 ：一次性对一条数据加锁。如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概率 发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题） c.页锁4.表锁： --自增操作 MYSQL/SQLSERVER支持；oracle需要借助于序列来实现自增create table tablelock(id int primary key auto_increment , name varchar(20))engine myisam;insert into tablelock(name) values('a1');insert into tablelock(name) values('a2');insert into tablelock(name) values('a3');insert into tablelock(name) values('a4');insert into tablelock(name) values('a5');--增加锁locak table 表1 read/write ,表2 read/write ,...--查看加锁的表show open tables ;--释放锁unlock tables ;--会话：session，每一个访问数据的dos命令行、数据库客户端工具都是一个会话------------------------------------------加读锁------------------------------------------会话1：lock table tablelock read ;select * from tablelock; --读（查）tablelock，可以delete from tablelock where id =1 ; --写（增删改）tablelock，不可以select * from emp ; --读其他表，不可以delete from emp where eid = 1; --写其他表，不可以--结论1：如果某一个会话对A表加了read锁，则该会话可以对A表进行读操作、不能进行写操作；且该会话不能对其他表进行读、写操作。即，如果给A表加了读锁，则当前会话只能对A表进行读操作。会话2（其他会话）：select * from tablelock; --读（查）tablelock表，可以delete from tablelock where id =1 ; --写，会“等待”会话0将锁释放select * from emp ; --读（查）其他表，可以delete from emp where eno = 1; --写，可以--结论2：会话1给A表加了锁；其他会话的操作：a.可以对其他表（A表以外的表）进行读、写操作；b.对A表：读-可以；写-需要等待释放锁。------------------------------------------加写锁--------------------------------------------结论1：如果某一个会话对A表加了read锁，则该会话可以对加了写锁的表 进行任何操作（增删改查）；但是不能 操作（增删改查）其他表--结论2：会话1给A表加了锁；其他会话的操作：a.可以对其他表（A表以外的表）进行读、写操作；b.对A表：读写(增删改查)-需要等待释放锁-------------------------------------MySQL表级锁的锁模式------------------------------------MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（DML）前，会自动给涉及的表加写锁，所以对MyISAM表进行操作，会有以下情况：a、对MyISAM表的读操作（加读锁），不会阻塞其他进程（会话）对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。b、对MyISAM表的写操作（加写锁），会阻塞其他进程（会话）对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。-----------------------------------------分析表锁定----------------------------------------查看哪些表加了锁：show open tables ; --1代表被加了锁分析表锁定的严重程度： show status like 'table%' ; Table_locks_immediate：可能获取到的锁数 Table_locks_waited：需要等待的表锁数(如果该值越大，说明存在越大的锁竞争)一般建议：Table_locks_immediate/Table_locks_waited > 5000， 建议采用InnoDB引擎，否则MyISAM引擎
9.2 行锁
1.行锁create table linelock(id int(5) primary key auto_increment,name varchar(20))engine=innodb ;insert into linelock(name) values('1') ;insert into linelock(name) values('2') ;insert into linelock(name) values('3') ;insert into linelock(name) values('4') ;insert into linelock(name) values('5') ;--mysql默认自动commit; --oracle默认不会自动commit;--为了研究行锁，暂时将自动commit关闭; set autocommit=0; -----------------------------------行锁：操作同一行数据----------------------------------------会话0：写操作insert into linelock values(6, 'a6') ;--会话1：写操作，同样的数据update linelock set name='ax' where id = 6; --结论：1.如果会话x对某条数据a进行DML操作（研究时：关闭了自动commit的情况下），则其他会话必须等待会话x结束事务(commit/rollback)后 才能对数据a进行操作。2.表锁是通过unlock tables，也可以通过事务解锁; 行锁是通过事务解锁。-----------------------------------行锁：操作不同行数据----------------------------------------会话0：写操作insert into linelock values(8,'a8') ;--会话1：写操作，不同的数据update linelock set name='ax' where id = 5;--结论：行锁，一次锁一行数据。因此如果操作的是不同数据，则不干扰。-----------------------------------行锁的注意事项-------------------------------------------1.如果没有索引，则行锁会转为表锁show index from linelock ;alter table linelock add index idx_linelock_name(name);--会话1：写操作update linelock set name = 'ai' where name = '3' ; --会话2：写操作，不同的数据update linelock set name = 'aiX' where name = '4' ;--会话1：写操作update linelock set name = 'ai' where name = 3 ; --会话2：写操作，不同的数据update linelock set name = 'aiX' where name = 4 ;--可以发现，数据被阻塞了（加锁）-- 原因：如果索引发生了类型转换，则索引失效。 因此此次操作，会从行锁转为表锁。2.行锁的情况二：间隙锁（值在范围内，但却不存在）--此时linelock表中 没有id=7的数据--即在此where范围中，没有id=7的数据，则id=7的数据成为间隙。update linelock set name ='x' where id >1 and id< ;--间隙：Mysql会自动给间隙加锁->间隙锁。即本题会自动给id=7的数据加间隙锁（行锁）。--行锁：如果有where，则实际加索的范围 就是where后面的范围（不是实际的值）3.行锁的情况三：如何仅仅是查询数据，能否加锁？ 可以，for update--关闭自动提交set autocommit =0 ;start transaction ;begin ;--通过for update对query语句进行加锁select * from linelock where id =2 for update ;----------------------------------------行锁分析-------------------------------------------show status like '%innodb_row_lock%' ;Innodb_row_lock_current_waits：当前正在等待锁的数量 Innodb_row_lock_time：等待总时长。从系统启到现在 一共等待的时间Innodb_row_lock_time_avg：平均等待时长。从系统启到现在平均等待的时间Innodb_row_lock_time_max：最大等待时长。从系统启到现在最大一次等待的时间Innodb_row_lock_waits：等待次数。从系统启到现在一共等待的次数--InnoDB默认采用行锁；缺点： 比表锁性能损耗大。优点：并发能力强，效率高。因此建议，高并发用InnoDB，否则用MyISAM。
