应该努力地去生活,对过往闭口不提,是好是坏皆为经历。
MySql中索引这部分知识是要去看下会的,特别是对自己有点要求的,因为索引使用到好的对Sql的查询语句速度会大大的提升,所以是很有必要去学习的。
MySql 官方对索引的定义
索引(Index)是帮助MySQL高效获取数据的数据结构。 ----> 推断出 : 可以得到索引的本质:索引是数据结构。
可以简单的理解为 : 排好序的快速查找数据结构
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上.
聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种类型的索引之外,还有哈稀索引(hash index)等。
MySql 索引优势 :
打个网上都有的比列 :
类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本。通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
MySql 索引的不足之处
需要占用空间 : 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的。
添加修改删除等 : 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。
MySql 中索引的介绍
主键索引 : 设定为主键后数据库会自动建立索引,innodb为聚簇索引.
单值索引 : 即一个索引只包含单个列,一个表可以有多个单列索引
创建语法 : CREATE INDEX idx_test_name ON test(test_name);
删除语法 : DROP INDEX idx_test_name ;
唯一索引 : 索引列的值必须唯一,但允许有空值
创建语法 : CREATE UNIQUE INDEX idx_test_no ON test(test_no);
删除语法 : DROP INDEX idx_test_noon test;
符合索引 : 即一个索引包含多个列
创建语法 : CREATE INDEX idx_no_name ON customer(customer_no,customer_name);
删除语法 : DROP INDEX idx_no_name on customer ;
基本的语法使用 :
创建 : ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
删除 : DROP INDEX [indexName] ON mytable;
查看 : SHOW INDEX FROM table_name\G
添加方式 :
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):
该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):
这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER TABLE tbl_name ADD INDEX index_name (column_list):
添加普通索引,索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):
该语句指定了索引为 FULLTEXT ,用于全文索引。
一般需要创建索引的情况 和 一般不要创建的
一般需要 :
1 : 主键自动建立唯一索引
2 : 频繁作为查询条件的字段应该创建索引(where 后面的语句)
3 : 查询中与其它表关联的字段,外键关系建立索引
4 : 单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
5 : 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
6 : 查询中统计或者分组字段
一般不需要 :
1 : 数据量不多的话
2 : 经常增删改的表 : 提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件
3 : Where条件里用不到的字段不创建索引
4 : 数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
一般MySql 常见的瓶颈
CPU : SQL中对大量数据进行比较、关联、排序、分组
IO : 实例内存满足不了缓存数据或排序等需要,导致产生大量 物理 IO。查询执行效率低,扫描过多数据行。
锁 : 不适宜的锁的设置,导致线程阻塞,性能下降。死锁,线程之间交叉调用资源,导致死锁,程序卡住。
对于查询慢的处理方法
估计慢查询是我们都很好奇和想去理解或者处理的东西
1 : 慢查询日志 :
- MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。
- 具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10秒以上的语句。
- 由他来查看哪些SQL超出了我们的最大忍耐时间值,比如一条sql执行超过5秒钟,我们就算慢SQL,希望能收集超过5秒的sql,结合之前explain进行全面分析。
默认情况下,MySQL数据库没有开启慢查询日志,需要我们手动来设置这个参数.当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件
查看是否开启 : SHOW VARIABLES LIKE '%slow_query_log%'; 开启 : set global slow_query_log=1;
SHOW VARIABLES LIKE 'long_query_time%'; 这是查看设置多少秒才能算的。
show global status like '%Slow_queries%'; 当前系统中有多少慢的Sql
最后说下慢的Sql都会在对应的日志文件中
2 : Show Profile
- mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
默认情况下,参数处于关闭状态,并保存最近15次的运行结果
Show variables like 'profiling'; 查看是否开启 set profiling=1; 开启
show profiles 是查看结果
诊断SQL,show profile cpu,block io for query n (n为上一步前面的问题SQL数字号码);
3 : 全局查询日志
- 在mysql的my.cnf中进行设置
- 开启 : general_log=1
- 记录日志文件路径 : general_log_file=/path/logfile
- 输出格式 : log_output=FILE
- set global general_log=1;
- set global log_output='TABLE'; : 全局日志可以存放到日志文件中,也可以存放到Mysql系统表中。存放到日志中性能更好一些,存储到表中
- select * from mysql.general_log;
- 不建议在生产环境中使用
使用Explain来分析Sql的好坏文章 Explain链接地址
加油,坚持学习下去方可不迷失自我。