Mysql总结四

Mysql优化

Mysql可以优化的方面：存储引擎、数据类型、范式、索引
分库分表、主从复制、读写分离、负载均衡(nginx)…

支付流程：接口 ——》将服务进行扩容，扩容成10台服务
负载均衡：将所有请求均衡的分配给不同的服务器，让每台服务器的请求保持均衡

存储引擎
myiasm: 并发量特别大-》innodb

数据类型：
选取原则：内存占用量比较小的，选取定长类型 varchar(5)

范式：根据合适的情况建立依赖关系

索引：选取合适的索引 分析：explain
主键索引、单值索引、排序 多条件查询 索引失效

单表查询执行过程及优化-普通索引

使用explain分析通过id来查询数据

mysql> explain select * from student where SID= 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

student表中虽然有多条数据，但是只影响到其中一行，是因为使用了primary 主键索引(Innodb的存储引擎)，通过id=5来查询时，在B+上使用的是主键索引来查询，通过主键索引就可以一次来查询出当前行的数据。

通过Sname=X 来查询ID值是多少？
select SID from student where Sname =‘CJ1210’;

mysql> explain select SID from student  where Sname ='LG1213'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 27
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

过explain分析执行过程，查询条件时Sname=LG1210数据，执行过程是使用到了idx_name索引，采用的是辅助索引（INNODB的辅助：叶子节点上存储的是关键字和主键值），通过查询一次辅助索引就能获取到查询值，不需要到主键索引上获取信息

通过Sname=X 来查询年龄值是多少？
select Sage from student where Sname =‘LG1213’;

mysql> explain select Sage from student where Sname ='LG1213'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 27
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

sql通过Sname来查询Sage值，由于Sname是创建了辅助索引，要查询的Sage是在主键索引的叶子节点点，执行的过程先通过Sname查询辅助索引idx_name,找到主键id，通过主键id值来主键索引上来查询，该过程进行了两次索引查询过程。

查询过程中建议合理的使用select结果值，如果经常通过Sname来查询ssex，建议创建(Sname,Ssex)的联合索引，注意最左侧原则，在辅助索引上最左侧的值建立一个辅助索引的B+树，叶子节点上存在的就是关键字Sname和Ssex以及主键值，在执行SQL时select SID from student where Sname =‘LG1213’，只需要查询一次辅助索引就可以拿到结果了。

单表查询执行过程及优化-普通索引+排序或者分组

用户订单表信息，来查询用户的订单信息并倒叙排序
select * from orderlist where userid=1 order by data desc;

explain select * from orderlist where userid=1 order by date desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orderlist
         type: ALL
possible_keys: idx_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

这个过程中查询了整个表，效率是比较低的，有序查询完userID=1所有的行记录后，还要按照date进行排序，所以出现了Using filesort。出现文件排序，效率比较低，一般要进行优化。

如何优化：force index (索引名):指定索引，强制索引一定执行

mysql> explain select * from orderlist force index (idx_id) where userid=1 order by date desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orderlist
         type: ref
possible_keys: idx_id
          key: idx_id
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

通过force 强制使用索引后，执行过程中是使用到了idx_id索引，查询表也就没有使用到全表查询了
注意：一个SQL查询一张表，一次只能使用一个索引，因此使用了idx_id，那么idx_date就使用不了了，

继续优化，可以创建一个联合索引（userid,date）userId放在最左侧

explain select * from orderlist force index(idx_id_date) where userid=1 order by date desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orderlist
         type: ref
possible_keys: idx_id_date
          key: idx_id_date
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

通过userID和date创建的联合索引，不会出现filesort问题，根据userid=1查询辅助索引，找到的数据已经按照date排好序了，提高了查询效率

多表查询-多表进行连接查询及优化

在使用多表连接查询的时候，MYSQL会首先判断那个表小，表小主要指的是数据行数少。
小表无论如何都是整表遍历的，是使用不到索引的，但是大表就使用到索引了
所以在连接查询的时候，小表总是要整表搜索的，建索引没有用，大表创建索引是能提高查询效率的，

小表决定查询次数，大表决定查询时间
在连接查询中，大表小表的角色是不一样的，没有where子句，那么就按照表的行数来定，如果有where子句，那么就按照条件过滤完的行数来定大小表!

索引的设计原则

1、给查询操作较多的属性添加索引
2、给经常作为过滤条件的字段添加索引
…

对sql语句优化的经验

① 为查询缓存优化查询
② EXPLAIN 我们的SELECT查询(可以查看执行的行数)
③ 当只要一行数据时使用LIMIT 1
④ 为搜索字段建立索引
⑤ 在Join表的时候使用相当类型的列，并将其索引
⑥ 千万不要 ORDER BY RAND ()
⑦ 避免SELECT *
⑧ 永远为每张表设置一个ID
⑨ 可以使用ENUM 而不要VARCHAR
⑩ 尽可能的使用NOT NULL
⑪ 固定长度的表会更快
⑫ 垂直分割
⑬ 拆分打的DELETE或INSERT语句
⑭ 越小的列会越快
⑮ 选择正确的存储引擎
⑯ 小心 “永久链接”