mysql 优化浅谈

1、表结构优化

        1） 要有主键、primary key, 建议使用自增主键。唯一标示添加唯一索引。

        2）字段类型按照存储的数据设置对应类型。

        3）int/bigint/ 优于 decimal 优于 float/double

        4) 少用 txt/blob 等文本字段，建议使用 char 、varchar

        5)使用分库分表设计模式：1、业务拆分 2、数据库端使用中间件进行控制 mycat、atlas、zebra 等等。

2、索引

        mysql 中索引类型有： 1.B+ 树 2. 倒排序 3. R 树

                1、 B+树： 主键索引、唯一索引、二级索引

                2、倒排序： 全文索引

                3、R树 空间索引

    1）索引结构

        

 

 2、索引优化

        1) 主索引不建议使用 UUID md5 等无序字符，插入数据分频繁页分裂，影响性能，占用磁盘。

        2）适合的索引通常出现在 where 、join、group by 、order by 后面。

        3）区分度不高的字段不建议设置索引，比如 性别

        4）组合索引建议将区分部高的字段放到前面，区分度底的字段放到后面

        5）建议使用短索引，对于其前N个字符已经能够很好的区分，建议使用前缀索引，即优化了查询，还节省了空间。

        6）更新频繁的字段不建议创建索引。更新操作会变更索引，影响性能。

        7）不要创建冗余索引，一个字段不要出现在多个索引中。

        8）一张表建议5个索引左右。不要过度索引。

        9）组合索引建议5个字段以内，超过5个字段索引效率很低，严重占用磁盘。

3. SQL 优化

        sql 优化需要先进行sql 执行计划的分析。使用 explain 查看sql执行计划。使用了那些索引，耗时在哪里。

 1.id：查询序列号，id越大，越先执行
2.select_type：查询类型simple，primary，subquery，union，derived…
3.table：表名称或别名
4.type：连接使用了那种类型：const> eq_ref> ref > ref_or_null> range > index > ALL
5.possible_keys：MySQL能使用哪些索引在该表中找到行
6.key：MySQL实际使用的索引
7.key_len：实际使用的索引长度
8.ref：显示使用哪个列或常数与key一起从表中选择行
9.rows：MySQL估计的需要扫描的行数
10.Extra：显示上述信息之外的其它信息，但却很重要

type：const> eq_ref> ref > ref_or_null> range > index > ALL        

 const : 主键或者唯一索引查询，最多返回一行。

eq_ref:  join 关联查询，索引为主键或者非空唯一主键。

ref: 字段 索引，可以返回多行。

ref_or_null : 字段可以为null

range: 索引范围查询。

index : 扫描整个索引。

all : 全表扫描。

Extra：

Using filesort，进行额外的排序, order by

Using temporary，使用临时表，group by, order by

Using index，扫描索引

Using index condition，索引条件下推

Using where，从存储引擎取出数据后，需要在server层进一步过滤

Using intersect，索引合并，idx_c1, idx_c2 两个单列索引

Using index for group-by，group by 使用索引

Using join buffer (Block Nested Loop)，join连接