mysql sql语句调优及，索引总结

Mysql的索引

1.btree索引，btree索引是二叉平衡树的结构表有（myisam innodb），

2.Hash索引,通过hash算法计算到的索引是随机的没有规律(memory)，没有回杭

一、Btree索引

索引同时只能用上一个

查询一条sql的执行计划

Explain sql \G

此语句体现在 查询时索引使用情况分

查看sql执行的时间

Set profiling=on;

Show profiles;

如果要看具体那一条使用

Show profile for query 5 //具体哪一行

Show profile; //最新的sql的执行信息

聚簇索引和非聚簇索引，非聚簇索引是对数据行的引用（索引指向磁盘数据行），聚簇索引是对主键的引用

Mysisam使用的是非聚簇索引，存储的索引树跟数据相互独立分开的，使查询时，需要回行

Innodb是聚簇索引，

注意: innodb来说,

1: 主键索引 既存储索引值,又在叶子中存储行的数据

2: 如果没有主键(primary key), 则会Unique key做主键

3: 如果没有unique,则系统生成一个内部的rowid做主键.

4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

索引文件和数据是粘合的（主键下挂在着数据），非主键索树引指向主键是对主键的引用，

使用主键查询时不需要回行，但根据其他的键进行查询，需要回行

聚簇索引优势在使用主键查询时不需要回行，因为主键和数据是粘合的，

劣势，如果插入不规则数据，就会不断的造成索引的页分裂，因为主键索引下挂载着数据，就会造成性能低下//我测试的一张表分别顺序，和乱序插入10000条数据，相差60秒左右

测试脚本执行时间

$str=microtime();//返回微秒数//如果参数为true 返回 秒数精确到毫秒

索引覆盖

索引覆盖就是查询不走数据，只走索引文件

重复索引和冗余索引

重复索引会拖慢速度，冗余索引会起到索引覆盖的效果，查询数据不用回行，效率更快

冗余索引用的好是一种查询优化策略

索引碎片与维护

优化方法一

Alter table sss engine innodb;//虽然表面没有效果，但是他会把表里的数据整理一遍，

优化方法二

专用方法

Optimize table 表名，会对数据进行整理把碎片优化

注意如果表的数据太大不要频繁的操作，因为耗费资源

Sql语句优化

Sql语句好费时间的项

1.主要在沿着索引找键

2.取数据

优化

1.建立合理的联合索引，区分度合适

2.取少的行和列

3.使用索引覆盖技巧

思路1.不查->少查->高效的查

少查例子，比如一个网站的会员有多少，可以根据统计算每天的会员注册数量，估算出会员数

少查，取较少的列

高效的查，沿着索引查

in查询陷阱

mysql> explain select goods_id,cat_id from goods where cat_id in (select cat_id

from cat where parent_id=2) \G

goods表cat_id有索引

cat表cat_id 是主建

解决方式用连接查询

*************************** 1. row ***************************

       id: 1

select_type: PRIMARY

    table: goods

     type: ALL

possible_keys: NULL

      key: NULL

  key_len: NULL

      ref: NULL

     rows: 24

    Extra: Using where

*************************** 2. row ***************************

       id: 2

select_type: DEPENDENT SUBQUERY

    table: cat

     type: unique_subquery

possible_keys: PRIMARY

      key: PRIMARY

  key_len: 4

      ref: func

     rows: 1

    Extra: Using where

原因，其实是先执行的是外部sql

获得结果集，再子句中执行 select cat_id where cat_id=外层查到的cat_id and parent_id=2

此时子句就用到了主键，而主句用不到，要查询的表很大，效率很低

改进

用连接查询

mysql> explain select goods_id,goods.cat_id from goods inner join cat on goods.

cat_id=cat.cat_id \G

mysql> explain select goods_id,goods.cat_id from goods inner join cat on goods.

cat_id=cat.cat_id and cat.parent_id=2\G

*************************** 1. row ***************************

       id: 1

select_type: SIMPLE

    table: cat

     type: ALL

possible_keys: PRIMARY

      key: NULL

  key_len: NULL

      ref: NULL

     rows: 4

    Extra: Using where

*************************** 2. row ***************************

       id: 1

select_type: SIMPLE

    table: goods

     type: ref

possible_keys: pc

      key: pc

  key_len: 2

      ref: tpshop.cat.cat_id

     rows: 8

    Extra: Using where

下率很高

---

作者：webmazha
来源：CSDN
原文：https://blog.csdn.net/qq_29676303/article/details/69218657
版权声明：本文为博主原创文章，转载请附上博文链接！