索引

索引是一种数据结构，是SQL编排数据的内部方法。

索引的优劣

• 优势
  1）提高检索效率，降低IO成本
  2）通过索引列对数据进行排序，降低数据排序CPU消耗
• 劣势
  1）索引也要占用空间
  2）提高查询速度的同时降低的更新表的速度

索引的分类

1）单列索引：一个索引值包含单个列，一个表可以有多个单列索引
2）唯一索引：索引列的值必须唯一，但允许有空值
3）复合索引：一个索引包含多个列

索引设计原则

1）对查询频次高且数据量大的表建立索引
2）索引字段的选择应从where子句的条件中提取
3）使用唯一索引，区分度高，效率高
4）使用短索引提升索引访问的I/O效率
5）针对复合索引，尽量利用最左前缀
6）尽量使用覆盖索引，避免使用select *
7）or前的条件有索引，or之后的条件没有索引，那么会导致索引失效
8）like模糊匹配%在前面索引失效
9）若mysql底层判断全表扫描比索引更快时，则索引失效
10）in走索引，而not in索引失效

索引的使用

1）全值匹配
2）最左前缀法则：查询从索引的最左前列开始，并且不跳过索引中的列
3）范围查询之后的索引失效
4）索引列上进行计算操作索引失效
5）字符串不加单引号索引失效

mysql存储引擎

存储引擎就是存储数据，建立索引，更新、查询数据等技术的实现方式。存储引擎是基于表的。mysql支持多种存储引擎，而oracle、sqlserver等只有一种存储引擎

InnoDB

• mysql默认存储引擎
• 支持事务
• mysql存储引擎中唯一支持外键
• 存储方式：表结构存储在frm文件中，数据和索引存储在ibd文件中
• 支持行锁

MyISAM

• mysql5.5之前默认存储引擎
• 不支持事务和外键，访问速度快，适合对事务没有要求的select、insert等
• 存储方式：表结构存储在frm文件，表数据存储在MYD（MYData），表索引存储在MYI（MYIndex）

Memory

memory存储引擎将表数据存在内存中，表结构存储在frm文件中，数据存放在内存中，默认使用HASH索引，一旦服务关闭，表中的数据就会丢失

Merge

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表的结构必须完全相同，Merge表本身不存储数据，对Merge类型的表可以进行查询、更新、删除操作，实际是对内部的MyISAM表进行操作。

• 可以突破对单个MyISAM表的大小限制，并通过将不同的表分布在多个磁盘上，可以有效改善Merge表的访问效率。

B+树

B+树为B树的变种，区别为：
1）n叉B+树最多包含n个key，而B树最多包含n-1key
2）B+树的叶子节点保存所有的key信息，按key大小顺序排列
3）所有的非叶子节点都可以看做是key的索引部分
mysql中对B+树进行了优化，在B+树的基础上增加一个指向相邻叶子节点的连表指针，形成带有顺序指针的B+树，提高了区间访问的性能

sql优化

一、order by 优化
1）尽量减少额外排序，通过索引覆盖直接返回有序数据。where条件和order by使用相同索引，并且order by字段都是升序或降序，才会命中索引，否则会出现fileSort。
2）Filesort优化：
两次扫描算法先是取出排序字段和行指针，在排序区sort bufffer中排序，如果sort buffer不够，则在临时表temporary table中排序，再根据行指针回表读取记录，I/O占用高。
一次扫描算法是直接取出所有字段在sort buffer中排序直接输出结果，内存占用高，效率也更高。
mysql通过比较系统变量max_length_for_sort_data的大小和取出的字段总大小来判定用哪种算法。
可以适当提高max_length_for_sort_data和sort_buffer_size来增大排序区的大小，提高排序效率。
二、group by 优化
1）利用索引覆盖原则
2）group by 内部默认进行了排序，可以通过order by null来禁用排序
三、子查询优化
1）尽量使用多表联查替换子查询
四、or优化
1）or如果要命中索引，需使用单列索引，不能命中复合索引
2）使用union替换or
五、limit优化
limit分页在页数大的时候会很慢，可以通过以下方案优化
1）在索引上完成分页操作，再通过索引关联查询其他列的内容
2）针对于主键自增的表且主键不会出现断层，可以把limit查询装换成某个位置的查询。
六、索引提示
1）use_index建议使用某个索引（mysql不一定会使用）
2）ignore_index忽略一个或多个索引
3）force_index强制使用某个索引

应用优化

一、使用数据库连接池
二、减少对mysql的访问
1）避免重复查询
2）增加cache层
三、负载均衡
1）通过主从实现读写分离
2）采用分布式数据库架构

查询缓存

• 查询数据库是否支持查询缓存：show variables like 'have_query_cache'
• 查询数据库是否开启查询缓存：show variables like 'query_cache_type'
• 查询数据库缓存大小：show variables like 'query_cache_size'
• 查看查询缓存的状态变量：show status like 'Qcache%'
  mysql查询缓存默认是关闭的，需要手动配置参数query_cache_type开启

一、查询缓存select选项
通过SQL_CACHE或SQL_NO_CACHE来指定该sql是否走缓存
二、查询缓存失效情况
1）SQL语句不一致
2）当查询语句中有一些不确定的函数时不走缓存，例如：now()、uuid()等
3）没有查询任何表时不走缓存
4）查询mysql系统数据库时不走缓存
5）在存储函数的触发器或事件的主体内执行查询不走缓存
6）若表发生更改，该表对应的所有缓存失效

内存优化

一、内存优化原则
1）将尽量多的内存分配给mysql做缓存
2）MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果是MyISAM表就要预留更多的内存给操作系统做IO缓存
3）排序区、连接区等缓存是分配给每个数据库会话的，其默认值的设置要根据最大连接上合理配置
二、MyISAM内存优化
MyISAM存储引擎使用key_buffer缓存索引块，而对于数据块则没有缓存机制，完全依赖于操作系统的IO缓存
1）key_buffer_size决定MyISAM索引块缓存区大小，建议至少分配可用内存的1/4
2）read_buffer_size是每个session独占的，如果经常需要顺序扫描可以适当增大read_buffer_size来改善性能
3）对于需要排序的MyISAM表若带有order by子句，适当增加read_rnd_buffer_size的值可以改善性能
三、InnoDB内存优化
InnoDB用一块内存去做IO缓存池，同时缓存索引和数据
1）innodb_buffer_pool_size决定缓存区的大小，提高该缓存区大小可以提高缓存的命中率
2）innodb_log_buffer_size决定重做日志缓存的大小，适当提高可以避免innodb在事务提交前日志写磁盘的动作
四、mysql并参数调整
1）调整最大连接数max_connections
2）调整积压请求栈的大小back_log
3）调整线程可打开表缓存数量table_open_cache
4）thread_cache_size控制mysql缓存客户服务线程的数量
5）innodb_lock_wait_time设置行锁的等待时间

mysql锁

锁分类

数据库操作粒度：
1）表锁
2）行锁：innodb默认支持，开销大，加锁慢；锁粒度低，发生锁冲突的概率也低，并发读高
3）页面锁
数据操作类型粒度：
1）读锁（共享锁）：阻塞写，不阻塞读
2）写锁（排它锁）：阻塞写，也阻塞读

innodb事务

ACID属性：

• 原子性（Atomicity）：事务是一个原子操作，要么全部成功，要么全部失败
• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境下运行
• 持久性（Durable）：事务完成之后对数据的修改时永久的

事务的隔离级别：

• 读未提交（read uncommitted）：事务中的修改，即使没有提交，对其他事务也是可见的
• 读已提交（read committed）：事务中的修改，只有已提交的才能可见
• 可重复读（repeatable read）：同一个事务中，多次读取同样记录的结果是一直的，mysql默认的隔离级别
• 串行化（serializable）：最高的隔离级别，读取每一行数据都加上锁，可能导致大量的超时和锁竞争

并发事务带来的问题

• 丢失更新：最初事务修改的值会被后面的事务修改的值覆盖
• 脏读：事务可以读取未提交的数据
• 不可重复读：读取记录前后可能出现不一致
• 幻读：当某个事务A在读取某个范围内的记录时，另外一个事务B又在该范围插入新的记录，当事务A再次读取该范围的记录时，会产生幻行。
  ps：不可重复读的重点在于update和delete，而幻读在于insert

innodb行锁

• 对于update、delete和insert，innodb会默认加排它锁
• 对于select语句，innodb不会加任何锁

innodb锁升级

如果不通过索引条件检索数据，那么innodb将对表的所有记录加锁，实际效果跟表锁一致

innodb间隙锁

当查询用范围条件时，innodb会给符合条件的已有数据进行加锁，当该范围中有不存在的记录，也会加锁