查询authorid=1且 typeid为2或3的 bid
explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc ;优化：加索引
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 ;再次优化（之前是index级别）：因为范围查询in有时会实现，因此交换索引的顺序，将typeid in(2,3) 放到最后。
drop index idx_tab on book; alter table book add index idx_atb (authorid,typeid,bid); explain select bid from book where authorid=1 and typeid in(2,3) order by typeid desc ;小结：
最佳做前缀，保持索引的定义和使用的顺序一致性索引需要逐步优化将含In的范围查询 放到where条件的最后，防止失效。本例中同时出现了Using where（需要回原表）; Using index（不需要回原表）：原因，where authorid=1 and typeid in(2,3)中authorid在索引(authorid,typeid,bid)中，因此不需要回原表（直接在索引表中能查到）；而typeid虽然也在索引(authorid,typeid,bid)中，但是含in的范围查询已经使该typeid索引失效，因此相当于没有typeid这个索引，所以需要回原表（using where）； 例如以下没有了In，则不会出现using where
explain select bid from book where authorid=1 and typeid =3 order by typeid desc ;还可以通过key_len证明In可以使索引失效。
左连接：
explain select * from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java';索引往哪张表加？
小表驱动大表索引建立经常使用的字段上 （本题 t.cid=c.cid可知，t.cid字段使用频繁，因此给该字段加索引）[一般情况对于左外连接，给左表加索引；右外连接，给右表加索引]小表：10 大表：300
where 小表.x 10 = 大表.y 300; --循环了几次？10大表.y 300=小表.x 10 --循环了300次 select ...where 小表.x10=大表.x300 ; for(int i=0;i<小表.length10;i++) { for(int j=0;j<大表.length300;j++) { ... } } select ...where 大表.x300=小表.x10 ; for(int i=0;i<大表.length300;i++) { for(int j=0;j<小表.length10;j++) { ... } } 以上2个FOR循环，最终都会循环3000次；但是 对于双层循环来说：一般建议 将数据小的循环 放外层；数据大的循环放内存。当编写 …on t.cid=c.cid 时，将数据量小的表 放左边（假设此时t表数据量小）alter table teacher2 add index index_teacher2_cid(cid) ; alter table course2 add index index_course2_cname(cname); Using join buffer: extra中的一个选项，作用：Mysql引擎使用了 连接缓存。示例：
create table test03 ( a1 int(4) not null, a2 int(4) not null, a3 int(4) not null, a4 int(4) not null ); alter table test03 add index idx_a1_a2_a3_4(a1,a2,a3,a4) ; explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a3=3 and a4 =4 ; --推荐写法，因为 索引的使用顺序（where后面的顺序） 和 复合索引的顺序一致 explain select a1,a2,a3,a4 from test03 where a4=1 and a3=2 and a2=3 and a1 =4 ; --虽然编写的顺序 和索引顺序不一致，但是sql在真正执行前 经过了SQL优化器的调整，结果与上条SQL是一致的。 --以上 2个SQL，使用了全部的复合索引 explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=4 order by a3; --以上SQL用到了a1 a2两个索引，该两个字段不需要回表查询using index ;而a4因为跨列使用，造成了该索引失效，需要回表查询 因此是using where；以上可以通过 key_len进行验证 explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a3; --以上SQL出现了 using filesort(文件内排序，“多了一次额外的查找/排序”) ：不要跨列使用( where和order by 拼起来，不要跨列使用) explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a2 , a3; --不会using filesort总结：
如果 (a,b,c,d)复合索引和使用的顺序全部一致(且不跨列使用)，则复合索引全部使用。如果部分一致(且不跨列使用)，则使用部分索引。select a,c where a = and b= and d=where和order by 拼起来，不要跨列使用using temporary: 需要额外再多使用一张表. 一般出现在group by语句中；已经有表了，但不适用，必须再来一张表。 解析过程： from … on… join …where …group by …having …select dinstinct …order by limit …
explain select * from test03 where a2=2 and a4=4 group by a2,a4 ;--没有using temporary explain select * from test03 where a2=2 and a4=4 group by a3 ;--有using temporary（1）复合索引
复合索引，不要跨列或无序使用（最佳左前缀）复合索引，尽量使用全索引匹配MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。（2） 不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效
select ..where A.x = .. ; --假设A.x是索引,不要：select ..where A.x*3 = .. ; explain select * from book where authorid = 1 and typeid = 2 ;--用到了at2个索引 explain select * from book where authorid = 1 and typeid*2 = 2 ;--用到了a1个索引 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) ; alter table book add index idx_typeid (typeid) ; explain select * from book where authorid*2 = 1 and typeid = 2 ;（3）复合索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效。复合索引中如果有>，则自身和右侧索引全部失效。
explain select * from book where authorid = 1 and typeid =2 ; -- SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。 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）补救。尽量使用索引覆盖（using index） select a,b,c from xx…where a= … and b =… ; （5）like尽量以“常量”开头，不要以’%'开头，否则索引失效
select * from xx where name like '%x%' ; --name索引失效 explain select * from teacher where tname like '%x%'; --tname索引失效 explain select * from teacher where tname like 'x%'; 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 失效。（8）索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
（1）选取最适用的字段属性 MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。
另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
（2）使用连接（JOIN）来代替子查询(Sub-Queries) MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询。
连接（JOIN）之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作
（3）使用联合(UNION)来代替手动创建的临时表 MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要相同。
（4）事务 尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。
（5）exist和in select …from table where exist (子查询) ; select …from table where 字段 in (子查询) ;
如果主查询的数据集大，则使用In ,效率高。 如果子查询的数据集大，则使用exist,效率高。
exist语法： 将主查询的结果，放到子查询结果中进行条件校验（看子查询是否有数据，如果有数据 则校验成功），如果 复合校验，则保留数据；
select tname from teacher where exists (select * from teacher) ; --等价于select tname from teacher select tname from teacher where exists (select * from teacher where tid =9999) ; in: select ..from table where tid in (1,3,5) ;（6）order by 优化 当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。对于filesort，MySQL有两种排序算法。
两遍扫描算法(Two passes)
实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。
一次扫描算法(single pass)
该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。
当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”。
提高order by查询的策略：
选择使用单路、双路 ；调整buffer的容量大小；避免select * …复合索引 不要跨列使用 ，避免using filesort保证全部的排序字段排序的一致性（都是升序或降序）存储过程（无return）/存储函数（有return）
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;通过存储函数 插入海量数据，创建存储函数：randstring(6) ->aXiayx 用于模拟员工名称
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 $产生随机整数
delimiter $ create function ran_num() returns int(5) begin declare i int default 0; set i =floor( rand()*100 ) ; return i ; end $通过存储过程插入海量数据：emp表中 ， 10000, 100000
delimiter $ 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 $通过存储过程插入海量数据：dept表中
delimiter $ 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$插入数据
delimiter ; call insert_emp(1000,800000) ; call insert_dept(10,30) ;(1) profiles show profiles ; --默认关闭 show variables like ‘%profiling%’; set profiling = on ; show profiles ：会记录所有profiling打开之后的 全部SQL查询语句所花费的时间。缺点：不够精确，只能看到 总共消费的时间，不能看到各个硬件消费的时间（cpu io ）
(2) 全局查询日志 ：记录开启之后的 全部SQL语句。 （这次全局的记录操作 仅仅在调优、开发过程中打开即可，在最终的部署实施时 一定关闭）
show variables like '%general_log%'; --执行的所有SQL记录在表中 set global general_log = 1 ;--开启全局日志 set global log_output='table' ; --设置 将全部的SQL记录在表中 --执行的所有SQL记录在文件中 set global log_output='file' ; set global general_log = on ; set global general_log_file='d:/general.log' ; 开启后，会记录所有SQL ： 会被记录 mysql.general_log表中。 select * from mysql.general_log ;解决因资源共享 而造成的并发问题。
按操作类型分类：
读锁（共享锁）： 对同一个数据（衣服），多个读操作可以同时进行，互不干扰。写锁（互斥锁）： 如果当前写操作没有完毕（买衣服的一系列操作），则无法进行其他的读操作、写操作按操作范围分类：
表锁 ：一次性对一张表整体加锁。如MyISAM存储引擎使用表锁，开销小、加锁快；无死锁；但锁的范围大，容易发生锁冲突、并发度低。行锁 ：一次性对一条数据加锁。如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概率发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题）。页锁自增操作 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'); commit;增加锁：locak table 表1 read/write ,表2 read/write ,…
查看加锁的表：show open tables ;
会话：session :每一个访问数据的dos命令行、数据库客户端工具 都是一个会话
(1) 加读锁： 会话0：
lock table tablelock read ; select * from tablelock; --读（查），可以 delete from tablelock where id =1 ; --写（增删改），不可以 select * from emp ; --读，不可以 delete from emp where eid = 1; --写，不可以结论1：如果某一个会话 对A表加了read锁，则该会话可以对A表进行读操作、不能进行写操作； 且该会话不能对其他表进行读、写操作。 即如果给A表加了读锁，则当前会话只能对A表进行读操作。
会话1（其他会话）：
select * from tablelock; --读（查），可以 delete from tablelock where id =1 ; --写，会“等待”会话0将锁释放会话1（其他会话）：
select * from emp ; --读（查），可以 delete from emp where eno = 1; --写，可以结论2：会话0给A表加了锁；其他会话的操作：
可以对其他表（A表以外的表）进行读、写操作对A表：读-可以； 写-需要等待释放锁。释放锁: unlock tables ; (2) 加写锁： 会话0：
lock table tablelock write ; 当前会话（会话0） 可以对加了写锁的表进行任何操作（增删改查）；但是不能 操作（增删改查）其他表其他会话：对会话0中加写锁的表 可以进行增删改查的前提是：等待会话0释放写锁MySQL表级锁的锁模式：
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（DML）前，会自动给涉及的表加写锁。所以对MyISAM表进行操作，会有以下情况：
对MyISAM表的读操作（加读锁），不会阻塞其他进程（会话）对同一表的读请求， 但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。对MyISAM表的写操作（加写锁），会阻塞其他进程（会话）对同一表的读和写操作， 只有当写锁释放后，才会执行其它进程的读写操作。分析表锁定
查看哪些表加了锁： show open tables ; 1代表被加了锁分析表锁定的严重程度： show status like 'table%' ; Table_locks_immediate:即可能获取到的锁数 Table_locks_waited：需要等待的表锁数(如果该值越大，说明存在越大的锁竞争)一般建议： Table_locks_immediate/Table_locks_waited > 5000， 建议采用InnoDB引擎，否则MyISAM引擎。InnoDB实现了以下两种类型的行锁：
共享锁 (S): 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁排他锁 (X): 允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X） 对于普通SELECT语句，InnoDB不会加任何锁
事务可以通过以下语句显式地给记录集加共享锁或排他锁：
共享锁: SELECT * FROM table_name WHERE … LOCK IN SHARE MODE，等同读锁排他锁: SELECT * FROM table_name WHERE … FOR UPDATE，等同写锁用SELECT … IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
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 ; --以后需要通过commit(1) 操作同样的数据 会话0： 写操作
insert into linelock values('a6') ;会话1： 写操作
update linelock set name='ax' where id = 6;对行锁情况：
如果会话x对某条数据a进行DML操作（研究时：关闭了自动commit的情况下），则其他会话必须等待会话x结束事务(commit/rollback)后才能对数据a进行操作。表锁是通过unlock tables，也可以通过事务解锁 ; 行锁是通过事务解锁。(2) 操作不同数据 会话0： 写操作
insert into linelock values(8,'a8') ;会话1： 写操作， 不同的数据
update linelock set name='ax' where id = 5;--行锁，一次锁一行数据；因此 如果操作的是不同数据，则不干扰。行锁的注意事项：如果没有索引，则行锁会转为表锁 show index from linelock ; alter table linelock add index idx_linelock_name(name);
会话0： 写操作
update linelock set name = 'ai' where name = '3' ;会话1： 写操作， 不同的数据
update linelock set name = 'aiX' where name = '4' ;会话0： 写操作
update linelock set name = 'ai' where name = 3 ;会话1： 写操作， 不同的数据
update linelock set name = 'aiX' where name = 4 ;可以发现，数据被阻塞了（加锁）原因：如果索引类 发生了类型转换，则索引失效。 因此 此次操作，会从行锁 转为表锁。
行锁的一种特殊情况：间隙锁：值在范围内，但却不存在 此时linelock表中 没有id=7的数据
update linelock set name ='x' where id >1 and id<9 ; --即在此where范围中，没有id=7的数据，则id=7的数据成为间隙。间隙：Mysql会自动给 间隙 加索 ->间隙锁。即 本题会自动给id=7的数据加间隙锁（行锁）。 行锁：如果有where，则实际加索的范围 就是where后面的范围（不是实际的值）
如何仅仅是查询数据，能否加锁？ 可以 for update 研究学习时，将自动提交关闭： set autocommit =0 ; start transaction ; begin ; select * from linelock where id =2 for update ;
通过for update对query语句进行加锁。
行锁： (1) InnoDB默认采用行锁； (2) 缺点： 比表锁性能损耗大。 (3) 优点：并发能力强，效率高。 因此建议，高并发用InnoDB，否则用MyISAM。
行锁分析：
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 ： 等待次数。从系统启到现在一共等待的次数