• mysql优化问题


    mysql关键字执行顺序

    from > on > join > where > group by > having > select > distinct > union > order by


    sql语句优化

    (1)使用limit对查询结果的记录进行限定
    (2)避免select *,将需要查找的字段列出来
    (3)使用连接(join)来代替子查询
    (4)拆分大的delete或insert语句

    选择合适的数据类型

    (1)使用可存下数据的最小的数据类型,整型 < date,time < char,varchar < blob
    (2)使用简单的数据类型,整型比字符处理开销更小,因为字符串的比较更复杂。如,int类型存储时间类型,bigint类型转ip函数

    IPv4地址。人们常用VARCHAR(15)列来存储IP地址。然而,它们实际上是32们无符号整数,不是字符串。用小数点将地址分成四段的表示方法只是让人们阅读容易 。所以应该用无符号整数 存储IP地址。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。

        例:select inet_aton(‘192.168.222.255‘);  输出:3232292607

                select inet_ntoa(3232292607);        输出:192.168.222.255

    (3)使用合理的字段属性长度,固定长度的表会更快。使用enum、char而不是varchar
    (4)尽可能使用not null定义字段
    (5)尽量少用text,非用不可最好分表


    使用命令分析

    1、show查看状态

    1) 显示状态信息

    SHOW [SESSION|GLOBAL] STATUS LIKE '%Status_name%';
    session(默认):取出当前窗口的执行
    global:从mysql启动到现在
    (1)查看查询次数(插入次数com_insert、修改次数com_insert、删除次数com_delete)
    SHOW STATUS LIKE 'com_select';
    (2)查看连接数(登录次数)
    SHOW STATUS LIKE 'connections';
    (3)数据库运行时间
    SHOW STATUS LIKE 'uptime';
    (4)查看慢查询次数
    SHOW STATUS LIKE 'slow_queries';
    (5)查看索引使用的情况:
    SHOW STATUS LIKE 'handler_read%';
    handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。
    handler_read_rnd_next:这个值越高,说明查询低效。

    2、显示系统变量

    SHOW ENGINE INNODB STATUS;

    3、EXPLAIN分析查询

    EXPLAIN SELECT column_name FROM table_name;

    explain查询sql执行计划,各列含义:
    table:表名;
    type:连接的类型
    -const:主键、索引;
    -eq_reg:主键、索引的范围查找;
    -ref:连接的查找(join)
    -range:索引的范围查找;
    -index:索引的扫描;
    -all:全表扫描;
    possible_keys:可能用到的索引;
    key:实际使用的索引;
    key_len:索引的长度,越短越好;
    ref:索引的哪一列被使用了,常数较好;
    rows:mysql认为必须检查的用来返回请求数据的行数;
    extra:using filesort、using temporary(常出现在使用order by时)时需要优化。
    -Using filesort 额外排序。看到这个的时候,查询就需要优化了
    -Using temporary 使用了临时表。看到这个的时候,也需要优化

    3、PROFILING分析SQL语句

    1)开启profile。查看当前SQL执行时间
    SET PROFILING=ON;
    SHOW profiles;
    2)查看所有用户的当前连接。包括执行状态、是否锁表等
    SHOW processlist;

    4、PROCEDURE ANALYSE()取得建议

    通过分析select查询结果对现有的表的每一列给出优化的建议
    SELECT column_name FROM table_name PROCEDURE ANALYSE();

    5、OPTIMIZE TABLE回收闲置的数据库空间

    OPTIMIZE TABLE table_name;
    对于MyISAM表,当表上的数据行被删除时,所占据的磁盘空间并没有立即被回收,使用命令后这些空间将被回收,并且对磁盘上的数据行进行重排(注意:是磁盘上,而非数据库)。
    对于InnoDB表,OPTIMIZE TABLE被映射到ALTER TABLE上,这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。
    只需在批量删除数据行之后,或定期(每周一次或每月一次)进行一次数据表优化操作即可,只对那些特定的表运行。

    6、REPAIR TABLE修复被破坏的表

    REPAIR TABLE table_name;

    7、CHECK TABLE检查表是否有错误

    CHECK TABLE table_name;


    mysql索引类型

    1.普通索引,是最基本的索引,它没有任何限制。
    (1)直接创建索引
    CREATE INDEX index_name ON table(column(length))
    (2)修改表结构的方式添加索引
    ALTER TABLE table_name ADD INDEX index_name ON (column(length))
    (3)创建表的时候同时创建索引
    CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
    )
    (4)删除索引
    DROP INDEX index_name ON table
    2.唯一索引,与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
    (1)创建唯一索引
    CREATE UNIQUE INDEX indexName ON table(column(length))
    (2)修改表结构
    ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
    (3)创建表的时候直接指定
    CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
    );
    3.主键索引,是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。
    CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
    );
    4.组合索引
    指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合
    ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
    5.全文索引
    主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。
    (1)创建表的适合添加全文索引
    CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
    );
    (2)修改表结构添加全文索引
    ALTER TABLE article ADD FULLTEXT index_content(content)
    (3)直接创建索引
    CREATE FULLTEXT INDEX index_content ON article(content)


    索引的缺点

    1.虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行insert、update和delete。因为更新表时,不仅要保存数据,还要保存一下索引文件。
    2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会增长很快。
    索引只是提高效率的一个因素,如果有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。


    索引失效的情况(在表中建立索引,优先考虑where、group by使用到的字段)

    1、如果where中有or,及时其中有条件带索引也不会使用(若想使用or且还想用到索引,只能在or条件的每个列都加上索引),尽量避免使用in 和not in,会导致数据库引擎放弃索引进行全表扫描
    2、对于多列索引,不是使用的第一部分,则不会使用索引
    3、like查询是以%开头
    4、存在隐式转换,如果列列类型是字符串,一定要在条件将数据使用引号引用起来,否则不使用索引
    5、mysql估计使用全表扫描比使用索引快,则不使用索引(数据量较小的时候)
    6、where条件中使用函数或运
    7、范围条件查询导致索引失效


    什么样的列不适合加索引
    1、唯一性差
    2、where条件中不使用的字段
    3、使用<>时,效果一般(如果where后含有is null/is not null/like '%值%/where 1 = 1)
    4、频繁更新不用的字段,增加数据库的工作量,降低效率


    为什么说B+ 树比B 树更适合实际应用中操作系统的文件索引和数据库索引?
    B树:有序数组+平衡多叉树
    B+树:有序数组链表+平衡多叉树;
    1)B+树的磁盘读写代价更低
    B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对于B树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
    2) B+-tree的查询效率更加稳定
    由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。
    3)由于B+树的数据都存储在叶子结点中,分支结点均为索引,方便扫库,只需要扫一遍叶子结点即可,但是B树因为其分支结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,所以B+树更加适合在区间查询的情况,所以通常B+树用于数据库索引。

    详情参考:https://www.cnblogs.com/tiancai/p/9024351.html

  • 相关阅读:
    今天开通cnblogs了,留下点印记
    在windows下编译xvidcore
    linux下c文件里使用中文引发的编译错误及解决
    ThinkPad T400
    据说IBM欲64亿收购Sun
    Visual Studio 的 DllImport 缺陷
    再整MySQL的中文UTF8问题
    Webbased/online OCR services and demos
    用FileSystemWatcher实现山寨版Second Copy [1]
    以前的帖子没了??
  • 原文地址:https://www.cnblogs.com/2661314cn/p/12543427.html
Copyright © 2020-2023  润新知