mysql知识点（mysql底层结构、mysql调优）

一、存储引擎

存储引擎	说明
MyISAM	高数，拥有较高的插入、查询速度，但是不支持事务
InnoDB	5.5版本后的默认数据库，支持事务和行级锁定
memory	内存存储引擎，拥有极高的插入、更新、查询效率；会占用内存空间，由于在内存中保存数据，意味着数据可能会丢失；
archive	将数据压缩进行存储，适合大数据量独立的，比如历史记录进行插入和查询；

常用的是MyISAM和InnoDb，下面是其比较

	InnoDb	MyISAM
存储文件	.frm 表结构定义文件 .ibd 数据文件和索引文件	.frm 表结构定义文件 .myd 数据文件 .myi 索引文件
锁	表级锁 行级锁	表级锁
事务	支持	不支持
CRDU	读写	读多
count	扫表	专门存储的地方
索引结构	B+ Tree	B+ Tree

二、性能分析

explain查看执行计划

explain select * from test_table where id = 1

explain查询结果字段分析

type

这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型依次为：

system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL

除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

1、system：表中只有一行数据或者是空表，这是const类型的一个特例。且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index

2、const：最多只有一行记录匹配。当联合主键或唯一索引的所有字段跟常量值比较时，join类型为const。其他数据库也叫做唯一索引扫描

3、eq_ref：多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型。
  eq_ref可用于使用’='操作符作比较的索引列。比较的值可以是常量，也可以是使用在此表之前读取的表的列的表达式。
  相对于下面的ref区别就是它使用的唯一索引，即主键或唯一索引，而ref使用的是非唯一索引或者普通索引。
  eq_ref只能找到一行，而ref能找到多行。

4、ref：对于来自前面表的每一行，在此表的索引中可以匹配到多行。若联接只用到索引的最左前缀或索引不是主键或唯一索引时，使用ref类型（也就是说，此联接能够匹配多行记录）。
  ref可用于使用’=‘或’<=>'操作符作比较的索引列。

5、 fulltext：使用全文索引的时候是这个类型。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

6、ref_or_null：跟ref类型类似，只是增加了null值的比较。实际用的不多。

eg.
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

7、index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range

8、unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。
该类型替换了下面形式的IN子查询的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

9、index_subquery：该联接类型类似于unique_subquery。适用于非唯一索引，可以返回重复值。

10、range：索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

11、index：索引全表扫描，把索引从头到尾扫一遍。这里包含两种情况：
一种是查询使用了覆盖索引，那么它只需要扫描索引就可以获得数据，这个效率要比全表扫描要快，因为索引通常比数据表小，而且还能避免二次查询。在extra中显示Using index，反之，如果在索引上进行全表扫描，没有Using index的提示。

# 此表见有一个name列索引。
# 因为查询的列name上建有索引，所以如果这样type走的是index
mysql> explain select name from testa;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | testa | index | NULL          | idx_name | 33      | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set

# 因为查询的列cusno没有建索引，或者查询的列包含没有索引的列，这样查询就会走ALL扫描，如下：
mysql> explain select cusno from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | testa | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

# 包含有未见索引的列
mysql> explain select * from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | testa | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

12、all：全表扫描，性能最差。

三、索引

1.什么是索引

  索引是帮助mysql高效获取数据的数据结构；

• 优势： 提高检索效率，降低IO成本；
• 劣势： 占用磁盘空间；虽然提高查询效率，但是降低更新表的效率，因为会更具索引排序，在更新字段后，需要重新排序；

2.索引分类

单列索引

• 普通索引
• 唯一索引
• 主键索引：主键索引都是存在的，如果没有设置主键，则先用唯一非空列来当主键索引，如果没有则创建隐藏列；

组合索引

• 在表中多个字段组合上创建的索引，遵循最左原则

ALTER TABLE 'TABLE_NAME' ADD INDEX INDEX_NAME('COL1','COL2','COL3')--相当于创建了col1，col1 col2，col1 col2 col3 3个索引

• 最左前缀原则
    当where条件中有"a=1 and b = 1 and c>1 and d=1"时，当组合索引创建为(a,b,c,d)则只能用到abc的索引；如果组合索引创建为(abdc)则能用到abdc全部；

前缀索引，比如字段长度为40，但是我只将其前20个设置为索引

3.索引数据结构

• 二叉树：不能平衡；不用
• 红黑树：平衡二叉树，会自旋，但是高度太高；不用
• hash表：通过hash运算计算出hash值，精确查找是没有问题的，但是范围查找就不行了；mysql中创建索引的时候可以设置成hash；
• B-TREE：使用的是b+Tree；
  
  

聚集索引（InnoDB）

聚簇索引（索引组织表），B+树，数据和索引是在一块的；

• 主键索引：按主键排序，存储整个数据；
• 辅助索引：但是存储的是主键值，然后根据主键值获取信息；

非聚集索引（MyISAM）

同样是B+数，数据和索引是分开的，树中存储的是地址；非聚集索引中主键索引和辅助索引没有太大区别，只不过辅助索引中key可以重复；

4.索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

• 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。

• 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

link

5.索引相关问题

数据库中查询记录时是否每次只能使用一个索引？

  与其说是“数据库查询只能用到一个索引”，倒不是说是 和全表扫描/只使用一个索引的速度比起来，去分析两个索引二叉树更加耗费时间，所以绝大多数情况下数据库都是是用一个索引。
如这条语句：

select count(1) from table1 where column1 = 1 and column2 = 'foo' and column3 = 'bar'

我们来想象一下当数据库有N个索引并且查询中分别都要用上他们的情况：
查询优化器（用大白话说就是生成执行计划的那个东西）需要进行N次主二叉树查找[这里主二叉树的意思是最外层的索引节点]，此处的查找流程大概如下：
查出第一条column1主二叉树等于1的值，然后去第二条column2主二叉树查出foo的值并且当前行的coumn1必须等于1，最后去column主二叉树查找bar的值并且column1必须等于1和column2必须等于foo。
如果这样的流程被查询优化器执行一遍，就算不死也半条命了，查询优化器可等不及把以上计划都执行一遍，贪婪算法（最近邻居算法）可不允许这种情况的发生，所以当遇到以下语句的时候，数据库只要用到第一个筛选列的索引（column1），就会直接去进行表扫描了。

select count(1) from table1 where column1 = 1 and column2 = 'foo' and column3 = 'bar'

所以与其说是数据库只支持一条查询语句只使用一个索引，倒不如说N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢。
所以如上条的情况，最佳推荐是使用index(column1,column2,column3） 这种联合索引，此联合索引可以把b+tree结构的优势发挥得淋漓尽致：
一条主二叉树（column=1），查询到column=1节点后基于当前节点进行二级二叉树column2=foo的查询，在二级二叉树查询到column2=foo后，去三级二叉树column3=bar查找。数据库中查询记录时是否每次只能使用一个索引？

where条件中有or索引的执行情况

• where 语句里面如果带有or条件, myisam表能用到索引， innodb不行，会直接扫描全表。
• 即使是MyISAM也必须所有的or条件都必须是独立索引；

哪些情况下创建索引哪些不应该创建

• 应该创建索引
  1，主键自动创建
  2，频繁查询的字段
  3，经常用来关联的字段
  4，经常用来分组、排序的字段
• 不应该创建索引
  1，表记录较少
  2，经常进行增删改的字段
  3，频繁更新的字段
  4，使用频率不高的字段

索引失效的情况

• like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

like查询百分号前置，并不是100%不会走索引。
如果只select索引字段，或者select索引字段和主键，也会走索引的。
如果where条件中有主键时，走索引。
一句话：在select和where中存在除了索引和主键外的其他条件或字段时，不走索引。

• or语句前后没有同时使用索引。
• 组合索引，不是使用第一列索引，索引失效。
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引
• 在索引列上使用 IS NULL 或 IS NOT NULL操作。
• 在索引字段上使用not，<>，!=。
• 对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）
• 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

mysql调优

分区、分表、分库
读写分离