一、常用mysql知识
（一）查询语句优化
1.避免非操作符的使用，NOT、<>数据库系统不会使用索引。
2.避免对查询的列的操作，任何对列操作都可能导致全表扫描，包括数据库函数、技术表达式等。尽可能将操作移至等式右边
3.避免不必要的类型转换
4.增加查询的范围限制
5.合理使用IN和EXIST
6.尽量去掉<>
7.去掉WHERE字句中的IS NULL和IS NOT NULL，不会使用索引而进行全表搜索。
8.尽量不要使用前导模糊查询，前导模糊查询不能利用索引。
9.SELECT字句中避免使用‘*’
10.规范所有的SQL关键之的书写，不要大小写混用(二) exists与in
1.区别：in是把外表和内表作为hash连接，而exist是对外表做loop循环，每次loop循环再对内表进行查询。如果两个表大小相当，in或exists都可以，如果一大一小，子查询大表用exists，子查询小表用in
2.not in 与not exist:如查询语句使用了not in那么内外表都进行全表扫描，没用到索引，而not exists子查询依然能用到表上索引，所以无论哪个表大，用not exists都比not in要快。
(三) 事物隔离级别
1) 幻读
事物1读取一条指定where数据之后，事物2插入一条符合where条件的数据，事物1使用相同条件再次查询，结果集就可以看到事物2插入的数据，这条数据是幻读数据
2) 不可重复读
事物1读取一条记录，事物2修改1刚刚读取的，事物1再次查询，发现与第一次读取记录不同
3) 脏读
事物1更新一条记录还没提交，事物2读取更新后的数据，然后事物1回滚，事物2读取的行无效，也就是脏数据。
4) 事务隔离级别
READ UNCOMMITTED 幻读、不可重复读和脏读都允许
READ COMMITTED 允许幻读、不可重复读，脏读不允许
REPEATABLE READ允许幻读，不允许不可重复读和脏读
SERIALIZABLE 幻读、不可重复读和脏读都不允许。
SQL标准所定义的默认事物隔离级别是SERIALIZABLE，但oracle默认使用READ COMMITTED
(四)索引优缺点
优点
创建唯一索引，可以保证数据库表中每一行数据唯一性
加快数据检索速度
加速表与表之间连接
减少查询中分组和排序的时间
在查询过程中使用优化隐藏器，提高系统性能
缺点
创建索引和维护索引需要耗费时间
索引需要占物流空间
表中数据增删改时需要动态维护
不合适的创建索引不会提高性能
主键也是一个特殊的所有。数据表中只允许一个主键，但是可以有多个索引。
(五)Mysql优化
在MySQL中可以使用EXPLAIN查看SQL执行计划
1、参数说明
select_type
1.SIMPLE， 表示简单查询，其中不包含连接查询和子查询。
2.PRIMARY，表示主查询，或者是最外面的查询语句。
3.UNION 表示连接查询的第2个或后面的查询语句。
4.DEPENDENT UNION UNION中的第二个或后面的SELECT语句，取决于外面的查询。
5.UNION RESULT 连接查询的结果。
6.SUBQUERY 子查询中的第1个SELECT语句。
7.DEPENDENT SUBQUERY 子查询中的第1个SELECT语句，取决于外面的查询。
8.DERIVED SELECT(FROM 子句的子查询)。
table
表示查询的表
type
以下参数是最佳类型到最差类型
1.system 表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计。
2.const 数据表最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于PRIMARY KEY或者UNIQUE索引的查询，可理解为const是最优化的
3.eq_ref 对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY”。eq_ref可以用于使用=比较带索引的列
4.ref 查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。
5.ref_or_null 该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
以上5种情况都是很理想的索引使用情况
6.index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素
7.unique_subquery 是一个索引查找函数,可以完全替换子查询,效率更高
8.index_subquery 可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引value IN (SELECT key_column FROM single_table WHERE some_expr)
9.range 只检索给定范围的行,使用一个索引来选择行。
10.index 该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
11.ALL 对于每个来自于先前的表的行组合,进行完整的表扫描
possible_keys
指出MySQL能使用哪个索引在该表中找到行。如果该列为NULL，说明没有使用索引，可以对该列创建索引来提高性能。
key
显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL
可以强制使用索引或者忽略索引：IGNORE INDEX(age).USE INDEX(age)
key_len
显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
注意：key_len是确定了MySQL将实际使用的索引长度。
ref
显示使用哪个列或常数与key一起从表中选择行
rows
显示MySQL认为它执行查询时必须检查的行数。
Extra
该列包含MySQL解决查询的详细信息
2、使用索引查询需要注意
使用LIKE关键字的查询
1.在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引才会生效
使用联合索引的查询
MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。
使用OR关键字的查询
查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效
子查询优化
执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。
3、数据库结构优化
将字段很多的表分解成多个表
对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。
因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。
增加冗余字段
设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。
注意：冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题
4、插入数据的优化
插入数据时，影响插入速度的主要是索引、唯一性校验、一次插入的数据条数等
MySQL中常用的存储引擎有，MyISAM和InnoDB两者的区别：
MyISAM 是非事务安全型的，InnoDB事务安全型的。
MyISAM锁粒度是表级 ，InnoDB支持行级。
MyISAM支持全文类型索引 ，InnoDB不支持全文索引。
MyISAM 相对简单，效率上优于InnoDB，小型系统考虑使用
MyISAM表是报错成文件的形式 ，跨平台的数据转移中使用会省去不少麻烦
MyISAM ，InnoDB更加安全，保证数据不会丢失的情况下，切换非事务表到事物表。
MyISAM管理非事务，如果应用中需要执行大量SELECT查询，那么更好的选择是MYISAM ，InnoDB用于事物处理应用程序，具有众多性，包括ACID，如应用中需要执行大量的INSERT或UPDATE操作，则应使用INNoDB
MyISAM
禁用索引，禁用唯一性检查，批量插入数据。当需要批量导入数据时，使用LOAD DATA INFILE语句比INSERT语句插入速度快很多
InnoDB
禁用唯一性检查，禁用外键检查，禁止自动提交
(七) 服务器优化
1、优化服务器硬件
主要通过使用高性能硬件设备提高效率
2、优化MySQL的参数
MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中
key_buffer_size:表示索引缓冲区的大小
table_cache:表示同时打开的表的个数
query_cache_size:表示查询缓冲区的大小
sort_buffer_size 表示排序缓存区的大小
read_buffer_size:表示每个线程连续扫描时为扫描每个表分配的缓冲区的大小
read_rnd_buffer_size:表示为每个线程保留的缓冲区的大小
innodb_buffer_pool_size:表示InnoDB类型的表和索引的最大缓存
max_connections:表示数据库的最大连接数
innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志文件
back_log:表示在mysql暂时停止回答新请求之前的短时间内，多少个请求可以放在堆栈中。
interactive_timeout:表示服务器在关闭连接前等待行动的秒数。
sort_buffer_size: 表示每个需要进行排序的线程分配的缓冲区的大小。
thread_cache_size 表示可以复用的线程的数量
wait_timeout 表示服务器在关闭一个连接时等待行动的秒数。默认数值时28800
