深入浅出MySQL笔记

版权声明:本文为博主原创文章,未经博主允许不得转载。

MySQL元数据:

-- 查看可用的字符编码
SHOW character set;    |    DESC infomation_schema.character_sets;

-- 查看字符集校对规则
SHOW COLLATION LIKE 'gbk';    |    DESC infomation_schema.COLLATIONS;

-- 查看当前服务器|数据库的字符集和校对规则
SHOW variables LIKE 'character_set_server';    character_set_database
SHOW variables LIKE 'collation_server';        collation_database

-- 查看表的字符集和校对规则
SHOW CREATE TABLE test_tab;

-- 查看数据库模式
SELECT @@sql_mode;

-- 修改数据库模式
SET SESSION | GLOBAL sql_mode='STRICT_TRANS_TABLES';

-- 查看是否支持分区
SHOW VARIABLES LIKE 'partition';

分区4种类型:

- RANGE:适用于 1.需删除过期数据 2.经常查询包含分区键
- LIST:类似RANGE,枚举值列表分区
- HASH:平均分布 1.常规HASH(取模) 2.线性HASH(线性2的幂运算),只支持整型
- KEY:类似HASH,但不允许使用表达式,除整形外,支持其他类型
- COLUMNS1. RANGE COLUMNS, 2. LIST COLUMNS

SQL优化

-- 查看当前session所有统计参数
SHOW status LIKE 'com_%' | Connections Uptime Slow_queries

观察以下参数:
- Com_select    - Com_insert    - Com_update    - Com_delete
- Innodb_rows_read    - Innodb_rows_inserted    - Innodb_rows_updated    - Innodb_rows_deleted
- Com_commit    - Com_rollback
- Connnections:链接服务器次数    - Uptime:服务器工作时间        - Slow_queries:慢查询次数

1、分析SQL语句

EXPLAN [extended] sql....

ALL    >    index    >    range    >    ref    >    eq_ref    >    const,system    >    NULL

全表        索引全表    范围         唯一/非唯一索引    唯一索引    主键 / 唯一索引        常量


2SHOW PROFILE 分析 SQL

-- 查看是否支持profile
SELECT @@have_profiling;

-- 开启session级别的profiling
SELECT @@profiling;

SET profilling=1;

-- 执行SQL
SELECT COUNT(*) FROM test;

SHOW profiles;

SHOW profile FOR query id;


关注executing, sending data, end 三项

SHOW profile cpu FOR query id;    -- 查看CPU耗费时间


3、索引优化

    B-Tree不是二叉树(binary),而平衡树(balanced)

    能用到索引场景:
        1.所有字段都匹配全值(=)
        2.范围匹配
        3.最左前缀匹配
        4.仅对索引列查询
        5.匹配列前缀,使用第一列索引
        6.部分精确,部分范围
        7.IS NULL会用到索引
        8.ICP特性,条件过滤下放到存储引擎

    不能使用索引的场景:
        1.%开头LIKE查询,一般先条件缩小范围,再回表LIKE
        2.数据类型出现隐式转换,例如字段是字符串,where=整数
        3.复合索引不遵循最左前缀
        4.Mysql认为扫索引比扫全表慢
        5.多个OR,其中字段有的有索引,有的没有


4、查看索引使用情况

SHOW status LIKE 'Handler_read%';

Handler_read_key越高证明使用索引越多;

Handler_read_rnd_next越高,证明全表扫描的多;

表优化

- 1.定期分析和检查表
ANALYZE TABLE test;

CHECK TABLE test;

- 2.定期优化表
OPTIMIZE TABLE test;

常用优化

1. 大批量插入数据

    - MyISAM:
        ALTER TABLE test DISABLE KEYS;
        LOAD DATA INFILE 'data.txt' INTO TABLE test;
        ALTER TABLE test ENABLE KEYS;

    - InnoDB:
        1.按主键顺序保存后导入
        2.SET UNIQUE_CHECKS=0关闭唯一检查
        3.SET AUTOCOMMIT=0

2. 优化INSERT

    1.使用INSERT INTO test VALUES(1,2),(1,3) ...
    2.使用INSERT DELAYED,马上直接,不保留在内存队列
    3.建表时索引文件和数据文件分开存放
    4.批量插入,增大bulk_insert_buffer_size(只对MyISAM有效)
    5.使用LOAD DATA INFILE

3. 优化ORDER BY 

    1.适当增大sort_buffer_size和max_length_for_sort_data值
    2.SELECT需要的字段,尽量不要SELECT *

4. 优化GROUP BY

    1.强制不排序 ORDER BY NULL
    2.JOIN取代嵌套子查询

5. 优化OR

    OR的各个字段都要有索引

6. 优化分页查询

    1.使用关联查询,先ORDER BYLIMIT后的分页数据,主键JOIN回表
    2.记录last_page_record,利用LIMIT n查询(只用于排序字段不会重复场景)

7. 人为优化

    1.SELECT COUNT(*) FROM test USER INDEX(idx_test_id);(希望参考使用索引)
    2.SELECT COUNT(1) FROM test IGNORE INDEX(idx_test_id);(忽略索引)
    3.SELECT COUNT(1) FROM test FORCE INDEX(idx_test_id);(强制使用索引)

8. 表分析
    SELECT * FROM tab PROCEDURE ANALYSE();
    SELECT * FROM tab PROCEDURE ANALYSE(16, 256);

MyISAM:

    -- 查看表锁争夺情况
    SHOW status LIKE 'table%';

    concurrent_insert: 
        0:不允许并发插入
        1:如果没有空洞,可以在表尾插入
        2:无论是否有空,都允许插入

    锁调度:
        low-priority-updates:默认以读优先
        SET LOW_PRIORITY_UPDATES=1:使当前连接更新请求优先级降低
        指定INSERT,UPDATE,DELETE语句的LOW_PRIORITY属性,降低优先级
        max_write_lock_count设置值,读大于此值,降低写优先级

InnoDB:

    事物ACID属性

    事物带来的问题:
        更新丢失、脏读、不可重复读、幻读

    隔离级别:
        未提交读、已提交读、可重复读、可序列化

    -- 查看行锁争夺情况
    SHOW status LIKE 'innodb_row_lock';

    行锁实现方式:
        Record lock:对索引项加锁
        Gap lock:对索引项之间的间隙枷锁
        Next-key lock:前两种组合

    1.如果检索记录没有索引,相当于表锁
    2.不是同一行记录,但是相同索引键,也会出现行锁
    3.有多个索引,不同事物使用不同索引锁定不同行
    4.检索数据MySQL通过执行计划代价决定

    死锁:

        1.多个session并发存取多个表,约定相同顺序访问,否则有可能产生死锁
        2.批量处理数据,事先对数据排序,每个线程按固定顺序处理,可降低死锁几率
        3.如果更新数据,应直接申请足够级别锁,不应先申请共享锁,再申请排它锁
        4.在可重复读隔离级别下,如果2个线程对相同记录加排他锁,此记录不存在,
            2线程都会加锁成功,如2个线程程序都试图插入新纪录,会出现死锁,
            将隔离级别改为读已提交级别可以避免
        5.隔离级别为读已提交,如果2个线程对相同记录加排他锁,此记录不存在,
            2线程都会加锁成功,如2个线程程序都试图插入新纪录,此时只有1个线程
            能插入成功,另一个线程出现锁等待,第1线程提交后,第2线程因主键重复出错,
            虽然出错,但已获得排他锁,此时有第3个线程来申请排他锁,也会出现死锁

MySQL Server优化

组成:1个主线程,4组IO线程,1个锁线程,1个错误监控线程,purge线程

内存优化原则:
    适当增大内存给MySQL
    如果是MyISAM表,预留适当内存给操作系统
    排序区、链接区是会话级别的,根据最大连接数合理分配

MyISAM优化:
    1.增大key_buffer_size,建议为内存的1/4

    2.使用多个索引缓存
        key_buffer_size=4G
        hot_cache.key_buffer_size=2G
        cold_cache.key_buffer_size=1G
        init_file=/path/to/data-directory/mysql_init.sql

        cache index sales in hot_cache;
        cache index sales2 in cold_cache;
        load index into cache sales, sales2;

    3.调整中点插入策略
        set global key_cache_divson_limit=70
        set global hot_cache.key_cache_divsion_limit=70

    4.调整read_buffer_size和read_rnd_buffer_size
        以上2个参数是每个session独占

InnoDB优化:
    1.增大innodb_buffer_pool_size
    2.调整old sublist大小,SHOW GLOBAL VARIABLES LIKE '%innodb_old_blocks_pct%'
    3.调整innodb_old_blocks_time
    4.减少缓存池数量,减少内部争用,调整innodb_buffer_pool_instances
    5.控制innodb buffer刷新,innodb_max_dirty_pages_pct,innodb_io_capacity
    6.打开doublewrite,SHOW GLOBAL VARIABLES LIKE '%doublewrite%'

并发优化:
    1.调整max_connections
    2.调整back_log
    3.调整table_open_cache
    4.调整thread_cache_size
    5.调整innodb_lock_wait_timeout

备份与恢复

备份:
    mysqldump [options] db_name [tables] > out.sql
    常用参数:
        --add-drop-database:加上drop database语句
        --add-drop-table:加上drop table语句
        --no-create-db:不要create database语句
        --no-create-info:不要create table语句
        --no-data:不要数据,只要表结构

恢复:
    mysql -uroot -p db_name < out.sql
    mysqlbinlog binlog-file | mysql -u root -p

表的导出:

    SELECT * FROM table INTO OUTFILE '/tmp/tmp.txt'

    mysqldump -u username -T target_dir db_name table_name [option]

表的导入:

    LOAD DATA INFILE  'filename' INTO TABLE table_name [option]

    mysqlimport -u root -p db_name out.sql [option]

权限

授权:
    GRAN SELECT ON *.* TO t1@localhost IDENTIFIED BY '123'

查看权限:

    SHOW GRANTS FOR t1@localhost;

回收/更改权限:

    REVOKE SELECT ON *.* FROM t1@localhost;

修改密码:

    - mysqladmin -u user_name -h host_name password 'newpwd'
    - SET PASSWORD FOR 't1'%'%' = PASSWORD('123')
    - SET PASSWORD = PASSWORD('123')
    - GRANT USERAGE ON *.* TO 't1'@'%' IDENTIFIED BY '123';
    - UPDATE user SET Password = PASSWORD('123') WHERE Host='%' AND User='t1';
    - GRANT USER ON *.* TO 't1'@'%' IDENTIFIED BY PASSWORD '123AE809808FDSFSFS';

删除账号:

    SHOW GRANTS FOR t1@localhost;
    DROP user t1@localhost;

如果此文章能给您带来小小的工作效率提升,不妨小额赞助我一下,以鼓励我写出更好的文章!
kaito-kidd WeChat Pay

微信打赏

kaito-kidd Alipay

支付宝打赏