当前位置：Gxlcms > 数据库问题 > python学习_day47_mysql数据库索引相关

python学习_day47_mysql数据库索引相关

时间：2021-07-01 10:21:17 帮助过：12人阅读 

  1. <span style="color: #000000">普通索引INDEX：加速查找
  2. 唯一索引：
  3.     </span>-主键索引PRIMARY KEY：加速查找+<span style="color: #000000">约束（不为空、不能重复）
  4.     </span>-唯一索引UNIQUE:加速查找+<span style="color: #000000">约束（不能重复）
  5. 联合索引：
  6.     </span>-<span style="color: #000000">PRIMARY KEY(id,name):联合主键索引
  7.     </span>-<span style="color: #000000">UNIQUE(id,name):联合唯一索引
  8.     </span>-INDEX(id,name):联合普通索引

　　除此之外还有全文索引，即FULLTEXT，但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。

二、索引类型

　　索引主要包括hash和btree两大类型，我们在创建索引时可以为其指定索引类型。其中hash类型的索引：查询单条快，范围查询慢；btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）。

  1. <span style="color: #008000">#</span><span style="color: #008000">不同的存储引擎支持的索引类型也不一样</span>
  2. InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-<span style="color: #000000">text 等索引，不支持 Hash 索引；
  3. MyISAM 不支持事务，支持表级别锁定，支持 B</span>-tree、Full-<span style="color: #000000">text 等索引，不支持 Hash 索引；
  4. Memory 不支持事务，支持表级别锁定，支持 B</span>-tree、Hash 等索引，不支持 Full-<span style="color: #000000">text 索引；
  5. NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B</span>-tree、Full-<span style="color: #000000">text 等索引；
  6. Archive 不支持事务，支持表级别锁定，不支持 B</span>-tree、Hash、Full-text 等索引；

三、创建与删除索引

1、在创建表时创建索引

  1. <span style="color: #000000">create table t1(
  2.     id int,
  3.     name char(</span>5<span style="color: #000000">),
  4.     age int;
  5.     unique key uni_name(name),           </span><span style="color: #008000">#</span><span style="color: #008000">uni_name为索引名</span>
  6.     index index_age(age),                <span style="color: #008000">#</span><span style="color: #008000">index_age为索引名，不需要key</span>
  7.     primary key(id)                      <span style="color: #008000">#</span><span style="color: #008000">primary不需要起索引名，起了也不会显示</span>
  8.      );

技术分享

2、创建完表后为其添加索引

  1. <span style="color: #000000">create table t3(
  2.     id int,
  3.     name char(</span>5<span style="color: #000000">),
  4.     age int
  5.   );
  6. create index indx_name on t3(name);  </span><span style="color: #008000">#</span><span style="color: #008000">常用</span>
  7. <span style="color: #000000">alter table t3 add index indx_id(id);
  8. alter table t3 add primary key(age);</span>

技术分享

3、删除索引

  1. <span style="color: #000000">drop index indx_id on t3;
  2. alter table t3 drop primary key;</span>

　　上述第一个删除语法中，因primary key 没有名字，所以删除方式为：drop index ‘primary’ on t3，其他有名字的索引删除方式为：drop index 索引名 on 表名

四、测试索引

　　按照如下sql语句创建表s1，后续所有测试均基于此表：

  1. <span style="color: #008000">#</span><span style="color: #008000">1. 准备表</span>
  2. <span style="color: #000000">create table s1(
  3. id int,
  4. name varchar(</span>20<span style="color: #000000">),
  5. gender char(</span>6<span style="color: #000000">),
  6. email varchar(</span>50<span style="color: #000000">)
  7. );
  8. </span><span style="color: #008000">#</span><span style="color: #008000">2. 创建存储过程，实现批量插入记录</span>
  9. delimiter $$ <span style="color: #008000">#</span><span style="color: #008000">声明存储过程的结束符号为$$</span>
  10. <span style="color: #000000">create procedure auto_insert1()
  11. BEGIN
  12.     declare i int default </span>1<span style="color: #000000">;
  13.     </span><span style="color: #0000ff">while</span>(i<3000000<span style="color: #000000">)do
  14.         insert into s1 values(i,</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,concat(<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,i,<span style="color: #800000">‘</span><span style="color: #800000">@oldboy</span><span style="color: #800000">‘</span><span style="color: #000000">));
  15.         set i</span>=i+1<span style="color: #000000">;
  16.     end </span><span style="color: #0000ff">while</span><span style="color: #000000">;
  17. END$$ </span><span style="color: #008000">#</span><span style="color: #008000">$$结束</span>
  18. delimiter ; <span style="color: #008000">#</span><span style="color: #008000">重新声明分号为结束符号</span>
  19. <span style="color: #008000">#</span><span style="color: #008000">3. 查看存储过程</span>
  20. <span style="color: #000000">show create procedure auto_insert1\G 
  21. </span><span style="color: #008000">#</span><span style="color: #008000">4. 调用存储过程</span>
  22. call auto_insert1();

1、加索引可以加快查询效率，但是会降低写的效率

技术分享

五、正确使用索引

　　并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题。

1、范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like

　　大于 小于

技术分享

　　不等于

技术分享

　　between...and

技术分享

　　like 

技术分享

2、尽量选择区分度高的字段作为索引，区分度是指的字段中数据的重复性，越重复，区分度变低

技术分享

  1. <span style="color: #000000">我们编写存储过程为表s1批量添加记录，name字段的值均为egon，也就是说name这个字段的区分度很低（gender字段也是一样的，我们稍后再搭理它）
  2. 回忆b</span>+树的结构，查询的速度与树的高度成反比，要想将树的高低控制的很低，需要保证：在某一层内数据项均是按照从左到右，从小到大的顺序依次排开，即左1<左2<左3<<span style="color: #000000">...
  3. 而对于区分度低的字段，无法找到大小关系，因为值都是相等的，毫无疑问，还想要用b</span>+树存放这些等值的数据，只能增加树的高度，字段的区分度越低，则树的高度越高。极端的情况，索引字段的值都一样，那么b+树几乎成了一根棍。本例中就是这种极端的情况，name字段所有的值均为<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>
  4. <span style="color: #008000">#</span><span style="color: #008000">现在我们得出一个结论：为区分度低的字段建立索引，索引树的高度会很高，然而这具体会带来什么影响呢？？？</span>
  5. <span style="color: #008000">#</span><span style="color: #008000">1：如果条件是name=‘xxxx‘,那么肯定是可以第一时间判断出‘xxxx‘是不在索引树中的（因为树中所有的值均为‘egon’），所以查询速度很快</span>
  6. <span style="color: #008000">#</span><span style="color: #008000">2：如果条件正好是name=‘egon‘,查询时，我们永远无法从树的某个位置得到一个明确的范围，只能往下找，往下找，往下找。。。这与全表扫描的IO次数没有多大区别，所以速度很慢</span><span style="color: #000000"><br></span>

3、索引字段不可以参与计算

技术分享

4、and or

  1. <span style="color: #008000">#</span><span style="color: #008000">注意：</span>
  2. 条件1 <span style="color: #0000ff">and</span><span style="color: #000000"> 条件2:查询原理是：首先条件1与条件2都成立的前提下，才算匹配成功一条记录；其次mysql会按先优先判断索引字段的条件,如果按照该条件为真，但锁定的范围很小，或者干脆为假，那我们即便是没有为其他条件的字段添加索引，最终的结果仍然很快
  3. </span><span style="color: #008000">#</span><span style="color: #008000">例如：</span>
  4. <span style="color: #000000">若条件1的字段有索引，而条件2的字段没有索引，那么如果在按照条件1查出的结果很少的情况下，即便我们没有为条件2创建索引，最终的查询速度依然很快
  5. 若条件1的字段没有索引，而条件2的字段有索引，那么如果在按照条件2查出的结果很少的情况下，即便我们没有为条件1创建索引，最终的查询速度依然很快</span>

技术分享

 在左边条件成立但是索引字段的区分度低的情况下（name与gender均属于这种情况），会依次往右找到一个区分度高的索引字段，加速查询

技术分享

　　 经过分析，在条件为name=‘egon‘ and gender=‘male‘ and id>333 and email=‘xxx‘的情况下，我们完全没必要为前三个条件的字段加索引，因为只能用上email字段的索引，前三个字段的索引反而会降低我们的查询效率。

技术分享

 5、最左前缀匹配原则，非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

技术分享

 6、其他情况

  1. -<span style="color: #000000"> 使用函数
  2.     select </span>* <span style="color: #0000ff">from</span> tb1 where reverse(email) = <span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span><span style="color: #000000">;
  3.             
  4. </span>-<span style="color: #000000"> 类型不一致
  5.     如果列是字符串类型，传入条件是必须用引号引起来，不然...
  6.     select </span>* <span style="color: #0000ff">from</span> tb1 where email = 999<span style="color: #000000">;
  7.     
  8. </span><span style="color: #008000">#</span><span style="color: #008000">排序条件为索引，则select字段必须也是索引字段，否则无法命中</span>
  9. -<span style="color: #000000"> order by
  10.     select name </span><span style="color: #0000ff">from</span><span style="color: #000000"> s1 order by email desc;
  11.     当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
  12.     select email </span><span style="color: #0000ff">from</span><span style="color: #000000"> s1 order by email desc;
  13.     特别的：如果对主键排序，则还是速度很快：
  14.         select </span>* <span style="color: #0000ff">from</span><span style="color: #000000"> tb1 order by nid desc;
  15.  
  16. </span>-<span style="color: #000000"> 组合索引最左前缀
  17.     如果组合索引为：(name,email)
  18.     name </span><span style="color: #0000ff">and</span> email       --<span style="color: #000000"> 命中索引
  19.     name                 </span>--<span style="color: #000000"> 命中索引
  20.     email                </span>--<span style="color: #000000"> 未命中索引
  21. </span>- count(1)或count(列)代替count(*<span style="color: #000000">)在mysql中没有差别了
  22. </span>- create index xxxx  on tb(title(19)) <span style="color: #008000">#</span><span style="color: #008000">text类型，必须制定长度</span>
  1. - 避免使用select *
  2. - count(1)或count(列) 代替 count(*<span style="color: #000000">)
  3. </span>-<span style="color: #000000"> 创建表时尽量时 char 代替 varchar
  4. </span>-<span style="color: #000000"> 表的字段顺序固定长度的字段优先
  5. </span>-<span style="color: #000000"> 组合索引代替多个单列索引（经常使用多个条件查询时）
  6. </span>-<span style="color: #000000"> 尽量使用短索引
  7. </span>- 使用连接（JOIN）来代替子查询(Sub-<span style="color: #000000">Queries)
  8. </span>-<span style="color: #000000"> 连表时注意条件类型需一致
  9. </span>- 索引散列值（重复少）不适合建索引，例：性别不适合

六、索引合并与覆盖

1、索引合并

  1. <span style="color: #008000">#</span><span style="color: #008000">索引合并：把多个单列索引合并使用</span>
  2. <span style="color: #008000">#</span><span style="color: #008000">分析：</span>
  3. <span style="color: #000000">组合索引能做到的事情，我们都可以用索引合并去解决，比如
  4. create index ne on s1(name,email);</span><span style="color: #008000">#</span><span style="color: #008000">组合索引</span>
  5. 我们完全可以单独为name和email创建索引，然后按照where name=<span style="color: #800000">‘</span><span style="color: #800000">xxx</span><span style="color: #800000">‘</span> <span style="color: #0000ff">and</span> email=<span style="color: #800000">‘</span><span style="color: #800000">xxx</span><span style="color: #800000">‘</span>使用 <span style="color: #008000">#</span><span style="color: #008000">索引合并</span>
  6. <span style="color: #000000">
  7. 组合索引可以命中：
  8. select </span>* <span style="color: #0000ff">from</span> s1 where name=<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span><span style="color: #000000"> ;
  9. select </span>* <span style="color: #0000ff">from</span> s1 where name=<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span> <span style="color: #0000ff">and</span> email=<span style="color: #800000">‘</span><span style="color: #800000">adf</span><span style="color: #800000">‘</span><span style="color: #000000">;
  10. 索引合并可以命中：
  11. select </span>* <span style="color: #0000ff">from</span> s1 where name=<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span><span style="color: #000000"> ;
  12. select </span>* <span style="color: #0000ff">from</span> s1 where email=<span style="color: #800000">‘</span><span style="color: #800000">adf</span><span style="color: #800000">‘</span><span style="color: #000000">;
  13. select </span>* <span style="color: #0000ff">from</span> s1 where name=<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span> <span style="color: #0000ff">and</span> email=<span style="color: #800000">‘</span><span style="color: #800000">adf</span><span style="color: #800000">‘</span><span style="color: #000000">;
  14. 乍一看好像索引合并更好了：可以命中更多的情况，但其实要分情况去看，如果是name</span>=<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span> <span style="color: #0000ff">and</span> email=<span style="color: #800000">‘</span><span style="color: #800000">adf</span><span style="color: #800000">‘</span>,那么组合索引的效率要高于索引合并，如果是单条件查，那么还是用索引合并比较合理

2、索引覆盖

  1. <span style="color: #008000">#</span><span style="color: #008000">覆盖索引：</span>
  2.     -<span style="color: #000000"> 所有字段（条件的，查询结果的等）都是索引字段
  3.     http:</span>//blog.itpub.net/22664653/viewspace-774667/
  4. <span style="color: #008000">#</span><span style="color: #008000">分析</span>
  5. select age <span style="color: #0000ff">from</span> s1 where id=123 <span style="color: #0000ff">and</span> name = <span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>; <span style="color: #008000">#</span><span style="color: #008000">id字段有索引，但是name字段没有索引</span>
  6. <span style="color: #000000">该sql命中了索引，但未覆盖全部。
  7. 利用id</span>=<span style="color: #000000">123到索引的数据结构中定位到了id字段，但是仍要判断name字段，但是name字段没有索引，而且查询结果的字段age也没有索引
  8. 最牛逼的情况是，索引字段覆盖了所有，那全程通过索引来加速查询以及获取结果就ok了</span>

七、查询优化神器explain

　　关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

技术分享

八、慢日志管理

  1. <span style="color: #000000">慢日志
  2.             </span>- 执行时间 > 10
  3.             -<span style="color: #000000"> 未命中索引
  4.             </span>-<span style="color: #000000"> 日志文件路径
  5.             
  6.         配置：
  7.             </span>-<span style="color: #000000"> 内存
  8.                 show variables like </span><span style="color: #800000">‘</span><span style="color: #800000">%query%</span><span style="color: #800000">‘</span><span style="color: #000000">;
  9.                 show variables like </span><span style="color: #800000">‘</span><span style="color: #800000">%queries%</span><span style="color: #800000">‘</span><span style="color: #000000">;
  10.                 set </span><span style="color: #0000ff">global</span> 变量名 =<span style="color: #000000"> 值
  11.             </span>-<span style="color: #000000"> 配置文件
  12.                 mysqld </span>--defaults-file=<span style="color: #800000">‘</span><span style="color: #800000">E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini</span><span style="color: #800000">‘</span><span style="color: #000000">
  13.                 
  14.                 my.conf内容：
  15.                     slow_query_log </span>=<span style="color: #000000"> ON
  16.                     slow_query_log_file </span>= D:/<span style="color: #000000">....
  17.                     
  18.                 注意：修改配置文件之后，需要重启服务</span>
  1. <span style="color: #000000">MySQL日志管理
  2. </span>========================================================<span style="color: #000000">
  3. 错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息
  4. 二进制日志: 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作
  5. 查询日志: 记录查询的信息
  6. 慢查询日志: 记录执行时间超过指定时间的操作
  7. 中继日志： 备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放
  8. 通用日志： 审计哪个账号、在哪个时段、做了哪些事件
  9. 事务日志或称redo日志： 记录Innodb事务相关的如事务执行时间、检查点等
  10. </span>========================================================<span style="color: #000000">
  11. 一、bin</span>-<span style="color: #000000">log
  12. </span>1<span style="color: #000000">. 启用
  13. </span><span style="color: #008000">#</span><span style="color: #008000"> vim /etc/my.cnf</span>
  14. <span style="color: #000000">[mysqld]
  15. log</span>-bin[=<span style="color: #000000">dir\[filename]]
  16. </span><span style="color: #008000">#</span><span style="color: #008000"> service mysqld restart</span>
  17. 2<span style="color: #000000">. 暂停
  18. </span>//<span style="color: #000000">仅当前会话
  19. SET SQL_LOG_BIN</span>=<span style="color: #000000">0;
  20. SET SQL_LOG_BIN</span>=1<span style="color: #000000">;
  21. </span>3<span style="color: #000000">. 查看
  22. 查看全部：
  23. </span><span style="color: #008000">#</span><span style="color: #008000"> mysqlbinlog mysql.000002</span>
  24. <span style="color: #000000">按时间：
  25. </span><span style="color: #008000">#</span><span style="color: #008000"> mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"</span><span style="color: #008000">
  26. #</span><span style="color: #008000"> mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"</span><span style="color: #008000">
  27. #</span><span style="color: #008000"> mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54" </span>
  28. <span style="color: #000000">
  29. 按字节数：
  30. </span><span style="color: #008000">#</span><span style="color: #008000"> mysqlbinlog mysql.000002 --start-position=260</span><span style="color: #008000">
  31. #</span><span style="color: #008000"> mysqlbinlog mysql.000002 --stop-position=260</span><span style="color: #008000">
  32. #</span><span style="color: #008000"> mysqlbinlog mysql.000002 --start-position=260 --stop-position=930</span>
  33. 4. 截断bin-log（产生新的bin-<span style="color: #000000">log文件）
  34. a. 重启mysql服务器
  35. b. </span><span style="color: #008000">#</span><span style="color: #008000"> mysql -uroot -p123 -e ‘flush logs‘</span>
  36. 5. 删除bin-<span style="color: #000000">log文件
  37. </span><span style="color: #008000">#</span><span style="color: #008000"> mysql -uroot -p123 -e ‘reset master‘ </span>
  38. <span style="color: #000000">
  39. 二、查询日志
  40. 启用通用查询日志
  41. </span><span style="color: #008000">#</span><span style="color: #008000"> vim /etc/my.cnf</span>
  42. <span style="color: #000000">[mysqld]
  43. log[</span>=<span style="color: #000000">dir\[filename]]
  44. </span><span style="color: #008000">#</span><span style="color: #008000"> service mysqld restart</span>
  45. <span style="color: #000000">
  46. 三、慢查询日志
  47. 启用慢查询日志
  48. </span><span style="color: #008000">#</span><span style="color: #008000"> vim /etc/my.cnf</span>
  49. <span style="color: #000000">[mysqld]
  50. log</span>-slow-queries[=<span style="color: #000000">dir\[filename]]
  51. long_query_time</span>=<span style="color: #000000">n
  52. </span><span style="color: #008000">#</span><span style="color: #008000"> service mysqld restart</span>
  53. MySQL 5.6<span style="color: #000000">:
  54. slow</span>-query-log=1<span style="color: #000000">
  55. slow</span>-query-log-file=<span style="color: #000000">slow.log
  56. long_query_time</span>=3<span style="color: #000000">
  57. 查看慢查询日志
  58. 测试:BENCHMARK(count,expr)
  59. SELECT BENCHMARK(</span>50000000,2*3<span style="color: #000000">);
  60. 日志管理</span>

 

python学习_day47_mysql数据库索引相关

标签：效果   相等   ref   flush   二进制日志   而且   管理   查询   views   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！