MySQL—优化和索引

索引的建立和优化

• UUID ：
  8位 机器IP
  8位 进程IP
  8位 当前时间
  8位 随机数
  每位为16进制数
  UUID是全局唯一的，除非同一台机器同一进程在同一个毫秒级的时间插入且随机数相同才会重复，而这基本是不可能的，所以UUID全局唯一

• 索引的代价？
  索引本身要占空间
  建立索引后，增删改查时间增加

• 如果本身操作就需要扫描全表或者本身表并不大，建立索引意义不大

• MySQL里的索引都是B树吗？
  并不是，至少包含以下几种

  
  
  MySQL索引类型.png

1. 空间数据类型使用R-trees
2. 内存表支持哈希索引
3. InnoDB对FULLTEXT索引（反向索引）使用倒排列表

• 反向索引：
  正常的索引是某一列包含哪些内容
  反向的索引是包含某个内容的在哪几列

  
  
  反向索引.png

• 索引目的是快速匹配

• MySQL会在主键建立索引，主键被查概率大，且主键不为NULL

• InnoDB上建立的主键索引超乎寻常的快

• 如果表很庞大，字段较多，区别不了重要程度，如何选择主键？
  单独弄一列出来当主键。
  主键最好不要是组合主键

• 空间数据索引定义那两列必须非空

• 建立索引可以指定一列只用前缀做索引
  

  指定前缀做索引.png

• 全文索引必须在整个一列上建立，不能使用前缀建立

• 内存里强制指定也可建立B-tree

• 在多列上建立索引
  建立索引的顺序需要考虑
  要用到的条件前面之前的条件也必须指定
  

  能用到索引.png
  
  
  不能用到索引.png

• B-tree索引支持比较和LIKE
  

  B-tree索引
  
  前面两句可以用到索引
  后两句不能用到，如果LIKE前面的可以变化或者后面跟的是变量，索引无法被用到

B-tree索引

• HASH索引只能找等于和不等于，速度相当快
  限制：

1. 无法知道找到的两行之间有多少行
2. 不能用前缀做搜索，必须是全部

• MySQL建立索引的升序降序

  
  
  组合建立升降索引

• 数据库结构

1. I/O尽量变小
2. 相关东西尽量存放在一起
3. 一开始要考虑性能问题

• 设计表以达到最小化他们在硬盘中需要的空间

1. 表的列，在允许的情况下尽量小，列应该尽量不允许为空
   （如果有空，需要额外的位来表示该位是否为空）

2. 行格式

   
   
   行格式

三种格式，目的是让写到硬盘上的数据尽量小
空间和CPU的平衡

3. 索引应该尽量保持短小，和建立多行索引比起来建立复合索引比较好，但是顺序非常重要
4. Joins
   表中有很多列，每次搜索时只会用到部分列，这种时候可以进行分表。（经常访问和经常不访问的分开，读取相同数据块得到的有效数据更多）
5. 数据之间尽量减少冗余，但是合理使用一定冗余可以提高性能，需要进行平衡
6. 既可存成字符也可以存成数值，建议存成数值，它速度快，它表示相同范围用的字节数更少，且更好压缩
7. Vchar和BLOB，希望速度快要用Vchar，如果太大只能用BLOB
8. BLOB中存的地址，要读出地址再访问内存
   BLOB尽量用单独的表使用外键关联，而不是设计成一列。
   BLOB和TEXT用生成hash值加快比较速度，不同的情况是大多数的，少数相同的情况再去比较原本的值（HASH出的值可能重复，但是设计比较好的话概率非常小）

9. 在MySQL上同时打开表的数量是有限制的

   
   
   MySQL查看打开的表
   
   
   上限

打开缓存的限制（服务器）和打开文件数量的限制（操作系统）

10. 缓存满了怎么办？
    满了再开新的，会关掉之前的
    周期性关掉未用的缓存
    人为命令关掉缓存

    
    
    查看缓存命令