mysql服务端的设计

连接层

提供了与客户端的连接

服务层

1. 提供了各种用户使用的增删改查的操作接口

2. 提供了SQL优化器(MYSQLQuery Optimizer)

引擎层

show engine 显示数据库支持引擎

提供各种存储数据的方式(innodb / myisam)

innodb(mysql默认支持) : 事务优先，适合高并发操作 ，使用的行锁 ；
myisam : 性能优先， 使用的表锁 ;

存储层

存储数据

SQL优化

解决的问题：执行时间太长，等待时间太长， SQL语句欠佳(连接查询)，索引失效，服务器参数设置不合理(缓冲，线程数)

使用 in 来做范围查询时会使用索引失效，导致无索引，我们可以使用between, >, <, >=,<= 这些来转化处理

system/const : 结果只有一条数据

eq_ref : 结果多条，但是每条数据是唯一的；

ref : 结果多条，但是每条数据是0条或多条 ；

复合索引中如果有字段可以为Null, mysql会使用1个字节来用于区分和标识null， 使用2个字节来区分和标识可变长度

对于单索引，如果排序和查找是同一个字段，则不会出现额外的排序，如果排序和查找的不是同一个字段，就会出现额外的排序；

复合索引，满足规则就是 ，最佳左前缀 ； where和order by 按照复合索引的顺序使用，不要跨列或无序使用。

sql的解析过程 ：
from ...on join ..where ...group by ....having....select dinstinct....order by... limit ....

create index : alert table 表名 add index 索引名 (索引列) ；
delete index : drop index 索引名 on 表名；

使用到的索引列都在查询条件中，就会形成索引覆盖，达到性能提升，原因是不用读取原表数据文件，只从索引文件中拿数据，不用回表查询(using index); 出现跨列使用索引会导致索引失效，然后会回表查询，(文件内排序，也就是多了一次额外的查找和排序)，消耗了性能和占用了内存空间，所以在使用where和order by时，不要跨列使用；在使用复合索引时，索引顺序和条件查询顺序完全一致，则会形成索引覆盖使用索引全部使用，如果是部分一致，则会使用部分索引(索引覆盖)。

索引的类型和级别，级别越高执行效率越高 ：system > const > eq_ref > ref > range > index > all 索引的实际级别在ref>range 时即最佳

explain sql语句 执行后 ，有几个指标来衡量sql语句的效率，type使用到的索引级别，级别越高越好；extra，using where 表示有回表查询，using index 表示索引中查询，回表查询是额外的原本查询，using join buffer 使用了连接缓存 ；key_len表示使用到的索引列长度，越接近索引的定义时的列，则效率越高 ；

索引优化原则 ：1 ，最佳左前缀，保持索引的定义顺序和使用的顺序（条件字段）一致性；2， 索引需要逐步优化；3， 出现In的范围查询情况，最好是把这个条件查询放到where条件的最后，避免干扰索引的使用防止失效；4， 多表连接时，连接时的条件应该是小表驱动大表，使用数据量少的表去连数据量大的表，索引要建立在经常使用的字段上面，左连接索引建立在左表上，右连接索引建立在右表上面，多表连接时，查询的列最好都建立相应的索引；5，在使用子查询时，可以使用exist和in的相互转换，如果主查询的数据集大，使用in ,如果子查询的数据集大，则使用exist; 6， 使用order by时尽可能的保证索引可用，避免或者不使用select*方式，复合索引不要跨列使用，排序的字段要么全升序或全降序；

多表连接避免索引失效 ：1 ， 复合索引 ：不要跨列使用或无序使用，符合最佳左前缀，在使用时尽量使用全索引匹配；2， 在使用索引时尽量不要在索引的基础上进行一些操作，比如某个列的(计算，函数，类型转换)，索引容易失效； 3，对于复合索引，如果左边失效，则右边的全部失效(a字段失改，则abc索引中的bc也失效)；4， 对于复合索引中不能使用不等于这个逻辑（！=或者<>或者is null / is not null）否则自身或者右边索引全部失效；5 ， like 条件尽量使用“xx%”,常量开头，不要使用"%xx%"这种方式，在使用查询时select 具体字段和具体条件查询具体字段，形成索引覆盖；6，条件查询时，尽量不要使用类型转换(显式或隐式的,比如 =“123”，然后写成=123)；7，条件查询时，尽量不要使用Or的方式来连接使用；

排查慢sql的方式： 慢sql的执行会有一个执行阀值，可以在mysql中进行设置，超过阀值的慢sql会被记录到慢sql日志中，这个设置一般是设置为关的，可以进行设置开启，开启后慢查询的语句这些会被记录到指定的文件中，可以把文件取出查看，也可以通过mysqldumpslow语法方式去查看符合自身要求的日志记录；如果是海量数据的分析里，可以使用profiles的方式去查询相应的sql所花费的时间；

是否优化完成，通过explain 的方式去判断；由于sql语句通过mysql的服务层中的sql优化器处理后，sql的执行会稍有变化，是一种概率层的优化 ！

mysql的锁机制 ：
操作类型 ： 1，读锁(共享锁)；2，写锁(互斥锁)；
操作范围 ：
1，表锁 ；myisam存储引擎的默认锁机制，对整张表加锁，开销小，加锁快，无死锁，但是锁粒度大，并发量低，易产生锁冲突；
2，行锁；一次只对一行数据加锁，开销大，加锁慢，容易死锁，但锁粒度小，但并发度高，不易产生脏读，幻读，不可重复读，丢失更新的问题；

表锁--------------加读锁 ：
1 ， 如果给A表加了读锁，则当前会话只能对A表进行读操作；
2 ， 如果给A表加了读锁， 其它会话可以对其它表除A以外的其它的表进行读写操作；对A表读可以，写操作需要A表释放锁；
加写锁 ：
1 ， 如果对A表加了写锁，则当前会话可以对A表进行读写操作；
2 ， 如果对A表加了写锁， 其它会话需要A表释放写锁才能进行增删改查；

MYSQL表级锁的锁模式 ：
myisam在执行查询语句前，会自动给涉及到的所有表加读锁，在执行更新操作前，会自动的给涉及到的表加写锁。所以对于myisam表进行操作，会有以下情况 ：
1， 对myisam表的读操作(加读锁)，不会阻塞其它会话对同一表的读请求，但会阻塞对同一表的写操作。只有当读锁释放后，才会执行其它进程的写操作；
2， 对myisam表的写操作(加写锁)，会阻塞其它会话对同一表的读和写操作，只有当锁释放后，才能执行其它进程的读写操作；

行锁 innodb：
1, 如果对某条A数据进行增删改操作，在关闭自动commit的情况下，其它会话无法对A数据进行操作，只有等A数据操作commit/rollback之后才能进行；
2 ， 行锁是通过事务解锁的，每次锁一行数据，对于其它行数据操作是无干扰的；
3 ， 如果索引失效，则行锁会转化为表锁 ；
4 , 行锁并发行高，但是系统开销大消耗性能 ；

行锁分析 ： show status like ‘xxxxxxxxxxxxxx’

mysql中字符对应字节数 ：
UTF8 : 一个字符对应3个字节
GBK : 一个字符对应2个字节
LATIN : 一个字符对应1个字节