美高梅开户-美高梅官方网站开户

您的位置:美高梅开户 > 数据库 > MySQL中的索引,开发中的mysql

MySQL中的索引,开发中的mysql

发布时间:2020-01-01 16:26编辑:数据库浏览(193)

    5.限制索引的数目

    索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。
    修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。

    使用前缀长度

      对于CHAR和VARCHAR列,只用一列的一部分就可创建索引。创建索引时,使用col_name(length)语法,对前缀编制索引。前缀包括每列值的前length个字符。BLOB和TEXT列也可以编制索引,但是必须给出前缀长度。
      例如,有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行索引,而是CREATE INDEX index_name ON tbl_name(col_name(10))。
      使用短索引不仅可以提高查询速度,而且可以节省磁盘空间和I/O操作。

    1、普通索引的管理实战:

    美高梅开户 1美高梅开户 2

    1.1添加索引:
    use db
    alter table student add index idx_cid(classid);
    
    1.2查询索引:
    show index from student;
    desc student;
    
    1.3删除索引:
    alter table student drop key idx_cid;
    

    代码

     

    多表连接

      如果数据表需要很多连接查询,首先应该确认两张数据表中连接的字段已经创建索引。这样,MySQL内部会启动优化连接的SQL语句的机制。
      除此之外,这些被用来连接的字段,应该是属于同一类型。例如,如果要把DECIMAL类型的字段和一个INT类型的字段连接在一起,MySQL就无法使用它们的索引。另外对于STRING类型,还需要有相同的字符集才行(两张数据表的字符集有可能不一样)。

    3.为常作为查询条件的字段建立索引

    如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,
    为这样的字段建立索引,可以提高整个表的查询速度。

    建立条件:经常查询、列值的重复值少

    注:如果经常作为条件的列,重复值特别多,可以建立联合索引。

    查看索引

    SHOW INDEX FROM tbl_name;
    SHOW KEYS FROM tbl_name;

    3、唯一索引

    内容唯一,但不是主键。

    alter table student add unique key uni_tel(telnum);

    怎么判断某个列的值都是唯一的?  

    如何选择聚簇索引或非聚簇索引

    动作描述 使用聚簇索引 使用非聚簇索引
    列经常被分组排序 使用 使用
    返回某范围内的数据 使用 不使用
    一个或极少不同值 不使用 不使用
    少量的不同值 使用 不使用
    大量的不同值 不使用 使用
    频繁更新的列 不使用 使用
    外键列 使用 使用
    主键列 使用 使用
    频繁修改索引列 不使用 使用

    企业中版本选择

    5.6 5.7 选择 GA 6个月到1年之间的----------------------------------MySQL体系结构

    实例:

    mysqld在启动时,自动派生master thread ------>生成工作的线程(read write 资源管理 等线程)
    预分配内存区域

    LIKE语句

      若LIKE语句是不以通配符开头的常量串,MySQL会使用索引。比如:

    SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'
    或
    SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'
    

      而以下情况无法使用索引:

    //LIKE语句以通配符开头
    SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'
    //LIKE语句不是常量串
    SELECT * FROM tbl_name WHERE key_col LIKE other_col
    

    客户端工具中自带命令

    美高梅开户 3美高梅开户 4

    1、h 或 help 或 ?
    2、G
    3、source
      source world.sql
    4、use
    5、show
    6、desc
    7、create
    8、truncate
    
    ........
    

    自带命令

    避免使用“SELECT * ”

      从数据库里读出的数据越多,那么查询就会变得越慢。而且如果数据库服务器和WEB服务器是两台独立的服务器的话,还会增加网络传输的负担。因此,应该养成需要什么就查找什么的好习惯:

    //不推荐使用
    SELECT * FROM tbl_name WHERE id = 1;
    //推荐使用
    SELECT username FROM tbl_name WHERE id = 1;
    

    权限管理

    普通用户
    系统用户
    1、定义:
      用户名:密码,用于限制用户所能使用的功能,并为数据库加锁,避免外人访问
    2、能做什么?
      1、登录系统
      2、管理系统对象

    5、索引的SQL语句汇总

    • 普通索引
      直接创建索引
      CREATE INDEX index_name ON tbl_name(col_name(length));
      修改数据表结构时添加索引
      ALTER TABLE tbl_name ADD INDEX index_name (col_name(length));
      创建数据表时同时创建索引
      CREATE TABLE tbl_name (
      ……
      INDEX index_name (col_name(length))
      )

    • 唯一索引
      直接创建索引
      CREATE UNIQUE INDEX index_name ON tbl_name(col_name(length));
      修改数据表结构时添加索引
      ALTER TABLE tbl_name ADD UNIQUE index_name (col_name(length));
      创建数据表时同时创建索引
      CREATE TABLE tbl_name (
      ……
      UNIQUE index_name (col_name(length))
      )

    • 全文索引
      直接创建索引
      CREATE FULLTEXT INDEX index_name ON tbl_name(col_name(length));
      修改数据表结构时添加索引
      ALTER TABLE tbl_name ADD FULLTEXT index_name (col_name(length));
      创建数据表时同时创建索引
      CREATE TABLE tbl_name (
      ……
      FULLTEXT index_name (col_name(length))
      )

    • 查看索引
      SHOW INDEX FROM tbl_name;
      SHOW KEYS FROM tbl_name;

    • 删除索引
      DORP INDEX index_name ON tbl_name;
      ALTER TABLE tbl_name DROP INDEX index_name;


    版权声明:欢迎转载,欢迎扩散,但转载时请标明作者以及原文出处,谢谢合作!             ↓↓↓
    

    6.删除不再使用或者很少使用的索引

    表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理
    员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。

    全文索引

      全文索引仅可用于MyISAM数据表,对于较大的数据集而言,将资料输入进一个没有全文索引的数据表中,然后创建索引;其速度远比把资料输入现有全文索引的数据表中更快。不过对于大容量的数据表来说,生成全文索引是一个非常消耗时间及硬盘空间的做法。

    直接创建索引
    CREATE FULLTEXT INDEX index_name ON tbl_name(col_name(length));
    修改数据表结构时添加索引
    ALTER TABLE tbl_name ADD FULLTEXT index_name (col_name(length));
    创建数据表时同时创建索引
    CREATE TABLE tbl_name (
    ……
    FULLTEXT index_name (col_name(length))
    )

    建立索引的原则(运维规范)

    select count(distinct user) from mysql.user;
    select count(distinct user,host) from mysql.user;
    

     

    为每张数据表设置一个ID字段

      应该为数据库里的每张表都设置一个ID做为其主键,且最好为INT类型的(推荐使用UNSIGNED),并设置上AUTO_INCREMENT。
      使用例如VARCHAR类型来当主键会使MySQL性能下降。而且,还有一些操作,例如集群、分区等需要使用主键,在这些情况下,主键的性能和设置变得非常重要。

    4.1、前缀索引

    根据字段的前N个字符建立索引
    alter table student add note varchar(200);
    alter table student add index idx_note(note(10)); 
    

    删除索引

    DORP INDEX index_name ON tbl_name;
    ALTER TABLE tbl_name DROP INDEX index_name;


    4、前缀索引和联合索引

    唯一索引

      唯一索引与普通索引类似,不同之处是:索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似。

    直接创建索引
    CREATE UNIQUE INDEX index_name ON tbl_name(col_name(length));
    修改数据表结构时添加索引
    ALTER TABLE tbl_name ADD UNIQUE index_name (col_name(length));
    创建数据表时同时创建索引
    CREATE TABLE tbl_name (
    ……
    UNIQUE index_name (col_name(length))
    )

    5)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

    这样会导致索引失效. 错误的例子:

    mysql> alter table tab add index inx_tel(telnum);
    
    
    mysql> desc tab;
     -------- ------------- ------ ----- --------- ------- 
    | Field | Type | Null | Key | Default | Extra |
     -------- ------------- ------ ----- --------- ------- 
    | id | int(11) | YES | | NULL | |
    | name | varchar(20) | YES | | NULL | |
    | telnum | varchar(20) | YES | MUL | NULL | |
     -------- ------------- ------ ----- --------- ------- 
    3 rows in set (0.01 sec)
    
    
    mysql> select * from tab where telnum='1333333';
     ------ ------ --------- 
    | id | name | telnum |
     ------ ------ --------- 
    | 1 | a | 1333333 |
     ------ ------ --------- 
    1 row in set (0.00 sec)
    
    mysql> select * from tab where telnum=1333333;
     ------ ------ --------- 
    | id | name | telnum |
     ------ ------ --------- 
    | 1 | a | 1333333 |
     ------ ------ --------- 
    1 row in set (0.00 sec)
    
    mysql> explain select * from tab where telnum='1333333';
     ---- ------------- ------- ------ --------------- --------- --------- ------- ------ ----------------------- 
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
     ---- ------------- ------- ------ --------------- --------- --------- ------- ------ ----------------------- 
    | 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
     ---- ------------- ------- ------ --------------- --------- --------- ------- ------ ----------------------- 
    1 row in set (0.00 sec)
    
    mysql> explain select * from tab where telnum=1333333;
     ---- ------------- ------- ------ --------------- ------ --------- ------ ------ ------------- 
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
     ---- ------------- ------- ------ --------------- ------ --------- ------ ------ ------------- 
    | 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
     ---- ------------- ------- ------ --------------- ------ --------- ------ ------ ------------- 
    1 row in set (0.00 sec)
    
    
    mysql> explain select * from tab where telnum=1555555;
     ---- ------------- ------- ------ --------------- ------ --------- ------ ------ ------------- 
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
     ---- ------------- ------- ------ --------------- ------ --------- ------ ------ ------------- 
    | 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
     ---- ------------- ------- ------ --------------- ------ --------- ------ ------ ------------- 
    1 row in set (0.00 sec)
    
    mysql> explain select * from tab where telnum='1555555';
     ---- ------------- ------- ------ --------------- --------- --------- ------- ------ ----------------------- 
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
     ---- ------------- ------- ------ --------------- --------- --------- ------- ------ ----------------------- 
    | 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
     ---- ------------- ------- ------ --------------- --------- --------- ------- ------ ----------------------- 
    1 row in set (0.00 sec)
    
    mysql>
    

    1、概述

      索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。可以将索引理解为一本书前面的目录,能加快数据的查询速度。
      对于没有索引的表,MySQL会遍历全部数据后选择符合条件的记录,因此单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢,而有了相应的索引之后,MySQL会直接在索引中查找符合条件的选项,效率会大大提升。

      索引分为聚簇索引和非聚簇索引两种:

    1. 聚簇索引是按照数据存放的物理位置为顺序的,对于数据而言,通常来说物理顺序结构只有一种,因此每张数据表也只能有一个聚簇索引。在设置主键时,系统会默认为其加上聚簇索引,当然也可以使用其他字段作为索引,此时需要在设置主键之前先手动为待选字段添加上唯一的聚簇索引,然后再设置主键,就可以解决这一问题。
    • 非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系,与数据的存储物理位置也没有关系;每张数据表对应的非聚簇索引可以有多个,根据不同列的约束可以建立不同要求的非聚簇索引。

      简单总结即,聚簇索引能提高多行检索的效率,而非聚簇索引对于单行的检索更有效。


    4.尽量使用前缀来索引

    如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索
    会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。

    多列索引、单列索引

      单个多列索引与多个单列索引的查询效果不同。当使用多个单列索引并执行查询时,MySQL只会从多个索引中选择一个限制最为严格的索引,剩余的索引将起不到作用。在建立多列索引时,字段的顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高。

    美高梅开户 5

    1) 没有查询条件,或者查询条件没有建立索引

    select * from tab; 全表扫描。
    select * from tab where 1=1;
    (1)select * from tab;
    
    SQL改写成以下语句:
    selec * from tab order by price limit 10 需要在price列上建立索引
    
    
    (2)
    select * from tab where name='zhangsan' name列没有索引
    
    改:
    1、换成有索引的列作为查询条件
    2、将name列建立索引
    

    2、索引的类型

    mysql大法

    组合索引(最左前缀)

      平时用的SQL查询语句一般都有比较多的限制条件,因此为了进一步提高MySQL的效率,就要考虑建立组合索引。例如上图中针对“last_name”和“first_name”建立一个组合索引:

    INDEX name (last_name,first_name)
    

      建立这样的组合索引,其实是相当于分别建立了下面两组组合索引:

    (last_name,first_name)
    (last_name)
    

      之所以没有(first_name)这样的组合索引,是因为MySQL组合索引采取“最左前缀”的结果。简单的理解就是只从最左面的开始组合,组合索引的最左列一定选择好,否则无法起到索引的效果。如果查询时最左列不在查询条件中则该组合索引不会被使用。
      最左列一定是使用最频繁的列,然而并不是只要包含在组合索引中的列的查询都会用到该组合索引,例如以下形式的查询语句能够使用组合索引:

    SELECT * FROM tbl_name WHERE last_name='Widenius';  
    
    SELECT * FROM tbl_name WHERE last_name='Widenius' AND first_name='Michael';  
    
    SELECT * FROM tbl_name WHERE last_name='Widenius' AND (first_name='Michael' OR first_name='Monty');  
    

      以下形式的查询语句不能使用组合索引:

    SELECT * FROM tbl_name WHERE first_name='Michael';  
    
    SELECT * FROM tbl_name WHERE last_name='Widenius' OR first_name='Michael';  
    

    连表

    美高梅开户 6美高梅开户 7

    内连接:
    (1)传统连接
    select a.id,b.xyz from a,b
    where a.id=b.id
    
    (2)自连接
    NATURAL JOIN
    select a.id,b.xyz from a
    NATURAL JOIN b
    
    
    (3)join using(id)
    select a.id,b.xyz from a
    JOIN b
    using(id);
    
    
    (4) join on 
    select a.id,b.xyz from a
    JOIN b
    on a.id=b.bid
    where
    
    
    (5) left join riht join 
    select a.id,b.xyz from a
    left JOIN b
    on a.id=b.bid
    
    
    表连接就是集合操作
    
    内链接
    
    where :
    select a.xxx,b.xxx from a,b
    where a.xxx=b.xxx and
    
    NATURAL JOIN :
    select a.xxx,b.xxx from a
    NATURAL JOIN b
    
    join using :
    select a.xxx,b.xxx from a
    JOIN b
    using(id)
    
    join on:
    
    
    外连接
    
    
    left join
    
    select a.xxx,b.xxx from a
    left JOIN b
    on a.xx=b.xxx
    and age>20
    
    select * from student where id>10
    
    select * from student where id=10 or id=20;
    select * from student where id in (10,20);
    
    
    
    select * from student where id=10
    union all
    select * from student where id=20
    

    连接

    3、索引的缺点

      虽然索引极大提高了数据查询的速度,但同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELET时,MySQL不仅要保存数据,还要保存一下索引文件,这样就会降低数据的维护速度。
    美高梅开户 ,  建立索引会占用磁盘空间,一般而言这个问题不太严重,但如果在一个拥有大量数据的表上创建了多种组合索引,索引文件的会膨胀很快。
      索引只是提高效率的一个因素,如果有大数据量的表,就需要花费时间研究建立最优秀的索引组合,或优化查询语句,且随着数据量的增加,维护索引的成本也会增加。


    数据库对象

    库 :用来存放表对象

    建库-----> 进入库 ----> 表操作

    表 : 原数据 数据行

    二维表: 数据行 (列名字 列属性 多少行 占多大磁盘空间 权限)原数据

    WHERE子句

    1. 在WHERE子句的查询条件中进行运算会导致索引失效;
    2. 在WHERE子句的查询条件中使用了函数会导致索引失效;
    3. 在WHERE子句的查询条件中使用“or”来连接条件会导致索引失效;
    4. 在WHERE子句的查询条件中使用“!=”或“<>”操作符会导致索引失效。

    索引及执行计划

    B树(默认索引树)
    1、B tree
    2、B*tree
    3、Hash 索引
    4、fulltext 索引

    B树:
    cluster indexes 聚集索引
    辅助索引 ------>人为管控的:unique 普通的 index


    8) 单独引用复合索引里非第一位置的索引列.

    列子:

    复合索引:
    
    DROP TABLE t1
    CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);
    
    ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
    
    DESC t1
    SHOW INDEX FROM t1
    
    走索引的情况测试:
    EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 AND sex='m';
    EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 ;
    EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND sex='m'; ----->部分走索引
    不走索引的:
    EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=20
    EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';
    EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';
    

     

    尽可能的使用“NOT NULL”

      要尽可能地把字段定义为“NOT NULL”。即使该数据表无须保存“NULL”(没有值),也有许多表包含了可空列(Nullable Column),这仅仅因为它是默认选项。除非真的要保存“NULL”,否则就把字段定义为“NOT NULL”。
      MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。可空列需要更多的储存空间,还需要在MySQL内部进行特殊处理。当可空列被索引的时候,每条记录都需要一个额外的字节,还可能导致MyISAM中固定大小的索引(例如一个整数字段上的索引)变成可变大小的索引。
      即使要在表中储存“没有值”的字段,还是有可能不使用“NULL”的。考虑使用0、特殊值或空字符串来代替它。


    连接层:

    1、提供连接协议(TCP/IP socket)
    2、验证的功能
    3、提供一个专门的连接线程(接收用户发来的SQL,执行完成之后返回最终结果,但是没有能力“看懂”SQL,会将SQL语句丢给下一层)

    4、使用索引的注意事项

    不走索引的情况(开发规范)

    重点关注:

    普通索引

      普通索引是最基本的索引,它没有任何限制,是大多数情况下用到的索引。

    直接创建索引
    CREATE INDEX index_name ON tbl_name(col_name(length));
    修改数据表结构时添加索引
    ALTER TABLE tbl_name ADD INDEX index_name (col_name(length));
    创建数据表时同时创建索引
    CREATE TABLE tbl_name (
    ……
    INDEX index_name (col_name(length))
    )

    mysqld三层结构

    美高梅开户 8美高梅开户 9

    1、DDL:数据库对象定义语言
    
    1.1 库定义
        (1)建库?
          CREATE DATABASE db CHARSET utf8;
    
          规范: 
          库名小写。
    
          建库时加上CHARSET
    
          字符集:utf8  utf8mb4  
    
          校对规则:默认都是大小写不敏感的。
    
          lower_case_table_names=1     
    
        (2)删库?
            DROP  DATABASE db;
         (3)修改库
        ALTER DATABASE db CHARSET utf8mb4
    -- 1.2 表定义
    -- 1.2.1 创建表
    USE db;
    CREATE TABLE t1 (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学生学号',
    NAME VARCHAR(30) NOT NULL COMMENT '学生姓名',
    gender ENUM ('m','f') NOT NULL DEFAULT 'm' COMMENT '学生性别',
    birthday DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间'
    ) ENGINE INNODB CHARSET utf8;
    
    -- 1.2.1 删除表
    DROP TABLE t1;
    
    -- 1.2.3 修改表
    
    -- (1)先添加手机号,内容非空(最后一列)
    ALTER TABLE t1 ADD telnum CHAR(11);
    
    -- int -2^31 ~ 2^31-1
    -- bigint -2^63 ~ 2^63-1
    
    -- (2)指定添加年龄列到name列后面的位置,示例如下:
    ALTER TABLE t1 ADD age TINYINT AFTER NAME;
    DESC t1;
    
    -- (3) 通过下面的命令在第一列添加sno字段。p200000001
    ALTER TABLE t1 ADD sno CHAR(10) FIRST;
    
    -- (4)若要删除字段,可采用如下命令。
    ALTER TABLE t1 DROP sno;
    
    -- (5)若要同时添加两个字段,可采用如下命令。
    ALTER TABLE t1 ADD sage TINYINT FIRST,ADD qq VARCHAR(15);
    
    -- (6)修改字段类型的命令如下:
    ALTER TABLE t1 MODIFY NAME VARCHAR(20) ;
    
    -- (7)修改字段名称的命令如下:
    ALTER TABLE t1 CHANGE age oldboyage CHAR(4) ;
    
    
    2、 DCL:
    
    GRANT 
    REVOKE
    
    
    3、DML
    
    
    INSERT INTO t1(id,NAME,age,...)
    
    
    
    UPDATE t1 SET xx=xx WHERE 1=1
    
    
    DELETE FROM t1 WHERE id=100
    

    DLL、DCL、DCL

    防止误删除,误修改

    TRIGGER 触发器, 防止无修改。

    伪删除 —— UPDATE 替代 DELETE

    美高梅开户 10美高梅开户 11

    DESC t1;
    CREATE TABLE t2 (id INT ,NAME VARCHAR(20));
    DESC t2;
    INSERT INTO t2(id,NAME ) VALUES(1,'zhang3'),(2,'li4'),(3,'wang5');
    SELECT * FROM t2;
    DELETE FROM t2 WHERE id=2;
    ALTER TABLE t2 ADD state TINYINT NOT NULL DEFAULT 1;
    UPDATE t2 SET state=0 WHERE id =2;
    SELECT * FROM t2 WHERE state=1;
    
    使用中间件,做灰度处理。
    

    防删

    本文由美高梅开户发布于数据库,转载请注明出处:MySQL中的索引,开发中的mysql

    关键词: 美高梅开户

上一篇:【美高梅开户】事情调节和锁定语句

下一篇:没有了